Board index » delphi » MS-SQL 2000, recreating indexes

MS-SQL 2000, recreating indexes

Hi,

we use D5 and MS-SQL 2000 (we used MS-SQL 7 since one month ago).
Normally we recreated any Index every night, but with MS-SQL 2000 this
seems not be possible. In the log it is said, that "recreating indexes" can only
be done, when in single_user mode. How can I make the MS-SQL-Server
recreate them at night automatically (Without manually chaning the DATA-
BASE to SINGLE_USER)?

Is it furthermore necessary in MSSQL 2000 to recreate the indexes or is
this done on the fly?

thx a lot
carsten willems

 

Re:MS-SQL 2000, recreating indexes


Quote
"Carsten Willems" <cwill...@eoa.de> wrote in message

news:3c568e79$1_2@dnews...

Quote
> Hi,

> we use D5 and MS-SQL 2000 (we used MS-SQL 7 since one month ago).
> Normally we recreated any Index every night, but with MS-SQL 2000 this
> seems not be possible. In the log it is said, that "recreating indexes"
can only
> be done, when in single_user mode. How can I make the MS-SQL-Server
> recreate them at night automatically (Without manually chaning the DATA-
> BASE to SINGLE_USER)?

Generally speaking, rebuilding indexes is strictly server's job/business.
You should do that only if some of them got corrupted. And there's very slim
chance they'd ever.

Quote

> Is it furthermore necessary in MSSQL 2000 to recreate the indexes or is
> this done on the fly?

I run SQL7 and I have never called reindex.

rb

Re:MS-SQL 2000, recreating indexes


thx a lot
cw

Re:MS-SQL 2000, recreating indexes


Hi,

We are using D5 with SQL server 7 and we are planning to migrate to 2000.

You should recreate index every once in a while.
The main reasons I think its worth DROP/CREATE index are:
  1) For table batch updates.
     When I need to import/update tables with external data from file (CSV, XML or fixed length
file) or from other Databases/Servers.
     I will drop the index to speed up process time and then recreate them.
  2) Index will get unbalanced after a while (even with casual inserts/updates/deletes).
  3) Fill factor is no longer optimal.

Cheers,
Clment

Quote
Carsten Willems wrote:
> Hi,

> we use D5 and MS-SQL 2000 (we used MS-SQL 7 since one month ago).
> Normally we recreated any Index every night, but with MS-SQL 2000 this
> seems not be possible. In the log it is said, that "recreating indexes" can only
> be done, when in single_user mode. How can I make the MS-SQL-Server
> recreate them at night automatically (Without manually chaning the DATA-
> BASE to SINGLE_USER)?

> Is it furthermore necessary in MSSQL 2000 to recreate the indexes or is
> this done on the fly?

> thx a lot
> carsten willems

Re:MS-SQL 2000, recreating indexes


Sorry for the above post. (u...@domain.invalid)
Im new to netscape 6 :-)

Cheers,
Clment

Other Threads