SQLServerCentral / SQL Server 2005 / Data Corruption

Corrupt Database

SSW — Thu, 18 Oct 2007 07:19:26 GMT

I am getting an error in SQL Server 2005 when I try to open or manage a databases. The ldf and mdf files exist in the data directory but I cannot detach, delete, restore the database. The error is as follows:Attempt to retrieve data for object failed for Server (name of server) (Microsoft.SQLServer.SMo)Additional Information:The database (database name) does not exist on the server. (Microsoft.SQLServer.SMo)

Database Restore Error

Saji Krishnan — Tue, 13 May 2008 06:51:56 GMT

Hi All,Recently i have got a problem with one database that few rows in a table got accidently deleted. I tried to rollback the records using SQLLogrescue but failed because by mistake i took one full backup of the database after correction. The full backup available before the deletion is only contains data till 14th of november, after that i have only log backups available for the database.The log back up set contains log generated on each day after the full backup date and it contains nearly 175 log file set. I tried to restore the database from the fullbackup with the norecovery option then to apply the log backup till date with recovery (i dont care if it is only recover data one day before the error).But this process gave me an error becuase one transaction from the log was not recoverable. This error log is the coming in the 4th file of the log backup set. I dont know what to do to restore my database at this stage. Please find the below statement which i applied. My SQL Server is on Ver 2000 (SP4).Restore Database dbcrmapplnfrom disk='c:\backup\dbcrmappln_full_20081114.bak'withmove 'dbcrmappln_data' to 'c:\dbcrmappln\dbcrmappln_data.mdf',move 'dbcrmappln_log' to 'c:\dbcrmappln\dbcrmappln_log.ldf',replace,norecoveryRestore log dbcrmapplnfrom disk='c:\backup\dbcrmappln_log.trn'with recoveryThe error came after the execution is like, 'Can't Redo transaction ID... (LSN 145679892).. Restore terminated abnormally'I also tried with 'Continue_After_Error' option after installing a sql 2005 std edition on another machine , but failed to restore.I know it is purely a problem of maintaining proper backup strategy, but i was not aware what was going on this database ( this was maintained by anothr person).Please give me a solution to restore my database by ignoring the error log in the backupset.Regards,Saji Krishnan

Sql Server 2005

wpw — Mon, 12 May 2008 21:51:29 GMT

Dear Sirs.We have problem to install Sql Server 2005, because our CD is Broken and SQLRUN_SQL.msi file is corrupt.Can you help me, so we can download SQLRUN_SQL.msi file.Thank you for your attention and cooperation.

Database growth after optimizations

gustav233 — Mon, 12 May 2008 14:25:23 GMT

Hi All,We recently migrated a database from SQL Server 2000 to SQL Server 2005 (which has yet to have service packs applied). As the migration was a side by side migration, we had to re-create all of the maintenance plans and jobs that were running on the 2000 instance. At this point the database was about 3.5 GB in size (2.4 GB data file and 1.1 GB in indexes)In re-creating the optimizations I inadvertently missed unchecking the Sunday box on the Reorganize step of the maintenance plan I was creating, so what ended up happening was that a Reorganize job ran at the same time as a DBREINDEX. As a result of this the database grew to over 5 times its original size. (We made sure this was the culprit by restoring the last normal backup in a test environment and running those steps again, and yes, it grew to the same size). The database is now about 20 GB in size (12.8 GB of data and 7.8 GB of indexes). We have tried reindexing, updating statistics, and shrinkfile to no avail. Does anybody have any ideas of how we can get the size of this database back down to normal?Thanks!!

Update rows to resolve issues about duplicate keys on Create Unique Index

heiko3169 — Wed, 07 May 2008 06:57:11 GMT

Hi there ...here comes a tricky one.I have a database table which needs to make the Index "ParentREF, UniqueName" unique - but this fails because duplicate keys are found. Thus I now need to cleanup these duplicate rows - but I cannot just delete the duplicates, because they might have rows in detail tables.This means that all duplicate rows needs an update on the "UniqueName" value - but not the first (valid) one!I can find those rows bySELECT OID, UniqueName, ParentREF, CreatedUTC, ModifiedUTC FROM dbo.CmsContent AS table0WHERE EXISTS ( SELECT OID, UniqueName, ParentREF FROM dbo.CmsContent AS table1 WHERE table0.ParentREF = table1.ParentREF AND table0.UniqueName = table1.UniqueName AND table0.OID != table1.OID)ORDER BY ParentREF, UniqueName, ModifiedUTC desc...but I struggle to make the required SQL (SP?) to update the "invalid" rows.Note: the "valid" row is the one with the newest ModifiedUTC value - this row must kept unchanged!ATM the preferred (cause easiest) way is to rename the invalid rows with UniqueName = OIDbecause if I use any other name I risk to create another double entry.Thanks in advance to whoever can help meHeiko

SQL2000 Corrupted Record - can't delete or rename TABLE

John_Idol — Fri, 09 May 2008 05:30:28 GMT

Hi All,I have just one corrupted record on a table: I copied everything else to another table but I can't delete or rename the old one!Is restoring the whole DB my only way out there?Any help or suggestion would be much appreciated!Cheers

Errors that checkdb won''t repair.

GilaMonster — Fri, 07 Apr 2006 07:17:00 GMT

Hi all.

I've got a database with some consistency and allocation errors and it seems that checkdb doesn't want to repair them. Here's the report from checkdb (it was run with repair_allow_data_loss)

Msg 2536, Sev 16: DBCC results for 'MainDB'. [SQLSTATE 01000]
Msg 1505, Sev 16: CREATE UNIQUE INDEX terminated because a duplicate key was found for index ID 1. Most significant primary key is 'Oct 17 2005 11:59PM'. [SQLSTATE 23000]
Msg 8968, Sev 16: Table error: IAM page (0:0) (object ID 1246627484, index ID 1) is out of the range of this database. [SQLSTATE 42000]
Msg 8979, Sev 16: Table error: Object ID 315148168, index ID 1. Page (1:12963862) is missing references from parent (unknown) and previous (page (1:12963861)) nodes. Possible bad root entry in sysindexes. [SQLSTATE 42000]
Msg 8977, Sev 16: Table error: Object ID 315148168, index ID 1. Parent node for page (1:12963863) was not encountered. [SQLSTATE 42000]
Msg 8922, Sev 16: Could not repair this error. [SQLSTATE 01000]
Msg 8924, Sev 16: Repairing this error requires other errors to be corrected first. [SQLSTATE 01000]
Msg 8981, Sev 16: Table error: Object ID 315148168, index ID 1. The next pointer of (1:12963863) refers to page (1:12963864). Neither (1:12963864) nor its parent were encountered. Possible bad chain linkage. [SQLSTATE 42000]
Msg 8906, Sev 16: Page (1:5052010) in database ID 13 is allocated in the SGAM (1:4601089) and PFS (1:5046912), but was not allocated in any IAM. PFS flags 'MIXED_EXT ALLOCATED 0_PCT_FULL'. [SQLSTATE 42000]
Msg 8906, Sev 16: Page (1:5052012) in database ID 13 is allocated in the SGAM (1:4601089) and PFS (1:5046912), but was not allocated in any IAM. PFS flags 'MIXED_EXT ALLOCATED 0_PCT_FULL'. [SQLSTATE 42000]
Msg 8924, Sev 16: Repairing this error requires other errors to be corrected first. [SQLSTATE 01000]
Msg 8990, Sev 16: CHECKDB found 0 allocation errors and 3 consistency errors in table 'BALANCEHistory' (object ID 315148168). [SQLSTATE 01000]
Msg 8922, Sev 16: Could not repair this error. [SQLSTATE 01000]
Msg 8924, Sev 16: Repairing this error requires other errors to be corrected first. [SQLSTATE 01000]
CHECKDB found 2 allocation errors and 0 consistency errors in table 'BALANCE' (object ID 1246627484). [SQLSTATE 01000]
CHECKDB found 2 allocation errors and 3 consistency errors in database 'MainDB'. [SQLSTATE 01000]
repair_allow_data_loss is the minimum repair level for the errors found by DBCC CHECKDB (MainDB repair_allow_data_loss). [SQLSTATE 01000]

Thanks

CHECKDB ERROR

jorge33165 — Mon, 05 May 2008 14:56:29 GMT

Attribute (object_id=) of row (object_id=,column_id=1) in sys.columns does not have a matching row (object_id=) in sys.objects

Database shows wrong tables in Management Studio

rgtft — Wed, 02 Jan 2008 08:33:56 GMT

Using SQLServer 2005 SP2, I have two databases (say DB1 and DB2). Occasionally (once or twice a week) using management studio, I'll browse (in the object browser) to the tables in DB2, and the list will show the tables in DB1. I stop and restart the SQL Server and this seems to temporarily fix the problem.What is getting corrupted? I don't know what to look for or what to check? Any ideas?Thank you,Rob

SQL Server 2005, ConnectionCheckForData error

tanya_bardakh — Tue, 12 Feb 2008 08:23:03 GMT

I have a problem running my app on a machine where SQL Server 2005 with CU4 installed (on Windows 2003).If I connect my app to a remote SQL server, my queries are executed correctly.If I connect an app from other desktops to SQL 2005, the same queries are executed correctly.I tried different protocols (Shared memory, Named pipes). It seems that Shared Memory is the most fragile: my app fails within 5-10 mins. A connection using Named Pipes can stay up longer.An error is [ODBC SQL Server Driver] [protocol_name] ConnectionCheckForData (CheckData()).Any help would be appriciated.

consistency error in xml_index_nodes

Buxton69 — Thu, 01 May 2008 07:47:41 GMT

Hi, I originally posted this in Administration but never got any replies but I've still got this issue, if anyone can help point me in the right direction or can confirm I will only loose index data it would be of great help.Thanks.......I'm getting a consistency error in xml_index_nodes in a database table after I run CHECKDB, it only started to happen yesterday, if I repair it and it gets rid of data from an internal table like this what would it affect?The full error is:DBCC results for 'sys.xml_index_nodes_421576540_32000'.Msg 8964, Level 16, State 1, Line 1Table error: Object ID 1813581499, index ID 1, partition ID 72057594098286592, alloc unit ID 72057594104119296 (type LOB data). The off-row data node at page (1:2635768), slot 0, text ID 1229193216 is not referenced.Msg 8964, Level 16, State 1, Line 1Table error: Object ID 1813581499, index ID 1, partition ID 72057594098286592, alloc unit ID 72057594104119296 (type LOB data). The off-row data node at page (1:2635770), slot 1, text ID 775946240 is not referenced.Msg 8964, Level 16, State 1, Line 1.....There are 382492705 rows in 2979246 pages for object "sys.xml_index_nodes_421576540_32000".CHECKDB found 0 allocation errors and 55 consistency errors in table 'sys.xml_index_nodes_421576540_32000' (object ID 1813581499).CHECKDB found 0 allocation errors and 55 consistency errors in database 'dbCPULink'.repair_allow_data_loss is the minimum repair level for the errors found by DBCC CHECKDB (dbCPULink).Is there anything I can do appart from a restore which is not possible to avoid data loss and if this data was removed from the internal table what would I be losing?Thanks ........I've found out what causes this error, it's reindexing on a primary XML index.I ran repair_allow_data_loss on a backup copy of the database and it fixes the consistency error but then I reindex again and the error comes back, I know what you're thinking, don't reindex it then, I can miss out the index in my reindex job but it still doesn't tell me why it has happened or by fixing the error what data I have lost if any or what I can do to be able to reindex without causing the error.Thank you for your help in advance.

DELETE DUPLICATE RECORDS

Ariel Dimapilis — Wed, 23 Apr 2008 23:56:15 GMT

Hi AllAnyone can help me how to delete a duplication records retain one value in one tableCan anyone give a script for deleting duplicates records.Please help me, I'm a new in SQLThank you very

Error 5172: Header for file is not valid.

maggie429 — Wed, 23 Apr 2008 13:02:01 GMT

I have tried to reattach a database on SQL2000. Error code:5172: The header for file 'databaselog.ldf' is not a valid database file header. The PageAudit property is incorrect.Device activation error. The physicalfile name 'dataaselog.ldf' may be incorrect. New Log file was created.I still cannot attach this database. I am presuming it cannot be done.Maggie

Select statement returned one colums insted of two!!!

Imran Ud Din — Wed, 16 Apr 2008 23:38:21 GMT

We are using SQL Server 2005 Enterprise edition on our prodution server and we are calling stored procedures from Ado.net code written in C# (.net framework 1.0). Two days back we were facing serious performance issues in on of our application area, mean while we got some exceptions in application stating Index out of bound in the dataset. The Stored Procedure contains only one select statement that returns two colums like SELECT A, B FROM myTablethe application connect to data base through Ado.net and prepar command to call the stored proceudre, fill the data in a database (result) and then the code is like thisIf ( result != null and result.Tables.count >1){ foreach(DataRow r in result.Tables[0].rows) { value1 = r[0]; value2 = r[1]; // on this like i am getting exception Index out of bound }}Which gives me an exception Index out of bound, which means it is unable to find the second colum. the code was working we didn't change any thing so far but we start getting this exception. we were able to reproduce the exception at that time but it was just for few minuts. After that the same code is working fine!!!! On safe side I have restarted the sql server and it is working fine with out any change now but still I want to know if some one knows the exact reason of this isse. I don't know if it was sql problem or it was Ado.Net!! (I think it was sql crash some how as I was getting one error in past like this, my customer complained me there data has been changed by someone else, which was not possible. When I looked at the data in the colum , it was some thing like "Deadlock vicktom process id ...." but I don't remember it exactlly as 1 year passed now. thats why I was thinking this issue could be also from sql server)Please help me in finding out the exact reason if any one knows about such problems.

Database Status SUSPECT

JMSM — Fri, 08 Feb 2008 08:21:40 GMT

Hello, Can anyone help me?I've got one database in my development environment that after machine crashes when SQL Server starts this database stays in SUSPECT status.I send you the errorlog with errors, can you tell me how can i recover my database with or without data loss.Thanks and Regards,JMSM2008-02-08 13:09:58.23 spid19s Error: 824, Severity: 24, State: 2.2008-02-08 13:09:58.23 spid19s SQL Server detected a logical consistency-based I/O error: incorrect checksum (expected: 0x50d7a8c4; actual: 0xd5a37a53). It occurred during a read of page (1:1678481) in database ID 5 at offset 0x00000333922000 in file 'D:\SQL_DATA\PTC_QS.mdf'. Additional messages in the SQL Server error log or system event log may provide more detail. This is a severe 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.2008-02-08 13:09:58.31 spid19s Error: 3313, Severity: 21, State: 2.2008-02-08 13:09:58.31 spid19s During redoing of a logged operation in database 'PTC_QS', an error occurred at log record ID (50862:16816:84). Typically, the specific failure is previously logged as an error in the Windows Event Log service. Restore the database from a full backup, or repair the database.2008-02-08 13:09:58.32 spid19s Error: 3414, Severity: 21, State: 1.2008-02-08 13:09:58.32 spid19s An error occurred during recovery, preventing the database 'PTC_QS' (database ID 5) from restarting. Diagnose the recovery errors and fix them, or restore from a known good backup. If errors are not corrected or expected, contact Technical Support.

writing result to a file

anujahnavi — Tue, 13 Nov 2007 05:53:00 GMT

execution of a query in sql server express and saving the result/output in a doc /pdf file.

I/O Error (bad page id)

Palwi Chugh — Mon, 17 Mar 2008 08:14:48 GMT

I am using Sql Server 2000. After taking data backup, it saysI/O Error (Bad Page Id) detected during reat at offset 0x00000000862000 in file 'E:\DATABASE\myDB.mdf'.

attatch the sqlserver2005 database

lamia — Sat, 15 Mar 2008 10:50:11 GMT

hiI want to attatch the sql server2005 database in the sqlsever2000how can i do it?

Need help! Cannot re attach suspect database

pete — Thu, 13 Mar 2008 07:57:50 GMT

I had a database marked as suspectHeres the logs on why it became suspect:2008-03-13 02:53:09.42 spid16 Starting up database 'mydatabase'.2008-03-13 02:53:16.87 spid16 2258 transactions rolled forward in database 'mydatabase' (7).2008-03-13 02:53:17.21 spid16 1 transactions rolled back in database 'mydatabase' (7).2008-03-13 02:53:17.25 spid16 Recovery is checkpointing database 'mydatabase' (7)2008-03-13 02:53:25.23 spid53 Using 'dbghelp.dll' version '4.0.5'...2008-03-13 02:53:25.23 spid53 ex_raise2: Exception raised, major=79, minor=87, severity=22, attempting to cre2008-03-13 02:53:26.28 spid53 Using 'dbghelp.dll' version '4.0.5'...2008-03-13 02:53:26.28 spid53 ex_raise2: Exception raised, major=79, minor=87, severity=22, attempting to cre2008-03-13 02:53:26.28 spid53 A possible database consistency problem has been detected on database 'mydatabase'2008-03-13 02:53:26.28 spid53 Error: 7987, Severity: 22, State: 32008-03-13 02:53:26.28 spid53 Stack Signature for the dump is 0x6DAD4D8E2008-03-13 02:53:27.85 spid53 Stack Signature for the dump is 0x23D395292008-03-13 02:53:27.87 spid53 Using 'dbghelp.dll' version '4.0.5'...2008-03-13 02:53:27.87 spid53 ex_raise2: Exception raised, major=79, minor=87, severity=25, attempting to cre2008-03-13 02:53:27.87 spid53 A possible database consistency problem has been detected on database 'mydatabase'2008-03-13 02:53:27.87 spid53 Error: 7987, Severity: 22, State: 32008-03-13 02:53:29.53 spid53 Stack Signature for the dump is 0x6CE596A32008-03-13 02:53:29.75 spid53 Using 'dbghelp.dll' version '4.0.5'...2008-03-13 02:53:31.35 spid53 SQL Server Assertion: File: , line=3176 ...2008-03-13 02:53:31.35 spid53 Stack Signature for the dump is 0xD985F6DE2008-03-13 02:53:31.37 spid53 Using 'dbghelp.dll' version '4.0.5'...2008-03-13 02:53:31.37 spid53 ex_raise2: Exception raised, major=79, minor=87, severity=22, attempting to cre2008-03-13 02:53:31.37 spid53 Error while undoing logged operation in database 'mydatabase'. Error at log record2008-03-13 02:53:31.37 spid53 Error: 3314, Severity: 21, State: 42008-03-13 02:53:31.37 spid53 Error: 3624, Severity: 20, State: 1.2008-03-13 02:53:32.98 spid53 Stack Signature for the dump is 0xCA4BC80C2008-03-13 02:53:33.01 spid53 Using 'dbghelp.dll' version '4.0.5'...2008-03-13 02:53:33.01 spid53 ex_raise2: Exception raised, major=79, minor=87, severity=25, attempting to cre2008-03-13 02:53:33.01 spid53 A possible database consistency problem has been detected on database 'mydatabase'2008-03-13 02:53:33.01 spid53 Error: 7987, Severity: 22, State: 32008-03-13 02:53:34.73 spid53 Stack Signature for the dump is 0x598648EE2008-03-13 02:53:34.93 spid53 Using 'dbghelp.dll' version '4.0.5'...2008-03-13 02:53:36.60 spid53 SQL Server Assertion: File: , line=3176 ...2008-03-13 02:53:36.60 spid53 Stack Signature for the dump is 0x34DE50F12008-03-13 02:53:36.62 spid53 Using 'dbghelp.dll' version '4.0.5'...2008-03-13 02:53:38.34 spid53 An error occurred while processing the log for database 'mydatabase'..2008-03-13 02:53:38.34 spid53 Error: 9004, Severity: 23, State: 72008-03-13 02:53:38.34 spid53 SQL Server Assertion: File: , line=3399 ...2008-03-13 02:53:38.34 spid53 Stack Signature for the dump is 0x437635942008-03-13 02:53:38.35 spid53 Error while undoing logged operation in database 'mydatabase'. Error at log record2008-03-13 02:53:38.35 spid53 Error: 3314, Severity: 21, State: 42008-03-13 02:53:38.43 spid53 The log for database 'mydatabase' is not available..2008-03-13 02:53:38.43 spid53 Error: 9001, Severity: 21, State: 12008-03-13 02:53:38.71 spid53 Using 'dbghelp.dll' version '4.0.5'...2008-03-13 02:53:40.21 spid53 Using 'dbghelp.dll' version '4.0.5'...2008-03-13 02:53:40.21 spid53 SQL Server Assertion: File: , line=3176 ...2008-03-13 02:53:40.21 spid53 Stack Signature for the dump is 0xBEE481FB2008-03-13 02:53:41.37 spid53 SQL Server Assertion: File: , line=3399 ...2008-03-13 02:53:41.37 spid53 Stack Signature for the dump is 0x0530B3492008-03-13 02:53:41.39 spid53 Error while undoing logged operation in database 'mydatabase'. Error at log record2008-03-13 02:53:41.39 spid53 Error: 3314, Severity: 21, State: 52008-03-13 02:53:41.39 spid53 An error occurred while processing the log for database 'mydatabase'..2008-03-13 02:53:41.39 spid53 Error: 9004, Severity: 23, State: 72008-03-13 02:53:42.53 spid16 Starting up database 'mydatabase'.2008-03-13 02:53:43.96 spid16 3 transactions rolled forward in database 'mydatabase' (7).2008-03-13 02:53:44.06 spid16 ex_raise2: Exception raised, major=79, minor=87, severity=22, attempting to cre2008-03-13 02:53:44.07 spid16 Using 'dbghelp.dll' version '4.0.5'...2008-03-13 02:53:45.76 spid16 Stack Signature for the dump is 0xD03D644B2008-03-13 02:53:45.82 spid16 Using 'dbghelp.dll' version '4.0.5'...2008-03-13 02:53:45.82 spid16 ex_raise2: Exception raised, major=79, minor=87, severity=25, attempting to cre2008-03-13 02:53:45.82 spid16 A possible database consistency problem has been detected on database 'mydatabase'2008-03-13 02:53:45.82 spid16 Error: 7987, Severity: 22, State: 32008-03-13 02:53:47.53 spid16 Stack Signature for the dump is 0xF901BC452008-03-13 02:53:47.54 spid16 Error while undoing logged operation in database 'mydatabase'. Error at log record2008-03-13 02:53:47.54 spid16 Error: 3314, Severity: 21, State: 32008-03-13 02:53:47.79 spid16 An error occurred while processing the log for database 'mydatabase'..2008-03-13 02:53:47.79 spid16 Error: 9004, Severity: 23, State: 72008-03-13 02:53:47.81 spid16 Database 'mydatabase' (database ID 7) could not recover. Contact Technical Support2008-03-13 02:53:47.81 spid16 Error: 3414, Severity: 21, State: 12008-03-13 02:55:29.10 spid3 SQL Server is terminating due to 'stop' request from Service Control Manager.I winded up detaching the database deleting the LDF file and I try to re-attach with MDF only but I get a "You must run DBCC CHECKDB" error.Please help out entire site is down becaue of this.ThanksPete

how to recover deleted database objects

haroonjaved786 — Wed, 12 Mar 2008 07:22:39 GMT

after power failuer one table in sql server 2000 database has been removed unexpectedly . any one have solution to recover that table from that database ,there is no any database backup available

Distribution.mdf growing very fast

Vania Villarreal — Tue, 04 Mar 2008 10:44:25 GMT

hello, well my problem is that the distribution.mdf fille is growing really fast, right now has 60gb, i been trying to reduce de size, but with out success, any one who can help me? :)

Reliably reproducible Fatal error 625

nathanxcampbell — Wed, 05 Mar 2008 12:02:20 GMT

withdrawn...

Error 17 off of SQL Trigger HELP please!

wtipton — Wed, 27 Feb 2008 16:14:32 GMT

I have been trying to pinpoint this error for 6 months... We have a trigger(9 pages long) that assigns customer invoices to three separate batches depending on a customer's billing type (EFT, credit card, or manual bill). The nice thing is that an EFT payment is made also for the EFT customers and we then attach the payments to a bank portal.Every once in a while during the billing process an error message will appear, "An error occurred with this document. 17" The invoice header disappears, but the line items remain in a table. The thing is that only EFT invoices disappear along with the payment. I have run a trace through SQL profiler and still don't know what is happening. I have attached a screenshot of where I think the error is happening...Any suggestions would be more than appreciated! Thank you!!!

count(*) returning incorrect value

auaiomrn — Fri, 08 Feb 2008 11:54:26 GMT

Hello, I had a strange problem and am wondering if anyone can tell me what could have caused it. Running these queries on a certain table gave the following results:select count(*) from Table1 -> about 19 million select count(*) from Table1 where DateColumn < 'Jan 31, 2008' -> about 10.5 millionselect count(*) from Table1 where DateColumn >= 'Jan 31, 2008' -> about 10.5 million(there are actually only 2 different dates in DateColumn, one from the beginning of January and one from the beginning February)note: DateColumn is part of the primary keyAfter reindexing the table the first query returned the correct result of about 21 million. Does anyone know what the problem could have been and how it could have happened?Thanks

DBCC CHECKDB Hangs

sean.johnson — Fri, 22 Feb 2008 05:59:23 GMT

I have SQL 2005 SP2 64 bit running on W2K3 SP2 64 bit and when I run DBCC CHECKDB it hangs. I have run it against the same database on SQL 2005 SP2 32 bit and it completes in 30 mins.Any idea's would be appreciated

Corruption Detection Issue

Tom — Thu, 21 Feb 2008 16:56:31 GMT

I'm a developer trying to write some corruption detection into my application (Delphi) when it backs up and restores a database, and I want to be able to read the results of a [i]dbcc checkdb[/i] and a [i]restore verifyonly[/i] command into a result set so I can pull it back into my application. Is there a way to do this? If not, can anyone suggest another way of storing the results and allowing me to retrieve them? Thanks.

After power outage, only certain areas of application become unresponsive

anoel — Fri, 15 Feb 2008 13:27:56 GMT

Hello,A customer of mine experienced a power outage and immediately afterwards noticed a decrease in performance.....but only in certain areas of the application. The last time this happened to them I was able to defrag all indexes and that corrected the issue. It happened again to the same area of the application but this time defragging the indexes did not help. DBCC CHECKDB doesn't return any errors. Any suggestions? Perhaps I need to recompile all stored procedures? :crazy:Any help would be greatly appreciated!!

deadlock log - please assist

shipleyv — Fri, 15 Feb 2008 15:26:36 GMT

From research in interpreting this log, it looks like one node has an excludive on key_generator table and the other node has an update lock on the same table. How can this happen? I thought these were mutually exclusive? Or, do I just not understand what is being shown here? Any help is appreciated!!2/13/2008 10:27:41,spid4,Unknown,Wait-for graph02/13/2008 10:27:41,spid4,Unknown,02/13/2008 10:27:41,spid4,Unknown,Node:102/13/2008 10:27:41,spid4,Unknown,RID: 8:1:61633:7 CleanCnt:1 Mode: X Flags: 0x202/13/2008 10:27:41,spid4,Unknown,Grant List 3::02/13/2008 10:27:41,spid4,Unknown,Owner:0x63d34200 Mode: X Flg:0x0 Ref:0 Life:02000000 SPID:72 ECID:002/13/2008 10:27:41,spid4,Unknown,SPID: 72 ECID: 0 Statement Type: UPDATE Line #: 102/13/2008 10:27:41,spid4,Unknown,Input Buf: Language Event: update key_generator SET kg_next_value =2989303 WHERE kg_table_id ='scheduled_block'02/13/2008 10:27:41,spid4,Unknown,Requested By:02/13/2008 10:27:41,spid4,Unknown,ResType:LockOwner Stype:'OR' Mode: U SPID:54 ECID:0 Ec:(0x330D5500) Value:0x41fcaa20 Cost:(0/10620)02/13/2008 10:27:41,spid4,Unknown,02/13/2008 10:27:41,spid4,Unknown,Node:202/13/2008 10:27:41,spid4,Unknown,KEY: 8:1125579048:2 (7802f631993e) CleanCnt:1 Mode: U Flags: 0x002/13/2008 10:27:41,spid4,Unknown,Grant List 1::02/13/2008 10:24:39,spid4,Unknown,Input Buf: Language Event: update key_generator SET kg_next_value =2988858 WHERE kg_table_id ='scheduled_block'02/13/2008 10:24:39,spid4,Unknown,Requested By:02/13/2008 10:24:39,spid4,Unknown,ResType:LockOwner Stype:'OR' Mode: U SPID:65 ECID:0 Ec:(0x3CA5F500) Value:0x420e2280 Cost:(0/0)02/13/2008 10:24:39,spid4,Unknown,Victim Resource Owner:02/13/2008 10:24:39,spid4,Unknown,ResType:LockOwner Stype:'OR' Mode: U SPID:65 ECID:0 Ec:(0x3CA5F500) Value:0x420e2280 Cost:(0/0)02/13/2008 10:24:39,spid4,Unknown,02/13/2008 10:24:39,spid4,Unknown,02/13/2008 10:24:39,spid4,Unknown,Wait-for graph02/13/2008 10:24:39,spid4,Unknown,02/13/2008 10:24:39,spid4,Unknown,Node:102/13/2008 10:24:39,spid4,Unknown,KEY: 8:1125579048:2 (7802f631993e) CleanCnt:1 Mode: U Flags: 0x002/13/2008 10:24:39,spid4,Unknown,Grant List 2::02/13/2008 10:24:39,spid4,Unknown,Owner:0x4790c880 Mode: U Flg:0x0 Ref:1 Life:00000000 SPID:65 ECID:002/13/2008 10:24:39,spid4,Unknown,SPID: 65 ECID: 0 Statement Type: UPDATE Line #: 102/13/2008 10:24:39,spid4,Unknown,Input Buf: Language Event: update key_generator SET kg_next_value =kg_next_value WHERE kg_table_id ='scheduled_block'02/13/2008 10:24:39,spid4,Unknown,Requested By:02/13/2008 10:24:39,spid4,Unknown,ResType:LockOwner Stype:'OR' Mode: U SPID:61 ECID:0 Ec:(0x6454B500) Value:0x701d13e0 Cost:(0/236C)02/13/2008 10:24:39,spid4,Unknown,02/13/2008 10:24:39,spid4,Unknown,Node:202/13/2008 10:24:39,spid4,Unknown,RID: 8:1:61633:7 CleanCnt:1 Mode: X Flags: 0x202/13/2008 10:24:39,spid4,Unknown,Grant List 1::02/13/2008 10:24:39,spid4,Unknown,Owner:0x70515fc0 Mode: X Flg:0x0 Ref:0 Life:02000000 SPID:61 ECID:002/13/2008 10:24:39,spid4,Unknown,SPID: 61 ECID: 0 Statement Type: UPDATE Line #: 102/13/2008 10:05:21,spid4,Unknown,0

Foreign Key issues

Disco — Mon, 14 Jan 2008 11:05:28 GMT

I moved two tables from one server to another... after moving, the foreign key relationship is missing on one of the columns in the table in the destination server..does anybody have any idea as to how to add the FK to that column so that the relationship is maintained properly..Thanks

Possible Corrupt data or Hidden characters/ Bad Index

Cory Blythe — Thu, 14 Feb 2008 07:33:25 GMT

I am running SQL Server 2000 SP 4 on a Windows Server 2003 environment.I have a table 'ASSIGN_ID' in my database that contains 2 columns.Table_Name varchar(30)UNIQUE_ID numeric 9(15,0)Something is happening so that the following select fails:SELECT UNIQUE_ID FROM ASSIGN_ID WHERE TABLE_NAME = 'POLICY'TABLE_NAME = 'POLICY' is a standard value in the table and it will work without issue for a time. Then the select fails. Viewing the data in the table the row containing POLICY is still there, executingSELECT UNIQUE_ID FROM ASSIGN_ID WHERE TABLE_NAME LIKE 'POLICY'returns the row desired.Performing a dbcc checktable(ASSIGN_ID) returns the following.[quote]Server: Msg 2511, Level 16, State 2, Line 1Table error: Object ID 786101841, Index ID 0. Keys out of order on page (1:554), slots 10 and 11.Server: Msg 2511, Level 16, State 1, Line 1Table error: Object ID 786101841, Index ID 0. Keys out of order on page (1:554), slots 40 and 41.Server: Msg 2511, Level 16, State 1, Line 1Table error: Object ID 786101841, Index ID 0. Keys out of order on page (1:554), slots 51 and 52.Server: Msg 2511, Level 16, State 1, Line 1Table error: Object ID 786101841, Index ID 0. Keys out of order on page (1:554), slots 78 and 79.Server: Msg 2511, Level 16, State 1, Line 1Table error: Object ID 786101841, Index ID 0. Keys out of order on page (1:554), slots 80 and 81.DBCC results for 'ASSIGN_ID'.There are 90 rows in 1 pages for object 'ASSIGN_ID'.CHECKTABLE found 0 allocation errors and 5 consistency errors in table 'ASSIGN_ID' (object ID 786101841).repair_rebuild is the minimum repair level for the errors found by DBCC CHECKTABLE (PS.dbo.ASSIGN_ID ).[/quote]Performing a Dbcc dbreindex (ASSIGN_ID) Fixes the issue but it has reoccurred.I've never run into this combination of issues before, can anyone point me in a good direction to start resolving this?ThanksCory Blythe

order of rows during bulk insert

basava_v — Wed, 19 Dec 2007 03:48:06 GMT

Hi,When a bulk insert is done from a flat file or a xls file, how do u ensure the rows are added in the same order as they are in the xls file?Basically, I want to load the data into a table which has an identity column which would need to store the rownumber of each record in the original flat file or xls file.Thanks.

Variation on error 824 that some other threads cover (Sql 2005)

administrator — Wed, 13 Feb 2008 11:39:22 GMT

I could start SQL Server Management Studio 2005 (RTM) one morning and in the evening it would not start.I tried starting in the services but received following prompt:The SQL Server (MSSQLSERVER) service on local computer started then stopped......I looked in the event log:SQL Server detected a logical consistency-based I/O error: incorrect pageid (expected 1:361; actual 0:0). It occurred during a read of page (1:361) in database ID 1 at offset 0x000000002d2000 in file 'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\master.mdf'. I tried running SQLCMD to run DBCC CHECKDB but received:Names Piped Provider: Could not open a connection to sql server....I have tried changing the settings to enable SQLCMD but no joy.What can I do? Does it come down to reinstalling SQL Server? Is it possible for me to run SQLCMD?If it means reinstalling (or should I uninstall and then install), I have the mdf and ldf of the database I am working on, is the best thing to create the database again (blank) take it offline and then replace the mdf and ldf files with the backed up one?Thank you for the help, have spent at least ten hours on it now and stumped!

Invalid null value

Pete Kesselring — Fri, 08 Feb 2008 07:20:02 GMT

HelloNew to sql and learning the hard way.we had our 2005 sql go down so in order to get it up we have a 2000 sql server going.1. I restore a 2005 backup to the a 2005 develop version 2. Restore an older 2000 backup onto the 2000 server. (we had moved the database from 2000 to 2005 back in august). 3. deleted all data from the tables in the 2000 sql database.4. I exported all the data from table in the 2005 sql database to excel.5. I imported all the data to the empty tables in the sql 2000 database.Now when the program that the user use to reigister people for seminars they get the error 94 invalid use of naull value.I tried retoring the recent backup from the 2000 sql database that is sowing the errors in the 2005 sql develop and the error is still occuring.Any help would be great. The main cencern is getting it back to the 2005 without the error so when we stand up the new 2005 server.Thanks pete

SLOT 1 for text, ntext, or image node does not exist

Vishal Prajapati — Fri, 09 Jun 2006 00:55:00 GMT

Hi,

The corruption in the database is that in one of the tables a column containing data value TEXT cannot be accessed, everything else in database works fine… When running a select on that column it returns “SLOT 1 for text, ntext, or image node does not exist”

What caused the corruption was that a Fulltext index was being moved to another disk, that disc crashed while moving the fulltext index….

The last working backup we got is 4 month old which means we are missing approx 20 000 rows…

We have restored the old database and are thinking it might help to copy the sysindex tables from the old working to the corrupt one…

Any thoughts on this idea?

ERROR-CODE 0x80004005

PaulPaulsson — Wed, 06 Feb 2008 05:42:37 GMT

Hi there,I have a problem. I work with SQL Server 2k5 and the functionallity was 'til now really good.But in the last days, there were a Failure ([b]Exception 0x80004005: [Microsoft][ODBC SQL SERVER DRIVER]Communicationfailure[/b]).I were very proud, if i get help soonest.Thx by the wayPaulPaulsson

ntext is not working....

ahmadrashid17 — Sun, 03 Feb 2008 04:27:21 GMT

I have a table which has a primary key, and three ntext field storing text data of lenght ranging from 1000 - 6000 characters. When i copy and paste text in two of the ntext fields, it works well, as soon as i enter or copy/paste text from notepad to the third field of type ntext, it display message like "can't edit this cell".the text data contain simple a-z, 0-9, and some special characters like @, (, ), ., /, \, #, {, }.Can someone guide me what i have to do to store a text of around 5000 characters or more in different fields. Is there any limitation of using ntext or text fields in a table (1, 2, 3 or ?).What special setting it need? can someone guide me?best regards,Rashid

Xml-Invalid character in text.Sp_xmlpreparedoc

usha.neelkant — Fri, 01 Feb 2008 10:02:21 GMT

Hi My source database is of collation :SQL_Latin1_General_CP1_c1_ASMy destination database is of collation:Latin1_General_c1_ASI have an xml field of varchar(500) to be load into destination.All these it was working fine.Now i am getting an error"Xml parsing error-Invalid character in text"I cannot make any changes to source and also collation cannot be changed.Please help me

DBCC checkdb file header corruption issue

Mark Horninger — Fri, 01 Feb 2008 06:20:06 GMT

Dunno if anyone else has seen this problem. Have a test SQL server (SQL2005 standard, SP1) running on Win2K3 Server (standard) with about 6 databases on it.All of the databases give the same error with a DBCC checkdb:Msg 5172, Level 16, State 15, Line 1The header for file 'E:\Data\dbawork.mdf:MSSQL_DBCC14' is not a valid database file header. The PageAudit property is incorrect.Msg 5120, Level 16, State 9, Line 1Unable to open the physical file "E:\Data\dbawork.mdf:MSSQL_DBCC14". Operating system error 0: "(null)".Msg 1823, Level 16, State 2, Line 1A database snapshot cannot be created because it failed to start.Msg 7928, Level 16, State 1, Line 1The database snapshot for online checks could not be created. Either the reason is given in a previous error or one of the underlying volumes does not support sparse files or alternate streams. Attempting to get exclusive access to run checks offline.DBCC results for 'dbawork'.DBCC CHECKDB will not check SQL Server catalog or Service Broker consistency because a database snapshot could not be created or because WITH TABLOCK was specified.DBCC results for 'sys.sysrowsetcolumns'.There are 538 rows in 5 pages for object "sys.sysrowsetcolumns".DBCC results for 'sys.sysrowsets'.There are 78 rows in 1 pages for object "sys.sysrowsets".DBCC results for 'sysallocunits'.There are 89 rows in 1 pages for object "sysallocunits".DBCC results for 'sys.sysfiles1'.There are 2 rows in 1 pages for object "sys.sysfiles1".DBCC results for 'sys.syshobtcolumns'.There are 538 rows in 5 pages for object "sys.syshobtcolumns".DBCC results for 'sys.syshobts'.There are 78 rows in 1 pages for object "sys.syshobts".DBCC results for 'sys.sysftinds'.There are 0 rows in 0 pages for object "sys.sysftinds".DBCC results for 'sys.sysserefs'.There are 89 rows in 1 pages for object "sys.sysserefs".DBCC results for 'sys.sysowners'.There are 14 rows in 1 pages for object "sys.sysowners".DBCC results for 'sys.sysprivs'.There are 120 rows in 1 pages for object "sys.sysprivs".DBCC results for 'sys.sysschobjs'.There are 47 rows in 1 pages for object "sys.sysschobjs".DBCC results for 'sys.syscolpars'.There are 419 rows in 7 pages for object "sys.syscolpars".DBCC results for 'sys.sysnsobjs'.There are 1 rows in 1 pages for object "sys.sysnsobjs".DBCC results for 'sys.syscerts'.There are 0 rows in 0 pages for object "sys.syscerts".DBCC results for 'sys.sysxprops'.There are 0 rows in 0 pages for object "sys.sysxprops".DBCC results for 'sys.sysscalartypes'.There are 27 rows in 1 pages for object "sys.sysscalartypes".DBCC results for 'sys.systypedsubobjs'.There are 0 rows in 0 pages for object "sys.systypedsubobjs".DBCC results for 'sys.sysidxstats'.There are 111 rows in 1 pages for object "sys.sysidxstats".DBCC results for 'sys.sysiscols'.There are 225 rows in 1 pages for object "sys.sysiscols".DBCC results for 'sys.sysbinobjs'.There are 23 rows in 1 pages for object "sys.sysbinobjs".DBCC results for 'sys.sysobjvalues'.There are 111 rows in 19 pages for object "sys.sysobjvalues".DBCC results for 'sys.sysclsobjs'.There are 14 rows in 1 pages for object "sys.sysclsobjs".DBCC results for 'sys.sysrowsetrefs'.There are 0 rows in 0 pages for object "sys.sysrowsetrefs".DBCC results for 'sys.sysremsvcbinds'.There are 0 rows in 0 pages for object "sys.sysremsvcbinds".DBCC results for 'sys.sysxmitqueue'.There are 0 rows in 0 pages for object "sys.sysxmitqueue".DBCC results for 'sys.sysrts'.There are 1 rows in 1 pages for object "sys.sysrts".DBCC results for 'sys.sysconvgroup'.There are 0 rows in 0 pages for object "sys.sysconvgroup".DBCC results for 'sys.sysdesend'.There are 0 rows in 0 pages for object "sys.sysdesend".DBCC results for 'sys.sysdercv'.There are 0 rows in 0 pages for object "sys.sysdercv".DBCC results for 'sys.syssingleobjrefs'.There are 133 rows in 1 pages for object "sys.syssingleobjrefs".DBCC results for 'sys.sysmultiobjrefs'.There are 102 rows in 1 pages for object "sys.sysmultiobjrefs".DBCC results for 'sys.sysdbfiles'.There are 2 rows in 1 pages for object "sys.sysdbfiles".DBCC results for 'sys.sysguidrefs'.There are 0 rows in 0 pages for object "sys.sysguidrefs".DBCC results for 'sys.sysqnames'.There are 91 rows in 1 pages for object "sys.sysqnames".DBCC results for 'sys.sysxmlcomponent'.There are 93 rows in 1 pages for object "sys.sysxmlcomponent".DBCC results for 'sys.sysxmlfacet'.There are 97 rows in 1 pages for object "sys.sysxmlfacet".DBCC results for 'sys.sysxmlplacement'.There are 17 rows in 1 pages for object "sys.sysxmlplacement".DBCC results for 'sys.sysobjkeycrypts'.There are 0 rows in 0 pages for object "sys.sysobjkeycrypts".DBCC results for 'sys.sysasymkeys'.There are 0 rows in 0 pages for object "sys.sysasymkeys".DBCC results for 'sys.syssqlguides'.There are 0 rows in 0 pages for object "sys.syssqlguides".DBCC results for 'sys.sysbinsubobjs'.There are 0 rows in 0 pages for object "sys.sysbinsubobjs".DBCC results for 'sys.queue_messages_1977058079'.There are 0 rows in 0 pages for object "sys.queue_messages_1977058079".DBCC results for 'sys.queue_messages_2009058193'.There are 0 rows in 0 pages for object "sys.queue_messages_2009058193".DBCC results for 'sys.queue_messages_2041058307'.There are 0 rows in 0 pages for object "sys.queue_messages_2041058307".CHECKDB found 0 allocation errors and 0 consistency errors in database 'dbawork'.DBCC execution completed. If DBCC printed error messages, contact your system administrator.Couple more things:1) if I create a new DB on the server, the problem exists on the newly created database (I know the model database is probably the problem)2) sp_attach... was not used on these databases.3) product support has been less than helpful.4) Backups from this machine yield the same issue when restored to another machine.5) DBCC CheckDB runs fine if I put the DB in single user mode.I'd like to understand the problem as well as come up with a solution to solve it. I saw this issue beforeThe last time I saw this issue I had to DTS the data off the box to another box and DTS it back on.Most of the posts around this issue relate to someone using sp_Attach and sp_detach, which were not used.There is some info on the 'net indicating a possible problem with the drive array controller.Anyone have any ideas?Mark

Intermittent database catalog failure on iSCSI NetApp

stew_b — Tue, 29 Jan 2008 19:34:12 GMT

SQL 2005 Enterprise edition sp2 on IBM 64bit Intel 16GB RAM deployed on NetApp iSCSI protocol 1 volume aggregate 2 LUN's comprised of 9 RAID-DP 300GB disks. Intermittently, SQL server will lose track of some, but not all, catalogs, reporting Error 21 in the log "O/S is reporting a media error...". Simply taking the DB catolog offline then back online fixes the issue. Run CHECKDB and other maintenance, backup, etc. These catalogs have little to no activity, but 24 hours later the catalog is "missing again". Same procedures as above and the catalog can then be queried, used by applications, etc., until the media fails again. Some catalogs on the NetApp NEVER fail, others take turns intermittently failing.

Warning: Fatal error 602 while trying to delete from linked server

Igor Marchenko — Wed, 30 Jan 2008 13:33:52 GMT

Hello! This is not directly data corruption related post, but I am hoping someone is able to assist. I was trying to troubleshoot problem with sp call that came done to 'Warning: Fatal error 602...' while trying to delete entry from table on linked server.delete from SPRStrata.SBMSData.sro.MediaPlanAdMap_Staging This is executed from SQL Servr 2005. Linked server is SQL 2000. I create test table using schema sro.MediaPlanAdMap_Staging and was able to run DELETE against it. I have also verified permissions of the remote user linked server is using. Also, I can run UPDATE against SPRStrata.SBMSData.sro.MediaPlanAdMap_Staging table. The problem appears to be with DELETE statement only. Table in question has identity and PK defined. SBMSData participates in replication. Error log on linked server has following error:Could not find row in sysindexes for database ID 5, object ID 86291367, index ID 0. Run DBCC CHECKTABLE on sysindexes.. I ran DBCC CHECKDB and verified sysindexes, but found no problems.Any ideas what might be causing this fatal error?Thanks,Igor