Hello guys,
Two things:
1) Could somebody explain me how to reindex all tables in my db?
2) How do I know when I should reindex my db?
Thank you very much for any help!
Regards,
Fabian
my favorit hoster is ASPnix : www.aspnix.com !
Attempt to fetch logical page (1:166354) in database 'pm_pmc_prod' belongs to object '837120', not to object 'ng_visit'.
I got his error while importing data into the error, when i ran dbcc checkdb it gave me Msg 8928,8942,8976 etct witl serverity level 16...
is it possible to fix this curropt tables?
We load tables from text files for inquiries.
My procedure is to truncate the table
Use DTS to move the text file into the table
do the command DBCC DBREINDEX (TABLE,'')
Am I wasting my time?
Does SQL 7 rebuild the indicis as it loads the data from the text file?
I've just recently tried to perform a scheduled reindexing job with the following command:
EXEC sp_MSforeachtable @command1="print '?' DBCC DBREINDEX ('?')"
Unfortunately, this command has only finished once of the five or six times I've tried to run it. The times it has failed, it deadlocks behind what seems to be a succession of other transactions. How can I make sure this command finishes?
Hello Everyone,
I need to rebuild all indexes in one table, is there any command that will do automatically instead of deleting and recreating them all?
Any help will be appreciated.
Thanks
Hi All,
is it really improve performance that making table reindexing?
what i mean to say is i've one script, which will automatically drops all the indexes in a database, and reconstruct them with the same name.
is it really worth doing that?.....
thankyou very much
Vinod
Even you learn 1%, Learn it with 100% confidence.
All,
I've got a medium sized database in a mirror configuration with witness. The database size is about 300gb and I would to reindex all of the tables in the database. My process would go something like this:
1) Backup principal
2) Break the mirror
3) Set the principal database to simple recovery mode
4) Perform the reindexing
5) Backup the principal and transfer that backup to the mirror
6) Restore the backup
7) Re-establish the mirror
Does anyone see any issues with the process itself?
Regards,
Ian
Hi experts,
For defragmenation and reindexing they are using the below cursor, and now they have asked me to remove the cursor and schedule the job accordingly to do the same functionality, so what will be the other way we can do without cursor? can you plase let me know the solution?
DECLARE @Database VARCHAR(255)
DECLARE @Table VARCHAR(255)
DECLARE @cmd NVARCHAR(500)
DECLARE @fillfactor INT
SET @fillfactor = 90
DECLARE DatabaseCursor CURSOR FOR
SELECT database_name FROM dbadmin.dbo.tdbstatus WHERE status='y'
AND database_name NOT IN ('master','model','msdb','tempdb')
ORDER BY 1
OPEN DatabaseCursor
FETCH NEXT FROM DatabaseCursor INTO @Database
WHILE @@FETCH_STATUS = 0
BEGIN
SET @cmd = 'DECLARE TableCursor CURSOR FOR SELECT table_catalog + ''.'' + table_schema + ''.'' + table_name as tableName
FROM ' + @Database + '.INFORMATION_SCHEMA.TABLES WHERE table_type = ''BASE TABLE'''
-- create table cursor
EXEC (@cmd)
OPEN TableCursor
FETCH NEXT FROM TableCursor INTO @Table
WHILE @@FETCH_STATUS = 0
BEGIN
SET @cmd = 'ALTER INDEX ALL ON ' + @Table + ' REBUILD WITH (FILLFACTOR = ' + CONVERT(VARCHAR(3),@fillfactor) + ')'
EXEC (@cmd)
FETCH NEXT FROM TableCursor INTO @Table
END
CLOSE TableCursor
DEALLOCATE TableCursor
FETCH NEXT FROM DatabaseCursor INTO @Database
END
CLOSE DatabaseCursor
DEALLOCATE DatabaseCursor
Your help will be appreciated.
In the Enterprise Manager of SQL Server 2000 I have set up a maintenance plan which rebuilds my indexes. I've stuided the documentation, and from what I've learned what happens behind the curtain is that several DBCC REINDEX commands are being issued.
Question:
If I have 20 tables and 40 indexes: Will SQL Server do the maintance plan in 1 single transaction, or will it divide the it up to eg. 20 or 40 transactions?
-h
Hi
We are upgrading from sql 7 to 2000.During the upgrade process do we have to do a reindexing of all tables or will update statistics take care of that.
Or do we have to do both?
What is the difference between reindexing and update statistics.
Thanks
Madhukar Gole
Hi All,
Just after some feedback on a scenario where we have full logging setup on one of the databases, and the transaction logs are backed up every 60min. At 0000-0100 the log jumps from being a few thousand k up to over 1.7gb.
I did some profiling for this time, and it appears that this jump is related to the reindexing of the indexes on the database.
Is this normal for the log file to jump in size so much? Or is this an indication of some other issue (potentially with the indexes)?
Is there any way that the reindexing can be excluded from the log files or is this a necessity?
Thanks in advance for your help.
Cheers
Troy
hi
i have reindex of the tables
is it really improve performance that making table reindexing ?
Folks,I work on a system which is growing rapidly, with the number oftransactions we process growing on a daily basis. While this is goodnews or the business, maintenance is starting to become an issue as thedatabase is the backend for a website which cannot be down for alengthy period of time.While I do defrag the indexes, periodically the indexes do need to berebuilt. When this happens, the process locks pages and transactionsstart getting bounced out.Are their any third party utilities which will rebuild an indexwithout this locking occuring? Any help in pointing me in the rightdirection would be appreciated.
View 1 Replies View RelatedDoes someone know if doing a reindex on a clustered or non-clustered index cause the snapshot file to grow? In other words, is the data that makes up the snapshot copied from the source to the snapshot database? If a normal reindex is done on the underlying database, will it block users from acessing the snapshot? Any help would be appreciated.
View 1 Replies View Related
I need to reindex all tables in my database and would like to do this without using a Cursor. What is the simplest way to achieve this.
Cheers
Nat
All,
I've got a medium sized database in a mirror configuration with witness. The database size is about 300gb and I would to reindex all of the tables in the database. My process would go something like this:
1) Backup principal
2) Break the mirror
3) Set the principal database to simple recovery mode
4) Perform the reindexing
5) Backup the principal and transfer that backup to the mirror
6) Restore the backup
7) Re-establish the mirror
Does anyone see any issues with the process itself?
Regards,
Ian
Hi,
We have scheduled a job for DB Reindexing (Maitinance Plan) for a OLTP database on sunday.
We have used mirroring for automatic failover with a witness server now the DR Reindexing job fails after 30 mins without any error.
Please let me know why Database reindexing gets failed.
Regards
Sufian
Hello,
I'm looking for the query command that will go out to all the user Tables and will tell me what Indexes need to be reindexed.
We are having a problem with some of the tables and we don't know when our tables need to be reindexed other than when operations are stopped for our company.
Thanks,
Ron
Hello. When reviewing the DBCC SHOWCONTIG immediately after reindexing all indexes on a database, I see the ExtentFragmentation has values like 50 to 70%... These are SQL 2005 tables with clustered PK's, no large varchars/blobs, and at least 100 pages in the index... The numbers related to PAGE fragmentation are ok after reindexing, but not the EXTENT fragmentation numbers.
I noticed the drive is in need of being defragged at the disk level. Is that a reason why reindexing doesn't fix the Extent frag numbers?? ANy other ideas on this? I can try defragging the DISK over the weekend, bringing the database offline then, but any other thougths on why the Extents show these high %'s? Is there any command to reset them and maybe that isn't happening? Like must I do update usage to get valid Extent frag #'s??
If there were MANY autogrows on the files, is that a different level of fragmentation? and how could all those small pieces of files be pulled back together? Thanks, Bruce