SQL Server 2005 Database Log File
Search
× Search

Sprocket Websites - Blog / News / Updates

James Nagy
/ Categories: Technology

If you find this helpful, pay it forward! More after the article...

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…

Did you enjoy this article? Was it helpful? Insightful?
Then please share it! Post it on your favorite Social Media platform(s) so your followers see it.
It's easy. Just click on any of the social icons below and we'll do the heavy lifting for you.
Oh yeah, leave a comment below. We'd love to hear from you. Thanks for visiting!

Print
5828 Rate this article:
No rating
James Nagy

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
Previous Article SEO for the Common Man
Next Article SEO First Steps for the Common Man

Leave a comment

This form collects your name, email, IP address and content so that we can keep track of the comments placed on the website. For more info check our Privacy Policy and Terms Of Use where you will get more info on where, how and why we store your data.
Add comment

Contact author

x

Stay In-The-Know...

Via QR Code

... With Every-Other-Week Tips!

Every other Tuesday, you'll receive the Sprocket Report completely free! Learn the latest business tip or news about what's currently happening in internet marketing. You get:

  • A web marketing tip from Kate
  • Another one from Breanne, and
  • Bonus! Tips curated from around the web

That's three valuable posts that you can read, plus a quick look at upcoming events and what's being discussed on Twitter. 

You'll want to get this info for yourself, in your own inbox. It's easy!

Type your email in the box above. OR scroll to the bottom of any page on this site. We have our subscription box there, too!

We NEVER give out your email address to anybody else and we don't flood you with ads. It's just good, free information. 

About Us

We turn surfers into visitors and visitors into customers.

We'll make you a beautiful, interactive website. Then we'll help drive traffic to it.

Read Archived Articles

Search

GET SOCIAL

LEARN MORE

Get The Sprocket Report

 

Terms Of UsePrivacy StatementCopyright © 2024 by Sprocket Websites, Inc.
Back To Top