Link to home
Create AccountLog in
Avatar of cweldoncd
cweldoncd

asked on

SCOM 2007 Audit Database

I have a successfull implementation of SCOM 2007 Audit collection services.  I have an application server, a database server for the DW, and a separate DB server for the ACS database and reporting services.  The collect the default events from 34 dc's. Over the years, I have played with the grooming and partitions.  I had set the ACS to keep 365 days at one point.  And this seemed to work for the past year but now, the database has ballooned to over 100 gb.  We talked it over and have reset it to 45 days.  The number of partitions was reduced dramatically from over 90 to around 46.  All have a status of 2 except the open one.  

My problem is that the size on the disk of the database keeps growing and there isn't a whole lot of free space to shring the db.  Shouldn't this clean up since I set the days from 365 to 45?

E:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\dbAuditData.mdf
Size: 123846.69 MB
Available Free Space: 790.88 MB (0%)

Any ideas on why this is happening and how I can reduce the size.
ASKER CERTIFIED SOLUTION
Avatar of wwwally
wwwally
Flag of Netherlands image

Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
Avatar of cweldoncd
cweldoncd

ASKER

Ok, so I have been through many of the steps in this document before.  However, I didn't have the filter applied, which I have done so now.

My output for the select * dtconfig incidates I have 45 partitions.  I double checked and all of them are set to 2 except for the open one.

I have around 2000 clients/workstations and 2000 users.  I just don't understand why all of a sudden the size of the database will not go downb because in the past I had the number of days set to 360.  I only see one error on the collector and it seems kind of limited at best.
 
Event Type:	Error
Event Source:	AdtServer
Event Category:	None
Event ID:	4620
Date:		1/3/2011
Time:		1:02:21 AM
User:		NT AUTHORITY\NETWORK SERVICE
Computer:	HOUASMOM01P
Description:
Database partition closed:
 Guid:	4d02b338_7f40_40ab_95aa_6f4539cca269
 Status:	0x00000005


For more information, see Help and Support Center at http://go.microsoft.com/fwlink/events.asp.

Open in new window


So all the old partition are gone and you only have the 45 partitions you want.
Can you run a report on db space used and db space in use on the ACD database.
I guess you mdf file is filling up your e drive but your DB is quite empty due to the reduction to 45 days instead of 365 days.
A shrink DB should be the step to free up the free space.
Regards,
Walter
Yes, you are correct on all that, however, when it comes to free space in the db, it is strange.  I have 129 GB on size for the DB, but the free space is only 1.9 gb.....not too much to recover.   I have run the shrink a few times for the logs and db and it doesn't show any more free.

I am wondering if my older setting of 365 days is still in effect for all the records that were kept under that setting.  Now the new setting of 45 days, even though in effect, it doesn't have any impact on the older data.?  Could that be possible?
The number of partitions is the number of days the data is kept. So if your seeining 45 partition your not retaining 365 days of partition and data.
Try reducing ACS data to 30 day's and wait for 24 hours (in which automatic grooming will take place) and shrink again.
This should have a 1/3 impact on you database size.
Regards,
Walter
Yes, understood but the scenario has been that at one time I had it at 365 days and it was smaller, like 90 gb, and then after I put it down to 45 days, it has kept growing.
SOLUTION
Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
I am closing out this account and moving to another account. I am going to rewart 250 points for helping with the filter setup. I will re-open this question most likely with my new account, so if you see a similar question, its me.