The postings on this site are my own and do not represent my Employer's positions, advice or strategies.

LifeAsBob - Blog

 

Home

No Ads ever, except search!
Tuesday, October 19, 2021 Login
Public

Blog posts for the month of July,2015.
Fighting VarDecimal and Compressed Tables7/25/2015 9:35:48 AM

Enteprise Only features when moving to standard edition.  In this case sql2008 enterprise to sql 2012 standard edition, I ran into both compressed tables and vardecimal data types which are enterprise only features.

Lots of write-ups out there, so you can google them, i'm just putting this here as a placeholder for me.

--script to identify compressed tables

SELECT st.name, st.object_id, sp.partition_id, sp.partition_number, sp.data_compression,

sp.data_compression_desc FROM sys.partitions SP

INNER JOIN sys.tables ST ON

st.object_id = sp.object_id

WHERE data_compression <> 0

SELECT

SCHEMA_NAME(sys.objects.schema_id) AS [SchemaName]

,OBJECT_NAME(sys.objects.object_id) AS [ObjectName]

,[rows]

,[data_compression_desc]

,[index_id] as [IndexID_on_Table]

FROM sys.partitions

INNER JOIN sys.objects

ON sys.partitions.object_id = sys.objects.object_id

WHERE data_compression > 0

AND SCHEMA_NAME(sys.objects.schema_id) <> 'SYS'

ORDER BY SchemaName, ObjectName

ALTER TABLE dbo.DatabaseSizes REBUILD PARTITION = ALL WITH (DATA_COMPRESSION = NONE);

ALTER INDEX [IX_DatabaseSizes_CapturedAt_dbSizeId_FileId_Totalmb_Usedmb] ON [dbo].[DatabaseSizes]

REBUILD PARTITION = ALL WITH ( DATA_COMPRESSION = NONE, PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, ONLINE = OFF, SORT_IN_TEMPDB = OFF )

ALTER INDEX ALL ON TableSizes REBUILD WITH (DATA_COMPRESSION = None);

EXEC sp_db_vardecimal_storage_format

sp_tableoption 'DatabaseSizes', 'vardecimal storage format', 0

SELECT OBJECTPROPERTY(OBJECT_ID('DatabaseSizes'),'TableHasVarDecimalStorageFormat') ;

EXEC sp_db_vardecimal_storage_format 'SQLMonitor', 'OFF'

sp_msforeachtable 'exec sp_tableoption ''? '', ''vardecimal storage format'', 0'

select name, object_id, type_desc

from sys.objects

where objectproperty(object_id, N'TableHasVarDecimalStorageFormat') = 1

select 'exec sp_tableoption ' + CHAR(39) + name + char(39) + ',' + char(39) + 'vardecimal storage format' + char(39) + ',0'

from sys.objects where type = 'u'

backup database sqlmonitor to disk = 'H:\MSSQL\Backup\Full\SQLMonitor_20150725.bak' with compression

go


Blog Home