Follow

How do I Recover from large database growth or out of disk space or reached database size limits?

KB#:  KKB000707

QUESTION

How do I recover from large database growth or out of disk space or reached database size limits?

As a result of event logging no longer rolling over as a part of changes made in Kaseya 2008, I have experienced either

a) Massive database growth

b) Ended up in a situation where I am out of disk space on the database volume or the SQL service has stopped because of low disk space

c) Reached either the 2 GB or 4 GB database limit for MSDE or SQL Server Express 2005

Clicking the Run Archive Logs Now or Delete All button as described in http://kb.kaseya.com/article.aspx?p=11855&article=283554 (dead URL) either fails, or causes a further increase in database size.

ANSWER

The Run Archive Logs Now or Delete All button deletes database rows which causes a further increase in transaction log size corresponding to the amount of information deleted.This means these remediation techniques cannot be used in a low disk space condition, when the database size is constrained, or if you are concerned about further database size increases.

Please review the following knowledge base article about using SQL commands before proceeding:

http://kb.kaseya.com/article.aspx?p=11855&article=279591 (dead URL)

If you arecompletely out of disk space or the SQL Service has stopped due to low disk space conditions

1. Stop the Kaseya Server service.

2. Free up at least 30 MB of disk space on the volume that holds the SQL transaction logs and/or SQL database by deleting unnecessary and temporary files and/or moving SQL backups elsewhere. If you can free more disk space, do so.

3. Start the SQL Server service instance running the ksubscribers database.

4. If you have space on another volume, or removable hard drive, immediately backup the database to this drive from within SQL 2005 Management Studio, or SQL Enterprise Manager. Refer to the following Microsoft knowledge base:How to Backup A Database. This will commit all transactions, hopefullyfreeing up additional space on the volume holding the transaction logs. This is also best practice before any major database work.

5. Follow the procedures under 'If you don't care about losing event log data' to resolve the issue. If those steps do not resolve the issue, you may need to migrate the Kaseya database to another server and restore the database you backed up in step 4 to resolve this issue. See KKB000523 for more details.

If you are running SQL 2005 Express and wish to upgrade to SQL 2005 and have more than 4 GB of space available

Please see the knowledgebase article http://kb.kaseya.com/article.aspx?p=11855&article=275263 (dead URL)

If you don't care about losing event log data

1. Immediately backup the Kaseya database to a volume that has sufficient free space, under System >Server Management > Configure.This will commit all transactions outstanding in the database and is best practise before any major database work.

2. You will need to run a TRUNCATE ntEventLog on the ksubscribers database. This will cause all data to be lost from this database. See the following Microsoft article for the syntax of this command discussing the advantages and disadvantages of the TRUNCATE table command: http://msdn2.microsoft.com/en-us/library/ms177570.aspx

The TRUNCATE command will increase the size of the SQL transaction logs, but not nearly as much as deleting database rows. To determine the ntEventLog table size, use the following SQL command:

USE ksubscribers

EXEC sp_spaceused'ntEventLog'

Ensure you have this much disk space free on the volume holding the SQL transaction logs.

3. Having completed the above tasks, you should then backup the database again.

4. Then you should shrink the database file and transaction logs.This will reclaim the space on disk that the inflated transaction logs are taking up. To do this, you can use the DBCC SHRINKDATABASE command. See this MSDN entry for the technical documentation on the DBCC SHRINKDATABASE or DBCC SHRINKFILE commands:

 

DBCC SHRINKDATABASE

DBCC SHRINKFILE

If you do care about losing event log data

1. Immediately backup the Kaseya database to a volume that has sufficient free space, under System >Server Management > Configure. This will commit all transactions outstanding in the database and is best practice before any major databasework.

2. You will need to clear enough space on the volume to allow a successful Run Archive Logs now. To do this, redirect the archive folder to a volume that has sufficient free space (Under System > Server Management > Configure).

If Run Archive Logs does not run, you will have to create additional free space on the volumes holding the SQL database and transaction logs. If you cannot, you will have to lose data. Use the Delete and Delete All buttons to select which data to delete.

3. Having completed the above tasks, you should then backup the database again.

4. Then you should shrink the database file and transaction logs.This will reclaim the space on disk that the inflated transaction logs are taking up. To do this, you can use the DBCC SHRINKDATABASE command. See this MSDN entry for the technical documentation on the DBCC SHRINKDATABASE or DBCC SHRINKFILE commands:

DBCC SHRINKDATABASE

DBCC SHRINKFILE

Once you have resolved the steps below, you will still be in a position where you experience continued data growth. You need to evaluate your event log monitoring and storage requirements as outlined inhttp://kb.kaseya.com/article.aspx?p=11855&article=283798 (dead URL)

MORE INFORMATION

APPLIES TO

Kaseya 2008

MSDE

Microsoft SQL 2005 ExpressEdition

System tab

Was this article helpful?
0 out of 0 found this helpful
Have more questions? Submit a request

0 Comments

Article is closed for comments.