How to Collecting the SQL server information

SELECT CONVERT(CHAR(100), SERVERPROPERTY('Servername')) AS Server,
CONVERT(CHAR(100), SERVERPROPERTY('ProductVersion')) AS ProductVersion,
CONVERT(CHAR(100), SERVERPROPERTY('ProductLevel')) AS ProductLevel,
CONVERT(CHAR(100), SERVERPROPERTY('ResourceLastUpdateDateTime')) AS ResourceLastUpdateDateTime,
CONVERT(CHAR(100), SERVERPROPERTY('ResourceVersion')) AS ResourceVersion,
CASE WHEN SERVERPROPERTY('IsIntegratedSecurityOnly') = 1 THEN 'Integrated security'
WHEN SERVERPROPERTY('IsIntegratedSecurityOnly') = 0 THEN 'Not Integrated security'
END AS IsIntegratedSecurityOnly,

CASE WHEN SERVERPROPERTY('EngineEdition') = 1 THEN 'Personal Edition'
WHEN SERVERPROPERTY('EngineEdition') = 2 THEN 'Standard Edition'
WHEN SERVERPROPERTY('EngineEdition') = 3 THEN 'Enterprise Edition'
WHEN SERVERPROPERTY('EngineEdition') = 4 THEN 'Express Edition'
END AS EngineEdition,

CONVERT(CHAR(100), SERVERPROPERTY('InstanceName')) AS InstanceName,
CONVERT(CHAR(100), SERVERPROPERTY('ComputerNamePhysicalNetBIOS')) AS ComputerNamePhysicalNetBIOS,
CONVERT(CHAR(100), SERVERPROPERTY('LicenseType')) AS LicenseType,
CONVERT(CHAR(100), SERVERPROPERTY('NumLicenses')) AS NumLicenses,
CONVERT(CHAR(100), SERVERPROPERTY('BuildClrVersion')) AS BuildClrVersion,
CONVERT(CHAR(100), SERVERPROPERTY('Collation')) AS Collation,
CONVERT(CHAR(100), SERVERPROPERTY('CollationID')) AS CollationID,
CONVERT(CHAR(100), SERVERPROPERTY('ComparisonStyle')) AS ComparisonStyle,

CASE WHEN CONVERT(CHAR(100), SERVERPROPERTY('EditionID')) = -1253826760 THEN 'Desktop Edition'
WHEN SERVERPROPERTY('EditionID') = -1592396055 THEN 'Express Edition'
WHEN SERVERPROPERTY('EditionID') = -1534726760 THEN 'Standard Edition'
WHEN SERVERPROPERTY('EditionID') = 1333529388 THEN 'Workgroup Edition'
WHEN SERVERPROPERTY('EditionID') = 1804890536 THEN 'Enterprise Edition'
WHEN SERVERPROPERTY('EditionID') = -323382091 THEN 'Personal Edition'
WHEN SERVERPROPERTY('EditionID') = -2117995310 THEN 'Developer Edition'
WHEN SERVERPROPERTY('EditionID') = 610778273 THEN 'Enterprise Evaluation Edition'
WHEN SERVERPROPERTY('EditionID') = 1044790755 THEN 'Windows Embedded SQL'
WHEN SERVERPROPERTY('EditionID') = 4161255391 THEN 'Express Edition with Advanced Services'
END AS ProductEdition,

CASE WHEN CONVERT(CHAR(100), SERVERPROPERTY('IsClustered')) = 1 THEN 'Clustered'
WHEN SERVERPROPERTY('IsClustered') = 0 THEN 'Not Clustered'
WHEN SERVERPROPERTY('IsClustered') = NULL THEN 'Error'
END AS IsClustered,

CASE WHEN CONVERT(CHAR(100), SERVERPROPERTY('IsFullTextInstalled')) = 1 THEN 'Full-text is installed'
WHEN SERVERPROPERTY('IsFullTextInstalled') = 0 THEN 'Full-text is not installed'
WHEN SERVERPROPERTY('IsFullTextInstalled') = NULL THEN 'Error'
END AS IsFullTextInstalled,

CONVERT(CHAR(100), SERVERPROPERTY('SqlCharSet')) AS SqlCharSet,
CONVERT(CHAR(100), SERVERPROPERTY('SqlCharSetName')) AS SqlCharSetName,
CONVERT(CHAR(100), SERVERPROPERTY('SqlSortOrder')) AS SqlSortOrderID,
CONVERT(CHAR(100), SERVERPROPERTY('SqlSortOrderName')) AS SqlSortOrderName

ORDER BY CONVERT(CHAR(100), SERVERPROPERTY('Servername'))

https://docs.microsoft.com/en-us/sql/t-sql/functions/serverproperty-transact-sql?

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