Today I noticed that I hadn't received any notification from CM in many hours. Upon further investigation (and testing by creating some failed logins) I concluded that the console and each monitored server's database weren't updating. Looking into if further I noticed that the Program Files\Idera\SQLcompliance\CollectionServerTraceFiles on the collection server was collecting files but apparently not loading them up. The number of files kept growing and growing. It also wasn't a case of it not keeping up with a deluge. I created a report of files grouped within the hour they were last accessed and starting about 10am yesterday things appeared to have frozen. I'm not sure what happened at 10--I don't think I added anything or changed any configuration.
I actually renamed that folder and created another one with a the old name and that one just fills up also. I've restarted services and rebooted to no avail.
Anyone ever encountered this before? Does anyone have any real world advise on the maximum number of servers I can monitor? I've got about 40 in there now and have at least 300 more to add in the coming weeks.
That can happen if the SQL Server service doesn't have permissions to the CollectionServerTraceFiles folder. Since you have recreated that folder, please verify that you gave full control to the logon account for the SQL Server service. You can also check the status of the jobs by running the following Query: SELECT state, COUNT(state) from [SQLcompliance].[dbo].[Jobs] GROUP BY state
yup--that was it. My SA changed something on me without telling.
That query is great and certainly is showing a ton of jobs in state 4 which I can only presume is something bad. If I drop those files back into the working folder they don't get picked back up. Any idea how to make that happen or how to clear out those jobs. I don't care too much if that data ends up being lost.
You can put the files back in the folder. Then change the state from 4 to 1 to reprocess them. I suggest doing them in batches and not all at once.
I changed some of the auditing yesterday and last night the number of trace files went pretty high. Around 9000 files. I recently removed most of the auditing except for security changes both at the server level and at the database level. We are only talking one server and 2 user databases along with the system dbs. Over the course of today the number of trace files is now 25,000 and the server is not keeping up with processing. These files are very small so I don't understand the issue with this application. Any help is appreciated at this point.
This sounds like some unusual behavior, we're sorry it's happening to you. Please contact our support team and they can help you to identify and diagnose the issue.