Saturday, February 25, 2012

.mdf file

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!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_sh
rink_in_MB')
go
dbcc shrinkfile('logical_LDF_name',size_to_sh
rink_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!|||The master database is the system database for SQL Server. It should not be
that big. It appears that the application (or something/someone else) has
been creating tables and loading data into this database. You probably
need to determine what is in it and clean out as much of it as possible and
relocate it ot a different user defined database.
Rand
This posting is provided "as is" with no warranties and confers no rights.|||I just re-read your post and if the mdf file is not related to the master
database you can disregard my last post.
Rand
This posting is provided "as is" with no warranties and confers no rights.

No comments:

Post a Comment