MS SQL Server - Stored Procedure Fails To Run!

Author

Stored Procedure Fails To Run!

Izzy

2006-11-09, 7:13 pm

I have this procedure set up to run once a week, it's been running fine
but the last 2 weeks it's failing.

Here is the code:

ALTER PROCEDURE [dbo].& #91;SVTN_UTIL_REINDE
XTABLES]
AS
SET NOCOUNT ON
DECLARE @TABLENAME VARCHAR(128)
DECLARE @EXECSTR VARCHAR(255)
DECLARE @MAXFRAG DECIMAL
DECLARE @FILLFACTOR TINYINT
DECLARE @LOGSIZELIMI
T DECIMAL(10,5)
DECLARE @FILENAME VARCHAR(100)
DECLARE @CURRENTDATE DATETIM
E

SELECT @MAXFRAG = 30.0
SELECT @FILLFACTOR = 80
SELECT @LOGSIZELIMIT = 200 --MB

DECLARE TABLES CURSOR FOR
SELECT TABLE_NAME
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_TYPE = 'BASE TABLE'

CREATE TABLE #FRAGLIST (
OBJECTNAME CHAR (255),
OBJECTID INT,
INDEXNAME CHAR (255),
INDEXID INT,
LVL INT,
COUNTPAGES INT,
COUNTROWS INT,
MINRECSIZE INT,
MAXRECSIZE INT,
AVGRECSIZE INT,
FORRECCOUNT INT,
EXTENTS INT,
EXTENTSWITCHES INT,
AVGFREEBYTES INT,
AVGPAGEDENSITY INT,
SCANDENSITY DECIMAL,
BESTCOUNT INT,
ACTUALCOUNT INT,
LOGICALFRAG DECIMAL,
EXTENTFRAG DECIMAL)

CREATE TABLE #LOGS
(
DATABASENAME VARCHAR
(25),
LOGSIZE DECIMAL(10,
5),
LOGSPACEUSED DECIMAL
(10,5),
LOGSTATUS TINYINT
)

OPEN TABLES

FETCH NEXT FROM TABLES INTO @TABLENAME

WHILE @@FETCH_STATUS = 0
BEGIN
INSERT INTO #FRAGLIST
EXEC ('DBCC SHOWCONTIG (''' + @TABLENAME + ''')
WITH FAST, TABLERESULTS, ALL_INDEXES, NO_INFOMSGS')
FETCH NEXT FROM TABLES INTO @TABLENAME
END

CLOSE TABLES
DEALLOCATE TABLES

DECLARE INDEXES CURSOR FOR
SELECT DISTINCT OBJECTNAME
FROM #FRAGLIST
WHERE LOGICALFRAG >= @MAXFRAG

OPEN INDEXES

FETCH NEXT FROM INDEXES INTO @TABLENAME

WHILE @@FETCH_STATUS = 0
BEGIN
SELECT @EXECSTR = 'DBCC DBREINDEX (' + @TABLENAME + ','''',' +
RTRIM(@FILLFACTOR) + ')'
EXEC (@EXECSTR)

INSERT INTO #LOGS
EXEC ('DBCC SQLPERF(LOGSPACE)')

IF (SELECT LOGSIZE FROM #LOGS WHERE DATABASENAME = 'TMW_LIVE') >=
@LOGSIZELIMIT
BEGIN
SET @CURRENTDATE = GETDATE()

SELECT @FILENAME = 'D:\TMW DATA\BACKUP\TMW_Live
_tlog_'
+ RTRIM(YEAR(@CURRENTD
ATE)) +
REPLICATE('0',2- LEN(MONTH(@CURRENTDA
TE))) + RTRIM(MONTH(@CURRENT
DATE))
+
REPLICATE('0',2- LEN(DAY(@CURRENTDATE
))) + RTRIM(DAY(@CURRENTDA
TE)) +
REPLICATE('0',2- LEN(DATEPART(HH,@CUR
RENTDATE))) +
RTRIM(DATEPART(HH,@C
URRENTDATE)) +
REPLICATE('0',2- LEN(DATEPART(MI,@CUR
RENTDATE))) +
RTRIM(DATEPART(MI,@C
URRENTDATE)) +
RTRIM(DATEPART(SS,@C
URRENTDATE)) + '.TRN'

BACKUP LOG TMW_LIVE TO DISK = @FILENAME
DBCC SHRINKFILE (2,@LOGSIZELIMIT)
END

DELETE FROM #LOGS

FETCH NEXT FROM INDEXES INTO @TABLENAME
END

CLOSE INDEXES
DEALLOCATE INDEXES

DROP TABLE #LOGS
DROP TABLE #FRAGLIST
SET NOCOUNT OFF

Here is the error I get:
Server: Msg 2560, Level 16, State 14, Line 93
Parameter 2 is incorrect for this DBCC statement.

I can't figure out which DBCC statement is throwing the error, they
look fine too me.
Does anyone else see a problem?

Thanks,
Izzy

Amol Lembhe

2006-11-10, 5:14 am

Hi,

Debug the procedure
probably the statemnt DBCC SHRINKFILE (2,@LOGSIZELIMIT) causing problem
identify the value of @LOGSIZELIMIT and execute the DBCC statement manually.

Amol Lembhe

"Izzy" wrote:

> I have this procedure set up to run once a week, it's been running fine
> but the last 2 weeks it's failing.
>
> Here is the code:
>
> ALTER PROCEDURE [dbo].& #91;SVTN_UTIL_REINDE
XTABLES]
> AS
> SET NOCOUNT ON
> DECLARE @TABLENAME VARCHAR(128)
> DECLARE @EXECSTR VARCHAR(255)
> DECLARE @MAXFRAG DECIMAL
> DECLARE @FILLFACTOR TINYINT
> DECLARE @LOGSIZELIMI
T DECIMAL(10,5)
> DECLARE @FILENAME VARCHAR(100)
> DECLARE @CURRENTDATE DATETIM
E
>
> SELECT @MAXFRAG = 30.0
> SELECT @FILLFACTOR = 80
> SELECT @LOGSIZELIMIT = 200 --MB
>
> DECLARE TABLES CURSOR FOR
> SELECT TABLE_NAME
> FROM INFORMATION_SCHEMA.TABLES
> WHERE TABLE_TYPE = 'BASE TABLE'
>
> CREATE TABLE #FRAGLIST (
> OBJECTNAME CHAR (255),
> OBJECTID INT,
> INDEXNAME CHAR (255),
> INDEXID INT,
> LVL INT,
> COUNTPAGES INT,
> COUNTROWS INT,
> MINRECSIZE INT,
> MAXRECSIZE INT,
> AVGRECSIZE INT,
> FORRECCOUNT INT,
> EXTENTS INT,
> EXTENTSWITCHES INT,
> AVGFREEBYTES INT,
> AVGPAGEDENSITY INT,
> SCANDENSITY DECIMAL,
> BESTCOUNT INT,
> ACTUALCOUNT INT,
> LOGICALFRAG DECIMAL,
> EXTENTFRAG DECIMAL)
>
> CREATE TABLE #LOGS
> (
> DATABASENAME VARCHAR
(25),
> LOGSIZE DECIMAL(10,
5),
> LOGSPACEUSED DECIMAL
(10,5),
> LOGSTATUS TINYINT
> )
>
> OPEN TABLES
>
> FETCH NEXT FROM TABLES INTO @TABLENAME
>
> WHILE @@FETCH_STATUS = 0
> BEGIN
> INSERT INTO #FRAGLIST
> EXEC ('DBCC SHOWCONTIG (''' + @TABLENAME + ''')
> WITH FAST, TABLERESULTS, ALL_INDEXES, NO_INFOMSGS')
> FETCH NEXT FROM TABLES INTO @TABLENAME
> END
>
> CLOSE TABLES
> DEALLOCATE TABLES
>
> DECLARE INDEXES CURSOR FOR
> SELECT DISTINCT OBJECTNAME
> FROM #FRAGLIST
> WHERE LOGICALFRAG >= @MAXFRAG
>
> OPEN INDEXES
>
> FETCH NEXT FROM INDEXES INTO @TABLENAME
>
> WHILE @@FETCH_STATUS = 0
> BEGIN
> SELECT @EXECSTR = 'DBCC DBREINDEX (' + @TABLENAME + ','''',' +
> RTRIM(@FILLFACTOR) + ')'
> EXEC (@EXECSTR)
>
> INSERT INTO #LOGS
> EXEC ('DBCC SQLPERF(LOGSPACE)')
>
> IF (SELECT LOGSIZE FROM #LOGS WHERE DATABASENAME = 'TMW_LIVE') >=
> @LOGSIZELIMIT
> BEGIN
> SET @CURRENTDATE = GETDATE()
>
> SELECT @FILENAME = 'D:\TMW DATA\BACKUP\TMW_Live
_tlog_'
> + RTRIM(YEAR(@CURRENTD
ATE)) +
> REPLICATE('0',2- LEN(MONTH(@CURRENTDA
TE))) + RTRIM(MONTH(@CURRENT
DATE))
> +
> REPLICATE('0',2- LEN(DAY(@CURRENTDATE
))) + RTRIM(DAY(@CURRENTDA
TE)) +
> REPLICATE('0',2- LEN(DATEPART(HH,@CUR
RENTDATE))) +
> RTRIM(DATEPART(HH,@C
URRENTDATE)) +
> REPLICATE('0',2- LEN(DATEPART(MI,@CUR
RENTDATE))) +
> RTRIM(DATEPART(MI,@C
URRENTDATE)) +
> RTRIM(DATEPART(SS,@
CURRENTDATE)) + '.TRN'
>
> BACKUP LOG TMW_LIVE TO DISK = @FILENAME
> DBCC SHRINKFILE (2,@LOGSIZELIMIT)
> END
>
> DELETE FROM #LOGS
>
> FETCH NEXT FROM INDEXES INTO @TABLENAME
> END
>
> CLOSE INDEXES
> DEALLOCATE INDEXES
>
> DROP TABLE #LOGS
> DROP TABLE #FRAGLIST
> SET NOCOUNT OFF
>
> Here is the error I get:
> Server: Msg 2560, Level 16, State 14, Line 93
> Parameter 2 is incorrect for this DBCC statement.
>
>
> I can't figure out which DBCC statement is throwing the error, they
> look fine too me.
> Does anyone else see a problem?
>
> Thanks,
> Izzy
>
>

Izzy

2006-11-10, 7:14 pm

That was it.

I declared @LOGSIZELIMIT as Decimal(10,2) for some reason. I changed it
to INT and all is working fine.

Thanks Amol!

Izzy

Amol Lembhe wrote:[color=darkred
]
> Hi,
>
> Debug the procedure
> probably the statemnt DBCC SHRINKFILE (2,@LOGSIZELIMIT) causing problem
> identify the value of @LOGSIZELIMIT and execute the DBCC statement manually.
>
> Amol Lembhe
>
> "Izzy" wrote:
>

Sponsored Links