DBCC Shrinkfile (<filename>, NOTRUNCATE) > 7 Hours

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Administration (2000)

DBCC Shrinkfile (<filename>, NOTRUNCATE) > 7 Hours

Author

Topic

Jayded
Starting Member

2 Posts

Posted - 2005-06-17 : 10:32:46

Hi there,

I would like to remove unused space from a 280GB database. My actions are as follows:

1. Shrink the data file but with the NOTRUNCATE option
2. Shrink the file after NOTRUNCATE is completed, but using the TRUNCATEONLY switch
3. Shrink the file to the size of the file that I would like it to be

There is currently 100GB's of free space in the database file, but the transaction log is obviously being filled up at a rapid rate.

May anyone please give me an idea how much diskspace is required for the transaction log to run in the first step, and possibly any time estimates. I'm not sure how NOTRUNCATE actually works, only what it's end result is, which I've read fom BOL and numerous SQL posts.

Looking forward to some feedback.
Cheers

Jayded
Starting Member

2 Posts

Posted - 2005-06-20 : 06:04:11

NOTRUNCATE finished after 15 hours, and took up 70GB's of diskspace in the transaction log.
TRUNCATEONLY took 95 seconds and released 105GB's from my database (which I was expecting - need to do this more regularily I agree!)

When I started I only had 35GB's free diskspace, so got backing up on the transaction log to our backup server, which meant that every hour the transaction log removed the data, and maintained the local disk space.

Lesson learnt - disaster averted!

robvolk
Most Valuable Yak

15732 Posts

Posted - 2005-06-20 : 07:15:13

It may not have been much faster, but running DBCC SHRINKFILE without either option would have accomplished both operations in one execution.

paulrandal
Yak with Vast SQL Skills

899 Posts

Posted - 2005-06-22 : 15:45:27

quote:
Originally posted by Jayded

Hi there,

I would like to remove unused space from a 280GB database. My actions are as follows:

1. Shrink the data file but with the NOTRUNCATE option
2. Shrink the file after NOTRUNCATE is completed, but using the TRUNCATEONLY switch
3. Shrink the file to the size of the file that I would like it to be

There is currently 100GB's of free space in the database file, but the transaction log is obviously being filled up at a rapid rate.

May anyone please give me an idea how much diskspace is required for the transaction log to run in the first step, and possibly any time estimates. I'm not sure how NOTRUNCATE actually works, only what it's end result is, which I've read fom BOL and numerous SQL posts.

Looking forward to some feedback.
Cheers

How much log space - it all depends on how much data needs to be moved to satisfy the parameters you specify for the shrink.

NOTRUNCATE compacts the data down below the implicit allocation fence you've set (by your parameters to the command) but does not release the free space back to the OS.

TRUNCATEONLY will always be very fast as it just releases the free space to the OS and changes the internal filesize.

Beware of doing regular shrinks. Depending on how your database is used, the database may have to grow again, in which case you've wasted your time.

Also - shrinking causes index fragmentation bigtime, which will slow down any queries that uses index range scans.

Thanks

Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2005-06-22 : 21:54:17

If you are worried about how much transaction log space is used, you can use a script to shrink it in smaller increments.


DBCC SHRINKFILE ('Data1', 280000 )
go
DBCC SHRINKFILE ('Data1', 279500 )
go
DBCC SHRINKFILE ('Data1', 279000 )
go
... and so on

CODO ERGO SUM

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources