Hi Steven,

Thanks for your reply. I'm not the one-man-band, I'm glad to say, but I sort of 
run the TSM stuff at the moment for the Windows clients, including TDP *and* am 
one of the people responsible for the Windows servers. Theses growing logfiles 
have impact on the whole system and as such I was asked to look for a fix. I 
haven't been around this company for a long time, and I'm not even sure we have 
a decent DBA in-house. But thanks for the hint, I hadn't looked at the problem 
from that angle.

Still, I would like to fix it myself :-)

 Cheers,
Rick


 On 10/31/07, Steven Harris <sjharris AT au1.ibm DOT com> wrote:
Rick,

I don't normally take this line,  I like to be helpful, but Why is this
your problem?

We have our areas of expertise,  DBAs have theirs.  Big logs falls neatly
into theirs IMO.  Or are you in one of those one-man-band shops?

Regards

Steve

Steven Harris
TSM Admin

Sydney Australia






            Rick Harderwijk
            <rick.harderwijk@
            GMAIL.COM>                                                 To
            Sent by: "ADSM:            ADSM-L AT VM.MARIST DOT EDU
            Dist Stor                                                  cc
            Manager"
            < [email protected]                                     Subject
            .EDU>                     [ADSM-L] Shrinking the SQL logs
                                      after TDP full backup

            31/10/2007 08:09
            AM


            Please respond to
            "ADSM: Dist Stor
                Manager"
            <[email protected]
                  .EDU>






Hi *

I have been staring at this problem for too long now and I need it fixed.
We
have some rather large transaction logs on our MSSQL2005 servers because
some backups went wrong for some time. Now that the backups are a-ok
again, I need to shrink those logfiles, and I could do that from osql with
dbcc shrinkfile, but I'd rather make it stick - so I thought I'd put it in
as part of the TDP backup cycle. However, I cannot figure out how to do it.
I do not have powershell installed on the machines, so that is out. DMO is
a
way to go, but will be discontinued, so why bother and I just can't get it
right with SMO and VBScript - I'm no expert in VBScripting, but with the
right examples I usually can manage something that seems pretty basic to me
as lined out below...

What I need is:

1. Check what databases are on the server (as new databases are added over
time I do not wish to rewrite the script every time)
2. Get the physical names of the logfiles for each and every one of those
databases
3. Shrink the logfiles so it will free up diskspace.

@2 - I might need to add an extra check to make sure I only shrink the
logfiles of the databases that are in full recovery mode.

I'm sure people are doing this - otherwise all the transaction logs would
be
way too big - but I just cannot find the info I need.

I was hoping maybe you could help...


Cheers,

Rick