Database Experts:Microsoft SQL Server 2008 R2:Get Windows Server Memory Usage info
Server Memory Usage Info
Sometime we need to know about our Windows server memory usage on daily basis.
For this purpose,Create one job which send Email regarding memory usage and running services on server.
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
/******************************************************************************/
/* Copyright: 2012 Database Experts
*
* Name : Archive: [usp_daily_user_login_mail] $
* Version : Revision:
* Last Updated : Modtime:
* Created By : $Author: Database Experts
* Modified By :
* Script : This Procedure is developed for Email the Usage of Memory.
* Input: Nothing
* Output: Nothing
* Version Info:
*
*
*/
/*****************************************************************************/
ALTER PROCEDURE [dbo].[Server_Memory_Usage]
as
BEGIN
Declare
@Subject varchar(1000),
@ProfileName varchar(100),
@Header varchar(5000),
@msg varchar(MAX),
@sql varchar(MAX),
@Footer varchar(5000),
@body_format varchar(10),
@to varchar(100),
@cc varchar(100),
@bcc varchar(100),
@file varchar(50),
@MemoryValue varchar(1000)
Create Table #MemoryUsage (MemoryValue varchar(1000))
insert into #MemoryUsage Exec xp_cmdshell 'systeminfo |find "Memory" '
Exec xp_cmdshell 'Tasklist >D:\Backup\Tasklist\Tasklist.log'
SET @file='D:\Backup\Tasklist\Tasklist.log'
SET @cc=''
SET @bcc=''
SET @Header='<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN"
"http://www.w3.org/TR/html4/loose.dtd">
<html>
<head>
<title>~RepValue1~</title>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1">
<style type="text/css">
<!--
.ContentStyle {
font-family: Arial, Helvetica, sans-serif;
font-size: 12px;
}
.ContentStyleNotes1 {
font-family: Verdana, Arial, Helvetica, sans-serif;
font-size: 10px;
}
.ContentStyleNotes2 {
font-family: Verdana, Arial, Helvetica, sans-serif;
font-size: 10px;
color:#FFFFFF;
}
.TitleStyle {
font-family: Georgia, "Times New Roman", Times, serif;
font-size: 24px;
font-style: italic;
font-weight: 300;
}
.HeaderStyle {
font-family: Georgia, "Times New Roman", Times, serif;
font-size: 12px;
font-weight: 400;
color: #FFFFFF;
}
.NotificationFont {
color: #FFFFFF;
font-weight: bold;
}
-->
</style>
</head>
<body>
<table border="0" cellspacing="1" cellpadding="0" align="center" class="ContentStyle">
<br />
<tr bordercolor="#F3F3EF">
<td align="center" class="TitleStyle">
Windows Server 2008(Server1) Memory Usage
</td>
</tr>
<br />
</table>
<table border="0" cellspacing="1" cellpadding="0" align="center" class="ContentStyle">
<tr bgcolor="#666666">
</tr>'
SET @Footer='<tr bgcolor="#666666">
<td height="20" align="center" valign="middle" class="ContentStyleNotes2">
<span class="ContentStyleNotes1">This automatically generated by Database Jobs</span>
</td>
</tr>
</table>
<br/>
<table border="0" cellpadding="0" cellspacing="1" align="center">
<tr>
<td width="540" height="40" align="center" valign="middle" class="ContentStyleNotes1">
<span>This is auto generated email by system monitoring service. Please do not reply to this email.</span><br>
<span>Powered by DecisionCraft Pvt Ltd.</span>
</td></tr>
</table> '
SET @msg=@Header
SET @to='dbexprt@dbexprt.com;'
SET @Subject='[Automated Mail] Windows Server Memory Usage Info'
SET @ProfileName='Database Mail'
SET @body_format='HTML'
DECLARE db_cursor CURSOR FOR Select *from #MemoryUsage where MemoryValue is not null
OPEN db_cursor
FETCH NEXT FROM db_cursor INTO @MemoryValue
WHILE @@FETCH_STATUS = 0
BEGIN
SET @msg=@msg+'<tr bgcolor="#C9BDBD" height="20" align="center" valign="middle">
<td>'+@MemoryValue+'</td>
</tr>'
FETCH NEXT FROM db_cursor INTO @MemoryValue
END
SET @msg=@msg+@Footer+'</body></html>'
--SET @sql='USE msdb '+
--'EXEC sp_send_dbmail @profile_name='''+@ProfileName+''',
--@recipients='''+@to+''',
--@subject='''+@Subject+''',
--@body='''+@msg +''',
--@body_format ='''+@body_format+''''
SET @sql='USE msdb '+
'EXEC sp_send_dbmail @profile_name='''+@ProfileName+''',
@recipients='''+@to+''',
@copy_recipients='''+@cc+''',
@blind_copy_recipients='''+@bcc+''',
@subject='''+@Subject+''',
@body='''+@msg +''',
@body_format ='''+@body_format+''',
@file_attachments ='''+@file+''''
print 'Mail in Queue... '
EXEC(@sql)
print 'Mailed... '
CLOSE db_cursor
DEALLOCATE db_cursor
drop table #MemoryUsage
END
2.Set into Database jobs.Schedule for everyday.
Sometime we need to know about our Windows server memory usage on daily basis.
For this purpose,Create one job which send Email regarding memory usage and running services on server.
1.Create Stored Procedure into your database.
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
/******************************************************************************/
/* Copyright: 2012 Database Experts
*
* Name : Archive: [usp_daily_user_login_mail] $
* Version : Revision:
* Last Updated : Modtime:
* Created By : $Author: Database Experts
* Modified By :
* Script : This Procedure is developed for Email the Usage of Memory.
* Input: Nothing
* Output: Nothing
* Version Info:
*
*
*/
/*****************************************************************************/
ALTER PROCEDURE [dbo].[Server_Memory_Usage]
as
BEGIN
Declare
@Subject varchar(1000),
@ProfileName varchar(100),
@Header varchar(5000),
@msg varchar(MAX),
@sql varchar(MAX),
@Footer varchar(5000),
@body_format varchar(10),
@to varchar(100),
@cc varchar(100),
@bcc varchar(100),
@file varchar(50),
@MemoryValue varchar(1000)
Create Table #MemoryUsage (MemoryValue varchar(1000))
insert into #MemoryUsage Exec xp_cmdshell 'systeminfo |find "Memory" '
Exec xp_cmdshell 'Tasklist >D:\Backup\Tasklist\Tasklist.log'
SET @file='D:\Backup\Tasklist\Tasklist.log'
SET @cc=''
SET @bcc=''
SET @Header='<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN"
"http://www.w3.org/TR/html4/loose.dtd">
<html>
<head>
<title>~RepValue1~</title>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1">
<style type="text/css">
<!--
.ContentStyle {
font-family: Arial, Helvetica, sans-serif;
font-size: 12px;
}
.ContentStyleNotes1 {
font-family: Verdana, Arial, Helvetica, sans-serif;
font-size: 10px;
}
.ContentStyleNotes2 {
font-family: Verdana, Arial, Helvetica, sans-serif;
font-size: 10px;
color:#FFFFFF;
}
.TitleStyle {
font-family: Georgia, "Times New Roman", Times, serif;
font-size: 24px;
font-style: italic;
font-weight: 300;
}
.HeaderStyle {
font-family: Georgia, "Times New Roman", Times, serif;
font-size: 12px;
font-weight: 400;
color: #FFFFFF;
}
.NotificationFont {
color: #FFFFFF;
font-weight: bold;
}
-->
</style>
</head>
<body>
<table border="0" cellspacing="1" cellpadding="0" align="center" class="ContentStyle">
<br />
<tr bordercolor="#F3F3EF">
<td align="center" class="TitleStyle">
Windows Server 2008(Server1) Memory Usage
</td>
</tr>
<br />
</table>
<table border="0" cellspacing="1" cellpadding="0" align="center" class="ContentStyle">
<tr bgcolor="#666666">
</tr>'
SET @Footer='<tr bgcolor="#666666">
<td height="20" align="center" valign="middle" class="ContentStyleNotes2">
<span class="ContentStyleNotes1">This automatically generated by Database Jobs</span>
</td>
</tr>
</table>
<br/>
<table border="0" cellpadding="0" cellspacing="1" align="center">
<tr>
<td width="540" height="40" align="center" valign="middle" class="ContentStyleNotes1">
<span>This is auto generated email by system monitoring service. Please do not reply to this email.</span><br>
<span>Powered by DecisionCraft Pvt Ltd.</span>
</td></tr>
</table> '
SET @msg=@Header
SET @to='dbexprt@dbexprt.com;'
SET @Subject='[Automated Mail] Windows Server Memory Usage Info'
SET @ProfileName='Database Mail'
SET @body_format='HTML'
DECLARE db_cursor CURSOR FOR Select *from #MemoryUsage where MemoryValue is not null
OPEN db_cursor
FETCH NEXT FROM db_cursor INTO @MemoryValue
WHILE @@FETCH_STATUS = 0
BEGIN
SET @msg=@msg+'<tr bgcolor="#C9BDBD" height="20" align="center" valign="middle">
<td>'+@MemoryValue+'</td>
</tr>'
FETCH NEXT FROM db_cursor INTO @MemoryValue
END
SET @msg=@msg+@Footer+'</body></html>'
--SET @sql='USE msdb '+
--'EXEC sp_send_dbmail @profile_name='''+@ProfileName+''',
--@recipients='''+@to+''',
--@subject='''+@Subject+''',
--@body='''+@msg +''',
--@body_format ='''+@body_format+''''
SET @sql='USE msdb '+
'EXEC sp_send_dbmail @profile_name='''+@ProfileName+''',
@recipients='''+@to+''',
@copy_recipients='''+@cc+''',
@blind_copy_recipients='''+@bcc+''',
@subject='''+@Subject+''',
@body='''+@msg +''',
@body_format ='''+@body_format+''',
@file_attachments ='''+@file+''''
print 'Mail in Queue... '
EXEC(@sql)
print 'Mailed... '
CLOSE db_cursor
DEALLOCATE db_cursor
drop table #MemoryUsage
END
2.Set into Database jobs.Schedule for everyday.
Comments
Post a Comment
Dear User,
Thank you very much for your kind response