Shrinking table space allocated - possible? 1

[mutley1]

Hi all,

I have run a table / data / index size script and was wondering if there is a lot of unused space in the table, can it be shrunk table by table?

Please see results below for a couple of tables. Where there is a lot of unused space, can that be returned to the mdf hence a shrink may be available so it can be returned to the OS?

Table	Row count	Total space KB	Data space	Index space	Unused space
mytablea	10795111	20677592	3129216	1154656	16393720
mytableb	1745013	10227864	899528	668552	8659784
mytablec	11267736	10048736	6301984	454048	3292704

Cheers,

M.

 

[mrdenny]

You can defrag your indexes which will release some of the free space. Other than that there is no way to remove the free space from the table.

Denny
MVP
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / SQL 2008 Implementation and Maintenance / Microsoft Windows SharePoint Services 3.0: Configuration / Microsoft Office SharePoint Server 2007: Configuration)
MCITP Database Admin (SQL 2005/2008) / Database Dev (SQL 2005)

My Blog

 

[JayKusch]

Mr Denny is right on the money.

A "possible" workaround would be to create secondary tables and export the table data.

Once the data is in the 2nd table, drop the source table and rename the 2nd table back to the source table.

Of course and FKs, Indexes, FT indexes and such may be compromised and need rebuilding.

Sloppy ... but if you really needed to shrink a table this may be the way to go at it.

I would just leave it alone if you have the disc resources to accomodate the table(s).

Thanks

J. Kusch

 

[mutley1]

Denny / Jay, thanks for the input.

Here's the next dilemma I have, on SQL 2000......

this table is replicated using constant transactional replication. It is no longer being written to as it is redundant, but there are currently 40 million rows. I have been given the all clear to remove everything in it except the last months worth of data. Retaining the last month is not a problem. My Q is obviously if I do a truncate on the live table, then how will distribution DB react / the replicated DB.......one would assume they would both go bananas.........truncate doesn't log, but I guess that the fact that everything has been deleted would have to go from A to B so they would go potty. (was thinking of transferring the 1 month to seperate table as per Jay's thoughts, truncating then copying back in).

Should I disable repl, copy 1 month to a seperate table, truncate, copy month back then reinstate relp and take a snapshot, or is there a better solution?

TIA geniuses.

M.

 

[mrdenny]

Truncate not being logged is a myth. Truncate is a minimally logged command in that SQL logs that a truncate occurred, but not that each record in the table is being removed.

You can either delete the data in small batches and let the deletes be sent to the subscribers, or copy out the data you need, truncate the table (I'm pretty sure it'll truncate a replicated table) then load the data you are keeping back into the table.

Any unused data pages will be reclaimed by the SQL Server and will be used by other tables.

I'd probably write a delete script to delete the needed data in small batches and let it run.

Denny
MVP
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / SQL 2008 Implementation and Maintenance / Microsoft Windows SharePoint Services 3.0: Configuration / Microsoft Office SharePoint Server 2007: Configuration)
MCITP Database Admin (SQL 2005/2008) / Database Dev (SQL 2005)

My Blog

 

[mutley1]

Thanks Denny,

I assume you're saying that if I use a truncate, then the deletes will be distributed as individual transactions as opposed to just a simple truncate on the DB and would not be a truncate on the dist / recipient DB?

Cheers,
M.

 

[mrdenny]

The truncate statement should be sent to the subscribers as a single truncate. Again I'm not sure if truncate is supported with replication.

Again I would recommend writing a delete script against the table and deleting the data in small batches.

Denny
MVP
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / SQL 2008 Implementation and Maintenance / Microsoft Windows SharePoint Services 3.0: Configuration / Microsoft Office SharePoint Server 2007: Configuration)
MCITP Database Admin (SQL 2005/2008) / Database Dev (SQL 2005)

My Blog

 

[mutley1]

Thanks Denny.