Trouble opening an inaccessible table in SQL Server 2005

Tags:
DBCC
SQL error messages
SQL Server 2005
A client of ours is running SQL Server 2005 with PS2 on Windows Server. His database size is about 5 GB. Every now and then something happens and one table (the most important) becomes inaccessible. When we try to open the table, it gives a “SQL time out” error. This sometimes happens twice a day, and the only way to get rid of it is to restart the machine. Stopping and starting SQL Server doesn’t work, and a DBCC CheckDB doesn’t give any issues. Any ideas?
ASKED: June 18, 2008  3:10 PM
UPDATED: July 1, 2008  9:09 AM

Answer Wiki

Thanks. We'll let you know when a new response is added.

This sounds like a table lock.
I would check the basics. Apologies if this is too basic.

Check locks (Management\Activity Monitor -Right click: View locks by Process). Or run query:
Use TableName
exec sp_who or exec sp_who2 to find who is locking the table.

You should be able to view table data using “Select column1, column2 from TableName with (nolock)”.
The most common reason for table locks is an inefficient query running which is not using indexes. Check indexes and consider adding extra indexes or modifying current ones. Also look at queries to see if they are calling more columns than needed eg. replace “select * ” with “select column 1, column2 “. See if the WHERE statements can be tightened. Also consider using the “with (nolock)” in select queries if data is not prone to change. Also if index contains more than one column, make sure queries reference the first column in the index to ensure SQL Server uses the index.
Check queries using “display estimated execution plan” to see if you can find the slower / inefficient queries.
You should not need to restart the server, restarting the SQL Server service (Control Panel\ Administrative Tools\ Services \ SQL Server – restart) should be enough, although killing the lock should free up the table.

You could check disk configuration – split logs / backups / filegroups across different disks.

Check Windows Event Viewer and Task Manager to see if something else is slowing up the machine.
If the SQL Server is running in full 2005 mode then the reports available can give a lot of information. Download the Dashboard from microsoft to get even more impressive reports.

Discuss This Question:  

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Thanks! We'll email you when relevant content is added and updated.

Following