Post reply

Shrink Table

  • August 11, 2009 at 11:35 pm

    #73311

    I have a 110Gb table that will not shrink after a index rebuild or Database shrink.

    When I restore this database to another server the table is only 15GB with same amount of records.

    This is a table that does have a large amount of Inserts/deletes and there are XML columns(FYI).

    The index is a Clustered index

  • August 12, 2009 at 4:33 am

    #1037904

    Is the space being taken up by the log file or the database itself?

    Is the transaction recovery log in full, batch logged or simple mode?

    I had a problem whereby there were some pages in the log file which did not seem to write to the DB hence I couldn't do a shrink. Contacting the supplier, they verified that the DB did not require full recovery mode, so I change it to simple and those records were written into the DB and log file shrank.

    Geoff

  • August 12, 2009 at 7:41 am

    #1037979

    The ldf file is very small and percent free is about98 percent.

    spaceused on table shows reserved 110.1GB and data 110.5Gb and unused 650MB

    Hovever, when this database is backed up and restored to a reporting database on another server it shows 15.3GB reserved 15.8 Data and 65MB unused.

    Thanks in advance for your hep

  • August 12, 2009 at 7:51 am

    #1037987

    15.3GB reserved 15.8 Data

    That shows more Data than Reserved... have you run sp_spaceused @updateusage=true

  • August 12, 2009 at 7:57 am

    #1037997

    I apologise!

    Reverse the data and reserved numbers for both databases.

    Yes, I have sp_spaceused @updateusage=true (for both databases)

  • August 12, 2009 at 7:35 pm

    #1038409

    Are there any text/image columns in this table?

    MJ

  • August 13, 2009 at 4:46 am

    #1038600

    No Text/image columns - there are XML columns though

    I ran the following:

    declare @DBID int, @ObjID int

    select @ObjID= OBJECT_ID(N'')

    select @DBID=db_id()

    SELECT alloc_unit_type_desc, page_count, avg_page_space_used_in_percent, record_count, *

    FROM sys.dm_db_index_physical_stats(@DBID, @ObjID, NULL, NULL , 'Detailed');

    And .... I have LOB_DATA with 14Million+ Ghost_record_count.

  • August 15, 2009 at 2:29 am

    #1039790

    Its either because of high fragmentation or a low fillfactor.

    try to run showcontig on this table to see the percentage of fragmentation on this table.

    and double check the Fillfactor.

    I don' see any other reason.

    Regards,
    Sarabpreet Singh 😎
    Sarabpreet.com
    SQLChamp.com
    Twitter: @Sarab_SQLGeek

  • August 15, 2009 at 3:14 am

    #1039795

    And .... I have LOB_DATA with 14Million+ Ghost_record_count.

    I'm a bit confused here. You have posted this in the SQL 7,2000 forum.......

    If memory serves me correctly, there wasn't an XML datatype in SQL 2000 or earlier.

    You say you have no text or image datatypes, yet say you have LOB_DATA, so it sounds to me as though you must be using one of the BLOB datatypes.

  • August 15, 2009 at 9:35 am

    #1039841

    I assume you mean database and not table. A table uses the space allocated to it, +/- an extent. A database should always have reserved space. The table might have a lower or higher fill factor, LOB data, or minor allocations, but not a lot more space.

    Even if you have one table in a database, you are still dealing with a database primarily for space.

    So, is this SQL 2005? and is it a database that is causing you issues.?

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

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