Answer
The first thing to do, is to run a compress on the database (File>Compress Database). After this, check the size of the database and see if it is still excessively large. If it returned to normal, then there is likely an issue with the growth settings in SQL server or the log file. You should also check the log file and make sure it is not excessively large. If the log file is still quite large after the compress you should detach and delete it.
If you have run the compress and the database remains quite large that means that there is data in there.
The most common cause of runaway data relates to budget worksheets. Find out if any new budget worksheets have been created. It’s possible that very large budget worksheets may exist in the system and are causing the increase in size. Those worksheets should be deleted if they have been transferred and posted. Worksheets are NOT budget transactions until they have been transferred and posted. Once transferred and posted, the worksheet is just a reference tool.
If you did have recent budget worksheet activity just before the database size expanded it is possible that there is corrupt information in tblTempBudgetHistory. This table is used in the building of budget worksheets. If a build of a budget worksheet fails because of a crash, lack of resources or ending task it is possible that the information it was assembling for the worksheet will be stuck in that table. The contents of this table will have to be flushed out via a sql script.
If the budget doesn’t seem to be the source of the problem, check for unposted transactions, particularly if you did some type of import. Its possible excess information was written to the tables. Try deleting the unposted transactions and see if that restores the size. After everything has been deleted check the contents of the TE tables in sql to see if there is anything left. If there is, clear it out from SQL.
If the tables look clean, you may check to see if the database is damaged using the checkdb command in sql
The command is “dbcc checkdb (databasename)”
Go through the results and see if there are any failures. If there are you will need to contact someone who is qualified to repair sql databases to address this issue.
If there are no problems with the structure of the database above you may want to run data integrity checks (Organization>Data Integrity checks). These may take a long time on a large database. If any of them fail consult support or the knowledgebase for a solution depending on which one fails.
If all integrity checks pass you may want to run a select script to find out where the data is. The following script can be run in sql server.
SELECT
t.NAME AS TableName,
s.Name AS SchemaName,
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
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
GROUP BY
t.Name, s.Name, p.Rows
ORDER BY
t.Name
This will give a list of the tables and their sizes. Copy and paste this to excel and sort by size. This will tell you the largest tables. If you are seeing tables that begin with tblDLxxxx up around the top of your list it means that activity has been posted into your system that takes that much space. You can run a posted transaction report filtered for the dates in which the expansion happened to find out what was posted. If posted activity is responsible for the size increase then this is the new size of your database. Reversing the activity will change your balances but it will also increase the size of the database even more. You cannot delete those entries.
If you have run the compress and the database remains quite large that means that there is data in there.
The most common cause of runaway data relates to budget worksheets. Find out if any new budget worksheets have been created. It’s possible that very large budget worksheets may exist in the system and are causing the increase in size. Those worksheets should be deleted if they have been transferred and posted. Worksheets are NOT budget transactions until they have been transferred and posted. Once transferred and posted, the worksheet is just a reference tool.
If you did have recent budget worksheet activity just before the database size expanded it is possible that there is corrupt information in tblTempBudgetHistory. This table is used in the building of budget worksheets. If a build of a budget worksheet fails because of a crash, lack of resources or ending task it is possible that the information it was assembling for the worksheet will be stuck in that table. The contents of this table will have to be flushed out via a sql script.
If the budget doesn’t seem to be the source of the problem, check for unposted transactions, particularly if you did some type of import. Its possible excess information was written to the tables. Try deleting the unposted transactions and see if that restores the size. After everything has been deleted check the contents of the TE tables in sql to see if there is anything left. If there is, clear it out from SQL.
If the tables look clean, you may check to see if the database is damaged using the checkdb command in sql
The command is “dbcc checkdb (databasename)”
Go through the results and see if there are any failures. If there are you will need to contact someone who is qualified to repair sql databases to address this issue.
If there are no problems with the structure of the database above you may want to run data integrity checks (Organization>Data Integrity checks). These may take a long time on a large database. If any of them fail consult support or the knowledgebase for a solution depending on which one fails.
If all integrity checks pass you may want to run a select script to find out where the data is. The following script can be run in sql server.
SELECT
t.NAME AS TableName,
s.Name AS SchemaName,
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
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
GROUP BY
t.Name, s.Name, p.Rows
ORDER BY
t.Name
This will give a list of the tables and their sizes. Copy and paste this to excel and sort by size. This will tell you the largest tables. If you are seeing tables that begin with tblDLxxxx up around the top of your list it means that activity has been posted into your system that takes that much space. You can run a posted transaction report filtered for the dates in which the expansion happened to find out what was posted. If posted activity is responsible for the size increase then this is the new size of your database. Reversing the activity will change your balances but it will also increase the size of the database even more. You cannot delete those entries.
Article Type
Product Info
Product Line
MIP Fund Accounting
Product Module/Feature
Data Issues
Data Management
Ranking