Symptoms:
When a large database with a complex transformation schema is executed by RayVentory Data Hub, considerable space has to be reserved by the system. Depending on machine and data parameters, this may lead to lack of disk space, required to run the transformation.
SQLite Error 13: 'database or disk is full'.
Cause:
The issue is caused by an extra data security layer, in which all operations are executed inside a sandbox. The sandbox contains a physical clone of all data being transformed. Large databases can easily grow, resulting in files exceeding several gigabytes in size. This temporary file is created in the temporary location and removed after both successful and failed execution.
Workaround
The workaround is to ensure, that there is enough disk space available. Large transforms can easily reach many gigabytes in size. The required size can be estimated by analyzing the content of the SQL database.
Freeing up necessary space can be achieved in one of the following ways:
- Cleaning up the disk space on your drive, where the temporary folder is
- Move temporary location to another drive, where there is enough place.
Determining recommended disk space
The required disk space depends on the amount of the data, and complexity of the transformation. Based on our experiences, we suggest executing the following SQL script, that can calculate how much place should be reserved. The output is just an estimation.
---------------------------------------------------
USE DataHub_Result -- Put here the name of database Data Hub Results
---------------------------------------------------
SELECT
CONVERT(
nvarchar,
(
SUM(TotalSpaceKB) / 1024.0 / 1024.0
) * 10
) + ' GB recommended disk space for RayVentory transformation' recommndetSize
FROM
(
SELECT
t.NAME AS TableName,
SUM(a.total_pages) * 8 AS TotalSpaceKB,
CAST(
ROUND(
(
(
SUM(a.total_pages) * 8
) / 1024.00
),
2
) AS NUMERIC(36, 2)
) AS TotalSpaceMB
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
LEFT OUTER JOIN sys.schemas s ON t.schema_id = s.schema_id
WHERE
t.NAME NOT LIKE 'dt%'
AND t.is_ms_shipped = 0
AND i.OBJECT_ID > 255
AND (
t.NAME like 'inventory%'
OR t.NAME like 'SCCM%'
OR t.NAME like 'activedirectory%'
)
GROUP BY
t.Name,
s.Name,
p.Rows
) S
Sample output:
Moving temporary folder to another drive (Windows)
The following steps should be executed on every machine, which is configured to perform data transformation (agents and/or server).
Go to System variables, select TMP variable, and click on Edit...
Set value of variable to a location on another drive, for example E:\TEMP.
(When on Data Hub Agent): Go to Service Manager and restart the Data Hub Agent:
(When on server): reset the IIS by executing command
iis reset
as Administrator.
As a result, the temporary file will be created in the new folder:
Additional recommendations
We recommend using a standard (non-Express) edition of SQL Server. This ensures that the instance is not affected by maximum file size.
Ensure, that the database file located on the disk / partition, that has enough free disk space:
Comments
0 comments
Please sign in to leave a comment.