Voice of the DBA

Tag: administration

Two Types of Performance Counters
I had an issue where an instance of SQL Server was only showing the XTP (In Memory) performance counters. None of the other SQL Server counters were available, so I followed the procedure I’d written about previously. Once that was completed, I restarted SQL Server and looked.

No counters, still.

Hmmm. It was then I scrolled further and realized that I had the SQL Server Agent counters, but not the database engine ones. I looked closer in the Performance folder for my instance and noticed this:

There are two .ini files. There are
- perf-<named instance>sqlcrt.ini
- perf-SQLAgent<instance>sqlagtctr.ini
These refer to the counters for the database engine and the Agent subsystem. I had unknowingly copied the Agent file for the lodctr.exe call rather than the other one.

Lesson learned. I had to re-run the procedure with the engine ini file and restart the instance again.

If you need to add counters, make sure you load both and run a restart, otherwise you might incur more downtime than you expect.
June 11, 2018
No @@ServerName on Linux
I setup a new instance of SQL Server on Linux some time ago. At the time, the Linux machine didn’t have any Samba running, and no real “name” on the network. As a result, after installing SQL Server I got a NULL when running SELECT @@SERVERNAME.

The fix is easy. It’s what you’d do if you had the wrong name. You run:
```
sp_addserver ‘Ubuntu’, Local
```
In this case, Ubuntu was the name I gave my machine under the Samba server, since that’s the OS. I don’t plan on adding more for now, so this is fine.

After the chance, I couldn’t restart the instance from SSMS, so on the host itself, I ran a
```
sudo systemctl stop mssql-server
sudo systemctl start mssql-server
```
That completed, and my @@servername is now working.
June 7, 2018
Getting SQL Agent going for SQL Server on Linux–#SQLNewBlogger

Another post for me that is simple and hopefully serves as an example for people trying to get blogging as #SQLNewBloggers.

It’s actually a simple procedure, but I thought I’d write a short note to help me remember. The procedure is documented in Books Online.

When I first started playing with this version, I noticed that SQL Agent was disabled. That’s not great, since SQL Agent is a great tool for various tasks in SQL Server. I can’t start the agent from here, as the underlying implementation is different, and I’m not really a host OS admin when connecting in SSMS.

After checking which patch level I was at (CU6), I changed to my Linux console, and ran the configuration utility. For Linux, this is mssql-conf.

The next step is to run the command listed to restart the system. I actually just ran

sudo sysmctl restart mssql-server

Once this was done, I refreshed SSMS.

This appears to work, but let’s test it. I’ll create a new job that does a backup of a database. This should be simple, and I’ll use defaults, just a filename for a full backup. I’ll use this command:

backup database dbaadmin to disk = ‘dbaadmin.bak’ with init

I save the job and run it. Sure enough, I have a backup.

Tada, now I can move forward with work.

SQLNewBlogger

This is a fairly simple thing to do, but the writing helps me remember, but more importantly, I can document that I’ve done some learning here and experimenting.

The next person thinking about interviewing you wants to know that you can learn and solve problems.

May 31, 2018
Checking CHECKDB

One of the recommendations for SQLServer is that you run a DBCC CHECKDB regularly on your system. Those individuals that have worked with SQL Server for some time and worked on learning more about the system know to schedule this check, and even ensure that you restore backups to check them on a regular basis. Note, running these checks on secondary systems may or may not be valid. You might want to read Paul Randal’s post on this.

Experienced DBAs know that when corruption strikes, you can find yourself in a very problematic situation. Corruption can be captured in backups, which means that if it appears, it’s entirely possible that if this has been in your system for some time, all the backup files you have contain the same corruption. If this is inside a table, you might end up losing data, which is never what any of us want.

Those that might not be familiar with SQL Server, or have never learned about regular maintenance might not realize that DBCC is needed. In fact, they might not know if CHECKDB has ever been run on their system. It used to be hard to find this, but things became easier over the years. When checkdb runs, it does write a note in the error log, but that’s not a great way to track this information for administrators, especially if the instance has been restarted. Without a set of tools in place, this becomes a project for anyone that starts working with a new system to track down.

That changes in SQL Server 2016 SP2. There’s a new property for your database, a parameter for DatabasePropertyEx(). The value is LastGoodCheckDbTime, which in this case, you can get the last datetime when a checkdb was run. To me, this should be some sort of alert that your monitoring system has in place that lets you know if this value is too old.

The problem is that for many of us, we may run DBCC CHECKDB on another machine, perhaps on a restored copy of production, so how can we track this? Is there a way that would make sense? I’d like to think that perhaps any script testing a backup should connect back to the primary database and update this value. There isn’t a specific place for this, but I certainly could see using extended properties for this. At least then we could more easily determine if we haven’t been regularly checking a particular database for corruption. If you have other ideas, I’d be interested in how one might actually track this.

For now, use this property to ensure you’re checking those databases where you do execute CHECKDB, and if you aren’t sure if you should do this, you should. Go learn about what CHECKDB does and why it’s important for your production systems.

Steve Jones

May 22, 2018