When performing a restore from a SQL 2005 .bak file to a SQL 2000 Server, the above is likely to happen.
Another hint that things aren't going to work is the "Name" field being filled with the value *** INCOMPLETE ***.
*head*desk*
Thursday, 21 January 2010
Wednesday, 20 January 2010
SSIS Package fails from SQL Agent
I've been configuring a test version of one of our Virtual Servers, which was created by cloning the 'Live' server using VMWare. I managed to negotiate round the @@Servername issue , and made sure to rename the databases so that we were quite clear that we were on the test server.
Browsing to the package using the button with the three dots to the right of the Package Path made sure I was looking in the right place: and, to my surprise, the Package Path changed every-so-slightly once I'd clicked OK. 
It took a couple of days, and the addition of the new server into the SSIS package which checks for failed jobs, before I noticed that the Scheduled Maintenance Plans to rebuild indexes were failing on the new server: because, in part, they were trying to connect to the old server. So, I changed things around, pointed the plans at the new server, waited 24 hours, and still they failed.
Odd.
There was nothing in the job history for the Maintenance Plan, and only this in the Job History Log (servername blanked to protect the guilty).
"Unable to start execution of step 1 (reason: line (1): Syntax error). The step failed".
A similar job gave the error:
"The command line parameters are invalid. The step failed." Again, no errors...
Looking inside the job, the command line seemed reasonable, giving, as it did "/SQL "Maintenance Plans\Weekly Check Integrity and Rebuild Indexes" /SERVER Servername /CHECKPOINTING OFF /SET "\Package\Weekly.Disable";false /REPORTING E"
What I should have done next was run that in sqlcmd. What I did was start searching Google. I found a helpful discussion thread, and peered at the SQL Agent Job Step.
Now, given that the job had been scheduled via the Job Scheduler within the maintenance plan, all should have been well. However, it wasn't. There was a missing "\" in the Maintenance Plan path.
Browsing to the package using the button with the three dots to the right of the Package Path made sure I was looking in the right place: and, to my surprise, the Package Path changed every-so-slightly once I'd clicked OK.
A backslash appeared, and the job ran successfully.
Now, there are other reasons why an SSIS package would fail when called from a SQL Agent Job, and Microsoft describes them here: they apply regardless of the Service Pack you have on your SQL 2005 instance.
As far as I can tell, however, the issue I've described here only applies to SQL 2005 RTM. The server is on the list to be service packed, and, now that we have the test version, we can check that the third party software won't throw a wobbly before applying it to live.
All's well that ends well....
Monday, 18 January 2010
Index (zero based) must be greater than or equal to zero and less than the size of the argument list.
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...
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...
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.
Subscribe to:
Posts (Atom)