asked on

Can you use the database while it's re-indexing?

I didn't know that someone in my company had scheduled a training session on the database where I had started to re-index in hopes of fixing a sql server error. The trainer would be updating and inserting as well as selecting during the training session. Can they still use the database while the re-indexing is going on and not corrupt the database. I realize the response from the database would be slow but would that be the only negative.

Sue_W

ASKER CERTIFIED SOLUTION

Aneesh

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

ExpertAdmin

If it has clustered indexes, the tables will be locked. If it has non-clustered indexes, tables will be selectable but not updatable.

Sorry.

M@

Sue_W

ASKER

Ok! Does the re-indexing lock only one table at a time unless it's has dependencies on another table?

ExpertAdmin

It will lock them as are being indexed, which I would think would limit it to one table at a time. Still, this could cause some nasty intermittent problems.

I am not sure, but you may be able to restart the SQL Server service on the server to cancel the indexing job.

M@

SOLUTION

ptjcb

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

bwdowhan

You can use DBCC INDEXDEFRAG which will not lock the tables.

Here is an excerpt from an article on SQL Server Performance.com (http://www.sql-server-performance.com/dbcc_commands.asp)

DBCC INDEXDEFRAG: In SQL Server 2000, Microsoft introduced DBCC INDEXDEFRAG to help reduce logical disk fragmentation. When this command runs, it reduces fragmentation and does not lock tables, allowing other users to access the table when the defragmentation process is running. Unfortunately, this command doesn't do a great job of logical defragmentation.

The only way to truly reduce logical fragmentation is to rebuild your table's indexes. While this will reduce all fragmentation, unfortunately it will lock the table, preventing users from accessing it during this process. This means that you will need to find a time when this will not present a problem to your users.

Of course, if you are unable to find a time to reindex your indexes, then running DBCC INDEXDEFRAG is better than doing nothing.

Example:

DBCC INDEXDEFRAG (Database_Name, Table_Name, Index_Name)

Sue_W

ASKER

The indexing has been going on for more than 4 hours. Is this normal on a 417,000 Kb database with a raid controller? I think I would like to stop the re-indexing. If I do, then what will happen to the database? Also, if I stop it an re-install the database from a back up will there still be any issues from not finishing this indexing with the new database.

Thanks!

SOLUTION

ExpertAdmin

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

ptjcb

What version of SQL Server (including service pack)? This link may have some info: http://support.microsoft.com/kb/902851

Sue_W

ASKER

I am not going to actually use a backup per se. I plan on installing a clean database with the same name. Is this possible to do without corrupting anything if I stop the indexing or should I change the name? You stated earilier that you thought the indexing could be stopped by stopping the sql service. I acutally ran the re-indexing in query analyzer. Could I cancel the re-indexing through that or would I need to stop the re-indexing by stopping the sql service?

The database isn't being used yet for production so I can start over with a clean copy.

Thanks!
Sue_W

SOLUTION

bwdowhan

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Sue_W

ASKER

Thanks Guys! I really learned some new things so I shared the points.