Solved

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

Posted on 2008-10-08
5
262 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
  • 2
  • 2
5 Comments
 
LVL 59

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 59

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 Gift Card with Acronis Backup Purchase!

Backup any data in any location: local and remote systems, physical and virtual servers, private and public clouds, Macs and PCs, tablets and mobile devices, & more! For limited time only, buy any Acronis backup products and get a FREE Amazon/Best Buy gift card worth up to $200!

Join & Write a Comment

If you having speed problem in loading SQL Server Management Studio, try to uncheck these options in your internet browser (IE -> Internet Options / Advanced / Security):    . Check for publisher's certificate revocation    . Check for server ce…
by Mark Wills Attending one of Rob Farley's seminars the other day, I heard the phrase "The Accidental DBA" and fell in love with it. It got me thinking about the plight of the newcomer to SQL Server...  So if you are the accidental DBA, or, simp…
It is a freely distributed piece of software for such tasks as photo retouching, image composition and image authoring. It works on many operating systems, in many languages.
Internet Business Fax to Email Made Easy - With eFax Corporate (http://www.enterprise.efax.com), you'll receive a dedicated online fax number, which is used the same way as a typical analog fax number. You'll receive secure faxes in your email, fr…

760 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

19 Experts available now in Live!

Get 1:1 Help Now