We have a production server with 500GB RAM. max server memory is set to 450GB. Whenever we checked the memory usage it's showing 450GB, but maybe SQL Server reached 450GB for some time? The memory is not released and it will be used whenever it is needed in future. So we have a free space for utilizing by SQL Server when it needs.
I need to configure an alert when SQL Server really uses 450GB of memory. If it's having FREE space among 450GB then how much it is and if the free space is used beyond 90% of available memory then I need an alert.
I can say in DB level even the SQL Server occupies DB files more than it wants and didn't release it. But I can tell to anyone like we would have unused space and it could be used by SQL Server at any time without increasing the file size.
Like that I am looking for script in Memory Level as well. I hope you are understanding my problem. My SQL Server is fully dedicated and no other applications or no other services (ssis,ssrs etc) are running. Find the below example DB space related script.
select NAME = left(a.NAME,15), [FILE_SIZE_MB] = convert(decimal(12,2),round(a.size/128.000,2)), [SPACE_USED_MB] = convert(decimal(12,2),round(fileproperty(a.name,'SpaceUsed')/128.000,2)), [FREE_SPACE_MB] = convert(decimal(12,2),round((a.size-fileproperty(a.name,'SpaceUsed'))/128.000,2)), [FILE_MAXSIZE_MB] = convert(decimal(12,2),round(a.maxsize/128.000,2)),[FILE_GROWTH] = convert(decimal(12,2),round(a.growth/128.000,2)) from dbo.sysfiles a