SQL Server memory usage alert script 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
As JamesZ mentioned, the memory is intended for the SQL Server to use, particularly for the data cache.  Therefore SQL Server will attempt to use everything that has been made available to the SQL Server process.  (Plus some operating overhead.)

If you are concerned about reserving memory for Windows Operating System and any other processes that you run on the server you can adjust your maxservermemory to a lower number.

I have had good success with Jonathan Kehayias's "formula" that he posted here: 

How Much Memory Does My SQL Server Actually Need?

The first paragraph has a good outline of how Kehayias would by default  configure the Maximum Memory.  Of course, this is not absolute and you will need to observe your SQL Server and determine whether more or less memory needs to be reserved.

Brent Ozar has a sanity check post at:
Memory Dangerously Low or Max Memory Too High

So, you should not have a script to tell you that there is too little memory available.  You should configure appropriately for what your system needs.  

Brent Ozar's post for a 512 GB Server suggests:

EXEC sys.sp_configure ‘max server memory (MB)’, ‘471859’; 
RECONFIGURE;