000017625 - RSA Archer: History log has filled up instance database  unable to log on

Document created by RSA Customer Support Employee on Jun 14, 2016Last modified by RSA Customer Support Employee on Apr 21, 2017
Version 2Show Document
  • View in full screen mode

Article Content

Article Number000017625
Applies ToRSA Archer
Issue

Unable to log on to RSA Archer.  Archer.w3wp error log shows the following:


Message>Could not allocate space for object 'dbo.tblSessionTemp'.'PK_tblSessionTemp' in database 'Archer Instance' because the 'PRIMARY' filegroup is full. Create disk space by deleting unneeded files, dropping objects in the filegroup, adding additional files to the filegroup, or setting autogrowth on for existing files in the filegroup.

ResolutionThe immediate solution to the error shown above is to free up space on the database server. 
Run the SQL scripts below to determine which database tables are consuming the most space. 
If you determine that the field history table (tblIVFieldHistory) is the cause of the problem, consider modifying history log fields to turn off tracking for fields that are generating a large amount of history data.
 
NotesRun the following SQL query against the Archer instance database to determine which tables are consuming the most space.  The query will return a list of tables in descending order of used space.
SELECT
t.NAME AS TableName,
p.rows AS RowCounts,
SUM(a.total_pages) * 8 AS TotalSpaceKB,
SUM(a.used_pages) * 8 AS UsedSpaceKB,
(SUM(a.total_pages) - SUM(a.used_pages)) * 8 AS UnusedSpaceKB
FROM
sys.tables t
INNER JOIN
sys.indexes i ON t.OBJECT_ID = i.object_id
INNER JOIN
sys.partitions p ON i.object_id = p.OBJECT_ID AND i.index_id = p.index_id
INNER JOIN
sys.allocation_units a ON p.partition_id = a.container_id
WHERE
t.NAME NOT LIKE 'dt%'
AND t.is_ms_shipped = 0
AND i.OBJECT_ID > 255
GROUP BY
t.Name, p.Rows, used_pages
ORDER BY
used_pages desc
If the query above indicates that the field history table (tblIVFieldHistory) is consuming an excessive amount of space, run the following query. This will tell you which fields are generating the most data in the table.  Based on this information, you can evaluate whether history log fields should be modified to remove tracking for certain fields.
SELECT
M.module_name,
FD.field_name,
COUNT(FD.field_name)AS Number_of_Rows
FROM tblIVFieldHistory FH
JOIN tblIVFieldDef FD on FD.field_id=FH.field_id
JOIN tblLevel L on L.level_id=FD.level_id
JOIN tblModule M on M.module_id=L.module_id
GROUP BY M.module_name,FD.field_name
ORDER BY Number_of_Rows desc
 
Legacy Article IDa66870

Attachments

    Outcomes