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

Shrink Log File to Target Percentage Expand / Collapse
Author
Message
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: Yesterday @ 7:55 AM
Points: 1,157, Visits: 3,266
Comments posted to this topic are about the item Shrink Log File to Target Percentage

Tommy

Post #779868
Posted Tuesday, September 1, 2009 3:37 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, September 8, 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
Posted Tuesday, September 1, 2009 6:26 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Yesterday @ 7:55 AM
Points: 1,157, Visits: 3,266
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
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: Yesterday @ 7:55 AM
Points: 1,157, Visits: 3,266
Just FYI - procedure has been updated on SSC to account for checkpoint (previous post).

Tommy

Post #790394
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: Saturday, October 18, 2014 5:41 PM
Points: 1, Visits: 25
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
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: Yesterday @ 7:55 AM
Points: 1,157, Visits: 3,266
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
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: Yesterday @ 7:55 AM
Points: 1,157, Visits: 3,266
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
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: Yesterday @ 7:55 AM
Points: 1,157, Visits: 3,266
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