T-SQL Tuesday #21 – Bringing Ugly Back

T-SQL Tuesday #21 - I'm Bringing Ugly Back

This blog entry is participating in T-SQL Tuesday #21, hosted this month by Adam Machanic (Blog|@AdamMachanic). You are invited to visit his blog to join the blog party. You are welcome to write your own participating blog post for the party or just to read more blogs participating in this month’s theme: Your Crap Code.

The truth is, we all write crap code from time to time. Over time, we get better (hopefully). We learn that certain things are almost always bad (Select *, RBAR — Row By Agonizing Row) while other things are okay in certain situations but not good for everyday use (clustering on GUIDs, cursors). Or sometimes we write good code that does bad things. Today, I want to talk about a piece of good code that I wrote that does something bad. In fact, this code was even included in my Mirroring book. Even though the code does a bad thing, at the time that I wrote it, it was a very necessary thing to address a particularly heinous bug in database mirroring. I wrote and promoted a stored procedure to …….. routinely shrink the database log file.

Bringing Ugly Back

Bringing Ugly Back

The bug I mentioned is documented under KB article KB937531. In this situation, if your log file grew too large, and both partners were rebooted, the initial synchronization check would fail and both partners would be taken offline. In this scenario, the only way to get a partner online again is to drop mirroring and bring the principal online. This bug was fixed in CU6 for SQL Server 2005 SP2, so this is no longer needed, but at the time, it was needed.

Below is the offensive code snippet. The full procedure can be downloaded here: dba_ShrinkMirroredDatabases.sql (4.5 KB).

IF @NewFileSize < @FileSize
              BEGIN
                SET @SQL = 'Alter Database ' + QUOTENAME(@DBName) +
                           ' Modify File (name = ' + @FileName +
                           ', size = ' +
                           CAST(@NewFileSize AS NVARCHAR) + 'KB);' 
                IF @Debug = 0
                  BEGIN
                    EXEC sp_executesql @SQL
                  END
                ELSE
                  BEGIN
                    PRINT @SQL
                  END
                END
              END 
      SET @CurrID = @CurrID + 1
    END
  END