This is somewhat of a quick or lazy way to truncate and shrink the transaction log because all you have to do is replace the database name and change your size requirements. It’s not the cleanest or best approach I admit, but sometimes I find myself on a development or staging server and I need some space quickly. There is no requirement on the server to preserve historic data, and since it is not highly transactional I don’t care what is on the log file to begin with. This happens often when running data aggregations in our DataMart environment where log files can grow up to 100GB+ with staging data that is not useful after the process is complete.
Please remember to use this at your own risk and I would recommend not using it at all if you do not fully understand the implications of truncating and shrinking log files. Remember, shrinking the transaction log can cause performance issues so I would not recommend doing this on a production server.
Here is the code, once you paste it into Management Studio remember to change the [DatabaseName] text to the database you would like to do maintenance on; there are three places.
/*Replace [DatabaseName] with the name of your database*/
DECLARE @logfilename varchar(max)
DECLARE @size INT
---Change the size to the desired size you would like
---Size is in MB
SET @size = 1024
SELECT @logfilename = name
WHERE TYPE =1
/*The actual shrinking will happen below*/
/*Setting to SIMPLE Recovery mode guarantees that the log will be truncated so that you can shrink it*/
ALTER DATABASE [DatabaseName] SET RECOVERY SIMPLE
DBCC SHRINKFILE (@logfilename,@size)
/*Only use this code if your database was set to FULL Recovery Model to start with or if you would like to change it to that*/
ALTER DATABASE [DatabaseName] SET RECOVERY FULL