David's Weblog

Recently, I was asked to write a stored procedure to shrink the SQL Server transaction log.

Below is the very simple code to do this:

USE MyDatabaseServerInstance
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE Shrink_Log
AS
BEGIN
	
	SET NOCOUNT ON
	
	ALTER DATABASE MyDatabase SET RECOVERY SIMPLE
	
	-- Shrink the truncated log file to 1000 MB.
	DBCC SHRINKFILE (MyDatabase_log, 1000)

	ALTER DATABASE MyDatabase SET RECOVERY FULL

END
GO

I do not recommend doing this regularly because it breaks the transaction log chain and you won’t be able to reconstruct your database. Be careful. Until you do a backup, the transaction log will be in Pseudo-FULL mode. Only after doing another backup will your transaction log be back in FULL RECOVERY mode.

For more information you can go here

Enjoy!

I was troubleshooting a pesky SQL Server 2005 problem this week.  I kept seeing “Failure Audit” in the Windows Application Event Viewer.  It looked like this: