FlushCache messages might not be an actual IO stall

SQL Server's FlushCache message might be not actually be an IO stall and using SQL Server's perfmon counters we can isolate another reason for this warning message.

At a client site recently a SQL Server 2012 (Enterprise, SP1 + CU#9) server with 512GB RAM and 80 cores (40 cores assigned to SQL Server) that had been patched and restarted displayed the following symptoms after an index reorg operation was restarted: CPU saturation, with all cores available to SQL Server (but not all […]

The SQL Server default trace

The SQL Server default trace reveals a lot about the recent history of the system. Find out how to troubleshoot common database issues using it.

The SQL Server default trace is useful! The SQL Server default trace (introduced from SQL Server 2005 onwards) is a background trace that runs continuously and records event information that, despite the adverse comments on the web about its value, can be useful in troubleshooting problems. Not only that, but it’s there out-of-the box so […]

Troubleshooting SQL Server listener connectivity

Verifying and troubleshooting SQL Server listener connectivity is a key component in a DBAs armoury but often overlooked until now this post shows how to confirm connectivity to SQL Server using any of its connectivity options such as TCP/IP, Named Pipes or Shared Memory.

SQL Server listeners Verifying and troubleshooting SQL Server listener connectivity is a key component in a DBAs armoury but often overlooked until now (hopefully!). SQL Server can listen for connection requests via multiple listeners, such as TCP/IP, shared memory or named pipes. Sometimes it’s necessary to check connectivity by a specific listener. The first place […]

Orphaned MSDTC transactions (-2 spids)

Understanding and troubleshooting orphaned MSDTC transactions

SPIDS with a value of -2 Any dba looking after a server that takes part in a distributed transaction will probably have come across situations where a SPID that does not show up in master..sysprocesses or sys.dm_exec_sessions (depending on your version of SQL Server) ends up blocking other users. Looking at sp_lock or the syslockinfo […]