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?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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.
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.
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 )

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Get Blueprints for Increased Customer Retention

The IT Service Excellence Tool Kit has best practices to keep your clients happy and business booming. Inside, you’ll find everything you need to increase client satisfaction and retention, become more competitive, and increase your overall success.

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?
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.
agradminAuthor Commented:
Apologies, this is still active and we will update soon.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2008

From novice to tech pro — start learning today.