SQL Server Performance Forum – Threads Archive

SQL DB Maintenance Fails on Server 2003 Cluster

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.

Check the following :
– No issues for space on the hard disk.
– Privilges for the SQL services account used. KBAhttp://support.microsoft.com/default.aspx?scid=kb;en-us;288577 to troubleshoot the maintenance plan. HTH Satya SKJ
Moderator
http://www.SQL-Server-Performance.Com/forum
This posting is provided â€œAS ISâ€ with no rights for the sake of knowledge sharing.

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

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.

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

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
[email protected] When life gives you a lemon, fire the DBA.

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.

Thanks for letting us know the fix. I have never run across this before. —————————–
Brad M. McGehee, MVP
Webmaster
SQL-Server-Performance.Com

It’s always nice when people tell us how they resolved the issues. <img src=’/community/emoticons/emotion-1.gif’ alt=’

‘ /> MeanOldDBA [email protected] When life gives you a lemon, fire the DBA.

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.

]]>