Horkay Blog
The postings on this site are my own and do not represent my Employer's positions, advice or strategies.
Saturday, 25 July 2015

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

Saturday, 25 July 2015 08:35:48 (Central Standard Time, UTC-06:00) | Comments [0] | SQL Server | Web_Blog#
Search
Popular Posts
Unpatched Vulnerabiltiy discovered ...
Spring Fornicator brewed...
DTA - Failed to initialize MSDB dat...
SQL Server Security, not where it n...
Check the Uptime of a Windows Serve...
Recent Posts
Archive
May, 2017 (2)
April, 2017 (1)
March, 2017 (1)
February, 2017 (1)
December, 2016 (2)
October, 2016 (2)
September, 2016 (1)
August, 2016 (1)
July, 2016 (1)
March, 2016 (2)
February, 2016 (3)
December, 2015 (4)
November, 2015 (6)
September, 2015 (1)
August, 2015 (2)
July, 2015 (1)
March, 2015 (2)
January, 2015 (1)
December, 2014 (3)
November, 2014 (1)
July, 2014 (2)
June, 2014 (2)
May, 2014 (3)
April, 2014 (3)
March, 2014 (1)
December, 2013 (1)
October, 2013 (1)
August, 2013 (1)
July, 2013 (1)
June, 2013 (2)
May, 2013 (1)
March, 2013 (3)
February, 2013 (3)
January, 2013 (1)
December, 2012 (3)
November, 2012 (1)
October, 2012 (1)
September, 2012 (1)
August, 2012 (1)
July, 2012 (4)
June, 2012 (3)
April, 2012 (1)
March, 2012 (3)
February, 2012 (3)
January, 2012 (4)
December, 2011 (3)
October, 2011 (2)
September, 2011 (2)
August, 2011 (8)
July, 2011 (4)
June, 2011 (3)
May, 2011 (3)
April, 2011 (1)
March, 2011 (2)
February, 2011 (3)
January, 2011 (1)
September, 2010 (1)
August, 2010 (2)
May, 2010 (2)
April, 2010 (3)
March, 2010 (1)
February, 2010 (4)
January, 2010 (1)
December, 2009 (3)
November, 2009 (2)
October, 2009 (2)
September, 2009 (5)
August, 2009 (4)
July, 2009 (8)
June, 2009 (2)
May, 2009 (3)
April, 2009 (9)
March, 2009 (6)
February, 2009 (3)
January, 2009 (8)
December, 2008 (8)
November, 2008 (4)
October, 2008 (14)
September, 2008 (10)
August, 2008 (7)
July, 2008 (7)
June, 2008 (11)
May, 2008 (14)
April, 2008 (12)
March, 2008 (17)
February, 2008 (10)
January, 2008 (13)
December, 2007 (7)
November, 2007 (8)
Links
Categories
Admin Login
Sign In
Blogroll