SSRS Subscription Status

Need to see the status of a report subscription in SSRS? Well now you can. This script, see download link below, will provide the following details.

• Event type
• Executed by
• Inactive or active 
• Last status
• Description
• Last run status
• Delivery extension
• Date modified

https://drive.google.com/file/d/0B07PMQYOhF2DWWhER19jazFEdk0/view?usp=sharing

Enjoy!

View User Access to your Reports in SSRS

How to see what access users have to your reports in SQL Server Reporting Service (SSRS)? Well now you can view the access with the following script, download it here 

https://drive.google.com/file/d/0B07PMQYOhF2DWWhER19jazFEdk0/view?usp=sharing

The script will show the following information

• Role name
• User name
• Path of report
• Report name
• Catalog type
• Description

Hope you find it useful.

Enjoy

Memory Usage by All Databases

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.

Enjoy!

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]


DROP TABLE #RAM;