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
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
Did you like this post?
|subscribe via rss||subscribe via e-mail|
|follow me on twitter|
转载本站任何文章请注明：转载至神刀安全网，谢谢神刀安全网 » Shrink a Live SQL Server Database and Logs – Caveats and Best Practices