SELECT s.NAME AS ColumnName ,sh.NAME + '.' + o.NAME AS ObjectName ,o.type_desc AS ObjectType ,CASE WHEN t.NAME IN ( 'char' ,'varchar' ) THEN t.NAME + '(' + CASE WHEN s.max_length < 0 THEN 'MAX' ELSE CONVERT(VARCHAR(10), s.max_length) END + ')' WHEN t.NAME IN ( 'nvarchar' ,'nchar' ) THEN t.NAME + '(' + CASE WHEN s.max_length < 0 THEN 'MAX' ELSE CONVERT(VARCHAR(10), s.max_length / 2) END + ')' WHEN t.NAME IN ('numeric') THEN t.NAME + '(' + CONVERT(VARCHAR(10), s.precision) + ',' + CONVERT(VARCHAR(10), s.scale) + ')' ELSE t.NAME END AS DataType ,CASE WHEN s.is_nullable = 1 THEN 'NULL' ELSE 'NOT NULL' END AS Nullable ,CASE WHEN ic.column_id IS NULL THEN '' ELSE ' identity(' + ISNULL(CONVERT(VARCHAR(10), ic.seed_value), '') + ',' + ISNULL(CONVERT(VARCHAR(10), ic.increment_value), '') + ')=' + ISNULL(CONVERT(VARCHAR(10), ic.last_value), 'null') END + CASE WHEN sc.column_id IS NULL THEN '' ELSE ' computed(' + ISNULL(sc.DEFINITION, '') + ')' END + CASE WHEN cc.object_id IS NULL THEN '' ELSE ' check(' + ISNULL(cc.DEFINITION, '') + ')' END AS MiscInfo ,CASE WHEN t.NAME IN ( 'char' ,'varchar' ) THEN CASE WHEN s.max_length < 0 THEN 0 ELSE CONVERT(VARCHAR(10), s.max_length) END WHEN t.NAME IN ( 'nvarchar' ,'nchar' ) THEN CASE WHEN s.max_length < 0 THEN 0 ELSE CONVERT(VARCHAR(10), s.max_length / 2) END END val INTO #temp FROM sys.columns s INNER JOIN sys.types t ON s.system_type_id = t.user_type_id AND t.is_user_defined = 0 INNER JOIN sys.objects o ON s.object_id = o.object_id INNER JOIN sys.schemas sh ON o.schema_id = sh.schema_id LEFT JOIN sys.identity_columns ic ON s.object_id = ic.object_id AND s.column_id = ic.column_id LEFT JOIN sys.computed_columns sc ON s.object_id = sc.object_id AND s.column_id = sc.column_id LEFT JOIN sys.check_constraints cc ON s.object_id = cc.parent_object_id AND s.column_id = cc.parent_column_id WHERE --t.name in ('nvarchar','nchar','char','varchar') S.NAME LIKE 'attendance%' --<--Write your columnname here ORDER BY sh.NAME + '.' + o.NAME ,s.column_id SELECT * FROM #temp --where val>500 DROP TABLE #temp
Because sharing knowledge is good. (MSBI, SQL SERVER, Python, Pyspark, Azure Data Factory, Databricks, Machine Learning & Cloud Services)
Find a column in SQL database tables
DBA Scenario 1: How to handle 100% CPU Utilization
Hi guys!
Today we shall discuss the hot topic how to handle 100% CPU Utilization. DBA
face this kind of situation often in their daily life. Some time it is must to
solve this situation in production environment as this will hamper the business
activity in terms of transactions and money.
The
methods we are about to discuss, help only if SQL server is the culprit, You
need to first identify whether SQL is consuming all memory resources, then only
you need to apply these methods otherwise it will not help you-
I shall
cover two methods; both methods are not same and can’t be used at same time.
Their aim is same to lower the memory usage but they are applied over different
scenario.
Before
begin, I want to discuss a myth that is, mostly it is assumed that longer
running queries are problem, but it is not true all the time, yes it may cause
problem but small concurrent running queries having maximum worker time or
maximum execution count can also be a problem. For example a query which is
executing before 1 sec can’t be a problem. But the same query if executed 1 lac
time concurrently (at the same time) can cause issue.
As per
my experience, mostly select queries are the culprit and create such situation
so that sql server starts consuming 100 % of memory resources. You can use task
manager or resource monitor to find the CPU usage.
Method 1
:
It is a
traditional approach, mostly used by DBAs. Whenever this kind of situation arises, you
need to first check for the intense processes running on the server. For this
you need to continuously execute one procedure sp_who2 and monitor which spid
is increasing gradually, then you need to identify what is going on that
session for that use dbcc inputbuffer(<spid>), if it is select query you
can kill it but you should not kill transaction and queries having insert
update delete on sql tables.
sp_who2
dbcc inputbuffer(<spid>)
kill <spid>
Note:
You need to look for spid greater than 50 because less than 50 spids are used
by Sql server for its internal working.
Method 2
:
It is
more granular approach to optimize the query. In this approach you need to use
few DMV’s. Sql server increases the execution_count for same query, if the
definition of the query changes new plan is created. So you need to find out
the queries having maximum exeution_count and maximum total_worker_time. When
you find the record you will get the query plan, which you need to copy and
paste to another dmv that is sys.dm_exec_query_stats.
Select * from sys.dm_exec_query_stats
order by execution_count desc
Select * from sys.dm_exec_query_stats
order by total_worker_time desc
Select * from sys.dm_exec_query_plan(<plan_handle>)
So from
above query we will get the execution plan and from there we can view xml view
of query and find parameters for the query. After getting queries you can apply
sargable rules, these rules are used to optimize queries.
This is
how you can trace costly queries and handle 100% CPU Utilization
SSRS - Download all Report files Or Single (RDL) from Report Server
Hi friends! Today my manager asked me to download all reports from Report Server and check in to TFS.
*/
There were plenty of reports almost 1000, so it is difficult to download one by one. I thought this kind of problem come to most of the DBAs so decided to write this blog, I found same script on google and modified it-
Prerequisites & Notes :
1. xp_cmdshell should be enabled. (Part 1)
2. SQL Engine Account should proper write permission on Output Folder where you will get all your reports
3. Please read comments before execution and follow (Part 2)
4. Change the Report Server database on the query
Part 1 : Enable xp_cmdshell
EXEC sp_configure 'show advanced options', 1 GO RECONFIGURE GO EXEC sp_configure 'xp_cmdshell', 1 GO RECONFIGURE GO EXEC sp_configure 'show advanced options', 0 GO RECONFIGURE GO
Part 2 : Script Implementation
/*
If you want all reports in output folder whether they were published in different folders
in SSRS Report Server then put Null else define folder name
eg: DECLARE @ReportFolderPath AS VARCHAR(500) ='/Folder1/Folder2/'
*/
DECLARE @ReportFolderPath AS VARCHAR(500) = null /* If you want some specific report then put its name else put Null eg: DECLARE @ReportName AS VARCHAR(500) = 'Bill' */ DECLARE @ReportName AS VARCHAR(500) = null /* This is the output folder where reports will be generated. Make sure Sql Server Engine Account has proper permission on this */ DECLARE @OutputFolderLocation AS VARCHAR(500) = 'C:\ReportFolder\' --To check Folder path SET @OutputFolderLocation = REPLACE(@OutputFolderLocation,'\','/') /* This for bcp access to sql server. If you want to access through sql login use eg : DECLARE @BCPComponents VARCHAR(1000)='-S"MachineName\SQL2012" -U"SA" -P"Pa$$word"' or If you want from windows account having permission on sql then eg : DECLARE @BCPComponents VARCHAR(1000)='-S"1MachineName\SQL2012"' else null */ DECLARE @BCPComponents VARCHAR(1000)= null DECLARE @SqlQuery AS NVARCHAR(MAX) IF LTRIM(RTRIM(ISNULL(@OutputFolderLocation,''))) = '' BEGIN SELECT 'Access denied!!!' END ELSE BEGIN SET @SqlQuery = STUFF((SELECT ';EXEC master..xp_cmdshell ''bcp " ' + ' SELECT ' + ' CONVERT(VARCHAR(MAX), ' + ' CASE ' + ' WHEN LEFT(C.Content,3) = 0xEFBBBF THEN STUFF(C.Content,1,3,'''''''') '+ ' ELSE C.Content '+ ' END) ' + ' FROM ' + ' [ReportServer$SQL2012].[dbo].[Catalog] CL ' + ' CROSS APPLY (SELECT CONVERT(VARBINARY(MAX),CL.Content) Content) C ' + ' WHERE ' + ' CL.ItemID = ''''' + CONVERT(VARCHAR(MAX), CL.ItemID) + ''''' " queryout "' + @OutputFolderLocation + '' + CL.Name + '.rdl" ' + '-T -c -x '+COALESCE(@BCPComponents, '')+'''' FROM [ReportServer$SQL2012].[dbo].[Catalog] CL WHERE CL.[Type] = 2 --Report AND '/' + CL.[Path] + '/' LIKE COALESCE('%/%' + @ReportFolderPath + '%/%', '/' + CL.[Path] + '/') AND CL.Name LIKE COALESCE('%' + @ReportName + '%', CL.Name) FOR XML PATH('')), 1,1,'') SELECT @SqlQuery --Execute the Dynamic Query EXEC SP_EXECUTESQL @SqlQuery END
Reference : VinayPugalia
Monitor Database Size Location through query and mailer
Hi friends! One of my friend asked me to help him creating a mailer that gives information about all databases of a server. I tried and created this-
----------------------------------------------------------------------
Use master Go
Create Table #temp
(
DatabaseName sysname,
LogicalName sysname,
physical_name varchar(500),
size decimal (18,2),
FreeSpace decimal (18,2),
[DBState] varchar(500)
)
Exec sp_msforeachdb '
Use [?];
Insert Into #temp (DatabaseName, LogicalName, physical_name, Size, FreeSpace,[DBState])
Select DB_NAME() AS [DatabaseName], Name as LogicalName, physical_name,
Cast(Cast(Round(cast(size as decimal) * 8.0/1024.0,2) as decimal(18,2)) as nvarchar) Size,
Cast(Cast(Round(cast(size as decimal) * 8.0/1024.0,2) as decimal(18,2)) -
Cast(FILEPROPERTY(name, ''SpaceUsed'') * 8.0/1024.0 as decimal(18,2)) as nvarchar) As FreeSpace,
state_desc as [DBState]
From sys.database_files
'
Select ROW_NUMBER() Over(Order by DatabaseName) RowNo, * From #temp
---------mailer
Declare @MailSubject varchar(1000)='Database Monitor Info for Servers '+Isnull(Cast(@@ServerName as varchar(500)),'')
Declare @MailBodyHeader varchar(3000)='Hi,<br><br>Please find the details of databases-<br><br>
<TABLE BORDER="0" CELLPADDING="0" CELLSPACING="0" WIDTH="100%" style="border-top:1px solid black; border-left:1px solid black;"><TBODY>
<TR><TD style="padding: 0 5px 0 5px; border-bottom:1px solid black; border-right:1px solid black;" BGCOLOR="#2f75b5" ALIGN="CENTER" colspan="7"><FONT FACE="Tahoma" SIZE="3" WIDTH="100PX" Color="white"><B>Database Monitor Info</B></FONT></TD></TR>
<TR><TD style="padding: 0 5px 0 5px; border-bottom:1px solid black; border-right:1px solid black;" BGCOLOR="#2f75b5" ALIGN="CENTER" colspan="7"><FONT FACE="Tahoma" SIZE="2" WIDTH="100PX" Color="white"><B>Status </B></FONT></TD></TR>
<TR><TD style="padding: 0 5px 0 5px; border-bottom:1px solid black; border-right:1px solid black;" BGCOLOR="#FCE4D6" ALIGN="CENTER"><FONT FACE="Tahoma" SIZE="2"><B>RowNo</B></FONT></TD>
<TD style="padding: 0 5px 0 5px; border-bottom:1px solid black; border-right:1px solid black;" BGCOLOR="#FCE4D6" ALIGN="CENTER"><FONT FACE="Tahoma" SIZE="2"><B>DatabaseName</B></FONT></TD>
<TD style="padding: 0 5px 0 5px; border-bottom:1px solid black; border-right:1px solid black;" BGCOLOR="#FCE4D6" ALIGN="CENTER"><FONT FACE="Tahoma" SIZE="2"><B>LogicalName</B></FONT></TD>
<TD style="padding: 0 5px 0 5px; border-bottom:1px solid black; border-right:1px solid black;" BGCOLOR="#FCE4D6" ALIGN="CENTER"><FONT FACE="Tahoma" SIZE="2"><B>physical_name</B></FONT></TD>
<TD style="padding: 0 5px 0 5px; border-bottom:1px solid black; border-right:1px solid black;" BGCOLOR="#FCE4D6" ALIGN="CENTER"><FONT FACE="Tahoma" SIZE="2"><B>size</B></FONT></TD>
<TD style="padding: 0 5px 0 5px; border-bottom:1px solid black; border-right:1px solid black;" BGCOLOR="#FCE4D6" ALIGN="CENTER"><FONT FACE="Tahoma" SIZE="2"><B>FreeSpace</B></FONT></TD>
<TD style="padding: 0 5px 0 5px; border-bottom:1px solid black; border-right:1px solid black;" BGCOLOR="#FCE4D6" ALIGN="CENTER"><FONT FACE="Tahoma" SIZE="2"><B>DBState</B></FONT></TD>
</TR>'
Declare @MailBody varchar(max)
Select @MailBody= Isnull(@MailBody,'')+'<TR><TD style="padding: 0 5px 0 5px; border-bottom:1px solid black; border-right:1px solid black;" BGCOLOR="white" ALIGN="CENTER"><FONT FACE="Tahoma" SIZE="2"><B>'+Isnull(Cast(ROW_NUMBER() Over(Order by DatabaseName) as varchar(10)),'')+'</B></FONT></TD>
<TD style="padding: 0 5px 0 5px; border-bottom:1px solid black; border-right:1px solid black;" BGCOLOR="white" ALIGN="Left"><FONT FACE="Tahoma" SIZE="2"><B>'+Isnull(Cast(DatabaseName as varchar(500)),'')+'</B></FONT></TD>
<TD style="padding: 0 5px 0 5px; border-bottom:1px solid black; border-right:1px solid black;" BGCOLOR="white" ALIGN="Left"><FONT FACE="Tahoma" SIZE="2">'+Isnull(Cast(LogicalName as varchar(500)),'')+'</FONT></TD>
<TD style="padding: 0 5px 0 5px; border-bottom:1px solid black; border-right:1px solid black;" BGCOLOR="white" ALIGN="Left"><FONT FACE="Tahoma" SIZE="2">'+Isnull(Cast(physical_name as varchar(2000)),'')+'</FONT></TD>
<TD style="padding: 0 5px 0 5px; border-bottom:1px solid black; border-right:1px solid black;" BGCOLOR="white" ALIGN="Center"><FONT FACE="Tahoma" SIZE="2">'+Isnull(Cast(size as varchar(50)),'')+'</FONT></TD>
<TD style="padding: 0 5px 0 5px; border-bottom:1px solid black; border-right:1px solid black;" BGCOLOR="white" ALIGN="Center"><FONT FACE="Tahoma" SIZE="2">'+Isnull(Cast(FreeSpace as varchar(50)),'')+'</FONT></TD>
<TD style="padding: 0 5px 0 5px; border-bottom:1px solid black; border-right:1px solid black;" BGCOLOR="white" ALIGN="Left"><FONT FACE="Tahoma" SIZE="2">'+Isnull(Cast(DBState as varchar(50)),'') +'</FONT></TD>
</TR>'
From #temp
Set @MAILBODY= @MailBodyHeader +@MAILBODY+'</Table>'
IF((Select Count(1) from #temp)>0)
Begin
EXEC MSDB.DBO.SP_SEND_DBMAIL
@RECIPIENTS ='vimal.lohani@gmail.com',
--@COPY_RECIPIENTS = 'vimal.lohani@gmail.com',
@BODY = @MailBody,
@SUBJECT = @MailSubject,
@BODY_FORMAT = 'HTML',
@PROFILE_NAME ='dbamail' --Your profile name
End
---------mailer enddrop table #temp
Monitor Windows Server having SQL Services Agent Services Reporting Services
Hi guys! Yesterday my boss asked me to build a tool to monitor Windows servers and Sql server services. I searched on Google and find few codes, i modified the code changed powershell concept and made below code.
In this post I shall tell you about how you monitor your all servers. The only thing you need is SQL Server which runs with an account having admin privilege on the network, or you can monitor those systems, the account having admin rights.
Lets start-
Create a database and two tables-
----------------------------------------------------------------------
Use master Go Create Database DBAMonitor GO Use DBAMonitor GO CREATE TABLE [dbo].[tbl_ServerInfo]( [Id] [int] identity(1,1) Not NULL, [WindowsServerName] [varchar](100) Not NULL, [SQLinstanceName] [varchar](100) NULL ) ON [PRIMARY] CREATE TABLE [dbo].[tbl_ServiceInfo]( [SQLServiceName] [varchar](500) NULL, [servicestatus] [varchar](100) NULL, [windowservername] [varchar](500) NULL, [startmode] [varchar](100) NULL, [UpdateDate] [datetime] NULL DEFAULT GETDATE(), [startname] [varchar](1000) NULL, [InstanceName] [varchar](1000) NULL, [ServerID] INT NULL ) ON [PRIMARY]
----------------------------------------------------------------------
Please add your server details on tbl_serverInfo you want to monitor-
----------------------------------------------------------------------
--For example
insert into [tbl_ServerInfo](windowsServerName,SQLinstanceName) values ('Proddb','Dashboard') insert into [tbl_ServerInfo](windowsServerName,SQLinstanceName) values ('192.168.0.512','sql2008r2') insert into [tbl_ServerInfo](windowsServerName,SQLinstanceName) values ('192.168.0.500','') insert into [tbl_ServerInfo](windowsServerName,SQLinstanceName) values ('192.168.0.510','')
--Checking details
----------------------------------------------------------------------
Now we need to run below script, but make sure xp_cmdshell is enabled. To enable it, please run below code-
----------------------------------------------------------------------
sp_configure 'Show Advanced Options',1 go reconfigure go sp_configure 'xp_cmdshell',1 go reconfigure go
----------------------------------------------------------------------
Now run the script and look the power-
----------------------------------------------------------------------
set NoCount on --Truncate table DBAMonitor..[tbl_ServiceInfo] -- Variable to store windows server name DECLARE @server_name varchar(100) DECLARE @SQLInstance_name varchar(100) DECLARE @SrvrID int -- table to store PowerShell script output CREATE TABLE #ServiceInfoData (line varchar(max) null) -- Declaring cursor to fetch windows server name DECLARE server_cursor CURSOR FOR select distinct LTRIM(rtrim(windowsservername)) as windowsServerName,SQLInstanceName,Id from DBAMonitor..[tbl_ServerInfo] OPEN server_cursor FETCH NEXT FROM server_cursor INTO @server_name, @SqlInstance_Name,@SrvrID WHILE @@FETCH_STATUS = 0 BEGIN declare @svrName varchar(255) declare @sql varchar(800) set @svrName = @server_name -- Preparing PowerShell Dynamic Statement set @sql = 'powershell.exe -c "get-wmiobject -Class win32_service -Computername ' + QUOTENAME(Isnull(@svrName,''),'''') + ' | where {$_.name -like ''MSSQLServer'' -OR $_.name -like ''MSSQL$' + Isnull(@SqlInstance_Name,'') + ''' -OR $_.name -like ''SQLSERVERAGENT'' -OR $_.name -like ''ReportServer$' + Isnull(@SqlInstance_Name,'') + ''' -OR $_.name -like ''SQLAgent$' + Isnull(@SqlInstance_Name,'') + ''' } | select-object Name,state,systemname,startmode,startname | %{$_.name+''|''+$_.state+''%''+$_.systemname+''*''+$_.startmode+''@''+$_.startname+''!''} "' -- Inserting PowerShell Output to temporary table --print @sql insert #ServiceInfoData EXEC xp_cmdshell @sql -- Deleting the rows which contains error or has not sufficient data delete from #ServiceInfoData where len(line) < 30 update #ServiceInfoData set line = line + '!' where line not like '%!%' IF (SELECT COUNT(*) FROM #ServiceInfoData where line like '%Get-Wmi%') = 0 begin insert into DBAMonitor..[tbl_ServiceInfo](SQLServiceName,servicestatus,windowservername,startmode,startname,InstanceName,ServerId) select rtrim(ltrim(SUBSTRING(line,1,CHARINDEX('|',line) -1))) as SQLServiceName ,(rtrim(ltrim(SUBSTRING(line,CHARINDEX('|',line)+1, (CHARINDEX('%',line) -1)-CHARINDEX('|',line)) ))) as ServiceStatus --,(rtrim(ltrim(SUBSTRING(line,CHARINDEX('%',line)+1, (CHARINDEX('*',line) -1)-CHARINDEX('%',line)) ))) as WindowsServerName ,@server_name ,(rtrim(ltrim(SUBSTRING(line,CHARINDEX('*',line)+1, (CHARINDEX('@',line) -1)-CHARINDEX('*',line)) ))) as startmode ,(rtrim(ltrim(SUBSTRING(line,CHARINDEX('@',line)+1, (CHARINDEX('!',line) -1)-CHARINDEX('@',line)) ))) as startname ,@SQLInstance_name ,@SrvrID from #ServiceInfoData where line is not null and LEN(line) > 30 end -- Clearing output table truncate table #ServiceInfoData -- Next windows record FETCH NEXT FROM server_cursor INTO @server_name,@SQLInstance_name,@SrvrID END CLOSE server_cursor; DEALLOCATE server_cursor; -- dropping the temporary table drop table #ServiceInfoData Select Srvr.windowsServerName ,Srvr.SQLinstanceName ,sevi.SQLServiceName ,sevi.startname [ServiceAccount] ,sevi.servicestatus ,sevi.UpdateDate [StatusTime] ,Case when sevi.serverid is null Then 'Server is down or Access denied!!' Else 'Check status!!' End [Remarks] From DBAMonitor..[tbl_ServerInfo] srvr Left Join DBAMonitor..[tbl_ServiceInfo] sevi on srvr.ID=sevi.Serverid
For better results, Create a job and run the above script from sql agent.
You can also create mailer for same to notify.
The below code is to notify if any service is stopped or any server is down.
----------------------------------------------------------------------
Declare @MailSubject varchar(1000)='Monitor info for Windows Servers and Sql Services'
Declare @MailBodyHeader varchar(3000)='Hi,<br><br>Please find the details of servers or services, which are not working perfectly-<br><br>
<TABLE BORDER="0" CELLPADDING="0" CELLSPACING="0" WIDTH="100%" style="border-top:1px solid black; border-left:1px solid black;"><TBODY>
<TR><TD style="padding: 0 5px 0 5px; border-bottom:1px solid black; border-right:1px solid black;" BGCOLOR="#2f75b5" ALIGN="CENTER" colspan="7"><FONT FACE="Tahoma" SIZE="3" WIDTH="100PX" Color="white"><B>Server Monitor Info</B></FONT></TD></TR>
<TR><TD style="padding: 0 5px 0 5px; border-bottom:1px solid black; border-right:1px solid black;" BGCOLOR="#2f75b5" ALIGN="CENTER" colspan="7"><FONT FACE="Tahoma" SIZE="2" WIDTH="100PX" Color="white"><B>Status </B></FONT></TD></TR>
<TR><TD style="padding: 0 5px 0 5px; border-bottom:1px solid black; border-right:1px solid black;" BGCOLOR="#FCE4D6" ALIGN="CENTER"><FONT FACE="Tahoma" SIZE="2"><B>Windows Server Name</B></FONT></TD>
<TD style="padding: 0 5px 0 5px; border-bottom:1px solid black; border-right:1px solid black;" BGCOLOR="#FCE4D6" ALIGN="CENTER"><FONT FACE="Tahoma" SIZE="2"><B>Sql Instance Name</B></FONT></TD>
<TD style="padding: 0 5px 0 5px; border-bottom:1px solid black; border-right:1px solid black;" BGCOLOR="#FCE4D6" ALIGN="CENTER"><FONT FACE="Tahoma" SIZE="2"><B>Sql Service Name</B></FONT></TD>
<TD style="padding: 0 5px 0 5px; border-bottom:1px solid black; border-right:1px solid black;" BGCOLOR="#FCE4D6" ALIGN="CENTER"><FONT FACE="Tahoma" SIZE="2"><B>Service Account</B></FONT></TD>
<TD style="padding: 0 5px 0 5px; border-bottom:1px solid black; border-right:1px solid black;" BGCOLOR="#FCE4D6" ALIGN="CENTER"><FONT FACE="Tahoma" SIZE="2"><B>Service Status</B></FONT></TD>
<TD style="padding: 0 5px 0 5px; border-bottom:1px solid black; border-right:1px solid black;" BGCOLOR="#FCE4D6" ALIGN="CENTER"><FONT FACE="Tahoma" SIZE="2"><B>Status Time</B></FONT></TD>
<TD style="padding: 0 5px 0 5px; border-bottom:1px solid black; border-right:1px solid black;" BGCOLOR="#FCE4D6" ALIGN="CENTER"><FONT FACE="Tahoma" SIZE="2"><B>Remarks</B></FONT></TD>
</TR>'
Declare @MailBody varchar(max)
Select @MailBody= Isnull(@MailBody,'')+'<TR><TD style="padding: 0 5px 0 5px; border-bottom:1px solid black; border-right:1px solid black;" BGCOLOR="white" ALIGN="CENTER"><FONT FACE="Tahoma" SIZE="2"><B>'+Isnull(Srvr.windowsServerName,'')+'</B></FONT></TD>
<TD style="padding: 0 5px 0 5px; border-bottom:1px solid black; border-right:1px solid black;" BGCOLOR="white" ALIGN="CENTER"><FONT FACE="Tahoma" SIZE="2"><B>'+Isnull(Srvr.SQLinstanceName,'')+'</B></FONT></TD>
<TD style="padding: 0 5px 0 5px; border-bottom:1px solid black; border-right:1px solid black;" BGCOLOR="white" ALIGN="CENTER"><FONT FACE="Tahoma" SIZE="2">'+Isnull(sevi.SQLServiceName,'')+'</FONT></TD>
<TD style="padding: 0 5px 0 5px; border-bottom:1px solid black; border-right:1px solid black;" BGCOLOR="white" ALIGN="CENTER"><FONT FACE="Tahoma" SIZE="2">'+Isnull(sevi.startname,'')+'</FONT></TD>
<TD style="padding: 0 5px 0 5px; border-bottom:1px solid black; border-right:1px solid black;" BGCOLOR="white" ALIGN="CENTER"><FONT FACE="Tahoma" SIZE="2">'+Isnull(sevi.servicestatus,'')+'</FONT></TD>
<TD style="padding: 0 5px 0 5px; border-bottom:1px solid black; border-right:1px solid black;" BGCOLOR="white" ALIGN="CENTER"><FONT FACE="Tahoma" SIZE="2">'+Isnull(Cast(sevi.UpdateDate as varchar(30)),'')+'</FONT></TD>
<TD style="padding: 0 5px 0 5px; border-bottom:1px solid black; border-right:1px solid black;" BGCOLOR="white" ALIGN="CENTER"><FONT FACE="Tahoma" SIZE="2">'+Case when sevi.serverid is null Then 'Server is down or Access denied!!' Else 'Check status!!' End +'</FONT></TD>
</TR>'
From DBAMonitor..[tbl_ServerInfo] srvr
Left Join DBAMonitor..[tbl_ServiceInfo] sevi on srvr.ID=sevi.Serverid
Where servicestatus<>'Running' or servicestatus is null
Set @MAILBODY= @MailBodyHeader +@MAILBODY+'</Table>'
IF((Select Count(1) from DBAMonitor..[tbl_ServerInfo] srvr Left Join DBAMonitor..[tbl_ServiceInfo] sevi on srvr.ID=sevi.Serverid Where servicestatus<>'Running' or servicestatus is null)>0)
Begin
EXEC MSDB.DBO.SP_SEND_DBMAIL
@RECIPIENTS ='vimal.lohani@gmail.com',
@COPY_RECIPIENTS = 'vimal.lohani@gmail.com',
@BODY = @MailBody,
@SUBJECT = @MailSubject,
@BODY_FORMAT = 'HTML',
@PROFILE_NAME ='dbamail' --Your profile name
End
----------------------------------------------------------------------
You can also schedule above script through agent.
Reference
Find a value in all columns of all tables
Declare @SearchString varchar(50) ='kishor.b'--<--Define your search string here Declare @ResultsFound TABLE(ColumnName nvarchar(370), ColumnValue nvarchar(3630)) SET NOCOUNT ON DECLARE @TableName nvarchar(256), @ColumnName nvarchar(128), @SearchStr2 nvarchar(110) SET @TableName = '' SET @SearchStr2 = QUOTENAME('%' + @SearchString + '%','''') WHILE @TableName IS NOT NULL BEGIN SET @ColumnName = '' SET @TableName = ( SELECT MIN(QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME)) FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE = 'BASE TABLE' AND QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME) > @TableName AND OBJECTPROPERTY( OBJECT_ID( QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME) ), 'IsMSShipped' ) = 0 ) WHILE (@TableName IS NOT NULL) AND (@ColumnName IS NOT NULL) BEGIN SET @ColumnName = ( SELECT MIN(QUOTENAME(COLUMN_NAME)) FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = PARSENAME(@TableName, 2) AND TABLE_NAME = PARSENAME(@TableName, 1) AND DATA_TYPE IN ('char', 'varchar', 'nchar', 'nvarchar') AND QUOTENAME(COLUMN_NAME) > @ColumnName ) IF @ColumnName IS NOT NULL BEGIN INSERT INTO @ResultsFound EXEC ( 'SELECT ''' + @TableName + '.' + @ColumnName + ''', LEFT(' + @ColumnName + ', 3630) FROM ' + @TableName + ' (NOLOCK) ' + ' WHERE ' + @ColumnName + ' LIKE ' + @SearchStr2 ) END END END SELECT ColumnName, ColumnValue FROM @ResultsFound
Find List of Year, Month,Days On The Basis Of Month Between Two Dates
Declare @StartDate date= '03/01/2015' Declare @EndDate date= '06/05/2015' select year(dt) [Year], month(dt) [Month], count(*) Days from ( select top (datediff(d, @StartDate, @EndDate)) dateadd(d, row_number() over (order by (select null)), @StartDate) dt from sys.columns ) q group by year(dt), month(dt) order by [Year], [Month]
Subscribe to:
Posts (Atom)