SQL Server 2005 Express: date of mdf file doesn’t change, ldf file get huge

sql-server-2008

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

Best Answer

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.

Related Topic