Defrag Tool?

I need to know if there are any good tools available to defrag/reindex
SQL2000 databases? I have to manage my own server and I am not a SQL guru. I
run Diskkeeper to take care of the physical files, but I need an easy and
effective way to defrag/reindex the SQL databases.
Thanks,
DerekHi Derek,
You can use DBCC DBREINDEX to reindex and DBCC INDEXDEFRAG to defragment the
indexes. DBCC DBREINDEX will defragment as well, so you don't have to run
them both. DBRINDEX will take out locks on your tables though, so you can
only run it when your database is not in use or very quiet.
You can run DBCC DBREINDEX on all tables with:
EXEC sp_MSforeachtable 'DBCC DBREINDEX (''?'') WITH NO_INFOMSGS'
And you can create a script to defrag all indexes with:
SELECT 'SELECT ''Defragging index ' + name + ' on table ' + OBJECT_NAME (id)
+ ''''+ CHAR(13) +
'DBCC INDEXDEFRAG(0, ' + CONVERT(VARCHAR, id) + ', ' + CONVERT(VARCHAR,
indid) + ')' + CHAR(13) +
'DBCC SHRINKFILE(1, TRUNCATEONLY)'
FROM sysindexes
WHERE indid BETWEEN 1 AND 254
AND OBJECTPROPERTY(id, 'IsMSShipped') = 0
AND INDEXPROPERTY(id, name, 'IsStatistics') = 0
ORDER BY OBJECT_NAME(id), indid
Jacco Schalkwijk
SQL Server MVP
"Derek" <derekb@.nospamderekb.com> wrote in message
news:%239PFWyS6DHA.2628@.TK2MSFTNGP10.phx.gbl...

quote:

---

> I need to know if there are any good tools available to defrag/reindex
> SQL2000 databases? I have to manage my own server and I am not a SQL guru.

---

I

quote:

---

> run Diskkeeper to take care of the physical files, but I need an easy and
> effective way to defrag/reindex the SQL databases.
> Thanks,
> Derek
>

---

|||Thanks. Just to make sure, I would ut the following into Query Analyzer and
run:
EXEC sp_MSforeachtable 'DBCC DBREINDEX (''?'') WITH NO_INFOMSGS'
Also, How do I make my databases offline to run?
Thanks much!
"Jacco Schalkwijk" <NOSPAMjaccos@.eurostop.co.uk> wrote in message
news:uzS2ZQX6DHA.2496@.TK2MSFTNGP09.phx.gbl...

quote:

---

> Hi Derek,
> You can use DBCC DBREINDEX to reindex and DBCC INDEXDEFRAG to defragment

---

the

quote:

---

> indexes. DBCC DBREINDEX will defragment as well, so you don't have to run
> them both. DBRINDEX will take out locks on your tables though, so you can
> only run it when your database is not in use or very quiet.
> You can run DBCC DBREINDEX on all tables with:
> EXEC sp_MSforeachtable 'DBCC DBREINDEX (''?'') WITH NO_INFOMSGS'
> And you can create a script to defrag all indexes with:
> SELECT 'SELECT ''Defragging index ' + name + ' on table ' + OBJECT_NAME

---

(id)

quote:

---

> + ''''+ CHAR(13) +
> 'DBCC INDEXDEFRAG(0, ' + CONVERT(VARCHAR, id) + ', ' + CONVERT(VARCHAR,
> indid) + ')' + CHAR(13) +
> 'DBCC SHRINKFILE(1, TRUNCATEONLY)'
> FROM sysindexes
> WHERE indid BETWEEN 1 AND 254
> AND OBJECTPROPERTY(id, 'IsMSShipped') = 0
> AND INDEXPROPERTY(id, name, 'IsStatistics') = 0
> ORDER BY OBJECT_NAME(id), indid
> --
> Jacco Schalkwijk
> SQL Server MVP
>
> "Derek" <derekb@.nospamderekb.com> wrote in message
> news:%239PFWyS6DHA.2628@.TK2MSFTNGP10.phx.gbl...
guru.[QUOTE]
> I
and[QUOTE]
>

---

|||Hi Derek,
SQL Server databases do not have to be taken offline to be reindexed. The
reindexing process will block other processes from accessing the tables and
indexes that are being reindexed though, which might be inconvenient if the
reindexing takes a long time.
If that is an issue, you can prevent users from connecting to the database
with (SQL Server 2000 only):
ALTER DATABASE <database name> SET SINGLE_USER WITH ROLLBACK IMMEDIATE
and after the reindexing you can give them access again with:
ALTER DATABASE <database name> SET MULTI_USER
Jacco Schalkwijk
SQL Server MVP
"Derek" <derekb@.nospamderekb.com> wrote in message
news:%23PZdXPZ6DHA.2524@.TK2MSFTNGP11.phx.gbl...

quote:

---

> Thanks. Just to make sure, I would ut the following into Query Analyzer

---

and

quote:

---

> run:
> EXEC sp_MSforeachtable 'DBCC DBREINDEX (''?'') WITH NO_INFOMSGS'
> Also, How do I make my databases offline to run?
> Thanks much!
>
> "Jacco Schalkwijk" <NOSPAMjaccos@.eurostop.co.uk> wrote in message
> news:uzS2ZQX6DHA.2496@.TK2MSFTNGP09.phx.gbl...
> the
run[QUOTE]
can[QUOTE]
> (id)
> guru.
> and
>

---

|||Thanks! I will try this later tonight
"Jacco Schalkwijk" <NOSPAMjaccos@.eurostop.co.uk> wrote in message
news:%23Cwm3YZ6DHA.2952@.tk2msftngp13.phx.gbl...

quote:

---

> Hi Derek,
> SQL Server databases do not have to be taken offline to be reindexed. The
> reindexing process will block other processes from accessing the tables

---

and

quote:

---

> indexes that are being reindexed though, which might be inconvenient if

---

the

quote:

---

> reindexing takes a long time.
> If that is an issue, you can prevent users from connecting to the database
> with (SQL Server 2000 only):
> ALTER DATABASE <database name> SET SINGLE_USER WITH ROLLBACK IMMEDIATE
> and after the reindexing you can give them access again with:
> ALTER DATABASE <database name> SET MULTI_USER
> --
> Jacco Schalkwijk
> SQL Server MVP
>
> "Derek" <derekb@.nospamderekb.com> wrote in message
> news:%23PZdXPZ6DHA.2524@.TK2MSFTNGP11.phx.gbl...
> and
defragment[QUOTE]
> run
> can
OBJECT_NAME[QUOTE]
CONVERT(VARCHAR,[QUOTE]
defrag/reindex[QUOTE]
easy[QUOTE]
>

---