| Author |
Topic |
|
sjpaq@rogers.com
Starting Member
18 Posts |
 Posted - 2007-11-10 : 09:40:11
|
| I am running SQL Server 2000. When I go into Enterprise Manager and right click on a database, the hourglass appears and it doesn't return.Similarly, when I run a query agains that database using a 3rd party tool similar to Query Analyzer it doesn't return.I am new to SQL Server, have worked with other db's. Any clues as to how I can trouble shoot what is happening with my DB?Thanks |
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2007-11-10 : 10:05:41
|
| runselect * from master..sysprocess (nolock)and see if anything is blocking.Better - add this to master and run ithttp://www.nigelrivett.net/SQLAdmin/sp_nrInfo.htmlit will show you what is happening.==========================================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. |
 |
|
|
sjpaq@rogers.com
Starting Member
18 Posts |
Posted - 2007-11-10 : 12:15:47
|
| I ran the sp_nrinfo 'blk' and got a table with about 12 rows in it. Not sure what to make of it. here are some of the entriesvlocking_spid last_batch hostname59 2007-11-08 12:02 dae ....RPC Event 0 sp_prepexec;1 ...1 213575799 1 ....59 RUNNABLE sa59 0 60 5982 zsleeping |
|
|
|
rmiao
Master Smack Fu Yak Hacker
7266 Posts |
Posted - 2007-11-10 : 23:03:01
|
| Did you have same problem when try to open other db in em? Is that db set to 'auto close'? Is it online? Does query return result in query analyzer? |
|
|
|
sjpaq@rogers.com
Starting Member
18 Posts |
Posted - 2007-11-10 : 23:09:57
|
| Thanks rmiao, see answers to your questionsDid you have same problem when try to open other db in em?Other DBs seem fineIs that db set to 'auto close'?How do I check thisIs it online?Yes it is.Does query return result in query analyzer?Some queries against this database are returning but others are not. I suspect related to locks against some tables. |
|
|
|
rmiao
Master Smack Fu Yak Hacker
7266 Posts |
Posted - 2007-11-10 : 23:13:38
|
| You can check locks in em, check blocks with 'sp_who2 active', check db option with sp_dboption. |
|
|
|
sjpaq@rogers.com
Starting Member
18 Posts |
Posted - 2007-11-11 : 05:59:18
|
| Thanks again rmiao.Ok I did an sp_who2 'active' and I can see that spID 60 and 61 are blocked by (BlkBy column) spid 59.Late yesterday I killed spid 59. Shouldn't this have removed the block? Why do I still see spid 59 when I do an sp_who2 'active'?To answer your questions from yesterday. The database is indeed set to autoclose. |
|
|
|
sjpaq@rogers.com
Starting Member
18 Posts |
Posted - 2007-11-11 : 07:18:22
|
| I wanted to add that I am not concerned about the data integrity in this database, it is pretty much just development work going on. If I just take the database offline and back online again, will this solve my problem.It seems that the spid 59 that I killed is still rolling back after 12 hours. |
|
|
|
dataguru1971
Master Smack Fu Yak Hacker
1464 Posts |
Posted - 2007-11-11 : 07:50:51
|
SOunds like spid 59 was doing a major process. You really need to wait until it is done rolling back. In QA you can run kill 59 if you hit F5 again and run it you can see the rollback %complete and estimated seconds remaining. |
|
|
|
sjpaq@rogers.com
Starting Member
18 Posts |
Posted - 2007-11-11 : 08:05:30
|
| Can i take the database offline and back online to clear any locks?spid 59 estimate complete for rollback has not changed in 12 hours. |
|
|
|
dataguru1971
Master Smack Fu Yak Hacker
1464 Posts |
Posted - 2007-11-11 : 08:43:17
|
| The time to completion is the same now as it was 12 hours ago? If you aren't concerned about data integrity, I suppose you "could" do a soft restart of SQL Services, or even a hard reboot. I wouldn't do it personally, until I exhausted every other option. ANy idea what exactly spid 59 was doing that it is now rolling back? |
|
|
|
sjpaq@rogers.com
Starting Member
18 Posts |
Posted - 2007-11-11 : 10:41:49
|
| The message I get when I issue a kill 59 again is"SPID 59: transaction rollback in progress. Estimated rollback completion: 0%. Estimated time remaining: 0 seconds."This is the same message that I got when I first killed 59 over 12 hours ago. I don't think the rollback is happening or there may be another problem with the DB.I can't shutdown SQL Server or reboot the server because there are other databases running smoothly. I can however do anything I want with this database. Can I force it offline and then back online? |
|
|
|
dataguru1971
Master Smack Fu Yak Hacker
1464 Posts |
Posted - 2007-11-11 : 10:49:20
|
| What does DBCC CHECKDB return? Any error messages?You can do:ALTER DATABASE 'DBNAME' SET OFFLINE WITH ROLLBACK IMMEDIATEand see if it sets it offline. |
|
|
|
sjpaq@rogers.com
Starting Member
18 Posts |
Posted - 2007-11-11 : 10:59:43
|
| I executed DBCC CHECKDB('dbname') and it is not returning from the command, seems to be hanging.Should I start another isql session and executeALTER DATABASE 'DBNAME' SET OFFLINE WITH ROLLBACK IMMEDIATELet me know, I appreciate your input. |
|
|
|
dataguru1971
Master Smack Fu Yak Hacker
1464 Posts |
Posted - 2007-11-11 : 11:04:29
|
| I guess you can do that, you don't need my permission. |
|
|
|
sjpaq@rogers.com
Starting Member
18 Posts |
Posted - 2007-11-11 : 13:35:46
|
| dataguru1971I offlined and onlined the db and this seems to have fixed things. Wasn't looking for your permission as much as your experience.Thanks, appreciate the time you put in to help diagnose and suggest fixes. |
|
|
|
dataguru1971
Master Smack Fu Yak Hacker
1464 Posts |
Posted - 2007-11-11 : 13:37:19
|
| no problem, I just worry about saying "sure, go ahead"...if it destroyed your database, I didn't want to be the one who caused that. |
|
|
|
dataguru1971
Master Smack Fu Yak Hacker
1464 Posts |
|
|
|
Enterprise Manager Hanging