:::: MENU ::::

Posts Categorized / Monitoring and Analysis

  • Mar 18 / 2015
  • 0
dbDigger, Monitoring and Analysis, SQL Server Filegroups

Get size of all databases on a server

Following are three scripts to get

  • Get size of all databases on a server
  • Get size of all databases in terms of data and log
  • Get size of all data and log files

Get Size of all databases

SELECT DB_NAME(database_id) AS DatabaseName,
cast(sum( size* 8.0 / 1024)/1024 as DECIMAL(18,3)) [Size(GB)]
FROM sys.master_files
GROUP BY database_id
ORDER BY database_id

Get Size of all database in data and log category

SELECT DB_NAME(database_id) AS DatabaseName, type_desc,
cast(sum( size* 8.0 / 1024)/1024 as DECIMAL(18,3)) [Size(GB)]
FROM sys.master_files
GROUP BY database_id, type_desc
ORDER BY database_id, type_desc DESC 

— Get Size of all data and log files

SELECT DB_NAME(database_id) AS DatabaseName, type_desc, name, physical_name,
cast( (size* 8.0 / 1024)/1024 as DECIMAL(18,3)) [Size(GB)]
FROM sys.master_files
WHERE DB_NAME(database_id) = 'DBToFiletr'
ORDER BY database_id, type_desc DESC, name 
  • Nov 12 / 2014
  • 0
dbDigger, Monitoring and Analysis, Security and Permissions

Generate object level permissions in a database

Following script can be used to generate object level permissions in a database. We can filter the results for specific user or type.

 SELECT Us.name AS username,  
     us.type_desc AS UserType,  
     schema_name(obj.schema_id)+'.'+Obj.name AS objectName,  
     dp.permission_name AS permission ,  
     dp.state_desc AS PermissionStatus  
 FROM sys.database_permissions dp  
 JOIN sys.database_principals Us ON dp.grantee_principal_id = Us.principal_id  
 JOIN sys.objects Obj ON dp.major_id = Obj.object_id --where us.type_desc <> 'DATABASE_ROLE'  
 ORDER BY Us.name  
 -- get user permissions on whole DB  
 EXECUTE AS USER = 'userName';  
 SELECT * FROM fn_my_permissions(NULL, 'DATABASE')   
 -- get user permissions on whole DB  
 select  
  a.class_desc, permission_name, state_desc, b.name  
 from sys.database_permissions a  
 inner join sys.database_principals b  
  on a.grantee_principal_id = b.principal_id  
  and permission_name not in ('connect','view definition','SELECT')  
  and b.type_desc in ('SQL_USER','WINDOWS_USER')  
  and state_desc = 'GRANT'  
  and class_desc = 'DATABASE'  

  • Oct 01 / 2014
  • 0
dbDigger, Dynamic Management Views DMV, Monitoring and Analysis, SQL Server IO, T-SQL Scripts

Get number of reads and writes for each database on SQL Server

We can get the number of read and write operations for each database on our server. This information id being fetched by a DMV so analysis data depends upon up time of server.

 -- total I/O for each database  
 SELECT name AS 'Database Name'  
 ,SUM(num_of_reads) AS 'Number of Read'  
 ,SUM(num_of_writes) AS 'Number of Writes'   
 FROM sys.dm_io_virtual_file_stats(NULL, NULL) I  
 INNER JOIN sys.databases D   
 ON I.database_id = d.database_id  
 GROUP BY name ORDER BY 'Number of Read' DESC;  
  • Oct 04 / 2013
  • 0
DBCC Commands, dbDigger, Monitoring and Analysis, Transaction log files

Get total size of log file along with used space percentage for each SQL Server database

Always there exists a single log file for each SQL Server database. Log file keeps growing when in full or bulk logged recovery model. DBAs implement strategies to manage the log file size. There are two aspects of log file size.

  • One is log file size that it captures on disk and is shown at OS level.
  • Second aspect relates to filled part of over all log file.

Suppose there is a log file with size 20 GB on disk then it is quite possible that it is filled with only 1 GB log and rest of 19 GB is empty. SQL Server will keep populating the remaining part unless it is full and then will claim more size according to file growth settings.
It is a basic requirement to have an idea of log file and its used space. Following is a very simple way to get this information. You may also insert the information into a table on daily or weekly basis and may process alerts for these.

DBCC SQLPERF (LOGSPACE)
GO

Get total size of log file along with used space percentage for each SQL Server database

As a result we have Database name, total log file size as well as the filled log space percentage. Last column status is always zero.

  • 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.

Consult us to explore the Databases. Contact us