1

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.

4
  • 2
    Did they really "grow"? What were they immediately after the restore operation? Jul 11, 2010 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. Jul 15, 2010 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, 2010 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. Jul 18, 2010 at 23:08

5 Answers 5

7

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.

2
  • The database originated from a ~2MB .bak file and no further data has been added to it.
    – CJ7
    Jul 11, 2010 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, 2010 at 13:37
4

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.

2
  • The database originated from a ~2MB .bak file and no further data has been added to it.
    – CJ7
    Jul 11, 2010 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. Jul 11, 2010 at 23:25
1

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

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

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).

1
  • a database log backup will not shrink the transaction log Mar 5, 2019 at 9:51
0

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.

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .