Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase ««12

Shrink DB and Log Expand / Collapse
Author
Message
Posted Monday, May 6, 2013 6:47 AM


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Yesterday @ 6:43 AM
Points: 335, Visits: 299
I have to agree with Paul.
I make my living as a DBA and this is not good.
Several things about the script would make most professional DBA's wish they never have to repair a server it was not correctly run on.
The main reason anyone would need to run this script is because the database Backup and maintenance Plan does not accomadate the transaction level for the database or database growth.

Please be very carefull if you try to use this script.
Post #1449696
Posted Monday, May 6, 2013 7:31 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Thursday, June 26, 2014 7:28 AM
Points: 257, Visits: 902
Paul Randal (5/6/2013)
I hate to be negative, but it's extremely unfortunate that this script truncates the log, breaking the log backup chain, and that isn't explained anywhere in the script or the comments. Even if it was explained, this resets all your disaster recovery options afterwards to starting with the full backup it advises you to take at the end. Previous backups cannot be used to recover past the log truncation.

Be extremely wary about using this script in production. There's a reason we (as I was on the SQL team at the time) removed the TRUNCATE_ONLY/NO_LOG options to BACKUP LOG in SQL Server 2008.

Thanks


thank you for explaining. I had a suspicion that something was scary about this script.

There are comments, but there are no explanations. I kept looking for some information on how/why this series of steps was affecting the proposed change. I appreciate that nobody should be running code they find online without understanding exactly what it does... but it would be nice to be told that inline with the commands rather than creating a research project for me.

An overview of why this solution is 'better' than the tools provided by Microsoft would be an interesting read too. My guess is that you don't use this script on trivial databases because it isn't necessary. I read Paul's suggestion that you don't use this script on appreciably large/important databases because it is dangerous. Please explain the circumstances where this script _should_ be used (caveats, risks, etc.)

Also, if you are so inclined - share with us any "gone wrong" experiences while writing/debugging this script if you thrashed a DB before you got it working :)
Post #1449715
Posted Monday, May 6, 2013 10:21 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Tuesday, January 21, 2014 9:31 AM
Points: 16, Visits: 171
In fairness the OP does state: "...Make sure that you understand the implications of this script on the backup and recovery of your database(s): Managing Transaction Logs."

The linked article was: http://www.sqlservercentral.com/articles/Administration/64582/

I agree with other comments posted to use caution and be wary of using the script. To that end, perhaps the OP could have made this above statement more prominent or provided ample warning/comments within the script.

As with consuming any scripts posted on the internet, one should be reviewing the script and understanding its use/impact and testing within their own environment before moving into production.
Post #1449784
Posted Monday, May 6, 2013 12:19 PM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Sunday, August 3, 2014 11:47 AM
Points: 2,038, Visits: 1,664
@bitBIG - that comment about understanding the implications was added this morning.

Paul Randal
CEO, SQLskills.com: Check out SQLskills online training!
Blog:www.SQLskills.com/blogs/paul Twitter: @PaulRandal
SQL MVP, Microsoft RD, Contributing Editor of TechNet Magazine
Author of DBCC CHECKDB/repair (and other Storage Engine) code of SQL Server 2005
Post #1449833
Posted Monday, May 6, 2013 12:31 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Tuesday, January 21, 2014 9:31 AM
Points: 16, Visits: 171
Thanks for noting that Paul.

It would appear that the OP has taken your contribution/comments to heart and (has now) provided the casual reader with some guidance.
Post #1449837
« Prev Topic | Next Topic »

Add to briefcase ««12

Permissions Expand / Collapse