SQL Server Performance Forum – Threads Archive

Backup

We are having few issues with backups, we have sql 2005 with IBM X366 cluster and Quad zeon 3.0 ghz processor WITH 16 GB ram. Also we have san discs attached to the server. ALL of sudden, out of blue our backups are failing very frequently. Also we have SQL server 2005, no sp.
Our initial backup was on local san discs and that failed quiet frequently with below mentioned error messages. There are no san or discs errors or any errors reported in sql server logs or windows event viewer. even checked for cluster and san logs and no errors are reported. we have done testing on san and cluster side and as such nothing show that there are any issues on cluster or san side. we have any we have even traced for network, but couldn’t find any errors. Many time it works as well, so really can’t figure out why its failing with this error message. Sql Message ID10004
Operator Emailed
Operator Net sent
Operator Paged
Retries Attempted0 Message
Executed as user: PATCHSQLACCOUNT. TCP Provider: The specified network name is no longer available. [SQLSTATE 08S01] (Error 64) Communication link failure [SQLSTATE 08S01] (Error 64). The step failed.
Then we tried to backup on our backup server and even on backup server, it failed some time with below mentioned error message. I know this error shows "server storage issues" however this is not at all relevant either because this database size is 163 gb and we have more than 700gb of free space on this server all the time. Not sure if this refer to sql server buffer memory or what….no idea?? Message
Executed as user: PATCHSQLACCOUNT. 10 percent processed. [SQLSTATE 01000] (Message 3211) 20 percent processed. [SQLSTATE 01000] (Message 3211) 30 percent processed. [SQLSTATE 01000] (Message 3211) 40 percent processed. [SQLSTATE 01000] (Message 3211) 50 percent processed. [SQLSTATE 01000] (Message 3211) 60 percent processed. [SQLSTATE 01000] (Message 3211) 70 percent processed. [SQLSTATE 01000] (Message 3211) 80 percent processed. [SQLSTATE 01000] (Message 3211) Write on "Retaildb_DSSU(\retaildb
etaildb.bak)" failed: 1130(Not enough server storage is available to process this command.) [SQLSTATE 42000] (Error 3202) BACKUP DATABASE is terminating abnormally. [SQLSTATE 42000] (Error 3013)

Error "Not enough server storage is available to process this command." is I believe it is related to VAS (MemToLeave memory) issue not the disk space issue. http://www.sql-server-performance.com/forum/topic.asp?TOPIC_ID=19594&SearchTerms=joe,chang,vas
MohammedU.
Moderator
SQL-Server-Performance.com All postings are provided â€œAS ISâ€ with no warranties for accuracy.

If this is failing on day to day basis then you have to monitor the event using profiler & performance monitor for system resource usage. If intermittent then ensure there are no other jobs clashing at the same time. Are there any recent changes to the infrastructure or SQL server?
Any changes to the database recovery models?
Any changes to the hardware or any firmware update?
Check permissions for SQL server services too.
Satya SKJ
Microsoft SQL Server MVP
Writer, Contributing Editor & Moderator
http://www.SQL-Server-Performance.Com
This posting is provided AS IS with no rights for the sake of knowledge sharing. The greatest discovery of my generation is that a human being can alter his life by altering his attitudes of mind.

Viskar, When you do the local backup try using UNC name and see what happens? MohammedU.
Moderator
SQL-Server-Performance.com All postings are provided â€œAS ISâ€ with no warranties for accuracy.

Yes its failing on day to day basis and eventviewer and trace doesn’t come up with any thing conclusive. We had symantec AV on sytem and we have uninstalled this and since then SQL server hasn’t restarted, so I beleive its because of AV that SQL server was restarting (Can be settings of AV). However we are still getting errors on backups as specified above. I am checking for "VAS" issue specified above and should be able to update you all by today. In the meantime, why are we having errors on network name no longer available. Can this be because of SAN. This local drivce where we are backing up database is just used for this single database. We have tried to backup this on different local drive as well and still get this same error??? any idea why??
Message
Executed as user: PATCHSQLACCOUNT. TCP Provider: The specified network name is no longer available. [SQLSTATE 08S01] (Error 64) Communication link failure [SQLSTATE 08S01] (Error 64). The step failed.

Most of the times "The specified network name is no longer " is connectivity related and I believe you need to open a ticket with SAN provider and MS to get more tech support becasue you don’t see any errors in cluster log too…
Genereally, if sql server loose the connectivity to the cofigured shared drives you should see an entry in cluster log. VAS issue can resolved by restarting the sql services… and in 2000 I use -g switch to increase the memory and I don’t know at this time -g switch works with 2005 because I have not used it…
MohammedU.
Moderator
SQL-Server-Performance.com All postings are provided â€œAS ISâ€ with no warranties for accuracy.

We did restarted SQL server yesterday and that hasn’t fix the issue as yet. I am still working on link provided by you, to see if anything else helps out. Cheers

I have checked the doc on VAS. I have executed trace in night with few parameters. Please find attach results. "Retaildb" is database which is having issues with backups. Please suggest??
Counter Object Average (Whole night) Average (Around Backup Failure)
Network IO waits Avg wait time (ms) 235 0
Memory grant queue waits Avg wait time (ms) 0 0
Pages/Sec Memory 85.4 28.9
Available Mbytes Memory 1717 1687
DiskTime_G (Temp DB) Physical Disk 30.2 0.6
Avg Disk Queue Length_G(Temp DB) Physical Disk 0.3 0.006
DiskTime_S (System DB) Physical Disk 193.6 1.3
Avg Disk Queue Length_S(System DB) Physical Disk 1.93 0.013
Avg Disk Queue length_F(RetailDb) Physical Disk 7.9 0.409
Processor Time_Total Processor 26.3 24.12
Processor Time_0 Processor 38.78 26.516
Processor Time_1 Processor 23.336 24.42
Processor Time_2 Processor 18.82 21.33
Processor Time_3 Processor 24.3 24.2
Queued Jobs SQL Server 0 0
Buffer Cache Hit Ratio SQL Server 99.58 99.8
Transactions/Sec_Total SQL Server 146.99 165
Transactions/Sec_RetailDB SQL Server 2.2 5.5
Backup/Restore Throughput/sec_Retaildb SQL Server 10330439 15518963
User Connections SQL Server 202 195

I don’t see anything in your stats… backup/restore throughput is low…. MohammedU.
Moderator
SQL-Server-Performance.com All postings are provided â€œAS ISâ€ with no warranties for accuracy.

Just another thought is SQL is upto latest service pack levels?
Now I see on your first post it has no Service pack, then you have to test and apply latest service in order to fix the bugs or issues since the RTM release. Satya SKJ
Microsoft SQL Server MVP
Writer, Contributing Editor & Moderator
http://www.SQL-Server-Performance.Com
This posting is provided AS IS with no rights for the sake of knowledge sharing. The greatest discovery of my generation is that a human being can alter his life by altering his attitudes of mind.

what should be the backup/ restore throughput?? can there be anything that we can do in interim till we upgrade to sp1, we might have to spend some time in testing this from our application point of view.

It all depends on your HW and configuration… It can as low as 5 and as high as in hunders… If it is production, try to call MS PSS and what they come up <img src=’/community/emoticons/emotion-1.gif’ alt=’

‘ /> MohammedU. Moderator SQL-Server-Performance.com All postings are provided â€œAS ISâ€ with no warranties for accuracy.

Thanks, do you think that backup is failing becuase of Bug in sql server 2005 and is this bug fixed in sql server 2005 sp1??

I have not seen any error like this so far…it might be a bug or something else… Please check with MS. MohammedU.
Moderator
SQL-Server-Performance.com All postings are provided â€œAS ISâ€ with no warranties for accuracy.

The best option is for you to go through the README.HTML for service pack 1 & 2 in this case, also it will be good option to refer to CSS and by default their answer will be to upgrade to latest patches. Satya SKJ
Microsoft SQL Server MVP
Writer, Contributing Editor & Moderator
http://www.SQL-Server-Performance.Com
This posting is provided AS IS with no rights for the sake of knowledge sharing. The greatest discovery of my generation is that a human being can alter his life by altering his attitudes of mind.

Thanks, I’ll check for Readme.html

.. if in doubt the best option is to test them on your dev. environment with the application too to ensure the deprecated features are not affecting the application availability. Satya SKJ
Microsoft SQL Server MVP
Writer, Contributing Editor & Moderator
http://www.SQL-Server-Performance.Com
This posting is provided AS IS with no rights for the sake of knowledge sharing. The greatest discovery of my generation is that a human being can alter his life by altering his attitudes of mind.

]]>