Sql-server – Occasional error shrinking transaction log files

maintenancesql serversql-server-2008

I have a transaction backup job which runs every hour.

First it runs a backup of the database log to another drive, then it shrinks the log and deletes old .trn files.

Maintenance job

The middle task shrinks the log fle, using this code:

USE myDB
DBCC SHRINKFILE('myDB_LOG', 0, TRUNCATEONLY)

However, sometimes I received the following error.

Description: Executing the query "USE myDB DBCC
SHRINKFILE('myDB_LOG'…" failed with the following error: "Backup,
file manipulation operations (such as ALTER DATABASE ADD FILE) and
encryption changes on a database must be serialized. Reissue the
statement after the current backup or file manipulation operation is
completed. Changed database context to 'myDB'.". Possible
failure reasons: Problems with the query, "ResultSet" property not set
correctly, parameters not set correctly, or connection not established
correctly. End Error DTExec: The package execution returned
DTSER_FAILURE (1). Started: 22:00:19 Finished: 22:04:17 Elapsed:
238.26 seconds. The package execution failed. The step failed.

Why is this happening?

Is there a better way to backup and shrink the database log?

Best Answer

There is NO NEED to run shrinkfile command after taking transaction log backup. What is the need of shrinking a log file which would eventually grow again? This is a blunder in my opinion considering the fact that you are doing it every day.

Whenever the log file grows, transactions have to wait for this event to complete so SQL server becomes still until this growth happens and considering the fact that instant file initialization is not there for log files it takes time for these growth events to complete, in turn hampering performance.

Regarding error message

Backup, file manipulation operations (such as ALTER DATABASE ADD FILE) and encryption changes on a database must be serialized. Reissue the statement after the current backup or file manipulation operation is completed

This means that you were trying to run dbcc shrinfile command when some backup operation was running on the database. It can be any backup full,differential and transaction log. All this message is saying that please try running shrikfile command when backup operation has completed. Now you have to find what backup was running at that time. See errrlog or msdb job history you could find more details.

If you refer to BOL

Operations that cannot run during a database or transaction log backup include:

• File management operations such as the ALTER DATABASE statement with either the ADD FILE or REMOVE FILE options.

• Shrink database or shrink file operations. This includes auto-shrink operations.

Related Question