SQL SERVER DBA, Linux and Azure: 2021

Find largest tables. Usually, we can prioritized index design based on slow queries that could be referencing one or more largest tables.

 --Find largest tables.  Usually, we can prioritized index design based on slow queries that could be referencing one or more largest tables.

TOP 20 Largerst
"select top 10 schema_name(tab.schema_id) + '.' + tab.name as [table],
cast(sum(spc.used_pages * 8)/1024.00 as numeric(36, 2)) as used_mb,
cast(sum(spc.total_pages * 8)/1024.00 as numeric(36, 2)) as allocated_mb
from sys.tables tab
join sys.indexes ind
on tab.object_id = ind.object_id
join sys.partitions part
on ind.object_id = part.object_id and ind.index_id = part.index_id
join sys.allocation_units spc
on part.partition_id = spc.container_id
group by schema_name(tab.schema_id) + '.' + tab.name
order by sum(spc.used_pages) desc;"



Review bad indexes

 -- Review bad indexes

Possible Bad NC Indexes (writes > reads)  

 -- Taking into connsideration the complete workload, and how long your instance has been running

 -- Advice not to drop or disable  indexes unless the is 100% certainty that it is no loger substantially benefitial

   SELECT OBJECT_NAME(s.[object_id]) AS [Table Name], i.name AS [Index Name], i.index_id, 

   is_disabled, i.is_hypothetical, i.has_filter, i.fill_factor,

   user_updates AS [Total Writes], user_seeks + user_scans + user_lookups AS [Total Reads],

   user_updates - (user_seeks + user_scans + user_lookups) AS [Difference]

   FROM sys.dm_db_index_usage_stats AS s WITH (NOLOCK)

   INNER JOIN sys.indexes AS i WITH (NOLOCK)

   ON s.[object_id] = i.[object_id]

   AND i.index_id = s.index_id

  WHERE OBJECTPROPERTY(s.[object_id],'IsUserTable') = 1

  AND s.database_id = DB_ID()

  AND user_updates > (user_seeks + user_scans + user_lookups)

  AND i.index_id > 1

  ORDER BY [Difference] DESC, [Total Writes] DESC, [Total Reads] ASC OPTION (RECOMPILE);

 

 

Improve the index design-- Review missing indexes

--  Review missing indexes

Missing Index Query

SELECT TOP 25 dm_mid.database_id AS DatabaseID, dm_migs.avg_user_impact*(dm_migs.user_seeks+dm_migs.user_scans) Avg_Estimated_Impact, dm_migs.last_user_seek AS Last_User_Seek, OBJECT_NAME(dm_mid.OBJECT_ID,dm_mid.database_id) AS [TableName], 'CREATE INDEX [IX_' + OBJECT_NAME(dm_mid.OBJECT_ID,dm_mid.database_id) + '_' + REPLACE(REPLACE(REPLACE(ISNULL(dm_mid.equality_columns,''),', ','_'),'[',''),']','') + CASE WHEN dm_mid.equality_columns IS NOT NULL AND dm_mid.inequality_columns IS NOT NULL THEN '_' ELSE '' END + REPLACE(REPLACE(REPLACE(ISNULL(dm_mid.inequality_columns,''),', ','_'),'[',''),']','') + ']' + ' ON ' + dm_mid.statement + ' (' + ISNULL (dm_mid.equality_columns,'') + CASE WHEN dm_mid.equality_columns IS NOT NULL AND dm_mid.inequality_columns IS NOT NULL THEN ',' ELSE '' END + ISNULL (dm_mid.inequality_columns, '') + ')' + ISNULL (' INCLUDE (' + dm_mid.included_columns + ')', '') AS Create_Statement FROM sys.dm_db_missing_index_groups dm_mig INNER JOIN sys.dm_db_missing_index_group_stats dm_migs ON dm_migs.group_handle = dm_mig.index_group_handle INNER JOIN sys.dm_db_missing_index_details dm_mid ON dm_mig.index_handle = dm_mid.index_handle WHERE dm_mid.database_ID = DB_ID() ORDER BY Avg_Estimated_Impact DESC

Tune inefficient queries in SQL Server

 --Tune inefficient queries in SQL Server

SELECT TOP 50
[Avg. MultiCore/CPU time(sec)] = qs.total_worker_time / 1000000 / qs.execution_count,
[Total MultiCore/CPU time(sec)] = qs.total_worker_time / 1000000,
[Avg. Elapsed Time(sec)] = qs.total_elapsed_time / 1000000 / qs.execution_count,
[Total Elapsed Time(sec)] = qs.total_elapsed_time / 1000000,
qs.execution_count,
[Avg. I/O] = (total_logical_reads + total_logical_writes) / qs.execution_count,
[Total I/O] = total_logical_reads + total_logical_writes,
Query = SUBSTRING(qt.[text], (qs.statement_start_offset / 2) + 1,
(
(
CASE qs.statement_end_offset
WHEN -1 THEN DATALENGTH(qt.[text])
ELSE qs.statement_end_offset
END - qs.statement_start_offset
) / 2
) + 1
),
Batch = qt.[text],
[DB] = DB_NAME(qt.[dbid]),
qs.last_execution_time,
qp.query_plan
FROM sys.dm_exec_query_stats AS qs
CROSS APPLY sys.dm_exec_sql_text(qs.[sql_handle]) AS qt
CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) AS qp
where qs.execution_count > 5 --more than 5 occurences
ORDER BY [Total MultiCore/CPU time(sec)] DESC


DB last updated and last_select_query_update_script

--DB last updated 

 select * from sys.dm_db_index_usage_stats where database_id = db_id('database_name') order by last_user_update 

 ---------------------------------------- 

 --last_select_query_update_script 


WITH LastActivity (ObjectID, LastAction) AS

  (

       SELECT object_id AS TableName,

              last_user_seek as LastAction

         FROM sys.dm_db_index_usage_stats u

        WHERE database_id = db_id(db_name())

        UNION

       SELECT object_id AS TableName,

              last_user_scan as LastAction

         FROM sys.dm_db_index_usage_stats u

        WHERE database_id = db_id(db_name())

        UNION

       SELECT object_id AS TableName,

              last_user_lookup as LastAction

         FROM sys.dm_db_index_usage_stats u

        WHERE database_id = db_id(db_name())

  )

  SELECT OBJECT_NAME(so.object_id) AS TableName,

         MAX(la.LastAction) as LastSelect

    FROM sys.objects so

    LEFT

    JOIN LastActivity la

      on so.object_id = la.ObjectID

   WHERE so.type = 'U'

     AND so.object_id > 100

GROUP BY OBJECT_NAME(so.object_id)

ORDER BY OBJECT_NAME(so.object_id)

Database Growth status report for All DB(s) via Email in SQL Server

 DECLARE @command VARCHAR(5000),@cmd Varchar (1000),@profile_name Varchar(1000)  

DECLARE @xml NVARCHAR(MAX)  

DECLARE @body NVARCHAR(MAX)  


Declare  @dbsize  Table

(Dbname sysname,dbstatus varchar(50),Recovery_Model varchar(40) default ('NA'), file_Size_MB decimal(30,2)default (0),Space_Used_MB decimal(30,2)default (0),Free_Space_MB decimal(30,2) default (0)) 


insert into @dbsize(Dbname,dbstatus,Recovery_Model,file_Size_MB,Space_Used_MB,Free_Space_MB) 

exec sp_msforeachdb 

'use [?]; 

  select DB_NAME() AS DbName, 

  CONVERT(varchar(20),DatabasePropertyEx(''?'',''Status'')) ,  

  CONVERT(varchar(20),DatabasePropertyEx(''?'',''Recovery'')),  

  sum(size)/128.0 AS File_Size_MB, 

  sum(CAST(FILEPROPERTY(name, ''SpaceUsed'') AS INT))/128.0 as Space_Used_MB, 

  SUM( size)/128.0 - sum(CAST(FILEPROPERTY(name,''SpaceUsed'') AS INT))/128.0 AS Free_Space_MB  

  from sys.database_files  where type=0 group by type' 

  -------------------log size-------------------------------------- 


--  if exists (select * from tempdb.sys.all_objects where name like '#logsize%') 


--drop table #logsize 


Declare  @logsize  Table

 (Dbname sysname, Log_File_Size_MB decimal(38,2)default (0),log_Space_Used_MB decimal(30,2)default (0),log_Free_Space_MB decimal(30,2)default (0)) 

 

insert into @logsize(Dbname,Log_File_Size_MB,log_Space_Used_MB,log_Free_Space_MB) 

exec sp_msforeachdb 

'use [?]; 

  select DB_NAME() AS DbName, 

  sum(size)/128.0 AS Log_File_Size_MB, 

  sum(CAST(FILEPROPERTY(name, ''SpaceUsed'') AS INT))/128.0 as log_Space_Used_MB, 

  SUM( size)/128.0 - sum(CAST(FILEPROPERTY(name,''SpaceUsed'') AS INT))/128.0 AS log_Free_Space_MB  

  from sys.database_files  where type=1 group by type' 

  --------------------------------database free size 


  --if exists (select * from tempdb.sys.all_objects where name like '%#dbfreesize%') 

  --drop table #dbfreesize 


Declare  @dbfreesize Table

(name sysname,database_size varchar(50),Freespace varchar(50)default (0.00))   

insert into @dbfreesize(name,database_size,Freespace) 


exec sp_msforeachdb 

'use [?];SELECT database_name = db_name() 

    ,database_size = ltrim(str((convert(DECIMAL(15, 2), dbsize) + convert(DECIMAL(15, 2), logsize)) * 8192 / 1048576, 15, 2) + ''MB'') 

    ,''unallocated space'' = ltrim(str(( 

                CASE  

                    WHEN dbsize >= reservedpages 

                        THEN (convert(DECIMAL(15, 2), dbsize) - convert(DECIMAL(15, 2), reservedpages)) * 8192 / 1048576 

                    ELSE 0 

                    END 

), 15, 2) + '' MB'') 

FROM ( 

    SELECT dbsize = sum(convert(BIGINT, CASE  

                    WHEN type = 0 

                        THEN size 

                    ELSE 0 

                    END)) 

        ,logsize = sum(convert(BIGINT, CASE  

                    WHEN type <> 0 

                        THEN size 

                    ELSE 0 

                    END)) 

    FROM sys.database_files 

) AS files 

,( 

    SELECT reservedpages = sum(a.total_pages) 

        ,usedpages = sum(a.used_pages) 

        ,pages = sum(CASE  

                WHEN it.internal_type IN (202,204,211,212,213,214,215,216 

      ) 

THEN 0 


                WHEN a.type <> 1 


                    THEN a.used_pages 


                WHEN p.index_id < 2 


                    THEN a.data_pages 


                ELSE 0 


                END) 


    FROM sys.partitions p 


    INNER JOIN sys.allocation_units a 


        ON p.partition_id = a.container_id 


    LEFT JOIN sys.internal_tables it 


        ON p.object_id = it.object_id 


) AS partitions' 


----------------------------------- 

--if exists (select * from tempdb.sys.all_objects where name like '%#alldbstate%') 

--drop table #alldbstate  


Declare @alldbstate  Table

(dbname sysname,DBstatus varchar(55),R_model Varchar(30)) 

--select * from sys.master_files 

insert into @alldbstate (dbname,DBstatus,R_model) 

select name,CONVERT(varchar(20),DATABASEPROPERTYEX(name,'status')),recovery_model_desc from sys.databases 

--select * from #dbsize 


insert into @dbsize(Dbname,dbstatus,Recovery_Model) 

select dbname,dbstatus,R_model from @alldbstate where DBstatus <> 'online' 

insert into @logsize(Dbname) 

select dbname from @alldbstate where DBstatus <> 'online' 

insert into @dbfreesize(name) 

select dbname from @alldbstate where DBstatus <> 'online' 


Declare @DB_space_Details Table

    (

[Dbname] [sysname] NOT NULL,

[dbstatus] [varchar](50) NULL,

[Recovery_Model] [varchar](40) NULL,

[DBsize] [decimal](38, 2) NULL,

[file_Size_MB] [decimal](30, 2) NULL,

[Space_Used_MB] [decimal](30, 2) NULL,

[Free_Space_MB] [decimal](30, 2) NULL,

[Log_File_Size_MB] [decimal](38, 2) NULL,

[log_Space_Used_MB] [decimal](30, 2) NULL,

[log_Free_Space_MB] [decimal](30, 2) NULL,

[DB_Freespace] [varchar](50) NULL,

[Capture_Date] [datetime] NOT NULL

Insert into @DB_space_Details 

select  

d.Dbname,d.dbstatus,d.Recovery_Model, 

(file_size_mb + log_file_size_mb) as DBsize, 

d.file_Size_MB,d.Space_Used_MB,d.Free_Space_MB, 

l.Log_File_Size_MB,log_Space_Used_MB,l.log_Free_Space_MB,fs.Freespace as DB_Freespace,getdate() 'Current_Date'  

from @dbsize d join @logsize l  

on d.Dbname=l.Dbname join @dbfreesize fs  

on d.Dbname=fs.name 

order by Dbname


--Select * from @DB_space_Details 

--Select * from DB_space_Details


SET @xml = CAST(( SELECT DB.DBname As 'td',' ',DB.dbstatus AS 'td' ,' ',DB.Recovery_Model as 'td' ,' ',DB.DBsize 'td',' ',DB.File_Size_MB as 'td',  

' ',DB.Space_Used_MB As 'td' ,' ',DB.Free_Space_MB as 'td',' ',  DB.Log_File_Size_MB As 'td', ' ', DB.Log_Space_Used_MB As 'td', ' ',

 DB.log_free_Space_MB As 'td',' ', DB.DB_Freespace As 'td' ,' ', convert(varchar(20), DB.Capture_Date,120) as 'td'  from @DB_space_Details  DB

FOR XML PATH('tr'), ELEMENTS ) AS NVARCHAR(MAX))  


SET @body ='<html><body><H5>Database Growth status </H5>  

<table border = 1>   

<th> Database Name </th><th> Ddbstatus </th> <th> Recovery_Model </th> <th> DBsize </th> <th> File_Size_MB </th> <th> Space_Used_MB </th>  

<th> Free_Space_MB </th><th> Log_File_Size_MB </th> 

<th> Log_Space_Used_MB </th> <th> log_free_Space_MB </th> <td bgcolor=#F0E68C><b>DB_Freespace<b> </td> <th>Capture_Date</th></tr>'      


SET @body = @body + @xml +'</table></body></html>'  

Set @cmd= @@SERVERNAME + ': Database Space '  

SELECT Top 1 @profile_name=name  FROM msdb.dbo.sysmail_profile  


EXEC msdb.dbo.sp_send_dbmail  

@profile_name = @profile_name, -- replace with your SQL Database Mail Profile   

@body = @body,  

@body_format ='HTML',  

@recipients =  'ajeyudub@sqldbanow.com', -- replace with your email address  

@subject = @cmd

DB_Growth status Report Alert

Hi Team,

Please find below mentioned script to get an alert DB_Growth status Report via email.

Once executed the following script and then run exec [DB_Growth] proc to get an alert


 USE [master]

GO


/****** Object:  StoredProcedure [dbo].[DB_Growth]    Script Date: 10/28/2021 5:26:50 AM ******/

SET ANSI_NULLS ON

GO


SET QUOTED_IDENTIFIER ON

GO


CREATE Proc [dbo].[DB_Growth]  

  

As  

  

Begin  

  

DECLARE @command VARCHAR(5000),@cmd Varchar (1000),@profile_name Varchar(1000)  

  

DECLARE @xml NVARCHAR(MAX)  

  

DECLARE @body NVARCHAR(MAX)  

  

  

  

DECLARE @DBSpaceInfo TABLE     

  

( ServerName VARCHAR(100),     

  

  DatabaseName VARCHAR(100),   

  

  PhysicalFileName NVARCHAR(520),     

  

  FileSizeMB DECIMAL(10,2),     

  

  SpaceUsedMB DECIMAL(10,2),    

  

  FreeSpaceMB DECIMAL(10,2),   

  

  FreeSpacePct varchar(8),  

  

  Capture_Date Datetime   

  

)   

  

  

  

 SELECT @command = 'Use [' + '?' + '] SELECT     

  

 @@servername as ServerName,     

  

 ' + '''' + '?' + '''' + ' AS DatabaseName   , name   

  

, convert(decimal(12,2),round(a.size/128.000,2)) as FileSizeMB   

  

, convert(decimal(12,2),round(fileproperty(a.name,'+''''+'SpaceUsed'+''''+')/128.000,2)) as SpaceUsedMB   

  

, convert(decimal(12,2),round((a.size-fileproperty(a.name,'+''''+'SpaceUsed'+''''+'))/128.000,2)) as FreeSpaceMB,   

  

CAST(100 * (CAST (((a.size/128.0 -CAST(FILEPROPERTY(a.name,' + '''' + 'SpaceUsed' + '''' + ' ) AS int)/128.0)/(a.size/128.0)) AS decimal(4,2))) AS varchar(8)) + ' + '''' + '%' + '''' + ' AS FreeSpacePct,  

  

Getdate() As Capture_Date  

  

from sys.database_files a'   

  

  

  

INSERT INTO @DBSpaceInfo   

  

EXEC sp_MSForEachDB @command     

  

  

  

  

  

SET @xml = CAST(( SELECT DB.ServerName As 'td',' ',DB.DatabaseName AS 'td' ,' ',DB.PhysicalFileName as 'td' ,' ',DB.FileSizeMB as 'td',' ',Db.SpaceUsedMB as 'td',  

  

' ',DB.FreeSpaceMB as 'td' ,' ',DB.FreeSpacePct as 'td',' ', convert(varchar(20), DB.Capture_Date,120) as 'td'  from @DBSpaceInfo  DB  

  

 FOR XML PATH('tr'), ELEMENTS ) AS NVARCHAR(MAX))  

  

  

  

  

  

SET @body ='<html><body><H3>Database Growth status </H3>  

  

<table border = 1>   

  

<th> Server Name </th><th> Database Name </th> <th> PhysicalFileName </th> <th> FileSizeMB </th> <th> SpaceUsedMB </th> <th> FreeSpaceMB </th>  

  

<td bgcolor=#F0E68C><b>FreeSpace(%)<b> </td> <th>Capture_Date</th></tr>'      

  

--<th> Server Name </th><th> Database Name </th> <th> PhysicalFileName </th> <th> FileSizeMB </th> <th> SpaceUsedMB </th> <th> FreeSpaceMB </th><th> FreeSpace(%) </th> <th>Capture_Date</th></tr>'      

  

   

  

SET @body = @body + @xml +'</table></body></html>'  

  

  

  

Set @cmd= @@SERVERNAME + ': Database Growth status '  

  

  

  

SELECT Top 1 @profile_name=name  FROM msdb.dbo.sysmail_profile  

  

  

  

EXEC msdb.dbo.sp_send_dbmail  

  

@profile_name = @profile_name, -- replace with your SQL Database Mail Profile   

  

@body = @body,  

  

@body_format ='HTML',  

  

@recipients = 'sqldbanow@gmail.com', -- replace with your email address  

  

@subject = @cmd  

  

End  

 

GO






Configure Deadlock Alert Using T-SQL in SQL Server

-- Here is a script to configure Deadlock Alert using t-sql


USE msdb

GO

 

DECLARE @perfcond NVARCHAR (100);

DECLARE @sqlversion TINYINT ;

 

-- get the major version of sql running

SELECT  @sqlversion = ca2.Ver

FROM    (SELECT CONVERT(VARCHAR (20),

                    SERVERPROPERTY('ProductVersion' )) AS Ver) dt1

        CROSS APPLY (SELECT CHARINDEX( '.', dt1 .Ver) AS Pos ) ca1

        CROSS APPLY (SELECT SUBSTRING( dt1.Ver , 1, ca1.Pos -1) AS Ver ) ca2;

 

-- handle the performance condition depending on the version of sql running

-- and whether this is a named instance or a default instance.

 

SELECT  @perfcond =

        CASE WHEN @sqlversion >= 11 THEN ''

        ELSE ISNULL (N'MSSQL$' +

                CONVERT(sysname , SERVERPROPERTY ('InstanceName')), N'SQLServer') + N':'

        END +

        N'Locks|Number of Deadlocks/sec|_Total|>|0' ;

 

EXEC msdb. dbo.sp_add_alert

    @name= N'Deadlock Alert',

    @message_id =0,

    @severity =0,

    @enabled =1,

    @delay_between_responses =0,

    @include_event_description_in =0,

    @category_name =N'[Uncategorized]',

    @performance_condition =@perfcond,

    --@job_name=N'Job to run when a deadlock happens, if applicable'

    -- or

    @job_id =N'00000000-0000-0000-0000-000000000000'

GO

 

EXEC msdb. dbo.sp_add_notification

    @alert_name = N'Deadlock Alert',

    @notification_method = 1, --email

    @operator_name = N'<operatorName>' ; -- name of the operator to notify

GO

Blocking

 

Blocking

user1 running a command on table1 which is still executing at the same time other user try to read/update the data from/to same table then user1 query will block the user2 Query.

 




 

 

 

Step1: Find the Blocking

There are number of ways to find out the details of the system processes IDs (spids) involved in blocking.

1.     sp_who2 System Stored Procedure

2.     sys.dm_exec_requests DMV

3.     sys.sysprocesses

4.     Sys.dm_os_waiting_tasks

5.     SQL Server Management Studio Activity Monitor

6.     SQL Server Management Studio Reports

7.     SQL Server Profiler

 

1.sp_who2 System Stored Procedure

The sp_who2 system stored procedure provides information about the current SQL Server processes with the associated SPID, status,login,Hostname,BlkBy,DBName,Command,CPU time, etc. The information returned can be filtered to return only the active processes by using the ‘active' parameter.

Below is some sample code and a screen shot with showing process 54 being blocked by process 53.

1.USE Master

Go

Sp_Who2

Go

 

2.USE Master

Go

Sp_Who2 active

Go

 



 

2.  sys.dm_exec_requests DMV

The sys.dm_exec_requests DMV provides details on all of the processes running in SQL Server. With the WHERE condition listed below, only blocked processes will be returned.

Use master

Go

select  * from sys.dm_exec_requests

where  blocking_session_id<>0



3. sys.sysprocesses

 

The sys.sysprocesses provides details on all of the processes running in SQL Server. With the WHERE condition listed below, only blocked processes will be returned.

Use master

GO

Select * from sys.sysprocesses where blocked<>0

 



4.sys.dm_os_waiting_tasks DMV

The sys.dm_os_waiting_tasks DMV returns information about the tasks that are waiting on resources.

USE Master

GO

SELECT session_id, wait_duration_ms, wait_type, blocking_session_id

FROM sys.dm_os_waiting_tasks

WHERE blocking_session_id <> 0

GO



5.SQL Server Management Studio Activity Monitor

It is easy procedure to do find the blocking from SSMS.

Open SSMS->Connect instance->Right click on instance-> select 'Activity Monitor'->Processes



OR





 

6.SQL Server Management Studio Reports

We can monitor the blocking on standard reports.

Open SSMS->Connect instance->Right click on instance->  Reports -> Standard Reports-> Activity - All Blocking Transactions.






 

7.SQL Server Profiler

To capture blocking related data on a continuous basis, one option is to run SQL Server Profiler and save the data to a table or file for analysis purposes. In order to configure Profiler to capture blocking related data, execute Profiler, configure the general properties. In addition, be sure to configure the 'blocked process threshold' before you start Profiler using this code:

sp_configure 'show advanced options', 1

GO

RECONFIGURE

GO

sp_configure 'blocked process threshold', 20

GO

RECONFIGURE

GO

Menu Bar->Tools->SQL Profiler->Connect the Instance->Select use template as TSQL-Lock->click on column filter->Select Database->Give the database name->ok->Run

 

Note: Don’t do this process in business hours. It causes a performance issue. Without change we can’t run the profiler.

  



 

Step2:INPUTBUFFER

Find the command which is running under SPID by using DBCCDBCC INPUTBUFFER(53)



Step3:KILL SPID

Kill the transaction by using below command

KILL 53

 

Note:

1.If select then kill the SPID ( Select command is not modify the data)

2. There is any other command part of the blocking then we can take the user/application team approval then we will kill it

 

Bandaru Ajeyudu

SQL and Azure DBA Trainer

Microsoft & Azure SQL DBA certified.

91-9494947541

Sqldbanow.com

ajeyudub@gmail.com                    

T-SQL query to get tables size in GB with no of rows in SQL server

Hi All,

Please find the below T-SQL script to get the tables size in GB with number of rows in SQL Server Database.


Use DB_Name;  --Change your database here

go

SELECT 

 s.name + '.' + t.Name AS [Table Name],

 part.rows AS [Total Rows In Table - Modified],

 CAST((SUM( DISTINCT au.Total_pages) * 8 ) / 1024.000 / 1024.000 AS NUMERIC(18, 3)) 

 AS [Table's Total Space In GB]

FROM 

 SYS.Tables t INNER JOIN sys.schemas s ON t.schema_id = s.schema_id

 INNER JOIN SYS.Indexes idx ON t.Object_id = idx.Object_id

 INNER JOIN SYS.Partitions part ON idx.Object_id = part.Object_id 

                    AND idx.Index_id = part.Index_id

 INNER JOIN SYS.Allocation_units au ON part.Partition_id = au.Container_id

 INNER JOIN SYS.Filegroups fGrp ON idx.Data_space_id = fGrp.Data_space_id

 INNER JOIN SYS.Database_files Df ON Df.Data_space_id = fGrp.Data_space_id

WHERE t.Is_ms_shipped = 0 AND idx.Object_id > 255 

GROUP BY t.Name, s.name, part.rows

ORDER BY [Table's Total Space In GB] DESC


Retrieve SSRS report schedule info through SQL

 --Retrieve SSRS report schedule info through SQL

Grabs information about what reports in SSRS are scheduled, when are they due to be sent out, 

report parameters and any errors in the last run etc.

Please note that the XPath on the [Parameters] column varies based on what parameters are setup in your own report. 

Please adjust them for your own purpose. Also the 'Prompt' value for a parameter is not stored, so you might have to 

join back to the relevant table for a friendly name.

Also there is additional info in the [ExtensionSetting].

=============================================================

  SELECT

[Locale],

[InactiveFlags],

'Next Run Date' = CASE next_run_date

WHEN 0 THEN null

ELSE

substring(convert(varchar(15),next_run_date),1,4) + '/' +

substring(convert(varchar(15),next_run_date),5,2) + '/' +

substring(convert(varchar(15),next_run_date),7,2)

END,

'Next Run Time' = isnull(CASE len(next_run_time)

WHEN 3 THEN cast('00:0'

+ Left(right(next_run_time,3),1)

+':' + right(next_run_time,2) as char (8))

WHEN 4 THEN cast('00:'

+ Left(right(next_run_time,4),2)

+':' + right(next_run_time,2) as char (8))

WHEN 5 THEN cast('0' + Left(right(next_run_time,5),1)

+':' + Left(right(next_run_time,4),2)

+':' + right(next_run_time,2) as char (8))

WHEN 6 THEN cast(Left(right(next_run_time,6),2)

+':' + Left(right(next_run_time,4),2)

+':' + right(next_run_time,2) as char (8))

END,'NA'),

Convert(XML,[ExtensionSettings]).value('(//ParameterValue/Value[../Name="TO"])[1]','nvarchar(50)') as [To]

,Convert(XML,[ExtensionSettings]).value('(//ParameterValue/Value[../Name="RenderFormat"])[1]','nvarchar(50)') as [Render Format]

,Convert(XML,[ExtensionSettings]).value('(//ParameterValue/Value[../Name="Subject"])[1]','nvarchar(50)') as [Subject]

        ---Example report parameters: StartDateMacro, EndDateMacro & Currency.

,Convert(XML,[Parameters]).value('(//ParameterValue/Value[../Name="StartDateMacro"])[1]','nvarchar(50)') as [Start Date]

,Convert(XML,[Parameters]).value('(//ParameterValue/Value[../Name="EndDateMacro"])[1]','nvarchar(50)') as [End Date]

,Convert(XML,[Parameters]).value('(//ParameterValue/Value[../Name="Currency"])[1]','nvarchar(50)') as [Currency]

,[ModifiedDate]

,S.[Description]

,[LastStatus]

,[EventType]

,[LastRunTime]

,[DeliveryExtension]

,[Version]

FROM [ReportServer$LIVE].[dbo].[Subscriptions] S

INNER JOIN ReportServer$LIVE.dbo.ReportSchedule R ON S.SubscriptionID = R.SubscriptionID

INNER JOIN msdb.dbo.sysjobs J ON Convert(nvarchar(128),R.ScheduleID) = J.name

INNER JOIN msdb.dbo.sysjobschedules JS ON J.job_id = JS.job_id


TempDB-Move TempDB Script in SQL Server

Hi All,

Please find the below mentioned script to generate the Move tempdb script in SQL Server 

 --Move_tempdb.sql

/*

Author: Brent Ozar

Original link: https://www.brentozar.com/archive/2017/11/move-tempdb-another-drive-folder/

*/

USE MASTER

GO

SELECT 'ALTER DATABASE tempdb MODIFY FILE (NAME = [' + f.name + '],'

    + ' FILENAME = ''Z:\MSSQL\DATA\' + f.name

    + CASE WHEN f.type = 1 THEN '.ldf' ELSE '.mdf' END

    + ''');'

FROM sys.master_files f

WHERE f.database_id = DB_ID(N'tempdb');


Notification --Email SP Results in SQL Server

 --Notification --Email SP Results

--https://www.sqlservercentral.com/scripts/email-sp-results

This piece of code will allow you to add a stored procedure name as a parameter, and will then take the output results of the procedure and email it to an address of your choosing, automatically formatting the results into an HTML table embedded in the email for clear and easy reading, or alternatively as an attachment.


Regretfully, there are a few limitations which I will eventually get around to fixing when I am interested in this again, namely:


Uses a static 100 length on the output columns instead of dynamically applying a length by referencing the source data

Cannot use procedures that have a blank column aliased ie.SELECT  ' ' as Store FROM table.

Requires Ad Hoc Distributed Queries server setting to be enabled.

Change cursors into tally or alternate loop

Customization of colours, table length, font etc.

It is executed as follows:


EXEC DBAdmin.[dbo].[dba_EmailProcResults] @vcProcName='sp_lock'


,@vcDatabaseName='master'


, @vcRecipients='<UrEmailHere>'


, @vcDynamicSubject = 'Log Backup Check'

=============================================================

 USE [tempdb]

GO

CREATE PROC [dbo].[dba_EmailProcResults]  --'dba_CheckLogbackups','DBAdmin', 'nicholaswilliams@', @vcDynamicSubject = 'Log Backup Check'

@vcProcName VARCHAR(1000)

,@vcDatabaseName VARCHAR(100)

,@vcRecipients VARCHAR(1000)

,@vcSchemaName VARCHAR(100) = NULL

,@bAttachQueryAsFile BIT = 0

,@vcDynamicSubject VARCHAR(100) = 'Query results'


AS

/*

sp_configure 'Show Advanced Options', 1

GO

RECONFIGURE

GO

sp_configure 'Ad Hoc Distributed Queries', 1

GO

RECONFIGURE

GO

*/

/*

Author: Nicholas Williams

Date: 20140519

Desc: Uses a proc as in input and email's the results.


Note: still plenty to update...

Procs cant reference non existant objects, even if logic would prevent them from being used ie. conditional if clause refering to non-existent table, as the remote call seems to force a parse on the query.

yet to make the table columns "smart" in terms of data lengths.

Requires Distributed Queries :/

add in a '?' reference help parameter

Cannot handle '' or blank string columns aliased as an actual column... need to fix.


@attach_query_result_as_file = 1,

    @query_attachment_filename='filename.csv',

*/

SET NOCOUNT ON

DECLARE

@vcServerName VARCHAR(100)

,@vcString VARCHAR(325)

,@vcColumnName VARCHAR(200)

,@nvcTable1 NVARCHAR(MAX)

,@nvcQuery NVARCHAR(MAX)

,@nvcBody NVARCHAR(MAX)

,@vcHeader VARCHAR(1000)

,@vcTrailer VARCHAR(1000)


DECLARE @MyTable TABLE

(

string NVARCHAR(MAX)

)


IF (@vcSchemaName) IS NULL

BEGIN

SET @vcSchemaName = 'dbo'

PRINT 'Warning: Schema name was null, setting it to dbo as a default.'

END


SET @vcServerName = 'Server='+(SELECT @@SERVERNAME)+';Trusted_Connection=yes;'

SELECT @vcString = 'EXEC ['+@vcDatabaseName+'].['+@vcSchemaName+'].'+(REPLACE(@vcProcName, '''',''''''))+''


EXEC ('

SELECT * INTO ##MyTempTable 

FROM OPENROWSET(''SQLNCLI'', '''+@vcServerName+''','''+@vcString+''')

')

IF (SELECT COUNT(1) FROM ##MyTempTable) >=1

BEGIN


IF @bAttachQueryAsFile <> 1

BEGIN

SET @vcHeader = '

<html><style>table{font-family:Arial;font-size:10;background=black;

color:Black;}html, body, title, 

head{font-family:Arial;font-size:10;background:white;color:Black;}th{font-family:Arial;font-size:15;background=orange;color:Black;}

{font-family:Arial;font-size:10;color:Black;background:White;align:left;valign:top;}</style><body><p><font face="Arial" size="2">Greetings,<br><br>Please see below.<br></font></p>'

SET @vcTrailer = '</body></html>'

--SELECT * FROM ##MyTempTable

SET @nvcTable1 = 

'<br>'+

'<table width=600 border=1 cellspacing=0 cellpadding=5>'+

'<tr style="background-color: White;">'

SET @nvcQuery = '

SELECT CAST((SELECT '

DECLARE column_cursor SCROLL CURSOR FOR

SELECT name FROM tempdb.sys.columns

WHERE object_id = 

(

SELECT id FROM tempdb.dbo.sysobjects

WHERE id = OBJECT_ID('tempdb..##MyTempTable')

)

/*

Need to put in a check to ensure that the result set is less than 4MB, so that it can be emailed.

*/

OPEN column_cursor

/*

Note: The odd use of the double fetch statement here is to force the force record of the fetch to be formatted differently, ie. no "," for the first record of the select.

hmm... now that I have changed how the select statement is used, i dont think i need it in the cursor anymore... change later.

*/

   FETCH FIRST FROM column_cursor

   INTO @vcColumnName


    SET @nvcQuery = ISNULL(@nvcQuery,'')+

'

['+@vcColumnName+'] AS ''td'','''''

SET @nvcTable1 = @nvcTable1 +

'<th width=100>'+@vcColumnName+'</th>'

   FETCH NEXT FROM column_cursor

   INTO @vcColumnName


WHILE @@FETCH_STATUS = 0

BEGIN 

SET @nvcTable1 = @nvcTable1 +

'<th width=100>'+@vcColumnName+'</th>'


SET @nvcQuery = ISNULL(@nvcQuery,'')+

'

,['+@vcColumnName+'] AS ''td'','''''


   FETCH NEXT FROM column_cursor

   INTO @vcColumnName


END

SET @nvcTable1 = @nvcTable1 +

'</tr>'

SET @nvcQuery = @nvcQuery+ 

'

FROM ##MyTempTable

FOR XML PATH(''tr''), TYPE) AS NVARCHAR(MAX))'

SET @vcTrailer= '</table><br><br><font face="Arial" size="2"><p>Regards</p><font face="Arial" size="2"><p>UrCompanyName<br></font><font size="1">Microsoft SQL Sever<br><br>Sender info:<br>'+'User - '+SYSTEM_USER+'<br>'+'Procedure - '+@vcProcName+'<br>'+'Database - '+@vcDatabaseName+'<br>'+'Server - '+isnull(@@SERVERNAME,'')+'<br></font></p>'

CLOSE column_cursor

DEALLOCATE column_cursor


INSERT INTO @MyTable

EXEC (@nvcQuery)


SELECT @nvcQuery = String FROM @MyTable

SELECT @nvcBody = @vcHeader+@nvcTable1+@nvcQuery+@vcTrailer

--SELECT @nvcBody


EXEC msdb.dbo.sp_send_dbmail   

@recipients = @vcRecipients

,@subject = @vcDynamicSubject

,@body = @nvcBody

,@body_format = 'HTML'  


END

ELSE

BEGIN

EXEC msdb.dbo.sp_send_dbmail   

@recipients = @vcRecipients

,@subject = @vcDynamicSubject

,@body = 'Please View attached file for query results.'

,@body_format = 'Text' 

,@attach_query_result_as_file = 1

,@query_attachment_filename = 'QueryResults.xls'

,@query_result_separator = ' '

,@query_result_no_padding = 0

,@query = 'SET NOCOUNT ON SELECT * FROM ##MyTempTable SET NOCOUNT OFF'

END


END

DROP TABLE ##MyTempTable

SET NOCOUNT OFF


/*

set a variable for the table width and the column widths.

add to each length for table for each iteration of the cursor.

look at sys.columns table for data type of the columns to manage table size effectively.

-- td = tranDate,''''

*/

SET NOCOUNT OFF


T-SQL Script to get SQL Data & Log Files Free Space & Space Used of all databases

 --TSQL Script to get SQL Data & Log Files Free Space & Space Used of all databases


Create Table #dbInfo (dId smallint, dbName sysname, gId smallint NULL, segName varchar(256) NULL, 

       filName varchar(520) NULL, sizeMB decimal(10,2) null, 

       usedMB decimal(10,2) null, freeMB decimal(10,2) null, 

       pcntUsed decimal(10,2) null, pcntFree decimal(10,2) null)

Declare @sSql varchar(1000)

Set @sSql = 'Use [?];

Insert #dbInfo (dId, dbName, gid, segName, filName, sizeMB, usedMB)

Select db_id(), db_name(), groupid, rtrim(name), filename, Cast(size/128.0 As Decimal(10,2)), 

Cast(Fileproperty(name, ''SpaceUsed'')/128.0 As Decimal(10,2))

From dbo.sysfiles Order By groupId Desc;'

Exec sp_MSforeachdb @sSql

Update #dbInfo Set

freeMB = sizeMB - usedMB,

pcntUsed = (usedMB/sizeMB)*100,

pcntFree = ((sizeMB-usedMB)/sizeMB)*100


select * from #dbInfo 

--where dbname in ('works')

--where filname like '%G:\%' order by freemb desc -- Put drive name here for files residing on specific drive

drop table #dbInfo