Monday, December 17, 2007

Rebuild index, when does it complete?

I have recently worked on the client site (SQL Server 2000) to help on optimization. They have a very huge table (about 250 million rows) and perfom rebuild index every day at night. That jobs has beeen completed in two hours or so till this monday. The job was in 'executin status' for more than 10 hours and imagine what happened to the LOG file. My first thing was it would be really helpfull to have any system table that shows a percentage or whatever about the rebuild index process (something similar to what we have in SQL Server 2005 for some commands (BACKUP DATABASE) to see percent's complete )as I did not intend to KILL the process.Finally we got on and the process was completed. I suggested to the client rebuilding only heavy fragmented indexes especially if you have such huge table. Also , it is unnecessary to rebuild that table every day (it depends on your business requirements) ,so in their case , it is enough once a week.

In SQL Server 2005 for below operations you are able to see completion time or percent of completion.

DBCC CHECKDB
DBCC SHRINKDATABASE
DBCC SHRINKFILE
BACKUP DATABASE
ROLLBACK


SELECT TOP 2 start_time,
percent_complete ,estimated_completion_time
FROM sys.dm_exec_requests
ORDER by start_time desc

2 comments:

Nike shox shoes said...

Modern parents are inundated with information on how to mould their children into responsible adults, capable of navigating the treacherous waters of society

Nike shox shoes can surely not let you down,romance yourself with nike shox

Unknown said...

I am very happy to read this. Appreciate your sharing
kid games
friv2
unblocked games
juegos de un show mas