Log File Management

Question

Log File Management

keshab.basnet

Ten Centuries

Points: 1299
More actions
April 18, 2015 at 9:50 pm

#317531

1. my Production log file is 59 GB. and My data file is 44 GB. It seems Log file is larger than Data file. I do regularly Full database Backup and Transaction log file backup by maintenance plan. After full database and transaction log file backup Log Space Used (%) becomes around 0.123%. Can i shrink log file or truncate log file?
Is there is any drawbacks of shrinking log file to 2 MB some thing like that? If log file size is large even though there is regular full database and transaction log backup does it effect in Performance?
Could you please suggest me and give me suggestion for me further steps?
2. My system database 'Master' has 85% Log space Used and log size MB is 10 MB. Could some one please suggest me what to do further?

Viewing 6 posts - 1 through 5 (of 5 total)

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

Eirikur Eiriksson SSC Guru Points: 182882 More actions · Answer 1

keshab.basnet (4/18/2015)
1. my Production log file is 59 GB. and My data file is 44 GB. It seems Log file is larger than Data file. I do regularly Full database Backup and Transaction log file backup by maintenance plan. After full database and transaction log file backup Log Space Used (%) becomes around 0.123%. Can i shrink log file or truncate log file?
Is there is any drawbacks of shrinking log file to 2 MB some thing like that? If log file size is large even though there is regular full database and transaction log backup does it effect in Performance?
Could you please suggest me and give me suggestion for me further steps?
2. My system database 'Master' has 85% Log space Used and log size MB is 10 MB. Could some one please suggest me what to do further?

Quick question: How frequently are you backing up the logs?

😎

You can probably start by reducing the size of the log file by 50%, suggest you back up the transaction logs more frequently and monitor the free space. Do this for few days and then you'll have a better idea of what the normal log size should be.

I would not go down the route of shrinking the file this much though, that is bound to grow again and growth is expensive.

The master is in simple recovery, just leave it as it is.

keshab.basnet Ten Centuries Points: 1299 More actions · Answer 2

Thanks Sir.

I take full database and Transaction log backup once a day at night time when there is almost no transaction in database. Can i shrink log file to 50% once a week and make it to minimum and handle this way. Currently Log file is 60 GB and free space in log file drive is 82 GB. Could you please suggest me accordingly with out any negative impact in database especially with out occurring any performance issues.

Also could you please suggest me right time to shrink log file. Can i do it after Full backup and Log backup.

Eirikur Eiriksson SSC Guru Points: 182882 More actions · Answer 3

keshab.basnet (4/19/2015)
Thanks Sir.
I take full database and Transaction log backup once a day at night time when there is almost no transaction in database. Can i shrink log file to 50% once a week and make it to minimum and handle this way. Currently Log file is 60 GB and free space in log file drive is 82 GB. Could you please suggest me accordingly with out any negative impact in database especially with out occurring any performance issues.
Also could you please suggest me right time to shrink log file. Can i do it after Full backup and Log backup.

Quick suggestion, start by reading through this book

SQL Server Transaction Log Management

as I understand that you are lacking the basic understanding of the transaction log role and functionality,

then come back if you have any questions?

😎

Jeff Moden SSC Guru Points: 1003859 More actions · Answer 4

keshab.basnet (4/19/2015)
Thanks Sir.
I take full database and Transaction log backup once a day at night time when there is almost no transaction in database. Can i shrink log file to 50% once a week and make it to minimum and handle this way. Currently Log file is 60 GB and free space in log file drive is 82 GB. Could you please suggest me accordingly with out any negative impact in database especially with out occurring any performance issues.
Also could you please suggest me right time to shrink log file. Can i do it after Full backup and Log backup.

That's not enough. Obviously, there are enough transactions through the day and night (don't forget that most index maintenance is also full logged) to cause this problem.

I'd recommend doing a log file backup at least once every 30 minutes or less. I usually backup my Dev boxes 30 minutes and my production boxes every 15 minutes. Because that divides the day into 96 backup slots, the log file backups are usually very quick and don't impact any performance at all.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

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

keshab.basnet (4/19/2015)
Also could you please suggest me right time to shrink log file.

Never.

Please have a read through the book mentioned above

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