[APCF][ALL] How to reduce DB capacity by using MSSQL Express version
1. Summary
The MSSQL Standard version can use as much DB capacity as the available space on the server, but the Express version has limited capacity depending on the version. If the available capacity is exceeded, APC service will not operate normally, so please refer to the below.
2. Method
1. How to check MSSQL version
select @@version
2. Capacity Limits by MSSQL Version
- SQL Server 2005 - 4 GB
- SQL Server 2008 - 4 GB
- SQL Server 2012 - 10 GB
- SQL Server 2014 - 10 GB
APC for Windows Server supports MSSQL 2005 to 2014.
Other versions do not guarantee normal operation.
3. DB Table Cleanup and Collapse Method
Use the queries below to check for capacity-intensive tables and then delete them. You can proceed with the shrinkage afterwards.
[Check Table Capacity - Top 5]
SELECT TOP 5 CONVERT(VARCHAR(30), MIN(o.name)) AS TABLENAME
, LTRIM(STR(SUM(reserved) * 8192.0 / 1024.0, 15, 0) + ' KB') AS TABLESIZE
FROM sysindexes i
INNER JOIN sysobjects o ON o.id = i.id
WHERE i.indid IN (0, 1, 255)
AND o.xtype = 'U'
GROUP BY
i.id
ORDER BY
-- t_name ASC
SUM(reserved) * 8192.0 / 1024.0 DESC
[Results pane for Table Capacity Verification Queries]
[Delete Table] - The table below can be deleted because it is a simple event log, and if other tables are deleted at random, the normal operation of the product cannot be guaranteed.
truncate table atbRawNodeEventLog
truncate table atbRawHSDMILog
[Table Compression] - Shut down the APC service and proceed with the shrink operation. [How to stop APC Services]
dbcc shrinkdabase ('appc4db')