DBLogFix.sql: MSSQL log file fixes

Author: Robert Lawson
Environment: Windows Server, MS SQL Server, PeopleSoft
Description: These are progressive steps to "fix" run-away log files on MS SQL Server database.
Code:

/*
file: DBLogFix.sql

purpose: fix log file too full

create: 13-SEP-2001/Robert Lawson

-- Verify log file set to grow by 5MB, fewer and less chance problem(?)
DBCC OPENTRAN -- open transactions
DBCC LOGINFO  -- log file paging, Lots and any with status=2 will not truncate

-- change to database recovery to simple
SELECT DATABASEPROPERTYEX( 'LSPROD' , 'recovery' )
ALTER DATABASE LSPROD  SET RECOVERY SIMPLE
*/

-- 1.
BACKUP LOG LSPROD WITH TRUNCATE_ONLY

-- 2.
DBCC SHRINKDATABASE(LSPROD, TRUNCATEONLY)

-- 3. This can only be done with all users off
EXEC sp_detach_db 'LSPROD', 'true'

-- 4. Rename/purge the existing log file (otherwise it will just get attached again!)

-- 5. This step will create a new log file (.ldf)
EXEC sp_attach_db @dbname='LSPROD', @filename1='D:\MSSQL.1\MSSQL\Data\LSPROD.mdf'