Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
Hommer
Aged Yak Warrior
808 Posts |
 Posted - 2005-03-04 : 10:51:30
|
| Can I run a reorganize data and index pages maintenance plan while all the users are using the database?Does dropping an index then recreating one on a table need an exclusive lock on the table?I know this sounds like a dumb question, but I just could not find answer anywere else. So for it is a nightly job. Can I force it to run during the day?Thank! |
|
|
chadmat
The Chadinator
1974 Posts |
Posted - 2005-03-04 : 14:34:59
|
| I think that would be a bad decision. As far as I know, you could run it with database activity, but reindexing takes a lot of resources and a lot of logging. So I would run it at the time of least load.-Chadhttp://www.phxpoker.comPhoenix's largest online poker community |
 |
|
|
Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)
7020 Posts |
Posted - 2005-03-04 : 15:06:08
|
Re-indexing is something you should do a times of near zero activity.I would stay away from dropping and recreating the index on a routine basis. Too much can go wrong with that.If you don't have a time window for re-indexing, you might consider using DBCC INDEXDEFRAG. It can be run while users are in the database. A word of caution: it can generate large transaction log dumps, so test it to see how well it works in your situation. Read the details in SQL Server BOL.quote:
Originally posted by Hommer
Can I run a reorganize data and index pages maintenance plan while all the users are using the database?Does dropping an index then recreating one on a table need an exclusive lock on the table?I know this sounds like a dumb question, but I just could not find answer anywere else. So for it is a nightly job. Can I force it to run during the day?Thank!
CODO ERGO SUM |
|
|
|
derrickleggett
Pointy Haired Yak DBA
4184 Posts |
|
|
|
|
|
Do I need to stop server for reindexing