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) GO EXEC sp_msforeachtable 'ALTER INDEX ALL ON ? REORGANIZE;'; GO
The result is notable and should be easier transmitted to my local development machine
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;