February 15, 2015

Simple command to shrink all database at once to reduce database size in SQL server

Simple command to shrink all database at once to reduce database size in SQL server

This is very simple command to shrink all database at once to reduce database size in SQL server but one thing you need to remember do not forget to make recovery mode simple before executing this command.


EXEC sp_MSForEachDB 'select ''?'' as [Database]; ALTER DATABASE [?] SET RECOVERY SIMPLE; DBCC SHRINKDATABASE (''?'' , 0)'


After execution of command it will display all database name with details of size. I hope this command will help you a lot on troubleshooting of disk size.

Shrinking and reducing database size in sql

Summary:

If there is big difference in ldf file and .bak file then you can shrink database to reduce size of database. you can use following method at

Details

use <dbname>
checkpoint
go
backup log <dbname> with truncate_only,
go
dbccshrinkfile(<logfilename>,1000)