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)
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)