If a disk was formatted incorrectly i.e. a SQL Server data volume,  but you want to check what allocation unit size was selected  when the disk was formatted, use this…

From a CMD window, type FSUTIL FSINFO NTFSINFO <drive letter:>

e.g . FSUTIL FSINFO NTFSINFO D:

and look at the line: Bytes Per Cluster

The accepted rule for all SQL Server drives is 64K which reads as 65536 bytes

If you are monitoring for deadlocks on your server when you setup the Data Collector, the default schedule for the upload jobs is to upload both jobs at the same 15 min interval. This causes deadlocks. It is easily fixed by staggering the schedules to 1 min apart.

I would recommend a separate MDW database (on your same CMS) for each instance you monitor. The reason for this is that there is a purge job created as part of the MDW setup. If you have 50 servers reporting to the same database then it is going to be extremely difficult to complete the purge in a timely manner each day and your database will grow out of control.

Therefore, using the instmdw.sql script located in:

C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\Install

Run this script each time for each MDW database you create. Just make small customisations for the names of data files and the DB name. You can name the database anything you like as you reference this DB in the Data Collector setup. I use MDW_ with a suffix of the servername in our environment.

(to be continued)

Setup a server-side trace by using SQL Profiler starting with a blank template and add the following items…

Errors and Warnings:

*Attention – this is the actual timeout event. This only tells you a timeout has occurred. You need the following and associate this event with what was running when the timeout occurred.

Stored Procedures:
*RPC:Starting
*SP:StmtStarting

TSQL:
*SQL:BatchStarting
*SQL:StmtStarting

Normally we would use the completed objects but remember with a timeout they don’t complete so we want the starting objects. If you want to filter the trace for a particular database then add this in now. Click Run and then Stop the trace. Go Export > Script Trace Definition and save the script to a file. Using SSMS, open the script you just created and connect to the server concerned. Edit the parameters for @maxfilesize and the sp_trace_create command. I would use the file rollover option to keep a predetermined number of files that you can overwrite on a daily basis without the files getting too large and also consuming all your disk space. Once running, it is possible to query the live trace file by using one of the built-in SQL functions… fn_trace_gettable()
You pass in the filename and then the number of files to read, or DEFAULT to read all files. e.g. SELECT * FROM fn_trace_gettable(‘D:\MSSQL\Trace\MyTraceFile.trc’, DEFAULT)

The EventID for a timeout is 16 – this can be found by querying sys.trace_events

After the trace has been running a while, run a query to see if there have been any timeouts. For example…

SELECT *
FROM fn_trace_gettable('D:\MSSQL\Trace\MyTraceFile.trc', DEFAULT)
WHERE EventClass = 16

if you get a result set back, note the times and correlate that with a particular file range so that the can query just that file and run something like the following…
SELECT b.* FROM
FN_TRACE_GETTABLE('E:\MSSQL\TimeoutTrace_30.trc', 1) AS a
INNER JOIN FN_TRACE_GETTABLE('E:\MSSQL\TimeoutTrace_30.trc', 1) AS b ON a.SPID = b.SPID
WHERE a.EventClass = 16
AND b.StartTime > DATEADD(ss, -30, a.StartTime)
AND b.StartTime <= a.StartTime

What is happening here is we are performing a self join on one of the trace files. In the first result set is the Attention event (16) and joining on the SPID of that event to the same file getting all the events in the last 30 seconds on the same SPID. This should reveal what command was running when the timeout occurred. You can use this information to diagnose further what the problem is but at least we now what was running at the time