Thursday, February 25, 2010

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 :


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.



Saturday, February 20, 2010

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. */

           FROM MASTER.dbo.sysprocesses
           WHERE program_name = N'SQLAgent - Generic Refresher')
   SELECT @@SERVERNAME AS 'InstanceName', 1 AS 'SQLServerAgentRunning'
EXEC msdb.dbo.sp_send_dbmail
    @profile_name = 'kinjal.dba test profile',
    @recipients = '',
    @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

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;


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()))


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')


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



PRINT 'OK ! SQL Server and SQL Server Agent both are running'





Sunday, February 14, 2010

Some free training for enhancing your career…

Recently, I was reading a weekly email from 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:!CD3E77E793DF6178!2526.entry

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



Saturday, February 6, 2010

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 


This blog is for SQL server and ArcSDE administration. Thanks for visiting !

Disclaimer: The contents provided on this blog are as is and the poster assumes no responsibility in case of any loss of data. Please test thoroughly the solution or codes provided in your environment.