asked on

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?

Anthony Perkins

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.

agradmin

ASKER

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

ASKER CERTIFIED SOLUTION

Scott Pletcher

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

agradmin

ASKER

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 Pletcher

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.

agradmin

ASKER

Apologies, this is still active and we will update soon.