Find only characters or only integer from alphanumeric value

Find only characters from string :

DECLARE @Temp VARCHAR(100)='548STA123Ldfgh45df45df5446fg54645dfg546';
Declare @NumRange AS varchar(50) = '%[0-9]%';
    While PatIndex(@NumRange, @Temp) > 0
        SET @Temp = Stuff(@Temp, PatIndex(@NumRange, @Temp), 1 ,'')

 SELECT @Temp

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

Find only integers from string :

DECLARE @var VARCHAR(100)='5STA123Ldfgh45df45df5446fg54645dfg546';
DECLARE @intdata INT
SET @intdata = PATINDEX('%[^0-9]%', @var)
BEGIN
WHILE @intdata > 0
BEGIN
SET @var = STUFF(@var, @intdata, 1, '' )
SET @intdata = PATINDEX('%[^0-9]%', @var )
END
END

Select ISNULL(@var,0)

Find a float value from string :

DECLARE @var VARCHAR(100)='STA123.26dfg';
DECLARE @intdata INT
SET @intdata = PATINDEX('%[^0-9]%', @var)
BEGIN
WHILE @intdata > 0
BEGIN
SET @var = STUFF(@var, @intdata, 1, '' )
SET @intdata = PATINDEX('%[^0-9.]%', @var )
END
END

Select ISNULL(@var,0)

How to find the Database Status Report Through Alert

This one of the most important DBA Alert you should know the Status of the database ( online , Offline , suspected, Recovery , Read only, Emergency …etc)

USE [msdb]
GO

/****** Object:  StoredProcedure [dbo].[DatabaseStatusReport]    Script Date: 02-Feb-16 1:37:39 AM ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

-- =============================================
-- Author: Rajendra Yadav
-- Create date: 02/02/2016 1:37:39 AM
-- Description: Check Database Status
-- =============================================
CREATE PROC [dbo].[DatabaseStatusReport]
@profile_name_P NVARCHAR(500) ='SQLProfile',@recipients_P NVARCHAR(500) ='raja09.mcp@gmail.com'
AS
BEGIN
SET NOCOUNT ON
DECLARE @p_subject NVARCHAR(500)
SET @p_subject = N'Offline Databases Report on DB Cluster ' + ( CAST((SELECT SERVERPROPERTY('ServerName')) AS NVARCHAR))

--Send the mail as table Formate
IF(SELECT COUNT(*) FROM sys.databases WHERE state_desc<>'Online')>0
BEGIN
DECLARE @table NVARCHAR(MAX) ;
SET @table =
N'<H2 style=" color: red; " >Offline Databases Report</H2>' +
N' <span style=” font-size: 16px;” >Urgnet this list by Databases not Accessible please take immediate action to fix it </span>' +
N'<table border="1">' +
N'<tr><th>Database Name</th><th>Database Status</th></tr>' +
CAST ( ( SELECT td=name, '',td=state_desc FROM sys.databases WHERE state_desc<>'Online'
FOR XML PATH('tr'), TYPE
) AS NVARCHAR(MAX) ) +
N'</table>' ;
EXEC msdb.dbo.sp_send_dbmail
@profile_name='SQLProfile', --Change to your Profile Name
@recipients='raja09.mcp@gmail.com', --Put the email address of those who want to receive the e-mail
@subject = @p_subject ,
@body = @table,
@body_format = 'HTML' ;
END
ELSE PRINT 'All Databases are Online'
END
GO

By this Stored Procedure any database will be under any status Except ONLINE status we will receive immediately  to receive email you should configure Database mail as i told you before plus you should create scheduled job to run every 10 or 15 Seconds(Depends) to Execute this Stored procedure.

Execute Stored procedure :

USE MSDB
GO
Exec DatabaseStatusReport 'SQLProfile','raja09.mcp@gmail.com'

How to remove Database mail log history by using script

      I have created a script to remove last 30 days mail log.First i have tried to remove all logs older than

 30 days but the situation is i have a mail log of last 6 months and deleting all records at a time also 

creates a space issue while deleting. So, i have tried to delete last 30 days data and than shrink  the file 

and than remove the last 30 days data again and this way i have completed disk space
issue and purge mail history.





Use MSDB


Go

DECLARE @Deletelastmonth DATETIME

-- Remove History of last 30 Days

SELECT @Deletelastmonth = Min(Sent_date) 

FROM DBO.Sysmail_mailitems

SET @Deletelastmonth =@Deletelastmonth + 30

/*Sysmail_attachments and Sysmail_send_retries table has a foreign key from Sysmail_mailitems 

so need to remove data from both the tables first.

Need to remove attachements otherwise you can't purge mailitem history.

*/

DELETE FROM DBO.Sysmail_attachments

WHERE  Last_mod_date <= @Deletelastmonth

--Need to remove entries from retries table too. 

DELETE FROM DBO.Sysmail_send_retries

WHERE  Last_send_attempt_date <= @Deletelastmonth

EXEC Sysmail_delete_mailitems_sp @Sent_before = @Deletelastmonth

EXEC Sysmail_delete_log_sp @Logged_before= @Deletelastmonth

How to get the tables size and No.of Rows in each table

USE <Database_Name_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

T-SQL Script to find out all databases size on a SQL Server

The below script is used to displays size in MB`s and File wise

SELECT [Database Name] = DB_NAME(database_id),
       [Type] = CASE WHEN Type_Desc = 'ROWS' THEN 'Data File(s)'
                     WHEN Type_Desc = 'LOG'  THEN 'Log File(s)'
                     ELSE Type_Desc END,
       [Size in MB] = CAST( ((SUM(Size)* 8) / 1024.0) AS DECIMAL(18,2) )
FROM   sys.master_files
-- WHERE      database_id = DB_ID(‘Database Name’)
GROUP BY      GROUPING SETS
              (
                     (DB_NAME(database_id), Type_Desc),
                     (DB_NAME(database_id))
              )
ORDER BY      DB_NAME(database_id), Type_Desc DESC
GO


The below Script  is used to display the size in GB's

SELECT
  dbs.NAME,
  CAST((SUM(mFiles.SIZE) * 8 / 1024.00 / 1024.00) AS NUMERIC(18,2))
      AS [DB SIZE (In GB)]
FROM
   SYS.MASTER_FILES mFiles INNER JOIN SYS.DATABASES dbs
      ON dbs.DATABASE_ID = mFiles.DATABASE_ID
--WHERE dbs.DATABASE_ID > 4
GROUP BY dbs.NAME
ORDER BY [DB SIZE (In GB)]

Transact-SQL script to analyse the database size growth using backup history.

 This Transact-SQL script uses the backup history to analyse the growth of the databases size over a given period.
Additional to the minimul, maximum and average size per month the growth of average size related to the former month is calculated.
The values are useful for future resource planning of the storage and backup system.

Works with MS SQL Server 2005 and higher versions in all editions.
Requires access and select permissions to the MSDB system database.


DECLARE @endDate datetime, @months smallint;
SET @endDate = GetDate();  -- Include in the statistic all backups from today
SET @months = 6;           -- 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 NOT BS.database_name IN
              ('master', 'msdb', 'model', 'tempdb')
          AND 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.MinSizeMB
      ,MAIN.MaxSizeMB
      ,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

Script to get Long Running queries, current running queries in SQL Server

Run following query to find longest running query using T-SQL.

SELECT DISTINCT TOP 10 t.TEXT QueryName,s.execution_count AS ExecutionCount,
s.max_elapsed_time AS MaxElapsedTime,ISNULL (s.total_elapsed_time / s.execution_count, 0)AS AvgElapsedTime,
s.creation_time AS LogCreatedOn,ISNULL (s.execution_count /DATEDIFF(s, s.creation_time,GETDATE()), 0)AS FrequencyPerSec
FROM sys.dm_exec_query_stats s
CROSS APPLY sys.dm_exec_sql_text( s.sql_handle) t
ORDER BY s.max_elapsed_time DESC
GO

How to identify modified objects by using SQL Script

When working on quality assurance, staging and production environments, it is very important to have control over the changes made to the database definition – objects and design.This is a simple script to help you get a list of objects in your database with their creation and modification dates.

USE RajendraDB
GO
SELECT  so.name AS ObjectName,
OBJECT_NAME(so.parent_object_id) AS ParentObjectName,
SCHEMA_NAME(so.schema_id) AS SchemaName,
so.type_desc AS ObjectType,
so.create_date AS ObjectCreationDateTime,
so.modify_date AS ObjectModificationDateTime,
so.is_published AS IsPublished
FROM    sys.objects AS so
WHERE   so.is_ms_shipped = 0
ORDER BY CASE WHEN so.parent_object_id = 0 THEN so.object_id
ELSE so.parent_object_id
END,
so.schema_id,
so.type DESC,
so.modify_date DESC,
so.create_date DESC ;
GO

Script to check db size available space

USE TempDB
       SELECT
              a.FILEID,
              CONVERT(decimal(12,2),ROUND(a.size/128.000,2)) as [FILESIZEINMB] ,
              CONVERT(decimal(12,2),ROUND(fileproperty(a.name,'SpaceUsed')/128.000,2)) as [SPACEUSEDINMB],
              CONVERT(decimal(12,2),ROUND((a.size-fileproperty(a.name,'SpaceUsed'))/128.000,2)) as [FREESPACEINMB],
              a.name as [DATABASENAME],
              a.FILENAME as [FILENAME]

       FROM      dbo.sysfiles a

Scriptto kill the existing connection

DECLARE @DBNAME VARCHAR(25)
       SET @DBNAME = DB_NAME()
       USE [master]
       DECLARE @KILLSPID VARCHAR(10)
       DECLARE @SPID INT
       DECLARE SPID_Cr CURSOR FOR SELECT SPID FROM Master..sysprocesses WHERE DBID = DB_ID(@DBNAME)
       OPEN SPID_Cr
       FETCH NEXT FROM SPID_Cr INTO @SPID
       WHILE @@FETCH_STATUS = 0
       BEGIN
        SET @KILLSPID = 'KILL '+ CAST(@SPID AS VARCHAR(10))
        Exec (@KILLSPID)
       FETCH NEXT FROM SPID_Cr INTO @SPID
       END
       CLOSE SPID_Cr
       DEALLOCATE SPID_Cr

SQL Script to find out Missing Index Script

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
GO

SQL Script to find out currently executing query

SELECT r.session_id,r.command,CONVERT(NUMERIC(6,2),r.percent_complete)
AS [Percent Complete],CONVERT(VARCHAR(20),DATEADD(ms,r.estimated_completion_time,GetDate()),20) AS [ETA Completion Time],
CONVERT(NUMERIC(10,2),r.total_elapsed_time/1000.0/60.0) AS [Elapsed Min],
CONVERT(NUMERIC(10,2),r.estimated_completion_time/1000.0/60.0) AS [ETA Min],
CONVERT(NUMERIC(10,2),r.estimated_completion_time/1000.0/60.0/60.0) AS [ETA Hours],
CONVERT(VARCHAR(1000),(SELECT SUBSTRING(text,r.statement_start_offset/2,
CASE WHEN r.statement_end_offset = -1 THEN 1000 ELSE (r.statement_end_offset-r.statement_start_offset)/2 END)
FROM sys.dm_exec_sql_text(sql_handle)))
FROM sys.dm_exec_requests r WHERE command IN ('RESTORE DATABASE','BACKUP DATABASE')

SQL Script to find the list of all the jobs failed yesterday

One of the important task of any DBA is to find out all the jobs which are failed yesterday.

SELECT DISTINCT
CAST(CONVERT(datetime,CAST(run_date AS char(8)),101) AS char(11)) AS 'Failure Date',
SUBSTRING(T2.name,1,40) AS 'Job Name',
T1.step_id AS 'Step_id',
T1.step_name  AS 'Step Name',
LEFT(T1.[message],500) AS 'Error Message'
FROM msdb..sysjobhistory T1
JOIN msdb..sysjobs  T2
ON T1.job_id = T2.job_id
WHERE  T1.run_status NOT IN (1,4)
AND T1.step_id != 0
AND run_date >= CONVERT(char(8), (select dateadd (day,(-1), getdate())), 112)