Solved

t-sql to modify transaction log size to be 25% of database

Posted on 2008-10-08
5
267 Views
Last Modified: 2012-05-05
Hi Folks,

I want the t-sql to modify transaction log size to be 25% of database. I want to be able to script this so using profiler or any other tool is no good for me.
0
Comment
Question by:david_32
[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
  • 2
  • 2
5 Comments
 
LVL 60

Accepted Solution

by:
Kevin Cross earned 500 total points
ID: 22675149
You can use DBCC SHRINKFILE.

Here is the reference from Microsoft: http://msdn.microsoft.com/en-us/library/ms189493.aspx

It even shows example of how to find the size of the database -- multiplying that by 0.25 you can get the target size to use for SHRINKFILE.
0
 
LVL 35

Expert Comment

by:David Todd
ID: 22675449
Hi,

You may need to do this up to twice prior to attempting to shrink the log file. Note that you will need to take a full backup reasonably soon after doing this to a production database!!!

(from http://technet.microsoft.com/en-us/library/ms186865(SQL.90).aspx)
Truncating the Transaction Log (breaks the log chain)
BACKUP LOG { database_name | @database_name_var }
  WITH { NO_LOG | TRUNCATE_ONLY }
[;]

HTH
  David
0
 

Author Comment

by:david_32
ID: 22675465
do what twice?
0
 
LVL 60

Expert Comment

by:Kevin Cross
ID: 22675488
Here is an example of what I usually do which will clarify the comment above from dtodd:
http:Q_23646730.html
0
 

Author Closing Comment

by:david_32
ID: 31504521
thanks.
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

So every once in a while at work I am asked to export data from one table and insert it into another on a different server.  I hate doing this.  There's so many different tables and data types.  Some column data needs quoted and some doesn't.  What …
In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
In this video we outline the Physical Segments view of NetCrunch network monitor. By following this brief how-to video, you will be able to learn how NetCrunch visualizes your network, how granular is the information collected, as well as where to f…
Michael from AdRem Software outlines event notifications and Automatic Corrective Actions in network monitoring. Automatic Corrective Actions are scripts, which can automatically run upon discovery of a certain undesirable condition in your network.…

705 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