Why is DBCC SHRINKFILE running inconsistently in a database job?
DBCC SHRINKFILE
always works when I run it manually in the log file, even when I get the following message:
'Cannot shrink log file 2 (Claim_Log) because all logical log files are in use.'
However, when I run it from a job, it only shortens the log by about a third. Other times, it just stays large (around 150GB). There are no errors other than those listed above. This is the statement I am using:
DBCC SHRINKFILE (N'Claim_log' , 0, TRUNCATEONLY)
I have "Include step output in history" that is 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 complete post 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 dumping users from db and setting it to single user mode.
source to share
I recently solved a similar problem, I found that in sys.databases the value of log_reuse_wait_desc is "replication". This appears to indicate that SQL Server is waiting for the replication task to complete before reusing the log space.
However, replication has never been used in our DB or on our server. You must clear the state by running 'sp_removedbreplication'; however for me "sp_removedbreplication" did not solve the problem. Instead, SQL just came back saying the database is not part of replication ...
I found my answer here:
- http://www.sql-server-performance.com/forum/threads/log-file-fails-to-truncate.25410/
- http://blogs.msdn.com/b/sqlserverfaq/archive/2009/06/01/size-of-the-transaction-log-increasing-and-cannot-be-truncated-or-shrinked-due-to- snapshot-replication.aspx
Basically I had to create replication, reset all replication pointers to zero; then remove the replication I just did. 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
source to share
try running CHECKPOINT command and then shorten the logs
taken from BOL ( http://msdn.microsoft.com/en-us/library/aa226036(SQL.80).aspx )
Forces all dirty pages to write the current database to disk. Dirty pages are data or log pages that have changed since being entered into the buffer cache, but the changes have not yet been written to disk. For more information about truncating the log, see Truncating the Transaction Log.
source to share
Values The log file is currently in the Use and checkpoint box, where the checkpoint will write to the data file that was not written to the data file from the transaction log file (Dirty pages). Check if there is current activity or not,
Check usage for active transaction IN 2005 SELECT * FROM sys.dm_tran_session_transactions
2000 DBCC LOGINFO
make a good plan => 1.Create a maintenance plan for log backup (plan made).