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.
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