Somehow one of tables in my database with clustered index got tempered and the pageid are not in proper order. I tried the checkdb command with repair_rebuild option but it is giving the following error.
Table error: Object ID 1984530649, index ID 1. Page (1:254682) is missing a reference from previous page (1:254681). Possible chain linkage problem.
Server: Msg 8936, Level 16, State 1, Line 1
Table error: Object ID 1984530649, index ID 1. B-tree chain linkage mismatch. (1:254680)->next = (1:256198), but (1:256198)->Prev = (1:256197).
Not able to reindex the index of table. I can not even export the data to any other table with same structure.
I recently updated the datatype of a sproc parameter from bit to tinyint. When I executed the sproc with the updated parameters the sproc appeared to succeed and returned "1 row(s) affected" in the console. However, the update triggered by the sproc did not actually work.
The table column was a bit which only allows 0 or 1 and the sproc was passing a value of 2 so the table was rejecting this value. However, the sproc did not return an error and appeared to return success. So is there a way to configure the database or sproc to return an error message when this type of error occurs?
Does anyone know a process for repairing a corrupt syslogs table (mssql 6.5). I have tried checkdb, newalloc and rebuilding the extents. All the latter did was screw up the device assignment. Any suggestions?
1) When I try to create a New Job/Alert/Operator, I get an error message:
Error 207: Invalid Column Name "category_id" Invalid Column Name "netsend_address" Invalid Column Name "last_netsend_date" Invalid Column Name "last_netsend_time"
.....All these are referring to columns in System Tables of the system Database MSDB.
2)In another SQL Server...say "A", in it's SQL Server Agent, using the Multi-Server Administration, I set up the SQL Server Agent of "A" as the Master (MSX) server, and set up a Target(TSX) server pointing to the Server "B" that has problems in SQL Agent....referred in 1) above. When I try to "Detach the MSX Server" from server B, i get a message Error 207: Invalid Column Name "netsend_address", and cannot detach..........This is again referrring to a System Table.
It appears that the problem is with the MSDB database being corrupt. Is there a way I can Repair a Corrupt MSDB without Re-installing SQL Server 2000 and without a Backup.
Suddenly one day I found some corruptions have occurred in my SQL Server 2012 installation. Because my 'msdb' has been marked SUSPECT/CORRUPT; hence no new work can be done as per my schedules.
I have gone about trying to repair it. I followed this article here: [URL] [the region 'Create new MSDB Database'].
I am facing some sizeable difficulties in doing this on my own. Steps 1. & 2. I have done. I am now stuck at step no. 3.My instance name is SQLEXPRESS. So, when I give,
SQLCMD -E -S<SQLEXPRESS> -dmaster -Q"EXEC sp_detach_db msdb"Â [as per the article], what I get is:
The System cannot find the file specified.
So I am stuck at that point. What's with the 'cannot find file specified'? The stored procedure, or the mdf, ldf files for my msdb? What is it?Also, if and when I am through with step no. 3 I would like to know about steps 5. & 6. also which are soon to follow [like how to do them correctly, safely and from where, the different options]. This is a huge priority for me to get my sql server up and running again because I can't do my other coding works without it. Everything is stalled. Slowly reaching the desperation, SOS mode..
I have a requirement to only rebuild the Clustered Indexes in the table ignoring the non clustered indexes as those are taken care of by the Clustered indexes.
In order to do that, I have taken the records based on the fragmentation %.
But unable to come up with a logic to only consider rebuilding the clustered indexes in the table.
Can someone tell me if it is possible to add an index to a Table variable that is declare as part of a table valued function ? I've tried the following but I can't get it to work.
ALTER FUNCTION dbo.fnSearch_GetJobsByOccurrence ( @param1 int, @param2 int ) RETURNS @Result TABLE (resultcol1 int, resultcol2 int) AS BEGIN
I'm looking for information on how to add indexes to a table in a SQL Server 2000 Database, why add them etc? Any source of good information on the web regarding this?
I'm working to improve performance on a database I've inherited, and there are several thousand indexes. I've got a list of ones which should definitely exist within the database, and I'm looking to strip out all the others and start fresh, though this list is still quite large (1000 or so).
Is there a way I can remove all the indexes that are not in my list without too much trouble? I.e. without having to manually go through them all individually. The list is currently in a csv file.
I'm looking to either automate the removal of indexes not in the list, or possibly to generate the Create statements for the indexes on the list and simply remove all indexes and then run these statements.
As an aside, when trying to list all indexes in the database, I've found various scripts to do this, but found they all seem to produce differing results. What is the best script to list all indexes?
I have a table with several indexes, currently most of them are very narrow (one column), and the question is, when I modify the table by updating a record, does all the indexes are calculated again?? Even if the modified field isn't indexed??? Or the server is smart and knows what indexes to calculate if any.
Second question, can I give to a query a low priority(In dynamic SQL), for example when I don't want my query to exploit too many system resources so it won't interfere the main system ?
Hi,I simply want to create a duplicate of an existing table with adifferent name. I've tried the "select * into newtable fromorgtable", which works great, except that it doesn't mark the primarykey field from orgtable in the newtable. I tried creating thenewtable prior to using this select, but then sql server cries aboutthe table already existing. I tried creating the index after theselect "CREATE UNIQUE INDEX FieldAIndex ON newtable (FieldA)" - thatdidn't give a error, but also failed to mark the field as primary.Suggestions?
Regarding to my previous post at http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=553652&SiteID=1, just wondering if there's a way to detect any changes which is made on the existing indexes in a published table so that the device can determine whether reinitialization of subscrption is needed before every synchronization.
I used the option "Script Table" --> "Create to" in SQL Server Management Studio Express for a table with secondary index (like IX_IndexName), but in the .sql script file there is only the instruction for the primary key and not for the secondary (I expected something like CREATE INDEX). What can I do?
In our environment we have a very high insert intensive OLTP table, this has 60 million rows. From some of our applications we are very rarely getting timeouts when inserting data into this table. We are expecting that this table would soon run into billion rows and continue to grow. below is the table description and indexes. What indexes should we keep/remove on this table?
Column Name Datatype computed Length Precision Scale Nullable Col1 int no 4 10 0 no Col2 smallint no 2 5 0 no Col3 smallint no 2 5 0 yes Col4 int no 4 10 0 yes Col5 int no 4 10 0 yes Col6 int no 4 10 0 yes Col7 char no 10 yes Col8 int no 4 10 0 yes Col9 uniqueidentifier no 16 yes Col10 datetime no 8 yes Col11 smallint no 2 5 0 yes Col12 varchar no 15 yes Col13 varchar no 30 yes Col14 varchar no 50 yes Col15 int no 4 10 0 yes Col16 int no 4 10 0 yes Col17 varchar no 12 yes Col18 smallint no 2 5 0 yes Col19 smallint no 2 5 0 yes Col20 int no 4 10 0 yes Col21 bit no 1 no Col22 int no 4 10 0 yes Col23 bit no 1 no Col24 bit no 1 no Col25 datetime no 8 no Col26 char no 6 yes Col27 char no 6 yes Col28 smallint no 2 5 0 yes Col29 char no 2 yes Col30 int no 4 10 0 yes Col31 smallint no 2 5 0 yes Col32 smallint no 2 5 0 yes Col33 bit no 1 yes
Index1 nonclustered, stats no recompute located on FileGroup2 Col9, Col10, Col12 Index2 nonclustered, stats no recompute located on FileGroup2 Col9, Col12, Col11, Col10, Col4 Index3 nonclustered, stats no recompute located on FileGroup2 Col6 Index4 nonclustered, stats no recompute located on FileGroup2 Col6, Col4 Index5 nonclustered, stats no recompute located on FileGroup2 Col5, Col9 Index6 nonclustered, stats no recompute located on FileGroup2 Col4, Col9 Index7 nonclustered, stats no recompute located on FileGroup2 Col29 Index8 nonclustered, stats no recompute located on FileGroup2 Col9 Index9 nonclustered, stats no recompute located on FileGroup2 Col6, Col9 Index10 nonclustered, stats no recompute located on FileGroup2 Col7 Index11 nonclustered, stats no recompute located on FileGroup2 Col12 Index12 clustered, unique, primary key, stats no recompute located on FileGroup3 Col1
Any links to MS best practises on indexes for sqlserver 2000 appreciated.
I use Indexes Fundamentals of Microsoft SQL Server - Lesson 30: Indexes in the website of URL... to learn the basic things of Indexes. In my SQL Server 2012 Management Studio (SSMS2012), I executed the following code..
-- scFTX_CreateTableEmployees.sql -- saved in C:/Documents/SQLServerIndexes_downloadCode -- 26 May 2015 10:52 AM USE ScottChangDB; GO CREATE TABLE Employees
[code]....
Where the SCHEMA and the index 'IX_Employees are located in the Object Explorer of the database "ScottChangDB" of my SSMS2012.Â
Dear all, I'm using SQL Server 2005 Standard Edetion. I have the following stored procedure that is executed against two tables (RecrodedCalls) and (RecordedCallsTags) The table RecordedCalls has more than 10000000 Records and RecordedCallsTags is about 7500000 Records Now the lines marked in baby blue are dynamic (Dynamic where statement) that varies every time this stored procedure is executed, may it contains 7 columns in condetion statement or may it contains 10 columns, or 2 coulmns.....etc Now I want to create non-clustered indexes on the columns used in the where statement, THE DTA suggests different indexing whenever the where statement changes. So what is the right way to created indexes, to create one index on all the columns once, or to create separate indexes on each columns, sometimes the DTA suggests 5 columns together at one if I€™m using 5 conditions, I can€™t accumulate all the possible indexes hence the where statement always vary from situation to situation, below the SP:
CREATE TABLE #tempLookups (ID int identity(0,1),Code NVARCHAR(100),NameE NVARCHAR(500),NameA NVARCHAR(500))
CREATE TABLE #tempTable (ID int identity(0,1),TypesCount INT,CallsType NVARCHAR(50))
INSERT INTO #tempLookups SELECT Code, NameE, NameA FROM lookups WHERE [Type] = 'CALLTYPES' ORDER BY Ordering ASC
INSERT INTO #tempTable SELECT COUNT(DISTINCT(RecordedCalls.ID)) As TypesCount,RecordedCalls.CallType as CallsType
FROM RecordedCalls LEFT OUTER JOIN RecordedCallsTags ON RecordedCalls.ID = RecordedCallsTags.CallID
WHERE RecordedCalls.ID <= '9369907'
AND (RecordedCalls.CallDate BETWEEN cast ('01 Jan 1910 00:00:00:000' as datetime ) AND cast ( '01 Jan 2210 00:00:00:000' as datetime ))
AND (RecordedCalls.Duration BETWEEN 0 AND 1000000)
AND RecordedCalls.ChannelID NOT IN('62061','62062','62063','62064','64110','64111','64112','64113','64114','69860','69861','69862','69863','69866','69867','69868')
AND RecordedCalls.ServerID NOT IN('2')
AND RecordedCalls.AgentID NOT IN('1000010000')
AND (RecordedCallsTags.TagID is null OR RecordedCallsTags.TagID NOT IN('100','200'))
AND RecordedCalls.IsDeleted='false'
GROUP BY RecordedCalls.CallType
SELECT IsNull(#tempTable.TypesCount, 0) AS TypesCount, CASE('English')
WHEN 'Arabic' THEN #tempLookups.NameA
ELSE #tempLookups.NameE
END AS CallsType FROM
#tempTable RIGHT OUTER JOIN #tempLookups ON #tempTable.CallsType = #tempLookups.Code
DROP TABLE #tempLookups
DROP TABLE #tempTable
Thanks all, Tayseer
Any suggestions how to create efficient indexes??!!
I have a table that is in a one way transactional publication. I need to create a full-text catalog on this table and have that catalog exist on the subscriber as well. I understand simply creating a FTC for an object in a publication will not cause that FTC to be replicated to the subscribers. I have scripted out the command and tried to use sp_addscriptexec to push it to the subscriber. When I do this, I get the following error:
Last 183 characters in 'sqlcmd' output buffer: Changed database context to 'database'. Msg 574, Level 16, State 1, Server SQLSERVER, Line 2 CREATE FULLTEXT CATALOG statement cannot be used inside a user transaction.
Also, when this script is run directly from the subscriber, it works fine (but since the subscriber is our production machine, I'd prefer to not have to do it that way). I believe I can reinitialize the publication and have the FTC pushed to the subscriber, but this is not ideal either, since some of our databases can be very large and take a long time to initialize.
Is there a better way to accomplishing this other then connecting directly to the subscriber and running the script or by reinitializing the publication? Thanks for your help!
Is there a performance limit on the number of indexes per table / database ? With Filtered indexes there appear to be many more opportunities for more finely defined, and therefore smaller indexes resulting in many more indexes on a single table.
Hi - please excuse my newness to this. I have a database with several tables and one of them is causing my application to lag really bad. I figure there is either not enough space or something is just wrong in general and i don't know what. Does SQL Server have a shortcut or easy way to test a table in the database?
I am trying to transfer a database from one server to another using the Import Export wizard in SSIS and I am consistantly getting this error on 2 different tables so far.
- Execute the transfer with the TransferProvider. (Error) Messages * ERROR : errorCode=-1073451000 description=The package contains two objects with the duplicate name of "output column "ErrorCode" (79)" and "output column "ErrorCode" (14)". helpFile=dtsmsg.rll helpContext=0 idofInterfaceWithError={8BDFE893-E9D8-4D23-9739-DA807BCDC2AC} (Microsoft.SqlServer.DtsTransferProvider)
This error message is beyond cryptic and when I click on the link it sends me to a web page the just tells me that there is no information available for my current issue. I am transfering the tables to an empty database so I do not understand why I am receiving this error. I have to say that I am not impressed with SSIS at all. I know alot of developers think it's the best thing since sliced bread, however either I am doing something wrong or Microsoft needs to come out with a service pack that fixes these bugs...
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 trying to make changes to the above query. This query will select 2 records per Company with top down priority on JobLevel. For example: This query will select 2 records from "123 Inc", where they have 2 Managers and 3 Staff contacts. The return results selects (2) managers and no staff records and FirstName and LastName are not duplicate. Then for "A Small Co.", they have 1 VP, 1 Director, 1 Manager and 3 Staff contacts. the return results chooses 1 VP and 1 Director and no managers or staff level people. And so on and so forth for the remainder of the companies.
What I was trying to accomplish is having this same query point to the database tables instead creating a table each and every time. I have this data residing in our database. I tried to make the change so it would point to the database tables but it kept giving me errors. This is way more complex than I thought.
My failed attempt:
Select * From CampServ.dbo.SampleFakeCustomerData //This is where I am getting stuck WITH Priorities(Priority, JobLevel) AS( SELECT 1, 'VP' UNION ALL SELECT 2, 'Director' UNION ALL
windows 2000 x86 sp4 sql 2000 enterprise x86 sp4 stand-alone all drives hosting sql databases are SAN attached, write-caching is enabled
server, to a...
windows 2003 enterprise x64 sp1 SQL 2005 enterprise x64 sp2 2-node active/passive all drives hosting sql databases are SAN attached, write-caching is enabled
server.
We are experiencing data corruption on different tables in different databases. So far, we have always been able to quickly resolve the issue by simply running a DBCC DBREINDEX against the table. However, we are unable to resolve the issue with the latest occurrence. Any help would be greatly appreciated. Here is the output (or partial output in some cases) of various commands:
DBCC CHECKDB ('DATABASE_NM')
DBCC results for 'main_notes'. Msg 8961, Level 16, State 1, Line 1 Table error: Object ID 434100587, index ID 1, partition ID 72057594146521088, alloc unit ID 71804568277286912 (type LOB data). The off-row data node at page (1:2487), slot 0, text ID 3788411843723132928 does not match its reference from page (1:34174), slot 0. Msg 8961, Level 16, State 1, Line 1 Table error: Object ID 434100587, index ID 1, partition ID 72057594146521088, alloc unit ID 71804568277286912 (type LOB data). The off-row data node at page (1:2487), slot 2, text ID 341442560 does not match its reference from page (1:2487), slot 0. Msg 8929, Level 16, State 1, Line 1 Object ID 434100587, index ID 1, partition ID 72057594146521088, alloc unit ID 72057594151239680 (type In-row data): Errors found in off-row data with ID 341442560 owned by data record identified by RID = (1:34174:0) There are 1 rows in 1 pages for object "main_notes". CHECKDB found 0 allocation errors and 3 consistency errors in table 'main_notes' (object ID 434100587).
** NOTE ** Index_ID 1 is the clustered index which is on a single-column PK of int type.
DBCC CHECKTABLE ('Main_Notes')
DBCC results for 'main_notes'. Msg 8961, Level 16, State 1, Line 2 Table error: Object ID 434100587, index ID 1, partition ID 72057594146521088, alloc unit ID 71804568277286912 (type LOB data). The off-row data node at page (1:2487), slot 0, text ID 3788411843723132928 does not match its reference from page (1:34174), slot 0. Msg 8961, Level 16, State 1, Line 2 Table error: Object ID 434100587, index ID 1, partition ID 72057594146521088, alloc unit ID 71804568277286912 (type LOB data). The off-row data node at page (1:2487), slot 2, text ID 341442560 does not match its reference from page (1:2487), slot 0. Msg 8929, Level 16, State 1, Line 2 Object ID 434100587, index ID 1, partition ID 72057594146521088, alloc unit ID 72057594151239680 (type In-row data): Errors found in off-row data with ID 341442560 owned by data record identified by RID = (1:34174:0) There are 1 rows in 1 pages for object "main_notes". CHECKTABLE found 0 allocation errors and 3 consistency errors in table 'main_notes' (object ID 434100587). repair_allow_data_loss is the minimum repair level for the errors found by DBCC CHECKTABLE (DATABASE_NM.dbo.main_notes). DBCC execution completed. If DBCC printed error messages, contact your system administrator.
DBCC PAGE (F00115_Chase_TPP, 1, 2487, 1)
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
DBCC PAGE (F00115_Chase_TPP, 1, 34174, 1)
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
DBCC DBREINDEX ('Main_Notes')
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
UPDATE STATISTICS Main_Notes WITH FULLSCAN
Command(s) completed successfully.
select * from main_notes
Msg 7105, Level 22, State 6, Line 1 The Database ID 17, Page (1:2487), slot 0 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.
select * from main_notes (nolock)
Msg 601, Level 12, State 3, Line 1 Could not continue scan with NOLOCK due to data movement.
