Results 1 to 3 of 3
  1. #1
    Join Date
    Jul 2010
    Posts
    2

    Red face Unanswered: What 's haapend with MA Plan?

    Maintenance Plan: S_RebuildAndShrink
    Duration: 02:35:46
    Status: Warning: One or more tasks failed..
    Details:
    Rebuild Index Task (D-DB-01)
    Task start: 2010-07-11T17:33:22.
    Task end: 2010-07-11T20:06:56.
    Success

    Shrink Database & Transaction Log (D-DB-01)
    Execute TSQL on Local server connection
    Execution time out: 0
    Task start: 2010-07-11T20:06:56.
    Task end: 2010-07-11T20:07:25.
    Failed-1073548784) Executing the query "use DM_S_base
    dbcc shrinkdatabase('DM_S_base')
    dbcc shrinkfile('DM_S_base',1)
    dbcc shrinkfile('DM_S_log',1)
    backup log DM_S_base with truncate_only
    dbcc shrinkfile('DM_S_log',1)
    " failed with the following error: "A severe error occurred on the current command. The results, if any, should be discarded.
    Changed database context to 'DM_S_base'.
    File ID 1 of database ID 7 cannot be shrunk as it is either being shrunk by another process or is empty.
    DBCC execution completed. If DBCC printed error messages, contact your system administrator.
    File ID 1 of database ID 7 cannot be shrunk as it is either being shrunk by another process or is empty.
    Cannot shrink log file 2 (DM_S_log) because total number of logical log files cannot be fewer than 2.
    DBCC execution completed. If DBCC printed error messages, contact your system administrator.
    Cannot shrink log file 2 (DM_S_log) because total number of logical log files cannot be fewer than 2.
    DBCC execution completed. If DBCC printed error messages, contact your system administrator.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.

    ========================
    Normally, we backup by stop servers and copy files. We never use backup command in SQL.

    I have question about this problem come from dbcc shrinkdatabase or not.Because I heard shrink database , need full backup first.
    Last edited by OOH; 07-18-10 at 04:48.

  2. #2
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    I'm going to assume that you are running a current version of Microsoft SQL Server. If you are running older versions, the following discussion may or may not apply.

    The problem is that your script is attempting to do a "brute force" maintenance on what I presume is a large database because it took almost 150 minutes to rebuild the indicies.

    The method that you are using worked well 20 years ago, and was tolerable 15 years ago. It was questionable ten years ago, and became dangerous five years ago. You really need to start using a different methodology for backups and database maintenance!

    I'd execute the command:
    Code:
    DBCC CHECKDB (DM_S_Base)
    to see if your script has done any damage to the database itself.

    If the database is damaged, you will need an experienced Database Administrator with data recovery skills to fix it. If the database is not damaged, you can do a SQL Server backup, then proceed very, very carefully to maintain the database.

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  3. #3
    Join Date
    Jan 2009
    Location
    United Kingdom
    Posts
    77
    Unless you are reclaiming space - based on a "permmanent" delete of data - it's worth reconsidering your usage of shrinking data files . Consider a statistics maintenance and reindexing based on some solid analysis

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •