Tom, Thanks. That's one way to tackle this issue, and it will work great for a one-time-only thing, but if you want to do this regularly, following a scheduled TDP Full+Log backup, this will become really hard, taking into account that we have machines hosting 25+ databases.
I have been checking out VBScript together with SMO, but that doesn't seem to work (having trouble getting to the filename properties of the logfile in the management objects)... Cheers, Rick -----Original Message----- From: ADSM: Dist Stor Manager [mailto:[EMAIL PROTECTED] On Behalf Of Quirt, Tom Sent: woensdag 31 oktober 2007 15:44 To: ADSM-L@VM.MARIST.EDU Subject: Re: [ADSM-L] Shrinking the SQL logs after TDP full backup In sql enterprise manager right click on the database and go to properties. Look at the options tab put a checkmark in the shrink log box. Within TSM from the sql gui check the truncate log box in the backup tab. Should do it. -----Original Message----- From: ADSM: Dist Stor Manager [mailto:[EMAIL PROTECTED] On Behalf Of Steven Harris Sent: Wednesday, October 31, 2007 12:20 AM To: ADSM-L@VM.MARIST.EDU Subject: Re: [ADSM-L] Shrinking the SQL logs after TDP full backup 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@VM.MARIST.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
