April 6, 2004 at 7:02 pm
Hi all,
I got a database which recently has shrunk so suddenly. One day it was like 3 GB and the next day, all of a sudden, its size reduced down to only about 400MB. I notice that the .ldf is still about 3 GB which is the same size as the .mdf file before it shrunk.
My question is if SQL has some kind of feature that compress the database automatically when it gets too big or if the content of the database has been modified without me knowing?
Also, could someone please kindly have a quick explain to me the difference between .mdf & ldf.
I'd really appreciate all your help again. Thank you.
April 7, 2004 at 8:11 am
First the files:
The .mdf is the Data file. That's your actual database. The .ldf is the LOG file. That's the transaction log. The transaction log keeps track of all the updates/inserts/deletes that happen on your database.
SQL Server does have something that could shrink your database to get rid of 'empty' space. Whether it is turned on or not only you can tell. There are two ways to automagically shrink a database. Open Enterprise Manager and expand to your database. Right click the database and select Properties. Go to the Options tab. Is AUTOSHRINK checked? If so, SQL Server will attempt to shrink your database whenever it can. If not, in EM find Management, expand that and click on Database Management Plans. Do you have a DMP? If so, review that plan, it might be shrinking the database.
How do you shrink the Log file? You can run DBCC SHRINKFILE or you can backup the log file (BACKUP LOG).
Refer to the BOL for more information on the .mdf and .ldf files along with shrinking database files.
BTW- have you made sure no one deleted data?
-SQLBill
BOL=Books OnLine=Microsoft SQL Server Help
Installed as part of the Client Tools
Found at Start>Programs>Microsoft SQL Server>Books OnLine
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply