:::: MENU ::::

Posts Categorized / Monitoring and Analysis

  • Oct 03 / 2013
  • 0
dbDigger, Monitoring and Analysis, Reporting Services SSRS, T-SQL Scripts

Scripts for SSRS reports usage analysis

Here are some handy and useful scripts that may be used to analyze the report usage of your SSRS report server. All these are select scripts and are using NOLOCK hint. So feel free to add filters and customize according to your specific requirements.
To get the last Time a Report was generated we have following script. Further filters may be applied if required.

 SELECT DISTINCT C.NAME AS [Report Name]  
   ,MAX(EL.TIMESTART) AS [LAST START TIME]  
 FROM REPORTSERVER.DBO.EXECUTIONLOG(NOLOCK) EL  
 INNER JOIN REPORTSERVER.DBO.CATALOG (NOLOCK) C ON EL.REPORTID = C.ITEMID  
 GROUP BY C.NAME  
 ORDER BY C.NAME  

If you want to analyze that how many reports were viewed on a specific day then we have following script. It generates report usage Stats by Date. It is advisable to insert the name of the account that runs SSRS. The reason is that if you have a report that is run off of cache or has subscription, the counts will show here. So if you want to see reports that have been run by users only then filter out the account that runs the SSRS service.

SELECT CONVERT(VARCHAR(25), TIMESTART, 101) AS [SPECIFIC DATE]  
   ,COUNT(*) AS [Gneerated Number]  
 FROM REPORTSERVER.DBO.EXECUTIONLOG(NOLOCK)  
 --WHERE USERNAME NOT IN  
 GROUP BY CONVERT(VARCHAR(25), TIMESTART, 101)  
 ORDER BY CONVERT(VARCHAR(25), TIMESTART, 101) DESC  

Get the report server usage during the hours.

SELECT DATEPART(HOUR, TIMESTART) AS HOUR  
   ,COUNT(*) AS [Gneerated Number]  
 FROM REPORTSERVER.DBO.EXECUTIONLOG(NOLOCK)  
 --WHERE USERNAME NOT IN  
 GROUP BY DATEPART(HOUR, TIMESTART)  
 ORDER BY DATEPART(HOUR, TIMESTART)  

To get the reports generated by users we have following script.

SELECT EL.USERNAME  
   ,C.NAME  
   ,COUNT(1) AS [Gneerated Number]  
 FROM REPORTSERVER.DBO.EXECUTIONLOG(NOLOCK) EL  
 INNER JOIN REPORTSERVER.DBO.CATALOG (NOLOCK) C ON EL.REPORTID = C.ITEMID  
 GROUP BY EL.USERNAME  
   ,C.NAME  
 ORDER BY EL.USERNAME  
   ,C.NAME  
  • Aug 16 / 2013
  • 0
dbDigger, Monitoring and Analysis, Replication, T-SQL Scripts

Get list of replicated objects in a database

If replication is implemented then it is important to have a list of objects that have been replicated. Replicated objects are known as article in terms of replication setup. So articles may have slightly different concerns in terms of performance, drop or truncate command. Use the following simple script to get the list of replicated objects in a database.

 USE [Ur DB Name Here]  
 GO  
 SELECT NAME AS [Object Name]  
   ,type_desc AS [Object Type]  
   ,is_published AS [Is Data and Schema Published]  
   ,is_schema_published AS [Is Only Schema Published]  
 FROM sys.objects  
 WHERE is_ms_shipped = 0  
 AND (is_published = 1 OR is_schema_published = 1)  

Get list of replicated objects in a database
[Is Data is Schema Published] is for tables. While [Is Only Schema Published] is for other objects like USPs, UDF and Views.

  • Dec 03 / 2012
  • 0
dbDigger, Monitoring and Analysis, SQL Server Agent scheduled Jobs, T-SQL Scripts

Get list of scheduled jobs and associated steps

I am required to generate a list of SQL Server scheduled jobs along with there associated steps. It would be compared to same list from another server where the jobs just have been transferred. Following code would provide me job names along with their steps

 USE MSDB  
 GO  
 SELECT v.name AS JobName, step_name   
 FROM sysjobs_view v   
 inner join sysjobsteps t ON v.job_id = t.job_id  
 ORDER BY v.name, step_name  
 GO   

We may add further properties of job or step from SYSJOBS_VIEW and SYSJOBSTEPS respectively.

  • Nov 06 / 2012
  • 0
dbDigger, Logins and Users, Monitoring and Analysis, Security and Permissions

Get current connection context through SUSER_SNAME

If you are required to get the current connection context then SUSER_SNAME() function may be used. Primarily it is meant to return the user when security identification number (SID) is passed as parameter. However without passing parameter it would return the current connection context in terms of user. This information may be more helpful when used along with impersonation. Here is a simple example to use the SUSER_SNAME.

SELECT SUSER_SNAME() 
GO
  • Oct 25 / 2012
  • 0
DBA Interview questions, dbDigger, Monitoring and Analysis, Ports, System Administration, Windows Server

How to identify the active port/ports of SQL Server DB engine

In previous post i have discussed the ports configuration for SQL Server database engine. For any SQL Server instance what are the options to identify the active ports? For this purpose we have three major methods to identify the SQL Server port through GUI. We may use undocumented extended stored procedure master..xp_regread system  to read the registry value for this purpose.However in this post i would cover only the major GUI methods to discover the active TCP/IP port for any SQL Server instance.

  • Through SQL Server Logs
  • Through SQL Server Configuration Manager
  • Through Windows Application Event Viewer

 Through SQL Server Logs

We may use xp_readerrorlog SP in SSMS or SSMS GUI to determine the current port for SQL Server. Execute the SP in SSMS query pane and get the results

 USE master   
 GO   
 xp_readerrorlog 0, 1, N'listening on', 'any', NULL, NULL, N'asc'   
 GO   

Same may be determined through SSMS GUI

identify the active port of SQL Server through SSMS GUI

Through SQL Server Configuration Manager

Go to SQL server configuration manager and open TCP/IP properties in network configurations of specific instance. It would show the active TCP/IP port for SQL Server. Port may be noted or even changed to a specific static one here.

Identify SQL Server port through Configuration Manager

Through Windows Application Event Viewer

Another major option available for port discovery is through Windows event viewer. Find the event viewer in Windows Administrative Tools and go to application logs folder. There you may get the port information for your specific SQL Server instance.

Identify SQL Server port through Windows event log viewer

This is not the end, there would be some other ways to discover the same information. Also read the post and comments section on MSSQLTips for same issue.

  • Jul 30 / 2012
  • 0
dbDigger, Monitoring and Analysis, Security and Permissions, System Stored Procedures

List members of a specific database or server role

SQL Server provides built-in database and server roles. Members of a specific role inherit the privileges of that specific role. Such implicit privileges can not be seen in security related system tables and views. We may find members of a role both by SSMS or T-SQL.

Get members of a role through SSMS

Database roles may be found under security folder of a database. While server roles may be browsed under security folder of server instance.
To view members of a database or server role, just  right click on the role in mentioned folder. There you may get list of role members.
 Get members of a role through SSMS

Get members of a role through T-SQL

More conveniently we may get list of role members both for database role or server role. Following script would list members of sysadmin server role and db_DDLAdmin database role.

 
-- Get members of sysadmin server role   
 EXEC sp_helpsrvrolemember 'sysadmin'   
 GO   
 -- Get members of DDLAdmin database role   
 EXEC sp_helprolemember'db_DDLAdmin'  
 GO  

Role name may be substituted as required in both cases. Result would be 3 column list of role members.

Consult us to explore the Databases. Contact us