Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

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

Posted on 2008-10-08
5
Medium Priority
?
272 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 1500 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

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

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

by Mark Wills PIVOT is a great facility and solves many an EAV (Entity - Attribute - Value) type transformation where we need the information held as data within a column to become columns in their own right. Now, in some cases that is relatively…
In this article I will describe the Copy Database Wizard 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.
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…

610 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