Results 1 to 9 of 9

Thread: Dbcc

  1. #1
    Join Date
    Dec 2002
    Location
    Brazil
    Posts
    59

    Unanswered: Dbcc

    Does someone has a dbcc code that rebuild the indexes of my entire database?
    Nadia

  2. #2
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Check out Tara's Blog
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  3. #3
    Join Date
    Feb 2003
    Location
    @ home
    Posts
    163
    You may have/use ONE of the options:

    Create index...with drop existing

    or

    DBCC DBREINDEX
    (to rebuild all indexes)

    Paulo

  4. #4
    Join Date
    Dec 2002
    Location
    Brazil
    Posts
    59
    Yes, but I want a procedure wich executes dbcc dbreindex in all indexes in all tables of my database. Dbcc dbreindex runs per table....

    Do you have any?

    I don't know if I can trust in Tara's procedure!!!!!!!
    Nadia

  5. #5
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    I swear...you can lead a horse to water....

    Why can't you "trust" Tara's proc?
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  6. #6
    Join Date
    Dec 2002
    Location
    Brazil
    Posts
    59
    The point is: how can I know if her procedure really rebuilds all indexes in my database?
    I read that her previous procedure had an error: it only rebuilds one index per table, you know?

    So, I am a litle bit afraid it may not work properly in all my indexes... and I am very bad in procedures codes...
    Nadia

  7. #7
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Well I would say that you need to address the need to rebuild them all...

    It depends on your activity...

    As far as Tar's stuff...a lot of eyes have looked at it by now....

    Do you check to see if they need it?
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  8. #8
    Join Date
    Jul 2003
    Location
    The Dark Planet
    Posts
    1,401
    Well .. thats a first ... not trusting Tara's code ...

    Did you point her to this thread yet .. Brett ???
    Get yourself a copy of the The Holy Book

    order has no physical Brett in The meaning of a Kaiser . -database data

  9. #9
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    You can use this code as a starting point. It does INDEX_DEFRAG on all tables/indexes within a database, but you can easily modify it to suit your needs:

    Code:
    @echo off
    set server=%1
    set db=%2
    set uid=-U%3
    set pwd=-P%4
    if "%1"=="" goto ErrorTrap
    if "%2"=="" set db=master
    if "%3"=="" set uid=-E
    if "%4"=="" set pwd=
    echo Checking for existence of a view on server %server% database %db%...
    osql -S %server% %uid% %pwd% -d %db% -l 1 -Q"if object_id('dbo.vw_DBCC_INDEX_DEFRAG') is not null drop view dbo.vw_DBCC_INDEX_DEFRAG"
    if errorlevel 1 goto LoginFailure
    echo Creating a view on server %server% database %db%...
    osql -S %server% %uid% %pwd% -d %db% -Q"create view dbo.vw_DBCC_INDEX_DEFRAG as select cmd='dbcc indexdefrag (' + db_name() + ', [' + o.name + '], ' + i.name + ') with no_infomsgs' from sysindexes i inner join sysobjects o on i.id=o.id where objectproperty(o.id, 'IsMSShipped') = 0 and objectproperty(o.id, 'IsTable') = 1 and indid > 0 and indid < 255 and (i.status & 64)=0"
    rem osql -S %server% %uid% %pwd% -d %db% -Q"create view dbo.vw_DBCC_INDEX_DEFRAG as select cmd='dbcc indexdefrag (' + db_name() + ', ' + o.name + ', ' + i.name + ')' from sysindexes i inner join sysobjects o on i.id=o.id where objectproperty(o.id, 'IsMSShipped') = 0 and objectproperty(o.id, 'IsTable') = 1 and indid > 0 and indid < 255 and (i.status & 64)=0"
    if "%uid%"=="-E" set uid=-T
    echo Generating the final script for server %server% database %db%...
    bcp %db%.dbo.vw_DBCC_INDEX_DEFRAG out DBCC_INDEX_DEFRAG.SQL -S %server% %uid% %pwd% -c
    if "%uid%"=="-T" set uid=-E
    echo Processing INDEXDEFRAG script on %server% database %db%...
    osql -S %server% %uid% %pwd% -d %db% -i DBCC_INDEX_DEFRAG.SQL -h-1 -n -w 256 -o %db%_DBCC_INDEX_DEFRAG.LOG
    echo Check %db%_DBCC_INDEX_DEFRAG.LOG for any errors!
    goto end
    :ErrorTrap
    echo No server and/or database specified!
    echo Execution returned Error Code %ERRORLEVEL%
    goto end
    :LoginFailure
    echo Failed to login to %server%!
    echo Execution returned Error Code %ERRORLEVEL%
    :end
    @echo on
    "The data in a record depends on the Key to the record, the Whole Key, and
    nothing but the Key, so help me Codd."

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •