Tuesday, February 28, 2012

Shrink Database fails with Error “Backup, file manipulation operations”

In some cases users may get below error while trying to shrink their databases manually or using SQLServer management studio.

Executing the query "DBCC SHRINKDATABASE(N'OperationsManagerDW', 10, TR..." failed with the following error: "Backup, file manipulation operations (such as ALTER DATABASE ADD FILE) and encryption changes on a database must be serialized.

Possible reason:
The backup is running when user are trying to perform the database shrink. Please note that below operations may not be performed at the same time as a backup operation

• File management operations such as the ALTER DATABASE statement with either ADD FILE or REMOVE FILE clauses.
• The file truncation phase of shrink database or shrink file.

Please note that if backup is running and one of the above operations is attempted, then operation fails immediately. When user is trying to add or remove a file and if backups is started then backup will wait for a timeout period, then fail.

If user is trying to shrink database or file while a backup is running, the shrink stops without truncating the file. If backup started after shrink database operation then backup waits for shrink operation to complete.

Action:
Reissue the statement after the current backup or file manipulation operation is completed.

Regards,
Satishbabu Gunukula
http://sqlserver-expert.com

4 comments:

  1. Resources like the one you mentioned here will be very useful to me! I will post a link to this page on my blog. I am sure my

    visitors will find that very useful.
    http://www.sqlservermasters.com/

    ReplyDelete
    Replies
    1. You can share the link, but do not copy the content as it is Copyright Protected.

      Regards,
      Satish

      Delete
  2. i am also facing this issue in my server while taking the backup and i use the folwing methood ,open one query windows and type
    sp_who2 'active'

    and kill the running process

    ReplyDelete
  3. Great stuff thanks for sharing ! The Shrinking operation is beginning to clear up. As i found another helpful post for the same see here: http://www.sqlserverlogexplorer.com/how-to-clear-transaction-with-dbcc-shrinkfile/

    ReplyDelete