April 21, 2014 at 7:01 am
Hi
I have a table with 160GB data size. To reduce size, I set the values of a varchar(max) column to null. This column had large texts in it. So it was expected the setting it to NULL will reduce the size of the table. But the size returned by sp_spaceused remains same even after rebuilding the clustered index as suggested in some posts.
What can be reason behind it and how to get that space freed?
thanks
Online Trainer For SQL DBA and Developer @RedBushTechnologies with 18 yrs exp.
April 21, 2014 at 2:36 pm
So you modified the column of the table to allow NULLs or you set the value of old records to be NULL for that column? The first just states that a value is not mandatory when a record is entered. As for the second, I am not sure that would actually make the space available. NULL means unknown data, so I am not positive that would remove the data from the table, instead I think it will just tell SQL that it does not know what that data is.
It is probably a better idea to find out if 160GB worth of data is needed in that table for the application it supports. If not, find a way to safely delete unnecessary rows from the table and that will clear up some space. If it is needed then that might be a price you need to pay for the data. Maybe there are some other things you could do to reduce the table size, like implement history tables or export the data to a data warehouse.
Joie Andrew
"Since 1982"
April 21, 2014 at 5:29 pm
You might also want to see if any non-clustered indexes had INCLUDEs for those blobs. They would also need to be rebuilt.
--Jeff Moden
Change is inevitable... Change for the better is not.
April 22, 2014 at 2:38 am
thanks Jeff.The column , whose values I set to NULL is not part of any index. Infact the table just has one Primary Key Clustered index
and that is on some other column. I set around 5 million rows with NULL for this column and these rows had large text in them. So i was logically expecting
some good amount of space to be freed.But that didn't happen even after clusteed index rebuild.
Any other thoughts to reclaim that space?
Online Trainer For SQL DBA and Developer @RedBushTechnologies with 18 yrs exp.
April 22, 2014 at 2:39 am
Joie. I set the values to NULL. Earlier there was long text in that column. Now it has NULLs.So logically the size of table should reduce, but that's not happening...
Online Trainer For SQL DBA and Developer @RedBushTechnologies with 18 yrs exp.
April 22, 2014 at 3:08 am
Post the result of
sp_spaceused
sp_spaceused <table_name>
---------------------------------------------------
"Thare are only 10 types of people in the world:
Those who understand binary, and those who don't."
April 23, 2014 at 7:40 am
ok, now the direction of problem changed totally after some findings. The clustered index was actually not rebuild by the maintenance job because table has a ntext column!!
And we can rebuild index only online on this table, this being our main production transaction table.
I have sql server 2012 enterprose edition.Now my question changes to:
Can i change the data type from ntext to nvarchar(max) and then will I be able to rebuild the index online?
Is there any other way around to rebuild it online?
Remember it is a 160GB table and can't go offline/locked even for 2-3 minutes as that will cause heavy loss.
thanks
Online Trainer For SQL DBA and Developer @RedBushTechnologies with 18 yrs exp.
April 23, 2014 at 8:03 am
Can i change the data type from ntext to nvarchar(max) and then will I be able to rebuild the index online?
If you are on SQL Server 2012 you should be able to do this. SQL Server 2008 R2 and below cannot do an online rebuild with VARCHAR(MAX)/NVARCHAR(MAX) though.
Joie Andrew
"Since 1982"
April 23, 2014 at 8:09 am
S_Kumar_S (4/23/2014)
ok, now the direction of problem changed totally after some findings. The clustered index was actually not rebuild by the maintenance job because table has a ntext column!!And we can rebuild index only online on this table, this being our main production transaction table.
I have sql server 2012 enterprose edition.Now my question changes to:
Can i change the data type from ntext to nvarchar(max) and then will I be able to rebuild the index online?
Is there any other way around to rebuild it online?
Remember it is a 160GB table and can't go offline/locked even for 2-3 minutes as that will cause heavy loss.
thanks
Given the circumstances, I wouldn't attempt to alter the datatype of the column; that itself may result in a lengthy offline operation.
Perhaps reorganizing the clustered index will help. Reorganizing is always executed online.
http://technet.microsoft.com/en-us/library/ms189858.aspx
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho
April 23, 2014 at 8:14 am
S_Kumar_S (4/23/2014)
Can i change the data type from ntext to nvarchar(max) and then will I be able to rebuild the index online?Is there any other way around to rebuild it online?
Remember it is a 160GB table and can't go offline/locked even for 2-3 minutes as that will cause heavy loss.
thanks
I would not be changing the datatype.
Why? You can't even rebuild the indexes offline. This is your most critical table (or so it seems). And you shouldn't be changing data types without evaluating the application to ensure it won't break.
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
April 23, 2014 at 8:17 am
ok, so it is finally boiling down to call for a down time.
But I wonder how much pain it must be causing in production environments across world. Usage of varchar(max) is not very uncommon in 2008R2 and older versions. And you can't rebuild indexes online with this data type in table, strange...
Online Trainer For SQL DBA and Developer @RedBushTechnologies with 18 yrs exp.
April 23, 2014 at 8:22 am
Also, for sizing up a table that includes nText and nVarChar(max) columns, the sys.dm_db_partition_stats view can be used to return more detailed information about In Row allocation versus LOB and Overflow.
select
db_name() as db_name,
SCHEMA_NAME(o.schema_id) as schema_name,
o.nameAS object_name,
i.name AS index_name,
i.type_descAS index_type,
p.data_compression_desc,
sum(p.rows) as row_count,
cast(((sum(ps.used_page_count)) * 8192.0)
/ (1024 * 1024 * 1024) as numeric(12,1)) as used_size_gb,
cast(((sum(ps.reserved_page_count)) * 8192.0)
/ (1024 * 1024 * 1024) as numeric(12,1)) as reserved_size_gb,
cast(((sum(ps.in_row_data_page_count)) * 8192.0)
/ (1024 * 1024 * 1024) as numeric(12,1)) as in_row_size_gb,
cast(((sum(ps.in_row_used_page_count)) * 8192.0)
/ (1024 * 1024 * 1024) as numeric(12,1)) as in_row_used_gb,
cast(((sum(ps.in_row_reserved_page_count)) * 8192.0)
/ (1024 * 1024 * 1024) as numeric(12,1)) as in_row_reserved_gb,
cast(((sum(ps.lob_used_page_count)) * 8192.0)
/ (1024 * 1024 * 1024) as numeric(12,1)) as lob_used_size_gb,
cast(((sum(ps.lob_reserved_page_count)) * 8192.0)
/ (1024 * 1024 * 1024) as numeric(12,1)) as lob_reserved_size_gb,
cast(((sum(ps.row_overflow_used_page_count)) * 8192.0)
/ (1024 * 1024 * 1024) as numeric(12,1)) as row_overflow_used_size_gb,
cast(((sum(ps.row_overflow_reserved_page_count)) * 8192.0)
/ (1024 * 1024 * 1024) as numeric(12,1)) as row_overflow_reserved_size_gb
from sys.dm_db_partition_stats ps
join sys.partitions p
on ps.partition_id = p.partition_id
join sys.objects o
on o.object_id = p.object_id
and o.is_ms_shipped = 0
and o.name like @object_name
and schema_name(o.schema_id) like @schema_name
join sys.indexes i
on p.index_id = i.index_id
and p.object_id = i.object_id
group by
SCHEMA_NAME(o.schema_id),
o.name,
i.name,
i.type_desc,
p.data_compression_desc
order by
SCHEMA_NAME(o.schema_id),
o.name;
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho
April 23, 2014 at 8:27 am
Ok... let's shift gears here a bit.
This is a 160GB table and there are several types of performance and space savings that many may not have considered. Before I can make any suggestions in that area, I have some questions...
1. What type of table is this? Is it an OLTP table that suffers regular updates to existing rows or is it more like an Audit or Order Detail Table where the data is either written once and never updated (Audit Table) or only the latest 30 days might be updated for things like backorder fulfillment (Order Detail Table)?
2. How much temporary free space do you have. To you have about 180GB that could be used as a temporary work area so that we can fix the conversion of TEXT to BLOB and future reindexing problems (even if this IS an OLTP Table) as well as making backups (and related DR restores) sing if this isn't an OLTP table?
--Jeff Moden
Change is inevitable... Change for the better is not.
April 23, 2014 at 8:29 am
Here's an alternative as well for table space usage that is very detailed.
http://jasonbrimhall.info/2014/03/28/table-space-updated-again/
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
April 23, 2014 at 9:09 am
Jeff
replying to your questions:
1.This table is more like your OrderDetail table. There might be some updates daily but very few records (may be less than 50) get updated daily.
2.I have enough free space >600 GB. Are you suggesting creating a new table with varchar(max), load data in it and then rename tables here?
Jeff Moden (4/23/2014)
Ok... let's shift gears here a bit.This is a 160GB table and there are several types of performance and space savings that many may not have considered. Before I can make any suggestions in that area, I have some questions...
1. What type of table is this? Is it an OLTP table that suffers regular updates to existing rows or is it more like an Audit or Order Detail Table where the data is either written once and never updated (Audit Table) or only the latest 30 days might be updated for things like backorder fulfillment (Order Detail Table)?
2. How much temporary free space do you have. To you have about 180GB that could be used as a temporary work area so that we can fix the conversion of TEXT to BLOB and future reindexing problems (even if this IS an OLTP Table) as well as making backups (and related DR restores) sing if this isn't an OLTP table?
Online Trainer For SQL DBA and Developer @RedBushTechnologies with 18 yrs exp.
Viewing 15 posts - 1 through 15 (of 19 total)
You must be logged in to reply to this topic. Login to reply