SQL Server Performance

SQL DB Maintenance Fails on Server 2003 Cluster

Discussion in 'SQL Server Clustering' started by roysha, Aug 17, 2004.

  1. roysha New Member

    I have a 3-node MNS cluster running Server 2003 and SQL 2000.
    SQL 2000 is set up and working to a point.
    When I schedule Jobs sometimes they fail and sometime they succeed.
    I have narrowed it down by running the jobs every 1 to 5 min.
    If I have a DB the size of 5 Meg the Jobs complete successfully most of the time, about every 4 to 5 hours I get 1 failure.
    On larger databases, approx. 30 to 40 Meg, it sporadically fails.
    On databases larger than 40 meg it fails continually.
    I have also watched the folder were the backup is to be placed and I see the file created with a size of 0Kb and then it disappears.
    I have found a couple of knowledgebase articles but none seem to fit.

    In the Job History I get the following.
    Executed as user: (DomainUser). sqlmaint.exe failed. [SQLSTATE 42000] (Error 22029). The step failed.

    In the Maintenance Plan History I get one of 2 things
    1. Primary
    [Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 3201: [Microsoft][ODBC SQL Server Driver][SQL Server]Cannot open backup device 'T:MSSQL$TEST1BACKUPInventory2Inventory2_tlog_200408171140.TRN'. Device error or device off-line. See the SQL Server error log for more details.
    [Microsoft][ODBC SQL Server Driver][SQL Server]VERIFY DATABASE is terminating abnormally.

    2. Secondary
    [Microsoft SQL-DMO (ODBC SQLState: 01000)] Error 4035: [Microsoft][ODBC SQL Server Driver][Named Pipes]ConnectionRead (WrapperRead()).
    [Microsoft][ODBC SQL Server Driver][Named Pipes]General network error. Check your network documentation.
    [Microsoft][ODBC SQL Server Driver][SQL Server]Processed 2408 pages for database 'Inventory2', file 'Inventory2_dat' on file 1.

  2. satya Moderator

  3. bradmcgehee New Member

    How many instances of SQL Server 2000 are running on this cluster?
    What kind of shared storage do you have?
    Where are the disk backups being saved to? The shared array? Another disk?
    Is this a new cluster?
    Has it every worked before?
    Is the cluster service account a domain account? Is it a local admin on each node?
    Is the SQL Server service account a domain account? Is it a local admin on each node?
    Who owns the jobs that are failing?

    -----------------------------
    Brad M. McGehee, MVP
    Webmaster
    SQL-Server-Performance.Com
  4. roysha New Member

    bradmcgehee
    How many instances of SQL Server 2000 are running on this cluster?
    ---- Curently 1 instance but I will have 2 to 4 Instances when finished.

    What kind of shared storage do you have?
    ---- It is a drive on the SAN. I am working with the SAN vendor on this issue as well
    ---- and they do not see that the SAN is an issue. Of course that does not mean that it
    ---- is not.

    Where are the disk backups being saved to?The shared array?Another disk?
    ---- They are being backed up to the shared disk from the SAN

    Is this a new cluster?
    ---- Yes

    Has it every worked before?
    ---- Backups have never worked succesfully. Everything else seems to work just fine.

    Is the cluster service account a domain account? Is it a local admin on each node?
    ---- The cluster service account is a domain account and is part of the Administrators
    ---- Group on each box.

    Who owns the jobs that are failing?
    ---- At first I had the Cluster Service account as the Owner. Having thought that might
    ---- be an issue I created New jobs and made sure the sa account within SQL was the
    ---- owner.
    ----(I installed SQL in a mixed authentication mode.)

    --------------------------------------------------------------------------------------

    satya
    No issues for space on the hard disk.
    ---- There is 30 gig free

    Privilges for the SQL services account used.
    ---- The SQL service account is a Domain User and member of the Administrators Group on
    ---- each box. If this were an issue I would expect the jobs to always fail.
  5. bradmcgehee New Member

    What do you mean that you are backing up to the shared disk from the SAN?

    -----------------------------
    Brad M. McGehee, MVP
    Webmaster
    SQL-Server-Performance.Com
  6. derrickleggett New Member

    Shared disk meaning a LUN presented to the host from the SAN, or shared disk meaning a SAN LUN registered as a shared resource of the cluster????

    MeanOldDBA
    derrickleggett@hotmail.com

    When life gives you a lemon, fire the DBA.
  7. roysha New Member

    I wasn't thinking correctly when I said "They are being backed up to the shared disk from the SAN". I meant that I was backing up to a SAN LUN registered as a shared resource of the cluster.

    I found a resolution to the issue
    In Windows 2000 Advanced Server (or any server version for that matter) the MSDTC Protocol is allowed to communicate over the network by default.

    As part of MS's Secure by Design Concept it is not allowed to communicate over the network by default.

    I followed KB Article 329332 (Just as a note: I was not getting the same error's that are in the Article.)

    After I enable Network DTC Access the error's went away.

    I am still unclear as to why this resolved the issue. I am also unclear as to why the size of the database mattered. I can only speculate.
  8. bradmcgehee New Member

    Thanks for letting us know the fix. I have never run across this before.

    -----------------------------
    Brad M. McGehee, MVP
    Webmaster
    SQL-Server-Performance.Com
  9. derrickleggett New Member

    It's always nice when people tell us how they resolved the issues. <img src='/community/emoticons/emotion-1.gif' alt=':)' /><br /><br /><br />MeanOldDBA<br />derrickleggett@hotmail.com<br /><br />When life gives you a lemon, fire the DBA.
  10. Argyle New Member

    Well I can't see how that solved the issue. MSDTC has nothing to do with backups. Then you would not be able to do backups if this services was stopped or disabled and that is just not the case.

Share This Page