| Author |
Topic |
|
revdnrdy
Posting Yak Master
220 Posts |
 Posted - 2010-08-05 : 17:29:04
|
| Hi all;Nice to be back posting again..We had an issue last night where we lost network connectivity and thus our scheduled nightly job failed during a restore. This job normally runs for about 5 hrs. The job runs via sql agent scheduling.Naturally this job failure leaves the DB in a restored state. To get it back into a normal state we simply restarted the job (oops!!). When we restarted the job it ran for a few hours and then died giving us the error "Failed to create execution thread".Has anyone else seen anything like this? I do not think its related to the restore job since none of that code was changed. Restarting the job does not clear the execution thread error.. Logs do not reveal anything..Additional info..The DB size is 400Gig.The database is being restored from server A to server BThe stored proc which does the restore has not been modified since last successful run.We do the restore with recovery and restart.Thanks! |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
revdnrdy
Posting Yak Master
220 Posts |
Posted - 2010-08-05 : 17:59:14
|
Hello;Our original scheduled restore job failed due to network issues.We restarted the scripted job at step 1 (see script below).During the second running of the job we got the execution error.The script seems to fail when its almost completed (when looking at run duration).Both times the job failed the system left the database in a restore stateHere is the script we used to restart the restore job------- RESTORE OF FULL BACKUP --- CHANGE RECOVERY MODE IF NECESSARY (NO DIFF or LOGS) USE MASTERRESTORE DATABASE FINANCES FROM DISK = '\\DDFDB\H$\MSSQL\BACKUP\FINANCES\FINANCES.BAK' WITH MOVE 'FINANCES.mdf' TO 'E:\MSSQL\DATA\FINANCES.MDF', MOVE 'FINANCES.LDF' TO 'F:\MSSQL\LOGS\FINANCES.LDF', RECOVERY, REPLACE Other forums have suggested this is a bug with sql agent. I can say that we have run this script several times in the past with no issues.On a side note it would have been nice to attend the conf in San diego since I live relatively close by.. Hope it went well.. |
 |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
revdnrdy
Posting Yak Master
220 Posts |
Posted - 2010-08-06 : 11:33:32
|
quote:
Originally posted by tkizer
The conference is in September, so you haven't missed it yet.Try restarting the Agent service just in case. And if that doesn't work, try deleting the database and then running it. And if that still doesn't work, run the code manually in SSMS rather than through a job.
We decided to rerun the job natively (without agent). This job ran successfully and we are back to normal. We also decided to restart the agent as an additional precaution. Still no root cause as to why an execution thread error ocurred.Good to know the conf is in sept. I guess I got some incorrect info earlier.This thread can be closed. |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
revdnrdy
Posting Yak Master
220 Posts |
Posted - 2010-08-06 : 14:18:58
|
Our version is: quote:
Microsoft SQL Server 2000 - 8.00.2187 (Intel X86) Mar 9 2006 11:38:51 Copyright (c) 1988-2003 Microsoft Corporation Enterprise Edition on Windows NT 5.2 (Build 3790: Service Pack 2)
I should have posted this in the sql2k forum. my mistake! |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
revdnrdy
Posting Yak Master
220 Posts |
Posted - 2010-08-06 : 17:28:22
|
| lol that sounds good but we already went down that route..Unfortunately I work for the state govt and there is no money in the budget to upgrade until next year. Given that Arizona is broke and the state capital bldg is engaged in a leaseback with the banks I doubt it will happen even then. |
|
|
|
|
Failed to create execution thread