September 27, 2010 at 2:14 pm
miksh (9/27/2010)
unused[/b] space which is based on sp_spaceused 1864 KB while reserved space is 8441576 KB and data space is 8439672 KB
Just did it on stage and it worked as I expected, i.e. db shrink freed only soem of the space.
It was
namerowsreserved data index_sizeunused
mytbl0 6959728 KB6662424 KB0 KB297184 KB
and after shrinking
namerowsreserved dataindex_sizeunused
mytbl0 5526112 KB5318536 KB0 KB207576 KB
September 27, 2010 at 2:17 pm
Derrick Smith (9/27/2010)
Have you tried doing ALTER TABLE tablename REBUILD ?
ALTER TABLE tablename REBUILD did not change any table size returned by sp_spaceused
September 27, 2010 at 2:19 pm
Hmm... even TRUNCATE didn't work! Any suggestions before I re-created the table?
September 27, 2010 at 2:23 pm
Just for kicks, try this:
Delete from tablename ( with tablock )
see if it changes the data size, which should be 0.
edit: I doubt we'd find anything out of the ordinary, but can you post the output from the below please? :
DBCC SHOWCONTIG (tablename) WITH TABLERESULTS
September 27, 2010 at 2:28 pm
raistlinx (9/27/2010)
getoffmyfoot (9/27/2010)
Yea, the drop/create table is brutal...Maybe I am missing something here, could someone elaborate on why this is not good in this situation?
There used to be data in this table. The data was deleted. Data is inserted into this table on a regular basis. What downstream process will be affected by doing this drop create? That information would have to be gathered before going to an extreme option such as dropping and recreating the table. There is also the need to ensure that all FKs associated with this table are properly dropped and recreated. It's not just as simple as drop / create.
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
September 27, 2010 at 2:32 pm
Run a dbcc checktable and determine if there is any corruption.
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
September 27, 2010 at 2:32 pm
Is this table replicated?
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
September 27, 2010 at 2:33 pm
Derrick Smith (9/27/2010)
Just for kicks, try this:Delete from tablename ( with tablock )
see if it changes the data size, which should be 0.
Will do a little later after running DBCC CHECKDB
edit: I doubt we'd find anything out of the ordinary, but can you post the output from the below please? :
DBCC SHOWCONTIG (tablename) WITH TABLERESULTS
Not sure how to better post this:
ObjectNameObjectIdIndexNameIndexIdLevelPagesRowsMinimumRecordSizeMaximumRecordSizeAverageRecordSizeForwardedRecordsExtentsExtentSwitchesAverageFreeBytesAveragePageDensityScanDensityBestCountActualCountLogicalFragmentationExtentFragmentation
Mytbl2045250341PK_MyFK1000000000001000000
September 27, 2010 at 2:34 pm
CirquedeSQLeil (9/27/2010)
raistlinx (9/27/2010)
getoffmyfoot (9/27/2010)
Yea, the drop/create table is brutal...Maybe I am missing something here, could someone elaborate on why this is not good in this situation?
There used to be data in this table. The data was deleted. Data is inserted into this table on a regular basis. What downstream process will be affected by doing this drop create? That information would have to be gathered before going to an extreme option such as dropping and recreating the table. There is also the need to ensure that all FKs associated with this table are properly dropped and recreated. It's not just as simple as drop / create.
Just trying to avoid several replications re-init.
September 27, 2010 at 2:35 pm
CirquedeSQLeil (9/27/2010)
Is this table replicated?
Yes but not on stage where I currently do testing.
September 27, 2010 at 2:37 pm
CirquedeSQLeil (9/27/2010)
Run a dbcc checktable and determine if there is any corruption.
It's when you don't like good news :crazy:
CHECKDB found 0 allocation errors and 0 consistency errors in database 'MyDatabase'.
September 27, 2010 at 2:40 pm
Btw, one of the columns of XML type if it helps you.
September 27, 2010 at 2:40 pm
To recap:
sp_updatestats did not work
dbcc updateusage did not work
dbcc checkdb shows no corruption?
The table is replicated
Index Rebuild did not work
Truncate table did not work
Are there any pending replication actions?
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
September 27, 2010 at 2:41 pm
miksh (9/27/2010)
CirquedeSQLeil (9/27/2010)
Run a dbcc checktable and determine if there is any corruption.It's when you don't like good news :crazy:
CHECKDB found 0 allocation errors and 0 consistency errors in database 'MyDatabase'.
I figured that might be the result.
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
September 27, 2010 at 2:42 pm
miksh (9/27/2010)
Btw, one of the columns of XML type if it helps you.
How many indexes?
Have all indexes been defragged / rebuilt?
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
Viewing 15 posts - 16 through 30 (of 65 total)
You must be logged in to reply to this topic. Login to reply