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.
SQL Server and CPU core usage How many CPU cores can SQL Server support should be a simple question, you might think. However, judging by how often this question is asked on the SQL Server forums its clear that there is a lot of confusion about exactly how many CPUs a particular edition of SQL Server can use.
The problem is due mainly to incorrect assumptions about Microsoft’s licencing policy, combined with the prevalence of multi-core CPUs.
[NOTE: This is an old post that is no longer actively maintained and covers versions of SQL Server that are no longer in support. I’m leaving it up because it’s generated a lot of comments over the years which may contain useful information for those folks having to maintain legacy systems.]
Correctly configuring SQL Server memory settings is critical for server performance, but one of the things that I frequently come across when reviewing SQL Server installations whilst working in CSS for MS, is just how many of them had not been set up with appropriate memory configuration settings, or, as in many cases, not set up in the way the administrators of the system had assumed they were; usually the DBAs thought the system was set up to use all e.