All the advice you’ve heard out there is true: It’s not a good idea to regularly shrink your files! If you find yourself in that situation, you probably have other problems to deal with, such as
- You might need more storage!
- Data files are supposed to grow (in most cases)! If they’re growing faster than normal, however, you should find out why, and plan accordingly (and you do have growth baselines, right?)
- Shrinks cause fragmentation!
- Shrinks cause unnecessary I/O (because they files are just going to grow again).
Unfortunately, sometimes problems do happen, and now you have a fire to put out. While I have taken the time to size my volumes correctly, sometimes we’ll have a large data load, bulk update, etc. Over time, my log volume will be almost full, and one large update away from filling out. Not good!
Another situation I might run into is when I’m building a new test server, and using prod backups. The log file in that restore will be as big as it was when the backup took place, and the dev server may not have been sized as big as the prod server (or maybe it was, but as of 2 years ago) so now I can’t fit the log files!
For those situations, I wrote the script below. It’s not anything super fancy, it’ll just go through each of the user databases (database_ID >4) and issue a TRUNCATE_ONLY. What that does is instruct SQL to return any empty space that’s already available at the end of the log file back to the Operating System. If you set the parameter @ChangetoSimple to 1, it’ll change the recovery mode to simple before it issues the truncate_only. The ins and outs of the log space usage is a topic for a different post, but for my purposes here, all it means is that it’ll free up space in the log file.
The big letter caveat is DO NOT DO SET THIS TO 1 ON PRODUCTION, IF YOU DEPEND ON LOG BACKUPS! It will ruin your log backup chain, and you’ll need to start the chain again (with a full log backup). I usually only change things to simple in non-production environments.
USE master;
DECLARE @dbname sysname,
@logicalname sysname,
@sql VARCHAR(200);
DECLARE @ChangetoSimple INT = 0;
DECLARE @simple VARCHAR(200) = ' ';
SELECT name,
log_reuse_wait_desc
FROM sys.databases
ORDER BY name;
DECLARE list CURSOR FOR
SELECT DISTINCT
DB_NAME(mf.database_id),
mf.name
FROM sys.master_files mf
INNER JOIN sys.databases db
ON mf.database_id = db.database_id
WHERE DB_NAME(mf.database_id) NOT IN ( 'master', 'tempdb', 'distribution', 'msdb', 'model' )
AND mf.type_desc = 'log';
OPEN list;
FETCH NEXT FROM list
INTO @dbname,
@logicalname;
WHILE @@FETCH_STATUS = 0
BEGIN
IF (@ChangetoSimple) = 1
SELECT @simple = 'alter database [' + @dbname + '] set recovery simple';
SET @sql
= 'use [' + @dbname + ']; ' + @simple + ' DBCC shrinkfile (N''' + @logicalname
+ ''',0, truncateonly);
';
PRINT @sql;
FETCH NEXT FROM list
INTO @dbname,
@logicalname;
BEGIN TRY
EXEC (@sql);
PRINT @sql;
END TRY
BEGIN CATCH
SELECT ERROR_NUMBER(),
ERROR_SEVERITY(),
ERROR_MESSAGE();
END CATCH;
END;
CLOSE list;
DEALLOCATE list;