Prolonged downtime when rebuilding transaction log VLFs?

We are planning to defragment out transaction log files following the advice of Kimberley Tripp (http://www.sqlskills.com/blogs/kimberly/transaction-log-vlfs-too-many-or-too-few/) and are to get a grasp on how long of downtime to plan.  We have not done this before and would like to get some advice from others who have.  

Since we are first backing up the transaction log and doing a shrinkfile, I expect the 100 Gig transaction log will have minimal data and we will encounter minimal downtime... and can plan a maintenance window of 30 minutes.  

If you have experience defragmenting your transaction log files, how big is your transaction log and how long did it take for you?
LVL 1
agradminAsked:
Who is Participating?
 
Scott PletcherSenior DBACommented:
An explicit shrink on just the log file:
DBCC SHRINKFILE ( 2, 1 )
is fine.

But you still have several things to do to prepare for this.

1) Verify that sys.databases.log_reuse_wait_desc for that db has a value of "NOTHING".

2) Decide how big the final log needs to be, and, if more than ~4 gig, in what increments you wish to allocate the new log space: allocating all the space at once could make your VLFs too large.  Also, decide how much you want the log to grow if it does need to grow again in the future.

3) Prepare commands to make the changes -- NEVER use the gui for log work.

4) Remember that log space must be pre-formatted before use.  Create a test database, shrink its log to the minimum, then grow it to the same log size to see how long that pre-formatting takes.


DBCC SHRINKFILE ( 2, 1 )
-- assume for this example that you've decided on 20GB of log space total, with a
-- 200MB growth amount; I've allocated it in stages to prevent overly-large VLFs.
ALTER DATABASE [<db_name>] MODIFY FILE ( NAME = <logical_log_name>, SIZE = 6GB, FILEGROWTH = 200MB )
ALTER DATABASE [<db_name>] MODIFY FILE ( NAME = <logical_log_name>, SIZE = 13GB )
ALTER DATABASE [<db_name>] MODIFY FILE ( NAME = <logical_log_name>, SIZE = 20GB )
0
 
Anthony PerkinsCommented:
Since we are first backing up the transaction log and doing a shrinkfile,
Don't do DBCC SHRINKFILE as a scheduled event .  That is a very bad idea and is probably why your Transaction log is fragmented in the first place.


It should not take you a minute to do a Transaction log file that is say 10GB.
0
 
agradminAuthor Commented:
"Don't do DBCC SHRINKFILE as a scheduled event ."  

We would not ever schedule such an event and agree that is a very bad idea.  The log file is fragmented due to Autogrowth that was left unattended for a while.
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
agradminAuthor Commented:
That is excellent advice, thank you ScottPletcher.  

One follow up question on 3).  Is it ok to use a SSMS query window to execute the command or are you suggesting to run the command through an agent job?
0
 
Scott PletcherSenior DBACommented:
Yes, sorry, you can use a query window to run the commands, like the samples I gave above.  What I really meant was don't use the menus to do shrinks.
0
 
agradminAuthor Commented:
Apologies, this is still active and we will update soon.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.