[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 524
  • Last Modified:

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
0
Sue_W
Asked:
Sue_W
  • 4
  • 3
  • 2
  • +2
4 Solutions
 
Aneesh RetnakaranDatabase AdministratorCommented:
Usually the DBCC operations will Lock the associated tables, so you wont be able to read the data from those..
0
 
ExpertAdminCommented:
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@
0
 
Sue_WAuthor Commented:
Ok! Does the re-indexing lock only one table at a time unless it's has dependencies on another table?
0
Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.

 
ExpertAdminCommented:
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@
0
 
ptjcbCommented:
DBCC reindex will drop an index and then recreate it. It should be one table at a time.
0
 
bwdowhanCommented:
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)


0
 
Sue_WAuthor Commented:
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!
0
 
ExpertAdminCommented:
Without knowing the table structures that is hard to determine. If you are using clustered indexing, the data is actually rearranged on the disk and it can take a long time. But 4 hours does seem excessive.

I will have to yield to someone with more indexing knowledge on this one. But if it were me I would be a little nervous about betting it all on a reload from backup until the backup had been verified on another machine.

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

0
 
Sue_WAuthor Commented:
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
0
 
bwdowhanCommented:
I believe that if you click cancel from Query Analyzer, it is going to try and rollback the changes which will more than likely take longer than the reindex (so another 4-5 hours). If you really don't need this database for anything, look for the id number in the bottom of the Query Aanalyzer window running the DBCC DBREINDEX command (its by your name in () ) ,  Open a new QA windows and run KILL xx (where xx is the id you found in the window running the DBCC command. This will stop the process and you can delete or restore, ore create a new database.


0
 
Sue_WAuthor Commented:
Thanks Guys! I really learned some new things so I shared the points.
0

Featured Post

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

  • 4
  • 3
  • 2
  • +2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now