| Author |
Topic |
|
mzwebbie
Starting Member
15 Posts |
 Posted - 2003-06-06 : 11:40:56
|
| I hope this is the right forum. We have a server with quite a number of databases on it, and one particular one is not allowing the users to update it (something they urgently need to do). We have decided to restore the database, and we have quite recent backup so this would probably make everyone fairly happy. The only trouble is the database won't let us do anything..backup, restore, delete, anything, because of a particular process that will not die. I've been searching the forum on this, and yep sure enough its in ROLLBACK status. But its been there forever. And we've killed it, and killed it and killed it. And..its like like the process from Friday the 13th..it just won't die! Restarting the server really isn't an option because this would take down several other clients and another company we share the server with. It doesn't matter if the rollback doesn't complete, since we want to scrap the database and start over with that recent backup. But the rolling back process won't let us, and one of our users is anxiously awaiting a fix so she can put in time sensitive change for a client.So..um..HELP! Anyone have any better ideas? Soon? Thanks in advance |
|
|
mzwebbie
Starting Member
15 Posts |
Posted - 2003-06-06 : 12:11:14
|
| Will "Take Offline" help me at all? I see this option under all tasks when I right click on the database. I'm wondering if using it would let me restore the database? |
 |
|
|
Shastryv
Posting Yak Master
145 Posts |
Posted - 2003-06-06 : 12:14:35
|
| You can not take it offline while its Active |
|
|
|
efelito
Constraint Violating Yak Guru
478 Posts |
Posted - 2003-06-06 : 12:17:54
|
| How flexible is the application that connects to the database? Would it be a problem to restore the database with a different name and use the temporary name until you have a change to reboot the server or that process clears up? Should be able to set the old db to dbo use only or something along those lines so people can't accidently access the wrong one.Jeff Banschbach, MCDBAEdited by - efelito on 06/06/2003 12:18:42 |
|
|
|
mzwebbie
Starting Member
15 Posts |
Posted - 2003-06-06 : 12:20:18
|
| Rats! Thanks. Does anyone know anything about KillDatabase?? This below is from the BOL. If I kill it, create a new one, then restore the data from yesterday's backup, will it bring in all the permissions and such too or just the data? Hope thats not a dumb question, I don't do the administration end of it much. Boy if all this wasn't enough fun for a Friday, we are doing a major system replacement/upgrade this weekend. ----------------------KillDatabase MethodThe KillDatabase method drops a database from the referenced Microsoft® SQL Server™ 2000 installation, regardless of the status or availability of the database.Applies ToSQLServer Object Syntaxobject.KillDatabase( Database )PartsobjectExpression that evaluates to an object in the Applies To listDatabase |
|
|
|
mzwebbie
Starting Member
15 Posts |
Posted - 2003-06-06 : 12:22:40
|
| Well I think the internal app could be changed, but we have a web app that also connects to this db, and unfortunately a lot of the older code in the ASP has (argh!) hard coded db names. I may end up doing that though, thanks for the suggetion.-------------------How flexible is the application that connects to the database? Would it be a problem to restore the database with a different name and use the temporary name until you have a change to reboot the server or that process clears up? Should be able to set the old db to dbo use only or something along those lines so people can't accidently access the wrong one. Jeff Banschbach, MCDBA |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2003-06-06 : 12:39:19
|
| What query is this process running? Just run DBCC INPUTBUFFER(93) to find out. And yes if you restore the database backup, it will contain all of the permissions and everything else that you need. As long as you restore the backup on the same server, you won't have to worry about passwords.Tara |
|
|
|
mzwebbie
Starting Member
15 Posts |
Posted - 2003-06-06 : 13:24:59
|
| Well I ran that and it told me what query it was running. A select query. But, I'm not sure what to do with the info... |
|
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2003-06-06 : 13:27:11
|
| Look at the physical io in sysprocesses to see if it is really doing anything. A rollback can take a very long time - often much longer than the thing that is being rolled back.If you have a stuck process and it is in rollback (kill probably won't help as it's already trying to stop) then you will probably need to bounce the server to get rid of it.After that the database recovery procedure will complete the rollback from the tran log.==========================================Cursors are useful if you don't know sql.DTS can be used in a similar way.Beer is not cold and it isn't fizzy. |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2003-06-06 : 13:29:12
|
| I don't understand why it is rolling back a SELECT statement. What is there to rollback? There are certain things that can't be killed or rolled back such as DBCC statements, so that's why I was asking what query it was running.Tara |
|
|
|
|
Process 93 won't die!