Solved

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

Posted on 2008-06-26
3
364 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
  • 2
3 Comments
 
LVL 51

Expert Comment

by:Mark Wills
Comment Utility
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
Comment Utility
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
Comment Utility
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

Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

Join & Write a Comment

More or less everybody in the IT market understands the basics of Networking, however when we start talking about Storage Networks, things get a bit dizzier, and this is where I would like to help.
Hyper-convergence systems have taken the IT world by storm and have quickly started to change our point of view of how the data center should and could be architected. In this article, I’ll explain the benefits of employing a hyper-converged system …
In this seventh video of the Xpdf series, we discuss and demonstrate the PDFfonts utility, which lists all the fonts used in a PDF file. It does this via a command line interface, making it suitable for use in programs, scripts, batch files — any pl…
This video explains how to create simple products associated to Magento configurable product and offers fast way of their generation with Store Manager for Magento tool.

763 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

10 Experts available now in Live!

Get 1:1 Help Now