Sorry if the question has been answered or is asked in a wrong way - I am developer, no sys- or database admin.

Problem: An SQL Server 2005 Express mdf file (size 370 MB) didn't change it's file date for two month (even after server reboot) while the ldf file has grown to 56 GB.

The database is running on a Windows 2003 Webedition Server.

It is an important customer, I don't want to risk anything (so I haven't tried the option "Shrink" in Management Studio for fear of losing data or a long downtime.

We are trying to set up a new Server and are trying to restore the latest .bak file, but it sticks at "Executing 90%" for some time now.

What can be done? 1. Wait much longer 2. Try to shrink original database will website is running 3. Anything else

Any help is greatly appreciated.

Thanks, Olaf


The other two answers are entirely correct, but I think that the underlying reason may not be coming across here:

In Full recovery mode, SQL Server does not write data to the mdf (data) file... it only writes to the transaction log.

The only way to get that data into the data file is to do a transaction log backup. It is generally accepted that an active, production database in Full recovery mode should have its transaction log backed up at least once an hour.

The suggestions posted earlier are to temporarily switch the database to Simple recovery mode. In this mode, SQL Server does not keep data records of transactions (edited so as not to confuse some people) in the transaction log for any period of time. If you do this change, SQL Server will do -lots- of disk thrashing for a good amount of time, as it commits the transactions in the log to the data file.

When that is complete, you will still have a 56GB transaction log file, and your data file will be larger. (not nearly so much bigger, though). The transaction log file will not get smaller until you 'shrink' it.

normally, this is something you should not ever do

But because of the mess this database is in, it's probably a good idea to shrink the transaction log down to perhaps half the size of the data file after the above process.

Then... switch the database back to Full backup, and put a maintenance plan in place immediately.

A commenter below suggests that my answer is "completely wrong", based on a couple tiny nits being picked. I could pick those same nits with that comment and say that it is "completely wrong" as well.

  • I did not mean to suggest, that SQL Server writes your data to the transaction log. It writes exactly what it's name suggests: records of transactions. I'm sorry the commenter could not deduce that fact by reading; I don't believe the OP has such a problem, so I didn't think it necessary to elaborate.

But this should be noted: 'transaction records' are data.

Other notes I make suggest clearly that I know very well what the Transaction Log contains - such as my statement that though the data file will grow once the TL is backed up, it won't grow nearly as much as the size of the TL.

  • I did not mean to state - and don't really think I did - that changing the recovery model changes how SQL Server writes data to the data file. But I did state that it affects when data is written to the data file. The commenter below is picking an incredible nit here - and using extremely clumsy (irrelevant) wording to do so.

The nit being picked is that the recovery model changes what SQL Server does with the transaction log records. In Simple, it persists those transactions to data in the data file and removes them from the TL 'quickly'. In Full, it leaves them alone until a TL backup is done.

You could argue that the recovery model therefore has nothing to do, specifically, with the Data file, or when data is written to it. But that's sort of like arguing that pressing the accelerator in your car does not cause the car to move faster, because, well; all it's really doing is causing the engine to spin faster.

Perhaps technically correct, but a totally useless addition of information in the context we're speaking.

  • Thanks for the very nice and detailed answer. This is the way to go (and we already assigned a person). – Olaf Nov 17 '10 at 16:11
  • 2
    This answer is completely wrong. SQL doesn't keep any data in the transaction log, whatsoever. SQL keeps a record of database transactions in the transaction log. The recovery model determines what SQL does with the records of those transactions in the log file. The recovery model does not determine how SQL writes data to the data file(s). – joeqwerty Nov 17 '10 at 20:06
  • Your answer states: "In Full recovery mode, SQL Server does not write data to the mdf (data) file... it only writes to the transaction log. The only way to get that data into the data file is to do a transaction log backup. It is generally accepted that an active, production database in Full recovery mode should have its transaction log backed up at least once an hour." How else can that be interpreted other than that you are proposing that data is written to the log and will never be written to the database unless a log backup is performed? Are we to "deduce" what you mean or what you write? – joeqwerty Nov 19 '10 at 15:41

Two options

  1. Change your recovery model to Simple
  2. Start doing transaction log backups (and ensure you're also doing full database backups.)

Something you must do:

  1. Start reading up on this stuff or hire someone who already knows it (and can train you on what you need to know.)

Running a swerever is like driving a car. Without knowing what yuo do... you run into trouble. As you do.

  • date of mdf file doesn't change

IIRC it changes when the file is closed.... and SQL Server only closes files when you shut it down.

  • ldf file get huge

Because you dont make proper backups? Without FULL AND LOG BACKUPS - the log runs full. this is by design, I strnogly suggest you read up on backing up databases or you WILL loose this customer.

To solve the immediate problem change the db recovery model to simple so no log entries are kept long term. Then afte a day or an hour shring the log file to a mangeable level. This is stil a "you will loocse the customer" situation, but at least your disc does not run full.

To NOT loose the customer long term make sure you have the beginner level understanding of satabase desaster recovery and start making backups.

  • Thanks for the advice and the strong words. Normally, I wouldn't have to deal with this at all, but sometimes the right people are simply not there all the time. How to afford a backup sysadmin is an economical matter I am unable to solve :-). We ARE doing backups and COULD recover the database, and also we started implementing your and mfinnis suggestions. However, I was surprised to learn that restarting the SQL Server indeed sets the new date, but restarting the Windows server DOES NOT (probably because the SQL Server isn't shut down properly). – Olaf Nov 17 '10 at 15:56
  • @Olaf: A graceful (proper) shutdown of Windows will always perform a graceful (proper) shutdown of SQL server. – joeqwerty Nov 18 '10 at 0:36
  • @joeqwerty: It should, of course. But for some reason, on this particular server it doesn't. Which is another reason why we move away from it. – Olaf Nov 18 '10 at 8:21

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.