Voice of the DBA

Category: Blog

  • A Full Backup can Impact a Log Backup–MCM Prep

    In the early days of SQL Server you could not run a log backup while a full backup was running. In fact,you tried to schedule them apart from each other early in my career to prevent collisions. Nothing bad happens, but it does cause failures in your monitoring and those are annoying.

    In modern versions of SQL Server, you can run log backups and simultaneous full backups. They don’t collide or block each other, but there is a way that the full backup impacts a log backup.

    If you start a full backup, once all data pages are written to the backup device, the log records that were created since the start of the full backup are added to the full backup file. This allows the redo/undo process to complete and this gives you a full backup set that is intact as of the point in time in which the data pages finish writing.

    If you run a log backup, typically the log records are written out, and then the VLFs in the log file that were written to disk are marked as inactive.

    However since those log records are needed for the full backup to be complete, the log backup cannot clear those VLFs when the log backup finishes. That process still occurs, and technically this is part of the log backup, but it is deferred until the full backup completes.

  • Work Isn’t the Most Important Thing

    brokenpipe_cI had quite an adventure today, away from work. We had a pipe freeze in the house, a sick kid, and then a plumber call when the pipe broke. All in all, with school closed and kids home, I haven’t gotten much work done. My wife ended up also missing a bunch of work at a busy time. I leave for Cleveland Friday morning, so it’s already a short week and that adds to the stress.

    But that happens. Life gets in the way of work, and there are times you need to let things go. I know there are friends that have gotten off of work this week because of the weather, and I’ve had companies close. When I lived in Virginia, we closed the office sometimes with hurricanes or strong storms, helping employees to manage their lives outside of work. I’ve helped hang plywood and even get boats out of the water in preparation for the storm.

    I think that a part of what business has to include is social responsibility. It can come with benefits like goodwill that come from donating back to schools, but I think that the highest level of profit you can make is not the way business should be run. You should consider what helps your employees, what helps your community, and what is the best way to integrate your company into the world outside of your profit.

    Hopefully your employer understands that.

  • SQL Server Recovery–MCM Prep

    I always have to think about what happens during the recovery process in SQL Server after a restore. I’ve always known the process as roll forward and roll back, which is how the items are noted in the SQL Serve error log. Transactions are rolled forward or rolled back.

    But which comes first?

    If you think about it, you want to roll forward the committed transactions, which were not written to the data files. Then you roll back the changes made to the data files which were not committed. How you get transactions in these states is for another day.

    However I found an easier way to remember. While studying for the MCM, I heard these processes referred to as redo and undo, which apply to roll forward and roll back, respectively. If you use these terms and place them in alphabetical order, you get redo, undo.

    Or redo before undo.

  • A SQL Azure Customer

    Not me, though I’m not necessarily against Azure in principle. I had a friend ask me for a SQL Server hosting provider the other day, saying they had built a web front end, and had a web host, but were thinking to separate out some data to a separate provider. I have no idea why, or what this was.

    “Have you looked at SQL Azure?” I queried?

    He responded to say he was embarrassed to say he didn’t know what Azure was. I told him that I wasn’t completely sure myself, but it offered SQL Server almost as a web service, and it might work for him.

    “$9.99/mo for a 1GB database? I’m in” was what I heard a bit later. I did caution him to be careful of transactions and transfer. Test locally was my advice.

    We’ll see how he likes it. I’ll follow up in a few weeks and see what he thinks.