I have a test environment for an application. From time to time I take the database to my local development environment.

A smaller database log means less network latency and a faster local reproduction scenario.

After a while of pumping data into a MSSQL Server instance the log can grow a bit – even if there is a lot of free space left.

image

Shrinking is NOT GOOD at all, but I don’t have 50gigs of space left to pull up that database on my local machine. So I will shrink to gain space and reorganize the indices to fight fragmentation.

DECLARE @DbName VARCHAR(128);  
SET @DbName = DB_NAME();  
DECLARE @LogName VARCHAR(128);  
SET @LogName = @DbName + '_Log';   
DECLARE @AlterSql NVARCHAR(512);  
SET @AlterSql = 'ALTER DATABASE ' + @DbName + ' SET RECOVERY SIMPLE;';   
EXEC sys.sp_executesql @AlterSql;   
DBCC SHRINKFILE(@LogName, 1, TRUNCATEONLY)  
GO   
EXEC sp_msforeachtable 'ALTER INDEX ALL ON ? REORGANIZE;';  
GO

The result is notable and should be easier transmitted to my local development machine

image

Another option is to set the recovery model to simple. But that cuts a few features:

  • Log shipping AlwaysOn or Database mirroring Media recovery without data loss

Point-in-time restores

SELECT
    name,
    recovery_model_desc
FROM
    sys.databases
WHERE
    name = 'mydatabasename';

GO

USE master;
ALTER DATABASE [mydatabasename] SET RECOVERY SIMPLE;