Dead SQL Server 2005 box… could it be down to a cursor?

Today was an odd one… a colleague and I were doing some eXtreme Programming (XP)…. i.e. we needed to solve an issue with one of our many ASP.NET applications quickly!!

We were looking at a stored procedure that contained a cursor. We then fiddled a bit and commented out lots of it out to aid our debug… In our excitement to solve the problem we made the schoolboy error of not un-commenting the CLOSE and DEALLOCATE steps in the cursor when we reinstated it.

The web page linked to the stored procedure was loading in the browser… it took an age to appear… alarm bells were ringing! What had we done… was there an infinite loop cursor in progress?

I logged into SQL Server Management Studio to run a diagnostic query. The diagnostic query I was running was very similar to the excellent one Glenn Berry has published. It checked for obvious issues such as

  1. High CPU load operations
  2. Blocking queries
  3. Transaction log size
  4. Network and I/O issues

I was not unsurprised to see that the transaction log on tempdb was full. Not being a SQL expert in the slightest, I called the Operations Manager over for assistance. Remote Desktop to the machine was also non functioning… what had we done! Could a messed up cursor really cause all these issues?

Well, the investigations continue. If you experience similar issues to this, definitely look at Glen Berry’s SQL script and the following Microsoft support articles

  1. Causes of Transaction log expansion
  2. Unexpected transaction log growth

enjoy

SpittingCAML




You can follow any responses to this entry through the RSS 2.0 feed. You can leave a response, or trackback from your own site.

Leave a Reply