Is our log file too big?

Question

Is our log file too big?

Matt-1034261

Default port

Points: 1453
More actions
August 31, 2010 at 5:54 am

#148559

Hi
The log file for our database has reached in excess of 25Gb. Is this healthy and if not what do I need to do to shrink it?
Matt

Viewing 15 posts - 1 through 15 (of 54 total)

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

Ramji29 Hall of Fame Points: 3832 More actions · Answer 1

Matt-1034261 (8/31/2010)
Hi
The log file for our database has reached in excess of 25Gb. Is this healthy and if not what do I need to do to shrink it?
Matt

Matt... size of log file depends on the size of the data file (mdf) and as per my experience this shud be generally between 20% to 40% of the data file size and check if the recovery model is full and you're taking timely log backups. Check log_reuse_wait_desc in sysdatabases table and see why space is not been reused, refer http://msdn.microsoft.com/en-us/library/ms345414.aspx

Tell me if this helps.

Rohit

Matt-1034261 Default port Points: 1453 More actions · Answer 2

Ramji29 (8/31/2010)
Matt... size of log file depends on the size of the data file (mdf) and as per my experience this shud be generally between 20% to 40% of the data file size and check if the recovery model is full and you're taking timely log backups. Check log_reuse_wait_desc in sysdatabases table and see why space is not been reused, refer http://msdn.microsoft.com/en-us/library/ms345414.aspx
Tell me if this helps.
Rohit

Thanks Rohit, the size of the data file is about the same size as the log file so I guess there is something wrong there then.

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

DB I used to work with had a 250GB log file. Make sure that you're maintaining it correctly and don't worry too much.

Managing Transaction Logs[/url]

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

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

Ramji29 (8/31/2010)
Check log_reuse_wait_desc in sysdatabases table ...

log_reuse_wait_desc is not in sysdatabases (which is a backward compatibility view). It's in sys.databases.

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

Matt-1034261 Default port Points: 1453 More actions · Answer 5

GilaMonster (8/31/2010)
DB I used to work with had a 250GB log file. Make sure that you're maintaining it correctly and don't worry too much.
Managing Transaction Logs[/url]

And therein lies the problem as I am primarily a programmer and not an sql server administrator so I'm not sure that I am maintaining properly.

Grant Fritchey SSC Guru Points: 398881 More actions · Answer 6

Frequently when the log is really big and no one is sure why, it's because the database is set to Full Recovery mode and there are no log backups running. I would check the database options to see what the recovery model is set to. If it's full, you need to have log backups running on a regular basis, say, once an hour or so.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

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

Matt-1034261 (8/31/2010)
GilaMonster (8/31/2010)
DB I used to work with had a 250GB log file. Make sure that you're maintaining it correctly and don't worry too much.
Managing Transaction Logs[/url]
And therein lies the problem as I am primarily a programmer and not an sql server administrator so I'm not sure that I am maintaining properly.

So read through the article I referenced, check what you are and are not doing and post back if you want to clarify more things.

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

Matt-1034261 Default port Points: 1453 More actions · Answer 8

GilaMonster (8/31/2010)
So read through the article I referenced, check what you are and are not doing and post back if you want to clarify more things.

Thanks Gail, I'm looking at it right now and will have a tinker. I will post back if there is anything I'm unsure of.

No doubt I'll be posting later on asking how to recover a wrecked database 😀

Ramji29 Hall of Fame Points: 3832 More actions · Answer 9

GilaMonster (8/31/2010)
DB I used to work with had a 250GB log file. Make sure that you're maintaining it correctly and don't worry too much.
Managing Transaction Logs[/url]

Thx Gail for correction... I missed the dot(.).... 🙂

Matt.. if you're not taking the log backups then it will be good if you change the recovery model to simple and size the log file to be around 5-6 GB..... btw is this a prod db??

Rohit

Matt-1034261 Default port Points: 1453 More actions · Answer 10

Ramji29 (8/31/2010)
GilaMonster (8/31/2010)
DB I used to work with had a 250GB log file. Make sure that you're maintaining it correctly and don't worry too much.
Managing Transaction Logs[/url]
Thx Gail for correction... I missed the dot(.).... 🙂
Matt.. if you're not taking the log backups then it will be good if you change the recovery model to simple and size the log file to be around 5-6 GB..... btw is this a prod db??
Rohit

No we're not doing Log Backups but we do take backups of the entire disk.

Yes, this is a prod db

Grant Fritchey SSC Guru Points: 398881 More actions · Answer 11

The think about backing up the drive is, SQL Server maintains locks on the files. They're not getting backed up. You'll need to create a backup process that creates a backup copy of the database and then that backup copy can be backed up by your other process (complicated isn't it?).

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

Matt-1034261 Default port Points: 1453 More actions · Answer 12

Grant Fritchey (8/31/2010)
The think about backing up the drive is, SQL Server maintains locks on the files. They're not getting backed up. You'll need to create a backup process that creates a backup copy of the database and then that backup copy can be backed up by your other process (complicated isn't it?).

When the backup process is being perfomed would there be a drop in performance for the website using the database?

Grant Fritchey SSC Guru Points: 398881 More actions · Answer 13

Matt-1034261 (8/31/2010)
Grant Fritchey (8/31/2010)
The think about backing up the drive is, SQL Server maintains locks on the files. They're not getting backed up. You'll need to create a backup process that creates a backup copy of the database and then that backup copy can be backed up by your other process (complicated isn't it?).
When the backup process is being perfomed would there be a drop in performance for the website using the database?

Some, but not huge. I'd still run it during off-hours. If you are in full and don't have log backups running, you also need to enable these. I've never seen much impact from log backups at all. We run them on most servers 24/7, on differing schedules depending on the servers. Some are as often as every 10 minutes, most are every 1/2 hour.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

Ramji29 Hall of Fame Points: 3832 More actions · Answer 14

Matt-1034261 (8/31/2010)
Ramji29 (8/31/2010)
GilaMonster (8/31/2010)
DB I used to work with had a 250GB log file. Make sure that you're maintaining it correctly and don't worry too much.
Managing Transaction Logs[/url]
Thx Gail for correction... I missed the dot(.).... 🙂
Matt.. if you're not taking the log backups then it will be good if you change the recovery model to simple and size the log file to be around 5-6 GB..... btw is this a prod db??
Rohit
No we're not doing Log Backups but we do take backups of the entire disk.
Yes, this is a prod db

Then the best approach will be switch to simple and shrink log file to minimum and then size it back to 5-6 GB (this will reduce the count of VLF due to auto increment). Make sure you've taken a full db backup before doing anything.

Use below command to take db backup

use master

go

backup database <Your DB Name> to disk = '<Any local path>'

go

once the backup file is in place, do the above exercise.... it will be good if you've a window for this.. then schedule your full backup timely and you're done.

Rohit