SQL Server Performance

Automation of dbcc dbreindex()

Discussion in 'Contribute Your SQL Server Scripts' started by thomas, Mar 5, 2003.

  1. thomas New Member

    This stored procedure automates dbcc dbreindex() from defragmenting indexes in a database. It could also be used for dbcc indexdefrag() with simple modification. This SP is based on sample code from Books Online, which I have taken, made some amendments to and formed into a stored procedure.<br /><br />You should create this in the master database. Then to run it, you simply run it in the database you wish to defragment. You pass the SP the threshold of scan density below which you wish to run dbcc dbreindex(). For example, if you wish to run it in a database called TESTDB and you want to defragment all indexes with a scan density below 80%, do the following:- <br /><br /><font face="Courier New">USE TESTDB <br />GO <br />exec sp_defragment_indexes 80.00 </font id="Courier New"><br /><br />The output will show you which indexes it is defragmenting, and show you how fragmented the indexes are. <br /><br />If you run this as a scheduled task, you can set the job to output to text file, then you can review what the SP has done each time it's run.<br /><br /><br /><br />-- SP code starts here<br /><br /><font face="Courier New">CREATE PROCEDURE sp_defragment_indexes @maxfrag DECIMAL<br /><br />AS<br /><br />/* T.Pullen 29.11.2001<br /><br />This stored procedure checks index fragmentation in a database and defragments<br />indexes whose scan densities fall below a specified threshold, @magfrag, which<br />is passed to the SP.<br /><br />Must be run in the database to be defragmented.<br />*/<br />-- Declare variables<br /><br />SET NOCOUNT ON<br />DECLARE @tablename VARCHAR (12<img src='/community/emoticons/emotion-11.gif' alt='8)' /><br />DECLARE @execstr VARCHAR (255)<br />DECLARE @objectid INT<br />DECLARE @indexid INT<br />DECLARE @frag DECIMAL<br />DECLARE @indexname CHAR(255)<br />DECLARE @dbname sysname<br /><br />SELECT @dbname = db_name()<br />IF @dbname IN ('master', 'msdb', 'model', 'tempdb')<br />BEGIN<br />PRINT 'This procedure should not be run in system databases.'<br />RETURN<br />END<br /><br />-- Declare cursor<br />DECLARE tables CURSOR FOR<br /> SELECT so.name<br /> FROM sysobjects so<br /> JOIN sysindexes si<br /> ON so.id = si.id<br /> WHERE so.type ='U'<br /> AND si.indid &lt; 2<br /> AND si.rows &gt; 0<br /><br /><br />-- Create the table<br />CREATE TABLE #fraglist (<br /> ObjectName CHAR (255),<br /> ObjectId INT,<br /> IndexName CHAR (255),<br /> IndexId INT,<br /> Lvl INT,<br /> CountPages INT,<br /> CountRows INT,<br /> MinRecSize INT,<br /> MaxRecSize INT,<br /> AvgRecSize INT,<br /> ForRecCount INT,<br /> Extents INT,<br /> ExtentSwitches INT,<br /> AvgFreeBytes INT,<br /> AvgPageDensity INT,<br /> ScanDensity DECIMAL,<br /> BestCount INT,<br /> ActualCount INT,<br /> LogicalFrag DECIMAL,<br /> ExtentFrag DECIMAL)<br /><br />-- Open the cursor<br />OPEN tables<br /><br />-- Loop through all the tables in the database<br />FETCH NEXT<br /> FROM tables<br /> INTO @tablename<br /><br />WHILE @@FETCH_STATUS = 0<br />BEGIN<br />-- Do the showcontig of all indexes of the table<br /> INSERT INTO #fraglist <br /> EXEC ('DBCC SHOWCONTIG (''' + @tablename + ''') <br /> WITH FAST, TABLERESULTS, ALL_INDEXES, NO_INFOMSGS')<br /> FETCH NEXT<br /> FROM tables<br /> INTO @tablename<br />END<br /><br />-- Close and deallocate the cursor<br />CLOSE tables<br />DEALLOCATE tables<br /><br />SELECT * FROM #fraglist<br /><br />-- Declare cursor for list of indexes to be defragged<br />DECLARE indexes CURSOR FOR<br /> SELECT ObjectName, ObjectId, IndexName, ScanDensity<br /> FROM #fraglist<br /> WHERE ScanDensity &lt;= @maxfrag<br /> AND INDEXPROPERTY (ObjectId, IndexName, 'IndexDepth') &gt; 0<br /><br />SELECT 'Started defragmenting indexes at ' + CONVERT(VARCHAR,GETDATE())<br /><br />-- Open the cursor<br />OPEN indexes<br /><br />-- loop through the indexes<br />FETCH NEXT<br /> FROM indexes<br /> INTO @tablename, @objectid, @indexname, @frag<br /><br />WHILE @@FETCH_STATUS = 0<br />BEGIN<br /> SELECT @execstr = 'DBCC DBREINDEX (' + RTRIM(@tablename) + ',<br /> ' + RTRIM(@indexname) + ') WITH NO_INFOMSGS'<br /> SELECT (@execstr)<br /> EXEC (@execstr)<br /><br /> FETCH NEXT<br /> FROM indexes<br /> INTO @tablename, @objectid, @indexname, @frag<br />END<br /><br />-- Close and deallocate the cursor<br />CLOSE indexes<br />DEALLOCATE indexes<br /><br />SELECT 'Finished defragmenting indexes at ' + CONVERT(VARCHAR,GETDATE())<br /><br />-- Delete the temporary table<br />DROP TABLE #fraglist<br /><br /><br />GO</font id="Courier New"><br /><br />Tom Pullen<br />DBA, Oxfam GB
  2. bambola New Member

    Thanks Tom for this script. I use parts of it to improve my procedure that rebuilds <br />indexes. <img src='/community/emoticons/emotion-1.gif' alt=':)' /><br /><br />Bambola.<br />
  3. thomas New Member

    Yep - it's the WITH TABLERESULTS of DBCC SHOWCONTIG that make all the difference, make it so much easier to do this conditionally.

    My corresponding 6.5 script is a mess - you have to keep xp_cmdshelling out to read text file output!

    Tom Pullen
    DBA, Oxfam GB
  4. dcohn New Member

    We want to use your stored procedure to reindex database but we are a bit confused by the output.

    We have about 35 indexes that show up in the grid with all the statistics. What we are confused about are these last three entries. Why does it show the PxUser, PK_User index??? Is that the only one it reindexed???


    To verify I only included the bottom of the results as the first part of the results did show all my indexes. I did this on a test database only before running it on a live database.

    Thanks

    Doug

    ---------------------------------------------------------------
    Started defragmenting indexes at Oct 16 2003 3:21PM


    ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
    DBCC DBREINDEX (PxUser,
    PK_User) WITH NO_INFOMSGS


    ----------------------------------------------------------------
    Finished defragmenting indexes at Oct 16 2003 3:21PM
  5. Luis Martin Moderator

    Late but not last, thank Tom for this scrip.



    Luis Martin

    ...Thus mathematics may be defined as the subject in which we never know what we are talking about, nor whether what we are saying is true.
    Bertrand Russell
  6. ykchakri New Member

  7. Luis Martin Moderator

    I was using Tom, Chakry and Gaurav (via web) scripts.(particulary I like Tom's scrip)<br />All woks find in SQL 2000.<br />I need one for SQL7, since I'm not a devopment, can someone help me?<br />Thank in advance to Chappy, Bambola, Twan, Tom, Chakry and Vbkenya.[<img src='/community/emoticons/emotion-1.gif' alt=':)' />]<br /><br /><br />Luis Martin<br /><br />...Thus mathematics may be defined as the subject in which we never know what we are talking about, nor whether what we are saying is true. <br />Bertrand Russell
  8. bambola New Member

    You should be able to use the procedure above with sql7. it is using DBCC DBREINDEX and not INDEXDEFRAG (which was only introduces in sql 2000), and it is not using table datatype or anything else I can see to prevent you from using it. Or am I missing something...

    Bambola.
  9. thomas New Member

    I don't think my script will work in SQL Server 7.0, because it uses DBCC SHOWCONTIG()WITH TABLERESULTS which was introduced in 2000.

    This makes a big difference, because without it, you are reduced to outputting to text file, and the re-reading the results back in from text file, using xp_cmdshell for example, which is cumbersome.

    I do not have an equivalent for 7.0, but I do have something for 6.5 which automates the SHOWCONTIG part, but not the REINDEX - but it could easily be adapted to do so. Let me know if you would like me to post that.

    Tom Pullen
    DBA, Oxfam GB
  10. thomas New Member

    In reply to dcohn - yes, that does mean it has only reindexed your PK index. This procedure will only reindex indexes which are fragmented - it will not reindex entire tables if that is unnecessary.

    Remember that if the index being defragged is the clustered index, the entire table will be reindexed along with it, because clustered indexes contain the data, and if they are rebuilt, all nonclustered indexes will be rebuilt too.

    Tom Pullen
    DBA, Oxfam GB
  11. bambola New Member

    > I don't think my script will work in SQL Server 7.0, because it uses DBCC SHOWCONTIG()WITH TABLERESULTS which was introduced in 2000.

    I didn't know that...

    Bambola.
  12. Luis Martin Moderator

    Bambola:

    Even more, SQL2000 can manage DBCC SHOWCONTIG ('TABLE-NAME) but SQL7 need
    DBCC SHOWCONTIG (Table ID), same thing with index.
    BTW SQL don´t manage any other like FAST, and so on.
    Sintax SQL: DBCC SHOWCONTI (TableID, IndexID).

    Tom:

    Yes Tom, please.



    Luis Martin

    ...Thus mathematics may be defined as the subject in which we never know what we are talking about, nor whether what we are saying is true.
    Bertrand Russell
  13. thomas New Member

    Okay, here it is. I must admit that this is shamelessly stolen from someone else who wrote it originally, it was a woman, but I can't remember who she was or where I got it from .. so if she's reading this, which I hope she isn't, I'm very sorry.

    --sp code starts here

    if exists (select * from sysobjects where id = object_id('dbo.sp_get_fragmentation') and sysstat & 0xf = 4)
    drop procedure dbo.sp_get_fragmentation
    GO

    create procedure sp_get_fragmentation @login_name varchar(30) = NULL, @pwd varchar(30) = NULL,
    @dbname varchar(30), @target decimal(5,2) = 85.00,@file varchar(255) = 'c: emp',
    @rebuild_ind char(1) = 'N'

    as

    /* sp_get_fragmentation
    T.Pullen 28.8.2001
    This SP reports all index fragmentation and optionally reindexes tables below a certain threshold. Suitable for SQL Server 6.5 & potentially 7.0.
    */

    begin

    set nocount on

    declare @obj_name sysname
    declare @idint
    declare @indidsmallint
    declare @ind_namesysname
    declare @ownersysname
    declare @where_clausevarchar(255)
    declare @cmdvarchar(255)
    declare @scan_dnstydecimal(5,2)
    declare @rowcntint


    if @dbname = 'master' or @dbname = 'model' -- or @dbname = 'pubs'
    or @dbname = 'tempdb' or @dbname = 'distribution' or @dbname = 'msdb'
    return 1

    exec('declare obj_cursor cursor for
    select o.name, o.id, i.indid, i.name, u.name
    from ' + @dbname + '.dbo.sysobjects o, ' + @dbname + '.dbo.sysindexes i, ' + @dbname + '.dbo.sysusers u
    where o.id = i.id and i.id > 99 and i.rows > 0
    and o.uid = u.uid order by o.name FOR READ ONLY')

    create table #iw_report
    (dbname varchar(30) not null,
    obj_name varchar(30) not null,
    idint not null,
    indidsmallint not null,
    ind_name varchar(30) not null,
    ownervarchar(30) not null,
    density decimal(5,2) null)

    create table #iw_density
    (density_str varchar(255) null)


    open obj_cursor

    fetch next from obj_cursor into @obj_name, @id, @indid, @ind_name, @owner



    while @@fetch_status = 0
    begin
    -- use trusted connection if password and login name are NULL
    if @pwd is NULL and @login_name is NULL
    select @cmd = 'isql /Q"DBCC SHOWCONTIG(' + convert(varchar(30),@id) + ',' + convert(varchar(5),@indid)
    + ')" /E /d' + @dbname +
    ' /o ' + @file + 'showcontig.out'
    else
    select @cmd = 'isql /Q"DBCC SHOWCONTIG(' + convert(varchar(30),@id) + ',' + convert(varchar(5),@indid)
    + ')" /U' + @login_name + ' /P' + @pwd + ' /d' + @dbname +
    ' /o ' + @file + 'showcontig.out'

    execute master..xp_cmdshell @cmd

    select @cmd = 'findstr /c:"Scan Density" ' +
    @file + 'showcontig.out'


    truncate table #iw_density


    insert into #iw_density(density_str)
    exec master..xp_cmdshell @cmd



    select @scan_dnsty = convert(decimal(5,2),RTRIM(substring(density_str, charindex('%',density_str) - 6, 6)))
    from #iw_density

    -- select @scan_dnsty

    if @scan_dnsty < @target
    insert into #iw_report (dbname, obj_name, id, indid, ind_name, owner, density)
    values(@dbname, @obj_name, @id, @indid, @ind_name, @owner, @scan_dnsty)



    fetch next from obj_cursor into @obj_name, @id, @indid, @ind_name, @owner
    end

    close obj_cursor
    deallocate obj_cursor

    -- delete output file
    select @cmd = 'if exist ' + @file + 'showcontig.out del ' + @file + 'showcontig.out'
    execute master..xp_cmdshell @cmd

    select @rowcnt = count(*) from #iw_report

    if @rowcnt > 0
    begin
    print ' '
    print 'Fragmented tables:'
    print @dbname
    select * from #iw_report
    print ' '

    -- rebuild indexes
    if @rebuild_ind = 'Y'
    begin
    declare rebuild_cur cursor for
    select distinct obj_name, id, owner
    from #iw_report

    open rebuild_cur

    fetch next from rebuild_cur into @obj_name, @id, @owner

    while @@fetch_status = 0
    begin
    -- make sure that table has at least one index

    select @where_clause = 'where id = ' + convert(varchar(15),@id)

    exec ('use ' + @dbname + ' if exists (select * from sysindexes ' + @where_clause +
    ' and indid > 0 ) DBCC DBREINDEX(''' + @dbname + '.' + @owner + '.'+ @obj_name + ''') WITH NO_INFOMSGS')


    fetch next from rebuild_cur into @obj_name, @id, @owner
    end
    close rebuild_cur
    deallocate rebuild_cur
    end


    end
    else
    select @dbname + ' - No fragmented tables have been found'


    end

    GO

    --sp code ends here.

    Hope this is useful.

    Tom Pullen
    DBA, Oxfam GB
  14. Luis Martin Moderator

    Tom:

    Thanks very much, it work without problem in SQL7.
    New question now:
    I see the following output in DBCC SHOWCONTIG:

    DBCC SHOWCONTIG scanning 'AW_AFIP_TipoDestinacion' table...
    Table: 'AW_AFIP_TipoDestinacion' (2048778406); index ID: 0, database ID: 6
    TABLE level scan performed.
    - Pages Scanned................................: 2
    - Extents Scanned..............................: 2
    - Extent Switches..............................: 1
    - Avg. Pages per Extent........................: 1.0
    - Scan Density [Best Count:Actual Count].......: 50.00% [1:2]
    - Extent Scan Fragmentation ...................: 50.00%
    - Avg. Bytes Free per Page.....................: 2656.0
    - Avg. Page Density (full).....................: 67.19%
    DBCC execution completed. If DBCC printed error messages, contact your system administrator.

    I gess this is not a Index, is table itself.

    So the table is fragmented, there is something to defragment it or is normal?


    Luis Martin

    ...Thus mathematics may be defined as the subject in which we never know what we are talking about, nor whether what we are saying is true.
    Bertrand Russell
  15. ykchakri New Member

    Yes, if there are no indexes on the table, it shows you the fragmentation of the table. It looks like there are 2 pages in your table and each page is in its own extent. Well, it's not a big problem with a table with 2 pages, but if you still want to see 100%, you may delete and re-create the table <img src='/community/emoticons/emotion-1.gif' alt=':)' />
  16. Luis Martin Moderator

    I confuse in one topic in both scripts (sql2000 y sql7), since I'm not development:

    DBCC RBINDEX is neccesary to be into begin transaction, rollback, etc?
    My question is, if for any razon, server crash when DBCC is running.


    Luis Martin

    ...Thus mathematics may be defined as the subject in which we never know what we are talking about, nor whether what we are saying is true.
    Bertrand Russell
  17. ykchakri New Member

    DBREINDEX on each index runs as an implicit transaction. So, if for some reason the process that started the DBREINDEX fails, then it always rollback. But, if the server itself crashes then there are heavy possibilities of the index getting corrupted.

    INDEXDEFRAG is less prone to corruptions in such instances.
  18. Luis Martin Moderator

    To those people who use Tom SP for SQL 7.0 in spanish must change:

    select @cmd = 'findstr /c:"Scan Density" ' +
    @file + 'showcontig.out'

    to

    select @cmd = 'findstr /c:"Densidad de recorrido" ' +
    @file + 'showcontig.out'

    don't laugh I was working a lot to find out.



    Para aquellos usuarios que usen la SP de Tom para SQL 7.0 en castellano, tiene que cambiar lo siguiente:

    select @cmd = 'findstr /c:"Scan Density" ' +
    @file + 'showcontig.out'

    por

    select @cmd = 'findstr /c:"Densidad de recorrido" ' +
    @file + 'showcontig.out'




    Luis Martin

    ...Thus mathematics may be defined as the subject in which we never know what we are talking about, nor whether what we are saying is true.
    Bertrand Russell
  19. pro New Member

    I am comparitively new to this SQL server. I did not understand the following part of code

    -----

    SELECT so.name
    FROM sysobjects so
    JOIN sysindexes si
    ON so.id = si.id
    WHERE so.type ='U'
    AND si.indid < 2
    AND si.rows > 0
    ------------
    It looks like it is taking all tables with indid=0 and indid=1..
    There may be tables in database that do not have any indexes on them.. So what is the use of running dbcc dbreindex on these tables.
    Following sql query lists the tables that have
    ******************************************************************************
    select distinct(so.name),so.id from sysobjects so,sysindexes si where so.type='u' and so.id in (select distinct(si.id) from sysindexes si,sysobjects so where si.id = so.id and si.indid > 0 and si.indid <> 255 and so.type='u') order by so.name
    ************************************************************************************

    Secondly, what about tables that nonclustered index but no clustered index on them.

    Thanks for your patience and time in answering this novice user.

    Regards,
    PRashanth
  20. Luis Martin Moderator

    Tables without index DBCC do nothing. May be tables has _WA statistics and even in this case DBCC do nothing.

    With tables without cluster but with clusters, DBCC run fine and defragmented those indexs.


    Luis Martin

    ...Thus mathematics may be defined as the subject in which we never know what we are talking about, nor whether what we are saying is true.
    Bertrand Russell
  21. satya Moderator

    True, if you don't have any index then no sight of fragmentation and still to defrag you can simply create a clustered index, reindex and then drop it.

    And DBCC INDEXDEFRAG is introduced in SQL 2000 to defrag such indexes without affecting the performance and db access.

    _________
    Satya SKJ
    Moderator
    SQL-Server-Performance.Com
  22. thomas New Member

    Hi, I have recently come across a 3rd-party app (*sigh*) which uses non-dbo-owned tables and realised that this script does not work for these. I have re-written it so it should work for all tables, regardless of the owner.<br /><br />I hope you find this of some use.<br /><br /><br />--procedure code starts here<br /><br /><br /><font face="Courier New">CREATE PROCEDURE sp_defragment_indexes @maxfrag DECIMAL<br /><br />AS<br /><br />/* T.Pullen 29.11.2001<br /><br />This stored procedure checks index fragmentation in a database and defragments<br />indexes whose scan densities fall below a specified threshold, @magfrag, which<br />is passed to the SP.<br /><br />Must be run in the database to be defragmented.<br /><br />Modification history<br />Changed by T.Pullen 3.3.2004 to cope with non-dbo owned tables<br /><br />*/<br />-- Declare variables<br /><br />SET NOCOUNT ON<br />DECLARE @tablename VARCHAR (12<img src='/community/emoticons/emotion-11.gif' alt='8)' /><br />DECLARE @execstr VARCHAR (255)<br />DECLARE @objectid INT<br />DECLARE @objectowner VARCHAR(255)<br />DECLARE @indexid INT<br />DECLARE @frag DECIMAL<br />DECLARE @indexname CHAR(255)<br />DECLARE @dbname sysname<br />DECLARE @tableid INT<br />DECLARE @tableidchar VARCHAR(255)<br /><br /><br /><br /><br />SELECT @dbname = db_name()<br />IF @dbname IN ('master', 'msdb', 'model', 'tempdb')<br />BEGIN<br />PRINT 'This procedure should not be run in system databases.'<br />RETURN<br />END<br /><br />-- Declare cursor<br />DECLARE tables CURSOR FOR<br /> SELECT so.id<br /> FROM sysobjects so<br /> JOIN sysindexes si<br /> ON so.id = si.id<br /> WHERE so.type ='U'<br /> AND si.indid &lt; 2<br /> AND si.rows &gt; 0<br /><br /><br />-- Create the table<br />CREATE TABLE #fraglist (<br /> ObjectName CHAR (255),<br /> ObjectId INT,<br /> IndexName CHAR (255),<br /> IndexId INT,<br /> Lvl INT,<br /> CountPages INT,<br /> CountRows INT,<br /> MinRecSize INT,<br /> MaxRecSize INT,<br /> AvgRecSize INT,<br /> ForRecCount INT,<br /> Extents INT,<br /> ExtentSwitches INT,<br /> AvgFreeBytes INT,<br /> AvgPageDensity INT,<br /> ScanDensity DECIMAL,<br /> BestCount INT,<br /> ActualCount INT,<br /> LogicalFrag DECIMAL,<br /> ExtentFrag DECIMAL)<br /><br /><br />-- Open the cursor<br />OPEN tables<br /><br />-- Loop through all the tables in the database<br />FETCH NEXT<br /> FROM tables<br /> INTO @tableid<br /><br />WHILE @@FETCH_STATUS = 0<br />BEGIN<br />-- Do the showcontig of all indexes of the table<br /> SELECT @tableidchar = CONVERT(varchar,@tableid)<br /> INSERT INTO #fraglist <br /> EXEC ('DBCC SHOWCONTIG (' + @tableidchar + ') <br /> WITH FAST, TABLERESULTS, ALL_INDEXES, NO_INFOMSGS')<br /> FETCH NEXT<br /> FROM tables<br /> INTO @tableid<br />END<br /><br />-- Close and deallocate the cursor<br />CLOSE tables<br />DEALLOCATE tables<br /><br />SELECT * FROM #fraglist<br /><br />-- Declare cursor for list of indexes to be defragged<br />DECLARE indexes CURSOR FOR<br /> SELECT ObjectName, ObjectOwner = user_name(so.uid), ObjectId, IndexName, ScanDensity<br /> FROM #fraglist f<br /> JOIN sysobjects so ON f.ObjectId=so.id<br /> WHERE ScanDensity &lt;= @maxfrag<br /> AND INDEXPROPERTY (ObjectId, IndexName, 'IndexDepth') &gt; 0<br /><br />SELECT 'Started defragmenting indexes at ' + CONVERT(VARCHAR,GETDATE())<br /><br />-- Open the cursor<br />OPEN indexes<br /><br />-- loop through the indexes<br />FETCH NEXT<br /> FROM indexes<br /> INTO @tablename, @objectowner, @objectid, @indexname, @frag<br /><br />WHILE @@FETCH_STATUS = 0<br />BEGIN<br /> SELECT @execstr = 'DBCC DBREINDEX (' + RTRIM(@objectowner) + '.' + RTRIM(@tablename) + ',<br /> ' + RTRIM(@indexname) + ') WITH NO_INFOMSGS'<br /> EXEC (@execstr)<br /><br /> FETCH NEXT<br /> FROM indexes<br /> INTO @tablename, @objectowner, @objectid, @indexname, @frag<br />END<br /><br />-- Close and deallocate the cursor<br />CLOSE indexes<br />DEALLOCATE indexes<br /><br />SELECT 'Finished defragmenting indexes at ' + CONVERT(VARCHAR,GETDATE())<br /><br />-- Delete the temporary table<br />DROP TABLE #fraglist<br /><br />GO</font id="Courier New"><br /><br />--Procedure code ends here<br /><br />Tom Pullen<br />DBA, Oxfam GB
  23. Luis Martin Moderator

    Tom:
    I try to use the last sp. I have the following problem:
    When sp find a non dba table, job stop and give some message about that table.
    Is there a chance to filter and defrag only dba tables?

    Thanks,

    Luis Martin
    Moderator
    SQL-Server-Performance.com

    The views expressed here are those of the author and no one else. There are no warranties as to the reliability or accuracy of anything presented here.
  24. thomas New Member

    Luis, there isn't at present but I will have a look at if I get a chance and maybe I can change it. I'm sure it'd just be a case of adding a relevant WHERE clause to one of the cursor selects.


    Tom Pullen
    DBA, Oxfam GB
  25. Luis Martin Moderator

    Thanks Tom.


    Luis Martin
    Moderator
    SQL-Server-Performance.com

  26. andyobl New Member

    quote:Originally posted by LuisMartin

    Tom:
    I try to use the last sp. I have the following problem:
    When sp find a non dba table, job stop and give some message about that table.
    Is there a chance to filter and defrag only dba tables?

    Thanks,

    Luis Martin
    Moderator
    SQL-Server-Performance.com

    The views expressed here are those of the author and no one else. There are no warranties as to the reliability or accuracy of anything presented here.

    I had the same problem and this was my solution:-
    select so.[name]
    from sysobjects so,
    sysindexes si,
    sysusers su
    where
    so.[id]=si.[id] and
    so.[uid]=su.[uid] and
    so.type='U' and
    si.indid < 2 and
    si.[rows] > 0 and
    su.uid = 1 -- where owner is 'dbo'
    order by so.[name]

    I have been running this on the QA server which has dbs with non dbo tables and it seems to work OK so far.

    BTW, thanks for the code Thomas. [8D]

    Regards,
    Andy Ong

    'sapere aude.'
  27. Luis Martin Moderator

    Thanks, Andy I'll try.


    Luis Martin
    Moderator
    SQL-Server-Performance.com

    All postings are provided “AS IS” with no warranties for accuracy.

  28. andyobl New Member

    quote:Originally posted by LuisMartin

    Thanks, Andy I'll try.


    Luis Martin
    Moderator
    SQL-Server-Performance.com

    All postings are provided “AS IS” with no warranties for accuracy.



    Hi, I'd just found out another vendor's database we have here contains tables with different owners. So I tweaked the select statement to do this instead:-

    select so.[name]
    from sysobjects so,
    sysindexes si,
    sysusers su
    where
    so.[id]=si.[id] and
    so.[uid]=su.[uid] and
    so.type='U' and
    si.indid < 2 and
    si.[rows] > 0 and
    su.[uid] < 16384 -- where 16384 and above represents db roles
    order by so.[name]

    So far I have look at other SQL Servers at our sites at random and in sysusers.uid, the int 16384 to 16393 (for SQL Server 7.0 and above) always represents db roles. Can you guys confirm this is always the case?

    Regards,
    Andy Ong

    'sapere aude.'
  29. thomas New Member

    i think it's always over 16,000 or so for any groups if memory serves.


    Tom Pullen
    DBA, Oxfam GB

Share This Page