SQL SERVER – Disk Space Monitoring – Detecting Low Disk Space on Server

CREATE PROCEDURE [CSMSDVLP].[DiskSpaceMonitor]
@mailProfile nvarchar(500),
@mailto nvarchar(4000),
@threshold INT,
@logfile nvarchar(4000)
AS

BEGIN

declare @count int;
declare @tempfspace int;
declare @tempdrive char(1);
declare @mailbody nvarchar(4000);
declare @altflag bit;
declare @sub nvarchar(4000);
declare @cmd nvarchar(4000);

SET @count = 0;
SET @mailbody = '';
SET @cmd = '';

SET NOCOUNT ON

--Create temp table to hold drive free space info
IF EXISTS(select * from sys.sysobjects where id = object_id('#driveinfo'))
drop table #driveinfo

create table #driveinfo(id int identity(1,1),drive char(1), fspace int)

insert into #driveinfo EXEC master..xp_fixeddrives

 SET @mailbody ='Dear Team, </br>'

--Loop through each drive to check for drive threshold
while (select count(*) from #driveinfo) >= @count
begin

set @tempfspace = (select fspace from #driveinfo where id = @count)
set @tempdrive = (select drive from #driveinfo where id = @count)

 --SET @threshold=@threshold/1024

 --select @tempfspace
 --select @tempdrive
 --select @threshold

--If free space is lower than threshold appends details to mail body and dumps the file size details into the logfile.
if @tempfspace < @threshold
BEGIN
SET @tempfspace=@tempfspace/1024
SET @altflag = 1;

SET @mailbody = @mailbody + '<p>Drive ' + CAST(@tempdrive AS NVARCHAR(10)) + ' has ' + CAST(@tempfspace AS NVARCHAR(10)) + ' GB Free in __ Server</br>'

SET @cmd = 'dir /s /-c ' + @tempdrive + ':\ > ' + @logfile
-- select @mailbody
--EXEC xp_cmdshell @cmd

END

set @count = @count + 1

end

--If atleast one drive is below threshold level sends the mail with attachment
IF (@altflag = 1)
BEGIN

SET @sub = 'Free Space is lessthan 25GB  in _______ Server ' + CAST(@@SERVERNAME AS NVARCHAR(30))
SET @mailbody = @mailbody+ '</br> Thanks & Regards, </br> Srinivas Lakkapally'
EXEC msdb.dbo.sp_send_dbmail
@profile_name = 'SendEmailSqlProfile',
@recipients= 'test@gmail.com',

@subject = @sub,
@body = @mailbody,
--@file_attachments = @logfile,
@body_format = 'HTML'

END
--select * from #driveinfo
drop table #driveinfo

set nocount off

END

Split Function in SQL Server

We use split function in our development frequently. The purpose of this function is to split single string having delimiters (comma, semicolon etc.) to convert into multiple strings. Many developers have designed it in different ways.
Given below is the split function, developed with the help of XQuery.
Let me explain this function demonstrating simple example.

Split the @variable:

--Create this user deinfed function. Create Function dbo.[UDF_Split] (                 @String VARCHAR(MAX),  -- Variable for string       @delimiter VARCHAR(50) -- Delimiter in the string ) RETURNS @Table TABLE(        --Return type of the function Splitcolumn VARCHAR(MAX) ) BEGIN      Declare @Xml AS XML  -- Replace the delimiter to the opeing and closing tag --to make it an xml document      SET @Xml = cast(('<A>'+replace(@String,@delimiter,'</A><A>')+'</A>') AS XML)  --Query this xml document via xquery to split rows --and insert it into table to return.      INSERT INTO @Table SELECT A.value('.', 'varchar(max)') as [Column] FROM @Xml.nodes('A') AS FN(A)  RETURN END GO -- For Example : SELECT * FROM dbo.[UDF_Split] ('Bob,Hulda,Jacoba',',') -- We can use this function to split table's column as well --but the issue is, if we have 5 rows in a table --This function will be executed 5 times. --I have another solution to split table's column GO

Split the Table’s Column:

CREATE TABLE [dbo].[Student](       [Student ID]   [varchar] (6) NOT NULL ,       [Student Name] [varchar](50) NOT NULL,       [Code] [varchar] (MAX)) GO Insert into dbo.[Student] values ('STD001','Bob','E1,E2,E3') Insert into dbo.[Student] values ('STD002','Alexander','X1,X2,X3') Insert into dbo.[Student] values ('STD003','Hosanna','A1,A2,A3') GO -- Here is the sample query to split table's column. Declare @delimiter VARCHAR(50) Set @delimiter=',' ;WITH Cte AS (     SELECT         [Student ID],         [Student Name],         -- Replace the delimiter to the opeing and closing tag         --to make it an xml document         CAST('<M>' + REPLACE([Code], @delimiter , '</M><M>') + '</M>' AS XML) AS [Code]     FROM  [Student] ) Select     [Student ID],     [Student Name],     --Query this xml document via xquery to split rows     Split.a.value('.', 'VARCHAR(MAX)') AS [Code] FROM Cte CROSS APPLY [Code].nodes('/M')Split(a)