Restore Error

Hello everyone I am experiencing backup problem I am backing up 335 GB database over the network.
I am running MS SQL 2000 Enterprise on windows 2000 Advance server. The system where I want to backup has windows 20003 server standards addition.

Error at “view Job History”
Executed as user: QMANAGERAdministrator. 10 percent backed up. [SQLSTATE 01000] (Message 3211) 20 percent backed up. [SQLSTATE 01000] (Message 3211) 30 percent backed up. [SQLSTATE 01000] (Message 3211) 40 percent backed up. [SQLSTATE 01000] (Message 3211) 50 percent backed up. [SQLSTATE 01000] (Message 3211) 60 percent backed up. [SQLSTATE 01000] (Message 3211) 70 percent backed up. [SQLSTATE 01000] (Message 3211) 80 percent backed up. [SQLSTATE 01000] (Message 3211) 90 percent backed up. [SQLSTATE 01000] (Message 3211) Write on '\utl000fs001workgroupsackupQManagerSQLBackupsFull_UtlQM.bak' failed, status = 1450. See the SQL Server error log for more details. [SQLSTATE 42000] (Error 3202) BACKUP DATABASE is terminating abnormally. [SQLSTATE 42000] (Error 3013). The step failed.

Message at server Error Log.
BackupMedium::ReportIoError: write failure on backup device '\utl000fs001workgroupsackupQManagerSQLBackupsFull_UtlQM.bak'. Operating system error 1450(Insufficient system resources exist to complete the requested service.).


I check both my SQL server box event viewer for error and the destination server where backup is going. I did not found any entry that system run out of page pool memory.

Any idea or suggestion please……….


Ashfaq

Please see the link below.

http://support.microsoft.com/kb/304101

Thanks
Danny

Thanks Danny for posting reply; is this be the only solution to get around this problem? and at what server should I make this changes. The one which I have SQL server or the server where I am backing up.
Also if there is any other way to get around this problem. Because these articles are saying like “try it on your own risk”

Thanks;
Ashfaq

Ashfaq

I never had such problem but i have been through this article so i refered you.
I would suggest dont make any step based on this article only. Either approach the senior DBA or wait for some other advices form members then only make any move.

Thanks
Danny

Thanks Danny

Ashfaq

What sql service pack or build you are on?
Are you restoring from disk or tape over the network?

What is the status of the database when the job failed?
Try running "Restore database <db name> with recovery" after the job failed...

Check your system log if there are any disk errors reported...and make sure you have enough disk space on destination server where you are restoring...

I restore 800+GB database over the network...


Mohammed U.

i am backing up not restoring and backing up to a network drive . SQl server Enterprise Ed service pack 3 running on Windows 2000 Advance server

Ashfaq

http://support.microsoft.com/kb/304101
According to the article I made changes to registry setting and set that value to 60 I am using 2 Gig of ram at the destination server where I am dumping my backup. But still it was not successful. Did I make the right changes or should I edit registry setting of the SQL server box.
Also how we can change behavior of the backup such that it request less memory.

Regards

Ashfaq

no i am not getting any performance issues.


Ashfaq

My Backup get failed at the end and the error "insufficent system resources" upon searching of this error take me that one my server has problem with page/sec problem. i don't know if thats the real cause. if i did not figure out after holidays i will give some business to Microsoft.

Ashfaq

Is your backup is native sql backup? or using 3rd party tools like LiteSpeed?

Did you Network Adapter errors?

Mohammed U.

i am using Native SQL Backup? i will try Litespeed trial version if it successful then i will buy a licience. does any one know about the trial version database limit. i mean how much data you can backup using trial version.

Ashfaq

I tried to another server but it did not worked. then i followed Kb article http://support.microsoft.com/kb/304101) and edit destination box registry setting to FFFFFFFF it worked.

thank you all for your support.
Regards;
Ashfaq


Ashfaq

Ashfaq - Can you please explain in detail what was the exact problem what steps you followed to resolve it. Just for my own knowledge sake as i want to know the exact use of that article .
Thanks
Danny

quote:Originally posted by DBA_Ashfaq

i am using Native SQL Backup? i will try Litespeed trial version if it successful then i will buy a licience. does any one know about the trial version database limit. i mean how much data you can backup using trial version.

Ashfaq

I don't think there is any database limit for trial version...
I used trial softwary to backup 600GB database...You backup backup will much faster and smaller.

I run my 600+ GB backup in < 2hrs and the backup size is <200GB...

Note: There are some other SW also available you can shop around for pricing...


Mohammed U.

i tried to post it at my thread but it not let me post it there. here is what i did
According to the article i changed registry setting by adding new key to registry; steps are as follows.


1. Click Start, click Run, type regedit in the Open box, and then click OK
2. Locate and then click the following registry subkey:
HKEY_LOCAL_MACHINESYSTEMCurrentControlSetControlSession ManagerMemory Management
3. On the Edit menu, point to New, and then click DWORD Value.
4. Type PagedPoolSize as the entry name, and then press ENTER.
5. Right-click PagedPoolSize, and then click Modify.
6. Click Hexadecimal.
7. In the Value data box, type a value of FFFFFFFF, and then click OK.

then after making change i restart this box and start my job.
it took around 1 hour 40 minutes to complete on size is currently 351 GB

quote:Originally posted by danny123

Please see the link below.

http://support.microsoft.com/kb/304101

Thanks
Danny

Ashfaq

i used spotlight demo version on test box and never tried on production. but it really compress the data i mean the backup file. it would be very handy if you have limited space on the backup server.
if you are looking to reduce backup time you can split backup database file to multi files which SQL server write it parallel. like in my case if i backup my 351 GB database over the network it take more than 5 hours or some time more but if i split it to 10 files than it take only 1 hour 30 minuties.


Ashfaq

Becareful when your using many files because SpotLite backup process spins sepeate threads for each file which will may cuase performance issue if you run during peak hours and process is disk intensive...



Mohammed U.

i did not used spotlite for this backup i am using Native SQL backup.
Regards;

Ashfaq

above changes made my christmas weekend happy but now today it failed twice with same errors.


Ashfaq

Last option is PSS<img src='/community/emoticons/emotion-1.gif' alt='' /><br /><br />Mohammed U.

last option u mean lightspeed.

Ashfaq

I mean Microsoft PSS (Product Support Services)<img src='/community/emoticons/emotion-1.gif' alt='' /><br /><br />Litespeed may work you have to test it...<br /><br /><br />Mohammed U.