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.
| Author |
Topic |
|
Pooh99
Starting Member
14 Posts |
 Posted - 2010-03-25 : 09:15:43
|
| Hi,I am new to DBA functionality on SQL Server 2005. I need to reclaim space from the database and/or Data/Log files.I know about shrinkfile on the LOG/Data files and Shrinkdatabase.I have read about ShrinkDatabase and a lot of writers warn you to stay away from this function as it creates fragmentation within the database??Can I ask does anybody know what is the best method of sorting this out for a maintenance plan ?I am also looking at rebuilding/defrag on indexes to claim space back.Thanksmarkg |
|
|
Kristen
Test
22859 Posts |
Posted - 2010-03-25 : 09:36:48
|
Log file needs X MB of peak space. Do not shrink below this amount (otherwise Log will just regrow, and each time it does that fragmentation is increased).However, if you do a one-off large deletion, which makes Tlog/LDF grow, then you can shrink it back to its "normal maximum working size"Quite often I see databases where the Tlog backup is only done once a day. This means that the TLog/LDF file has to hold 24 hours worth of transactions - the Tlog backup marks the space used by transactions (i.e. in Tlog/LDF file) that it backs up as available for reuse.Increasing the frequency of Tlog backups (every 15 minutes is ideal) means that the TLog/LDF file only has to be big enough to store the transactions for busiest 15 minutes of the day.You will have 96 backup files instead of 1, but the total size will be the same (except that each Tlog backup has some "overhead")So if you make 96 backups per day, instead of 1, your TLog/LDF file may be able to be reduced by nearly 99%  In reality of course the transaction volume will not be spread evenly through the day - but nonetheless the saving should be significant. You can then shrink your Tlog/LDF file to the new required size.Setting up Index Rebuild / Defrag will tidy up indexes which have page splits and/or deletions and may result in a saving of space - probably not a huge amount though - and if you have a Fill Factor set ion the indexes the index pages will be reset to provide that "free space" - so the index may, actually, get bigger.Note that FILL FACTOR should be set to 100% on any index on an IDENTITY column, or Sequential GUID, as there will be no insertions in the middle of the index. |
 |
|
|
|
|
|
|
|
Reclaiming Disk space