Check status of HEAP tables

  • Wilfred van Dijk

    SSCrazy Eights

    Points: 8969

    Comments posted to this topic are about the item Check status of HEAP tables

    Wilfred
    The best things in life are the simple things

  • gserdijn

    Hall of Fame

    Points: 3259

    Thanks, it helped me to spot a potential problem that I was yet unaware of.


    Dutch Anti-RBAR League

  • Mark D Powell

    SSCarpal Tunnel

    Points: 4379

    I have not read all the code yet but doesn't #db03632 need to be defined somewhere?

    Mark D Powell

  • JStiney

    Say Hey Kid

    Points: 671

    I would like to run your script, but I get invalid characters in blank spaces when I copied it out of the window and ran it in Management Studio.

    What is the easiest way to fix for this?

  • Mark D Powell

    SSCarpal Tunnel

    Points: 4379

    I copied it into notepad and used the edit/replace option to replace a copy/paste of the stupid little squate box to space. That seemed to work but I got an error when I tried to exectue the script complaigning #db03632 does not exist.

    I will have took look into the temp table definition later.

    HTH -- Mark D Powell --

  • SQLRNNR

    SSC Guru

    Points: 281243

    Mark D Powell (3/18/2010)


    I copied it into notepad and used the edit/replace option to replace a copy/paste of the stupid little squate box to space. That seemed to work but I got an error when I tried to exectue the script complaigning #db03632 does not exist.

    I will have took look into the temp table definition later.

    HTH -- Mark D Powell --

    It looks like the temp table is either an insert into or select into based on the if condition. This should probably be changed so that the an existence check for the temp table is performed prior to that if condition.

    Jason...AKA CirqueDeSQLeil
    _______________________________________________
    I have given a name to my pain...MCM SQL Server, MVP
    SQL RNNR
    Posting Performance Based Questions - Gail Shaw[/url]
    Learn Extended Events

  • SQLRNNR

    SSC Guru

    Points: 281243

    JStiney (3/18/2010)


    I would like to run your script, but I get invalid characters in blank spaces when I copied it out of the window and ran it in Management Studio.

    What is the easiest way to fix for this?

    Use something like notepad++ to remove the unprinted characters.

    Jason...AKA CirqueDeSQLeil
    _______________________________________________
    I have given a name to my pain...MCM SQL Server, MVP
    SQL RNNR
    Posting Performance Based Questions - Gail Shaw[/url]
    Learn Extended Events

  • SQLRNNR

    SSC Guru

    Points: 281243

    Last comment for the moment. I like the principle of this script. Nice idea and it looks like it could be very helpful.

    Thanks.

    Jason...AKA CirqueDeSQLeil
    _______________________________________________
    I have given a name to my pain...MCM SQL Server, MVP
    SQL RNNR
    Posting Performance Based Questions - Gail Shaw[/url]
    Learn Extended Events

  • Wilfred van Dijk

    SSCrazy Eights

    Points: 8969

    Regarding the temptable: I use the @toggle flag to determine if the temptable is already created or not.

    In the first loop, @toggle=0 and a "select into" is used to create the table. @toggle is also set to 1

    In the next loops, @toggle=1, resulting in an "insert into"

    If you want to be sure this table doesn't exist, add this code before the cursor declaration:

    begin try

    drop table #db03632

    end try

    begin catch

    -- dummy

    end catch

    Regarding the "Strange characters": that's a result of different charactersets :crazy: . As suggested, copy code into an editor and remove the goofy characters

    Wilfred
    The best things in life are the simple things

  • Federico Iori

    SSCommitted

    Points: 1625

    Is possible to rebuild an heap simply by using :

    alter table tt rebuild partition=all(x) ,

    avalaible in Sqlserver 2008, online operation.

    In SqlServer2005 is possible to use my script , published on sqlservercentral, rebuild_partition .

    It should work for a nonpartitioned table giving there partition_number=1

    Another method is to create a clustered index on the table, then dropping the index

    Federico Iori

Viewing 10 posts - 1 through 10 (of 10 total)

You must be logged in to reply to this topic. Login to reply