Home | Contact Us | FAQ | Search & Site Map | Link to Us
Sign In | Join | Other 45 Sites in Network
Home
Discussion Groups
DB Engine
SQL ServerMSDESQL Server CE
Services
Analysis (Data Mining)Analysis (OLAP)DTSIntegration ServicesNotification ServicesReporting Services
Programming
CLRConnectivitySQLXML
Other Technologies
ClusteringEnglish QueryFull-Text SearchReplicationService Broker
General
Data WarehousingPerformanceSecuritySetupSQL Server ToolsOther SQL Server Topics
DirectoryUser Groups
Related Topics
MS AccessOther DB ProductsMS Server Products.NET DevelopmentVB DevelopmentJava DevelopmentMore Topics ...

SQL Server Forum / DB Engine / SQL Server / February 2008

Tip: Looking for answers? Try searching our database.

SQL .ldf and .mdf files growing to huge sizes

Thread view: 
Enable EMail Alerts  Start New Thread
Thread rating: 
LMI - 25 Feb 2008 15:07 GMT
I just looked on my SQL server runing SBS 2003 and noticed that all my
database file are growing to huge sizes. When i investigate further i have an
.mdf and .ldf files tat are really big. How can I control these file.  I know
i can;t delete the .mdf file however does the .ldf serve a purpose????
Linchi Shea - 25 Feb 2008 15:29 GMT
How big are they anyway? For the log file, you may need to regularly backup
your database log to contain its size. If it's already grown larger than it
should, you may have to shrink it first, and then implement a regular log
backup schedule. Also, you need to watch your transaction sizes. If you run a
very large single transaction, you can blow up your log even with regular log
backups.

The data file (.mdf) shouldn't really grow >too< large. I'd check the table
sizes and see which tables are taking up the most significant portion of the
overall database size, and go from there. There are cases where if a table
doesn't have a clustered index, it may be using more space than it should.
That may or may not apply to your case.

Linchi

> I just looked on my SQL server runing SBS 2003 and noticed that all my
> database file are growing to huge sizes. When i investigate further i have an
> .mdf and .ldf files tat are really big. How can I control these file.  I know
> i can;t delete the .mdf file however does the .ldf serve a purpose????
Jack Vamvas - 26 Feb 2008 20:39 GMT
As well as what has been outlined , consider file partitioniong or splitting
the Log and Data files onto different hard drives

Signature

Jack Vamvas
___________________________________
Search  IT jobs from multiple sources-   http://www.ITjobfeed.com

> How big are they anyway? For the log file, you may need to regularly
> backup
[quoted text clipped - 23 lines]
>> know
>> i can;t delete the .mdf file however does the .ldf serve a purpose????
Pete Berry - 28 Feb 2008 18:21 GMT
>  I know
> i can;t delete the .mdf file however does the .ldf serve a purpose????

absolutely.  The ldf is not really a separate entity but an integral
part of the database.  Transactions are first made in memory and written
to the logfile at the same time.  Subsequently committed transactions
are rolled into the mdf/ndf by a background checkpoint process.  This
facilitates data recovery in the event of failure -- so the database at
any given time consistes of the committed trasactions in the log file
and the mdf/ndf files. Without this write ahead logging, you very
quickly end up with a trashed database and loss of ACID properties. Do
*not* delete the ldf's -- might want to check out BOL (Books on Line)
for more information.
Signature

Graham (Pete) Berry
PeteBerry@Caltech.edu

 
Sign In
Join
My Latest Posts
My Monitored Threads
My Blog
My Photo Gallery
My Profile
My Homepage

Start New Thread
Enable EMail Alerts
Rate this Thread



©2009 Advenet LLC   Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.