Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 


 
     
Recent Posts
   
Popular Topics
Home   
Search
    Members    Calendar    Who's On

Home » Article Discussions » Article Discussions by Author » Discuss content posted by Tommy Bollhofer » Shrink Log File to Target Percentage
Add to briefcase

Shrink Log File to Target Percentage Expand / Collapse
Rate Topic
Display Mode
Topic Options
Author
Message
Tommy Bollhofer
Posted Monday, August 31, 2009 7:02 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Thursday, May 16, 2013 12:43 PM
Points: 1,148, Visits: 3,148
Comments posted to this topic are about the item Shrink Log File to Target Percentage

Tommy
Post #779868
hannesm
Posted Tuesday, September 01, 2009 3:37 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, September 08, 2009 2:40 AM
Points: 2, Visits: 26
This seems to be a great piece of work....
One question though - it seems as if there is a line missing after the last checkpoint ???

    
if (select cast(databasepropertyex(db_name(), 'recovery') as varchar(10))) = 'FULL'
    begin
     set @backup_file_name = upper(db_name()) + '_Log_' + replace(replace(replace(convert(varchar(50), getdate(), 120), '-', ''), ' ', ''), ':', '') + '.lbk'
     set @sql = 'backup log ' + @database_name + ' to disk = ''' + @backup_log_file_path + @backup_file_name + ''' '
     exec sp_executesql @sql 
     set @sql = 'checkpoint'
    end
Post #780527
Tommy Bollhofer
Posted Tuesday, September 01, 2009 6:26 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Thursday, May 16, 2013 12:43 PM
Points: 1,148, Visits: 3,148
This is an artifact of copy and paste :) You can safely comment out the "set @sql = 'checkpoint'" after the backup. It won't do any harm in however I'll be sure to post a new revision without it - good catch!

Tommy
Post #780625
Tommy Bollhofer
Posted Friday, September 18, 2009 7:54 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Thursday, May 16, 2013 12:43 PM
Points: 1,148, Visits: 3,148
Just FYI - procedure has been updated on SSC to account for checkpoint (previous post).

Tommy
Post #790394
dlucas-615847
Posted Saturday, September 19, 2009 3:30 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, April 11, 2013 1:45 PM
Points: 1, Visits: 20
When I try to creat this stored procedure, I get two syntax errors:

Msg 102, Level 15, State 1, Procedure usp_maintain_database_log, Line 107
Incorrect syntax near ' '.
Msg 102, Level 15, State 1, Procedure usp_maintain_database_log, Line 111
Incorrect syntax near '#results'.

I'm not sure why.
Post #790842
Tommy Bollhofer
Posted Sunday, September 20, 2009 11:07 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Thursday, May 16, 2013 12:43 PM
Points: 1,148, Visits: 3,148
dlucas-615847 (9/19/2009)
When I try to creat this stored procedure, I get two syntax errors:

Msg 102, Level 15, State 1, Procedure usp_maintain_database_log, Line 107
Incorrect syntax near ' '.
Msg 102, Level 15, State 1, Procedure usp_maintain_database_log, Line 111
Incorrect syntax near '#results'.

I'm not sure why.


I can't seem to reproduce it but check the quotes ('). I've run into some problems with the WYSWYG editor on SSC when uploading scripts. You can e-mail me at tbollhofer2@gmail.com if you continue to have problems and I'll send you a copy. I've been running this in production for some time now.

Thanks :)

Tommy
Post #790905
Tommy Bollhofer
Posted Monday, September 21, 2009 12:00 PM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Thursday, May 16, 2013 12:43 PM
Points: 1,148, Visits: 3,148
Tommy Bollhofer (9/20/2009)
dlucas-615847 (9/19/2009)
When I try to creat this stored procedure, I get two syntax errors:

Msg 102, Level 15, State 1, Procedure usp_maintain_database_log, Line 107
Incorrect syntax near ' '.
Msg 102, Level 15, State 1, Procedure usp_maintain_database_log, Line 111
Incorrect syntax near '#results'.

I'm not sure why.


I can't seem to reproduce it but check the quotes ('). I've run into some problems with the WYSWYG editor on SSC when uploading scripts. You can e-mail me at tbollhofer2@gmail.com if you continue to have problems and I'll send you a copy. I've been running this in production for some time now.

Thanks :)


Update: I can reproduce this by copying the T-SQL from SSC so I'm assuming this is related to the WYSWYG editor. I just uploaded a new version which should post later this afternoon or sometime tomorrow morning. If this continues to be a problem I will reach out to Steve Jones to see if we can add a resource section so that the procedure can be downloaded directly.

In the interim, please feel free to e-mail me at tbollhofer2@gmail.com and I'll e-mail you a copy.

Thanks!

Tommy
Post #791339
Tommy Bollhofer
Posted Monday, September 21, 2009 1:30 PM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Thursday, May 16, 2013 12:43 PM
Points: 1,148, Visits: 3,148
Tommy Bollhofer (9/21/2009)
Tommy Bollhofer (9/20/2009)
dlucas-615847 (9/19/2009)
When I try to creat this stored procedure, I get two syntax errors:

Msg 102, Level 15, State 1, Procedure usp_maintain_database_log, Line 107
Incorrect syntax near ' '.
Msg 102, Level 15, State 1, Procedure usp_maintain_database_log, Line 111
Incorrect syntax near '#results'.

I'm not sure why.


I can't seem to reproduce it but check the quotes ('). I've run into some problems with the WYSWYG editor on SSC when uploading scripts. You can e-mail me at tbollhofer2@gmail.com if you continue to have problems and I'll send you a copy. I've been running this in production for some time now.

Thanks :)


Update: I can reproduce this by copying the T-SQL from SSC so I'm assuming this is related to the WYSWYG editor. I just uploaded a new version which should post later this afternoon or sometime tomorrow morning. If this continues to be a problem I will reach out to Steve Jones to see if we can add a resource section so that the procedure can be downloaded directly.

In the interim, please feel free to e-mail me at tbollhofer2@gmail.com and I'll e-mail you a copy.

Thanks!


The new version has been posted to SSC. I've tested and verified everything is working. To avoid any formatting issues; i.e. UNICODE, etc., I would recommend copying and pasting the code into SSMS.

Thanks!

Tommy
Post #791396
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse

 
 
Copyright © 2002-2013 Simple Talk Publishing. All Rights Reserved. Privacy Policy. Terms of Use. Report Abuse.