Scheduling DBCC DBReindex

How do I schedule a task like DBCC DBReindex, or DBCC Indexdefrag?
I've got backups scheduled nightly, but don't know how to schedule something like this. I've got 7 tables that need to be reindexed and defragged every Saturday night.
Tips/Help/etc appreciated.
Thanks!
Ysandre
Just create a TSQL job step which includes your DBCC DBREINDEX or DBCC INDEXDEFRAG commands. The commands are
documented in Books Online. You also want to read below excellent article abut defragmentation. :-)
http://www.microsoft.com/technet/pro.../ss2kidbp.mspx
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Ysandre" <ysandre@.hotmail.com> wrote in message news:317B96C9-15E6-46A7-972E-7DA32C7DEB21@.microsoft.com...
> How do I schedule a task like DBCC DBReindex, or DBCC Indexdefrag?
> I've got backups scheduled nightly, but don't know how to schedule something like this. I've got 7 tables
that need to be reindexed and defragged every Saturday night.
> Tips/Help/etc appreciated.
> Thanks!
> Ysandre
|||Thanks for the article.
I know the commands for DBReindex and indexDefrag.
What I don't know is how to schedule it so it kicks off at, say, 9pm tonight, without me having to be here (physically or remotely).
I'm fairly familiar with NT Scheduler, and I have scheduled backups of the SQL database running nightly.
How do I create a job/task/process that kicks off the DBCC DBReindex at a specific time, either daily or weekly?
I'm not familiary with TSQL, and BOL is not giving me what I'm looking for.
Help appreciated!
Thanks . . .
|||How do you schedule the backups? I hope you don't backup just the database files... You should use the TSQL
BACKUP DATABASE and possibly the BACKUP LOG command to do SQL Server backups.
Anyhow, you can use the NT Scheduler and use OSQL.EXE with the /i option to specify a script file containing
the TSQL commands you want to execute.
Most dba's, however, use SQL Server's scheduler for these things: SQL Server Agent. In Enterprise Manager,
Management, SQL Server Agent, Jobs, create a new job, add a TSQL job step which contains your TSQL commands.
To get fancy, I also suggest you specify an output file for the jobsteps, and then add a couple of finishing
job steps to send email on success/failure. Use xp_smtp_sendmail for the email (www.SQLDev.Net) and include
the output file in the email. See http://www.karaszi.com/sqlserver/info_no_mapi.asp.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Ysandre" <anonymous@.discussions.microsoft.com> wrote in message
news:9387A1F6-A6C3-49B3-A939-0E983AEC2256@.microsoft.com...
> Thanks for the article.
> I know the commands for DBReindex and indexDefrag.
> What I don't know is how to schedule it so it kicks off at, say, 9pm tonight, without me having to be here
(physically or remotely).
> I'm fairly familiar with NT Scheduler, and I have scheduled backups of the SQL database running nightly.
> How do I create a job/task/process that kicks off the DBCC DBReindex at a specific time, either daily or
weekly?
> I'm not familiary with TSQL, and BOL is not giving me what I'm looking for.
> Help appreciated!
> Thanks . . .
|||yes this is exactly how I do it.
I just reindex every night in a SQL Agent Job.
the job has the following steps.
1. run indexDefrag on all indexes that I have decided need to be in my
maintenance list (Details would bore you)
2. After the index step has ran (either successfully or unsuccessfully) I
run the full backup
I want to make sure that the two steps above are not running at the same
time....
hope this, along with Tibors post, helps
Greg Jackson
PDX, Oregon
|||Thank you! That (and Tibor's post) were exactly what I needed.