We are having quite a time since moving a large database to a new
server (actually built new server, renamed as old to make seamless for
users, etc.)
Import 104 million row database (5 column) into table (CD_Assets_bad2)
from existing (CD_Assets):
Account(varchar(8))
TransactionDate(datetime(8)
Flow(varchar(1))
Category(varchar(7))
TotalValue(decimal(8))
Run DBCC CheckTable - no issues.
Create 4 non clustered indexes (3 single column, 1 two-column). All
indexes create fine.
Run DBCC CheckTable again and receive the following:
Server: Msg 8951, Level 16, State 1, Line 1
Table error: Table 'CD_Assets_bad2' (ID 244195920). Missing or invalid
key in index 'idx_totalvalue' (ID 7) for the row:
Server: Msg 8955, Level 16, State 1, Line 1
Data row (1:11154499:98) identified by (RID = (1:11154499:98) ) has
index values (TotalValue = -10).
Server: Msg 8952, Level 16, State 1, Line 1
Table error: Database 'CD', index 'CD_Assets_bad2.idx_totalvalue' (ID
244195920) (index ID 7). Extra or invalid key for the keys:
Server: Msg 8956, Level 16, State 1, Line 1
Index row (1:20855652:338) with values (TotalValue = -0¤
4?) points to the data row identified by (RID = (1:11154499:98)).
DBCC results for 'CD_Assets_bad2'.
There are 104397173 rows in 677904 pages for object 'CD_Assets_bad2'.
CHECKTABLE found 0 allocation errors and 2 consistency errors in table
'CD_Assets_bad2' (object ID 244195920).
repair_fast is the minimum repair level for the errors found by DBCC
CHECKTABLE (CD.dbo.CD_Assets_bad2 ).
Any ideas? It seems like some sort of corruption, but the index
creates fine. If anyone can help please let me know. If I can provide
any addtional information that might help, please let me know.
I ran a dbcc checktable command on my table and I got 21 consistency errors. What baffles me is that no specific error is reported. How do I rectify this situation?
when i run query like select * from item where itemid=1476941 it gives me this error ... do ne body know y this happening ??? other itemids works fine.. only this item id creating problem ...
Error: Database ID 7, page (1:99330), slot 27 for LOB data type node does not exist. This is usually caused by transactions that can read uncommitted data on a data page. Run DBCC CHECKTABLE.
I have a SQL Database (150 gb), Some table are corrupted. I ran the command DBCC CHECKTABLE for 1 table the most important table in my system, but this command delays 50 hours and still running. Is there something I can do to do it faster? what else can I do?
I downloaded SharePoint Database Explorer on SBS from http://mindsharpblogs.com/james/archive/2005/01/20/190.aspx. I followed the instructions published on http://blogs.technet.com/sbs/archive/2007/01/05/using-sharepoint-database-explorer-on-sbs.aspx. After copying the STS_servername_1.mdf and STS_servername_1_log.ldf files to the folder, I experience problem executing the sp_attach_db statement.
My statement reads, EXEC sp_attach_db @dbname='STS_servername_1',@filename1='C:Program FilesMicrosoft SQL ServerMSSQL$SHAREPOINTDataSTS_SERVERNAME_1.mdf',@filename2='C:Program FilesMicrosoft SQL ServerMSSQL$SHAREPOINTDataSTS_SERVERNAME_1_log.LDF' GO
I received the following message after executing, Msg 1813, Level 16, State 2, Line 1 Could not open new database 'STS_servername_1'. CREATE DATABASE is aborted. Msg 602, Level 21, State 50, Line 1 Could not find row in sysindexes for database ID 5, object ID 1, index ID 1. Run DBCC CHECKTABLE on sysindexes.
What does "Could not find row in sysindexes for database ID 5, object ID 1, index ID 1." mean? What should I do? How do you run DBCC CHECKTABLE? From the error message, I don't know which table should I run the DBCC CHECKTABLE command with...
We are using SQL 6.5 and we have had problems before with our SEM reporting an incorrect value for the log size when we had just truncated the log. I would run DBCC CHECKTABLE (syslogs) just like MS Tech article Q183100 states and it would update sysindexes and correct the problem. That was on a database that is only 500mb and only has 21 rows in syslogs and it would take only a few second to run the checktable command. Now I have to run it on a database that is 5gigs and has 2,877,358 rows in syslogs. My concern is that this will take an extremely long time to run.
Does anyone have any ideas on how long this could run?
I have a MS SQL Server 6.5, SP5A database running in a MS Cluster Server environment. The transaction log (500MG)is dumped every 30 minutes to control the size and ease the point in time recovery. I have noticed that many times throughout the day, when you do a sp_spaceused on the database, the transaction log space doesn't get reported as going back to zero or near zero. It more or less stays where it was at, maybe a few MG relief. When I look at the output from the dump transaction statement (Dump Transaction dbname to dbname_tlog_dump with NOINIT, STATS), it reports that many, many pages have been dumped. However, I have been watching the sp_spaceused and it doesn't really seemed to be reflected in the numbers. (Or even in the Enterprise Manager Tool/Manage Databases - which I know has been known to report incorrectly). I have had to run dbcc checktable(syslogs) to ensure that the transaction log doesn't fill up.
Question: Does this hurt anything on recoverability by running dbcc checktable(syslogs)? Does anyone know of a bug around space not being freed in SP5A or in a MS Cluster Server environment? The application is relatively new, so I don't discount the fact that something could be going on there as well. Ideas?
I am getting the following after kicking off DBCC CHECKDB 'DATABASE', REPAIR_ALLOW_DATA_LOSS) :
Executed as user: USER. Database 'TEST' consistency errors in sysobjects, sysindexes, syscolumns, or systypes prevent further CHECKDB processing. [SQLSTATE 42000] (Error 7995) Clustered index successfully restored for object 'syscolumns' in database 'TEST'. [SQLSTATE 01000] (Error 2592) DBCC execution completed. If DBCC printed error messages, contact your system administrator. [SQLSTATE 01000] (Error 2528). The step failed.
When I try to delete a job from Enterprise Manager Console I get the following error: Erro 644: Could not find the index entry for RID '163bd10000010000' in index page (1:553), index ID 0 database 'msdb'
Oh and this is on MSDE.
Here is my complete output of DBCC CHECKDB DBCC results for 'msdb'. DBCC results for 'sysobjects'. There are 280 rows in 6 pages for object 'sysobjects'. DBCC results for 'sysindexes'. There are 143 rows in 6 pages for object 'sysindexes'. DBCC results for 'syscolumns'. There are 1567 rows in 26 pages for object 'syscolumns'. DBCC results for 'systypes'. There are 26 rows in 1 pages for object 'systypes'. DBCC results for 'syscomments'. There are 357 rows in 108 pages for object 'syscomments'. DBCC results for 'sysfiles1'. There are 2 rows in 1 pages for object 'sysfiles1'. DBCC results for 'syspermissions'. There are 116 rows in 1 pages for object 'syspermissions'. DBCC results for 'sysusers'. There are 13 rows in 1 pages for object 'sysusers'. DBCC results for 'sysproperties'. There are 0 rows in 0 pages for object 'sysproperties'. DBCC results for 'sysdepends'. There are 1635 rows in 8 pages for object 'sysdepends'. DBCC results for 'sysreferences'. There are 12 rows in 1 pages for object 'sysreferences'. DBCC results for 'sysfulltextcatalogs'. There are 0 rows in 0 pages for object 'sysfulltextcatalogs'. DBCC results for 'sysfulltextnotify'. There are 0 rows in 0 pages for object 'sysfulltextnotify'. DBCC results for 'sysfilegroups'. There are 1 rows in 1 pages for object 'sysfilegroups'. DBCC results for 'backupset'. There are 1045 rows in 44 pages for object 'backupset'. DBCC results for 'sysjobschedules'. There are 7 rows in 1 pages for object 'sysjobschedules'. DBCC results for 'syscategories'. There are 19 rows in 1 pages for object 'syscategories'. DBCC results for 'systargetservers'. There are 0 rows in 0 pages for object 'systargetservers'. DBCC results for 'backupfile'. There are 1451 rows in 24 pages for object 'backupfile'. DBCC results for 'systargetservergroups'. There are 0 rows in 0 pages for object 'systargetservergroups'. DBCC results for 'systargetservergroupmembers'. There are 0 rows in 0 pages for object 'systargetservergroupmembers'. DBCC results for 'restorehistory'. There are 1 rows in 1 pages for object 'restorehistory'. DBCC results for 'sysalerts'. There are 9 rows in 1 pages for object 'sysalerts'. DBCC results for 'sysoperators'. There are 0 rows in 0 pages for object 'sysoperators'. DBCC results for 'sysnotifications'. There are 0 rows in 0 pages for object 'sysnotifications'. DBCC results for 'restorefile'. There are 2 rows in 1 pages for object 'restorefile'. DBCC results for 'systaskids'. There are 0 rows in 0 pages for object 'systaskids'. DBCC results for 'syscachedcredentials'. There are 0 rows in 0 pages for object 'syscachedcredentials'. DBCC results for 'restorefilegroup'. There are 1 rows in 1 pages for object 'restorefilegroup'. DBCC results for 'logmarkhistory'. There are 0 rows in 0 pages for object 'logmarkhistory'. DBCC results for 'sysdtscategories'. There are 3 rows in 1 pages for object 'sysdtscategories'. DBCC results for 'sysdtspackages'. There are 0 rows in 0 pages for object 'sysdtspackages'. DBCC results for 'sysdtspackagelog'. There are 0 rows in 0 pages for object 'sysdtspackagelog'. DBCC results for 'sysdtssteplog'. Server: Msg 8935, Level 16, State 1, Line 1 Table error: Object ID 2073058421, index ID 1. The previous link (1:343) on page (1:371) does not match the previous page (1:382) that the parent (1:300), slot 32 expects for this page. Server: Msg 8978, Level 16, State 1, Line 1 Table error: Object ID 2073058421, index ID 1. Page (1:371) is missing a reference from previous page (1:343). Possible chain linkage problem. There are 0 rows in 0 pages for object 'sysdtssteplog'. DBCC results for 'sysdtstasklog'. There are 0 rows in 0 pages for object 'sysdtstasklog'. DBCC results for 'sysdbmaintplans'. There are 4 rows in 1 pages for object 'sysdbmaintplans'. DBCC results for 'sysdbmaintplan_jobs'. There are 4 rows in 1 pages for object 'sysdbmaintplan_jobs'. DBCC results for 'sysdbmaintplan_databases'. There are 12 rows in 1 pages for object 'sysdbmaintplan_databases'. DBCC results for 'sysdbmaintplan_history'. There are 724 rows in 23 pages for object 'sysdbmaintplan_history'. DBCC results for 'log_shipping_primaries'. There are 0 rows in 0 pages for object 'log_shipping_primaries'. DBCC results for 'log_shipping_secondaries'. There are 0 rows in 0 pages for object 'log_shipping_secondaries'. DBCC results for 'mswebtasks'. There are 0 rows in 0 pages for object 'mswebtasks'. DBCC results for 'sqlagent_info'. There are 0 rows in 0 pages for object 'sqlagent_info'. DBCC results for 'sysdownloadlist'. There are 0 rows in 0 pages for object 'sysdownloadlist'. DBCC results for 'backupmediaset'. There are 1045 rows in 11 pages for object 'backupmediaset'. DBCC results for 'sysjobhistory'. Server: Msg 8935, Level 16, State 1, Line 1 Table error: Object ID 2073058421, index ID 1. The previous link (1:382) on page (1:564) does not match the previous page (1:371) that the parent (1:300), slot 33 expects for this page. There are 626 rows in 208 pages for object 'sysjobhistory'. CHECKDB found 0 allocation errors and 3 consistency errors in table 'sysjobhistory' (object ID 2073058421). DBCC results for 'sysjobs'. There are 7 rows in 1 pages for object 'sysjobs'. DBCC results for 'backupmediafamily'. There are 1045 rows in 20 pages for object 'backupmediafamily'. DBCC results for 'sysjobservers'. There are 7 rows in 1 pages for object 'sysjobservers'. DBCC results for 'sysjobsteps'. There are 9 rows in 1 pages for object 'sysjobsteps'. CHECKDB found 0 allocation errors and 3 consistency errors in database 'msdb'. repair_rebuild is the minimum repair level for the errors found by DBCC CHECKDB (msdb ). DBCC execution completed. If DBCC printed error messages, contact your system administrator.
SQL Server 2005 version: 2153 I created a maintplan for system and user databases includes rebuild index, maint cleanup tasks.
Job is failing for user databases It includes rebuild index task( online index enabled) and maintenance cleanup task, scheduled at every sunday 1 AM.
I receive following errors:
In eventvwr log
sql server scheduled job 'DBMP_RebuildIndex_User' status: failed-Invoked on 2007-12-02 -1:00 Message: The job failed. The job was invoked by schedule 8 ('DBMP_RebuildIndex_User-Schedule).The last step to run was step1 ('DBMP_RebuildIndex_User')[/red]
In log report:
Failed:(-1073548784) Excuting the query "ALTER INDEX [XPKact_log] ON [dbo].[act log] REBUILD WITH (PAD_INDEX=OFF, STATISTICS_NORECOMPUTE=OFF,ALLOW_ROW_LOCKS=ON,ALLOW_PAGE_LOCKS=ON,SORT_IN_TEMPDB=OFF,ONLINE=ON) "failed with the following error "Online index operation cannot be performed for index 'XPKact_log' because the index contains column 'action_desc' of data type text, ntext.image.varchar(max),varbinary(max) or xml. For non clusterd index the column could be an include column of the index. for clusterd index it could be any column of the table .Incase of drop_existing the cloumn could be part of new or old index. The operation must be performed offline". Possible failure reasons : Problems with the querey .'" Resultset" property not set correctly, parameters not set correctly, or connection not established correctly.
Please anyone help me on this? I really appriciate
Getting this error when running a maintenance plan step. The backup steps work fine.
" Description: Failed to acquire connection "Local server connection". Connection may not be configured correctly or you may not have the right permissions on this connection. End Error Warning: 2008-04-15 09:15:03.02 Code: 0x80019002 Source: OnPreExecute Description: SSIS Warning Code DTS_W_MAXIMUMERRORCOUNTREACHED. The Execution method succeeded, but the number of errors raised (1) 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. End Warning Error: 2008-04-15 09:15:45.02 Code: 0xC0024104 Source: Reorganize Index Task ... The package execution fa... The step failed."
The step is run under the sql service agent account.
that violates the targets referential integrity?I am getting error Msg 2601, Level 14, State 1, Line 1Cannot insert duplicate key row in object XXX with unique index YYY.The statement has been terminated.I would like to know if there is a way to examine or determine what source rows are not conforming to the unique index.I'm fine with dropping and reestablishing the index, and i know its cataloged somewhere because during index creation, the error message does tell you the row details clobbering index creation. Ideally i would like to be able to trap all the failing rows and see what i can do about rehabilitating them or ignoring them or managing them some other way, but id like to know what the server knows when it will not create the index.
Does anyone have a script that can force a table/database corruption. We are in process of evaluating MS Clustering on Compaq platform using SQL Enterprise. We are told by Compaq and MS that the MS clustering with Compaq hardware detects SQL table corruptions and does a fail over to the backup box. I need to be able to simulate the corruption to test this claim. Can someone out there help me.
I've seen one other post on this topic from October 2005 and I thought I'd bring it up again. I've a Fuzzy Grouping component in my data flow. The output data from it appears to be the result of records spliced into other records. This includes pass-through columns, not merely "clean" or similarity columns. For example (I've added the suffixes for illustrative purposes):
Am running ipwsitch's ws_ftp 2007 pro software on windows 2000 platform on a network. . Trying to download a 17.4mb zipped file via FTP. File comes across to my environment but is corrupt - when attempting to unzip it.
I've an SSIS packeage that genrates offline cube (.cub) files via an MDX query run against an Analysis Services cube. When I run the package through the Management Studio all runs fine and the cube files are generated and can be used by excel.
However when I run this package through a SQL Agent job the files are generated and the job completes successfully , but when I try to open them in Excel they appear to be corrupted and I the Data Link properties screen comes up. In looking at the files the ones generated by the SQL Agent are also a different size than those generated from running the package directly
Has anyone else seen this kind of behavior? As an FYI I'm adding the files to the SQL Agent step from the SSIS Package Store.
Msg 2601, Level 14, State 1, Procedure DFP_report_load, Line 161 Cannot insert duplicate key row in object 'dbo.DFP_Reports_History' with unique index 'ix_report_history_creative_id'.
The duplicate key value is (40736326382, 1, 2015-07-03, 67618862, 355324). Msg 3621, Level 0, State 0, Procedure DFP_report_load, Line 161
The statement has been terminated.
Exception in Task: Cannot insert duplicate key row in object 'dbo.DFP_Reports_History' with unique index 'ix_report_history_creative_id'. The duplicate key value is (40736326382, 1, 2015-07-03, 67618862, 355324).
We have run the following command... DBCC CHECKDB ('db_name') ... and 12'622 consistency errors were found (all on the same index on the same table). So next we ran... DBCC CHECKTABLE ('table_name', REPAIR_ALLOW_DATA_LOSS) ... and this fixed SOME of the errors, but not all... 12586 consistency errors in table
Does anyone have any ideas? Just to confirm this is SQL Server 2000. I do have both of the results logs available if that would help.
I am playing with DBCC command to check the contsrainst on a perticular table (DBCC CHECKCONSTRAINTS ('myTable') WITH ALL_CONSTRAINTS), it always gives the following result:
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
I followed the advice of Paul Randal, but Im stumped as I am not able to determin what the corruption issues are. This is SQL 2000 and the database is a Solomon database that was recently upgraded to 6.5. the error I get when running the DBCC checkdb is as follows:
Server: Msg 8966, Level 16, State 1, Line 1 Could not read and latch page (1:18645) with latch type SH. sysindexes failed. DBCC execution completed. If DBCC printed error messages, contact your system administrator.
DBCC execution completed. If DBCC printed error messages, contact your system administrator. DBCC execution completed. If DBCC printed error messages, contact your system administrator.
Now, the bad news. I am a bit of a novice and have picked this up from someone who left my company. It appears the latch error has been around for some time and only reared up when I instituted a new back up system that runs a dbcc check befor backing up. I don't think I have any clean backups.
please explain the differences btween this logical & phisicall operations that we can see therir graphical icons in execution plan tab in Management Studio
I am using Full Text Index to index emails stored in BLOB column in a table. Index process parses stored emails, and, if there is one or more files attached to the email these documents get indexed too. In result when I'm querying the full text index for a word or phrase I am getting reference to the email containing the word of phrase if interest if the word was used in the email body OR if it was used in any document attached to the email.
How to distinguish in a Full Text query that the result came from an embedded document rather than from "main" document? Or if that's not possible how to disable indexing of embedded documents?
My goal is either to give a user an option if he or she wants to search emails (email bodies only) OR emails AND documents attached to them, or at least clearly indicate in the returned result the real source where the word or phrase has been found.
Web Base application or PDA devices use to initiate the order from all over the country. The issue is this table is not Partioned but good HP with 30 GB RAM is installed. this is main table that receive 18,0000 hits or more. All brokers and users are using this table to see the status of their order.
The always search by OrderID, or ClientID or order_SubNo, or enter any two like (Client_ID+Order_Sub_ID) or any combination.
Query takes to much time when ever server receive more querys. some orther indexes are also created on the same table like (OrderDate, OrdCreate Date and Status)
My Question are:-
Q1. IF Person "A" query to DB on Client_ID, then what Index will use ? (If any one do Query on any two combination like Client_ID+Order_ID, So what index will be uesd.? How does MS-SQL SERVER deal with these kind of issues.?
Q2. If i create 3 more indexes on ClientID, ORderID and OrdersubID. will this improve the performance of query.if person "A" search record on orderNo so what index will be used. (Mind it their would be 3 seprate indexes for Each PK columns) and composite-Clustered index is also available.?
Q3. I want to check what indexes has been used? on what search?
Q4. How can i check what table was populated when, or last date of update (DML)?
My Limitation is i Dont Create a Partioned table. I dont have permission to do it.
In Teradata we had more than 4 tb record of CRM data with no issue. i am not new baby in db line but not expert in sql server 2003.
My SSIS package is running very slow taking so much time to execute, One task is taking 2hr for inserting 100k records, i have disabled unused index still it is taking time.I am rebuilding/Refreshing indexes and stats once in month if i try to execute on daily basis will it improve my SSIS Package performance?Â
hello friends i have table1 and 200 coulumn of table1 :) i have 647.600 records. i entered my records to table1 with for step to code lines in one day :) i select category1 category2 and category3 with select code but i have just one index.. it is productnumber and it is primarykey..So my select code lines is so slow.. it is 7-9 second.. how can i select in 0.1 second ? Should i create index for category1 and category2 and category3 ? But i dont know create index.. My select code lines is below.. Could you learn me and show me index for it ?? or Could you learn me and show me fast Select code lines and index or etc ??? Also my search code line have a dangerous releated to attaching table1 with hackers :) cheersi send 3 value of treview1 node and childnode and child.childnode to below page.aspx :) Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load If Not Me.IsPostBack Then If Request("TextBox1") IsNot Nothing ThenTextBox1.Text = Request("TextBox1") End If If Request("TextBox2") IsNot Nothing ThenTextBox2.Text = Request("TextBox2") End If If Request("TextBox3") IsNot Nothing ThenTextBox3.Text = Request("TextBox3") End If End If Dim searchword As String If Request("TextBox3") = "" And Request("TextBox2") = "" Then searchword = "Select * from urunlistesi where kategori= '" & Request("TextBox1") & "'" End If If Request("TextBox3") = "" Then searchword = "Select * from urunlistesi where kategori= '" & Request("TextBox1") & "' and kategori1= '" & Request("TextBox2") & "'" End If If Request("TextBox3") <> "" And Request("TextBox2") <> "" And Request("TextBox1") <> "" Then searchword = "Select * from urunlistesi where kategori= '" & Request("TextBox1") & "' and kategori1= '" & Request("TextBox2") & "' and kategori2= '" & Request("TextBox3") & "'" End If SqlDataSource1.SelectCommand = searchword End Sub
I'm running a merge replication on a sql2k machine to 6 sql2k subscribers. Since a few day's only one of the merge agents fail's with the following error:
The merge process could not retrieve generation information at the 'Subscriber'. The index entry for row ID was not found in index ID 3, of table 357576312, in database 'PBB006'.
All DBCC CHECKDB command's return 0 errors :confused: I'm not sure if the table that's referred to in the message is on the distribution side or the subscribers side? A select * from sysobjects where id=357576312 gives different results on both sides . .
Hi everyone, When we create a clustered index firstly, and then is it advantageous to create another index which is nonclustered ?? In my opinion, yes it is. Because, since we use clustered index first, our rows are sorted and so while using nonclustered index on this data file, finding adress of the record on this sorted data is really easier than finding adress of the record on unsorted data, is not it ??