Search code examples
sqlsql-serversql-server-2008sql-server-2012spid

SQL Server Query Time Out

A 3rd party application accessing a SQL Server 2012 database is getting [Microsoft][ODBC SQL Server Driver]Query timeout expired errors after executing for about 20 mins.

This is what I see on the database after the application starts receiving the errors:

Query Screenshot -1

SPID 102 is not shown in the query above. It is another connection from the same application for the same process. I was able to capture some details of this in the screenshot below. It is the one in the topmost row in this screenshot. Apologize that the scrollbar was moved to the right when taking the screenshot.

Query Screenshot - 2

AutoShrink was set to TRUE on this database when this occurred.

It looks like:

  • several connections from the 3rd party application which started later on are waiting on SPID 27 - which is an AUTOSHIRNK command

  • SPID 27 is waiting on SPID 102 which is another connection from the same 3rd party application

Question:

  • Is there any way to tell if the connection with SPID 27 created since AutoShirnk was set to TRUE on the database?

  • If so, why does it wait on SPID 102 and why are the other connections (83,85,86,88 and 90) waiting on 27?

According to the TechNet article in the link below, AutoShrink shouldn't have an impact on any activity on the database

If a database has the AUTO_SHRINK option set to ON, the Database Engine reduces the size of the files in the database. This activity occurs in the background and does not affect any user activity within the database.

Shrinking a Database

Notes:

  • This is currently running on SQL Server 2012 SP1 where this issue occurs.

  • This process in the application works ok when the application runs off a database on SQL Server 2008 SP3.

Solution

  • AUTO SHRINK works on a round robin fashion and if it sees free space on any DB, it deallocates it.

    Although it doesn’t have any effect like long-term blocking, it does take up a lot of resources, both IO and CPU. It also moves a lot of data through the buffer pool and so can cause hot pages to be pushed out to disk, slowing things down further. If the server is already pushing the limits of the IO subsystem, running shrink may push it over, causing long disk queue lengths and possibly IO timeouts.

    You will get a lot of answers and clarifications here - http://www.sqlskills.com/blogs/paul/auto-shrink-turn-it-off/