Hello

We have just installed our SCSM in our production environment but havent gone live yet. 

For some reason the service manager database grows with 200MB a day, with no technicians working in the system!

Our pre-production does not have the same symptom but same configuration. Only difference is that our production environment is connected to our live AD.

Any thoughts on what might be causing this? We have about 120 workitems now. And a 5 GB database.

Using SCSM 2012R2 and SQL 2012

Best regards

Christian

Malm University

Sweden

• Edited by Christian Incident mgr Thursday, December 12, 2013 4:30 PM

Hi,

You can try to change the transaction log into "simple mode" instead of "Full".

Its in simple mode already

5GB data

2.2 GB logs

What's the normal size for a service manager DB?

Hi

Dowload and try the sizing tools for SCSM 2012 : http://go.microsoft.com/fwlink/p/?LinkID=232378

We are way past that...considering we have not started using the system

/ch

Do you have a lot of workflow history? For example, a workflow running every 5 minutes generates a ridiculous amount of history data.

You can configure how long this data is kept via SQL, see here:
http://blogs.technet.com/b/servicemanager/archive/2010/12/07/more-aggressively-grooming-the-jobstatus-and-windowsworkflowtaskjobstatus-tables.aspx

When the job next runs (once per day) it will look at the new values and clear out all the old job history.

You can also try to shrink the log file via SQL Server Management Studio.

If you have databases in full logging mode, you MUST do transaction log backups, say, every 15 minutes or similar schedule to suit your needs. If you do not do this, the space is never reused and the logs will just grow and grow.

Thanks Rob! My DBA went on vacation yesterday, either this work or not, I will give feedback to this thread when he gets back. 

In addition to what Rob said; depending on your connectors (AD, SCOM, SCCM, etc), your database can get pretty big.

For example (just a single example, mind you), my environment has a few thousand computers from AD. I also bring in a bunch of information from SCCM and SCOM..my database is about 35 gigs right now. But 10 gigs of that is my JobStatus table with just a 7 day retention period :)

For AD connectors, I recommend only bringing in those OUs that you absolutely need in your CMDB.

Also be aware that connectors make a lot of changes to objects in the CMDB. Each of those changes are recorded as object history (again, increasing the size of your DB tables). Furthermore, the AD connectors, by defaut, run once an hour..so that's a lot of changes being made 24 times a day, filling up those history tables. If you don't need hourly AD imports, you can reschedule your AD connectors with a little powershell.

http://www.code4ward.net/main/Blog/tabid/70/EntryId/144/Change-Service-Manager-Connector-Schedule.aspx

• Proposed as answer by Rob.FordMVP, Moderator Wednesday, December 18, 2013 5:49 AM

I also noticed a error in the eventlog: 

Service Manager CMDB Grooming failed.

 Grooming Type: StagedChangeLog
 The following error was encountered:
 Transaction (Process ID 107) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction. Event ID: 10880

But I cant find any relevant information on Google, could this be the source to my problem? 

Best regards

Christian

These are normally not a sign of a problem unless you repeatedly get lots of these.

I get 1 per/day (Service Manager CMDB Grooming failed) together with about 150 of these : (

"The database subscription query is longer than expected. Check the database or simplify the database subscription criteria."

Looks like it correlates with when the AD-connector runs at night

My DBA is still on vacation so I haven't tried your earlier suggestion solution, but I will asap! Thanks for all your input Rob and Aaron so far!

Update: 

Havent tried Rob`s suggestion yet. "You can configure how long this data is kept via SQL, see here:
http://blogs.technet.com/b/servicemanager/archive/2010/12/07/more-aggressively-grooming-the-jobstatus-and-windowsworkflowtaskjobstatus-tables.aspx"

But It seems that reducing the Data retention settings for history to 1 dag (under Administrator -> settings) did the trick. The DB is now back to 2.4 GB.

I`m now testing to gradually increase the retention for history and still no Extreme DB increase. Ill keep you updated on my progress.

• Marked as answer by Christian Incident mgr 2 hours 57 minutes ago