Solved

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

Posted on 2008-10-08
5
263 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

Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

Question has a verified solution.

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

Suggested Solutions

There are some very powerful Data Management Views (DMV's) introduced with SQL 2005. The two in particular that we are going to discuss are sys.dm_db_index_usage_stats and sys.dm_db_index_operational_stats.   Recently, I was involved in a discu…
I've encountered valid database schemas that do not have a primary key.  For example, I use LogParser from Microsoft to push IIS logs into a SQL database table for processing and analysis.  However, occasionally due to user error or a scheduled task…
Windows 10 is mostly good. However the one thing that annoys me is how many clicks you have to do to dial a VPN connection. You have to go to settings from the start menu, (2 clicks), Network and Internet (1 click), Click VPN (another click) then fi…
Both in life and business – not all partnerships are created equal. As the demand for cloud services increases, so do the number of self-proclaimed cloud partners. Asking the right questions up front in the partnership, will enable both parties …

867 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

17 Experts available now in Live!

Get 1:1 Help Now