none
Backup & Shrink Operation RRS feed

  • Question

  • Hi All,

    We have a server on which we have a maintenance job that performs index reorganize, shrink DB, integrity check, cleanup, etc (on Sunday).

    The job was failing while performing the shrink task with below error:

    Executing the query "DBCC SHRINKDATABASE(N'xxxxx', 20, TRUNCATE..." 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.
    DBCC SHRINKDATABASE: File ID 1 of database ID 11 was skipped because the file does not have enough free space to reclaim.".

    Log backup was running every 15 min on the server. And we split the log backup schedule on Sunday accordingly that I doesn't conflict with the maintenance job. After doing this, the job was running successfully for few weeks.

    Now again the job is failing with same error. And we see it conflicting the log backup job. Earlier the maintenance task was taking around 5-6 hours to complete & we adjusted log backup schedule on Sunday such that's it doesn't run during that time.

    In last 2 weeks, the maintenance job is taking around 11-12 hours to complete (index reorganize job is taking 10 hrs to complete (earlier it took only 4 hrs)), so again the shrink task is conflicting with log backup job & failing.

    How to fix this?


    Monday, October 21, 2019 2:13 PM

All replies

  • Do you have a valid reason to shrinkdatabase as a job step?

    Are you shrinking your database data file or log file (or both)?

    Check your maintenance task to avoid shrink database operation all together. (you can search to find out why you should avoid this operation). 

    Monday, October 21, 2019 2:24 PM
    Moderator
  • Hi 

    Can you look if that is really causing due to back up issue.

    select database_name, type, backup_start_date, backup_finish_date
    
    from msdb.dbo.backupset
    
    order by database_name, type, backup_start_date, backup_finish_date
    
    go

    A KB from MS for the same is attached for reference.

    https://support.microsoft.com/en-in/help/2979636/sql-server-generates-a-3023-message-when-backup-and-file-operations-ar

    Check the intervals.

    Hope this is helpful !!

    Thank you


    If this post helps to resolve your issue, please click the "Mark as Answer" of that post and/or click Answered "Vote as helpful" button of that post. By marking a post as Answered and/or Helpful, you help others find the answer faster.

    Monday, October 21, 2019 2:39 PM
  • Is this a SQL Server Maintenance Plan?  What SQL version?


    22 years of database experience, most with SQL Server. Please 'Mark as answered' those posts that helped you.

    Monday, October 21, 2019 2:58 PM
  • Yes. Its as per app suggestion.

    Shrinking log file with truncate only option.

    Monday, October 21, 2019 5:07 PM
  • Yes. Log backup has happened at the same time when shrink was performed.
    Monday, October 21, 2019 5:08 PM
  • Yes. sql version is 2008 R2.
    Monday, October 21, 2019 5:08 PM
  • Just don't do it.

    Assign your log file to a proper size and skip this shrink log file operation in your maintenance job. 

    Monday, October 21, 2019 5:09 PM
    Moderator
  • Yes. sql version is 2008 R2.

    SQL Server Maintenance Plan, or some other method?

    If its a maintenance plan, the indexes are rebuilding/re-organizing everything, even those with no fragmentation.

    A better solution is needed and can be found at http://ola.hallengren.com

    Also, database file shrinks are rarely a good idea, and scheduled is never a good idea...as others have already said


    22 years of database experience, most with SQL Server. Please 'Mark as answered' those posts that helped you.

    Monday, October 21, 2019 5:34 PM
  • In last 2 weeks, the maintenance job is taking around 11-12 hours to complete (index reorganize job is taking 10 hrs to complete (earlier it took only 4 hrs)), so again the shrink task is conflicting with log backup job & failing.

    How to fix this?

    The fix is very simple. Stop shrinking you database on a regular basis. Shrinking is an exceptional operation. You would shrink a data file, if you have deleted a lot of data, and you know that you will not fill it up again. For instance, you took a copy for Q/A testing, but dropped archive tables you don't need Q/A. Likewise, you can need to shrink the look if someone left an SSMS window with open transaction while being a way for week of holiday and the log just exploded.

    But shrinking a on a regular basis - that's a total no-no!


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

    Monday, October 21, 2019 10:18 PM