August 16, 2012 at 2:20 am
Hi all...
while trying to find which of the 40 databases in the server is growing without control I've created a job that runs in 15 min interval that records to a table the file, data and log occupation for each database.
now I want to know in a daily base, for each database, the last recorded size.
I'm breaking my head with this ... I think I need a rest... can someone help me?
the recorded fields are:
RECORD_DATE (Datetime)
DATABASE_NAME
FILE_SIZE
FILE_SIZE_USED
DATA_SIZE
DATA_SIZE_USED
LOG_SIZE
LOG_SIZE_USED
Thanks in advance...
Miguel
August 16, 2012 at 2:23 am
You would want the MAX recorded date, then you will need to group by the remaining columns
SELECT
MAX(RECORD_DATE)
DATABASE_NAME,
FILE_SIZE,
FILE_SIZE_USED,
DATA_SIZE,
DATA_SIZE_USED,
LOG_SIZE,
LOG_SIZE_USED
FROM
sometable
GROUP BY
DATABASE_NAME,
FILE_SIZE,
FILE_SIZE_USED,
DATA_SIZE,
DATA_SIZE_USED,
LOG_SIZE,
LOG_SIZE_USED
That will only give the last recorded value, not on a daily basis.
Will create a different script.
August 16, 2012 at 2:35 am
Hopefully this will do it based on the max record date for a particular day then linking back to itself to get that row for that particular database.
SELECT
T1.RECORD_DATE,
T1.DATABASE_NAME,
T1.FILE_SIZE,
T1.FILE_SIZE_USED,
T1.DATA_SIZE,
T1.DATA_SIZE_USED,
T1.LOG_SIZE,
T1.LOG_SIZE_USED
FROM
sometable T1
INNER JOIN
(
SELECT
MAX(RECORD_DATE) AS MaxRecordDate,
DATABASE_NAME
FROM
sometable
GROUP BY
CONVERT(VARCHAR(10),RECORD_DATE,120)
DATABASE_NAME
) as Dev1
ON
T1.RECORD_DATE = Dev1.MaxRecordDate
AND
T1.DATABASE_NAME = Dev1.DATABASE_NAME
ORDER BY
T1.DATABASE_NAME,
T1.RECORD_DATE
August 16, 2012 at 2:41 am
😀 😀
anthony.green ... your the man!!! thank a lot for you help!
best regards
Miguel:-D
Viewing 4 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply