Solved

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

Posted on 2008-06-26
3
405 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

Get HTML5 Certified

Want to be a web developer? You'll need to know HTML. Prepare for HTML5 certification by enrolling in July's Course of the Month! It's free for Premium Members, Team Accounts, and Qualified Experts.

Question has a verified solution.

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

Learn about cloud computing and its benefits for small business owners.
Moving your enterprise fax infrastructure from in-house fax machines and servers to the cloud makes sense — from both an efficiency and productivity standpoint. But does migrating to a cloud fax solution mean you will no longer be able to send or re…
Michael from AdRem Software outlines event notifications and Automatic Corrective Actions in network monitoring. Automatic Corrective Actions are scripts, which can automatically run upon discovery of a certain undesirable condition in your network.…
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…

630 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