Track Database Growth – SQL Server

DECLARE @endDate datetime, @months smallint;
SET @endDate = GetDate(); — Include in the statistic all backups from today
SET @months = 12; — back to the last 6 months.

;WITH HIST AS
(SELECT BS.database_name AS DatabaseName
,YEAR(BS.backup_start_date) * 100
+ MONTH(BS.backup_start_date) AS YearMonth
,CONVERT(numeric(10, 1), MIN(BF.file_size / 1048576.0)) AS MinSizeMB
,CONVERT(numeric(10, 1), MAX(BF.file_size / 1048576.0)) AS MaxSizeMB
,CONVERT(numeric(10, 1), AVG(BF.file_size / 1048576.0)) AS AvgSizeMB
FROM msdb.dbo.backupset as BS
INNER JOIN
msdb.dbo.backupfile AS BF
ON BS.backup_set_id = BF.backup_set_id
WHERE
BF.file_type = ‘D’
AND BS.backup_start_date BETWEEN DATEADD(mm, – @months, @endDate) AND @endDate
GROUP BY BS.database_name
,YEAR(BS.backup_start_date)
,MONTH(BS.backup_start_date))
SELECT MAIN.DatabaseName
,MAIN.YearMonth
,MAIN.AvgSizeMB
,MAIN.AvgSizeMB
– (SELECT TOP 1 SUB.AvgSizeMB
FROM HIST AS SUB
WHERE SUB.DatabaseName = MAIN.DatabaseName
AND SUB.YearMonth < MAIN.YearMonth
ORDER BY SUB.YearMonth DESC) AS GrowthMB
FROM HIST AS MAIN
ORDER BY MAIN.DatabaseName
,MAIN.YearMonth

Query to find the SQL server instance port number

— Check the port number from error log
EXEC xp_ReadErrorLog 0, 1, N’Server is listening on’, N’any’, NULL, NULL, ‘DESC’
GO
— Check the port number using DMV , dont run this script from rigistered servers session
SELECT local_tcp_port
FROM sys.dm_exec_connections
WHERE session_id = @@SPID
GO

Query to retrieve installation Date and Time – SQL Server

SELECT create_date
FROM sys.server_principals
WHERE sid = 0x010100000000000512000000

Script to copy the roles with permissions – SQL Server

The below script will copy the roles with permissions,  before executing on your database  change the role name.  copy and paste the results  and execute on the target database.

declare @DBRoleName varchar(40) = ‘role_name’

SELECT ‘GRANT ‘ + dbprm.permission_name + ‘ ON ‘ + OBJECT_SCHEMA_NAME(major_id) + ‘.’ + OBJECT_NAME(major_id) + ‘ TO ‘ + dbrol.name + char(13) COLLATE Latin1_General_CI_AS

from sys.database_permissions dbprm

join sys.database_principals dbrol on

dbprm.grantee_principal_id = dbrol.principal_id

where dbrol.name = @DBRoleName

Useful commands for SQL server clustering TSQL

— Find the current server is clustered or not

SELECT ‘IsClustered’, SERVERPROPERTY(‘IsClustered’)

—Find SQL Server Cluster Nodes
SELECT * FROM fn_virtualservernodes()

—Find name of the Node on which SQL Server Instance is Currently running
SELECT SERVERPROPERTY(‘ComputerNamePhysicalNetBIOS’) AS [CurrentNodeName]

— Find shared drives for cluster
SELECT * FROM fn_servershareddrives()

— Useful statements for cluster

SELECT CAST( SERVERPROPERTY(‘InstanceName’) AS NVARCHAR(128)) AS ‘InstanceName’
SELECT CAST( SERVERPROPERTY(‘MachineName’) AS NVARCHAR(128)) AS ‘MachineName’
SELECT CAST( SERVERPROPERTY(‘ServerName’)AS NVARCHAR(128)) AS ‘ServerName’
SELECT CAST( SERVERPROPERTY(‘ComputerNamePhysicalNetBIOS’)AS NVARCHAR(128)) AS ‘ComputerNamePhysicalNetBIOS’

How to convert sql_handle to Sql text – SQL server

sys.dm_exec_sql_text(sql_handle | plan_handle) returns the text of the SQL batch that is identified by the specific sql_handle.

following are the few examples to convert sql_handle to Sql text

1. for finding blocking statements

select spid,lastwaittype,cmd,loginame,tx.text from sys.sysprocesses as st
cross apply sys.dm_exec_sql_text(st.sql_handle) as tx where blocked >0

2 . another simple script to convert the sql_handle to text.

select top 1 tx.text, * from sys.dm_exec_query_stats as st cross apply sys.dm_exec_sql_text ( st.sql_handle) as tx

Below are few DMV’s where we can get sql_handles.

1) sys.dm_exec_query_memory_grants
2) sys.dm_exec_requests
3) sys.dm_exec_query_stats

Send the query results in a tabular form using DBmail and SQL server agent.

declare @emailSubject varchar(100),

@columnHeaders varchar(1000),

@tableHTML nvarchar(max)

select @emailSubject = ‘report’,             — Subject of Email

@columnHeaders = ‘session_id </th><th> DB_Name </th><th> host_name </th><th> program_name </th><th> client_interface_name </th><th> login_name </th><th> nt_domain </th><th> nt_user_name </th><th>

connect_time </th><th> login_time</th><th>’    — Column headers (must put </th><th> between each header)

if (select COUNT(*) from sys.dm_exec_connections AS c

JOIN sys.dm_exec_sessions AS s  ON c.session_id = s.session_id

WHERE c.session_id in (select session_id from sys.dm_exec_requests where database_id not  in (‘1′,’4’) )) >0

begin

set @tableHTML =

‘<div><b>user report</b></div><br>’ + — This is the bold text at the top of your email.

‘<table border=”1″ cellpadding=”5″><font face=”Calibri” size=2>’ +

‘<tr><th>’ + @columnHeaders + ‘</th></tr>’ +

convert(nvarchar(max),

(

SELECT

td = c.session_id,”,

td = DB_NAME(database_id),”,

td = s.host_name,”,

td = s.program_name,”,

td =  s.client_interface_name,”,

td = s.login_name,”,

td = s.nt_domain,”,

td =  s.nt_user_name,”,

td =  c.connect_time,”,

td = s.login_time,”

FROM sys.dm_exec_connections AS c

JOIN sys.dm_exec_sessions AS s  ON c.session_id = s.session_id

WHERE c.session_id in (select session_id from sys.dm_exec_requests where database_id not  in (‘1′,’4’) ) )

for xml path(‘tr’), type

)) +

–‘</font>’

‘</table>’

exec msdb.dbo.sp_send_dbmail

@recipients = ‘@mail.com;’,              — Email address of recipient

@body = @tableHTML,

@subject = @emailSubject,

@body_format = ‘HTML’

end

else

begin

exec msdb.dbo.sp_send_dbmail

@recipients = ‘@mail.com;’,              — Email address of recipient

@body = ‘0 rows effected’,

@subject = @emailSubject,

@body_format = ‘HTML’

END

List all the views in a Database – TSQL

SELECT SCHEMA_NAME(schema_id) AS schema_name
,name AS view_name
FROM sys.views order by view_name
GO

List all indexes with size and table name – TSQL

select
object_name(i.object_id) as TableName,
i.name as IndexName,
i.index_id as IndexID,
8 * SUM(a.used_pages) as ‘Indexsize(KB)’
FROM sys.indexes as i
JOIN sys.partitions as p ON p.object_id = i.object_id and p.index_id = i.index_id
JOIN sys.allocation_units as a ON a.container_id = p.partition_id
where i.type_desc <> ‘heap’
Group by i.object_id,i.index_id,i.name
Order By object_name(i.object_id),i.index_id

List all Indexes with fragmentation percent in a Database – TSQL

SELECT OBJECT_NAME(ind.OBJECT_ID) AS TableName,
ind.name AS IndexName, indexstats.index_type_desc AS IndexType,
indexstats.avg_fragmentation_in_percent
FROM sys.dm_db_index_physical_stats(DB_ID(‘DB_name’), NULL, NULL, NULL, NULL) indexstats
INNER JOIN sys.indexes ind
ON ind.object_id = indexstats.object_id
AND ind.index_id = indexstats.index_id
WHERE indexstats.avg_fragmentation_in_percent > 30 and index_type_desc <> ‘heap’
ORDER BY indexstats.avg_fragmentation_in_percent DESC,TableName