So, having decided to use the built-in Standard Reports on SQL 2005 to check how one of my databases had been auto-growing and shrinking, as the free space looked a little suspect (as did the fact that the database had shrunk between Friday morning and Monday morning for no apparent reason other than a reindex...), I got the error in the title:
Index (zero based) must be greater than or equal to zero and less than the size of the argument list.
Fair enough. According to SQLSkills, this is a compatibility issue. I was using SQL 2008 Management Studio, and was checking a SQL 2005 database. Usually, this doesn't cause a problem, as the reporting tools were first introduced in SQL 2005.
Unfortunately, this database turned out to have a compatibility level of 80 - SQL 2000. Running the report in SSMS 2005 gave the following error:
Unabel to display the report because the database has a compatibility level of 80. To view this report, you need to use the Database Properties dialog to change the compatibility level to SQL 2005 (90).
This has the advantage of being rather more explicit, but none the less irritating...
Monday, 18 January 2010
Friday, 8 January 2010
Sometimes, it ought to be obvious....
Over the Christmas break, we moved our Raiser's Edge database files, so they weren't sitting on the c:\ drive (where they installed as default), but on the data drive for that server. One of my colleagues used the BlackBaud Manager for the system, rather than me doing anything clever in the back end.
At which point, the backup job, created via BlackBaud, failed.
First it failed owing to permissions. It decided that it couldn't connect to the (local) server. Easy enough to fix.
Then it failed owing to that hoary old chestnut "[SQLSTATE 42000] (Error 22029)". You know. That really useful error message that has you looking in six different places to find out why it failed: diskspace, file locked, unable to access backup file location etc etc etc. No SQL maintenance plan that I could right click and 'View History' - the thing's done by a T-SQL string invoking xp_sqlmaint. Well, the string *looked* OK: same as the version that did execute successfully when run manually.
Yup. String looked fine. Beautiful syntax. Until the second cup of coffee kicked in.
It helps if you're trying to backup a database that exists! In the process of moving the database files about, the database got renamed. The user connections got updated to reflect this new database name, but not the backup job.
Backups are now running.
At which point, the backup job, created via BlackBaud, failed.
First it failed owing to permissions. It decided that it couldn't connect to the (local) server. Easy enough to fix.
Then it failed owing to that hoary old chestnut "[SQLSTATE 42000] (Error 22029)". You know. That really useful error message that has you looking in six different places to find out why it failed: diskspace, file locked, unable to access backup file location etc etc etc. No SQL maintenance plan that I could right click and 'View History' - the thing's done by a T-SQL string invoking xp_sqlmaint. Well, the string *looked* OK: same as the version that did execute successfully when run manually.
Yup. String looked fine. Beautiful syntax. Until the second cup of coffee kicked in.
It helps if you're trying to backup a database that exists! In the process of moving the database files about, the database got renamed. The user connections got updated to reflect this new database name, but not the backup job.
Backups are now running.
Tuesday, 5 January 2010
Beware Third Party Software: it can do odd things
DBCC CHECKDB on (naturally) the largest database we have, the one which supports the mainwebsite and all the forums, failed.... PANIC! PANIC!
DBCC results for 'MyDB'.Msg 8966, Level 16, State 2, Line 1
Unable to read and latch page (1:576726) with latch type SH. 38
(failed to retrieve text for this error. Reason: 15105) failed.
CHECKDB found 0 allocation errors and 1 consistency errors not associated with any single object
.CHECKDB found 0 allocation errors and 1 consistency errors in database 'MyDB'.
is the minimum repair level for the errors found by DBCC CHECKDB (MyDB).
Msg 5269, Level 16, State 1, Line 1
Check terminated. The transient database snapshot for database 'MyDB' (database ID 6) has been marked suspect due to an IO operation failure. Refer to the SQL Server error log for details.
Msg 0, Level 20, State 0, Line 0
A severe error occurred on the current command. The results, if any, should be discarded.
The SQL Error Logs showed a sad tale:
A read of the file 'O:\DATA\MyDB.mdf:MSSQL_DBCC24' at offset 0x000001199a4000 succeeded after failing 1 time(s) with error: 38(failed to retrieve text for this error. Reason: 15105). Additional messages in the SQL Server error log and system event log may provide more detail. This error condition threatens database integrity and must be corrected. Complete a full database consistency check (DBCC CHECKDB). This error can be caused by many factors; for more information, see SQL Server Books Online.
A read of the file 'O:\DATA\MyDB.mdf:MSSQL_DBCC24' at offset 0x000001199a8000 succeeded after failing 1 time(s) with error: 38(failed to retrieve text for this error. Reason: 15105). Additional messages in the SQL Server error log and system event log may provide more detail. This error condition threatens database integrity and must be corrected. Complete a full database consistency check (DBCC CHECKDB). This error can be caused by many factors; for more information, see SQL Server Books Online.A read of the file 'O:\DATA\MyDB.mdf:MSSQL_DBCC24' at offset 0x000001199a6000 succeeded after failing 1 time(s) with error: 38(failed to retrieve text for this error. Reason: 15105). Additional messages in the SQL Server error log and system event log may provide more detail. This error condition threatens database integrity and must be corrected. Complete a full database consistency check (DBCC CHECKDB). This error can be caused by many factors; for more information, see SQL Server Books Online.
A read of the file 'O:\DATA\MyDB.mdf:MSSQL_DBCC24' at offset 0x000001199aa000 succeeded after failing 1 time(s) with error: 38(failed to retrieve text for this error. Reason: 15105). Additional messages in the SQL Server error log and system event log may provide more detail. This error condition threatens database integrity and must be corrected. Complete a full database consistency check (DBCC CHECKDB). This error can be caused by many factors; for more information, see SQL Server Books Online.
A read of the file 'O:\DATA\MyDB.mdf:MSSQL_DBCC24' at offset 0x000001199a0000 succeeded after failing 1 time(s) with error: 38(failed to retrieve text for this error. Reason: 15105). Additional messages in the SQL Server error log and system event log may provide more detail. This error condition threatens database integrity and must be corrected. Complete a full database consistency check (DBCC CHECKDB). This error can be caused by many factors; for more information, see SQL Server Books Online.
A read of the file 'O:\DATA\MyDB.mdf:MSSQL_DBCC24' at offset 0x000001199a2000 succeeded after failing 1 time(s) with error: 38(failed to retrieve text for this error. Reason: 15105). Additional messages in the SQL Server error log and system event log may provide more detail. This error condition threatens database integrity and must be corrected. Complete a full database consistency check (DBCC CHECKDB). This error can be caused by many factors; for more information, see SQL Server Books Online.
A read of the file 'O:\DATA\MyDB.mdf:MSSQL_DBCC24' at offset 0x000001199ae000 succeeded after failing 1 time(s) with error: 38(failed to retrieve text for this error. Reason: 15105). Additional messages in the SQL Server error log and system event log may provide more detail. This error condition threatens database integrity and must be corrected. Complete a full database consistency check (DBCC CHECKDB). This error can be caused by many factors; for more information, see SQL Server Books Online.
The operating system returned error 1784(failed to retrieve text for this error. Reason: 15105) to SQL Server during a write at offset 0x00000074b48000 in file 'O:\DATA\MyDB.mdf:MSSQL_DBCC24'. Additional messages in the SQL Server error log and system event log may provide more detail. This is a severe system-level error condition that threatens database integrity and must be corrected immediately. Complete a full database consistency check (DBCC CHECKDB). This error can be caused by many factors; for more information, see SQL Server Books Online.
Error: 17053, Severity: 16, State: 1.O:\DATA\MyDB.mdf:MSSQL_DBCC24: Operating system error 1784(failed to retrieve text for this error. Reason: 15105) encountered.
Enough to give anyone the screaming habdabs, right? SQL 2008, on the SAN, a really important database, and it can't complete a DBCC CHECKDB owing to I/O issues.
Oddly, every other DBCC CHECKDB (bar one, which showed page errors) completed successfully. The SAN guy couldn't find any problems with the SAN I/O, and there hadn't been any changes to the switches.
What else? Well, we use Diskeeper 2010 to keep the database drives defragged (it works rather well), and it would seem that the new Intelliwriter Option causes DBCC CHECKDB to react badly, as the file expands to allow for non-fragmented writing, and DBCC CHECKDB doesn't expect this. I am not the only DBA who has had the problem. Diskeeper have produced a fix, which is available here, but in the short term, disabling Intelliwriter corrects the issue - and DBCC CHECKDB will complete happily and there is no need to panic.
Still, my next move will be to write an SSIS package to connect to all the servers and run DBCC CHECKDB on each database in turn....
DBCC results for 'MyDB'.Msg 8966, Level 16, State 2, Line 1
Unable to read and latch page (1:576726) with latch type SH. 38
(failed to retrieve text for this error. Reason: 15105) failed.
CHECKDB found 0 allocation errors and 1 consistency errors not associated with any single object
.CHECKDB found 0 allocation errors and 1 consistency errors in database 'MyDB'.
is the minimum repair level for the errors found by DBCC CHECKDB (MyDB).
Msg 5269, Level 16, State 1, Line 1
Check terminated. The transient database snapshot for database 'MyDB' (database ID 6) has been marked suspect due to an IO operation failure. Refer to the SQL Server error log for details.
Msg 0, Level 20, State 0, Line 0
A severe error occurred on the current command. The results, if any, should be discarded.
The SQL Error Logs showed a sad tale:
A read of the file 'O:\DATA\MyDB.mdf:MSSQL_DBCC24' at offset 0x000001199a4000 succeeded after failing 1 time(s) with error: 38(failed to retrieve text for this error. Reason: 15105). Additional messages in the SQL Server error log and system event log may provide more detail. This error condition threatens database integrity and must be corrected. Complete a full database consistency check (DBCC CHECKDB). This error can be caused by many factors; for more information, see SQL Server Books Online.
A read of the file 'O:\DATA\MyDB.mdf:MSSQL_DBCC24' at offset 0x000001199a8000 succeeded after failing 1 time(s) with error: 38(failed to retrieve text for this error. Reason: 15105). Additional messages in the SQL Server error log and system event log may provide more detail. This error condition threatens database integrity and must be corrected. Complete a full database consistency check (DBCC CHECKDB). This error can be caused by many factors; for more information, see SQL Server Books Online.A read of the file 'O:\DATA\MyDB.mdf:MSSQL_DBCC24' at offset 0x000001199a6000 succeeded after failing 1 time(s) with error: 38(failed to retrieve text for this error. Reason: 15105). Additional messages in the SQL Server error log and system event log may provide more detail. This error condition threatens database integrity and must be corrected. Complete a full database consistency check (DBCC CHECKDB). This error can be caused by many factors; for more information, see SQL Server Books Online.
A read of the file 'O:\DATA\MyDB.mdf:MSSQL_DBCC24' at offset 0x000001199aa000 succeeded after failing 1 time(s) with error: 38(failed to retrieve text for this error. Reason: 15105). Additional messages in the SQL Server error log and system event log may provide more detail. This error condition threatens database integrity and must be corrected. Complete a full database consistency check (DBCC CHECKDB). This error can be caused by many factors; for more information, see SQL Server Books Online.
A read of the file 'O:\DATA\MyDB.mdf:MSSQL_DBCC24' at offset 0x000001199a0000 succeeded after failing 1 time(s) with error: 38(failed to retrieve text for this error. Reason: 15105). Additional messages in the SQL Server error log and system event log may provide more detail. This error condition threatens database integrity and must be corrected. Complete a full database consistency check (DBCC CHECKDB). This error can be caused by many factors; for more information, see SQL Server Books Online.
A read of the file 'O:\DATA\MyDB.mdf:MSSQL_DBCC24' at offset 0x000001199a2000 succeeded after failing 1 time(s) with error: 38(failed to retrieve text for this error. Reason: 15105). Additional messages in the SQL Server error log and system event log may provide more detail. This error condition threatens database integrity and must be corrected. Complete a full database consistency check (DBCC CHECKDB). This error can be caused by many factors; for more information, see SQL Server Books Online.
A read of the file 'O:\DATA\MyDB.mdf:MSSQL_DBCC24' at offset 0x000001199ae000 succeeded after failing 1 time(s) with error: 38(failed to retrieve text for this error. Reason: 15105). Additional messages in the SQL Server error log and system event log may provide more detail. This error condition threatens database integrity and must be corrected. Complete a full database consistency check (DBCC CHECKDB). This error can be caused by many factors; for more information, see SQL Server Books Online.
The operating system returned error 1784(failed to retrieve text for this error. Reason: 15105) to SQL Server during a write at offset 0x00000074b48000 in file 'O:\DATA\MyDB.mdf:MSSQL_DBCC24'. Additional messages in the SQL Server error log and system event log may provide more detail. This is a severe system-level error condition that threatens database integrity and must be corrected immediately. Complete a full database consistency check (DBCC CHECKDB). This error can be caused by many factors; for more information, see SQL Server Books Online.
Error: 17053, Severity: 16, State: 1.O:\DATA\MyDB.mdf:MSSQL_DBCC24: Operating system error 1784(failed to retrieve text for this error. Reason: 15105) encountered.
Enough to give anyone the screaming habdabs, right? SQL 2008, on the SAN, a really important database, and it can't complete a DBCC CHECKDB owing to I/O issues.
Oddly, every other DBCC CHECKDB (bar one, which showed page errors) completed successfully. The SAN guy couldn't find any problems with the SAN I/O, and there hadn't been any changes to the switches.
What else? Well, we use Diskeeper 2010 to keep the database drives defragged (it works rather well), and it would seem that the new Intelliwriter Option causes DBCC CHECKDB to react badly, as the file expands to allow for non-fragmented writing, and DBCC CHECKDB doesn't expect this. I am not the only DBA who has had the problem. Diskeeper have produced a fix, which is available here, but in the short term, disabling Intelliwriter corrects the issue - and DBCC CHECKDB will complete happily and there is no need to panic.
Still, my next move will be to write an SSIS package to connect to all the servers and run DBCC CHECKDB on each database in turn....
Monday, 4 January 2010
sp_fulltext_catalog
And a happy new year to you. Mine started like this (among other issues), with an error for an hourly rebuild index job.
USE [thisdatabase]
EXEC sp_fulltext_catalog
N'this_database_index' ,
N'start_full'
Gave this result
Executed as user: Machinename\SQLAdmin. Execution of a full-text operation failed. The Gatherer is shutting down. [SQLSTATE 42000] (Error 7619). The step failed.
The Gatherer? You what?
This was on a non-clustered SQL 2000 box (8.0.2055), and the solution was to restart MSSeach. This I did via Services Administration, where it is helpfully labelled as 'Microsoft Search'. I have no idea what The Gatherer was, or did, or does, but it seems to be happy now.
USE [thisdatabase]
EXEC sp_fulltext_catalog
N'this_database_index' ,
N'start_full'
Gave this result
Executed as user: Machinename\SQLAdmin. Execution of a full-text operation failed. The Gatherer is shutting down. [SQLSTATE 42000] (Error 7619). The step failed.
The Gatherer? You what?
This was on a non-clustered SQL 2000 box (8.0.2055), and the solution was to restart MSSeach. This I did via Services Administration, where it is helpfully labelled as 'Microsoft Search'. I have no idea what The Gatherer was, or did, or does, but it seems to be happy now.
Tuesday, 15 December 2009
Nifty...
The Midnight DBA has posted something rather nifty that can be found in SQL Profiler. There is the option, using sp_trace_setevent, to check for deprecated code - code that just won't work in future versions of SQL. Her explanation is rather more coherent than mine, I suspect she wasn't quite as excited.
I wish I had known about this one before I started migrating databases from SQL 2000 to SQL 2008....
I'll be implementing it on the last databases to be moved tomorrow. Murphy's Law says that if I don't, something ghastly will happen.
I wish I had known about this one before I started migrating databases from SQL 2000 to SQL 2008....
I'll be implementing it on the last databases to be moved tomorrow. Murphy's Law says that if I don't, something ghastly will happen.
So I reported a bug...
And they got back to me! Hurrah!
Alas, I was out of office the entire time they were trying to get back to me, didn't have a watch on the bug.... and Microsoft concluded that I was no longer having issues, so closed it.
I shall try to get them to re-open this one. I know I am not the only one with the problem, but I do seem to be the only one who has reported it.... It's 507494, (if that link doesn't work, please let me know? It's a tad tricky when one is logged in to MS Connect).
Alas, I was out of office the entire time they were trying to get back to me, didn't have a watch on the bug.... and Microsoft concluded that I was no longer having issues, so closed it.
I shall try to get them to re-open this one. I know I am not the only one with the problem, but I do seem to be the only one who has reported it.... It's 507494, (if that link doesn't work, please let me know? It's a tad tricky when one is logged in to MS Connect).
Monday, 2 November 2009
How to report a SQL Bug
1. Set up a Connect Account, using your Windows Live ID Passport (note, I find it more helpful to set these up using a personal email account, rather than a work account which won't migrate jobs with you. It is possible to change email accounts within Live, but it is a complete and utter pain). Join the SQL Server Connect group.
2. Visit the general feedback page and 'search this connection for' the thing that is causing you distress. IGNORE the 'Submit Feedback' button in the top right of the page. It isn't a button.
3. Find 'submit it yourself' (a link in the left hand menu).
OR
1. Click here to get directly to the relevant form (SQL Server Bug).
It's polite to search and check that no-one else has reported your bug. However, do not, unless you have lots of spare time, offer to fill in your SQL Profile. It takes an age, and I don't think it actually makes any difference as to whether you can report a bug.
2. Visit the general feedback page and 'search this connection for' the thing that is causing you distress. IGNORE the 'Submit Feedback' button in the top right of the page. It isn't a button.
3. Find 'submit it yourself' (a link in the left hand menu).
OR
1. Click here to get directly to the relevant form (SQL Server Bug).
It's polite to search and check that no-one else has reported your bug. However, do not, unless you have lots of spare time, offer to fill in your SQL Profile. It takes an age, and I don't think it actually makes any difference as to whether you can report a bug.
Subscribe to:
Posts (Atom)