Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Check status of HEAP tables Expand / Collapse
Author
Message
Posted Tuesday, March 09, 2010 6:35 PM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Today @ 1:46 AM
Points: 926, Visits: 1,095
Comments posted to this topic are about the item Check status of HEAP tables

Wilfred
The best things in life are the simple things
Post #879857
Posted Thursday, March 18, 2010 2:22 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Tuesday, March 25, 2014 12:35 AM
Points: 1,172, Visits: 727
Thanks, it helped me to spot a potential problem that I was yet unaware of.





Dutch Anti-RBAR League
Post #885309
Posted Thursday, March 18, 2010 1:20 PM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Thursday, April 17, 2014 12:16 PM
Points: 1,343, Visits: 373
I have not read all the code yet but doesn't #db03632 need to be defined somewhere?

Mark D Powell
Post #885848
Posted Thursday, March 18, 2010 1:47 PM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Friday, November 02, 2012 7:05 AM
Points: 75, Visits: 446
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?



Post #885875
Posted Thursday, March 18, 2010 1:51 PM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Thursday, April 17, 2014 12:16 PM
Points: 1,343, Visits: 373
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 --
Post #885880
Posted Thursday, March 18, 2010 1:56 PM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Today @ 5:28 PM
Points: 20,483, Visits: 14,136
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


SQL RNNR

Posting Performance Based Questions - Gail Shaw
Posting Data Etiquette - Jeff Moden
Hidden RBAR - Jeff Moden
VLFs and the Tran Log - Kimberly Tripp
Post #885883
Posted Thursday, March 18, 2010 1:57 PM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Today @ 5:28 PM
Points: 20,483, Visits: 14,136
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


SQL RNNR

Posting Performance Based Questions - Gail Shaw
Posting Data Etiquette - Jeff Moden
Hidden RBAR - Jeff Moden
VLFs and the Tran Log - Kimberly Tripp
Post #885884
Posted Thursday, March 18, 2010 1:58 PM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Today @ 5:28 PM
Points: 20,483, Visits: 14,136
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


SQL RNNR

Posting Performance Based Questions - Gail Shaw
Posting Data Etiquette - Jeff Moden
Hidden RBAR - Jeff Moden
VLFs and the Tran Log - Kimberly Tripp
Post #885885
Posted Thursday, March 18, 2010 4:20 PM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Today @ 1:46 AM
Points: 926, Visits: 1,095
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 . As suggested, copy code into an editor and remove the goofy characters


Wilfred
The best things in life are the simple things
Post #885968
Posted Tuesday, March 23, 2010 8:35 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Friday, February 07, 2014 6:42 AM
Points: 31, Visits: 277
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
Post #888166
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse