Tuesday, September 8, 2009

Much ado about transaction logs

I came into work this morning and our search database log took over the entire drive we were using for database logs. Additionally, the server had failed over in the night, but one of the databases had suspended mirroring on the original server and five others suspended it on the second.

It was a mess.

What happened: I had not implemented a maintenance plan to back up the transaction logs quick enough and it had initially gotten out of hand making it impossible to shrink them later.

Here's some back story about database shrinkage and how SQL server manages it. When SQL server tries to grow a database file or shrink a database file it creates a new file that is sized appropriately. It then copies the information to the new sized database and deletes the old one. So if you don't have enough space free, it will not shrink the database.
The second problem is that there was over 290 GB of data stuck on the principal server that wouldn't transfer to the mirror. The mirror's log drive was jam-packed as well. The suspended mirror on the principal was preventing me from truncating and shrinking that log file because those transactions had to get to the mirror. Here's how I fixed it:
  1. I stopped the services that were using the database.
  2. I broke the mirror.
  3. I created an additional database log file on our back up drive (had plenty of space free for it to replicate)
  4. I used the database shrink utility to empty the file on the log files directory into the file in the backups directory
  5. I truncated the logs (backed up the transaction logs) and then shrunk them again. This time the file went down to 36 MB.
  6. I removed the extra file. (see this blog for help removing extra log files: http://www.karaszi.com/SQLServer/info_dont_shrink.asp)
  7. I recreated the mirror
  8. Created a special maintenance subplan just for the search database to back up the transaction logs every 5 minutes to keep it in check.
  9. Restarted the search services.

After that it didn't grow anymore. To prevent this in the future? Do your maintenance plans early and monitor them closely.

No comments:

Post a Comment