Solved

T-LOG Shrink - Need Script to run a post process after 3rd party backup completes

Posted on 2008-06-26
3
395 Views
Last Modified: 2012-06-21
I am using commvault sql ida to backup my sql databases - i'm wanting to run a post-process task that causes the t-log to shrink.  i'm not very good with sql, so i'm guessing i need some type of dbcc shrinkfile command - just now sure how to create a file or bat file to execute it.
0
Comment
Question by:ltidrilling
[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
3 Comments
 
LVL 51

Expert Comment

by:Mark Wills
ID: 21883458
Some of it depends on your recovery mode. If "simple" then it will take care of itself. If "full" then you need to take care of it, but, would also be expecting a reasonable frequency of transaction log backups so that you can use the log for recovery purposes. The reason for this is fairly simple in so much as the log file size and "cleanliness" is pretty much determined by recovery mode and backup frequency. By backup, I am talking about the one inside SQL server. Given the nature of this question, would hazard a guess that it is not happening...

Now, on top of that, there are two aspects of database file sizing. The first is how much is allocated to disk. The second is how "full" that allocation is, or how much is being used. When approaching physical capacity the database files will automatically grow (can be controlled, but lets assume not for the moment). To manage that growth, you do need to keep on top of database management. It is a bit of a balancing act, you ideally have the database size / disk allocation set at the proper level to allow sufficient capacity whilst minimising the requirement to auto grow the disk files.

The easiest way to do that is to use a Maintenance plan. That can do a backup and keep the log file nice and clean. It is also this backup that you would use to recover a database from. And in a full recovery mode, you need both the starting point (ie the database backup), and the transaction log backups. Now, something to consider, is to change to "simple" recovery mode, unless the criticality of the database is such that you need optimal opportunity to recover the database to the most recent state. If "overnight" has been working for you, then go to Simple recovery and do a full SQL backup every night and the transaction log will be clean. If higher recovery modes are required, then you will have to generate transaction logs backups that best represent the granularity of recovery that you require.

Using Sql Server Management Studio, go to MANAGEMENT in the object explorer (toward the bottom left), expand it and you will see "Maintenance Plans". Right click on maintenance plans, and select the maintenance plan wizard. Here you can select Full Backups, transaction logs, shrink files etc... Very first step is to make sure you have a full backup... So, we will start there.

You can choose multiple options and then sequence them accordingly. Then you select the databases, and choose the "disk" option, and create a backup fuile for every database, and create a sub-directory... Choose the path fairly carefully - allow two or three time the size of the database as minimal free capacity. Then you can choose a schedule so that it automatically runs at a set time... And there you have it - an automated task that can create backups and shrink databases and even do transaction log backups (depending on the options you have selected).

If you need immediate relief, you can, in Sql server Management Studio, right click on the database, go to tasks, select shrink, then files and use the drop down to select either data or log - log files are generally pretty quick, data can take longer. Those pop-up screen will also show you capacity, size etc, so it is a pretty good tool to visualise capacity...


If you need immediate relief in database size, then you can right click on the

0
 

Author Comment

by:ltidrilling
ID: 21885252
Thanks for the info!!  I'm trying to find a way around the maintenance plans on individual servers though.  We have about 60 SQL servers in full recovery mode - we do a full backup once a week using commvault, and tlogs once an hour.  I'm wanting to run a "post process" that is run automatically as soon as the full is run.  The databases themselves are from our SAP systems - the tlogs tend to add up fairly quickly (200GB+).  
0
 
LVL 51

Accepted Solution

by:
Mark Wills earned 125 total points
ID: 21886103
You can create a script to do it... The equivelant of the maintenance plan that is... such as a full backup :

BACKUP DATABASE <Database_Name, sysname, Database_Name> TO  DISK = N'<Backup_Path,,C:\Backup\><Database_Name, sysname, Database_Name>.bak'

check out books online under Backup Database
0

Featured Post

Free Tool: Postgres Monitoring System

A PHP and Perl based system to collect and display usage statistics from PostgreSQL databases.

One of a set of tools we are providing to everyone 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

Suggested Solutions

this article is a guided solution for most of the common server issues in server hardware tasks we are facing in our routine job works. the topics in the following article covered are, 1) dell hardware raidlevel (Perc) 2) adding HDD 3) how t…
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.
In a recent question (https://www.experts-exchange.com/questions/29004105/Run-AutoHotkey-script-directly-from-Notepad.html) here at Experts Exchange, a member asked how to run an AutoHotkey script (.AHK) directly from Notepad++ (aka NPP). This video…
Attackers love to prey on accounts that have privileges. Reducing privileged accounts and protecting privileged accounts therefore is paramount. Users, groups, and service accounts need to be protected to help protect the entire Active Directory …

749 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