October 19, 2011 at 7:45 am
Hey Guys --
We have a vendor created system running on SQL 2000 Standard Edition that I honestly try not to mess much with other than ensuring the backups are kept up to date, but this week the users have started talking about it running slow. I checked the Indexes on the larger tables, some having 4-5 million rows, and they were heavily fragmented.
So after running some test rebuilds of the indexes in Test I scripted a rebuild of the indexes on two of the larger tables to run last night with an Insert both before and after so I could tell how long it took the indexes to run.
Here's my query to rebuild the indexes on two tables, JRNL_LN and LEDGER.
INSERT INTO DBLogs.dbo.IndexStats (RunStatus, TableName, IndexName) VALUES ('Start','JRNL_LN','BJRNL_LN');DBCC DBREINDEX("JRNL_LN",BJRNL_LN,70);INSERT INTO DBLogs.dbo.IndexStats (RunStatus, TableName, IndexName) VALUES ('End','JRNL_LN','BJRNL_LN')
INSERT INTO DBLogs.dbo.IndexStats (RunStatus, TableName, IndexName) VALUES ('Start','JRNL_LN','FJRNL_LN');DBCC DBREINDEX("JRNL_LN",FJRNL_LN,70);INSERT INTO DBLogs.dbo.IndexStats (RunStatus, TableName, IndexName) VALUES ('End','JRNL_LN','FJRNL_LN')
INSERT INTO DBLogs.dbo.IndexStats (RunStatus, TableName, IndexName) VALUES ('Start','JRNL_LN','AJRNL_LN');DBCC DBREINDEX("JRNL_LN",AJRNL_LN,70);INSERT INTO DBLogs.dbo.IndexStats (RunStatus, TableName, IndexName) VALUES ('End','JRNL_LN','AJRNL_LN')
INSERT INTO DBLogs.dbo.IndexStats (RunStatus, TableName, IndexName) VALUES ('Start','JRNL_LN','DJRNL_LN');DBCC DBREINDEX("JRNL_LN",DJRNL_LN,70);INSERT INTO DBLogs.dbo.IndexStats (RunStatus, TableName, IndexName) VALUES ('End','JRNL_LN','DJRNL_LN')
INSERT INTO DBLogs.dbo.IndexStats (RunStatus, TableName, IndexName) VALUES ('Start','JRNL_LN','JRNL_LN');DBCC DBREINDEX("JRNL_LN",JRNL_LN,70);INSERT INTO DBLogs.dbo.IndexStats (RunStatus, TableName, IndexName) VALUES ('End','JRNL_LN','JRNL_LN')
INSERT INTO DBLogs.dbo.IndexStats (RunStatus, TableName, IndexName) VALUES ('Start','LEDGER','BLEDGER');DBCC DBREINDEX("LEDGER",BLEDGER,70);INSERT INTO DBLogs.dbo.IndexStats (RunStatus, TableName, IndexName) VALUES ('End','LEDGER','BLEDGER')
INSERT INTO DBLogs.dbo.IndexStats (RunStatus, TableName, IndexName) VALUES ('Start','LEDGER','CLEDGER');DBCC DBREINDEX("LEDGER",CLEDGER,70);INSERT INTO DBLogs.dbo.IndexStats (RunStatus, TableName, IndexName) VALUES ('End','LEDGER','CLEDGER')
INSERT INTO DBLogs.dbo.IndexStats (RunStatus, TableName, IndexName) VALUES ('Start','LEDGER','ALEDGER');DBCC DBREINDEX("LEDGER",ALEDGER,70);INSERT INTO DBLogs.dbo.IndexStats (RunStatus, TableName, IndexName) VALUES ('End','LEDGER','ALEDGER')
INSERT INTO DBLogs.dbo.IndexStats (RunStatus, TableName, IndexName) VALUES ('Start','LEDGER','FLEDGER');DBCC DBREINDEX("LEDGER",FLEDGER,70);INSERT INTO DBLogs.dbo.IndexStats (RunStatus, TableName, IndexName) VALUES ('End','LEDGER','FLEDGER')
INSERT INTO DBLogs.dbo.IndexStats (RunStatus, TableName, IndexName) VALUES ('Start','LEDGER','WLEDGER');DBCC DBREINDEX("LEDGER",WLEDGER,70);INSERT INTO DBLogs.dbo.IndexStats (RunStatus, TableName, IndexName) VALUES ('End','LEDGER','WLEDGER')
INSERT INTO DBLogs.dbo.IndexStats (RunStatus, TableName, IndexName) VALUES ('Start','LEDGER','LEDGER');DBCC DBREINDEX("LEDGER",LEDGER,70);INSERT INTO DBLogs.dbo.IndexStats (RunStatus, TableName, IndexName) VALUES ('End','LEDGER','LEDGER')
Edit: I changed this from CODE to QUOTE since the double-quotes are throwing off the color coding.
The first block that ran against JRNL_LN ran with no problems, the rebuild ran great and the logs were saved to DBLogs.dbo.IndexStats, but the first DBCC DBREINDEX statement on LEDGER failed with this error:
INSERT failed because the following SET options have incorrect settings: 'QUOTED_IDENTIFIER'. [SQLSTATE 42000] (Error 1934).
And I'm not sure why. QUOTED_IDENTIFIER is set to On by default, and given the lines are identical between the first block and second I'm not sure why this error was thrown on the second block of code and not the first, unless there's something with the index LEDGER/WLEDGER.
So any ideas why this error was thrown? Most comments about QUOTED_IDENTIFIER seem to be in relation to stored procedures, but this is just a query thrown into a Job scheduled to run at night when no one is on the system.
Thanks for any feedback.
Sam
October 19, 2011 at 8:20 am
Can you run the code manually in QA (assuming this was in a job)?
Did the error message say in which object there was an error?
Anything more in the sql error logs?
Anything more in the job's logs?
October 19, 2011 at 8:31 am
This did run as a scheduled Job with the SQL just pasted into the Step, and here's the log from the job history:
DBCC execution completed. If DBCC printed error messages, contact your system administrator. [SQLSTATE 01000] (Message 2528)
DBCC execution completed. If DBCC printed error messages, contact your system administrator. [SQLSTATE 01000] (Message 2528)
DBCC execution completed. If DBCC printed error messages, contact your system administrator. [SQLSTATE 01000] (Message 2528)
DBCC execution completed. If DBCC printed error messages, contact your system administrator. [SQLSTATE 01000] (Message 2528)
DBCC execution completed. If DBCC printed error messages, contact your system administrator. [SQLSTATE 01000] (Message 2528)
INSERT failed because the following SET options have incorrect settings: 'QUOTED_IDENTIFIER'. [SQLSTATE 42000] (Error 1934).
The step failed.
The first five rebuild statements ran fine for JRNL_LN, and given they are identical to the sixth statement which starts the rebuild for LEDGER I'm not sure why it failed. Also the IndexStats table has a Start and End record for all five JRNL_LN statements but only the Start record for LEDGER exists which is what makes me think the error is related to the first DBCC statement for the LEDGER table.
As for any SQL error logs there were none for this Job.
I did some digging after posting this and found some people noting a possible bug in MS SQL 2000 that sets QUOTED_IDENTIFIER to OFF in SQL Agent, but I tested rebuilding the indexes using this exact same query in our Test system and the DBCC statement worked both with QUOTED_IDENTIFIER set to ON and OFF.
So I'm at a total loss on what caused this to fail.
Thanks for any insight --
Sam
October 19, 2011 at 8:41 am
Sounds like a bug in DBCC (or anything outside your control).
Unfortunately aside from upgrading or running this live I have nothing to offer.
Plan C might be to run a query in QA with the commands with a first step of WAITFOR TIME '22:00:00'
Maybe it would work. Of course it doesn't solve for the need to run as a scheduled task.
October 19, 2011 at 8:44 am
Ninja's_RGR'us (10/19/2011)
Sounds like a bug in DBCC (or anything outside your control).Unfortunately aside from upgrading or running this live I have nothing to offer.
Plan C might be to run a query in QA with the commands with a first step of WAITFOR TIME '22:00:00'
Maybe it would work. Of course it doesn't solve for the need to run as a scheduled task.
That's what I was afraid of... The WAITFOR option is one I didn't think of, and since this is more or less just a one time thing (hopefully) that is probably our best option.
Thanks for your help --
Sam
October 19, 2011 at 8:59 am
Hey wait 1 sec.
The error says that the INSERT fails.
So maybe you can reset the quoted identifier setting and see if that fixes it.
Google doesn't give much usefull info besides this which seems like a big strech : http://support.microsoft.com/kb/301292
October 19, 2011 at 10:03 am
Ninja's_RGR'us (10/19/2011)
Hey wait 1 sec.The error says that the INSERT fails.
So maybe you can reset the quoted identifier setting and see if that fixes it.
Google doesn't give much usefull info besides this which seems like a big strech : http://support.microsoft.com/kb/301292
I actually did find that KB, but given the error we got only mentioned QUOTED_IDENTIFIER I wasn't sure if it applied though you're right it's the closest thing I saw as well. Also though the error says it's on INSERT I assumed it may be related to the DBCC DBREINDEX statement because the Start record was written successfully to the log table but the index was not rebuilt. And given an identical INSERT statement ran 10 times before this point with no error as did 5 identical DBCC DBREINDEX statements that's where I'm with you in thinking this is some type of bug in SQL 2000. I didn't check to see which SP we're on, but given we're talking about upgrading the server to hopefully SQL 2008 soon I guess it's moot granted I can get these indexes rebuilt, even if it's manually through SSMS or QA.
Thanks ..
Sam
October 19, 2011 at 10:09 am
The only guess I have is that the setting is changed during dbcc.
Can't test this tho!
Viewing 8 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply