Tell me more ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.
up vote 1 down vote favorite

I'm having problems with a database.

  1. I can run basic queries, albeit much slower than normal.

  2. When I attempt to view the hierarchy trees for tables, views, or procedures in SSMS Object Explorer, I get lock request time out period exceeded.

  3. My SSRS reports that run on objects in this database are no longer completing.

  4. Jobs associated with procedures stored on this database also do not run.

I tried using sp_who2 to find and kill all connections on the database, however this has not solved the problem.

What is going on here? How can I resolve this?

share|improve this question
Also see: stackoverflow.com/questions/12167570/…; not sure if that counts as a duplicate or not. – LittleBobbyTables Aug 28 '12 at 21:17
Based on your comment to my answer below, I think you need to provide a lot more information. How is the server sized, have you watched it's performance counters, is it swapping to disk or otherwise resource starved in some way. Be sure to actually check the above and not just assume anything. Further, does this happen when you connect while remoted into the desktop? Is the problem only occuring when accessing from a single location? What is the network weather like for that server (and your connection to it)? – Chris Lively Aug 28 '12 at 21:36
Sounds like you have open transactions that are blocking the read access to the tables. – a_horse_with_no_name Aug 28 '12 at 22:04

migrated from stackoverflow.com Aug 29 '12 at 9:10

3 Answers

up vote 0 down vote

There are so many things this could be that all I can offer are a few questions to help guide you towards an answer.

  1. Is the DB on a server dedicated to just running SQL Server? If not, other processes might be interferring by stealing precious processor time.

  2. Is the DB server essentially out of memory? SQL Server will attempt to allocate every single byte it can, but if it's at capacity and your queries require more data to be loaded then it has to fallback to using virtual memory, which radically increases the amount of time even simple queries might take.

  3. Is the DB server's network bandwidth to small to handle transferring the data in a timely manner?

At the end of the day, it sounds like the machine you are hosting SQL Server on is under sized for what you are trying to do. It's entirely possible that you have finally reached those hardware limits where performance is dropping off radically. If this is the case (the above questions will help you determine that) then you'll want to move the DB to a server that is properly sized for the amount of data (and queries) you are trying to process.

This could mean using faster processors, faster drives, or just installing more RAM.

share|improve this answer
It's not a hardware issue. The server cluster hosts multiple databases. This is the only database having problems – Lloyd Banks Aug 28 '12 at 21:26
@LloydBanks: That doesn't mean this isn't a hardware problem. If I have 2 databases, one that's 20GB in size with a high transaction rate and another that's 1GB with a lower transaction rate then I'd expect the 1GB db to be swapped to virtual memory; which would increase query times. If the 20GB db was being hit hard enough, this could lead to connectivity issues with the smaller one. – Chris Lively Aug 28 '12 at 21:34
up vote 0 down vote

Excluding Harware consideration, perhaps you need to run the script to check what are the activity withholding the SQL Session, one of the common scenario is not to use an Implicit transactions Option in SQL Server Management Studio.

share|improve this answer
Hi turbot, can you go into more detail about what you are suggesting? – Lloyd Banks Aug 28 '12 at 21:45
up vote 0 down vote accepted

It was being caused by a perpetual rollback of a transaction. Had to eventually restart my server cluster

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.