Solved

Prolonged downtime when rebuilding transaction log VLFs?

Posted on 2014-01-09
6
209 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
PeopleSoft Has Never Been Easier

PeopleSoft Adoption Made Smooth & Simple!

On-The-Job Training Is made Intuitive & Easy With WalkMe's On-Screen Guidance Tool.  Claim Your Free WalkMe Account Now

 

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

Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

Question has a verified solution.

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

Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties

632 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