Database Hangs When Opening

[sandylou]

I was having a problem with a table when everytime I was trying to select from it or update the data, it would hang for over 20 minutes. It wasn't a very large table. I also would try and modify the table and it would hang for a very long time. I selected the data into a new table and I was able to update and select from the new table just fine. I then wanted to delete the other table, but was unsuccessful. I tried to truncate and drop the table and the query would hang for a very long time. I am not an expert using SQL server, so I thought, maybe I can just delete the table from the sysobjects. I did this, which afterwards I thought, yikes, I am sure there are other system tables I should delete information from in order to fully remove this table. The table did go away, but now, I can not open up the database in Enterprise Manager. When I try, the system hangs for a long time. I let it try to open for over 1 hour.

I kind of want to know why the system would just hang, but more importantly I want to fix my database. I tried to import the data into another database, but it hangs when trying to access the database. I can open up a query window and select the data. Any help?

 

[kishy]

I guess there is some problem with your database itself. Your queries are frequently getting hung which should not be the case.

Do you have a backup of your database. If yes, first restore your database to normal state. Then run some DBCC checkdb commands to see whats going on your database. This will give you a clue of what needs to be tuned to get your database running.

Hope this helps.
--Kishore

 

[sandylou]

one of my queries is getting information from a linked server database...could this cause the problem?

 

[mrdenny]

Don't ever delete data from the system tables. Doing so can completely hose your database (as you have seen).

First restore your database from backup.

A linked server could be causing some issues, but probably not to this degree.

First run:

dbcc checkdb ({database_name})

This will tell you if there are any problems with the database it self.
You can also run

sp_who2

This will tell you if there is any blocking going on.

You can also look in Enterprise Manager, under Managment, Current Activity at the Locks / Object and see what's being locked. You are looking for exclusive locks (X, SIX, or IX). If there are any locks of this type look at the code that is causing the lock, and see what's going on that it's locking the table.

Denny
MCSA (2003) / MCDBA (SQL 2000)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

(My very old site)