Mickey's T-SQL Ponderings

My good friend Chris Yates (b|t) is hosting this month’s T-SQL Tuesday blog party. The party was started by Adam Machanic (b|t) in December of 2009.

This month’s invitation is about something we’ve learned recently. Being that I just got back from Summit, I wasn’t quite sure what to pick. I ended up picking something prior to Summit. I learned how to recover from my Transaction Log filling up the disk via our SQL Community.

Silly rabbit, #SQLHelp is not just for kids

Hashtags have a WIDE variety of uses. They are used to express emotions, categorize tweets, to follow global discussions, and even to state the obvious. But my favorite use is to get help from our community with the use of #SQLHelp, #SSRSHelp, and others. And this is where my story begins… Freaking out and going immediately to #SQLHelp.

I am not a DBA. I’m a BI girl. I’m a Data Modeler. I’m a Database Developer. But please, oh please, don’t ask me to troubleshoot a serious problem on a failover cluster. That is not my cup of tea… and that is exactly what I found myself doing one Wednesday afternoon.

Before the internet, I would have been completely helpless. Today I’m not. Why? Because I have an entire community behind me. One that does not think twice before lending a hand. I posted a request on twitter requesting assistance for my problem and I included the #SQLHelp hashtag. Less than a minute passed before I had TWO community members offering to help. Wow! (I did take the help of only one of them, since it would have been confusing to have them both help.) He was kind enough to answer my questions and walk me through the solution over the course of a few hours. That is what I call Community. That is what I call #SQLFamily.

Earning my DBA card

I now know how to fix this problem. I even had the “opportunity” to try out my new knowledge a few weeks later due to some deadlocks and the lack of email notification setup on the our monitoring software. (Don’t ask.)

Lesson 1

I thought that a full backup also took care of the transactions stored in the Transaction Log. This is false. In order to have your Transaction Log truncated, you have to create Transaction Log backups. (Lesson Learned)

Lesson 2

Once the disk is full, you can’t make a backup of the transaction log. To get around this, you add a new, tiny, empty log file to the database. I don’t know the technical reasoning, but this will allow a backup to be created, thus truncating the very full log file. After which you can shrink the really big original log file to a reasonable size. Finally, you can remove the tiny, empty log file that you temporarily created.

When troubleshooting a log file that has filled up a disk on a Failover Cluster, you can only create a new log file on a shared resource. I had none. Luckily, I had a brand new database on that server. The database had been released into production, but was not used. Yet I was able to remove it from the server and create a new transaction log in its place. (And there was much rejoicing.)

Lesson 3

Fast forward to SQL Saturday Portland, right before Summit. I took a class from Randy Knight (b|t). His presentation was called Locks, Blocks, and Deadlocks Oh My! In his session he spoke about how the transaction log can get filled during a deadlock (been there, done that, got the T-shirt). He suggested creating some fake files on the drive to reserve space for when that awful day comes and your disk is full. I had been wondering if that would be a good solution. Not only did he confirm that it was, but he gave a great way to quickly create five 1G files that can be removed as needed to get the server back on its feet.

Conclusion

The moral of this story is not only to have Transaction Log Backups as well as Full Backups, but to also nurture your network. Your network is like gold. They are there to lift you up when you need it and to help you through technical challenges that are outside of your comfort zone.

Thanks for all the fish

Thanks go out to Chris Yates for hosting this month’s T-SQL Tuesday blog party. Thanks also go out to all the community members who quickly respond to people in need.