I have a scheduled job on my SQL Server 2000 that runs a DBREINDEX job on all the tables in my database. For some reason the system just hangs for hours now when it reaches one particular table. Can somebody please help me to correct the problem. Thank you all in advance.
View 2 RepliesI ran the dbreindex using query analyzer (on the tables mentioned in my previous posting - 1/7/02) and still the scan density remains at 50%.
I do not understand why the dbreindex is not reorging these tables to a
higher scan density.
I used a fill factor of 90%.
HI,
I have a database at 30GB, with 'FULL' recovery model. The transaction log backup after a DBREINDEX job on this database is creating a very huge backup file. Any Idea how can I avoid such a huge backup file.
I have tried to change the recovery model to 'Bulk_logged' before DBREINDEX and changed it back to 'FULL'. That sure helped me to avoid the growth of log file. But, that did not help me to reduce the size of the transactional log backup file.
Regards
Chakri
Is that OK to run dbreindex every 24 hours on heavily modified/inserted tables?Does that harm anyway?Gurus advice needed!
thanks.
We are new to SQL Server and we are currently setting up a job that
does a dbreindex for tables that need it. I got the scripts for this
from swynk. 50 of the tables that needed a dbreindex,
(under 90% scan density) were not impacted by running the dbreindex.
I.e., I ran the dbreindex and it left it at 50% scan density.
The fill factor for these tables is set to 100%.
I tried update stats, updateusage...nothing changes....
I am sure there must be a logical reason as to why the dbreindex
had no impact on these tables. Does any one have any info on this?
Thanks in advance!
I am executing a DBCC DBREINDEX for a table with about
4 million rown in SQL6.5. I has run for about
15 hrs now and is till running.
The index is the PK for the table and it is clustred index.
I ran it on a non clustered index on the same table and it ran for a couple of hrs.
Does any one have any idea how much time it might take for the clustered index?
I heard that this process might take more time and for one of my friends it ran for more than 40 hours.
I don;t have so much if time as th DB has to go for production to night and if not it might effect our businees.
I cancelled DBCC DBREINDEX before by killing the process and the master.DAT file got corrupted.I had to restore everytihng. Does anyone know any work around?
Help appreciated.
Thanks.
Could users use database while running DBCC DBREINDEX for all tables in database?
View 1 Replies View RelatedHi,
I wanted to know is DBCC DBREINDEX is a logged operation?
The reason why I am asking this is, Whenever we create an index or drop an existing it is a logged operation. Is this the same when we do DBCC DBREINDEX? Is it drops existing indexes and creates new indexes. My concern here is about transaction log. If I run a DBCC DBREINDEX on a huge table with lot of indexes, Is it going to fill transaction log?
Any help is appreciated!
Thank you,
Ravi.
About how long should I expect this to run? The Database is 200MB. The table I doing the reindex on has about 800 rows. Is an hour outrageous.. ?
View 1 Replies View RelatedHello!
SQL Server 7
After database clean up (removing old data)I run dbcc dbreindex.
Is it true I don't need to run UPDATE STATISTICS after DBCC DBREINDEX as since SQL Server 7 runs UPDATE STATISTICS automatically (after dbcc dbreindex).
Thank you.
Anny
I am executing a DBCC DBREINDEX for a table with about
4 million rown in SQL6.5. I has run for about
15 hrs now and is till running.
The index is the PK for the table and it is clustred index.
I ran it on a non clustered index on the same table and it ran for a couple of hrs.
Does any one have any idea how much time it might take for the clustered index?
I heard that this process might take more time and for one of my friends it ran for more than 40 hours.
I don;t have so much if time as th DB has to go for production to night and if not it might effect our businees.
I cancelled DBCC DBREINDEX before by killing the process and the master.DAT file got corrupted.I had to restore everytihng. Does anyone know any work around?
Help appreciated.
Thanks.
Can anyone shed some light as to how SQL Server performs DBCC DBREINDEX?
Does it use PRIMARY data file space and how much extra data space/log space do we need to have to be able to run this command?
Hi All,
I have a following script:
DECLARE @TableName sysname
DECLARE cur_reindex CURSOR FOR
SELECT table_name
FROM information_schema.tables
WHERE table_type = 'base table'
OPEN cur_reindex
FETCH NEXT FROM cur_reindex INTO @TableName
WHILE @@FETCH_STATUS = 0
BEGIN
PRINT 'Rebuilding indexes ' + @TableName + ' table'
DBCC DBREINDEX (@TableName, ' ', 10)
FETCH NEXT FROM cur_reindex INTO @TableName
END
CLOSE cur_reindex
DEALLOCATE cur_reindex
GO
I added as a step in the job. However, when I run it I get an error message:
Msg 2501, Sev 16: Could not find a table or object named 'table_1'. Check sysobjects. [SQLSTATE 42S02]
When I run
SELECT table_name
FROM information_schema.tables
WHERE table_type = 'base table'
table_1 is the first on list. Any idea why I am getting this error message?
Has anyone had the problem with DBCC DBREINDEX on a table/index not doing anything.
You just get the usual "DBCC execution completed. If DBCC printed error messages, contact your system administrator." message.
I have tried:
DBCC DBREINDEX ('<table_name>')
DBCC DBREINDEX (<table_name>)
DBCC DBREINDEX ('<table_name>', '')
DBCC DBREINDEX ('<table_name>', '<index_name>')
DBCC DBREINDEX ('<table_name>', '<index_name>', 80)
Maybe its "optimising" and not thinking anything needs updating.
Should I look for an alternative way of rebuilding an index like dropping and recreating?
The beauty with DBCC DBREINDEX ('<table_name>') would have been that I was going to get the list of user tables in the database and call it for each table to simply rebuild all indexes.
We are running a maintenance plan that rebuilds the indexes for all the databases. I have ran showcontig after the maintenance plan and am still seeing fragmentation. Here is one of the master tables that is included in on the maintenance job.
sysobjects table:
pages scanned - 5
extents scanned - 3
extents switches - 3
avg. pages per extent - 1.7
scan density - 25%
logical scan fragmentation - 40%
extent fragmentation - 66.67%
avg.bytes free per page - 1360.4
avg. page density 83.19%
According to what I have read this still seems to be fragmented. What can be done to improve this?
DBCC CHECKDB;
GO
Result:
DBCC results for 'AgentsStateChanges'.
There are 2422697 rows in 46484 pages for object 'AgentsStateChanges'.
CHECKDB found 0 allocation errors and 92 consistency errors in table 'AgentsStateChanges' (object ID 2025058250).
Server: Msg 8934, Level 16, State 3, Line 1
Table error: Object ID 53575229, index ID 1. The high key value on page (1:193625) (level 0) is not less than the low key value in the parent (0:1), slot 0 of the next page (1:269532).
Server: Msg 8935, Level 16, State 1, Line 1
Table error: Object ID 53575229, index ID 1. The previous link (1:269531) on page (1:269532) does not match the previous page (1:193625) that the parent (1:272396), slot 252 expects for this page.
Server: Msg 8936, Level 16, State 1, Line 1
Table error: Object ID 53575229, index ID 1. B-tree chain linkage mismatch. (1:193625)->next = (1:269532), but (1:269532)->Prev = (1:269531).
Server: Msg 2511, Level 16, State 1, Line 1
Table error: Object ID 53575229, Index ID 2. Keys out of order on page (1:266856), slots 14 and 15.
Server: Msg 2511, Level 16, State 1, Line 1
Table error: Object ID 53575229, Index ID 2. Keys out of order on page (1:266856), slots 154 and 155.
Server: Msg 2511, Level 16, State 1, Line 1
Table error: Object ID 53575229, Index ID 2. Keys out of order on page (1:266869), slots 49 and 50.
DBCC DBREINDEX ('Vestel.dbo.AgentsStateChanges', PK_LLAgentFlowSummary, 0);
GO
Result:
Server: Msg 1505, Level 16, State 1, Line 1
CREATE UNIQUE INDEX terminated because a duplicate key was found for index ID 1. Most significant primary key is 'type 24, len 16'.
The statement has been terminated.
PLEASE HELP!;
GO
Hi,
I have a dts job that imports an IIS log(a text, space delimited file) into a table. I do only one, very simple activeX transformation, and other than that, i copy the columns right in. I am loading hundreds of these logs. DTS will load most of them just fine via a batch script, but it sometimes just stops and hangs. I get no errors, but my Server's cpu is at 99%. There are no locks anywhere in the database. Any clues as to why this happens? Since i don't ever get errors, i'm really getting nervous on this one.
thanks.
My DTS job scheduled to run every half an hour is hanging for long.If I stop and restart it will succeed.It happened few times in the past where it ran for 3 days when nobody noticed,and still was executing!Any idea what could be the reason?
Thanks1
Is it Nessary to disconnect users , go in single user mode to execute
DBCC DBREINDEX in SQL Server 2000 ?
If I reindex tables on a production server and dump the transaction log then load that tran log to a
warm standby server --will the indices be rebuilt on the standby server? My guess is no,
but I've heard people say otherwise.
--jen
when i run the dbcc dbreindex command it failed and message like this
[Microsoft][ODBC SQL Server Driver][Named Pipes]ConnectionCheckForData (PeekNamedPipe()).
[Microsoft][ODBC SQL Server Driver][Named Pipes]Connection broken.
Connection Broken
please help
I pretty much understand the differences between DBCC DBREINDEX and DBCC INDEXDEFRAG. However, I need the forums help to understand a few specific issues relating to clustered/non-clustered indexes and the advantages/disadvantages of running the DBCC DBREINDEX/INDEXDEFRAG against the table or against each specific index...
"If a table has a clustered index, it's only necessary to re-index the clustered index because any non-clustered indexes on that table will be automatically re-indexed as well."
I think the above statement is true for DBCC DBREINDEX but is the following statement true for DBCC INDEXDEFRAG:-
"If a table has a clustered index, it's only necessary to Index Defrag the clustered index because any non-clustered indexes on that table will be automatically defragged as well."
Following on from the above, is there any advantage with an index maintenance strategy to individually running DBCC DBREINDEX against each specific index as opposed to running it against the table and letting SQL sort out the underlying indexes? Does the same apply to DBCC INDEXDEFRAG?
Regards,
Clive
DBCC SHOWCONTIG shows following information. But after ran
DBCC DBREINDEX ('CM20100', ''), nothing changed.
What I did wrong? Thanks in advance.
******************************************************
DBCC SHOWCONTIG scanning 'CM20100' table...
Table: 'CM20100' (1957582012); index ID: 0, database ID: 16
TABLE level scan performed.
- Pages Scanned................................: 491
- Extents Scanned..............................: 159
- Extent Switches..............................: 158
- Avg. Pages per Extent........................: 3.1
- Scan Density [Best Count:Actual Count].......: 38.99% [62:159]
- Extent Scan Fragmentation ...................: 99.37%
- Avg. Bytes Free per Page.....................: 946.0
- Avg. Page Density (full).....................: 88.31%
I have a stored proc that identifies indexes that need to bedefragmented ( LogicalFragmentation > 20% ) using DBCC SHOWCONTIG.This works fine and shows me all of the candidate indexes to bedefragged. I am using DBCC DBREINDEX to rebuild the indexes. What Inoticed yesterday is that it seems that DBREINDEX is not actually doinganything. I would check the target indexes, run DBREINDEX, then checkagain. The same list of indexes came up unchanged.What is strange is although I am not specifying "WITH NO_INFOMSGS" thisis the only output from the DBREINDEX:"DBCC execution completed. If DBCC printed error messages, contact yoursystem administrator."According to BOL DBREINDEX should return a result set if "NO_INFOMSGS"is not specified, but if "NO_INFOMSGS" is supplied the prior message iswhat is returned. This doesn't seem to be working correctly, or if itis, I have no idea where any error messages are being returned.Anyone have any ideas?
View 2 Replies View RelatedIs this command, which I know can be executed on a per-table basis,the same as the Rebuild Index Task in the SQL Server Management Studiofor maintenance tasks??The documentation I have mentions the reindex.sql script, is this thescript that is executed by the Rebuild Index Task??Thank you, Tom
View 1 Replies View RelatedHi,
I've previously raised a thread about an index corruption issue we are getting on an infrequent basis. We are running SQL 2000 SP4, patched up with latest hotfixes on a cluster.
Yesterday, we experienced the corruption again. In the past, running DBCC DBREINDEX on the corrupted index has fixed the problem.
However, this time, even though DBCC DBREINDEX reported that it completed successfully, the corruption remained (proved by running DBCC CHECKTABLE on the index).
First question:
We created an identical index, with a different name, using the CREATE INDEX command, but this was created with the same corruption. Does SQL Server try and be clever and say "this index is the same as another, so I'll just copy that one instead of creating a new one" ? This is what would appear to be the case.
We then dropped the index and then recreated it. This time the index was fine.
Second question:
My understanding of DBCC DBREINDEX is that it drops and recreates the index. As manually dropping and recreating the index worked but DBCC DBREINDEX didn't I can only assume that this isn't the case. So what does it do?
This then begs a third question that, as I've been 'fixing' the issue in the past by running DBCC DBREINDEX, is it possible that it fixes it enough for DBCC CHECKTABLE to think it's been fixed, but deep down the problem has remained which is why the index has continued to corrupt a few weeks down the line?
If anyone can help answer these questions that would be really useful.
Thanks.
I have a problem with a process hanging my SQL server machine and the only thing that can fix it is to reboot the machine.
The environment is SQL Server 7, NT sp5, dual processor 500MHZ, 1 gig Ram.
The applications are run through MTS written in VB all executing stored procedures.
The symptoms are that the process hangs with an open transaction and it can be seen through DBCC OPENTRAN. It can also be seen in MTS on the Transaction List screen and shows as Aborting. It seems to get stuck in that state.
The Kill command does not work on the process either. I have tried one fixs that I thought may be causing the error by setting the Max Degree of Paralelism in sp_configure to 1 but that does not fix the problem.
Has anyone seen this and/or have any ideas on how to fix/repair the problem.
Thanks
Greg Flynn
One of my clients runs a report using Access retrieving data from SQL*server. Recently I often encounter a problem which makes the server hang.
The error messages are something like this, can somebody help?
SqlDumpExceptionHandler: Process 42 generated fatal exception c0000005 EXCEPTION_ACCESS_VIOLATION. SQL Server is terminating this process.
Error: 0, Severity: 19, State: 0
CImageHelper::GetSym Error - The specified module could not be found
Stack Dump being sent to H:MSSQL7logSQL00158.dmp
Thanks for any thoughts.
Roy
I created a simple DTS which executes a VB standard exe that simply writes a string to an ascii file opened as append. SQL Server, the exe, and the ascii file are all on the same NT box (mine). If I execute the DTS myself the process works with no problems. When I attempt to execute the DTS via a job the job hangs with no apparent indications as to what may be the cause of the hangup. The SQL Server Agent is up and running and set to run under the system account. I have applied SQL Server SP3. The same problem was occuring prior to applying SP3.
Does anybody have any idea? All sugestions are appreciated.
Thanks in advance.
We have just combined 3 sites into one server.
Two of the sites are serving fine, however one site has spids
that won't disconnect. By the end of the day there are over 600 spids
from that one site. At first we thought it was due to the ASP pages
had a db connect, but nothing that closed that db connection. But after
they modified the pages, we are still having the same problem. Checked
the webserver and they are identical to the other webservers.
Any ideas???
Can anyone suggest what might be causing SQL Server 6.5 to hang?
The following messages were in the error log just before, but Books Online is not much help:
2000/08/21 10:35:20.90 ods Error : 17805, Severity: 18, State: 0
2000/08/21 10:35:20.90 ods Invalid buffer received from client.
2000/08/21 10:35:20.90 spid142 Process 142 entered sequencer without owning dataserver semaphore
there are then many messages like this one:
2000/08/21 10:36:21.06 ods Error : 17824, Severity: 10, State: 0
2000/08/21 10:36:21.06 ods Unable to write to ListenOn connection '.pipesqlquery', loginname 'E04180', hostname 'N90459'.
2000/08/21 10:36:21.06 ods OS Error : 109, The pipe has been ended.
and finally many more messages like this:
2000/08/21 10:51:20.75 ods Unable to connect. The maximum number of '750' configured user connections are already connected. System Administrator can configure to a higher value with sp_configure.
I don't think that simply increasing the number of user connections will help, but if anyone can throw some light on the cause of the first message I would be grateful.
Thanks,
John
I am attempting a very simple bcp out to a text file in SQL 2000 for the first time. I've tried various syntax but the command consistently hangs no matter which i use. Nothing is being directed to the output log............
"bcp rfsspd..bcp_product_view out product.out -T -c -b500 -r>>test.log"
I have no problems with this command in SQL 6.5, could it be initial setup or config of 2000 or something really daft????
Any help greatly appreciated
I have a job which is set of few Stored procedures,Usually it taked around 3-5 mins to complete the job.But somehow today the job was still executing even after 3:45:24 (yes 3 hrs,45 mins 25 secs)
WHen i tried to run the each procedure indivdually even its taking more time in the query analyzer.But when i try to execute those SPS as individual sql statements(it's step by step) they were working in reasonable time.What should be the reason for these SPs taking that much time?
Thanks.