Category: Administration

Tracy Boggiano continues her series on configuring Telegraf:

The solution for this is fairly simple now that you have setup Part 1 of this series.  You can download the Windows conf file for Telegraf from my presentation.  Below are the important pieces of the file. The main part of the OUTPUT PLUGINS being to place the data in the InfluxDB database. The data will be housed in the same database as our SQL performance metrics. Next, you can collect any Windows Performance Counters you want and group them into a “Measurement”. I’m using the dashboard that is on the Grafana website along with the performance metrics they have set up to be collected.

Click through for Tracy’s setup script to get an idea of which Perfmon counters she’s tracking.

Kenneth Fisher wants to delete a lot of rows:

I recently had the task of deleting a bit over a billion rows from a table. Now I could have done just this:

DELETE FROM tablename WHERE createdate >= '1/1/2017'

But I have a few problems here. The table has no index on createdate, potentially causing problems with tempdb (the sort on createdate). Although in this case tempdb is pretty large because of some large batch work done at various times. I’m also going to be deleting > billion rows of ~6 billion which is probably going to fill up the log of the database (which fortunately isn’t in use yet) and end up rolling back my delete anyway. Even if I don’t fill up the log, I’m still going to bloat it pretty badly (autogrowth). And last, and anything but least, this is on a production server. Even if this database was on its own drive (meaning growth of the log can’t cause a problem with any other databases) that tempdb thing (let alone other resource usage) is going to be an issue.

Read on to see how to delete in batches.  My pattern is to have an explicit transaction within the WHILE loop, opening and closing for each deletion operation.  That has worked pretty well in the past when deleting large numbers of rows from a table.  It might also make sense to put a temporary filtered index on the table, dropping it afterward.

Jana Sattainathan has a few considerations before running a detach-attach operation:

On the detached database files, the file permissions change when detaching. i.e., The AD account (your AD account) performing the detach operation becomes the owner and the only person with permissions to the file. It does not inherit the permissions from the folder. Oddly, if you just detach and reattach, it would attach fine even though SQL Server Service account does not have any explicit permissions on the files. However, others cannot attach a file that you detached even if they are on the Administrators group until they are explicitly granted permissions on the files themselves. This is well explained in this MSSQLTips article. Quoting the article, if you want to retain file permissions on detach, set the trace flag 1802.

“SQL Server 2005 introduced trace flag 1802 which retains the database files permission after the detach operation. The trace flag is tested and still applicable with SQL Server 2016.”

Click through for several tips of similar ilk.

Warren Estes explains how he keeps track of his servers using a Central Management Server:

We use a CMS server for each domain and I can’t imagine life without it. Kind of like when I discovered Amazon prime, or bought my first memory foam mattress.

The real magic of a CMS comes from being able to push jobs, or evaluate policies, on any server (targets) you want.

You can also execute T-SQL against all, or a subset of servers with either registered servers or CMS.

There are some caveats to look out for like collation differences and version specific DMVs when running queries across instances. Also security needs to be addressed. However, that is outside the scope of this post. You can find that information in the links in the first section.

I liked CMS when I had to deal with a dozen instances.  With hundreds of instances, I wouldn’t want to administer anything without one.