Updated on December 20th, 2017.
If you have deleted a lot of data in the database and truncated some tables, probably you may want to shrink database files to recover the unused space. Basically shrinking will move the data pages at the end of the file into unoccupied space, available in the front of the file.
You can use the DBCC SHRINKDATABASE command. For example, this command
You can also shrink log file alone using DBCC SHRINKFILE command Take full back up of the database and run the following :
Caveats of shrinking database and logs:
1. SHRINK commands should not be done frequently as it will increase the index fragmentation.
2. It is a fully logged operation which will increase the log size and slows down the performance
3. Shrinking is not possible while the current database is being backed up up or restored
4. Shrinking is not possible when columnstore index is enabled
Best practices
1. Use SHRINK only if a lot of operations such as TRUNCATE and DROP has lead to unused space
2. Take full backup before shrinking database files. If you shrink log file, set the Recovery mode into SIMPLE before shrinking and make it FULL after shrinking
3. Never turn ON AUTO SHRINK option
4. Always rebuild indexes after SHRINKING the database files to avoid fragmentation
If you have deleted a lot of data in the database and truncated some tables, probably you may want to shrink database files to recover the unused space. Basically shrinking will move the data pages at the end of the file into unoccupied space, available in the front of the file.
You can use the DBCC SHRINKDATABASE command. For example, this command
DBCC SHRINKDATABASE (your_database,10)will decrease database size and allow for 10 percent free space.
You can also shrink log file alone using DBCC SHRINKFILE command Take full back up of the database and run the following :
ALTER DATABASE your_database SET RECOVERY SIMPLE; DBCC SHRINKFILE (your_database_Log, 10); ALTER DATABASE your_database SET RECOVERY FULL;It will set the log file size to 10 MB
Caveats of shrinking database and logs:
1. SHRINK commands should not be done frequently as it will increase the index fragmentation.
2. It is a fully logged operation which will increase the log size and slows down the performance
3. Shrinking is not possible while the current database is being backed up up or restored
4. Shrinking is not possible when columnstore index is enabled
Best practices
1. Use SHRINK only if a lot of operations such as TRUNCATE and DROP has lead to unused space
2. Take full backup before shrinking database files. If you shrink log file, set the Recovery mode into SIMPLE before shrinking and make it FULL after shrinking
3. Never turn ON AUTO SHRINK option
4. Always rebuild indexes after SHRINKING the database files to avoid fragmentation
About The Author
3 comments:
Hmm, you shouldn't need to "set the Recovery mode into SIMPLE ... and make it FULL after shrinking."
If you're in FULL recovery mode already, run your transaction log backup job. That'll make that log shrink right down.
If you're already using SIMPLE, there's no need to ever set it to FULL.
Good stuff, although I have my doubts about setting recovery mode to SIMPLE first and then FULL after the shrink.
Thanks for the comments. My point on on setting recovery mode to simple is under the assumption that the recovery mode is already FULL. In that case it is better to set it SIMPLE and then to FULL when doing the shrinking
Post a Comment