category:

Howto shrink MSSQL DB and transaction logs without Admin privileges
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 database role. Script to shrink the database
USE <databasename> DBCC SHRINKDATABASE (<databasename>, 0); GO
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
USE <databasename> GO ALTER DATABASE <databasename> SET RECOVERY SIMPLE DBCC SHRINKFILE(2,0) ALTER DATABASE <databasename> SET RECOVERY FULL GO
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.
Share
Comments (0)

Leave a Reply

Your email address will not be published. Required fields are marked *

+ 40 = 48

Related:

Stay Updated

Please enter your details below to get
A Free Trial
x + x* =