Tuesday, October 9, 2012

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.

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.


 

No comments:

Post a Comment

Dear User,

Thank you very much for your kind response