Database Journal
MS SQL Oracle DB2 Access MySQL PostgreSQL Sybase PHP SQL Etc SQL Scripts & Samples Links Database Forum

» Database Journal Home
» Database Articles
» Database Tutorials
MS SQL
Oracle
DB2
MS Access
MySQL
» RESOURCES
Database Tools
SQL Scripts & Samples
Links
» Database Forum
» Sitemap
Free Newsletters:
DatabaseDaily  
News Via RSS Feed


follow us on Twitter
Database Journal |DBA Support |SQLCourse |SQLCourse2
 

Featured Database Articles

SQL Scripts & Samples

Posted Dec 9, 2002

Shrink Log File Script

By DatabaseJournal.com Staff


This script from Eli Leiba will attempt to shrink the log file to a specific size.


Create PROCEDURE Sp_ShrinkLog 
              (@LogF  varchar(200),   @NSize INT) 
AS
--BY ELI L.
-- DATE 08/2002
-- DESC: attempt to shrink the log file to a specific size
--    gets logical file name and attempt size

SET NOCOUNT ON
DECLARE @MaxMin INT, @LogicalFileName sysname,@origs int
DECLARE @Cnt INT,@StartTime DATETIME, @Truncl VARCHAR(255)

SELECT @LogicalFileName = @LogF
SELECT @MaxMin = 2

SELECT @origs = size 
 FROM sysfiles
 WHERE name = @LogicalFileName

SELECT 'Original Size OF ' + db_name() + ' LOG IS ' + 
CONVERT(VARCHAR(30),@origs) + ' 8K pages or ' + 
CONVERT(VARCHAR(30),(@origs*8/1024)) + 'MB'
 FROM sysfiles
 WHERE name = @LogicalFileName

CREATE TABLE DumpTrn  (DumCol char (8000) NOT null)

SELECT @StartTime = GETDATE(),
                @Truncl = 'BACKUP LOG ['+ db_name() + '] WITH TRUNCATE_ONLY'

DBCC SHRINKFILE (@LogicalFileName, @NSize)
EXEC (@Truncl)

WHILE @MaxMin > DATEDIFF (mi, @StartTime, GETDATE()) 
 AND @origs =    (SELECT size FROM sysfiles WHERE name = @LogicalFileName) 
 AND (@origs * 8 /1024) > @NSize 
                 
  BEGIN 

    set @Cnt = 0

    WHILE ((@Cnt < @origs / 16) AND (@Cnt < 50000))

       BEGIN -- UPDATE
        INSERT DumpTrn VALUES ('Fill Log') -- Because it IS a char field it inserts 8000 bytes.
        DELETE DumpTrn
        set  @Cnt = @Cnt + 1
     END-- UPDATE
    EXEC (@Truncl) -- See IF a trunc OF the log shrinks it.
 END-- OUTER loop

SELECT 'Final Size OF ' + db_name() + ' LOG IS ' +
CONVERT(VARCHAR(30),size) + ' 8K pages OR ' + 
CONVERT(VARCHAR(30),(size*8/1024)) + 'MB'
 FROM sysfiles 
 WHERE name = @LogicalFileName

DROP TABLE DumpTrn

PRINT '*** Perform a FULL DATABASE BACKUP ***'

SET NOCOUNT OFF

-- Example How to Use
use northwind
go
exec sp_shrinkLog 'Northwind_Log',2,2


» See All Articles by Columnist Eli Leiba




SQL Scripts & Samples Archives

Comment and Contribute

 


(Maximum characters: 1200). You have characters left.

 

 




Latest Forum Threads
SQL Scripts & Samples Forum
Topic By Replies Updated
sql help!! add 1 August 22nd, 11:58 AM
How To Combine These 2 Queries into 1 Query ? tarek_land 1 June 7th, 08:37 AM
solving query svibuk 1 February 3rd, 06:08 AM
converting from a character string to uniqueidentifier saturnius 4 January 4th, 05:56 AM