Home » Databases » HOWTO: SAP IQ Drop Device File from DBSpace

HOWTO: SAP IQ Drop Device File from DBSpace

Sometimes when we build an IQ Data Warehouse, our initial space/growth estimate is off significantly enough to warrant reducing the size of the dbspace(s). The process is quite easy put there are a few steps to perform:

Determine how much space is used by the data/indexes. The usage field is the percentage of the totalsize that is being used. For example, the below shows 38% of 4.99TB being used. (4.99 TB * 1024 GB/TB) * 0.38 = 1941.70 GB used:

select convert(varchar(20), DBSpaceName), Usage, TotalSize from sp_iqdbspace() where DBSpaceName = 'IQ_USER_MAIN';
DBSpaceName          Usage TotalSize
------------------------------------
IQ_USER_MAIN         38    4.99T

Determine which device files are part of a dbspace

select convert(varchar(20), DBSpaceName), convert(varchar(20), DBFileName), convert(varchar(70), Path), DBFileSize, Usage from sp_iqfile() where DBSpaceName = 'IQ_USER_MAIN';
DBSpaceName          DBFileName           Path                                                                   DBFileSize Usage
---------------------------------------------------------------------------------------------------------------------------------
IQ_USER_MAIN         IQ_USER_MAIN_FILE_01 /sap/iq/devices/test_usermain001.iq                                    1024G      38
IQ_USER_MAIN         IQ_USER_MAIN_FILE_02 /sap/iq/devices/test_usermain002.iq                                    1024G      38
IQ_USER_MAIN         IQ_USER_MAIN_FILE_03 /sap/iq/devices/test_usermain003.iq                                    1024G      38
IQ_USER_MAIN         IQ_USER_MAIN_FILE_04 /sap/iq/devices/test_usermain004.iq                                    1024G      38
IQ_USER_MAIN         IQ_USER_MAIN_FILE_05 /sap/iq/devices/test_usermain005.iq                                    1024G      34

Verify that the used data will fit in the new allocation

sp_iqdbspace IQ_USER_MAIN

BACKUP DATABASE

backup database FULL to '/backups/test_FULL.dmp';

Alter the file to be dropped to be read only

alter dbspace IQ_USER_MAIN ALTER FILE IQ_USER_MAIN_FILE_05 readonly

Empty the device file (raw device) to be dropped (if dropping more than one file, drop the device file *LEAST* used first)

sp_iqemptyfile 'IQ_USER_MAIN_FILE_05'

Drop the device file (you may need to restart before IQ will let you do this)

ALTER DBSPACE IQ_USER_MAIN DROP FILE IQ_USER_MAIN_FILE_05

Verify the device was dropped

select convert(varchar(20), DBSpaceName), convert(varchar(20), DBFileName), convert(varchar(70), Path), DBFileSize, Usage from sp_iqfile() where DBSpaceName = 'IQ_USER_MAIN';
DBSpaceName          DBFileName           Path                                                                   DBFileSize Usage
---------------------------------------------------------------------------------------------------------------------------------
IQ_USER_MAIN         IQ_USER_MAIN_FILE_01 /sap/iq/devices/test_usermain001.iq                                    1024G      48
IQ_USER_MAIN         IQ_USER_MAIN_FILE_02 /sap/iq/devices/test_usermain002.iq                                    1024G      48
IQ_USER_MAIN         IQ_USER_MAIN_FILE_03 /sap/iq/devices/test_usermain003.iq                                    1024G      45
IQ_USER_MAIN         IQ_USER_MAIN_FILE_04 /sap/iq/devices/test_usermain004.iq                                    1024G      47
Share Button

Leave a Reply

Your email address will not be published. Required fields are marked *

*
*

Facebook login by WP-FB-AutoConnect