database optimization

Hi
We use Sql 2000.
When defining a database maintenance plan one of the options is to
optimize the database, reorganize data and index pages, remove unused
pages etc.
Is there a command to do this manualy (or in a job) and not by usung a
DBM plan ?

Re: database optimization

"David Greenberg" wrote in message
news:flaupm$35v$1@news2.netvision.net.il...
> Hi
> We use Sql 2000.
> When defining a database maintenance plan one of the options is to
> optimize the database, reorganize data and index pages, remove unused
> pages etc.
> Is there a command to do this manualy (or in a job) and not by usung a DBM
> plan ?

Yes. ALL of the features of Maintenance Plans are T-SQL commands.

http://msdn2.microsoft.com/en-us/library/aa258828(SQL.80).as px

--
David Portas

Re: database optimization

David Portas wrote:
> "David Greenberg" wrote in message
> news:flaupm$35v$1@news2.netvision.net.il...
>
>>Hi
>>We use Sql 2000.
>>When defining a database maintenance plan one of the options is to
>>optimize the database, reorganize data and index pages, remove unused
>>pages etc.
>>Is there a command to do this manualy (or in a job) and not by usung a DBM
>>plan ?
>
>
> Yes. ALL of the features of Maintenance Plans are T-SQL commands.
>
> http://msdn2.microsoft.com/en-us/library/aa258828(SQL.80).as px
>

Thanks for the link. Problem is that the "dbcc reindex" works on a
single table , meaning that I have to write a command per table and have
to remember to add a new line for new tables when I add them.
Isn't there a single command that will take care of the whole database
in one go ?

Re: database optimization

David Greenberg (davidgr@iba.org.il) writes:
> Thanks for the link. Problem is that the "dbcc reindex" works on a
> single table , meaning that I have to write a command per table and have
> to remember to add a new line for new tables when I add them.
> Isn't there a single command that will take care of the whole database
> in one go ?

You can easily write a script that iterates over sysobjects to do this.
In this case you can also run DBCC SHOWCONTIG WITH TABLE_RESULTS and
check for fragmentation, so that you only defragment tables that have a
certain fragmentation level.

If you don't like writing code, you can also do:

sp_MSforeachdb 'DBCC DBREINDEX(?)'

The stored procedure sp_MSforeachdb is undocumented and use of it is not
supported. Then again, it's commonly used.


--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downlo ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books .mspx

Re: database optimization

You can use the undocumented system stored proc called



Sp_msforeachtable



The parameter is the command you want to run and the proc will execute it
against every table within the database


--
Sincerely,
John K
Knowledgy Consulting, LLC
www.knowledgy.org

Atlanta's Business Intelligence and Knowledge Management Experts


"David Greenberg" wrote in message
news:flaupm$35v$1@news2.netvision.net.il...
> Hi
> We use Sql 2000.
> When defining a database maintenance plan one of the options is to
> optimize the database, reorganize data and index pages, remove unused
> pages etc.
> Is there a command to do this manualy (or in a job) and not by usung a DBM
> plan ?

Re: database optimization

Check SQL Books Online and search DBCC SHOWCONTIG - there is a script in
there which is useful and deals
with all the tables

--

Jack Vamvas
___________________________________
Search IT jobs from multiple sources- http://www.ITjobfeed.com




"David Greenberg" wrote in message
news:flaupm$35v$1@news2.netvision.net.il...
> Hi
> We use Sql 2000.
> When defining a database maintenance plan one of the options is to
> optimize the database, reorganize data and index pages, remove unused
> pages etc.
> Is there a command to do this manualy (or in a job) and not by usung a DBM
> plan ?