CODEWITHSUNDEEP
sql-serversql-server-2005sql-agent-joblog-files
Keith Walton
Keith Walton

Reputation: 5364

Why does DBCC SHRINKFILE work inconsistently in a database job?

DBCC SHRINKFILE always works when I run it manually on a log file, even when I get the following message:

'Cannot shrink log file 2 (Claim_Log) because all logical log files are in use.'

When I run it from a job, however, it only shrinks the log about one third of the time. The other times, it just remains large (about 150Gb). There is never any error other than the one listed above. This is the statement that I use:

DBCC SHRINKFILE (N'Claim_log' , 0, TRUNCATEONLY)

I have "Include step output in history" enabled on the job step. Is there something else I can do to get more information on why this isn't working?

Edit: Here is the full message from the log:

'Executed as user: *. Cannot shrink log file 2 (Claim_Log) because all logical
log files are in use. [SQLSTATE 01000] (Message 9008)  DBCC execution completed. 
If DBCC printed error messages, contact your system administrator. [SQLSTATE 01000]
(Message 2528).  The step succeeded.'

I have already tried kicking users out of the db and setting it to single user mode.

Upvotes: 1

Views: 7059

Answers (3)

MrEs
MrEs

Reputation: 715

I recently solved a similar issue, I found that in sys.databases, log_reuse_wait_desc was equal to 'replication'. Apparently this means something to the effect of SQL Server waiting for a replication task to finish before it can reuse the log space.

However replication had never been used on our DB nor on our server. You should be able to clear the state by running 'sp_removedbreplication'; however for me 'sp_removedbreplication' didn't solve the issue. Instead SQL just returned saying that the Database wasn't part of a replication...

I found my answer here:

Basically I had to create a replication, reset all of the replication pointers to Zero; then delete the replication I had just made. i.e.

Execute SP_ReplicationDbOption {DBName},Publish,true,1
GO
Execute sp_repldone @xactid = NULL, @xact_segno = NULL, @numtrans = 0, @time = 0, @reset = 1
GO
DBCC ShrinkFile({LogFileName},0)
GO
Execute SP_ReplicationDbOption {DBName},Publish,false,1
GO

Upvotes: 3

RamaUdaya.K
RamaUdaya.K

Reputation:

Means Currently the Log file is in Use and issue check point where Check point will writes to datfile that was not written to the datafile from transaction log file (Dirty pages). Check is there any current Activity is going on or Not,

Check using for Active Transaction In 2005 SELECT * FROM sys.dm_tran_session_transactions

2000 DBCC LOGINFO

make good Plan =>1.Create maintenace plan For Backing up the log (Made Plan).

Upvotes: 0

rip747
rip747

Reputation: 9455

try issuing the CHECKPOINT command first, then shrinking the logs

taken from BOL (http://msdn.microsoft.com/en-us/library/aa226036(SQL.80).aspx)

Forces all dirty pages for the current database to be written to disk. Dirty pages are data or log pages modified after entered into the buffer cache, but the modifications have not yet been written to disk. For more information about log truncation, see Truncating the Transaction Log.

Upvotes: 2

Related Questions