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

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

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+).