Size of MDF and LDF Files

Question

Size of MDF and LDF Files

ComITSolutions

SSC Eights!

Points: 813
More actions
December 25, 2009 at 7:07 am

#156531

Hi,
We have developed Mini ERP solution to our client, which includes One Ho server and as many as 38 branches, physically located miles apart. For the Data transfer we are using replication.
The physical file sizes of database which is used for Replication(publisher) are
MDF is 1.8 GB and LDF is 14.8 GB But in all other databases the size of LDF is very much less when compare with their respective MDF Files.
Is this difference is because of replication???

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

Lowell SSC Guru Points: 323482 More actions · Answer 1

could it be you do not have a regular backup schedule in place in that location, and that is causing your unnecessary growth?

the ldf is the log file. if your database is set to the recovery model "FULL" instead of simple, and you NEVER make a backup, it will keep growing till you run out of disk space, replicated or not, right?

if you make a backup, SQL will internally truncate the log file, but will keep the huge 14+ gig file , because it assumes the file space will be needed again.

so you could shrink the file after doing a backup, so that it's not so large, and make sure you put a regular backup process in place.

since it is replicated, i don't think you want to change the recovery model to SIMPLE, and it sounds like an important database that needs to be backed up a lot.

==edit== reread it and realized you were not talking about a specific subscriber, but the main database that is being replicated; a large log file would be normal, but i'm not sure it would be 14x larger than the mdf.

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Steve Jones - SSC Editor SSC Guru Points: 728164 More actions · Answer 2

I think this large log file is the publisher, correct?

If so, the log file could be there if you have replication troubles. Things can't be cleared from the log until the publisher is sure they are in some other location. It might have been a growth from a some communication issue, or it might have been from someone not running log backups at some point while the db is in full mode.

I wouldn't necessarily worry about it, or do anything, unless is continues to grow.

Follow me on Twitter: http://www.twitter.com/way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com

Gail Shaw SSC Guru Points: 1004494 More actions · Answer 3

Please read through this - Managing Transaction Logs[/url]

What's the output of this?

DBCC OPENTRAN

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

ComITSolutions SSC Eights! Points: 813 More actions · Answer 4

Lowell (12/25/2009)
could it be you do not have a regular backup schedule in place in that location, and that is causing your unnecessary growth?
the ldf is the log file. if your database is set to the recovery model "FULL" instead of simple, and you NEVER make a backup, it will keep growing till you run out of disk space, replicated or not, right?
if you make a backup, SQL will internally truncate the log file, but will keep the huge 14+ gig file , because it assumes the file space will be needed again.
so you could shrink the file after doing a backup, so that it's not so large, and make sure you put a regular backup process in place.
since it is replicated, i don't think you want to change the recovery model to SIMPLE, and it sounds like an important database that needs to be backed up a lot.
==edit== reread it and realized you were not talking about a specific subscriber, but the main database that is being replicated; a large log file would be normal, but i'm not sure it would be 14x larger than the mdf.

Hi Lowell,

Sorry for late reply, I was on year-end holidays.

You are bang on target. The schedule for back up was disabled. Now by activating schedule the log file size can be reduced???

Thanks everybody who replied to my query. I learnt a lot from you guys.

I have One more problem in restoring the back up I will post it in new thread.

Thanks again.

Gail Shaw SSC Guru Points: 1004494 More actions · Answer 5

ComITSolutions (1/2/2010)
You are bang on target. The schedule for back up was disabled. Now by activating schedule the log file size can be reduced???

Full backup or log backup?

If log backup, then enabling the schedule should ensure that the log doesn't grow any further. It won't shrink it though. If the ldf is way larger than it needs to be, do a once-off shrink to get it to a reasonable size.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

ComITSolutions SSC Eights! Points: 813 More actions · Answer 6

Yes, its log backup.

I will enable the schedule and get back to you.

Is there any other way to the size of LDF file?

Gail Shaw SSC Guru Points: 1004494 More actions · Answer 7

ComITSolutions (1/2/2010)
Is there any other way to the size of LDF file?

Any other way to do what?

Did you read the article I referred you to?

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass