Comments
Sort by recent activity
I don't have an automated script, but it should be possible to do it manually by limiting the Id of the Cluster_SqlServer_SqlProcess_Keys table -- Figure out the range
SELECT MIN(Id) FROM data.Cluster_SqlServer_SqlProcess_Keys;
SELECT MAX(Id) FROM data.Cluster_SqlServer_SqlProcess_Keys;
Then manually split that range into chunks that are small enough to complete INSERT INTO data.Cluster_SqlServer_SqlProcess_Sightings
SELECT Id, CollectionDate as SightingDate FROM data.Cluster_SqlServer_SqlProcess_Keys
WHERE 0 <= ID AND ID < 1000000;
/ comments
I don't have an automated script, but it should be possible to do it manually by limiting the Id of the Cluster_SqlServer_SqlProcess_Keys table-- Figure out the range
SELECT MIN(Id) FROM d...
If you run this snippet it will get the database back into a state where purging will work, and the regular purging run will then delete data correctly. INSERT INTO data.Cluster_SqlServer_SqlProcess_Sightings
SELECT Id, CollectionDate as SightingDate FROM data.Cluster_SqlServer_SqlProcess_Keys
If you don't want to wait for an hour then there is a hidden URL to kick off a manual purge, by going directly to this location: http://mymachine:8080/Configuration/Purging/Purge
You should see a green 'Data purge started' bar to confirm that it worked.
I hope that fixes it for you, at least until we get 2.2 out. / comments
If you run this snippet it will get the database back into a state where purging will work, and the regular purging run will then delete data correctly.INSERT INTO data.Cluster_SqlServer_SqlProcess...
We are currently working on this issue, which is caused by data purging causing Monitor to lose track of things in the database. This is worse for Sql Processes (SPIDs) because they come and go very quickly.
While we are working on a proper fix, running these couple of lines of SQL will recreate some of the lost information and will allow things that were missed to be purged again. INSERT INTO data.Cluster_SqlServer_SqlProcess_Sightings
SELECT Id, CollectionDate as SightingDate FROM data.Cluster_SqlServer_SqlProcess_Keys
If this issue isn't actually causing problems for your installation, then I'd recommend just leaving things. If your database is getting too big, then this should help you out until we can get a proper, tested, fix out. / comments
We are currently working on this issue, which is caused by data purging causing Monitor to lose track of things in the database. This is worse for Sql Processes (SPIDs) because they come and go ver...
No workaround as yet, but we are investigating the problem. / comments
No workaround as yet, but we are investigating the problem.
Thanks for your help. I am looking into this, but it might take a day or so to investigate this further on some test databases. / comments
Thanks for your help. I am looking into this, but it might take a day or so to investigate this further on some test databases.
I've managed to reproduce this, and it appears you have found a bug. (The internal bug number is SRP-3038, for reference)
Thank you for your help tracking it down, and I'm sorry that you hit the issue. / comments
I've managed to reproduce this, and it appears you have found a bug. (The internal bug number is SRP-3038, for reference)
Thank you for your help tracking it down, and I'm sorry that you hit the is...
Thank you for the version number.
Although Monitor only deletes 50k rows at a time (to avoid the query holding locks for ages and/or timing out), the Base monitor will loop until it deletes less than 50k rows in an attempt, so it won't be limited to deleting 50k rows/hour.
It would be interesting to know what the earliest sample in the table is? SELECT TOP 1
utils.TicksToDateTime([CollectionDate]) AS [CollectionDate]
FROM [data].[Cluster_SqlServer_Sqlprocess_UnstableSamples]
ORDER BY CollectionDate;
SELECT COUNT(*) AS [Rows] FROM [data].[Cluster_SqlServer_Sqlprocess_UnstableSamples];
/ comments
Thank you for the version number.
Although Monitor only deletes 50k rows at a time (to avoid the query holding locks for ages and/or timing out), the Base monitor will loop until it deletes less th...
Hi, which version of monitor are you using? It is in Configuration -> About.
One thing to note is that data is purged at start up and every hour after that, so it may take up to an hour for configuration changes to take effect. This wouldn't cause some of the tables to be being purged while others are not, however.
Are you looking at the number of rows in the tables, or the amount of storage used as reported by ssms? SELECT TOP 10
[Id],
utils.TicksToDateTime([CollectionDate]),
[_RoundtripTime]
FROM [data].[Cluster_Ping_UnstableSamples]
ORDER BY CollectionDate;
/ comments
Hi, which version of monitor are you using? It is in Configuration -> About.
One thing to note is that data is purged at start up and every hour after that, so it may take up to an hour for configu...
Monitor will create a SSL key at first run, so once it has been created the permissions required should be less. It might be worth trying to reduce the permissions now that a key has been created? / comments
Monitor will create a SSL key at first run, so once it has been created the permissions required should be less. It might be worth trying to reduce the permissions now that a key has been created?
The magic you are looking for is SELECT utils.TicksToDateTime(634250127993781348);
Have you seen that there are views which can be handy when writing queries by hand? They join the tables up to the root, and also provide the date times in both the Monitor internal representation and a SQL datetime. This saves lots of manual joins. For example to view the error logs of a SQL Server instance: SELECT TOP 1000
[Cluster_SqlServer_Error_LogDate_DateTime]
,[Cluster_SqlServer_Error_SequenceNumber]
,[Cluster_SqlServer_Error_ProcessInfo]
,[Cluster_SqlServer_Error_Text]
,[CollectionDate_DateTime]
,[Cluster_Name]
,[Cluster_SqlServer_Name]
FROM [SqlMonitorData].[data].[Cluster_SqlServer_Error_Instances_View]
WHERE Cluster_Name = '<machine>' AND Cluster_SqlServer_Name = 'sql2005'
ORDER BY Cluster_SqlServer_Error_LogDate, Cluster_SqlServer_Error_SequenceNumber;
/ comments
The magic you are looking for isSELECT utils.TicksToDateTime(634250127993781348);
Have you seen that there are views which can be handy when writing queries by hand? They join the tables ...