Large ReportDB file

[jeff.roback]

I have a very small unity 4.0.5 installation (20 users) but the "reportdb.mdf" file has gotten very large. Currently it's at 18 GB and growing. (It's the main datafile that's big, the transaction log file is nice and small and gets srhunk appropriately by the sql agent scripts). In browsing through the tables with Enterprise manager, I noticed the EVENTS table now has 52,127,077 rows.

Does this indicate that some sort of high-volume logging or diagnostic mode is in progress, or perhaps that the 7 day purge for reports isn't happening?

Either way, how can I forcibly purge out the old reports data to see if it's still continuing to grow?

Thanks!

[lindborg]

The events are pulled in from the event log - is your event log getting thrashed for some reason? Do the dates on the rows in the table show them all coming in recently or is just old data hanging out in there?

If the report/data retention times are 7 days it should be purging stuff older than that fom the tables - if you see that items in that table have dates older than that you'll want to open a case with TAC - not something I've seen in the field.

[mapennin]

Jeff if you are confortable executing queries in Query Analyzer, you can clear out the EVENTS table efficiently using the following query:

USE ReportDb

TRUNCATE TABLE EVENTS

This will at least recover your disk space and give you a way of knowing if there are still events being pumped into the table.

[mag19]

mapennin - thanks for the commands to clean up the reportdb. My reportdb was 2.6 GB (on a 8 GB drive). After I ran your command, I then compacted the database to reclaim the space:

C:\>osql -E

1>use ReportDB

1>go

1>backup log ReportDB with no_log

2>go

1>dbcc shrinkdatabase(ReportDB)

2>go

1>exit

2 GB reclaimed!! :) Thanks again!