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.