How to enable the trace flags when the server restarts ?

Scenario:  Recently, I was asked by my team lead to enable the trace flags esp. 1204 and 1205 which are deadlock trace flags and they should be in effect after the server restarts.

Approach:  The idea as seems much simple and straight forward. My first approach was to do

DBCC TRACEON (1204, 1205, -1)  -- here –1 is to enable the trace flags globally

                                                 --i.e. not just at the session level

But this will be in effect till the SQL server service is running and once it is restarted the trace flags are no longer in effect.

To overcome above problem, the trace flags can be enabled when the SQL server service starts. This can be done using SQL server Configuration Manager as show below:

SQL server Configuration Manager -> SQL server 2005 services -> SQL server –> Properties -> Advanced -> startup parameters -> ;-T1204;-T1205

The only downside is that we need to restart the service for the changes to take affect !

A quick check if the changes are in affect is to run in SSMS Query window :

DBCC TRACESTATUS

The result should be:

TraceFlag Status Global Session

--------- ------ ------ -------

1204          1       1        0

1205          1       1        0

(2 row(s) affected)

DBCC execution completed. If DBCC printed error messages, contact your system administrator.

HTH,

\\K

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

 

HTH,

\\K

Some free training for enhancing your career…

Recently, I was reading a weekly email from sqlservercentral.com which is named Database Weekly and it is a complete round-up sql server news for the whole week.

What caught my eyes the free training given for DBA’s and BI Developers by Pragmatic works authored by MVP’s like Brian Knight, Jorge Segarra, Steve Jones, Brad McGehee, and more. Without discussing more below is the link for the registration:

http://denglishbi.spaces.live.com/Blog/cns!CD3E77E793DF6178!2526.entry

http://denglishbi.spaces.live.com/?_c11_BlogPart_BlogPart=blogview&_c=BlogPart&partqs=amonth%3d2%26ayear%3d2010

Also, below is the link that points to Microsoft for some free training:

http://learning.microsoft.com/Manager/Catalog.aspx?view=free

HTH,

\\K

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 

  }
}