SQL queries

[James]

SQL query to extract the logger device ID and serial number as well as the connected probe device ID and serial number:

SELECT dbo.MeasurePoints.ID as 'MPT-ID',
dbo.MeasurePoints.Serial as 'MPT-Serial',
dbo.Devices.ID as 'DEV-ID',
dbo.Devices.Serial as 'DEV-Serial'
FROM dbo.MeasurePoints
INNER JOIN dbo.Devices ON dbo.MeasurePoints.DeviceId = dbo.Devices.ID
order by dbo.Devices.ID

[James]

SQL query to delete device audit trail events:
DELETE FROM AlarmItems WHERE Target = 2 and TargetId = XXX

SQL query to delete measuring point audit trail events:
DELETE FROM AlarmItems WHERE Target = 3 and TargetId = XXX

SQL query to delete user audit trail events:
DELETE FROM AlarmItems WHERE Target = 6 and TargetId = XXX

To test, replace "DELETE FROM" with "SELECT * FROM"

[James]

SQL query to delete measuring data from a specific measuring point:
DELETE from DataRecords2 WHERE MeasurepointId=XXX;
DELETE from DataRecords3 WHERE MeasurepointId=XXX;

[James]

Delete data older than 90 days:
delete from [rms].[dbo].[DataRecord3] where [TimeStamp] <DATEADD(dd, -90,GETDATE());
delete from [rms].[dbo].[AlarmItems] where [EndTime] is not NULL and [EndTime] <DATEADD(dd, -90,GETDATE());

[James]

Read out the database memory usage:

SELECT DB_NAME() AS DbName,

name AS FileName,

type_desc,

size/128.0 AS CurrentSizeMB,

size/128.0 - CAST(FILEPROPERTY(name, 'SpaceUsed') AS INT)/128.0 AS FreeSpaceMB

FROM sys.database_files

WHERE type IN (0,1);