Daniel Fisher (lennybacon.com)

SOA, DATA & THE WEB

MSSQL Log Size

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;

MsSQL, Project

« Sitzungslos/Statuslos MSSQL Backup Size »

Comments

Write a comment