Shrink MS SQL Server Databases

In this example we will shrink the size of a Microsoft SQL Database. We do this by using the command DBCC SHRINKFILE.

To find out the size of a database use the stored procedure sp_spaceused, see below


USE [DATABASE_NAME]
GO
EXEC sp_spaceused
GO

Once you know which databases have become to large, use the procedure below to shrink it


SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[sp_ShrinkDatabaseSize]
AS
 
DECLARE @logName as nvarchar(100)
DECLARE @logBackup as nvarchar(100)
DECLARE @dbname as nvarchar(100)
 
SELECT @dbname = DB_NAME(DB_ID())
 
PRINT @dbname
 
SET @logName = @dbName + '_log'
SET @logBackup = @dbName + 'logbackup'
 
BACKUP LOG @dbName WITH TRUNCATE_ONLY
 
DBCC SHRINKFILE(@logName, 1)

If you have trouble shrinking the database because multiple users are currently accessing it, use this command to set it to single user access.


alter database DATABASE_NAME set single_user with rollback immediate 

Once you have completed the shrinking, use the command below to reinstate multi user access.

 
alter database DATABASE_NAME set multi_user with rollback immediate 



Comments

No comments yet.

Add Yours

  • Author Avatar

    YOU


Comment Arrow




About Author

Robert

Programming today is a race between software engineers striving to build bigger and better idiot-proof programs, and the Universe trying to produce bigger and better idiots. So far, the Universe is winning hands down.