dbcc shrinkfile

Hi All,

I have a job that has multiple steps. Step 1 rebuilds the indexes, step 2 truncates the transaction log, and step 3 shrinks the transaction log via dbcc shrinkfile command. The job has been running for quite a while without any problems until this past weekend. The job ran successfully but when I looked at the size of the transaction log, it was the same as before the job ran. I have read on BOL that if part of the logical log is in the virtual logs beyond the target_size mark, SQL Server 2000 frees as much space as possible and issues an informational message. My questions is where is this message stored? How can I read it?

Thanks.The informational message is output to the console device, which is the messages window in Query Analyzer or the job step history for SQL Agent jobs.

-PatP|||I checked the job history and it showed that the transaction log shrinking step successed. So that doesn't help me.|||Then my next guess would be that you had open transactions spanning log segments, preventing the log from shrinking. No error message is issued when that happens.

-PatP|||Try the following:

use your_DB
go

checkpoint
go

BACKUP database your_DB TO DISK = 'junk1'
go

BACKUP LOG your_DB TO DISK='junk2'
go

BACKUP database your_DB TO DISK = 'junk3'
go

dbcc shrinkdatabase(your_DB)
go

dbcc shrinkfile(your_DB)
go|||Look into
1. backup log [databasename] with no_log
and
2. dump tran [databasename] with no_log

I usually use number 2 if the dbcc shrinkfile(dbname,1) doesn't work.

Good luck