Hi, all,
I want to re-create an index on a production table.
I got an error 644 "could not find index entry...".
The DBCC CHECKDB and CHECKTABLE gave me this:
Server: Msg 8928, Level 16, State 1, Line 1
Object ID 37575172, index ID 6: Page (1:939782) could not be processed. See other errors for details.
Server: Msg 8939, Level 16, State 1, Line 1
Table error: Object ID 37575172, index ID 6, page (1:939782). Test (*(((int*) &m_reservedB) + i) == 0) failed. Values are 7 and 36.
DBCC results for 'Mfg_DFSFNSF'.
There are 1142314 rows in 326143 pages for object 'Mfg_DFSFNSF'.
CHECKTABLE found 0 allocation errors and 2 consistency errors in table 'Mfg_DFSFNSF' (object ID 37575172).
The table script for the index is like this:
CREATE UNIQUE INDEX [Mfg_ITMDH_MbrIdx] ON [dbo].[Mfg_DFSFNSF]([_ITMDH_OwnRow], [_ITMDH_MbrKey], [RECTYPE]) WITH FILLFACTOR = 70 ON [PRIMARY]
My question is that can I drop it and run above create it to fix the problem in live mode?
I know the other option will be:
DBCC CHECKTABLE (FSDBMR.dbo.Mfg_DFSFNSF, repair_allow_data_loss)
But that has to put the db under single user mode.
I need a way to push create index statements drop to the subscribers. If I can use replication to distribute index creation that would be great. We don't allow our clients to make any schema changes directly. We are using transaction and merge replication.
hello everyone,we dropped the clustered & nonclustered indeces on a table, thenrebuilt them. logical fragmentation is near zero, but extentfragmentation is about 40%. how can this be if the indeces are brandnew?
Hi, I am trying to drop an index usiong enterprise manager.The process hangs and Enterprise manager is not responding.When I checked the Current activity the process is in sleeping condition.I appreciate if any one can tell me what could be the reason for the process to be hung. thanks Mohan
I have a table which I am unable to drop the index. I tried to drop it via the TSQL and GUI interface (Indexes dialog box), but when I clicked on table refresh, the index still there.
Below is the result after DBCC commands:
1. DBCC checktable(TB_LOCLoan)
Checking TB_LOCLoan The total number of data pages in this table is 146542. Table has 1596232 data rows. Msg 605, Level 21, State 1 Attempt to fetch logical page 462136 in database 'DM_LOCLoan' belongs to object '1744009244', not to object 'TB_LOCLoan'.
2. DBCC newalloc(DM_LOCLoan)
TABLE: TB_LOCLoan OBJID = 832005995 INDID=0 FIRST=60168 ROOT=642778 DPAGES=146542 SORT=0 Data level: 0. 146542 Data Pages in 18393 extents. INDID=5 FIRST=463752 ROOT=463739 DPAGES=5304 SORT=1 Msg 2525, Level 16, State 1 Table Corrupt: Object id wrong; tables: alloc page 463616 extent id=463752 l page#=463752 objid in ext=-832005995 (name = -832005995) objid in page=832005995 (name = TB_LOCLoan) objid in sysindexes=832005995 (name = TB_LOCLoan) TOTAL # of extents = 18393
3. From Error Msg 2525, I have tried the suggested action
use DM_LOCLoan go select indid, name, object_name(id) from sysindexes where id=832005995 and distribution=463752 go
This query does not return any row.
I am running out of clue of how to proceed! Anybody come across this problem before? I am very eager to solve this problem as the table is hanging here, I can't even drop it to recreate and continue data loading.
According to sysindexes, I have a table with a primary key and an index. I'd like to drop the index. However, when I give the drop command, the message "Cannot drop the index 'eventlog._INDEX', because it does not exist in the system catalog." is returned.
I'm not sure what to look for. How do I remove the index?
I'm trying to drop indexes if they already exist then recreate them as needed, however, I'm getting an error if the index does not exist and I thought that's what the "IF Exists" statement was for.
syntax: IF EXISTS (SELECT name FROM sysindexes WHERE name = 'idx_acct_no') DROP INDEX accounts.idx_acct_no go create index [idx_acct_no] ON [dbo].[accounts] ([acct_no]) go
Error when index does not exist: Server: Msg 3703, Level 11, State 7, Line 1 Cannot drop the index 'accounts.idx_acct_no', because it does not exist in the system catalog.
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.
Is there anyway to drop an index at the partition level in SQL 2005 ?There is a way to rebuild at the partition level (assuming this creates)i.e.: alter index ALL on dbo.LP_CQ rebuild partition = 229 ;I want to:1. Drop index(es) at partition level (partitioned by period value (1..400)).2. bulk copy data in (can be 1 or more periods per day).3. Rebuild at Partition level.Thx
I just tried to backup a database I recently deployed to to SQL Server 2005 box, and got the following error:
The backup of full-text catalog 'myCatalog' is not permitted because it is not online. Check errorlog file for the reason that full-text catalog became offline and bring it online. Or BACKUP can be performed by using the FILEGROUP or FILE clauses to restrict the selection to include only online data.
I don't need the index so I tried to drop it with both:
DROP fulltext catalog 'myCatalog'
and
DROP catalog 'myCatalog'
but both give me a syntax error.
How can I get rid of this index so I can backup my database?
SQL SERVER 2000System let's you alter the system tables and add indexes. However, it won'tlet you drop the index afterward.Anybody know how to drop an index on a system table?Thanks,Kevin
I have a non-clustered index on a table. If I rebuild or reorganize it in SQL 2005, the total fragmentation percent reported by properties/fragmentation on the index stays at 33%.
Why doesn't the fragmentation go to 0% ?
If I totally drop/create the index, starts even higher, but beorg or rebuild simply goes to 33%. This even if using with use temp db for sort option.
We have one LEDGER, where all the daily activities are stored. The LEDGER table has 4 indexes (1 clustered and 3 non-clustered). To get AR we use this table.
Well problem is some times in 1-2 months, any simple AR query takes a long time and every other client gets slow response (queries are very slow or sometimes block).
If we DROP any index on LEDGER table and again put it back (RECREATE), all our queries work fine and faster. This goes on till 1-2 months, till we see the same issue again.
This is a classic case happened today. Queries were running fine till morning 8 AM. We upload some 50 thousand records to Ledger table (Data Conversion). Well after 30 mins, all simple AR queries started taking a long time. We DROPPED an index in LEDGER table and everything was faster....Just to be same we added back the same index again.......everything is Faster.....
What is this. ....is it our QUERY, index or huge Transactions or no free space ???
We are scheduled to run SP4, next week. But is there any solution in the mean time on what is this?
Also is they any way to KILL all SQL server processes that take more than a mins. We just don't want ALL our client to Slow down because of one query????
After the incremental process and full process, SSAS doesn't drop the index files #.xxx.fact.map and #.xxx.fact.map.hdr in the file.0.dim folder. We now have all the versions from 3 to 5000 sitting in the folder. The DBA team only found this when the disk is running out of space recently.
We've already check the account running the SSAS has local admin to the server.
Is there any config setting that might cause this issue? If not, what could it be causing this issue.
What is the impact on the users to drop an index on a table while in use? I will recreate the index afterwards. The table is used constantly by a three of processes/users at all times.
This table takes up almost 80% of my database size, and the information is this table just captures the time spent by a user on the website(not very critical data)
I would like to know how to delete the entire index (which is what is occupying most space) to free up disk space. the index is a clustered index.
Can I dynamically (from a stored procedure) generatea create table script of all tables in a given database (with defaults etc)a create view script of all viewsa create function script of all functionsa create index script of all indexes.(The result will be 4 scripts)Arno de Jong,The Netherlands.
Hi I am wana to know why we every time drop the procedure and again create it as below...will that impact on performance or compilation of SP???
Use Northwind GO IF OBJECT_ID('dbo.ListCustomersByCity') IS NOT NULL DROP PROC dbo.ListCustomersByCity GO CREATE PROCEDURE dbo.ListCustomersByCity @Country nvarchar(30)='%' AS SELECT City, COUNT(*) AS NumberOfCustomers FROM Customers WHERE Country LIKE @Country GROUP BY City GO
I had a question and did not obtain an answer. So I am trying to rephrase and ask again, in case I was not clear the first time.
When we drop and recreate a table, do we always have to recompile the stored procedures that reference the table? Or is only under certain scenarios that we need to do so- like if an index on the table is changed Please let me know Thanks in advance Kiran
I have to run a Big Sproc for make a lot of updates and insert. because trigger it take to many time. I can drop the trigger before the procedure and recreate it after, but I wondered whether there existed of other solution?
Can I deactive the trigger? I'm affraid too got two copie of code for the trigger that why I dont really like the Drop-Create solution...
I have 2 questions: 1. How can I drop /remove a complete db from MSDE desktop engine? 2. After exporting my db onto MSDE server and get connected those db tables in FE (adp), how can I create a new table in the same back end db?
I know these are basic questions but since I am new to MSDE I hope the forum would bear me.
Hi, How can i create and drop table in MS SQL Server 2000 via VB6? I think I should use ADOX object, but I don't know exactly how.... The following code uses ADO connection object and returns with runtime error "incorrect syntax near AS":
Dim db as ADODB.Connection '... open connection to database
Dim strCmd As String
strCmd = "CREATE TABLE tmp_tbl AS SELECT * FROM tbl"
How to SQL server save information about create/drop objects action. How can I get this? Example for, a lot of objects(sp) in my database has been dropped, how I know who was dropped them (user login & time)?
I am Srinivas working on SSAS 2005. I am not able to create or drop dimension members. I need to drop and create all members in one dimension before cube processing, as the columns are coming dynamically.
E.g. ALTER CUBE [Reporting] DROP DIMENSION MEMBER [Tbl Analysis Test].[Name] Here cube is [Reporting] , dimension is [Tbl Analysis Test] and member is [Name].
Its giving the following error " Parsing the query ... Query (1, 46) The member '[Name]' was not found in the cube when the string, [Tbl Analysis Test].[Name], was parsed. Parsing complete"
Please look into this issue, advice me what do I do ASAP.
Thought I should post in the newbie forum for a while, instead. :-)
I have a couple of scripts that I've generated that drop a couple of system stored procedures and recreate them. I'm not sure why I did it in the first place, but I think it was that it wouldn't let me run an ALTER statement on them. Specifically, I'm now looking at sp_add_operator. I changed it to a 500 character email field instead of whatever it was (100, I think.)
/* Explanation: Why did I do that? SQL Mail is prohibited here, so I'm using CDO_Sysmail to email myself and the developers if a job fails. The list of people to email is determined by the owner of the database, who is also an operator in SQL. I get the list of emails from the email field of the operator properties. Hence, I need a bigger email field. Yes, I now know it would most likely be better to create an ADMIN database on each server for this kind of stuff. (Thanks to Tara for that blogged suggestion.) */
While I will probably go back to the default stored procedure, this got me to thinking: when would it be better to use an ALTER statement on a SProc rather than to do a DROP and CREATE?
Hi, I increased one of my base tables column which is referenced in view
I noticed sql server didn't recognized this change and its still showing old field size in the view.
I can simply drop and create it again. But wanted to know if there is any way (command/sp) to recompile the view which will be easy to deploy in production as patch.
