• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 280
  • Last Modified:

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

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
david_32
Asked:
david_32
  • 2
  • 2
1 Solution
 
Kevin CrossChief Technology OfficerCommented:
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
 
David ToddSenior DBACommented:
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
 
david_32Author Commented:
do what twice?
0
 
Kevin CrossChief Technology OfficerCommented:
Here is an example of what I usually do which will clarify the comment above from dtodd:
http:Q_23646730.html
0
 
david_32Author Commented:
thanks.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: Amazon Web Services - Basic

Are you thinking about creating an Amazon Web Services account for your business? Not sure where to start? In this course you’ll get an overview of the history of AWS and take a tour of their user interface.

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now