Hello. My name is Rudy Panigas and I am a Production Senior Microsoft SQL Server Database Administrator (DBA) with over 14 years of experience. Have published articles with SQL Central, am a leader for the my local PASS chapter in Toronto (TORPASS) and love to automate anything SQL including disaster recovery.
I created this blog to share my views, knowledge and to discuss Microsoft SQL server in order to help out others in this field.
Please test these scripts before using. I do not warranty and do not take any responsibility for it, its misuse or output it creates.
You can reach me at this email address: firstname.lastname@example.org
Hello, Do you know how much memory your databases are using and how much free memory you SQL Server has? Try this script below. The output will show the following:
List all the databases with how much memory they are using
Total usage for all databases
Max memory set in SQL Server
Memory available by subtracting the 2 values from above.
DECLARE @TotalUsedByDatabases NUMERIC ,@TotalMAXMemorySQLSrv NUMERIC SELECT isnull(db_name(database_id), 'resourcedb') [dbname] , CAST(COUNT(row_count)/128.0 as decimal(10,2)) [size] INTO #RAM FROM sys.dm_os_buffer_descriptors GROUP BY database_id SELECT dbname [Name of Databases], size [Memory Usage in MB] FROM #RAM SET @TotalUsedByDatabases = (SELECT SUM(size) FROM #RAM); SET @TotalMAXMemorySQLSrv = (SELECT CAST(value_in_use as NUMERIC) FROM sys.configurations WHERE name LIKE 'max server memory%'); SELECT @TotalUsedByDatabases [Total Memory used by Databases in MB] , @TotalMAXMemorySQLSrv [Max Memory Allocated to SQL Server in MB] , @TotalMAXMemorySQLSrv - @TotalUsedByDatabases [ ** Memory Available ** in MB]