SQL Server Detected A Logical Consistency-based I/O Error
Mar 15, 2007
Did anyone familiar with these errors:
SQL Server detected a logical consistency-based I/O error: incorrect checksum (expected: 0x1a00d2fd; actual: 0x1a02d2fd). It occurred during a read of page (1:103329) in database ID 11 at offset 0x00000032742000 in file 'C:Program FilesMicrosoft SQL ServerMSSQL.1MSSQLDATAServer02.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 c (Source: MSSQLServer, Error number: 824)
Get help: http://help/824
This local test DB was doing just fine. I fire up SQL Server again, and all of a sudden I can't access it (expend the database in Management Studio). So I detach and try to run this script which was run initially to re-attach it:
USE [master] GO CREATE DATABASE [ABS] ON ( FILENAME = N'C:DatabaseABS.mdf' ), ( FILENAME = N'C:DatabaseABS_log.ldf' ) FOR ATTACH GO if not exists (select name from master.sys.databases sd where name = N'CMS' and SUSER_SNAME(sd.owner_sid) = SUSER_SNAME() ) EXEC [CMS].dbo.sp_changedbowner @loginame=N'domain estuser', @map=false GO
and I get this error: SQL Server detected a logical consistency-based I/O error: torn page
When I am running sp there i am getting below error. I have try dbcc checkdb but there he didn't show any error. Please help me
Error: Msg 824, Level 24, State 2, Line 1 SQL Server detected a logical consistency-based I/O error: incorrect pageid (expected 1:5276956; actual 0:496). It occurred during a read of page (1:5276956) in database ID 16 at offset 0x00000a10a38000 in file 'F:xxxxxx_data.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.
Hi, In my database, when I try to start sql service, I got following message in event view.
SQL server is sql express version Microsoft SQL Server 2005 - 9.00.3054.00 (Intel X86) Mar 23 2007 16:28:52 Copyright (c) 1988-2005 Microsoft Corporation Developer Edition on Windows NT 5.1 (Build 2600: Service Pack 2)
10/09/2007 10:48:47,spid5s,Unknown,Recovery is complete. This is an informational message only. No user action is required. 10/09/2007 10:48:47,spid15s,Unknown,SQL Server detected a logical consistency-based I/O error: incorrect pageid (expected 1:9; actual 0:0). It occurred during a read of page (1:9) in database ID 8 at offset 0x00000000012000 in file 'C:Program FilesMicrosoft SQL ServerMSSQL.1MSSQLDATAmagirisilica_s24.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<c/> see SQL Server Books Online. 10/09/2007 10:48:47,spid15s,Unknown,Error: 824<c/> Severity: 24<c/> State: 2.
When I try to make dbcc dbcheckdb ... I got this.
Msg 945, Level 14, State 2, Line 1 Database 'magirisilica_s24' cannot be opened due to inaccessible files or insufficient memory or disk space. See the SQL Server errorlog for details.
every few days i'm getting an error in the application log , eventID 824, saying 'SQL Server detected a logical consistency-based i/o error; incorrect checksum. it then recommends completing a full database consistency check (dbcc checkdb).
i have run the dbcc checkdb ('databasename') and it is returning an error msg of:
Msg 8697, Level 16, State 215, Line 1 An internal error occurred in DBCC which prevented further processing Msg 8921, Level 16, State 1, Line 1 Check terminated. A failure was detected while collecting facts. Possibly tempdb out of space or a system table is inconsistent. Check previous errors
i have oodles of disk space left. any ideas on where to go from here.
I have a SQL Server 2008 R2 Cluster. I have moved a file to another drive by adding a new file on the drive, doing a shrinkfile with emptyfile. I have removed the old file and when I try to rename the new file to the old files old name, I get an error.
ALTER DATABASE mydb REMOVE FILE myfilename Msg 5009, Level 16, State 9, Line 1 One or more files listed in the statement could not be found or could not be initialized.
ALTER DATABASE mydb modify FILE ( NAME = temp_filename , NEWNAME = myfilename ) Msg 1828, Level 16, State 3, Line 4
The logical file name "myfilename" is already in use. Choose a different name.
I have completed a failover, thinking the name was being held in cache. I've also renamed to something completely different then tried to rename to the old filename.
When we try to run aggregation or purge queries on some tables we are getting following message:
" error [I/O error (bad page ID) detected during read at offset 0x000001ad65a000 in file 'E:MSSQL2KDataGenesys_DataMartGenesys_Datamart.mdf '. Severity 24, State 2, Procedure 'PWMGENESYSDB1 n u! ll', Line 1]"
After this we executed DBCC CHECKDB. Attaching the output obtained after executing this command, to fix these errors we executed DBCC repair_allow_data_loss. I am attaching output for this also. Pls go thru the logs and pls let me know what could be the problem and how it can be addressed.
we are running a maintainance plan on sql 2000 standard edition, got the error,
[2] Database db_source: Check Data Linkage... [Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 8928: [Microsoft][ODBC SQL Server Driver][SQL Server]Object ID 1221579390, index ID 0: Page (1:197116) could not be processed. See other errors for details. [Microsoft][ODBC SQL Server Driver][SQL Server]Table error: Object ID 1221579390, index ID 0, page (1:197116). Test (IS_ON (BUF_IOERR, bp->bstat) &&bp->berrcode) failed. Values are 2057 and -1. [Microsoft][ODBC SQL Server Driver][SQL Server]CHECKDB found 0 allocation errors and 2 consistency errors in table 'xxx'(object ID 1221579390). [Microsoft][ODBC SQL Server Driver][SQL Server]CHECKDB found 0 allocation errors and 2 consistency errors in database 'db_source'. [Microsoft][ODBC SQL Server Driver][SQL Server]repair_allow_data_loss is the minimum repair level for the errors found by DBCC CHECKDB (db_source noindex).
when i run query on anlyzer select * from xxx, i got the error Server: Msg 823, Level 24, State 2, Line 1 I/O error (torn page) detected during read at offset 0x000000603f8000 in file 'F:Program FilesMicrosoft SQL ServerMSSQLdatadb_Data.MDF'.
I have a message that is validated using a schema.
If I send the message that is not compliant with the schema, the message is not enqueued on the target queue but it is send back to the Initiator queue and the message_body contains the description of the Error.
On the Initiator queue I have activated a SP that is I used to end the conversation and log the error.
One of the information that I need to log is the original XML message that causes the validation error so I can check it.
It seems that in the moment I'm on this SP, activated when there is something on the Initiator queue I have no memory of the original message or at least I'm not able to find it.
I have execute a script on my SQL Db (script to create indexes and views) and got the following error
Server: Msg 823, Level 24, State 2, Line 1 I/O error (bad page ID) detected during read at offset 0x000000232fe000 in file 'D:...filename.mdf' ODBC: Msg 0, Level 16, STate 1 Communication link failure
DBCC CHECKDB returned
Database '' consistency errors in sysobjects, syscolumns, or systtypes prevent further CHECKDB processing..........
dbcc checktable ('syscolumns') returned
Server: Msg 8909, Level 16, State 1, Line 2 Table error: Object ID 0, index ID 0, page ID (1:86382). The PageId in the page header = (0:0). Server: Msg 8909, Level 16, State 1, Line 2 Table error: Object ID 0, index ID 0, page ID (1:86383). The PageId in the page header = (0:0). Server: Msg 8928, Level 16, State 1, Line 2 Object ID 3, index ID 0: Page (1:86382) could not be processed. See other errors for details. Server: Msg 8928, Level 16, State 1, Line 2 Object ID 3, index ID 0: Page (1:86383) could not be processed. See other errors for details. CHECKTABLE found 0 allocation errors and 2 consistency errors not associated with any single object. DBCC results for 'syscolumns'. There are 2059 rows in 133 pages for object 'syscolumns'. CHECKTABLE found 0 allocation errors and 2 consistency errors in table 'syscolumns' (object ID 3). repair_allow_data_loss is the minimum repair level for the errors found by DBCC CHECKTABLE (databasename.dbo.syscolumns ).
OS & SQL Version: SELECT @@VERSION Microsoft SQL Server 2000 - 8.00.2039 (Intel X86) May 3 2005 23:18:38 Copyright (c) 1988-2003 Microsoft Corporation Enterprise Edition on Windows NT 5.0 (Build 2195: Service Pack 4)
It's a working database. It's set up to make a backup every night at 4:15 AM when the load is minimal. When making a backup it should run DBCC CHECKDB without any repair option on. One day i got a notification that backup didn't completed. I checked the errorlogs for the day between last successful backup and the next one failed. Logs are clean.
Here is the result of "DBCC CHECKDB WITH NO_INFOMSGS, ALL_ERRORMSGS": Server: Msg 8951, Level 16, State 1, Line 1 Table error: Table 'Sessions' (ID 398624463). Missing or invalid key in index 'PK_Sessions' (ID 2) for the row: Server: Msg 8955, Level 16, State 1, Line 1 Data row (1:7420:37) identified by (RID = (1:7420:37) ) has index values (SessionId = 3539435). CHECKDB found 0 allocation errors and 1 consistency errors in table 'Sessions' (object ID 398624463). CHECKDB found 0 allocation errors and 1 consistency errors in database 'Accounting'. repair_fast is the minimum repair level for the errors found by DBCC CHECKDB (Accounting ).
Not sure if sp_helpindex results are necessary, but may be it will help: IX_Sessions_LoginTimenonclustered located on PRIMARYLoginTime IX_Sessions_NasIpnonclustered located on PRIMARYNasIp IX_Sessions_NasSessionIdnonclustered located on PRIMARYNasSessionId IX_Sessions_Usernonclustered located on PRIMARYUserId PK_Sessionsnonclustered, unique, primary key located on PRIMARYSessionId Sessions0nonclustered located on PRIMARYLoginTime, BytesIn
REPAIR_FAST does not help. The error isn't cleared. I have two questions about that issue. 1) How i can dig into that problem? For example i would like to simply look at the row causing problems, but i just don't know how to select rows referencing them by RID. Is it piossible? Actually i don't quite understand what DBCC output means. :( 2) Second question is of course how to correct that problem. :)
Of course i can restore from backup, but i'd like to know if there are other ways? Maybe without loosing all the information which was entered during the day? I can afford loosing several rows from that table, but loosing all info entered is not very good solution. No, i don't need backup strategy involving several DB backup during the day. :) Just don't want to reenter all the info, but it can be done if there are no other ways. :)
I have a database that has a consistency error. When I run a DBCC CHECKDB WITH NO_INFOMSGS, ALL_ERRORMSGS I get the following.
Server: Msg 8929, Level 16, State 1, Line 1 Object ID 2: Errors found in text ID 72205074432 owned by data record identified by RID = (1:1944:23) id = 1216723387 and indid = 6. Server: Msg 8961, Level 16, State 1, Line 1 Table error: Object ID 2. The text, ntext, or image node at page (1:62963), slot 49, text ID 72188297216 does not match its reference from page (1:1944), slot 23. CHECKDB found 0 allocation errors and 2 consistency errors in table 'sysindexes' (object ID 2). CHECKDB found 0 allocation errors and 2 consistency errors in database 'JDE_PDBANDIT_TEST'.
If I run a DBCC CHECKDB ('JDE_PDBANDIT_TEST', REPAIR_ALLOW_DATA_LOSS)I get the following
Server: Msg 8929, Level 16, State 1, Line 1 Object ID 2: Errors found in text ID 72205074432 owned by data record identified by RID = (1:1944:23) id = 1216723387 and indid = 6. Server: Msg 8961, Level 16, State 1, Line 1 Table error: Object ID 2. The text, ntext, or image node at page (1:62963), slot 49, text ID 72188297216 does not match its reference from page (1:1944), slot 23. DBCC results for 'JDE_PDBANDIT_TEST'. DBCC results for 'sysobjects'. There are 4707 rows in 64 pages for object 'sysobjects'. DBCC results for 'sysindexes'. The repair level on the DBCC statement caused this repair to be bypassed. The repair level on the DBCC statement caused this repair to be bypassed. There are 6811 rows in 477 pages for object 'sysindexes'.
Can anyone provide any other avenues to try and resolve this issue.
I have been running log shipping for more than a year now and it has been very successful until just recently. For some reason the SQL Agent jobs that backup the database, ship it across the network to a standby server and restore it are failing. On the source server the backup process is running, the log shipping is running however when it tries to restore it on the destination server it fails with the following error:
SQL Server Help file: Help context: 0 Error Detail Records: Error: -2147217900 (80040E14); Provider Error: 3013 (BC5) Error string: RESTORE DATABASE is terminating abnormally. Error source: Microsoft OLE DB Provider for SQL Server Help file: Help context: 0 Error: -2147217900 (80040E14); Provider Error: 3270 (CC6) Error string: An internal consistency error occurred. Contact Technical Support for assistance. Error source: Microsoft OLE DB Provider for SQL Server Help file: Help context: 0 Error: 0 (0); Provider Error: 3211 (C8B) Error string: 10 percent restored. Error source: Microsoft OLE DB Prov... Process Exit Code 1. The step failed.
If I delete the database on the destination server, re-run the sql agent job that ships the backup job, and re-run the restore job on the destination server the process will succeed without error. This tells me the original backup was fine but something may have been lost during the shipping. Have you ever seen this message before? What does it mean and what should I be looking at to resolve the error?
I have a sql databaseI run dbcc checkdb and get no errorsI go to backup the database in enterprise manager, and it won'trestore because it has internal consistency errorsI can go back to an older version of the databaseI run a particular procedure in our accounting software packageNever do I get errors when running dbcc checkdbSometimes I get the backup and restore problem, sometimes I don'tI am doing this on a test server with about 220 megs of RAMI am trying the dbcc checkdb ('dbname',REPAIR_REBUILD) commandAny other ideas?
Hi all, i have copied out the database backup from development center to my local machine,it is having approx.8GB in size and when i m trying to restote this database backup in SQL Server 2000 it is showing me error. An internal consistency error occurred. Contact Technical Support for assistance. RESTORE DATABASE is terminating abnormally. Plz. anyone can help me..
Server: Msg 3270, Level 16, State 1, Line 1 An internal consistency error occurred. Contact Technical Support for assistance. Server: Msg 3013, Level 16, State 1, Line 1 RESTORE DATABASE is terminating abnormally.
I'm getting the following output from dbcc checktable(sysdtssteplog):
Msg 8928, Level 16, State 1, Line 2 Object ID 594101157, index ID 0: Page (1:744) could not be processed. See other errors for details. Msg 8939, Level 16, State 98, Line 2 Table error: Object ID 594101157, index ID 0, page (1:744). Test (IS_ON (BUF_IOERR, bp->bstat) &&bp->berrcode) failed. Values are 2057 and -1. Msg 8976, Level 16, State 1, Line 2 Table error: Object ID 594101157, index ID 1. Page (1:744) was not seen in the scan although its parent (1:4660) and previous (1:607) refer to it. Check any previous errors. Msg 8978, Level 16, State 1, Line 2 Table error: Object ID 594101157, index ID 1. Page (1:745) is missing a reference from previous page (1:744). Possible chain linkage problem. CHECKTABLE found 0 allocation errors and 4 consistency errors in table 'sysdtssteplog' (object ID 594101157). repair_allow_data_loss is the minimum repair level for the errors found by DBCC CHECKTABLE (msdb.dbo.sysdtssteplog ).
Is this a DTS package or log file I can just remove, or can I just somehow remove that data from the table? I ask this because all the DTS packages seem to be running (and logging) fine.
Or will this involve restore and replace? What direction should I go from here?
I have not seen any hardware errors on this machine. Yet.
I am trying to restore a production database backup to a new location on a development workstation, and it fails with "An internal consistency error occurred, Contact Support". When I tried this on a more powerful workstation it ran without a hitch. The db is about 1.5GB, and I think the dev workstation may be running out of resources during the restore. I stopped non-essential services and ran from TSQL, and it still fails. Any suggestions other than getting a better workstation?
I'm getting the following error on our merge contents table in one of our DBs:
Msg 8952, Level 16, State 1, Line 1 Table error: Database 'ks241', index 'MSmerge_contents.nc3MSmerge_contents' (ID 1950682047) (index ID 3). Extra or invalid key for the keys: Msg 8956, Level 16, State 1, Line 1 Index row (1:1893:42) with values (partchangegen = NULL and tablenick = 17665001 and rowguid = AEAE7256-F571-478E-B2B4-D142B47C38C1) points to the data row identified by (). Msg 8952, Level 16, State 1, Line 1 Table error: Database 'ks241', index 'MSmerge_contents.nc3MSmerge_contents' (ID 1950682047) (index ID 3). Extra or invalid key for the keys: Msg 8956, Level 16, State 1, Line 1 Index row (1:1893:66) with values (partchangegen = NULL and tablenick = 17665001 and rowguid = B94F1591-33B0-4625-A269-DB9B8B05BCCE) points to the data row identified by (). CHECKTABLE found 0 allocation errors and 2 consistency errors in table 'MSmerge_contents' (object ID 1950682047). repair_fast is the minimum repair level for the errors found by DBCC CHECKTABLE (ks241.dbo.MSmerge_contents ).
There are no other corruption issues on the server that I can find. I do not believe there are any current hardware issues, though I am not on site.
What would be the best way to resolve this? Is recreating or defrag/rebuild the index an option, here? Or can I just run a repair on this? Since it's a merge table, I'm a little gun shy, having limited experience with replication.
Thanks.
____________________________________________________________________________________ "Believe in those who are seeking the truth. Doubt those who say they have found it." -Andre Gide
Had a recent drive failure on or server. Subsequently Our sql database failed to backup. Ran dbcc checkdb and got the following errors:
Msg 8966, Level 16, State 2, Line 1 Unable to read and latch page (1:241724) with latch type SH. 1(Incorrect function.) failed.
Msg 2533, Level 16, State 1, Line 1 Table error: page (1:241724) allocated to object ID 370100359, index ID 3, partition ID 868679827259392, alloc unit ID 868679827259392 (type In-row data) was not seen. The page may be invalid or may have an incorrect alloc unit ID in its header.
The many that I have spoken to all are clueless on this one. Thanks in advance for the right solution!
The insert trigger I created works fine (well, nearly fine), except that AFTER the first insert operation (ie second, third etc), it always produces the correct results BUT FOR THE PREVIOUS INSERTED ROW. It is as if there is a latency of one row in the temp table INSERTED.
I would greatly appreciate a 'why', and more importantly, a 'how to fix it' for this problem.
If you need to look at the code, a NOTEPAD file is attached.
Much appreciated
--start trigger-- create trigger UpdateAffiliateEarnings on Orders for insert, update as
--check existence of affiliateid, and for product type select @AffID=AffID, @ProductType=Source from Orders where AffID IS NOT NULL
--get relevant information if @AffID IS NOT NULL begin if @ProductType = 'FLOWER' begin select @Earnings=CONVERT(money,ChargedAmount*CommissionRa te) from Orders o,FlowerOrder t,Commission c where o.OrderID = t.OrderID and t.CommissionID = c.CommissionID and PaymentConfirmedYN='YES' end if @ProductType='PHONE' begin select @Earnings=CONVERT(money,ChargedAmount*CommissionRa te) from Orders o,PhoneOrder t,Commission c where o.OrderID = t.OrderID and t.CommissionID = c.CommissionID and PaymentConfirmedYN='YES' end
--update Affiliate account --get totals to update select @CurrentEarnings=AffTotalEarnings, @AffTotalPayments=AffTotalPayments from Affiliates where AffID=@AffID
--calculate new totals for affiliate account set @AffTotalEarnings=@CurrentEarnings+@Earnings set @AffOutstandingBalance=@AffTotalEarnings-@AffTotalPayments
--update affiliate account to new totals update Affiliates set AffTotalEarnings=@AffTotalEarnings, AffOutstandingBalance=@AffOutstandingBalance where AffID=@AffID
--roll back the transaction if there is an error if @@ERROR !=0 rollback tran end -- end of trigger --
I have encountered some strange output when testing an SSIS package I've been working on.
Please see this thread for background information: http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1590716&SiteID=1. The previous thread was resolved and i have used the tips listed here to create a package with Data Flow Tasks that use expressions to create 'dynamic' queries.
The Data Flow tasks use two package-scope variables to determine the date range that they're operating on - this allows me to script the from / to dates (usually 'last week' range) or, in event of error, 'back date' the job easily without having to update each individual components.
However, something weird is happening - when I have two data flow tasks (DFT) in the same Sequence Container (tasks that are not 'connected' or configured to operate in a flow) only one DFT is picking up the package variable & returning the rows. The other DFT is not returning any rows & seems to be using the default value of the variables.
I have tested this by simply enabling DFT A and disabling DFT B - debugging shows only one DFT executes correctly. The same point is illustrated when I disable DFT A and enable DFT B. However, if I configure DFT A to run after DFT B then both pick up the correct variable values and return the correct row counts!!
I want Data Flow Tasks that pull data from the same remote source to be the same sequence containers so when an error occurs I can get container level transaction support.
Note:
I have thoroughly checked the 'evaluated' expressions in IDE and in Management Studio - this is an SSIS specific quirk (or me!).
Questions:
What's happening here, have I made a conceptual error of how this should work? On the same variable topic: is there a way to set values for package level variables at execute time .e.g via command line execution / dtsexecui??
Let me know if you need any extra explaination / diagrams etc to understand this, the other (closed) thread I have listed above should provide more context for my problem
We have received a backup of a database from an external company for a project that we are taking over. We have attempted to restore the database and it fails giving us the error: "Internal consistency error occurred."
We have run the restore verifyonly command with the results: "The backup set is valid." However, I don't have confidence that the verify is telling the complete story.
It appears that the database is restored and the overall restore is failing on the transaction log file.
Error: The Script returned a failure result. Task SCR REIL Data failed
OnError - Task SQL Insert Error Msg Error: A deadlock was detected while trying to lock variable "System::ErrorCode, System::ErrorDescription, System::ExecutionInstanceGUID, System::StartTime, User::FEED_ID, User::t_ProcessedFiles" for read access. A lock could not be acquired after 16 attempts and timed out. Error: The expression ""EXEC [dbo].[us_sp_Insert_STG_FEED_EVENT_LOG] @FEED_ID= " + (DT_WSTR,10) @[User::FEED_ID] + ", @FEED_EVENT_LOG_TYPE_ID = 3, @STARTED_ON = '"+(DT_WSTR,30)@[System::StartTime] +"', @ENDED_ON = NULL, @message = 'Package failed. ErrorCode: "+(DT_WSTR,10)@[System::ErrorCode]+" ErrorMsg: "+@[System::ErrorDescription]+"', @FILES_PROCESSED = '" + @[User::t_ProcessedFiles] + "', @PKG_EXECUTION_ID = '" + @[System::ExecutionInstanceGUID] + "'"" on property "SqlStatementSource" cannot be evaluated. Modify the expression to be valid.
Warning: The Execution method succeeded, but the number of errors raised (4) reached the maximum allowed (1); resulting in failure. This occurs when the number of errors reaches the number specified in MaximumErrorCount. Change the MaximumErrorCount or fix the errors.
And how did I get 4 errors? - I only set my script task result to failure
Attempt to fetch logical page (3:8360) in database 'XYZ' belongs to object '1934538212', not to object 'product'. [SQLSTATE HY000] (Error 605). The step failed.
We tried to restore an empty database from a backup and got: 'Load database encountered an invalid logical page number' error. This then set the database as 'loading'
Anyone got any ideas?
We aren't exactly sure how big the first database was when the backup was done but if the new database isn't big enough to contain the backed up data, wouldn't we get a more meaningful error?
I could deploy across my environment, which is a mix of 2008R2/2012 servers, to give some information on log files. Running into a silly issue right off the bat. The table that DBCC LogInfo() conjures out of magic is different between the two. In 2012 it gained the RecoveryUnitID column. So I'm trying to write some logic to create a temp table based on which version is running. I would like to avoid a global temp table if possible. Here's what I've tried:
sp_executesql creates a table outside of the scope of my session: DECLARE @PrVers NVARCHAR(128) , @PrVersNum DECIMAL(10,2) , @StageTable NVARCHAR(1024) = N''
I'm working on an OLE DB provider. This provider is supposed to retrieve data from managed application running in background. The data retrieval is handled by C# component communicating with source application via remoting, the data are then exposed via component's COM interface. OLE DB provider itself instantiates the communication component.
Now, the problem: this worked ok in SQL server 2000, but it doesn't seem to work in SQL server express edition. Concretely, the communication component fails to get created (CoCreateInstance ends with 'class not registered') and SQL server logs this:
2007-08-24 14:48:49.42 spid51 Error: 6511, Severity: 16, State: 20. 2007-08-24 14:48:49.42 spid51 Failed to initialize the Common Language Runtime (CLR) v2.0.50727 with HRESULT 0x80131022. You may fix the problem and try again later.
I've googled a bit and found out someone else getting this message, and he was told something like 'trying to load CLR in some cases is disabled in newer (>2000) versions of SQL server'.
Now, I have very little knowledge of SQL server, CLR integration etc., so I'm not sure whether this applies to my case as well, but I think so - that problematic COM object is C#-based.
I'd be glad for any hints, ideas how to solve this issue, I just need the OLE DB provider to be able to access that managed application.
A table in one of my databases is running very slowly. The IO is very high and below is a printout from the SET STATISTICS IO ON command run on a common query used on the table:
I have a clustered unique index and a nonclustered index on the table. I have ran SQL Profiler and opened the trace in Database Tuning Advisor, DTA displays 0% improvement suggestions. I have a number of statistics on the table and index which are all up to date and fragmentation is less than 1%. I've tried a number of variations on indexes to improve performance but to no avail. There is only one query which runs on the table, and the nonclustered index created on the table did significantly improve performance, however the query still runs at around 23 seconds. The query does bring back a large amount of data however i'm sure there is a way to bring down the IO and logical reads to improve performance.
-- =================== Nonclustered Index ===========================
CREATE NONCLUSTERED INDEX [dta_ix_WebProxyLog_Kaction_clientusername_logtime_uri_mimetype_webproxylogid] ON [dbo].[WebProxyLog] ( [Action] ASC ) INCLUDE ( [ClientUserName], [logTime], [uri], [mimetype], [WebProxyLogid]) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
-- =================== Query which is called regularly on the table ===========================
SELECT [User] = CASE WHEN LEFT(clientusername,3) = domain' THEN RIGHT(clientusername,LEN(clientusername) - 3) ELSE clientusername END, logtime AS [Date], desthost AS [Site], uri AS [Actual Site] FROM webproxylog WHERE CONVERT(Datetime,CONVERT(VarChar(25),logtime,106),106) BETWEEN '20 apr 2008' AND '14 may 2008' AND(RIGHT(uri,4) NOT IN('.css','.jpg','.gif','.png','.bmp','.vbs')) AND (RIGHT(uri,3) NOT IN('.js')) AND LEFT(mimetype,6) = 'text/h' AND (uri NOT LIKE '%sometext.local%') AND (uri NOT LIKE '%sometext.co.uk%') AND [action] = 9 AND (clientusername IN ('USERNAME')) ORDER BY logtime ASC;
