Out of space? SQL dead? Here's what you do.

Microsoft makes it pretty easy to run a SQL database - and of course, the flip side of it is that there are gazillion improperly managed databases out there.

In this post - we'd like to share a way to recover from SQL server stoppage due to insufficient log space. Have you ever had a case where SQL Server database stops working for seemingly no reason? You might have ran out of disk drive space, plain and simple.

But how can this be?? You have 100GB storage and only 10GB data. How could one possibly run out of space? Well, there's this pesky thing called transaction log. It's quite useful - as the name implies, it logs every data and also code changes that happened to the server. And save them all to disk. If you look at the SQL server data folder, you will find a file with extension ".ldf", along with teh ".mdf" file.

To simplify:

LDF file -> Logs

MDF file -> Current Data

Of course, logs takes up some space - sometimes bigger than the data itself, which happens if you have a small but rapidly changing data.

In a properly managed database, transaction logs are backed up at a regular interval, say, 15 minutes. But, as we mentioned - since Microsoft makes it really easy to set up and run SQL server, often it's done by people without qualifications to properly set it up. The end result is fully logged databases everywhere without backup jobs to go with. One has to come with the other, like wine and cheese.

So, what happens when the wine is served without the cheese? Well, the log keeps filling up. Your .ldf file gets bigger and bigger as the time goes on, until it exhausts the capacity of the hard drive it lives in - and if it happens to be your operating system drive, it causes other problems like the whole operating system becoming unstable because C: drive is completely full.

Then, people take notice and say, call someone like us because the database isn't taking new transactions, but not before they try to delete some stuff to free some space. The thinking makes sense, right? The drive is full, delete stuff.

Not so.

Deleting junk files may buy you some time, but not much.

Deleting SQL data then. Well, this too is a problem because before a space can be reclaimed as a result of deletion, it must first log the deletion. This means the DELETE statment INCREASES the space requirement before it can reclaim the free space.

So here's what you do. (applies to all versions/editions of MS SQL)

STEP 1. In SSMS, right-click on the database. See Properties.

STEP 2. Go to Opetions pane.

STEP 3. Change Recovery Model from Full or Bulk-Logged to Simple.

STEP 4. Click OK to close dialog.

STEP 5. Again, right-click on the database.

STEP 6. Select Tasks -> Shrink -> Database.

STEP 7. Click OK.

There you go. Now your databases are running like you wanted it to, and it won't pile up transaction logs anymore (the difference between SIMPLE recovery model and FULL recovery model.) The chances are, if you are suffering this problem you don't care much about what transaction logs can do for you - only that it's piling up in your drive and taking up space, and you don't want it.

We hope this helps someone someplace sometime. Happy Holidays!