Voice of the DBA

Category: Blog

  • 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.

  • Backing up the log in simple mode

    Someone posted a note saying that their backups were really large and slow. So they were trying to manage their backup scheme and ensure some level of recovery.  I was going back and forth, along with others, talking about the way your recovery would go and the merits of that particular backup scheme. While posting, the person mentioned at one point that his databases were in simple mode.

    Huh?

    AFAIK, or rather, As-Far-As-I-Remember, you can’t do a log backup in simple mode. I think that’s been a part of the product, but before posting, I decided to test.

    Short answer, if you don’t want to read, is that you can’t backup a transaction log if the database is in simple mode.

    First I grabbed a sample database on my local 2008 instance and set it to simple mode. Actually, first I had to set a database to simple mode, and I didn’t want to use the GUI. This isn’t something I do often, so I had to look up the syntax:

    ALTER DATABASE SET OPTIONS – The first example shows setting to full recovery mode.

    ALTER DATABASE db2 SET RECOVERY SIMPLE

    Once this was set, I created a few transactions and ran a backup.

    CREATE TABLE MyTable (id INT)

INSERT MyTable DEFAULT VALUES
    BACKUP LOG db2 TO DISK = 'db2_log_test.trn'

    This gives me the following error:

    Msg 4208, Level 16, State 1, Line 2

    The statement BACKUP LOG is not allowed while the recovery model is SIMPLE. Use BACKUP DATABASE or change the recovery model using ALTER DATABASE.

    Msg 3013, Level 16, State 1, Line 2

    BACKUP LOG is terminating abnormally.

    This is what I expect, as there is no backup chain that can be used to build a backup. What about running a full and then a log right away?

    BACKUP DATABASE db2 TO DISK = 'db2_db_test.bak'
BACKUP LOG db2 TO DISK = 'db2_log_test.trn'

    Once again, the error occurs, and there should be no checkpoint after the full backup starts.

    db_simple_log

    So I think that my posted is mis-informed of his environment, which is a dangerous place for a DBA to be, especially with regard to backup and recovery.