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!
YsandreJust create a TSQL job step which includes your DBCC DBREINDEX or DBCC INDEX
DEFRAG commands. The commands are
documented in Books Online. You also want to read below excellent article ab
ut defragmentation. :-)
http://www.microsoft.com/technet/pr...n/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@.m
icrosoft.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 S
QL database running nightly.
How do I create a job/task/process that kicks off the DBCC DBReindex at a sp
ecific 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 f
iles... 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 wh
ich contains your TSQL commands.
To get fancy, I also suggest you specify an output file for the jobsteps, an
d then add a couple of finishing
job steps to send email on success/failure. Use xp_smtp_sendmail for the email (w.SQLDev.Net" target="_blank">ww
w.SQLDev.Net) and include
the output file in the email. See p." target="_blank">http://www.karaszi.com/sqlserver/in...pi.as
p.
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, with
out 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.

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.

schedule dbcc indexdefrag every Saturday?

How can I set up SQL Server to defragment all indexes on a wly basis, e.g
.
every saturday?Schedule through SQL Agent job(s).
You can iterate through the indexes in the system catalog tables/views and
loop through to fire off the defrag, if you do not want to set it up
individuallly.
Gregory A. Beamer
MVP; MCP: +I, SE, SD, DBA
***************************
Think Outside the Box!
***************************
"Mike" wrote:

> How can I set up SQL Server to defragment all indexes on a wly basis, e
.g.
> every saturday?|||Or use the code already written for us, which also doesn't defrag if the ind
ex isn't fragmented in
the first place. Code found in Books Online, DBCC SHOWCONTIG.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Cowboy (Gregory A. Beamer) - MVP" <NoSpamMgbworld@.comcast.netNoSpamM> wrote
in message
news:9B1D603C-F198-4977-A6DC-4203CC13AA5E@.microsoft.com...
> Schedule through SQL Agent job(s).
> You can iterate through the indexes in the system catalog tables/views and
> loop through to fire off the defrag, if you do not want to set it up
> individuallly.
> --
> Gregory A. Beamer
> MVP; MCP: +I, SE, SD, DBA
> ***************************
> Think Outside the Box!
> ***************************
>
> "Mike" wrote:
>|||There you go encouraging the use of cursor based progarmming. ;-)
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:uu$Y1qujFHA.3316@.TK2MSFTNGP14.phx.gbl...
> Or use the code already written for us, which also doesn't defrag if the
> index isn't fragmented in the first place. Code found in Books Online,
> DBCC SHOWCONTIG.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "Cowboy (Gregory A. Beamer) - MVP" <NoSpamMgbworld@.comcast.netNoSpamM>
> wrote in message
> news:9B1D603C-F198-4977-A6DC-4203CC13AA5E@.microsoft.com...
>|||LOL. Do you have a set based approach? ;-)
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"JT" <someone@.microsoft.com> wrote in message news:ubRC11vjFHA.3336@.tk2msftngp13.phx.gbl...

> There you go encouraging the use of cursor based progarmming. ;-)
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote i
n
> message news:uu$Y1qujFHA.3316@.TK2MSFTNGP14.phx.gbl...
>|||Below is an example of how some people iterate through a temporary table. It
doesn't run any faster, but at least they have the satisfaction of knowing
they didn't use a cursor. I was once on a project where the lead developer
actually wanted to re-write avoer 20 cursor based stored procedures like so.
Fortunately, management pulled the plug on the project, and I was able to
move on to more meaningful work.
CREATE #temptable
(
id int NOT NULL IDENTITY (1, 1),
. . .
)
. . .
select @.id = select min(id) from #temptable
select @.MAXID = select max(id) from #temptable
WHILE @.id <= @.MAXID
BEGIN
. . .
select @.id = @.id + 1
END
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:%23ARjZ63jFHA.1504@.TK2MSFTNGP10.phx.gbl...
> LOL. Do you have a set based approach? ;-)
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "JT" <someone@.microsoft.com> wrote in message
> news:ubRC11vjFHA.3336@.tk2msftngp13.phx.gbl...|||Yes, I have used that technique in version 4.2 (or was it 1.1?), before serv
er side cursors was
introduced. I think the term cursors is misinterpreted/misused, hence the ex
ample you mention
("re-do these cursors to another procedural technique"). I'm glad you didn't
have to do that
conversion... Personally, I prefer a cursor to the looping of temp table app
roach, I find cursor
code more readable. ... In cases like maint scripts, for instance.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"JT" <someone@.microsoft.com> wrote in message news:uLNefYRkFHA.1204@.TK2MSFTNGP12.phx.gbl...

> Below is an example of how some people iterate through a temporary table.
It doesn't run any
> faster, but at least they have the satisfaction of knowing they didn't use
a cursor. I was once on
> a project where the lead developer actually wanted to re-write avoer 20 cu
rsor based stored
> procedures like so. Fortunately, management pulled the plug on the project
, and I was able to move
> on to more meaningful work.
> CREATE #temptable
> (
> id int NOT NULL IDENTITY (1, 1),
> . . .
> )
> . . .
> select @.id = select min(id) from #temptable
> select @.MAXID = select max(id) from #temptable
> WHILE @.id <= @.MAXID
> BEGIN
> . . .
> select @.id = @.id + 1
> END
>
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote i
n message
> news:%23ARjZ63jFHA.1504@.TK2MSFTNGP10.phx.gbl...
>|||Yes, I agree on all points. The cursor is just SQL Server's standardized
implementation of looping through a temporary resultset. The only time I use
cursors is for excuting a procdure against a small number of rows, and the
cursor at least makes things more maintainable. By getting familiar with the
various cursor options (like FAST_FORWORD argument) and using common sense,
a developer can use cursors without it becomming a bottleneck.
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:uv2k3lRkFHA.3148@.TK2MSFTNGP09.phx.gbl...
> Yes, I have used that technique in version 4.2 (or was it 1.1?), before
> server side cursors was introduced. I think the term cursors is
> misinterpreted/misused, hence the example you mention ("re-do these
> cursors to another procedural technique"). I'm glad you didn't have to do
> that conversion... Personally, I prefer a cursor to the looping of temp
> table approach, I find cursor code more readable. ... In cases like maint
> scripts, for instance.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "JT" <someone@.microsoft.com> wrote in message
> news:uLNefYRkFHA.1204@.TK2MSFTNGP12.phx.gbl...
>