Solved

Prolonged downtime when rebuilding transaction log VLFs?

Posted on 2014-01-09
6
208 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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:
Scott Pletcher 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
Ransomware: The New Cyber Threat & How to Stop It

This infographic explains ransomware, type of malware that blocks access to your files or your systems and holds them hostage until a ransom is paid. It also examines the different types of ransomware and explains what you can do to thwart this sinister online threat.  

 

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:Scott Pletcher
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

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
Via a live example, show how to shrink a transaction log file down to a reasonable size.
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.

739 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