October 5, 2017 at 3:41 pm
Hello All,
I had to set maximum limit on the DATA and LDF files of all the database on UAT (not a good practice but let's not go there). How can I write a SQL job which can monitor free space in both the files so I will be notified prior to filling the max limit on the files.
Thanks,
Amita
October 5, 2017 at 4:18 pm
kush - Thursday, October 5, 2017 3:41 PMHello All,
I had to set maximum limit on the DATA and LDF files of all the database on UAT (not a good practice but let's not go there). How can I write a SQL job which can monitor free space in both the files so I will be notified prior to filling the max limit on the files.Thanks,
Amita
One option would be to query sys.database_files to get the files and the current sizes and then combine that with FILEPROPERTY to get the space used.
You could play around with something like this to get what you need:
SELECT DB_NAME() AS [Database],
[name] AS [FileName],
[Type_Desc] as FileType,
CAST(size/128.0 as DECIMAL(10,2)) SizeMB,
CAST((CAST(size/128.0 as DECIMAL(10,2)) -
CAST(FILEPROPERTY(name,'SpaceUsed')AS INT)/128.0) as DECIMAL(10,2)) AS FreeSpaceMB
FROM sys.database_files
WHERE [type] in (0,1) --log and data files;
Sue
October 9, 2017 at 11:23 pm
Similar issue resolved here
https://www.sqlservercentral.com/Forums/Topic1106432-1550-1.aspx
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply