Re: .mdf file

Tech Tip: Click here to run a free scan for Windows Errors and optimize PC performance

From: Hari Prasad (hari_prasad_k_at_hotmail.com)
Date: 05/28/04 

Date: Fri, 28 May 2004 20:13:02 +0530

Hi,

What exactly is the .mdf file , .LDF file?

MDF - Primary data file
NDF - Secondary data file
LDF - Transaction log file

Functions:-

MDF
------
It stores all the SQL Server Objects , data , system catelogs,security and
.....

LDF
-----
Server uses the transaction log of each database to recover transactions.
The transaction log is a serial record of all modifications
that have occurred in the database as well as the transaction that performed
each modification

Can I shrink it somehow?

Yes , you can shrink the database.

Steps:

1. Take a database backup and transaction log backup
2. Execute the below command in Query analyzer to identify the MDF and LDF
file info

    use <dbname>
    go
    sp_helpfile

3. Use the name column (Logical name for MDF and LDF) to shrink the
database. Use the below command

    dbcc shrinkfile('logical_MDF_name',size_to_shrink_in_MB')
    go
    dbcc shrinkfile('logical_LDF_name',size_to_shrink_in_MB')

How would it get SO big?

No idea about MDF file, Execute the command sp_spaceused to identify the
actual database size. LDF can grow if you have any
maintenance activies like reindexing, batch operation .....

Thanks
Hari
MCDBA

"Joe" <anonymous@discussions.microsoft.com> wrote in message
news:1460901c444bf$40824ce0$a101280a@phx.gbl...
> Your all going to laugh at me. I know NOTHING about SQL
> Server. I have an application which is using a SQL
> database I set-up to retrieve data. I noticed I have
> a .mdf file (Master database File???) and .ldf (Lock file
> I assume??) that are huge. 32GB and 15GB respectively.
> Huge considering that the actual data in the database is
> only about 1-1.5GB. (I can run it in MS Access no
> problem). What exactly is the .mdf file? What is its
> function? AND most importantly, can I shrink it somehow?
> How would it get SO big? I appreciate any help a lot!
> Thanks!

Relevant Pages