There have been some changes since sql server 2005 and the backup log with truncate no longer works. So here is a little stored procedure to shrink the current database log file for mssql 2008. I also created this in such a way so that it will run for any database.

CREATE PROCEDURE ShrinkLog
AS
BEGIN
        DECLARE @DBName nvarchar(MAX)
        DECLARE @DBLog nvarchar(MAX)
        DECLARE @SQL nvarchar(MAX)

        SELECT @DBName = DB_NAME()

        SELECT @DBLog = name
                FROM sys.master_files
                WHERE database_id = DB_ID()
                        AND type = 1

        SELECT @SQL = 'ALTER DATABASE ' + @DBName + ' SET RECOVERY SIMPLE'
        EXEC sp_executesql @SQL

        SELECT @SQL = 'DBCC SHRINKFILE(' + @DBLog + ')'
        EXEC sp_executesql @SQL

        SELECT @SQL = 'ALTER DATABASE ' + @DBName + ' SET RECOVERY FULL'
        EXEC sp_executesql @SQL

END
GO

You can use the following sql to run it

EXEC ShrinkLog


Did You find this page useful?

Yes No



Last Modified: 18 February 2017

Releated Posts


2012-08-07 - MSSQL - Finding the database restore history
2011-09-24 - MSSQL - Checking Uptime
2011-08-11 - MSSQL - Saving memory by using stored procedures
2011-08-09 - MSSQL - Last Backup time and size
2011-06-24 - MSSQL - Extracting part of an email address
2011-06-15 - MSSQL - Bulk Deleting rows with backup
2011-06-07 - MSSQL - DBCC Check All Databases
2011-06-03 - MSSQL - Who locks what
2011-05-26 - MSSQL - RandomString Function
2011-05-22 - MSSQL - Removing the aspnet membership database
2011-05-19 - MSSQL - ShrinkLog
2011-05-16 - MSSQL - Enum
2011-05-05 - MSSQL - Log Sizes
2011-03-01 - MSSQL - Kill connections by host
2011-02-23 - C Sharp / MSSQL Get inserted value of NEWSEQUENTIALID()
2011-02-22 - MSSQL - Kill connections by username
2011-02-20 - MSSQL - All temp tables are global
2011-02-20 - MSSQL - Unsigned int
2011-02-17 - MSSQL - Convert IP To big int
2011-02-05 - MSSQL - Levenshtein
2011-02-02 - MSSQL - Kill connections to a database
2011-02-01 - MSSQL - Convert unix timestamp to date time
2011-01-30 - MSSQL - Enabling CLR Functions
2011-01-30 - MSSQL - RegEx Support
2011-01-30 - MSSQL - Adding MD5 checksum support
2011-01-30 - MSSQL - Adding SHASum support
2011-01-29 - MSSQL - TRIM Function