Howto shrink MSSQL DB and transaction logs without Admin privileges
Feb 11, 2011
If you do not have Administrator privilege on the MS SQL Database server (common for shared hosting environments), you may often face a problem with shrinking the database or the transaction log, specially if the disk space is limited for you. You can easily shrink your database and log files using the simple scripts given below, even if you have the restricted db_owner
Script to shrink the database
DBCC SHRINKDATABASE (<databasename>, 0);
NOTE: <databasename> is the name of the database which you want to shrink. The second parameter in the SHRINKDATABASE command denotes the percentage of free space you want to keep in the database. If you put 0 (Zero) it means the database will be shrunk to the minimum possible size.
Script to shrink the transaction log
ALTER DATABASE <databasename> SET RECOVERY SIMPLE
ALTER DATABASE <databasename> SET RECOVERY FULL
NOTE: <databasename> is the name of the database for which you want to shrink the transaction log. Recovery mode
of the database needs to be changed to simple
to shrink the log file successfully which must be changed back to full
The first parameter in the DBCC SHRINKFILE command denotes the file which you want to shrink and the log file is defined by the numeric value 2. The second parameter is as same as the SHRINKDATABASE command where 0 (Zero) is shown to shrink the log file to its minimum size.
The above commands can be run through the SQL Query Analyser by connecting to the remote DB from your end. Hopefully this will help you to manage database files to keep them in size.