May 19, 2010 at 2:54 am
i wanna reclaim my unused space from log file and data file which wud be gud dbcc shrinkfile or dbcc shrink database??
and why?
May 19, 2010 at 3:08 am
1. backup the log
2. DBCC SHRINKFILE([log name here], TRUNCATEONLY)
3. If that didn't affect the log size then back it up again and run the shrink file command again.
4. DBCC SHRINKFILE([data file here], TRUNCATEONLY) -> repeat for any secondary data files
As regards to the TRUNCATEONLY option for the data file, it just tries to remove any unallocated pages from the end of the data file without moving around any pages thus causing fragmentation. If you run any other option on a SHRINK operation (size target/NOTRUNCATE) then you'll want to check fragmentation and rebuild were necessary. These are online operations btw and should run quickly. DBCC SHRINKDATABASE will do log and data files together so you could run that, but you might need to run it twice to reclaim log space (as highlighted in step 3 above with SHRINKFILE).
hth,
Alex.
May 20, 2010 at 6:21 am
if we will not give target size for data file as well as log file what does it take by default???
May 20, 2010 at 6:35 am
Before jumping to Shrink the Files be aware that shrinking will increase fragmentation.
What is the actual size of the data and log files and how much of that is free?
Blog -- LearnSQLWithBru
Join on Facebook Page Facebook.comLearnSQLWithBru
Twitter -- BruMedishetty
May 20, 2010 at 6:57 am
TRUNCATEONLY will never increase fragmentation as doesn't move any pages. Even so, you should only really do this if you're sure you're not going to fill the space you reclaim in the future as growing a data file is expensive. The common scenario (one I'm currently in!) is when you split up a data file into multiple other data files and want to reclaim from the original file. Then TRUNCATEONLY is your friend.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply