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

Sunday, December 2, 2007

Shrink TempDB database

I have been recently worked for some client who has huge tempdb size (SQL Server 2000). As you probably know, you cannot reduce physical size of tempdb without restart MSSQLServices. Using the command DBCC SHRINKFILE to shrink the individual tempdb files you must run DBCC SHRINKFILE command while no other activity occurs in the tempdb database. To make sure that other processes cannot use tempdb while DBCC SHRINKFILE executes, you must restart SQL Server in the single user
mode.
So I would like to show some stepS we ended up. The client completely understands the problem cause tempdb database to be growing and I hope it is not short time solution.

1) Stop SQL Server. Open a command prompt, and then start SQL Server by
typing the following command:

"sqlservr -c -f" (remove the quotation)

The -c and -f parameters force SQL Server to start in a minimum
configuration mode with a tempdb size of 1 MB for the data file and 0.5 MB
for the log file.
Connect to SQL Server with Query Analyzer, and then issue the following
TSQL commands for the tempdb database files that you need to shrink

use tempdb
go

dbcc shrinkfile (tempdev, 'target size in MB') --for the primary data file
or
dbcc shrinkfile (templog, 'target size in MB')--for the log file

As MS says
"An advantage of DBCC SHRINKFILE is that it can reduce the size of a file to
a size smaller than its original size. A limitation of DBCC SHRINKFILE is that you cannot make the database smaller than the size of the model database."