Solved

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

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

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

Data architecture is an important aspect in Software as a Service (SaaS) delivery model. This article is a study on the database of a single-tenant application that could be extended to support multiple tenants. The application is web-based develope…
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 …
I designed this idea while studying technology in the classroom.  This is a semester long project.  Students are asked to take photographs on a specific topic which they find meaningful, it can be a place or situation such as travel or homelessness.…
Need to grow your business through quality cloud solutions? With everything required to build a cloud platform and solution, you may feel like the distance between you and the cloud is quite long. Help is here. Spend some time learning about the Con…

911 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

20 Experts available now in Live!

Get 1:1 Help Now