Wednesday, April 8, 2009

Shrinking SQL 2000, 2005 Databases


If you ever run in a situation like you have only MBs of space left on production SQL servers and your transaction logs are growing bigger and bigger and you want to claim disk space immediately then you might need to run following command

Backup log <Database name> with no_log

This command is not recommended to run in normal circumstances and after this you are not able to recover your database point in time. if you have proper backups configured then you don't need to run this command because after each full backup the transaction logs are automatically truncated. Also in case you have log shipping configured then this command will break your log shipping sequences. Only use if you don't have any space left on physical disk and you don't have any other alternatives available.

if you don't required point in time recovery then change your database recovery model to ‘Simple’ doing this will ensure that your transaction log will not get bigger.

The above command will not reclaim disk space, you have to run DBCC shrink statement or go to the SQL management studio and manually shrink log file.

No comments: