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

3 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

aiya said...

Office 2010is powerful!
Microsoft Office 2010is the best software in the world.
Microsoft wordis so great!
Office 2007makes life great!
Many people likeMicrosoft Office.
Microsoft Office 2007is welcomed by the whole world.
Office 2007 keyis available here.
Office 2007 downloadis on sale now!
Office 2007 Professionalbring me so much convenience.
Outlook 2010 make life wonderful!
Microsoft outlook is my love.
Microsoft outlook 2010 is convenient!
Windows 7 is convenient!

games unblocked said...

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