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



Question:

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.


Solution:1

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.


Solution:2

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.


Solution:3

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


Solution:4

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


Solution:5

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.


Note:If u also have question or solution just comment us below or mail us on toontricks1994@gmail.com
Previous
Next Post »