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.
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)
EXEC sp_msforeachtable 'ALTER INDEX ALL ON ? REORGANIZE;';
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
SELECT name, recovery_model_desc FROM sys.databases WHERE name = 'mydatabasename'; GO USE master ; ALTER DATABASE [mydatabasename] SET RECOVERY SIMPLE;