Voice of the DBA

Tag: administration

  • Balloons and Data

    This editorial was originally published on Aug 8. 2013. It is being re-run as Steve is out of the office.

    We see a lot of questions at SQLServerCentral repeated over the years as workers new to the platform encounter some of the issues that many of us have dealt with repeatedly over the years. One of the more common questions that I see in the SQLServerCentral forums has to do with tempdb growth.

    Many people coming to the SQL Server platform realize tempdb is a workspace, but they don’t always understand how it works. Many times I see questions where people don’t understand why tempdb will grow from it’s default size. Even more surprising is that they don’t understand why it doesn’t shrink back to the default size. After all, it contains temporary information, and when it’s not being used, the size should shrink, right?

    If only that were true, it would make administration simpler, though performance might be worse. Many people view the files in a computer like balloons. We add data and they grow. We remove data, and they shrink. However that’s not the model for SQL Server files. Our mdf/ldf/ndf files are allocations, and once we allocate space, we don’t remove it if no data is present. The allocations are available for the next time we need them, minus the time we spent requesting and receiving the allocation from the Operating System.

    The space you need for tempdb is the peak space you need for the largest set of temporary operations in your workload. Even if all your data sets are small, say 100kb, if you have 1000 of them occurring at once, you’ll need 100MB of space. With sorts, intermediate worktables, and more, you could easily have an average data set above 10kb, which is why your tempdb size might need to grow.

    Just like with any other database, you need to monitor the size and load for tempdb. Adjust it as needed, based on your requirements, so that it will be properly sized each time to start the instance. The administration level isn’t high for tempdb, as long as you have monitoring in place and you periodically perform the administration to appropriately size your system.

    Steve Jones

  • Using Trace Flags

    At SQLServerCentral, we recently published a list of all the trace flags from Konstantin Taranov. It’s a great list, and thanks to Konstantin for compiling it. While I haven’t often used trace flags, there are some that have really helped me at various times when I needed to change SQL Server behavior. If you aren’t sure what trace flags are, Erin Stellato of SQLskills wrote a great post recently. I see trace flags as feature flags. The development team can allow us to experiment, test, and use functionality at our discretion, or ignore it.

    This week, I wanted to ask how many of you are using Trace Flags right now. Do you have any running in code or set for startup on your instances? If you don’t know how to do this, we’ve got a short piece to help you.

    In Erin’s post, she notes that SQLskills only recommends three trace flags (depending on version) for their customers. In general, I think that’s good advice. There is a risk with using flags, and certainly I would be wary of using without substantial testing. I do think Erin’s list is good, and you might consider using those. I also become wary about trace flags that aren’t embedded directly in code. I think these trace flags end up being hidden from anyone troubleshooting issues. After all, how many of you actually go to the Configuration Manager or the Services Applet and look for parameters?

    I expect that most of you don’t run trace flags on your instances. There may be some of you that have never heard or, or used, a trace flag in your career. That’s fine, though I hope you use today to a) let us know, and b) educate yourself to ensure you know how to enable a flag you need one. If nothing else, add one to a test instance, and ensure you have the skills to actually make the change.

    Steve Jones

    The Voice of the DBA Podcast

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

  • Global v Session Trace Flags

    I wrote a short article on enabling and disabling trace flags. You can read it, but I didn’t really discuss the implications of session v global trace flags, which is something I’d like to do here.

    In the article, I set trace flag 3226 for my session. This showed that a second backup wasn’t in the error log. Note the image below doesn’t have a backup message after (above) the trace flag change entry. You’ll have to trust me that I ran the backup, enabled the traceflag, and then re-ran the backup to get this image.

    2017-03-02 11_54_50-Log File Viewer - ._sql2014

    However, if I have a backup job, as I do here, does the trace flag affect this? This is, after all, run by SQL Agent, which would be a different session.

    2017-03-02 12_36_08-Job Step Properties - backup

    It turns out that the session trace flag doesn’t affect this. I ran the backup job and there was a message in the error log.

    2017-03-02 11_56_19-Log File Viewer - ._sql2014

    To suppress this, I’d have to use DBCC TRACEON (3226, –1) or put this in the startup parameters to ensure none of these messages appear.

  • Backups Aren’t Backups Until a Restore Is Made

    One of the interesting things I saw in the recent GitLab outage and data loss was the fact that none of their backups were available. They use PostgreSQL and I’m not familiar with the ways in which the modern PostgreSQL engine handles backups or the options you have, so I’m not knocking either GitLab or PostgreSQL. It’s possible one or the other had fewer options than we do with SQL Server with our full, differential, log, and filegroup backups, all during live database activity.

    There was a live stream and a Google Doc open during the incident, showing the response by their employees (and plenty of Hacker News comments). Kudos to GitLab for their bravery and transparency in showcasing their mistakes and choices. I’ve been in similar situations, and the war room can be chaotic and stressful. There have been no shortage of times when someone makes a mistake under pressure and we scramble to recover from the damage. I’ve made those mistakes and understand how they happen when you get desperate and are tired. This is one reason I’ve usually insisted that when an incident is declared, I immediately send at least one person home to rest. I never know what time I’ll need to get them back.

    In reading the notes, there are a number of issues. One of the respondents doesn’t know where the once a day backups are stored (1). The location they check has files only a few bytes in size, so backups might not be working (2). No disk snapshots in their Azure space for database servers (3), though the NFS servers get them. The snapshot process is incomplete, in that once snapshots are made, some data is removed from production, and will be lost in this recovery (4). The backups to S3 don’t work (5). All of this results in a backup that is six hours old being restored. For people that commit code often, this could be a lot of data. Hopefully there weren’t too many merges and branch deletions in this time for customers.

    A backup doesn’t matter. A restore matters. It doesn’t matter what backup process you have, if you don’t test it, then you don’t know if you can recover. In fact, with databases (really any system), you need to test the restores regularly because the backup process can fail. I learned this early in my career when one of our admins realized his fancy tape changer that let him only change tapes once a week was broken. The drive had stopped writing and he never noticed.

    Not only is it important to monitor that the backup process runs, it’s important to ensure the backup files exist, where we expect them to exist. If this is a remote location, you need monitoring there as well. It’s also important to restore backups regularly. Ideally you’d test every one, but at least get a regular rotation of testing once a week to ensure your process is working.

    If you don’t, then you risk not only data loss, as GitLab experienced, but an RGE. That’s a resume generating event, and it’s something none of us would like to experience.

    Steve Jones

    The Voice of the DBA Podcast

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