Quality of Data

All of us have wanted to get rid of the SQL Server log file at some stage. The natural way of shrinking the log file is to back it up and keep it in a safe place. Log file keeps transaction data and is essential for temporal disaster recovery.

However, it is a common request to get rid of the log file because it takes space and some people don’t need the log file at all. Beware that there is only ONE place and ONE situation that log file is a waste of space and that is Dev Environment.

SQL Server 2008 has removed the possibility of shrinking the log file using DBCC SHRINKFILE or BACKUP SHRINKONLY. But if you want to get rid of the log file in your dev environment, use the following script:

Note: Before running this script, in SQL Server Management studio, select Query -> SQL CMD mode.

:Setvar DbName **Your Database Here**

PRINT 'Droping log file for $(DbName)'
GO
EXEC sp_configure 'show advanced options', 1;
GO
RECONFIGURE;
GO
EXEC sp_configure 'xp_cmdshell', 1
GO
RECONFIGURE
GO
USE Master;
GO
DECLARE @logPath VARCHAR(400)
DECLARE @rowsPath VARCHAR(400)
DECLARE @logFile VARCHAR(400)
SELECT @rowsPath = physical_name FROM  [$(DbName)].sys.database_files AS df where type_desc='ROWS'
SELECT @logPath = physical_name FROM  [$(DbName)].sys.database_files AS df WHERE type_desc = 'LOG'
SELECT @logFile = name FROM [$(DbName)].sys.database_files AS  df WHERE type_desc = 'LOG'
PRINT 'Renaming the log file (' + @logPath + '). You can delete it later'
ALTER DATABASE [$(DbName)] SET  SINGLE_USER WITH ROLLBACK IMMEDIATE
EXEC master.dbo.sp_detach_db @dbname = '$(DbName)', @skipchecks = 'false'
DECLARE @CMD VARCHAR(400) = 'ren "' + @logPath + '" "' + @logFile +  REPLACE(CONVERT(VARCHAR(25), GETDATE(), 113), ':', '') + '"'
PRINT @CMD
EXEC master..xp_cmdshell @CMD
DECLARE @attachSql NVARCHAR(MAX) = N'CREATE DATABASE [$(DbName)] ON ( FILENAME = ''' + @rowsPath + ''' ) FOR ATTACH '
EXECUTE (@attachSql)
GO