Check the Default Trace

Often times while troubleshooting an issue, you'll want more details than what you can find in the application log or SQL Log.  In the background, SQL Server runs a default trace which includes a lot of items to help with troubleshooting including (but not limited to) errors, warnings, and audit data.  I often run the following script as a quick way to find additional details for "ERROR" items from the default trace.

DECLARE @TrcFile NVARCHAR(256);

SELECT @TrcFile = CONVERT(NVARCHAR(256), value)
FROM   Fn_trace_getinfo(DEFAULT)
WHERE  property = 2;

SELECT starttime,
       databaseid,
       transactionid,
       hostname,
       clientprocessid,
       applicationname,
       loginname,
       spid,
       severity,
       servername,
       eventclass,
       error,
       databasename,
       loginsid,
       requestid,
       eventsequence,
       sessionloginname,
       textdata
FROM   Fn_trace_gettable(@TrcFile, DEFAULT)
WHERE  error IS NOT NULL
ORDER  BY starttime; 

If your system is running more than one trace you may recieve the following error:

Msg 567, Level 16, State 5, Line 7
File '' either does not exist or is not a recognizable trace file. Or there was an error opening the file.

If you do, manually run the following:
SELECT CONVERT(NVARCHAR(256), value) 
FROM   Fn_trace_getinfo(DEFAULT) 
WHERE  property = 2; 

...and manually copy in the file location for the @TrcFile variable, like so:

SELECT @TrcFile = 
'D:\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\Log\log_2470.trc' 
...


One other item of note is that SQL keeps 5 rolling files for the default trace.  This cannot be modified, as far a size or number of files.  You can however query all of them.  In the above example the following five files could all be used:

'..\log_2466.trc' 
'..\log_2467.trc' 
'..\log_2468.trc' 
'..\log_2469.trc' 
'..\log_2470.trc' 

*Microsoft says:
This feature will be removed in a future version of Microsoft SQL Server. Avoid using this feature in new development work, and plan to modify applications that currently use this feature. Use Extended Events instead.

https://msdn.microsoft.com/en-us/library/ms175513.aspx

The default trace is still available through SQL Server 2016.