Category: Administration

Rolf Tesmer shows how proportional fill for files in a filegroup works:

When multiple files are involved, and if these are ideally located on different physical spindles on the underlying disk subsystem, then a rather nicely performing data striping can be achieved for the database.  If proportional fill kicks in and starts to focus on files with more free space then you may get hot spots for those files.  However nowadays with auto-tiering SAN’s, SSD and (abstracted) cloud storage (for IaaS deployments) this is beginning to matter less and less.

This is a good introduction to proportional fill, including what happens when you add files later.  If you are counting on proportional fill, it’s a good idea to make sure all files are the same size and grow them all at once.

Jon Morisi has a script to find orphaned users:

With the SQL Server 2005 end date approaching, you may find yourself migrating databases.  One of the gotchas with any database migration is orphaned users.  Below is a script I put together, and have been using for years, to help me resolve issues with orphaned users:

I’m not a fan of the sp_msforeachdb in there because there are issues which can cause “each” database to skip databases.  If you have installation authority, Aaron Bertrand’s sp_foreachdb is a better alternative, and if you need to do Insert/Exec calls, there’s a newer version which removes the INSERT/EXEC in that procedure.

Kevin Eckart shows you users with recent password changes:

There may come a time when you need to generate a list of SQL logins and the last time their password was changed. By using the LOGINPROPERTY of the name in sys.server_principals we can generate such a list.

Note: This will only work with SQL logins.

If you want (or need) to know that passwords are being updated but can’t turn on password policies, this at least answers the initial question.

Erin Stellato troubleshoots a cumulative update installation problem:

The initial email didn’t have a lot of details, so I started asking questions to understand what version was being installed, the environment configuration, etc.  Turns out this was a two-node Windows Server Failover Cluster (WSFC) with multiple SQL Server 2012 instances installed, and one of the instances was still running on the node this person was trying to patch.  To be clear, the two nodes were SRV1 and SRV2, and the instances were PROD-A and PROD-B running on SRV1, and PROD-C which was running on SRV2.  This person was trying to install the cumulative update on SRV2.

The behavior Erin describes is a little bit crazy, but at least there’s a good explanation and way to solve the issue.

Erik Darling has a short checklist of some things to check before upgrading SQL Server 2005:

MIND YOUR COMPATIBILITY LEVEL

When going to 2014 (as of today, 2016’s RTM hasn’t been announced yet), you’ll have to decide whether or not the new cardinality estimator suits you. There’s not a cut and dry answer, you’ll have to test it on your workload. If you’d like some of the more modern SQL features added to your arsenal, you can bump yourself up to 2012-levels to get the majority of them.

The interesting survey would be, among people who still have SQL 2005 installations, how many will move as a result of Microsoft declaring end-of-life for 2005.  My expectation is a fairly low percentage—by this point, I figure a at least a strong minority of 2005 instances are around for regulatory or compliance reasons (e.g., some piece of regulated software was certified only for 2005).

Jes Borland wants us to stop copying and pasting files between servers:

IT professionals (and amateurs), it’s time we had a chat. It’s time to stop dragging and dropping (or copying and pasting) files between servers and/or workstations.

It’s clumsy. It’s childish. It uses memory on the server.

Oh, and there’s a really easy tool to copy files built into Windows – Robocopy.

The syntax is pretty easy and robocopy handles small files well.  Check out Nic Cain’s comment, though, if you’re going to copy large files in production.

Aaron Bertrand has part 3 in his series on identity columns:

This post investigated two potential workarounds to either buy you time before changing your existing IDENTITY column, or abandoning IDENTITY altogether right now in favor of a SEQUENCE. If neither of these workarounds are acceptable to you, please watch for part 4, where we’ll tackle this problem head-on.

This is your weekly reminder to plan for appropriate data sizes.

Andy Galbraith shows how to collect and store Perfmon counters:

As you can see, Page Life Expectancy (PLE) on this graph dips, gradually climbs, and then dips again.  With a collection every five minutes you may not catch the exact peak – all you know is that the PLE was 50,000 at 12:55am and then only 100 at 1:00am on 03/13.  It may have climbed higher than that before it dipped, but by 1:00am it had dipped down to around 100 (coincidentally at 1am the CheckDB job had kicked off on a large database).

If you really need to know (in this example) exactly how high PLE gets before it dips, or exactly how low it dips, or at what specific time it valleys or dips, you need to actively watch or set up a collector with a more frequent collection.  You will find that in most cases this absolute value isn’t important – it is sufficient to know that a certain item peaks/valleys in a certain five minute interval, or that during a certain five minute interval (“The server was slow last night at 3am”) a value was in an acceptable/unacceptable range.

Andy also gives us a set of counters he uses by default and how to set up automated counter collection.  Left to the reader is integrating that into an administrator’s workflow.