Category: Administration

Lori Brown points out that there are some SQL Server service tables which can bloat your msdb database:

I recently received a panicked call from a client who had a SQL instance go down because the server’s C drive was full. As the guy looked he found that the msdb database file was 31 GB and was consuming all of the free space on the OS drive causing SQL to shut down. He cleaned up some other old files so that SQL would work again but did not know what to do about msdb.

As we looked at it together I found that the sysmaintplan_logdetail table was taking all the space in the database. The SQL Agent had been set to only keep about 10000 rows of history but for some unknown reason the table never removed history. After consulting MSDN I found this code did the trick for truncating this table.

Lori’s focus here is on SQL Agent history, but don’t forget about things like backup history as well.

Solomon Rutzky tries to reverse engineer the action_id values used in SQL Server audits:

This post is, for the most part, a continuation of Server Audit Mystery 1: Filtering class_type gets Error Msg 25713. In that post I was trying to filter on the class_type field / predicate source (i.e. the object, or whatever, that the event is on). The tricky part was that class_type is supposed to be filterable on string values such as U for “User Table”, P for “Stored Procedure”, etc. But, the reality is that it has to be filtered using numbers, and that list does not seem to be documented anywhere. Fortunately, I was able to find the list of possible numbers, so all is well with regards to class_type.

When it comes to the action_id field, however, we are not so lucky. It, too, should be filterable on the string codes that are returned by sys.database_audit_specification_details and sys.fn_get_audit_file. But, just like class_type, only numbers can be used for filtering. And, like class_type, that list of numbers does not seem to be documented anywhere. But, unlike class_type, I cannot find any list of numbers (in SQL Server) that corresponds to the available actions. I did find a list of statements by looking at Extended Events info (since Audits use Extended Events):

SELECT *
FROM sys.dm_xe_map_values
WHERE [name] = N'statement_type';

but it wasn’t the correct list. So, this means that I have to go hunting for these values the even-harder way

Read on for some fun with binary tree search.

Warren Estes explains how connection pools work and troubleshoots a connection pooling issue:

When an application connects to a database it takes resources to establish that connection. So rather than doing this over and over again a connection pool is established to handle this functionality and cache connections. There are several issues that can arise if either the pool is not created with the same connection string (fragmentation), or if the connections are simply not closed/disposed of properly.

In the case of fragmentation, each connection string associated with a connection is considered part of 1 connection pool. If you create 2 connection strings with different database names, maxpool values, timeouts, or security then you will in effect create different connection pools. This is much like how query plans get stored in the plan cache. Different white space, capital letters all create different plans.

You can get the .NET pool counts from:
Performance Monitor> .NET data provider for SQL Server > NumberOfActiveConnectionPools

Click through for more information.

David Fowler continues his series on building a test lab:

Now I don’t want to get into the details of Quorum, there are plenty of great posts out there that explain it far better than I can but in a nutshell, each node in the cluster has a vote and we really want the total number of votes to be an odd number.  But we’ve only got two servers, does that mean that we need a create another server to make an odd number?  Well, no we don’t.  What we can use is what’s known as a file share witness, and that’s simply a file share that each of the nodes in the cluster can access.  That file share will effectively act as our third vote.

So first thing that you’re going to need to do is create a file share somewhere, the best place for that in our setup would be on the domain controller or somewhere that we know is always likely to be available.  So go and do that now, call it what you like but make sure that the servers are going to have full rights to it.  As this is just our own personal little test lab and we’re not too worried about best practices you could possibly open it up to EVERYONE (probably not a great idea in a production environment but not the end of the world if we want to be lazy in our own little play pen).

David also shows how to set up an Availability Group.

Kenneth Fisher recommends cycling the SQL Server error log regularly:

You’ll note I have it set to keep 32 files. This way I have at least a months worth of information. It’s probably more than I need, but for my personal box, that’s what I chose. For your company, it’s going to depend on your needs. You can keep up to 99 files but that seems a bit excessive to me. Of course, these files aren’t all that large (usually) so it’s up to you.

I’d go with a bit more than 32—if you have three service restarts in March, you won’t have a full month’s worth of information.  Regardless of the exact number you choose, it’s a smart idea to cycle this regularly.

Jana Sattainathan shows how you can find the configuration options used when installing SQL Server:

Let us say that you want to install SQL Server on another host say NewHost but you want it to have the same settings as another host/instance on say ModelHost. In fact, let us say that these are almost identical hosts with similar drive locations and such and you are positive that you want the exact setup on both.

The hard way is to look at what features ModelHost has and try to click through the installation wizard with the correct options selected/input.

Fortunately, there is an easier way to clone an installation. Even if you installed manually using the installation wizard, SQL Server still generates a configuration file with all the settings used (except passwords) for the installation. We can simply use that file from the ModelHost to drive the installation on NewHost

Click through to see where that configuration file is and how you can use it.

Adrian Buckman announces the SQLUndercover Inspector:

In a Nutshell:

The SQLUndercover Inspector is a configurable daily report written in SQL that will send you an email (or log the report to a SQL Table) showing you information about specific parts of SQL Server in HTML format including highlighted warnings/advisory conditions, the report has configurable thresholds and settings leaving you in control.

Click through to see what they track.