MS SQL Data Space Management

1] Commands

1.1 DB Level

1.1.1 MODIFY / SHRINK FILE

ALTER DATABASE Application_de_service_de_recherche_CrawlStoreDB_9cb0fc22d2884dea9c3df8a1f7bd7044 MODIFY FILE (NAME = Application_de_service_de_recherche_CrawlStoreDB_9cb0fc22d2884dea9c3df8a1f7bd7044, FILEGROWTH = 128 MB);

ALTER DATABASE Application_de_service_de_recherche_CrawlStoreDB_9cb0fc22d2884dea9c3df8a1f7bd7044 MODIFY FILE (NAME = Application_de_service_de_recherche_CrawlStoreDB_9cb0fc22d2884dea9c3df8a1f7bd7044_log, FILEGROWTH = 64 MB);

USE Application_de_service_de_recherche_CrawlStoreDB_9cb0fc22d2884dea9c3df8a1f7bd7044

DBCC Shrinkfile(NAME = Application_de_service_de_recherche_CrawlStoreDB_9cb0fc22d2884dea9c3df8a1f7bd7044,8192)

1.1.2 DATA Used Space

-- tempdb only

select * from sys.dm_db_file_space_usage ;

-- in a db

dbcc showfilestats

-- know how much free space exists in database and may be released using dbcc shrinkdatabase

use <database>

go

EXEC sp_spaceused

go

1.1.3 DBCC SHRINKDATABASE

 ( database_name | database_id | 0

     [ , target_percent ] [ , { NOTRUNCATE | TRUNCATEONLY } ] ) [ WITH NO_INFOMSGS ]

target_percent  Is the percentage of free space that you want left in the database file after the database has been shrunk.

TRUNCATEONLY Releases all free space at the end of the file to the operating system but does not perform any page movement inside the file. The data file is shrunk only to the last allocated extent. target_percent is ignored if specified with TRUNCATEONLY. TRUNCATEONLY is applicable only to data files. The log files are not affected.

Option	Page Movement	Truncate File
Default TRUNCATE & MOVE	Y	Y
NO_TRUNCATE	Y	N
TRUNCATE_ONLY	N	Y

Note : MOVE is similar to Oracle 10g+ Table Shrink.

1.2 Table Level

1.2.1 DBCC CLEANTABLE Reclaims space for dropped variable length columns and text columns.

(    { database_name | database_id | 0 }    , { table_name | table_id | view_name | view_id }

    [ , batch_size ] ) [ WITH NO_INFOMSGS ]

1.2.2 DBCC SHOWCONTIG ('Policy')

use SMS

DBCC SHOWCONTIG ('Policy')

DBCC SHOWCONTIG scanning 'Policy' table...

Table: 'Policy' (581577110); index ID: 1, database ID: 6

TABLE level scan performed.

- Pages Scanned................................: 288

- Extents Scanned..............................: 44

- Extent Switches..............................: 88

- Avg. Pages per Extent........................: 6.5

- Scan Density [Best Count:Actual Count].......: 40.45% [36:89]

- Logical Scan Fragmentation ..................: 20.49%

- Extent Scan Fragmentation ...................: 95.45%

- Avg. Bytes Free per Page.....................: 2492.7

- Avg. Page Density (full).....................: 69.20%

DBCC execution completed. If DBCC printed error messages, contact your system administrator.

2] Get Table & Index Volume

2.1 Table & Index, using « sp_spaceused »

[Taken from Internet, thx]

use <database>

--CREATE PROCEDURE GetAllTableSizes

--AS

/*    Obtains spaced used data for ALL user tables in the database */

DECLARE @TableName VARCHAR(100)    --For storing values in the cursor

--Cursor to get the name of all user tables from the sysobjects listing

DECLARE tableCursor CURSOR

FOR select [name] from dbo.sysobjects  where  OBJECTPROPERTY(id, N'IsUserTable') = 1

FOR READ ONLY

--A procedure level temp table to store the results

CREATE TABLE #TempTable

(

    tableName varchar(100),

    numberofRows integer,

    reservedSize varchar(50),

    dataSize varchar(50),

    indexSize varchar(50),

    unusedSize varchar(50)

)

--Open the cursor

OPEN tableCursor

--Get the first table name from the cursor

FETCH NEXT FROM tableCursor INTO @TableName

--Loop until the cursor was not able to fetch

WHILE (@@Fetch_Status >= 0)

BEGIN

    --Dump the results of the sp_spaceused query to the temp table

    INSERT  #TempTable

        EXEC sp_spaceused @TableName

    --Get the next table name

    FETCH NEXT FROM tableCursor INTO @TableName

END

--Get rid of the cursor

CLOSE tableCursor

DEALLOCATE tableCursor

--Select all records so we can use the reults

SELECT * FROM #TempTable order by numberofRows desc

--Final cleanup!

DROP TABLE #TempTable

GO

Table	Rows	Reserved	Data	Index	unused
CI_SDMPackages	65018	2167008 KB	2134408 KB	28800 KB	3800 KB
Policy	39612	17476672 KB	17475840 KB	464 KB	368 KB
CI_CurrentSettingsComplianceStatusDetail	43590	74640 KB	49864 KB	21408 KB	3368 KB

Too bad, I can’t sort on volume because « sp_spaceused » is giving me a varchar result for sizes.

2.2 Table & Index, as a whole – sort by Size_MB desc

-- Table Size and Row count information

use <database>

go

SELECT  OBJECT_NAME(ps.[object_id]) AS [Table] ,

        ps.row_count AS [Rows],

        SUM(ps.used_page_count)*8/1024 AS [Size_MB],

        SUM(ps.reserved_page_count)*8/1024 AS [Reserved_MB],

        SUM(ps.reserved_page_count-ps.used_page_count)*8/1024 AS [Unused_MB]

FROM    sys.dm_db_partition_stats AS ps INNER JOIN sys.indexes AS i

ON i.[object_id] = ps.[object_id] AND i.index_id = ps.index_id

WHERE   -- i.type_desc IN ( 'CLUSTERED', 'HEAP' ) AND

                               i.[object_id] > 100 AND OBJECT_SCHEMA_NAME(ps.[object_id]) <> 'sys'

GROUP BY ps.[object_id],ps.row_count

ORDER BY [Size_MB] DESC ;

Table	Rows	Size_MB (Table + all Idx)	Reserved_MB	Unused_MB
Policy	39613	17078	17078	0
CI_SDMPackages	65018	2112	2116	3
SW_LICENSING_PRODUCT_HIST	2810420	1502	1503	0

2.3 Reminder : Instance Level - get DB Total volume / Data vol. / Log vol.

select a.name ,

( SELECT ceiling(SUM(size*8)/1024.) Total_mdf_ldf_MB_sz FROM sys.master_files c where a.database_id = c.database_id ) Total_sz_MB ,

( SELECT ceiling(SUM(size*8)/1024.) Total_ldf_MB_sz FROM sys.master_files c where a.database_id = c.database_id and type = '0' ) Total_Data_sz_MB ,

( SELECT ceiling(SUM(size*8)/1024.) Total_ldf_MB_sz FROM sys.master_files c where a.database_id = c.database_id and type = '1' ) Total_Log_sz_MB

from sys.databases a where a.name not in ('model','tempdb')

order by 2

2.4 Index Size

SELECT i.name AS IndexName, SUM(page_count * 8) AS IndexSizeKB

FROM sys.dm_db_index_physical_stats(db_id(), object_id('dbo.TableName'), NULL, NULL, 'DETAILED')

AS s JOIN sys.indexes AS i ON s.[object_id] = i.[object_id] AND s.index_id = i.index_id

GROUP BY i.name ORDER BY i.name

3] SCCM 2007 Table growth Bugs

3.0 SCCM Background

ConfigMgr (SCCM) CI & SDM Packages Definitions

First, some definitions.

CI – A CI is short for Configuration Item.  A configuration item generically is a discrete unit of configuration to assess for compliance. They can contain one or more elements and their validation criteria, and they typically define a unit of configuration you want to monitor at the level of independent change.

SDM Package – A SDM package is short for Service Definition Model.  You may also hear the SDM Package described as using the SML – service modeling language.  SDM Packages link together with CI’s and further describe how to effect whatever configuration it is that we wish to evaluate or implement.

Backup and Recovery

Like any enterprise software, your site should be backed up to provide recoverability in case of unexpected events. Backing up a SCCM 2007 site involves backing up the database, the file system, and the registry all at the same point in time - backing up just one of these elements is not sufficient to restore a working site. SCCM 2007 uses the Volume Shadow Copy Service (VSS) to take small, frequent snapshots of the necessary components, making it easier to restore a failed site. The Site Repair Wizard walks you through the necessary steps to complete the site recovery.

3.1 Bug # 1 : How to Fix 'ci_sdmpackages' Table Growth

CI_SDMPackages growing rapidly at Primary Site

table             num_rows    reservedS         dataS       indexS      unusedS

CI_SDMPackages    79619       11566048 KB            11531704 KB 23288 KB    11056 KB

use SMS_ARB

go

dbcc cleantable ('SMS_ARB','ci_sdmpackages',1000)

GO

table             num_rows    reservedS         dataS       indexS      unusedS

CI_SDMPackages    79619       4475232 KB       2593056 KB  23288 KB    1858888 KB

DBCC SHRINKDATABASE (SMS_ARB, 5);

GO

Result : 9 GB Freed

Not used :

alter index CI_SDMPackages_AK on ci_sdmpackages

reorganize with (lob_compaction=ON)

GO

alter index CI_SDMPackages_AK2 on ci_sdmpackages

reorganize with (lob_compaction=ON)

GO

alter index CI_SDMPackages_PK on ci_sdmpackages

reorganize with (lob_compaction=ON)

GO

3.2 Bug # 2 'policy' Table Growth

will be resolved in FEP 2012

I was experiencing this issue with the same table and had to open a ticket with Microsoft due to the lack of information from forums. Basically, the engineer said it was a bug with FEP 2010. In our case, FEP was generating ALOT of notifications for "unknown machine" and/or "unkown SMS application" in the 'dbo.Policy' table creating orphaned CI's.

With MS approval : deletion of orphaned CIs

USE SMS

GO

-- orphaned CIs : 29950 rows / 38710

select COUNT(PolicyID) from Policy

SELECT PolicyID FROM Policy

WHERE PolicyID NOT IN (SELECT PolicyID FROM PolicyAssignment WHERE PADBID IN (SELECT PADBID FROM ResPolicyMap))

AND PolicyID NOT IN (SELECT ModelName FROM CI_ConfigurationItems)

AND PolicyID LIKE '%SUM_%'

select COUNT(PolicyID) from Policy

IF NOT EXISTS(SELECT * from sys.objects so WHERE so.name='PolDelPolicy' and so.type='U')

BEGIN

  CREATE TABLE PolDelPolicy (

  PolicyID varchar(512) primary key not null,

  RecordSize int null,

  DeleteTime datetime null

  );

END

IF EXISTS(SELECT * FROM sys.objects so WHERE so.name = 'Task_DeletePolicy' AND so.type='P')

DROP PROCEDURE Task_DeletePolicy

CREATE PROCEDURE Task_DeletePolicy as

BEGIN

  DECLARE @cursor1 cursor

  DECLARE @PolicyID varchar(512)

  DECLARE @PolSize int

  SET @cursor1 = cursor FOR

    SELECT PolicyID, DATALENGTH(Body) dl

    FROM Policy

    WHERE PolicyID NOT IN (SELECT PolicyID FROM PolicyAssignment

    WHERE PADBID IN (SELECT PADBID FROM ResPolicyMap))

    AND PolicyID NOT IN (SELECT ModelName FROM CI_ConfigurationItems)

    AND PolicyID LIKE '%SUM_%'

  OPEN @cursor1

  WHILE 1=1

  BEGIN

  FETCH FROM @cursor1 INTO @PolicyID, @Polsize

  IF @@FETCH_STATUS <> 0

    BREAK

    BEGIN TRANSACTION

    IF NOT EXISTS (SELECT PolicyID FROM PolDelPolicy WHERE PolicyID=@PolicyID)

      INSERT INTO PolDelPolicy VALUES (@PolicyID,@PolSize,GETDATE())

    DELETE FROM Policy WHERE PolicyID=@PolicyID

    COMMIT TRANSACTION 

  END

  CHECKPOINT

END

select COUNT(PolicyID) from PolDelPolicy

exec Task_DeletePolicy

sp_RENAME 'PolDelPolicy','PolDelPolicy_bak_210312'

-- Attention : changer une partie du nom de l'objet peut inhiber les scripts et les procedures stockees.

  CREATE TABLE PolDelPolicy (

  PolicyID varchar(512) primary key not null,

  RecordSize int null,

  DeleteTime datetime null

  );

3.3 Schedule the fix in Weekly T-SQL tasks

use SMS_SAS

GO

exec Task_DeletePolicy

GO

dbcc cleantable ('SMS_SAS','ci_sdmpackages',1000)

GO

DBCC SHRINKDATABASE (SMS_SAS, 5);

GO

Conclusion

I got a big volume decrease : 110 GB -> 56 GB

Tables Policy : 60 -> 17 GB

Tables CI_SDMPackages : 23 -> 8 GB

4] Radius Accounting Table is getting bigger and bigger

Radius Log table gets bigger and bigger : NPSXML.dbo.accounting_data

• Actual size ( using 2.2 sql to get the info ): 43 GB

Table	Rows	Size_MB (Table + all Idx)	Reserved_MB	Unused_MB
accounting_data	62182415	43279	43291	12

• Data distribution [ Full Scan Table 1H15 ]

select COUNT(1) from accounting_data where timestamp < dateadd(mm,-6,getdate()) -- 4 912 056

select COUNT(1) from accounting_data where timestamp < dateadd(mm,-3,getdate()) -- 15 481 360 duration = 1H15

• Does the table have Index ?

use NPSXML

EXEC sp_helpindex 'dbo.accounting_data'

The object 'dbo.accounting_data' does not have any indexes, or you do not have permissions.

What’s decided :

• Archive the table
• Remove very old rows ( < 9 months )
• Compress it

4.0 Archive table

-- Archive Log table :

-- sp_rename log -> log_archive_0511_to_0512

-- CTAS log AS log_arc_0511_to_0512 where 1 = 2 ;

sp_RENAME 'accounting_data' , 'accounting_data_archive_0511_to_0512'

-- create empty table

SELECT * INTO accounting_data FROM accounting_data_archive_0511_to_0512 WHERE 1=2

-- check everything

select * from INFORMATION_SCHEMA.TABLES where TABLE_NAME = 'accounting_data'

4.1 Massive Delete

4.1.1 Pre-requisite

-       Add index to speed up the scanning process

--Non Clustered Index

CREATE INDEX IX_accounting_data_archive_0511_to_0512 ON accounting_data_archive_0511_to_0512 (timestamp); -- 1H20

SELECT * FROM sys.indexes WHERE OBJECT_ID = OBJECT_ID('accounting_data_archive_0511_to_0512')

object_id	name	index_id	type	type_desc
2105058535	NULL	0	0	HEAP
2105058535	IX_accounting_data archive_0511_to_0512	14	2	NONCLUSTERED

Note : to revert it DROP INDEX IX_accounting_data_archive_0511_to_0512 ON accounting_data_archive_0511_to_0512

-       Index size using sql from 2.1 ( sp_spaceused ) : 1.2 GB

table	num_rows	reservedSize	dataSize	indexSize	unusedSize
accounting_data_archive_0511_to_0512	62 643 137	45843904 KB	44 648 944 KB	1 180 744 KB	14216 KB
accounting_data	28 136	22680 KB	22 608 KB	24 KB	48 KB

• How many rows to delete ( < 9 months ) ? [ Index Scan 3 seconds ! ]

select COUNT(1) from accounting_data_archive_0511_to_0512

where timestamp < dateadd(mm,-9,getdate())-- 2 292 269

• Get DB space used

use NPSXML

go

sp_spaceused

base	db_size	unallocated_space
NPSXML	47 895.75 MB	346.80 MB

4.1.2 Bulk Delete 2 millions rows ( 3.65 % )

Each run Deletes at max 500 000 rows / Takes 2 to 3 minutes

Generates around 400 MB of TLOG overall : 1500 MB -- 2753 MB 94 % -> 4225 MB 96 %

use NPSXML

-- Bulk Delete

-- credit : http://www.sqlteam.com/forums/post.asp?method=Reply&TOPIC_ID=62356&FORUM_ID=5

-- ORACSD 22/05/2012

-- @@ROWCOUNT function to return the number of deleted rows

DECLARE @intRowsToDelete int

DECLARE @intRowCount int

DECLARE @intErrNo int

DECLARE @intLoops int

DECLARE @dtLoop datetime

DECLARE @intProcCount int

-- DECLARE @txtStatus varchar(255)

SET ROWCOUNT 100000 -- number of rows to be deleted each loop

--SET ROWCOUNT 0            -- Reset batch size to "all"

SELECT

                @intRowCount = 1,            -- Force first iteration

                @intErrNo = 0,

                @intLoops = 5                     -- maximum loops to make (too prevent running for too long)

SELECT   @intRowsToDelete = COUNT(*)      -- Number of rows to be deleted

FROM     dbo.accounting_data_archive_0511_to_0512

WHERE   timestamp < dateadd(mm,-9,getdate())

WHILE @intRowCount > 0 AND @intErrNo = 0 AND @intLoops > 0

BEGIN

                SELECT   @dtLoop = GetDate()

                DELETE   FROM dbo.accounting_data_archive_0511_to_0512

                WHERE   timestamp < dateadd(mm,-9,getdate()) -- needs index access !!

                SELECT   @intErrNo = @@ERROR, @intRowCount = @@ROWCOUNT

-- implicit commit

                SELECT   @intRowsToDelete = @intRowsToDelete - @intRowCount,

                                               @intLoops = @intLoops - 1

-- Debugging usage only:

PRINT 'Deleted: ' + CONVERT(varchar(20), @intRowCount)

    + ', Elapsed: ' + CONVERT(varchar(20), DATEDIFF(Second, @dtLoop, GetDate())) + ' seconds,'

    + ' remaining to delete=' + CONVERT(varchar(20), @intRowsToDelete)

    + ', Loops left=' + CONVERT(varchar(20), @intLoops)

                WAITFOR DELAY '000:00:05'           -- 5 seconds for other users to gain access

END

(100000 ligne(s) affectee(s))

Deleted: 100000, Elapsed: 11 seconds, remaining to delete=2192287, Loops left=4

(100000 ligne(s) affectee(s))

Deleted: 100000, Elapsed: 17 seconds, remaining to delete=2092287, Loops left=3

(100000 ligne(s) affectee(s))

Deleted: 100000, Elapsed: 14 seconds, remaining to delete=1992287, Loops left=2

(100000 ligne(s) affectee(s))

Deleted: 100000, Elapsed: 15 seconds, remaining to delete=1892287, Loops left=1

(100000 ligne(s) affectee(s))

Deleted: 100000, Elapsed: 23 seconds, remaining to delete=1792287, Loops left=0

…

(100000 ligne(s) affectee(s))

Deleted: 100000, Elapsed: 21 seconds, remaining to delete=692385, Loops left=4

(100000 ligne(s) affectee(s))

Deleted: 100000, Elapsed: 26 seconds, remaining to delete=592385, Loops left=3

(100000 ligne(s) affectee(s))

Deleted: 100000, Elapsed: 50 seconds, remaining to delete=492385, Loops left=2

(100000 ligne(s) affectee(s))

Deleted: 100000, Elapsed: 30 seconds, remaining to delete=392385, Loops left=1

(100000 ligne(s) affectee(s))

Deleted: 100000, Elapsed: 27 seconds, remaining to delete=292385, Loops left=0

(100000 ligne(s) affectee(s))

Deleted: 100000, Elapsed: 29 seconds, remaining to delete=192415, Loops left=4

(100000 ligne(s) affectee(s))

Deleted: 100000, Elapsed: 34 seconds, remaining to delete=92415, Loops left=3

(92425 ligne(s) affectee(s))

Deleted: 92425, Elapsed: 25 seconds, remaining to delete=-10, Loops left=2

(0 ligne(s) affectee(s))

Deleted: 0, Elapsed: 0 seconds, remaining to delete=-10, Loops left=1

base	db_size	unallocated_space
NPSXML	49 367.75 MB	1798.37 MB

Increase of log space.

table	num_rows	reservedSize	dataSize	indexSize	unusedSize
accounting_data_archive_0511_to_0512	60 350 712	44357312 KB	43 148 744 KB	1 137 560 KB	71008 KB

xxxSize decreased : reservedSize down of 1 486 592 KB

SELECT o.name AS table_name,p.index_id, i.name AS index_name , au.type_desc AS allocation_type, au.data_pages, partition_number

FROM sys.allocation_units AS au

    JOIN sys.partitions AS p ON au.container_id = p.partition_id

    JOIN sys.objects AS o ON p.object_id = o.object_id

    JOIN sys.indexes AS i ON p.index_id = i.index_id AND i.object_id = p.object_id

WHERE o.name = N'accounting_data_archive_0511_to_0512'

ORDER BY o.name, p.index_id;

table	index_id	index	allocation_type	data_pages	partition_number
accounting_data_archive_0511_to_0512	0	NULL	IN_ROW_DATA	5393593	1
accounting_data_archive_0511_to_0512	0	NULL	ROW_OVERFLOW_DATA	0	1
accounting_data_archive_0511_to_0512	14	IX_accounting_data_archive_0511_to_0512	IN_ROW_DATA	141680	1

-- Using a Dynamic Management View, let's see how much space our table is consuming.

--check the size of the table

SELECT alloc_unit_type_desc,page_count,avg_page_space_used_in_percent,record_count

FROM sys.dm_db_index_physical_stats(DB_ID(),OBJECT_ID(N'dbo.accounting_data_archive_0511_to_0512'),NULL,NULL,'Detailed') ;

alloc_unit_type_desc	page_count	avg_page_space_used_in_percent	record_count
IN_ROW_DATA	5393593	84,40	60350712
IN_ROW_DATA	141680	99,97	60350712
IN_ROW_DATA	452	96,79	141680
IN_ROW_DATA	9	15,49	452
IN_ROW_DATA	1	2,76	9

ANOTHER DELETION : 36 214 Rows

(36214 ligne(s) affectee(s))

Deleted: 36214, Elapsed: 7 seconds, remaining to delete=0, Loops left=4

(0 ligne(s) affectee(s))

Deleted: 0, Elapsed: 0 seconds, remaining to delete=0, Loops left=3

table	index_id	index	allocation_type	data_pages	partition_number
accounting_data_archive_0511_to_0512	0	NULL	IN_ROW_DATA	5390550	1
accounting_data_archive_0511_to_0512	0	NULL	ROW_OVERFLOW_DATA	0	1
accounting_data_archive_0511_to_0512	14	IX_accounting_data_archive_0511_to_0512	IN_ROW_DATA	141595	1

Heap total Pages goes down from 5393593 to 5390550 = 3043 pages

table	num_rows	reservedSize	dataSize	indexSize	unusedSize
accounting_data_archive_0511_to_0512	60 314 498	44333952 KB	43 124 400 KB	1 136 880 KB	72672 KB

Pages decrease, though I thought the « "holes" were re-used by SQL Server »

See below for another test on a smaller table.

4.2 Table Compression

4.2.0 Prerequisite

• Drop index created for massive Delete

drop index IX_accounting_data_archive_0511_to_0512 on accounting_data_archive_0511_to_0512

• Is COMPRESSION enabled ?

-- compression setting

select object_name (object_id) as table_name, data_compression_desc

from sys.partitions where object_name (object_id) = 'accounting_data_archive_0511_to_0512'

accounting_data_archive_0511_to_0512            NONE

• Table Size

table	num_rows	reservedSize	dataSize	indexSize	unusedSize
accounting_data_archive_0511_to_0512	60 314 498	43195608 KB	43 124 400 KB	88 KB	71120 KB

SELECT o.name AS table_name,p.index_id, i.name AS index_name , au.type_desc AS allocation_type, au.data_pages, partition_number

FROM sys.allocation_units AS au

    JOIN sys.partitions AS p ON au.container_id = p.partition_id

    JOIN sys.objects AS o ON p.object_id = o.object_id

    JOIN sys.indexes AS i ON p.index_id = i.index_id AND i.object_id = p.object_id

WHERE o.name = N'accounting_data_archive_0511_to_0512'

ORDER BY o.name, p.index_id;

table	index_id	index	allocation_type	data_pages	partition_number
accounting_data_archive_0511_to_0512	0	NULL	IN_ROW_DATA	5390550	1
accounting_data_archive_0511_to_0512	0	NULL	ROW_OVERFLOW_DATA	0	1

• Row length … too long on 40 GB Table

-   Look at the current row size

select max_record_size_in_bytes

from sys.dm_db_index_physical_stats (db_id('compression'),

                   object_id('compression.accounting_data_archive_0511_to_0512'),

                   null, null, 'DETAILED')

where object_name (object_id) = 'accounting_data_archive_0511_to_0512'

4.2.1 Estimate Gain

sp_estimate_data_compression_savings

     [ @schema_name = ] 'schema_name' 

   , [ @object_name = ] 'object_name'

   , [@index_id = ] index_id

   , [@partition_number = ] partition_number

   , [@data_compression = ] 'data_compression'

[;]

An (IS) lock is acquired on the table during this operation. If an (IS) lock cannot be obtained, the procedure will be blocked. The table is scanned under the read committed isolation level.

• What if we use ROW or PAGE Level TABLE COMPRESSION ?

USE NPSXML;

GO

EXEC sp_estimate_data_compression_savings 'dbo', 'accounting_data_archive_0511_to_0512', NULL, NULL, 'ROW' ;

object_name	schema	index_id	partition number	size_with_current compression_setting (KB)	size_with_requested compression_setting (KB)
accounting_data_archive_0511_to_0512	dbo	0	1	43 124 488	19 210 256

Size would be divided by 2.

EXEC sp_estimate_data_compression_savings 'dbo', 'accounting_data_archive_0511_to_0512', NULL, NULL, 'PAGE' ;

object_name	schema	index_id	partition number	size_with_current compression_setting (KB)	size_with_requested compression_setting (KB)
accounting_data_archive_0511_to_0512	dbo	0	1	43 124 488	5 887 840

Size would be divided by 7.

4.2.2 Compress Table : 1H04

• Switch to SIMPLE Mode if needed

USE NPSXML

GO

ALTER DATABASE NPSXML

SET RECOVERY SIMPLE;

GO

-- This command can also be executed ONLINE

ALTER TABLE dbo.accounting_data_archive_0511_to_0512 REBUILD WITH (DATA_COMPRESSION = PAGE);

GO

-- Reset the database recovery model.

ALTER DATABASE NPSXML

SET RECOVERY FULL;

GO

4.2.3 Gain

ü  Table downsize : 38713128 KB = 36.9 GB

table	num_rows	reservedSize	dataSize	indexSize	unusedSize
accounting_data_archive_0511_to_0512	60 314 498	4482480 KB	4 481 816 KB	24 KB	640 KB

ü  Table Pages downsize : 4 830 325

table	index_id	index	allocation_type	data_pages	partition_number
accounting_data_archive_0511_to_0512	0	NULL	IN_ROW_DATA	560225	1
accounting_data_archive_0511_to_0512	0	NULL	ROW_OVERFLOW_DATA	0	1

ü  DB Unallocated Space growth : 39.37 GB

base	db_size	unallocated_space	reserved	data	index_size	unused
NPSXML	49998.00 MB	42119.05 MB	4897736 KB	4895528 KB	744 KB	1464 KB

To give this space back to the OS : 43.3 GB ( includes LOG )

EXEC master.sys.xp_fixeddrives -- T : 12 313 MB

-- 02H37

DBCC SHRINKDATABASE (NPSXML, 15);

GO

EXEC master.sys.xp_fixeddrives -- T : 56 682 MB

5] Delete on Table

5.1] Delete on Table : Are the Pages deallocated ?

I wonder if the freed space stays part of the table for future Insert like in Oracle.

• Initial metrics

use ReportServerTempDB

go

sp_spaceused

base	db_size	unallocated_space	reserved	data	index_size	unused
ReportServerTempDB	255.81 MB	12.82 MB	7608 KB	6512 KB	952 KB	144 KB

• Create Table / Load 250 000 rows

CREATE TABLE accounts ( fname VARCHAR(20), lname VARCHAR(20))

GO

INSERT accounts VALUES ('Fred', 'Flintstone') -- 6min26

GO 250000

SELECT TOP 10 * FROM accounts

GO

base	db_size	unallocated_space	reserved	data	index_size	unused
ReportServerTempDB	255.81 MB	5.44 MB	15168 KB	14032 KB	960 KB	176 KB

table	num_rows	reservedSize	dataSize	indexSize	unusedSize
accounts	250000	7560 KB	7520 KB	8 KB	32 KB

• Non Clustered Index – HEAP Table

CREATE INDEX IX_accounts ON accounts (lname);

base	db_size	unallocated_space	reserved	data	index_size	unused
ReportServerTempDB	258.81 MB	1.55 MB	22216 KB	14032 KB	7936 KB	248 KB

table	num_rows	reservedSize	dataSize	indexSize	unusedSize
accounts	250000	14608 KB	7520 KB	6984 KB	104 KB

--check the size of the table

SELECT alloc_unit_type_desc,page_count,avg_page_space_used_in_percent,record_count

FROM sys.dm_db_index_physical_stats(DB_ID(),OBJECT_ID(N'dbo.accounts'),NULL,NULL,'Detailed') ;

alloc_unit_type_desc	page_count	avg_page_space_used_in_percent	record_count
IN_ROW_DATA	940	95,27	250000
IN_ROW_DATA	866	99,84	250000
IN_ROW_DATA	4	90,92	866
IN_ROW_DATA	1	1,66	4

SELECT o.name AS table_name,p.index_id, i.name AS index_name , au.type_desc AS allocation_type, au.data_pages, partition_number

FROM sys.allocation_units AS au

    JOIN sys.partitions AS p ON au.container_id = p.partition_id

    JOIN sys.objects AS o ON p.object_id = o.object_id

    JOIN sys.indexes AS i ON p.index_id = i.index_id AND i.object_id = p.object_id

WHERE o.name = N'accounts'

ORDER BY o.name, p.index_id;

table	index_id	index	allocation_type	data_pages	partition_number
accounts	0	NULL	IN_ROW_DATA	940	1
accounts	2	IX_accounts	IN_ROW_DATA	866	1

• Delete all rows

-- delete top(200000) from accounts

delete from accounts

(250000 ligne(s) affectee(s))

base	db_size	unallocated_space	reserved	data	index_size	unused
ReportServerTempDB	258.81 MB	15.43 MB	8008 KB	6696 KB	992 KB	320 KB

table	num_rows	reservedSize	dataSize	indexSize	unusedSize
accounts	0	400 KB	184 KB	40 KB	176 KB

table	index_id	index	allocation_type	data_pages	partition_number
accounts	0	NULL	IN_ROW_DATA	23	1
accounts	2	IX_accounts	IN_ROW_DATA	1	1

• Clustered Index

CREATE clustered INDEX IX_accounts ON accounts (lname);

alloc_unit_type_desc	page_count	avg_page_space_used_in_percent	record_count
IN_ROW_DATA	1083	99,79	250000
IN_ROW_DATA	5	85,59	1083
IN_ROW_DATA	1	1,88	5

table	index_id	index	allocation_type	data_pages	partition_number
accounts	1	IX_accounts	IN_ROW_DATA	1083	1

After Total Delete

table	index_id	index	allocation_type	data_pages	partition_number
accounts	1	IX_accounts	IN_ROW_DATA	1	1

Conclusion

table type	Init Pages #	# Pages after Delete all rows	Desc
Heap	940 Tab 866 Idx	23 1	Rebuild index to get more pages deallocated
Clustered	1083	1	Every pages are given back to the DB

5.2] Delete according to dates

SELECT GETDATE() -- sysdate

select dateadd(hh,-1,getdate()) -- remove one hour

select dateadd(dd,1,getdate()) -- add one day

select dateadd(dd,-200,getdate()) -- remove 200 days

-- 104 tables creation_date older than 6 months

SELECT name , type_desc , create_date FROM sys.Tables where create_date < dateadd(dd,-200,getdate()) order by create_date desc

-- drop them

SELECT 'drop table ODS.genesys.' + name + ' ;' FROM sys.Tables where create_date < dateadd(dd,-200,getdate()) order by create_date desc

-- 100 tables creation_date older than 6 months

SELECT name , type_desc , create_date FROM sys.Tables where create_date < dateadd(dd,-200,getdate()) order by create_date desc

-- drop them

SELECT 'drop table ODS.genesys.' + name + ' ;' FROM sys.Tables where create_date < dateadd(dd,-200,getdate()) order by create_date desc

drop table ODS.genesys.OL_DATA80 ;

drop table ODS.genesys.OL_DATA79 ;

SET LANGUAGE English

dbcc sqlperf('logspace')

use ODS

-- after drop tables

EXEC sp_spaceused

db     sz                  unallocated

ODS    14606.25 MB  6807.34 MB

DBCC SHRINKDATABASE ( ODS , 0 , TRUNCATEONLY ) -- same, need page movement

DBCC SHRINKDATABASE ( ODS , 5 ) -- log full

ALTER DATABASE ODS SET RECOVERY SIMPLE; -- 9 MB free

DBCC SHRINKDATABASE ( ODS , 5 )

db     fid    cur_sz min_sz used_pages   estim_pages

8      1      1050784      288    1050512      1050512

8      2      160    128    160    128

EXEC master.sys.xp_fixeddrives -- 6 GB Free