Re: database optimization

David Greenberg (davidgr@xxxxxxxxxx) 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@xxxxxxxxxxxxx

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

Relevant Pages

  • Re: Massive Log Files
    ... command with the TRUNCATE_ONLY option, you can find more info in books ... Co-Author SQL Server 2000 Programming by Example ... > We have a problem with huge log files for our SQL Server databases. ... > We've been digging through Books Online and trying various esoteric ...
    (microsoft.public.sqlserver.tools)
  • Re: drop a database user. assign objects to dbo.
    ... You can find objects that are not owned by dbo with this select: ... You can use this command ... Erland Sommarskog, SQL Server MVP, esquel@xxxxxxxxxxxxx ... Books Online for SQL Server 2005 at ...
    (microsoft.public.sqlserver.security)
  • Re: On to Bulk Insert issues
    ... in what situations does the sql server 2000 return with such an error? ... there is an error with your delimiters, so that BCP gets out of sync. ... post the CREATE TABLE command for the table and the ... Books Online for SQL Server 2005 at ...
    (comp.databases.ms-sqlserver)
  • Re: 100 % CPU Usage
    ... > the sp_who2 command. ... there is a lot of CPU on the Lazy Writer given the login time. ... If this indeed is a bug in SQL Server, ... Books Online for SQL Server 2005 at ...
    (comp.databases.ms-sqlserver)
  • FYI: Delete still shows the deleted record
    ... "kit" wrote: ... new SQL Server DB and I'm borrowing code from the previous SQL Server DB ... Put this in the click event of your "DELETE" command button: ... Public Sub DelCurrentRec ...
    (microsoft.public.access.forms)