SQL Server 2005 Database Log File

Microsoft SQL Server

So strangely the desktop came up and all looked normal. Check the website and it was still down, but was giving me the DotNetNuke Under Construction screen, which again is odd… So I check IIS and everything was running fine there, I check SQL Server and the database was in recovery mode. Whoa - what is that, I said. Can’t be good whatever is going on… SQL Server Manage became unresponsive after a few minutes and I was leery about ending the task, but didn’t want to wait around for it. So when I started it up again the database was now no longer in recovery mode. Checked the website again, still down, restarted IIS and we were back up and running. Ok so what had happened?

I started checking around and noticed some things that weren’t quite right on my server, so I cleaned them up, removed some strange user directories that had no corresponding user accounts, removed some unexplained software that was installed, like notepad++, and other things that were installed that I didn't recognize. I then did a reboot of the server after I have removed everything and it came back up but IIS/SQL Server didn’t start normally.  I had to kick start everything again to get it up and running. I noticed that the database seemed large, especially the LDF file, so I did a few DotNetNuke/SQL Server clean up items that I knew about, event log truncate, database shrink, etc… But nothing affected the LDF file size. After about 30 minutes of poking around and checking the site, everything appeared to be running fine, but the database size was causing me pause. Not great pause, because I threw in the towel and went to bed.

At 4:30am my phone began to tell me that my server was having problems again. I checked the site and sure enough, we are back to the DotNetNuke Under Construction page. Ok, fine I was just sleeping anyway, back into the office, RDC in, unplanned reboot message, still no good answer. I find that the database is in recovery mode again in SQL Server manager. So this time I wait it out and it, and after a couple of refreshes it is out of recovery mode, quick IIS restart and voilà, site comes back up. This is going to take some more investigation methinks. Why is the LDF file so big? ~20Gb And why is there only 13Gb of space on this machine?  Well, I had some old database copies around so I cleaned that up and got back to about 57Gb of free space. I did some Googling and found an article, written by Sabastian Leupold on DotNetNuke Database Performance Tips that had some great information in it. I truncated the Event Log, Site Log, Scheduler History Log, and trashed the Search Item/Word table structure. Then, I re-indexed the search stuff using Search Administration and updated SQL Statistics for query optimization. Checked my LDF file after a database shrink and nothing, nada, still ~20Gb in size.

Ok, well there has to be a way to get this file under control, so a new Google search for “Shrinking the transaction log file SQL LDF”. I came upon a good article from 4 Rapid Development where they talked about how to shrink the log file: Right-click on the database you want to shrink => Tasks => Shrink => Files, change the file type box to Log and make the shrink action “Reorganize pages before releasing unused space” and click OK. So I do that and it quickly comes back to me and there was no change. Reading a little further into the post finds that this doesn’t work so well with Microsoft SQL Server 2005, which is of course what I am running on this installation. So they have an alternate method that works for SQL Server 2005 that starts with Detach the Database - Whoa what? That doesn’t sound good… So before I proceed I take a backup of the database, and I make an additional manual copy of the .MDF and .LDF files into another directory just in case. Ok, where were we, oh yeah, detach the database, well that means stopping IIS first, and cycling SQL Server to clear connections, but finally I get it detached. Step two, delete the big LDF file - wait their solution is just to delete the file - isn’t there like important stuff in there? Ok, delete the file. Step three, attach the database again, remove the not found log file (.ldf) entry and click OK. Poof you’re done, SQL Server creates a new blank log file and you are off and running again. My MDF file is ~56Mb in size and all seems fine. I still have my backups just in case something is amiss - that is until I need some more free space on that machine.

I checked through the various system event log(s) and I see errors that say the database couldn’t be connected to because it was in recovery mode, and I see all kinds of messages that indicate a restart event, but I don’t see anything that helps me understand what happened or why I need to go in and clean things up. I also don’t see any items in the event log that tell me about the mysterious software that I found. Something is going on, but I can’t quite put my finger on it just yet. Stay tuned…

James NagyJames Nagy

Co-Founder and Managing Director of Sprocket Websites, Inc.; Co-Founder Chicago Area DotNetNuke Users Group; Co-Founder and Managing Partner of J&S Tech Designs, LLC

Other posts by James Nagy

Contact author Full biography

Full biography

I am an entrepreneur - and there is nothing like being your own boss and owning your own business. I own a Consulting business, J&S Tech Designs, LLC with my wife and business partner Susan. I also own a Website Design, Development, Services & Marketing business, Sprocket Websites, Inc. with my partners Susan, Don and Kate. My passion is in helping businesses grow and prosper through use of technology and creative innovation.

x

Facebook page Twitter Linked In Google Plus