SQL Server Performance

Restore Error

Discussion in 'General DBA Questions' started by DBA_Ashfaq, Dec 15, 2006.

  1. DBA_Ashfaq New Member

    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
  2. danny123 New Member

  3. DBA_Ashfaq New Member

    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
  4. danny123 New Member

    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
  5. DBA_Ashfaq New Member

    Thanks Danny

    Ashfaq
  6. MohammedU New Member

    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.
  7. DBA_Ashfaq New Member

    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
  8. DBA_Ashfaq New Member

    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
  9. satya Moderator

    Are you getting any performance issues during the backup process lately?

    Satya SKJ
    Microsoft SQL Server MVP
    Contributing Editor & Forums Moderator
    http://www.SQL-Server-Performance.Com
    This posting is provided AS IS with no rights for the sake of knowledge sharing.
  10. DBA_Ashfaq New Member

    no i am not getting any performance issues.


    Ashfaq
  11. satya Moderator

    Then why do you worry abot usage of pages/sec counter in this case during the backup process.

    Satya SKJ
    Microsoft SQL Server MVP
    Contributing Editor & Forums Moderator
    http://www.SQL-Server-Performance.Com
    This posting is provided AS IS with no rights for the sake of knowledge sharing.
  12. DBA_Ashfaq New Member

    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
  13. MohammedU New Member

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

    Did you Network Adapter errors?

    Mohammed U.
  14. DBA_Ashfaq New Member

    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
  15. satya Moderator

    Specify your hardware configuration and all sorts of processess that are scheduled during this backup time, capturing system usage with PERFMON would help you in this regard to see what is happening there

    Satya SKJ
    Microsoft SQL Server MVP
    Contributing Editor & Forums Moderator
    http://www.SQL-Server-Performance.Com
    This posting is provided AS IS with no rights for the sake of knowledge sharing.
  16. DBA_Ashfaq New Member

    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
  17. danny123 New Member

    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
  18. MohammedU New Member

    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.
  19. satya Moderator

    In this case about the third party tool refer to Software spotlight section in this website, where you will get how best you can utilize the referred tool for your purpose.

    Satya SKJ
    Microsoft SQL Server MVP
    Contributing Editor & Forums Moderator
    http://www.SQL-Server-Performance.Com
    This posting is provided AS IS with no rights for the sake of knowledge sharing.
  20. DBA_Ashfaq New Member


    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
  21. DBA_Ashfaq New Member

    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
  22. MohammedU New Member

    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.
  23. DBA_Ashfaq New Member

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

    Ashfaq
  24. DBA_Ashfaq New Member

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


    Ashfaq
  25. MohammedU New Member

    Last option is PSS<img src='/community/emoticons/emotion-1.gif' alt=':)' /><br /><br />Mohammed U.
  26. DBA_Ashfaq New Member

    last option u mean lightspeed.

    Ashfaq
  27. MohammedU New Member

    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.

Share This Page