SQL Server Performance

Maintenance Shrink fails job

Discussion in 'SQL Server 2005 General DBA Questions' started by rekri, Oct 22, 2007.

  1. rekri New Member

    I have maint. set up to run every saturday night. The scheduled job is like this:
    1.Check DB Integrity task
    2.Reorganize Index task
    3.Update statistics task
    4.Shrink DB task.
    Now the shrink task has started to fail with this error msg:
    Executing the query "DBCC SHRINKDATABASE(N'My_Database', 10, TRUNCATEONLY)
    " failed with the following error: "Backup and file manipulation operations (such as ALTER DATABASE ADD FILE) on a database must be serialized. Reissue the statement after the current backup or file manipulation operation is completed.
    Backup and file manipulation operations (such as ALTER DATABASE ADD FILE) on a database must be serialized. Reissue the statement after the current backup or file manipulation operation is completed.
    ". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.

    The job history tag shows this msg on the job:
    Started: 23:49:11 Progress: 2007-10-21 23:50:28.61 Source: {F5AAEB7A-8452-46C8-A3DA-59D9C8A9EECF} Executing query "DECLARE @Guid UNIQUEIDENTIFIER EXECUTE msdb..sp".: 100% complete End Progress Progress: 2007-10-21 23:50:29.58 Source: Check Database Integrity Task Executing query "USE [My_Database] ".: 100% complete End Progress Progress: 2007-10-21 23:50:42.11 Source: Check Database Integrity Task Executing query "DBCC CHECKDB WITH NO_INFOMSGS ".: 100% complete End Progress Progress: 2007-10-21 23:51:26.59 Source: Reorganize Index Task Executing query "USE [My_Database] ".: 0% complete End Progress Progress: 2007-10-21 23:51:28.83 Source: Reorganize Index Task Executing query "ALTER INDEX [Index_Name] ON [dbo].[My_database] R".: 1% complete End Progress Progress: ... The package execution fa... The step failed.
    Can anyone explain to me what this means and why the scheduled shrink maintenance fails?
    Remi
  2. satya Moderator

    Most likely you already have another backup operation already in progress. You must wait until it runs to completetion (or kill it) before you can backup your log.

  3. rekri New Member

    Many thanks for reply!
    Well, I have backup routines that runs every 15 minutes on this database. So as I understand it.. it will be almost impossible to have this maintenance run successfully without disabling backup?
    Remi
  4. satya Moderator

    Yes, both backup and DBCC clash in between with such issues.
  5. rekri New Member

    Ok, I understand. Thank you so much for your help!
    -Remi

Share This Page