SQL 2K Defrag Utility Recomendation?

19 Dec 2008 4:46 PM

Bishop

I manually run defrag utilities on my SQL 2K server using Query Analiser. I
wanted to find out any reasons I wouldn't want to purchase 3'rd party
software to do this for me. I also wanted any recomendations on which ones
to use.

19 Dec 2008 8:23 PM

Tibor Karaszi

Are you referring to file system fragmentation or index fragmentation?
If the later, why would you need a utility. You find code in Books
Online to check fraglevel for each index and only defrag the
fragmented indexes (DBCC SHOWCONTIG). OR use the Maint Plan of you
want to do all, regardless of current frag level.

--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi

Show quoteHide quote

"Bishop" <nospam@nospam.com> wrote in message
news:096D39E3-F07C-4803-8CC8-984F7E28BC57@microsoft.com...
>I manually run defrag utilities on my SQL 2K server using Query
>Analiser. I wanted to find out any reasons I wouldn't want to
>purchase 3'rd party software to do this for me. I also wanted any
>recomendations on which ones to use.

19 Dec 2008 9:02 PM

Bishop

Index fragmentation. I don't need a utility but the ability to set
thresholds and schedule it to run during off hours (when I'm sleeping) would
be nice.

Show quoteHide quote

"Tibor Karaszi" <tibor_please.no.email_kara***@hotmail.nomail.com> wrote in
message news:%23Qv6XehYJHA.556@TK2MSFTNGP06.phx.gbl...
> Are you referring to file system fragmentation or index fragmentation? If
> the later, why would you need a utility. You find code in Books Online to
> check fraglevel for each index and only defrag the fragmented indexes
> (DBCC SHOWCONTIG). OR use the Maint Plan of you want to do all, regardless
> of current frag level.
>
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://sqlblog.com/blogs/tibor_karaszi
>
>
> "Bishop" <nospam@nospam.com> wrote in message
> news:096D39E3-F07C-4803-8CC8-984F7E28BC57@microsoft.com...
>>I manually run defrag utilities on my SQL 2K server using Query Analiser.
>>I wanted to find out any reasons I wouldn't want to purchase 3'rd party
>>software to do this for me. I also wanted any recomendations on which
>>ones to use.
>
>

20 Dec 2008 2:56 AM

Andrew J. Kelly

You can use SQL Agent to schedule any type of task you wish. Have a look in
BooksOnLine under DBCC SHOWCONTIG for a script that lets you et thresholds
on fragmentation to be addressed.

--
Andrew J. Kelly SQL MVP
Solid Quality Mentors

Show quoteHide quote

"Bishop" <nospam@nospam.com> wrote in message
news:OdRSf0hYJHA.5020@TK2MSFTNGP03.phx.gbl...
> Index fragmentation. I don't need a utility but the ability to set
> thresholds and schedule it to run during off hours (when I'm sleeping)
> would be nice.
>
>
> "Tibor Karaszi" <tibor_please.no.email_kara***@hotmail.nomail.com> wrote
> in message news:%23Qv6XehYJHA.556@TK2MSFTNGP06.phx.gbl...
>> Are you referring to file system fragmentation or index fragmentation? If
>> the later, why would you need a utility. You find code in Books Online to
>> check fraglevel for each index and only defrag the fragmented indexes
>> (DBCC SHOWCONTIG). OR use the Maint Plan of you want to do all,
>> regardless of current frag level.
>>
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://sqlblog.com/blogs/tibor_karaszi
>>
>>
>> "Bishop" <nospam@nospam.com> wrote in message
>> news:096D39E3-F07C-4803-8CC8-984F7E28BC57@microsoft.com...
>>>I manually run defrag utilities on my SQL 2K server using Query Analiser.
>>>I wanted to find out any reasons I wouldn't want to purchase 3'rd party
>>>software to do this for me. I also wanted any recomendations on which
>>>ones to use.
>>
>>
>

21 Dec 2008 5:32 PM

NB

Here is a good article (with scripts) on defragging only the necessary indexes:
http://blog.sqlauthority.com/2008/03/04/sql-server-2005-a-simple-way-to-defragment-all-indexes-in-a-database-that-is-fragmented-above-a-declared-threshold/

Show quoteHide quote

"Andrew J. Kelly" wrote:

> You can use SQL Agent to schedule any type of task you wish. Have a look in
> BooksOnLine under DBCC SHOWCONTIG for a script that lets you et thresholds
> on fragmentation to be addressed.
>
> --
> Andrew J. Kelly SQL MVP
> Solid Quality Mentors
>
>
> "Bishop" <nospam@nospam.com> wrote in message
> news:OdRSf0hYJHA.5020@TK2MSFTNGP03.phx.gbl...
> > Index fragmentation. I don't need a utility but the ability to set
> > thresholds and schedule it to run during off hours (when I'm sleeping)
> > would be nice.
> >
> >
> > "Tibor Karaszi" <tibor_please.no.email_kara***@hotmail.nomail.com> wrote
> > in message news:%23Qv6XehYJHA.556@TK2MSFTNGP06.phx.gbl...
> >> Are you referring to file system fragmentation or index fragmentation? If
> >> the later, why would you need a utility. You find code in Books Online to
> >> check fraglevel for each index and only defrag the fragmented indexes
> >> (DBCC SHOWCONTIG). OR use the Maint Plan of you want to do all,
> >> regardless of current frag level.
> >>
> >> --
> >> Tibor Karaszi, SQL Server MVP
> >> http://www.karaszi.com/sqlserver/default.asp
> >> http://sqlblog.com/blogs/tibor_karaszi
> >>
> >>
> >> "Bishop" <nospam@nospam.com> wrote in message
> >> news:096D39E3-F07C-4803-8CC8-984F7E28BC57@microsoft.com...
> >>>I manually run defrag utilities on my SQL 2K server using Query Analiser.
> >>>I wanted to find out any reasons I wouldn't want to purchase 3'rd party
> >>>software to do this for me. I also wanted any recomendations on which
> >>>ones to use.
> >>
> >>
> >
>
>

21 Dec 2008 8:33 PM

John Bell

"Bishop" <nospam@nospam.com> wrote in message
news:OdRSf0hYJHA.5020@TK2MSFTNGP03.phx.gbl...
> Index fragmentation. I don't need a utility but the ability to set
> thresholds and schedule it to run during off hours (when I'm sleeping)
> would be nice.
>
>

Along with Andrews suggestion, there is the options to create a maintenance
plan and schedule it, although that is a bit of a black box/

Any script you have, may be converted into a SQL Agent job step and if it is
too long made into a stored procedure called as a job step.

John