| Author |
Topic |
|
jetberrocal
Starting Member
5 Posts |
 Posted - 2010-07-08 : 13:44:40
|
| I am planning to restore various MS SQL Databases with ADO withing VB.NET using the RESTORE T-SQL. Since I do not know how much time will it take to restore each database (each database varies in size), I am also planning to set the CommandTimeout=0.The problem is what if the restore has a problem which stays executing for ever?I was thinking on running the RESTORE inside a thread and having the user to optionally abort the thread, or closing the connection.What will happen to the T-SQL at the server? One thing is to stop the ADO command abruptly and other is that the RESTORE is also stopped at the server.What do you recommend? |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
jetberrocal
Starting Member
5 Posts |
Posted - 2010-07-09 : 09:27:32
|
quote:
Originally posted by tkizer
You'd have to issue a KILL command on the spid to cancel the restore.But if anything happens to the restore, it'll error and send the error back to your application. It won't just sit in a waiting state forever.Tara KizerMicrosoft MVP for Windows Server System - SQL Serverhttp://weblogs.sqlteam.com/tarad/Subscribe to my blog
Some questions:1. Does CommandTimeout=0 means infinite or is really a long time that is considered infinite? Then what is the real time for '=0'? If it is really infinite it will sit in a waiting state forever, why will it stop and return an error?2. How do I do a Kill to the spid using T-Sql or code? How do I know which spid to kill in code? |
 |
|
|
russell
Pyro-ma-ni-yak
5072 Posts |
Posted - 2010-07-09 : 09:46:00
|
| 1. infinite. if there's an error, the batch is complete and no more waiting. error is sent to the application.2. need to log into the SQL Server, find the SPID using SP_WHO2, then kill it using KILL command.only way your going to end up with it hanging forever is if you use the WITH NORECOVERY option in the restore.I would suggest that writing a VB.Net app to perform database restores is really not a good idea or useful anyway. Hopefully you're using Window Authentication... |
|
|
|
jetberrocal
Starting Member
5 Posts |
Posted - 2010-07-09 : 09:48:14
|
| I found the Kill T-SQL statement, but I still need to know in code which spid to kill.Does ADO or any other, has a way to return the spid value?Or a query that returns the RESTORE spid? |
|
|
|
jetberrocal
Starting Member
5 Posts |
Posted - 2010-07-09 : 10:42:06
|
quote:
Originally posted by russell
1. infinite. if there's an error, the batch is complete and no more waiting. error is sent to the application.2. need to log into the SQL Server, find the SPID using SP_WHO2, then kill it using KILL command.only way your going to end up with it hanging forever is if you use the WITH NORECOVERY option in the restore.I would suggest that writing a VB.Net app to perform database restores is really not a good idea or useful anyway. Hopefully you're using Window Authentication...
I am using Windows Authentication.I need to automate the restore process because there are multiple databases and the administrators are busy |
|
|
|
russell
Pyro-ma-ni-yak
5072 Posts |
Posted - 2010-07-09 : 11:48:59
|
| shouldn't kill a spid in the middle of a restoreanyway sp_who2 or select * from sysprocesses will show you which spid |
|
|
|
jetberrocal
Starting Member
5 Posts |
Posted - 2010-07-09 : 12:16:57
|
quote:
Originally posted by russell
shouldn't kill a spid in the middle of a restoreanyway sp_who2 or select * from sysprocesses will show you which spid
Some questions:1. Lets assume the restore is indeed hang(http://stackoverflow.com/questions/520967/sql-server-database-stuck-in-restoring-state). There are two reasons for it to hang. The norecovery as you said and premature disconnection. So what do you suggest in that event if not killing the spid?2. sp_who2 or select * from sysprocesses will show me all spid, or only the RESTORE one? How do I get only the one that is hang? |
|
|
|
|
Aborting CommandTimeout=0 T-SQL