2

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.

slayernoah
  • 4,382
  • 11
  • 42
  • 73
  • 2
    You really shouldn't use auto shrink anyway. – DavidG Oct 01 '14 at 13:03
  • 1
    They should just rename AutoShrink to AutoCrippleDatabase. Turn off that disaster and this issue will go away. – Sean Lange Oct 01 '14 at 13:32
  • Use DMV to view the query text and plan when it is running .. `sys.dm_exec_query_stats` cross apply `sys.dm_exec_query_plan` cross apply `sys.dm_exec_sql_text` – Tak Oct 01 '14 at 14:39
  • I realize `AutoShirnk` results in fragmented indexes etc. However, i'm not sure why given the text in the TechNet link in the question, it is interfering blocking and being blocked by other connections to the database – slayernoah Oct 01 '14 at 15:07
  • @t_m I will try that and let you know – slayernoah Oct 01 '14 at 15:08
  • @t_m: Could you elaborate on how to plan when the autoshrink process runs using DMV? – slayernoah Oct 06 '14 at 22:24
  • @slayernoah .. It's not about planning but more monitoring. This link [Monitoring scripts with DMVs](http://www.mssqltips.com/sqlservertip/1861/sql-server-monitoring-scripts-with-the-dmvs/) shows how to log the DMVs to a table and review the information. From the information gathered you can then take action. I totally agree with others to turn OFF AutoShrink. – Tak Oct 07 '14 at 13:52

1 Answers1

1

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/

SouravA
  • 5,147
  • 2
  • 24
  • 49