The SQL Server Error log At some point every DBA will have had to look at the current SQL Server error log using their favourite text editor. However, as the default error log file is literally just called errorlog (with no file extension), there’s the awkward process of selecting the application with which to open the file every time you need to view it, with no option to associate errorlog with your favourite text editor thanks to the lack of file extension.

Working out when a database backup or restore operation will take to complete can be a real pain. Fortunately, the sys.dm_exec_requests DMV has some useful columns which can help us predict this. Here’s the query I put together whilst waiting for my backup performance tests to complete: SELECT r.session_id, CONVERT(NVARCHAR(22),DB_NAME(r.database_id)) AS [database], r.command AS [backup_process], s.last_request_start_time AS [started], DATEADD(mi,r.estimated_completion_time/60000,getdate()) AS [finishing], DATEDIFF(mi, s.last_request_start_time, (dateadd(mi,r.estimated_completion_time/60000,getdate()))) - r.wait_time/60000 AS [mins left], DATEDIFF(mi, s.

The single biggest clue to the source of a performance problem will be from something referred to as the wait stats. What are waitstats? In short wait stats are statistics gathered on what resources SQL Server is waiting on internally whilst executing queries. Why are they useful? They can often provide very quick and objective evidence of a performance bottleneck by showing cumulative wait times that SQL Server spent on getting access to the CPU, memory or disk access as well as numerous other internal resources.

I’m starting off this series of posts with a discussion about partition offsets. Diving off at the deep end a bit perhaps, but if your disk setup is not based on firm foundations, you’re setting yourself up for one of the most common and easily-avoidable performance issues right from the off, and a IO performance hit of 20-30%. The partition offset issue is relevant to any disk partitions created prior to Windows Server 2008.

Many aspects of a database system’s configuration will affect the performance of queries running on that DBMS. However, there is one single component that has the greatest impact on DBMS performance, and that, of course, is disk (IO) access. It’s a broad topic and all aspects of it are pretty well covered in various blogs and technical articles, but there’s not much out there that gathers it all together. The intention of this series of posts is to try consolidate some of this information and focus in on some of the functions and procedures that can be used to monitor, troubleshoot and configure SQL Server disk configuration and most, importantly, disk performance.

The SQL Server default trace is useful! The SQL Server default trace (introduced from SQL Server 2005 onwards) is a background sql server 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 you don’t have to do put any effort into enabling or managing it.

How to upgrade your SQL Server edition (without reinstalling) With the updated SQL Server 2008 installation program, upgrading SQL Server editions has finally been made as straightforward process as it always should have been. In SQL Server 2005 upgrading/changing the edition involved running the setup program from the command line with a multitude of specific parameters set. This blog posting covers SQL Server 2005 and SQL Server 2008. Before you start, make sure you are on a supported upgrade path as described in the edition upgrade matrix (for SQL Server 2005) on Microsoft’s website.

SQL Server listeners Verifying and troubleshooting SQL Server listener connectivity via tcp, named pipes or shared memory connections is a key component in a DBA’s armoury but often overlooked until it’s too late and application teams call up complaining their apps can’t connect. SQL Server can listen for connection requests via multiple listeners, such as TCP/IP, shared memory or named pipes, but sometimes it’s necessary to check connectivity by a specific listener.

With a little help from SQL Server’s tracing tools you can easily get to the bottom of almost all login failures (18456 errors). If the login failure is against SQL Server 2005 or above and the default trace is running just go to my SQL Server default trace post. If you’d prefer to use extended events (as SQL Profiler is now deprecated), go to Capturing login failures via SQL Server Audit.

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 table or sys.dm_tran_locks would show resources being locked by a SPID with a value of -2.