My database files look very suspicious. we are using the DB in a production stage. and I'm seeing how the LDF file grows more and more. while the MDF file seems as it is not growing at all (I don't have a file size history to demonstrate it, but I think it is not doing it!! I bet!)
So I guess what could be happening? How could I force the merge to the MDF?
Are you taking periodic log backups? Like every 30 mins? Either start taking log backup, or change the recovery model to simple.
You can always see the reaosn which holds the log resuse in sys.databases , the log_reuse_wait_desc column:
Description of reuse of transaction log space is currently waiting on one of the following:
- NOTHING
- CHECKPOINT
- LOG_BACKUP
- ACTIVE_BACKUP_OR_RESTORE
- ACTIVE_TRANSACTION
- DATABASE_MIRRORING
- REPLICATION
- DATABASE_SNAPSHOT_CREATION
- LOG_SCAN
- OTHER_TRANSIENT
For more information, see Factors That Can Delay Log Truncation.
The LDF tracks all transactions performed on the MDB file, not just the actual data. As a result, the log file will continue to grow even if you are not inserting new data (update and delete statements are logged as well) until you do a Full Backup on the database in question. Once you do a full backup, SQL does not need to keep all those inactive transactions up to the point of the backup since you can simply restore from backup. Then you could be able to shrink the file back down to size. Regular backups will prevent you from having to do this last step.
Here is some MS KB articles to fill you in on some exact steps you can take.
Also, if this is important data, please read a few articles on why you shouldn't do things like truncate the log file or do a no_log backup option. Essentially, if you have not done a backup and perform one of these functions, you are throwing away unbacked data that could be used to help restore your database(s).
To answer your first concern:
Your database file (the MDF) likely still has free space from it's initial default size. A database usually has free space inside the file it utilizes. Eventually, it will fill up and then you will see it get larger (assuming you have that enabled). Until it reaches that point the size will not change, even though data is being saved to it.
As far as the log file goes:
The fact that you're asking this question tells me you are not very familiar with SQL recovery concepts. Do you need up to the minute recovery? If you have a catastrophic failure, will you be restoring the last full backup or will you be reapplying transaction logs?
The simplest solution to your problem if you do not have the ability to properly manage or apply transaction logs is to set your database to SIMPLE recovery mode. You can then basically forget about the log file (it will stop growing out of control).
