Thursday, June 04, 2015

Shrinking a T-SQL database using SQL commands (and encapsulating it into a stored procedure)

Update: This post discusses shrinking the data file of a Microsoft SQL database, caused in this case by misconfiguring the initial size of the database. For shrinking the log file one must at least use type 1, not 0, in the query. Also, a very pertinent comment from NULLable warns of the performance issues related to shrinking database files resulting from the fragmentation of the file.

I had this situation when the available space on the SQL database disk was less than the size of the database, in this case the temp database. Someone had wrongly configured the database to have an initial size of 64GB. Changing the size of the file in Microsoft SQL Management Studio doesn't work because it tries to create a different file, fill it with the data and then replace the file. No space for that. Also, it is damn slow, even if you have the space (I have no idea why). Shrink doesn't work either, because the database will not go smaller than the configured initial size. Time to do it command line style. Well, with sql queries, but you know what I mean.

The code for it goes like this:
USE [master];


USE [tempdb]

DBCC SHRINKFILE (tempdev, 3000);   --- New file size in MB
As you can see, you need to know not only the name of the database, but also the logical name of the database file that you want to shrink. It is not even a string, it is like a keyword in the DBCC SHRINKFILE command. Even if it does work, one would benefit from encapsulating it into a stored procedure. Here is the final code:
CREATE PROC ShrinkDatabase(@DbName NVARCHAR(100),@SizeMB INT)

DECLARE @filename NVARCHAR(255)

DECLARE @sql NVARCHAR(Max) = 'SELECT @filename = FROM ['+REPLACE(@DbName,'''','''''')+'].sys.database_files dbf WHERE dbf.[type]=0'
EXEC sp_executesql @sql,N'@filename NVARCHAR(255) OUTPUT',@filename OUTPUT

SET @sql='USE [master];
EXEC sp_executesql @sql

SET @sql='USE ['+REPLACE(@DbName,'''','''''')+'];
DBCC SHRINKFILE ('+REPLACE(@filename,'''','''''')+', '+CONVERT(NVARCHAR(100),@SizeMb)+');'
EXEC sp_executesql @sql


Create it in the master database and use it like this:
EXEC master.dbo.ShrinkDatabase 'tempdb',3000
Take note that you cannot use this to "shrink up" the database. If the value you set is larger than the current size, the file will remain the same size as well as the setting for the initial size. Also take note of the fact that this stored procedure only shrinks the data file, not the log file (dbf.[type]=0).


NULLable said...

1) It's not clear if that file is a data file or a log file. Generally speaking, shrinking data files is not recommended because it's generates fragmentation -> it has a negative impact of db performance. Also, log truncation should be one time tasks. If db log needs truncation then, very likely, db has full or bulk recovery model and maintenance plan doesn't includes log backups.
2) Instead of '...[' + @objectName + ']...' I use QUOTENAME function.

Siderite said...

I did mention in the last comments that this shrinks the data file, also the query to find the file name uses type=0 which is the data file, but maybe you are right. I will update the post. Never used the QUOTENAME function. Thanks for that, too!