Why would the .MDF and .LDF files grow to 1GB+ for a database that contains 1 table of only 10 rows?

The database originated from a 2MB .bak file and no further data has been added to it.

  • 2
    Did they really "grow"? What were they immediately after the restore operation? – Jeremy Smyth Jul 11 '10 at 15:17
  • 3
    A bounty? Really? You've had several answers all saying the same thing - your databse is full of empty pages where the disk space has not been reclaimed. When you ran the restore the 2Mb BAK file would have instantly jumped to the full size. – Mark Henderson Jul 15 '10 at 6:01
  • @Farseeker: what do you mean "jumped to the full size"? Are you saying all SQL databases take up 1GB regardless of their actual content? – CJ7 Jul 18 '10 at 12:12
  • I mean that if the SQL File is 1+Gb when you performed the backup, it will be 1+Gb when you restore the backup, regardless of the size of the backup file. – Mark Henderson Jul 18 '10 at 23:08

The MDF might have been created as 1 GB - or it contained a lot more than just 10 rows at some point in time. MDF's typically don't shrink in size when you remove data from them. In order to get the size back down, you'd almost have to create a new database, make sure to set its initial size low, and then copy over the data from that table to the new database.

The LDF (transaction log file) will grow if you run a lot of SQL statements that need to be logged, e.g. inserts, updates, deletes and other things. Using log backups on a regular basis, you can keep the transaction log file small, and switching the database to the Simple recovery model (instead of Full) will also help significantly.

  • The database originated from a ~2MB .bak file and no further data has been added to it. – CJ7 Jul 11 '10 at 8:47
  • 1
    Irrelevant - the backup file will not store empty pages, the recreated database will. I.e. I an put a 100gb empty database into a 2mb bak file.... the "normal" version will again have.... 100gb ;) – TomTom Jul 11 '10 at 13:37

A single row with a single column can have 2Gb.

But even if the table is indeed small, current size is not indicative of past history. A table can have 10 rows after 100 million rows were deleted. The log will retain all activity until is backed up. Databases do not shrink after growth, nor should they be forced to bare for extraordinary circumstances.

  • The database originated from a ~2MB .bak file and no further data has been added to it. – CJ7 Jul 11 '10 at 8:48
  • As Marc already said, the size of the BAK doesn't mean much. Restore operations will restore the size of the MDF files to their original size before the backup. – Remus Rusanu Jul 11 '10 at 23:25

Do you store BLOBs in your Database? Blobs (binary large object)s useally consume memory more than other things.

  • The database originated from a ~2MB .bak file and no further data has been added to it. – CJ7 Jul 11 '10 at 8:48

As many people said, there are lots of reasons thta could make your physical files grow even if you're storing very little data in your database.

If you need and/or want to, you can shrink them to the bare minimum needed. First take a database log backup (this will shrink the transaction logs), then use the DBCC SHRINKDATABASE command (or the equivalent GUI operation in Management Studio).

  • a database log backup will not shrink the transaction log – shouldbeq931 Mar 5 '19 at 9:51

Doesn't answer your question but if you can repeat the restore, you could use Profiler to "watch" what processes touch the database in question.

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.