MSSQL SERVER AND SYBASE ASE

My Journey as a SQL Server and Sybase DBA

How to check whether “SQL Agent” is running or not?

Scenario:  Recently, I was asked by a friend to find the status of SQL server agent job i.e. either it is in running or stopped state and if it is in a stopped state then email it to concerned DBA team.

Approach: Below is the script that I used to check the status of SQL agent:

/* schedule below script as a T-SQL agent job to run every 2 or 5 min depending on your environment. */

IF EXISTS (  SELECT 1
FROM MASTER.dbo.sysprocesses
WHERE program_name = N’SQLAgent – Generic Refresher’)
BEGIN
SELECT @@SERVERNAME AS ‘InstanceName’, 1 AS ‘SQLServerAgentRunning’
END
ELSE
BEGIN
EXEC msdb.dbo.sp_send_dbmail
@profile_name = ‘kinjal.dba test profile’,
@recipients = ‘kinjal.dba@gmail.com’,
@body = ‘Please check the status of SQL Agent. It is not running !’,
@query = ‘SELECT @@SERVERNAME AS [InstanceName], 0 AS [SQLServerAgentRunning]‘,
@subject = ‘SQL Agent is not running’,
@attach_query_result_as_file = 0 ; — set it to 1 to receive as txt file attachment
END

Enhancing the above code to a further step to know the uptime for our SQL server and check whether sql server and sql agent both are running or not.

USE master;

SET NOCOUNT ON

DECLARE @crdate DATETIME, @hr VARCHAR(50), @min VARCHAR(5)

SELECT @crdate=crdate FROM sysdatabases WHERE NAME=’tempdb’

SELECT @hr=(DATEDIFF ( mi, @crdate,GETDATE()))/60

IF ((DATEDIFF ( mi, @crdate,GETDATE()))/60)=0

SELECT @min=(DATEDIFF ( mi, @crdate,GETDATE()))

ELSE

SELECT @min=(DATEDIFF ( mi, @crdate,GETDATE()))-((DATEDIFF( mi, @crdate,GETDATE()))/60)*60

PRINT ‘SQL Server “‘ + CONVERT(VARCHAR(20),SERVERPROPERTY(‘SERVERNAME’))+’” is Online for the past ‘+@hr+’ hours & ‘+@min+’ minutes’

IF NOT EXISTS (SELECT 1 FROM master.dbo.sysprocesses WHERE program_name = N’SQLAgent – Generic Refresher’)

BEGIN

PRINT ‘Attention DBA Team ! SQL Server is running but SQL Server Agent is NOT running !!!!’

END

ELSE BEGIN

PRINT ‘OK ! SQL Server and SQL Server Agent both are running’

END

Powershell – How to rename and move files?

Scenario:  Recently, I came across a situation where in we had a a folder called Job_logs which is used for an output for SQL server agent job logs. All the jobs that are scheduled by SQL agent like SSIS packages, TSQL scripts, etc have their output files written to this Job_logs folder (this is done for better logging).

Now the situation is that when the output file for SQL agent is set to append the log file, the output file which is a .txt file gets bigger in size in long run.

Now when the output file is big lets say more than 5000KB, it downgrades the performance as the output file has to be opened up and then written !

Approach: To overcome above situation, I thought of writing a handy script that will do 2 tasks:

  1. Rename the file that is above 5000KB.
  2. Move the renamed file to another folder called Archive.

As we are on Windows Server 2008, the first thing came to my mind is using Powershell. It make life easier !

Now the question that come to mind is: “Is 5000 a magical number?” The answer is NO. Our servers are very heavy in terms of CPU (8 cores) and RAM (64GB). So after doing some testing, I came to the conclusion of going with 5000KB files.

Below is the Powershell script that I wrote to achieve what I wanted:

## set the file location where the job log files are
$file = “F:\temp\Job_logs”
## this will get the current date and format it and store in the variable
$ext = get-Date -format MMddyyyyhhmmss
## Loop through all the .txt files in the job_logs Folder
foreach ($file in gci $file -include *.txt -recurse)
{    ## using the length property of the file
if ($file.Length -gt 5000KB ) 

   { ## will rename the file as file_datetime.txt
rename-item -path $file -newname ($file.Name + “_$ext.txt”)
}
};

$file = “F:\temp\Job_logs”
$archive = “F:\temp\archive\”
foreach ($file in gci $file -include *.txt -recurse)
{
if ($file.Length -gt 5000KB )
{ ## Move the files to the archive folder
Move-Item -path $file.FullName -destination $archive 

  }
}