Is our log file too big?

Question

Is our log file too big?

Viewing 15 posts - 31 through 45 (of 54 total)

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

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

No. Absolutely not.

Constant shrink/grow causes all sorts of subtle performance problems.

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

Grant Fritchey SSC Guru Points: 397721 More actions · Answer 2

No, it shouldn't. If you've got appropriate log backups in place, in general, the size of the log is how big it needs to be to suppor the transaction load you're under. That load may change with time (usually getting bigger) but it ought to be fairly predictable. You just have to watch for batch load processes & stuff like that.

No, I don't recommend shrinking the log regularly.

----------------------------------------------------The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software

homebrew01 SSC Guru Points: 55454 More actions · Answer 3

Matt-1034261 (8/31/2010)
So if I had a maintenance plan that backed up the log files every so often, would that maintenance plan also need to do a shrink on the log files as well?

I back up production log files at least once an hour. More frequently for some DBs.

Once you have a good plan in place, you shouldn't need to shrink the log file again unless some extraordinary event causes it to grow a huge amount. Even then, you could leave it alone unless you need the space.

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

homebrew01 (8/31/2010)
I back up production log files at least once an hour. More frequently for some DBs.
Once you have a good plan in place, you shouldn't need to shrink the log file again unless some extraordinary event causes it to grow a huge amount. Even then, you could leave it alone unless you need the space.

Do you have it so that each backup overwrites the last? There doesn't seem to be an option in the maintenance plan setup to assign them different names

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

The maintenance plan adds a timestamp to the filename.

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 6

GilaMonster (8/31/2010)
The maintenance plan adds a timestamp to the filename.

okay thanks

homebrew01 SSC Guru Points: 55454 More actions · Answer 7

Matt-1034261 (8/31/2010)
Do you have it so that each backup overwrites the last? There doesn't seem to be an option in the maintenance plan setup to assign them different names

Don't overwrite your log backups ! You may need them all in case of disaster recovery !

SQL will append a timestamp to separate them. Create a "Clean-up" task in your Maint plan to delete them older than x days. It's common practice to keep a week or so available on a drive, along with your FULL backups, while also copying them to tape so you can go back some period in time if need be.

You could search something like "backup strategy" for some more reading and ideas.

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

Okay, so I went to to work on the log files to try and reduce them in size. The log files have now grown to just over 160 Gb which seems quite large.

Anyway, I checked the recovery model and it was set to simple so I changed it to full.

Then I did the following :-

Backed up the log file

Run Checkpoint Command

Run the Log Backup again

Run the Shrink application on the log files

After doing this the log file has remained the same size. After doing the above on a number of test databases the log file shrank considerably.

Any suggestions as to what the problem is?

SQLRNNR SSC Guru Points: 281324 More actions · Answer 9

try using dbcc shrinkfile instead

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

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

I wonder if the problem is something to do with the database running in simple recovery mode up until today?

SQLRNNR SSC Guru Points: 281324 More actions · Answer 11

Shouldn't be. Once you have a full backup and then a tran backup (and since you also manually checkpointed) you should have adequate free space in the file and it just needs to be manually shrunk. The method that works most consistently for that is the dbcc shrinkfile.

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

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

CirquedeSQLeil (9/4/2010)
Shouldn't be. Once you have a full backup and then a tran backup (and since you also manually checkpointed) you should have adequate free space in the file and it just needs to be manually shrunk. The method that works most consistently for that is the dbcc shrinkfile.

Thanks Jason, how do you use this command?

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

I foun this on the Microsoft site: -

DBCC SHRINKFILE (<FileName>, <TargetSize>) WITH NO_INFOMSGS

but how do I know what to set the target size to?

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

Query sys.databases. See what the log_reuse_wait_desc is for that database.

As for the target size, that's how small you want the log. Pick a value based on what you know of the DB activity and, if in full recovery, the log backup frequency.

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 15

GilaMonster (9/5/2010)
Query sys.databases. See what the log_reuse_wait_desc is for that database.
As for the target size, that's how small you want the log. Pick a value based on what you know of the DB activity and, if in full recovery, the log backup frequency.

Thanks Gail

The log_reuse_wait_desc says REPLICATION, what does that mean?

I now have a mintenance plan to back up the log files every hour. The db has high activity, so could I use say 4 GB?