Solved

Prolonged downtime when rebuilding transaction log VLFs?

Posted on 2014-01-09
6
200 Views
Last Modified: 2014-03-28
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?
0
Comment
Question by:agradmin
  • 3
  • 2
6 Comments
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 39768929
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
 

Author Comment

by:agradmin
ID: 39769156
"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
 
LVL 69

Accepted Solution

by:
ScottPletcher earned 500 total points
ID: 39769666
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
Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

 

Author Comment

by:agradmin
ID: 39771073
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
 
LVL 69

Expert Comment

by:ScottPletcher
ID: 39771323
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
 

Author Comment

by:agradmin
ID: 39884161
Apologies, this is still active and we will update soon.
0

Featured Post

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Suggested Solutions

Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

744 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now