Log not shrinking

21 Jul 2006 7:25 PM

Scott

I have an offline database that gets occasional updates maybe once or
twice a week. The attached script runs every night and each morning I
check it for successful completion. Today the log showed a size of 12
gig (All my other logs after shrinking are around 1 meg). This is
after successful completion of my script. I ran the same script
several times with the following results.

1st run:        Log dropped to 12 gig.
2nd run:                    Log dropped to 6 gig.
3rd run:        Log dropped to 41 meg.
4th run:        Log dropped to 37 meg.
5th run:        Log dropped to 33 meg.

Understand I am the only one that updates this database and no one else
could have been on adding or changing it. What am I not understanding?

--
-- Backup and Truncate database!
--
DBCC SHRINKDATABASE (Client, 0, TRUNCATEONLY)

-- Backup Log
BACKUP LOG Client
    TO DISK = '\\POWEREDGER\SQLBackups\poweredger\CLIENT1_Data.Trn'
    WITH INIT,
    NOUNLOAD,
    NAME = 'Client_Log',
    SKIP,
    STATS = 10,
    NOFORMAT

-- Backup Database
BACKUP DATABASE Client
    TO DISK = '\\POWEREDGER\SQLBackups\poweredger\CLIENT1_Data.BAK'
    WITH INIT,
    NOUNLOAD,
    NAME = 'Client_Data',
    SKIP,
    STATS = 10,
    NOFORMAT

-- Shrink Log
DBCC SHRINKFILE(CLIENT1_Data, truncateonly)
DBCC SHRINKFILE(CLIENT1_Log, truncateonly)

DBCC SHRINKDATABASE (Client, 10)

Thanks

Scott

21 Jul 2006 10:28 PM

Erland Sommarskog

Scott (biq***@yahoo.com) writes:
Show quote

> I have an offline database that gets occasional updates maybe once or
> twice a week. The attached script runs every night and each morning I
> check it for successful completion. Today the log showed a size of 12
> gig (All my other logs after shrinking are around 1 meg). This is
> after successful completion of my script. I ran the same script
> several times with the following results.
>
> 1st run:          Log dropped to 12 gig.
> 2nd run:                     Log dropped to 6 gig.
> 3rd run:          Log dropped to 41 meg.
> 4th run:          Log dropped to 37 meg.
> 5th run:          Log dropped to 33 meg.
>
> Understand I am the only one that updates this database and no one else
> could have been on adding or changing it. What am I not understanding?

This may give some hints:

http://www.karaszi.com/SQLServer/info_dont_shrink.asp

--
Erland Sommarskog, SQL Server MVP, esq***@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx