神刀安全网

Shrink a Live SQL Server Database and Logs – Caveats and Best Practices

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

Did you like this post?

Shrink a Live SQL Server Database and Logs - Caveats and Best Practices Shrink a Live SQL Server Database and Logs - Caveats and Best Practices
Shrink a Live SQL Server Database and Logs - Caveats and Best Practices Shrink a Live SQL Server Database and Logs - Caveats and Best Practices Shrink a Live SQL Server Database and Logs - Caveats and Best Practices
Shrink a Live SQL Server Database and Logs - Caveats and Best Practices subscribe via rss Shrink a Live SQL Server Database and Logs - Caveats and Best Practices subscribe via e-mail
Shrink a Live SQL Server Database and Logs - Caveats and Best Practices Shrink a Live SQL Server Database and Logs - Caveats and Best Practices follow me on twitter

转载本站任何文章请注明:转载至神刀安全网,谢谢神刀安全网 » Shrink a Live SQL Server Database and Logs – Caveats and Best Practices

分享到:更多 ()

评论 抢沙发

  • 昵称 (必填)
  • 邮箱 (必填)
  • 网址
分享按钮