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