RATE THIS ANSWER
0
Click to Vote:
- 0
0
Last Answered:
Jul 1 2008 9:09 AM GMT
by Ritchie1
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.
SQL Server 2005, DBCC, SQL error messages
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? 
