April 2, 2002 at 8:55 am
I have a log which grew to a predicted point, now I have to shrink it. I applie the script posted here and written by Andrew Zanevsky.
I am getting an error when try to run it. Maybe I am doing something wrong here (that's for sure) and need help. Please!!
The script is as follows:
use master
go
if object_id( 'sp_force_shrink_log' ) is not null drop proc sp_force_shrink_log
go
use master
create proc sp_force_shrink_log
go
/*
*************************************************************
Name: sp_force_shrink_log
Description: Shrink transaction log of the current database in SQL Server
Switch context to proper db to execute.
Usage:exec sp_force_shrink_log <target_percent>, <target MB>, <iterations>, <backup options>
Sample: exec pubs..sp_force_shrink_log
Author: Andrew Zanevsky, AZ Databases, Inc., 12/25/1999, v5 - 08/18/2000
Input Params:
-------------
@target_percent tinyint. default = 0. Target percentage of remaining shrinkable
space. Defaults to max possible.
@target_size_MB int. default = 25000. Target size of final log in MB.
@max_iterations int. default = 1000. Number of loops (max) to run proc through.
@backup_log_opt nvarchar(1000). default = 'with truncate_only'. Backup options.
Output Params:
--------------
Return:
Results:
---------
Locals:
--------
@errHolds error value
Modifications:
--------------
*************************************************************
*/
@target_percent tinyint = 0,
@target_size_MB int = 25000,
@max_iterations int = 1000,
@backup_log_opt nvarchar(1000) = 'with truncate_only'
as
set nocount on
declare @db sysname,
@last_row int,
@log_size decimal(15,2),
@unused1 decimal(15,2),
@unused decimal(15,2),
@shrinkable decimal(15,2),
@iteration int,
@file_max int,
@file int,
@fileid varchar(5)
select @db = db_name(),
@iteration = 0
create table #loginfo (
id int identity,
FileId int,
FileSize numeric(22,0),
StartOffset numeric(22,0),
FSeqNo int,
Status int,
Parity smallint,
CreateTime datetime
)
create unique clustered index loginfo_FSeqNo on #loginfo ( FSeqNo, StartOffset )
create table #logfiles ( id int identity(1,1), fileid varchar(5) not null )
insert #logfiles ( fileid ) select convert( varchar, fileid ) from sysfiles where status & 0x40 = 0x40
select @file_max = @@rowcount
if object_id( 'table_to_force_shrink_log' ) is null
exec( 'create table table_to_force_shrink_log ( x nchar(3000) not null )' )
insert #loginfo ( FileId, FileSize, StartOffset, FSeqNo, Status, Parity, CreateTime ) exec ( 'dbcc loginfo' )
select @last_row = @@rowcount
select @log_size = sum( FileSize ) / 1048576.00,
@unused = sum( case when Status = 0 then FileSize else 0 end ) / 1048576.00,
@shrinkable = sum( case when id < @last_row - 1 and Status = 0 then FileSize else 0 end ) / 1048576.00
from #loginfo
select @unused1 = @unused -- save for later
select 'iteration' = @iteration,
'log size, MB' = @log_size,
'unused log, MB' = @unused,
'shrinkable log, MB' = @shrinkable,
'shrinkable %' = convert( decimal(6,2), @shrinkable * 100 / @log_size )
while @shrinkable * 100 / @log_size > @target_percent
and @shrinkable > @target_size_MB
and @iteration < @max_iterations begin
select @iteration = @iteration + 1 -- this is just a precaution
exec( 'insert table_to_force_shrink_log select name from sysobjects
delete table_to_force_shrink_log')
select @file = 0
while @file < @file_max begin
select @file = @file + 1
select @fileid = fileid from #logfiles where id = @file
exec( 'dbcc shrinkfile( ' + @fileid + ' )' )
end
exec( 'backup log [' + @db + '] ' + @backup_log_opt )
truncate table #loginfo
insert #loginfo ( FileId, FileSize, StartOffset, FSeqNo, Status, Parity, CreateTime ) exec ( 'dbcc loginfo' )
select @last_row = @@rowcount
select @log_size = sum( FileSize ) / 1048576.00,
@unused = sum( case when Status = 0 then FileSize else 0 end ) / 1048576.00,
@shrinkable = sum( case when id < @last_row - 1 and Status = 0 then FileSize else 0 end ) / 1048576.00
from #loginfo
select 'iteration' = @iteration,
'log size, MB' = @log_size,
'unused log, MB' = @unused,
'shrinkable log, MB' = @shrinkable,
'shrinkable %' = convert( decimal(6,2), @shrinkable * 100 / @log_size )
end
if @unused1 < @unused
select 'After ' + convert( varchar, @iteration ) +
' iterations the unused portion of the log has grown from ' +
convert( varchar, @unused1 ) + ' MB to ' +
convert( varchar, @unused ) + ' MB.'
union all
select'Since the remaining unused portion is larger than 10 MB,' where @unused > 10
union all
select'you may try running this procedure again with a higher number of iterations.' where @unused > 10
union all
select'Sometimes the log would not shrink to a size smaller than several Megabytes.' where @unused <= 10
else
select 'It took ' + convert( varchar, @iteration ) +
' iterations to shrink the unused portion of the log from ' +
convert( varchar, @unused1 ) + ' MB to ' +
convert( varchar, @unused ) + ' MB'
exec( 'drop table table_to_force_shrink_log' )
go
if object_id( 'sp_force_shrink_log') Is Null
select 'sp_force_shrink_log Not Created'
else
select 'sp_force_shrink_log Created'
go
-----------
Where I am failing? The results I am getting reads:
Server: Msg 111, Level 15, State 1, Line 2
'CREATE PROCEDURE' must be the first statement in a query batch.
Server: Msg 137, Level 15, State 2, Line 2
Must declare the variable '@target_percent'.
Server: Msg 137, Level 15, State 1, Line 59
Must declare the variable '@target_percent'.
Server: Msg 137, Level 15, State 1, Line 74
Must declare the variable '@backup_log_opt'.
(1 row(s) affected)
Any help will be much appreciated.
April 2, 2002 at 9:45 am
April 2, 2002 at 9:50 am
Thanks Steve I did it and still getting errors
Errors reads:
Server: Msg 170, Level 15, State 1, Procedure sp_force_shrink_log, Line 1
Line 1: Incorrect syntax near 'sp_force_shrink_log'.
Server: Msg 137, Level 15, State 2, Line 37
Must declare the variable '@target_percent'.
Server: Msg 137, Level 15, State 1, Line 94
Must declare the variable '@target_percent'.
Server: Msg 137, Level 15, State 1, Line 109
Must declare the variable '@backup_log_opt'.
(1 row(s) affected)
I do understand that the main error is the syntax error, the others are related to the first one. If I can't create the store proc of course my declarations are going to give me errors too.
Thank you for your help
April 3, 2002 at 1:39 am
You will get this error because you have a go after create proc sp_force_shrink_log and not after use master. The code snippet should look like:
use master
go
create proc sp_force_shrink_log
-- Comments
@target_percent tinyint = 0,
@target_size_MB int = 25000,
@max_iterations int = 1000,
etc.
April 3, 2002 at 6:01 am
Theses errors are all related to the incorrect placement of the go as stated in the previous messages, so all should clear up as soon as you make the change noted.
"Don't roll your eyes at me. I will tape them in place." (Teacher on Boston Public)
April 3, 2002 at 6:34 am
Thank you guys. As always great help. I figured out thta I had USE MASTER also in the wrong place and that was giving me another error. I corrected both the GO and the USE MASTER and everything is working as it should Thank you.
April 9, 2002 at 8:25 am
Help again with this shirnk store proc. Please!!
I fixed the syntax and the store proc was created fine. I did not have the change to run it until today. When I execute the store proc I got the following message
"Server: Msg 8115, Level 16, State 2, Line 1
Arithmetic overflow error converting expression to data type datetime."
I changed the datetime, use cast and convert and still getting this error message. Any ideas? Thank you
May 27, 2002 at 11:43 pm
Error is due to Design change in SQL2000 ie 7th Column is now LSN not DateTime(SQL7)
see line... exec ( 'dbcc loginfo' ) select @last_row = @@rowcount in code
Try in SQL7 and then note difference with SQL2000
Edited by - michaelr777 on 05/27/2002 11:46:23 PM
July 11, 2002 at 5:19 am
To get this to work in SQL2000, make the following alteration in the SP:
create table #loginfo (
id int identity,
FileId int,
FileSize numeric(22,0),
StartOffset numeric(22,0),
FSeqNo int,
Status int,
Parity smallint,
CreateTime varchar(25)
Notice that varchar(25) is used not datetime.
Cheers,
FH
quote:
Help again with this shirnk store proc. Please!!I fixed the syntax and the store proc was created fine. I did not have the change to run it until today. When I execute the store proc I got the following message
"Server: Msg 8115, Level 16, State 2, Line 1
Arithmetic overflow error converting expression to data type datetime."
I changed the datetime, use cast and convert and still getting this error message. Any ideas? Thank you
July 11, 2002 at 5:27 am
Note: it is best not to use in SQL 2000. Truncate log and shrink work just fine and the issue no longer exists as 7 suffered.
"Don't roll your eyes at me. I will tape them in place." (Teacher on Boston Public)
July 11, 2002 at 8:21 am
I agree with the last statement. I did modified the script a couple of weeks back and it did not run as expected. I am using shrink log and truncate log. It does not show right away though, but it works fine. I still testing some other ways just to play safe.
August 22, 2002 at 1:54 pm
Well I did have the problem happen on SQL Server 2000 SP1.
Used M$'s approach to fix it.
http://support.microsoft.com/default.aspx?scid=kb;en-us;Q256650
Tim Schwallie
Tim Schwallie
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply