May 14, 2009 at 12:22 am
Malcolm Daughtree (5/13/2009)
Jrebooting the Server and restoring the databases from the last know "good" backup is all I can suggest.
No, There is a solution to every problem. OP has to give a try to what we have suggested. Did you check the link above that I have posted?
May 14, 2009 at 1:00 am
I agree in principal but, The error 'Insufficient resources' is exactly what the situation is if the job contained a DBCC CheckDB command.
Windows 2003 Error: The operating system returned error 1450(Insufficient system resources exist to complete the requested service.) to SQL Server during a write at offset 0x000031abb4e000 in file with handle 0x00000F74. This is usually a temporary condition and the SQL Server will keep retrying the operation. If the condition persists then immediate action must be taken to correct it.
The recovery phase of rebooting the SQL server will continue to retry the open transaction - in MSDB as that is the source of the Job. It will try and complete the ROLLedBACK transaction hence placing a lock again on the database therefore not allowing you to restore as previously suggested . If the job was to create a Database snapshot the sparse file creation will think there is insufficient space and again the error 'Insufficient resources' is exactly what the situation is
I agree with the last of the resolutions but running DBCC CheckDB during a ROLLBACK makes only for a worse situation.
Resolution:
· Run DBCC CHECKDB in OFFLINE mode (WITH TABLOCK) - but seeing as there is already an SPID on the database from the Retry this might n ot be possible.
· Run DBCC CHECKDB at a time when less data modification is taking place.
· Divide the database into a few more files. The limitations are per sparse file and each database snapshot creates matching sparse files for each database file. For example if the database files are each limited to 64GB in total size you can't exceed the limitation on a per file basis.
· Remove snapshot databases that have grown large.
As I don't know what the initial Job (SPID) that was killed and they still can't get access to the database, then you are no worse off if you take the corrective action of to delete the database and restore from a backup.
CodeOn
😛
May 14, 2009 at 5:43 am
0,9GB it's very low space to work with a production server. Only just for the pagination file.
Also you could probe backups at different hours. But only for that 0,9 GB.
If hardware monitoring it's ok and your logs in SQL are ok, you could monitor your Operative System with custom alerts by mail.
It's only an idea
May 14, 2009 at 6:27 am
Thanks for all your valuable replies. I appreciate it. I'll change the backup timing tonight and see if it is any better.
Viewing 4 posts - 31 through 33 (of 33 total)
You must be logged in to reply to this topic. Login to reply