Trouble deleting Journal to shrink database

(0) Share

Report

Posted on by Community Member Microsoft Employee

My database is approaching the 4GB limit of SQL 2005 so I am trying to delete some of the journal.

When I run the query "DELETE FROM JOURNAL WHERE TIME < '01/01/2012'" in RMS Administrator, the database stays the same size at approximately 39.5GB. The receipts for the period are deleted, but the LOG grows from 1MG to 2.3GB. I'm working in a test environment, so it hasn't damaged the live database.

Does anyone have an idea why this isn't working? Any help would be greatly appreciated.

*This post is locked for comments

All responses (4)

Answers (1)

Community Member Microsoft Employee on at

Like (0)

Report

RE: Trouble deleting Journal to shrink database

Mabuhay!

Hi! On our part after we deleted some journal from the past year (we backup first the database and deleted the said squery) it runs smoothly and database has been reduced based on the rows weve deleted.

We also run the truncate log file so we can avoid exceeding the database size limit on express.

Chris
Community Member Microsoft Employee on at

Like (0)

Report

RE: Trouble deleting Journal to shrink database

Thanks for the answer Michael. 3.95GB is correct. In looking at the "properties" in Administrator, the DB was actually at 100% already. I was able to delete some of the journal with a query that used Batch # instead of date. What you suggested is a much better long term solution.
Verified answer

Community Member Microsoft Employee on at

Like (0)

Report

RE: Trouble deleting Journal to shrink database

You can use the dB Archive tool in the Retail Realm Utilities, or you can easily upgrade to a later version of the free SQL Server Express. 2008 was 10GB, and later versions added even more. You mean to say 3.95GB not 39.5, correct?
Community Member Microsoft Employee on at

Like (0)

Report

RE: Trouble deleting Journal to shrink database

The first time a tried, I did not get an error message. I tried again with a new backup and got this:

An error occurred while executing query:

Could not allocate space for object 'dbo.RecordDeletedLog'.'IX_TableName' in database 'TEST OLD BACK' 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.