Voice of the DBA

Tag: administration

Backup Log to Nul– #SQLNewBlogger
Another post for me that is simple and hopefully serves as an example for people trying to get blogging as #SQLNewBloggers. This is also a part of a basic series on git and how to use it.

There are times when you might be working on your demo/lab system and you generate a lot of tranasaction log activity. This isn’t data you want to save, so perhaps you want to remove the activity without saving it.

There are a few choices:
1. Run a normal log backup to a file, then delete the .trn file.
2. Switch to simple mode
3. Run a log backup to nul
The first one is easy, but it’s a pain. I have to go to explorer, or open a VM, delete the file once I find it. The second one is what I’d suggest. In fact, as soon as you install SQL on a lab system, set model to Simple.

The third item is valid, and I ran across this recently. When you use this syntax, make sure you use “nul” and not “null”. We are trying to send to /dev/nul, which is nowhere. If you backup here, then nothing happens. You can use this command:

BACKUP Log sandbox2 TO DISK = N’nul’

This will run a backup, and discard all of the backup data. When I say discard, I mean it’s not written anywhere.

However, this is a real backup. It’s marked as such. This breaks a log chain, and you can do this with a full database backup as well, which means you really need another full backup after this to reestablish a baseline.

Again, I ran across this, but it’s not what you want to do. If you need to clear the log, use

ALTER DATABASE xx SET RECOVERY SIMPLE

ALTER DATABASE xx SET RECOVERY FULL

and take a full backup.

More thoughts from Gail Shaw.
July 31, 2017
Database Mirroring Needs FQDNs
A quick basic post, and one that I’ve forgotten. Since blogging is a good way to remind myself of things, here goes.

I was testing Database Mirroring (DBM) recently for an upgrade situation. I’ve set up it up in the past, but since it’s deprecated, I’ve moved on to working with Availability Groups (AG) for the most part. However, mirroring might still be in use for you, or you’re looking to perform a simple rolling upgrade, and DBM works well.

I restored a database on a new instance, opened the firewalls for 5022, and then went through the mirroring wizard. Once I was done, I enabled mirroring on the secondary database (the one restoring) and that worked fine.

Then I ran this on the primary:
```
ALTER DATABASE Baseball SET PARTNER = 'TCP://192.168.1.201:5022'
```
After a few minutes, I got an error:

Msg 1418, Level 16, State 1, Line 10
The server network address “TCP://192.168.1.201:5022” can not be reached or does not exist. Check the network address name and that the ports for the local and remote endpoints are operational.

I tried all sorts of things, including shutting off firewalls, and disabling the rebuilding mirroring. My endpoints were fine, the domain accounts running the instances had access, but it wouldn’t work. I tore down mirroring and added it back, verifying each machine could see the other by name. I tried again.
```
ALTER DATABASE Baseball SET PARTNER = 'TCP://SQL02:5022'
```
I knew I had a problem when this took more than 5sec to respond. Again, an errror.

Msg 1418, Level 16, State 1, Line 10
The server network address “TCP://SQL02:5022” can not be reached or does not exist. Check the network address name and that the ports for the local and remote endpoints are operational.

Finally I tore things down again, deleted endpoints, but this time I connected to the instance with an FQDN and configured things. I made I connected to the mirror with an FQDN as well. Finally things worked:
```
ALTER DATABASE Baseball SET PARTNER = 'TCP://SQL02.HOME.XXXXX.COM:5022'
```
I must have read the documentation numerous times, each time reading the FQDN, but somehow thinking that couldn’t be the issue in a small network.

Hopefully this blog will help me remember.
July 3, 2017
Handling Data Corruption

I wrote about dealing with data loss recently, ansking what your plans are for a situation. Some of you might rely on digital systems, some rely on humans, and some just accept some data loss. All of those are valid responses, depending on your environment. Today I wanted to take another step on this journey.

Imagine that one of your systems has data corruption. Any system you have, but pick one that might cause you some anxiety. Maybe you discover it from a failed query, maybe from an entry in the error log, maybe from a DBCC execution (I hope you use these). You have no idea how long the corruption has been there. Therefore, you don’t know how many backups are valid.

What do you do? What are your plans? Certainly there could be data loss potential here. There will likely be some questions about why this wasn’t known immediately, and maybe you’ll experience some embarrassment in the moment. This will be a stressful moment in your career, and one with which you will want to be able to cope.

The question today is to get you to prepare a bit and game the possibilities. Some of you might never experience corruption, but you never know, so it does make some sense to think in advance and anticipate the reaction you want to have in the moment.

We always want to be prepared, but we won’t have actual preparation plans (scripts, documents, etc) for all situations. It’s much easier to think about the possibilities, rather that actually build plans for every possibility. Maybe you want to even discuss and debate them among your peers. Today’s question is good practice for the real situation, and great mental preparation for a real event. Plus it can be a bit fun to brainstorm, have someone shoot holes in your ideas, you do the same for them, and then come up with another solution.

Steve Jones

The Voice of the DBA Podcast

Listen to the MP3 Audio ( 3.0MB) podcast or subscribe to the feed at iTunes and Libsyn.

June 29, 2017
Adding Performance Counters back for SQL Server
I had a strange situation the other day, where a number of things went wrong with my instance. First, I lost permissions to detached databases. The SID was listed in the file permissions, but apparently unlinked to an account.

Next, I went to add an alert, and I only had the XTP counters.

The counters are also missing in Performance Monitor. What is interesting is that I show the correct SQLAgent counters for each of my three instances.

A quick search found me this blog on MSDN, where it recommends the following:
```
unlodctr mssqlserver

lodctr perf-mssqlserversqlctr.ini
```
I had a named instance, so for me I entered:
```
unlodctr mssql$sql2016

lodctr perfMSSQL$SQL2016sqlctr.ini
```
from an elevated command prompt. Running the last command again shows the counters loaded.

I also checked my registry, which appeared to be fine:

I next found another blog that noted I might need to resynch WMI, so I ran winmgmt, using the PID from Task Explorer (details tab):

I didn’t see counters at first, but I restarted the instance. Once that was done …

A nice fix, and one I probably won’t forget after this blog.
June 26, 2017