REINDEXING and CHECKDB JOBS - SQL Server Forums

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.

All Forums

SQL Server 2000 Forums

SQL Server Administration (2000)

REINDEXING and CHECKDB JOBS

Author

Topic

debug2k
Starting Member

18 Posts

Posted - 2004-03-30 : 05:18:50

Hello frens... I am back and with a problem as usual :)

I have a very critical 24*7 mission critical SQL 2000 database server , rightnow the max size of a database in the sever is 4 GB but is expected to grow in future.

Its an OLTP database enviornment, where a daily data load and extraction is done apart from daily OLTP transactions.

So, as the data will grow...I beleive we should have proper Reindexing and CheckDb jobs for the databases so that there are no performance issues and fragmentation in future.

My doubt regarding the same is :

1. Wud these jobs block the objects ?
2. Stop the transactions from being processed ?

Please help in getting them implemented...smoothly.

Regards,
Debug

kish
Starting Member

45 Posts

Posted - 2004-03-30 : 06:21:23

Hi,

1. Wud these jobs block the objects ? Wud these jobs block the objects ?
2. Stop the transactions from being processed ?

DBCC dbreindex and CHeckdb do not lock objects in SQL 2k and transactions continue as normal.
However, you can schedule these jobs to run during non-peak hours
to aviod excess I/O performance issues on the server. Also make sure that you have enough disk space before you DBCC DBreindex because it increases your Database size.

JohnDeere
Posting Yak Master

191 Posts

Posted - 2004-03-30 : 08:30:41

From BOL

DBCC DBREINDEX is an offline operation. While this operation is running, the underlying table is unavailable to users of the database.

DBCC INDEXDEFRAG is an online operation. While this operation is running, the underlying table is available to users of the database.

DBREINDEX cannot be run while others are accessing the table. If you can afford the offline time DBREINDEX does a better job. If you can't be offline INDEXDEFRAG helps but you will still need to rebuild your indexes sometime.

CHECKDB can be run while others are using the table but it can slow down performance.

Lance Harra

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources