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:
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.