Is it time to shrink my log file?

Question

Post reply

Is it time to shrink my log file?

WayneS

SSC Guru

Points: 95405
More actions
October 29, 2010 at 10:43 am

#388127

I have a database where the data file is 7.5 gb, the log file is 4.1gb.

The log file has 214 VLF's. (Kimberly's blog says > 50 should be reduced... :()

Daily process that reports on space used within files shows log file is using about 156mb on average since 20100801 - never higher, sometimes as low as 12mb.

I'm considering shrinking the log file - but I'm also hesitant to do so (I've read too much about not doing this). I feel it's too big; yet it was needed to be this high for something. It also seems to have too many VLFs (Autogrowth is set to 50mb.) It seems to me that it would be good to shrink it to remove the numerous VLFs, then add 1-2GB to it (which will add 8-16 VLFs). Also set the AutoGrowth to something like 500mb?

Does all of this sound reasonable?

Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!
Links:
For better assistance in answering your questions
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2

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

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

ps. One Orange Chip Points: 29255 More actions · Answer 1

ps.

One Orange Chip

Points: 29255

October 29, 2010 at 10:45 am

#1244334

You have already gone through the places where i would have gone to search for that answer:-P. I would probably do what Kimberly says. like shrinking it for once and resizing it to an appropriate level.

Pradeep Singh

Jeffrey Williams SSC Guru Points: 89773 More actions · Answer 2

This sounds very reasonable to me. With the increased size of the auto-growth, even if it grows out to 4GB again you won't have too many VLF's. Well, it will be more than what Kimberly recommends - but not out of control.

Of course, if it does grow out to that size again - you can then shrink it again and reset the auto-growth to 1GB to reduce the number of VLF's that would be added.

I would recommend that you keep it consistent and decide what size you want to increment by. Use that size when you grow the file out to the initial 1-2GB size, that way each VLF will be consistent in size. The first couple of VLF's will not be the same size - but the rest will be.

Jeffrey Williams
Problems are opportunities brilliantly disguised as insurmountable obstacles.

How to post questions to get better answers faster
Managing Transaction Logs

@SQLFRNDZ SSCrazy Eights Points: 9095 More actions · Answer 3

This is all when your log file is growing every day and causing issues you have to go through this process

But kimberly says you have to set it single user mode when you do this process.

Is that possible in your environment?

I have 151 VLF's but setting my database to single user mode is not possible in my company.

Thanks,

[font="Tahoma"]
--SQLFRNDZ[/url]
[/font]

Jeffrey Williams SSC Guru Points: 89773 More actions · Answer 4

pavan_srirangam (11/1/2010)

This is all when your log file is growing every day and causing issues you have to go through this process

But kimberly says you have to set it single user mode when you do this process.

Is that possible in your environment?

I have 151 VLF's but setting my database to single user mode is not possible in my company.

Thanks,

I don't know of any reference where it is stated that you have to be in single user mode to shrink or grow the log. If your log is growing every day, then you have a problem. Either, you are not backing up the transaction log, or you have a long running open transaction, or replication that is preventing the log from being truncated.

In all cases, there is no requirement for single user mode to correct the problem.

Jeffrey Williams
Problems are opportunities brilliantly disguised as insurmountable obstacles.

How to post questions to get better answers faster
Managing Transaction Logs

Jeff Moden SSC Guru Points: 1000539 More actions · Answer 5

I just went through this on a server that's relatively new to me. You [font="Arial Black"]don't[/font] have to set to single use mode to shrink the log file. My recommendation is to backup the log file before you do the shrink. Then set it to 1 (it'll try to do that) and immediately regrow it.

--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)
Intro to Tally Tables and Functions

Jeff Moden SSC Guru Points: 1000539 More actions · Answer 6

ps. (10/29/2010)

You have already gone through the places where i would have gone to search for that answer:-P. I would probably do what Kimberly says. like shrinking it for once and resizing it to an appropriate level.

What's the link for that particular article, please?

--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)
Intro to Tally Tables and Functions

WayneS SSC Guru Points: 95405 More actions · Answer 7

Jeff Moden (11/1/2010)

ps. (10/29/2010)

You have already gone through the places where i would have gone to search for that answer:-P. I would probably do what Kimberly says. like shrinking it for once and resizing it to an appropriate level.

What's the link for that particular article, please?

Here, step 8.

Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes

If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!

Links:
For better assistance in answering your questions
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2

Hardy21 SSCrazy Eights Points: 9708 More actions · Answer 8

WayneS (11/1/2010)

Jeff Moden (11/1/2010)

ps. (10/29/2010)

You have already gone through the places where i would have gone to search for that answer:-P. I would probably do what Kimberly says. like shrinking it for once and resizing it to an appropriate level.

What's the link for that particular article, please?

Here, step 8.

Nice article on Transaction Log. Thanks.

Thanks

Jeff Moden SSC Guru Points: 1000539 More actions · Answer 9

WayneS (11/1/2010)

Jeff Moden (11/1/2010)

ps. (10/29/2010)

You have already gone through the places where i would have gone to search for that answer:-P. I would probably do what Kimberly says. like shrinking it for once and resizing it to an appropriate level.

What's the link for that particular article, please?

Here, step 8.

Wow! I read the sub article to that. I realize it was just an example but I just can't imagine ever having a 24GB log file! 😛 Of course, most of the systems that I work on are batch oriented without tens of thousands of users hitting the database each day. I've only had to do that once. Even then, we kept the log file to just a gig and, because we did backups every 15 minutes, it never grew to over a gig.

Both articles were excellent, though. If you look at the "boundaries" set, you can make a log file to be very efficient. Thanks for posting the link.

--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)
Intro to Tally Tables and Functions

Perry Whittle SSC Guru Points: 233792 More actions · Answer 10

Kimberley Tripp

Wait for an inactive time of day (ideally, it would be best to put the database into single user mode first)

It's important to note that Kimberley merely advises that you may want to set the database single user to prevent further transactional activity. It's not required.

More importantly, identify an inactive period of the day and schedule the operation for this time!

-----------------------------------------------------------------------------------------------------------

"Ya can't make an omelette without breaking just a few eggs" 😉

WayneS SSC Guru Points: 95405 More actions · Answer 11

We're having an outage this weekend when we're upgrading to SQL 2008... I plan on using this time to do this.

Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes

If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!

Links:
For better assistance in answering your questions
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2

Perry Whittle SSC Guru Points: 233792 More actions · Answer 12

WayneS (11/2/2010)

We're having an outage this weekend when we're upgrading to SQL 2008... I plan on using this time to do this.

Good luck with the upgrade Wayne, be interesting to hear your experience from the upgrade if you're willing to share it with us

-----------------------------------------------------------------------------------------------------------

"Ya can't make an omelette without breaking just a few eggs" 😉

WayneS SSC Guru Points: 95405 More actions · Answer 13

Perry Whittle (11/2/2010)

WayneS (11/2/2010)

We're having an outage this weekend when we're upgrading to SQL 2008... I plan on using this time to do this.

Good luck with the upgrade Wayne, be interesting to hear your experience from the upgrade if you're willing to share it with us

We've already upgraded 13 instances in lower environments, with zero issues (upgrading to SQL 2008 (R1) w/ SP2 slipstreamed), including 2 clusters. We'll be upgrading 2 DR computers tomorrow (1 cluster), and the live prod servers this weekend (1 cluster, a 2nd instance on another cluster (which has had it's "primary" instance already upgraded), and a 3rd server. Next week we do the DR/Prod servers (no clusters) for the remaining application, and the week after a couple of stragglers. We've been testing the upgrade for a couple of months, both clustered and non-clustered upgrades. Up to now, everything has gone very smoothly.

Just make sure that you have run the Upgrade Advisor, and do whatever it suggests to get ready.

Oh - we did run into one thing post-upgrade. Users can't see non-dbo schemas in SSMS 2008 unless granted the "VIEW ANY DEFINITION" server-level permission. This is supposed to be fixed in SQL 11.

Edit: one other thing... it's been averaging about 3 hours per instance to upgrade (for a cluster, 5 hours for both nodes).

Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes

If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!

Links:
For better assistance in answering your questions
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2

alen teplitsky SSC-Dedicated Points: 30014 More actions · Answer 14

alen teplitsky

SSC-Dedicated

Points: 30014

November 10, 2010 at 11:16 am

#1249073

i have log files that are larger than that, unless you need the space there is no reason to shrink

what are you going to do with blank hard drive space anyway?