Strange SQL Agent job failure.

Scenario:   Recently, I came across a situation on our production server. It was a SQL agent job failure with below error message:

Could not obtain information about Windows NT group/user 'kks', error code 0xffff0002.". Possible failure reasons: Problems with the query, "ResultSet"

property not set correctly, parameters not set correctly, or connection not established correctly.  E...  The package execution fa...  The step failed.

The interesting thing was that the job runs every 10 mins and it fails occasionally only e.g every 1 or 2 day or so.

Approach:   I started troubleshooting the error message by first looking into the permissions for the user account that the job is using for execution. The permissions were OK for the account.

I looked into the error logs and found that the failure was coinciding with the DB mail failure. So I looked into the code that the job was executing. The job was executing a SP that was calling internally another SP named sp_send_dbmail. And Voila … found the offending part !

I checked the DB mail configuration and noticed that the user ‘kks’ didn’t have any default mail profile associated with it.

So when it tries to execute the SP, it fails as the user is not associated with any default profile that it can use to send email. So I assigned the user ‘kks’ a profile with proper distribution list and made that profile default for it.

This way it was fixed and never failed again.

“sometimes little things become unnoticed and cause a lot of pain” :-)

HTH,

\\K

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 

  }
}

How to test whether your SQL server is available or not without installing anything?

Technorati Tags: Data Connection

Scenario: Recently, I was asked what if I want to test connectivity to my SQL server from a web server or from my local desktop machine ?

Approach: Well, this seems a much obvious question that can be answered on the spur as use PING <server IP> from the command prompt.

But PING just does the basic test and tells that whether your server is available or not. What if you want to see that your databases that your SQL server is hosting are available or not ?

There is a more smatter and easy way of doing this. The idea is to use UDL (Universal Data Link) files (i.e  .udl files).

Overview of UDL files:

UDL files are an easy, efficient and fast way to test connection to a server from your work station or from web server eliminating the need to install any application (e.g dtcping) or SSMS (SQL Server Management Studio). Also, its a good way to quickly test that the credentials you specified will connect to a server and to build a connection string.

How do I do this ?

1. Create an empty .txt file. Name it for example MyConnection.txt .
2. Now rename the extension and save it as MyConnection.udl .
3. Below is the image of the MyConnection.udl file.

4. Now double click the MyConnection.udl file to see the below screen.

5. Click the Provider tab and select SQL Native Client and then click Next.

6. Enter the data source: localhost or IP address of the server. Here I have used Windows authentication, but choosing the second option allows you to use SQL server authentication wherein you have to provide username and password. (Note: Here there is an option to save the password as blank or Allow saving password option and choosing this option will save the password as clear text on the disk which is a security risk !). Next you can choose to connect to the specific database by clicking the drop box and selecting the database name. Click test connection button to verify that the connection is successful. Below is what you will see:         

7. Press OK when you are done.

8. Now we can examine the contents of our MyConnection.udl file by renaming it back to MyConnection.txt

9. Below are the contents of the file:

[oledb]
; Everything after this line is an OLE DB initstring
Provider=SQLNCLI.1;Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=AdventureWorks;Data Source=localhost

10. You can use the above connection string in your application code directly !

Hope this helps !

Thanks,

\\K

How to rename Distribution Job in an automated fashion ?

Technorati Tags: Replication

Situation: Recently I came across a situation wherein I have to rename the distribution jobs that are created when replication is recreated. The need to recreate replication is due to the fact that we do every 3 or 4th week staging refresh in our environment.

Our environment is on Windows server 2008 (64bit) with SQL server 2005 enterprise edition SP3 with Transactional replication involved.

Approach: We have a automated process called a Master Job (I will blog this in my future posts) that does the staging refresh job. This job is named as per what it does, meaning it drops replication, does restores and recreates replication with just one click ! Isn’t that cool !

But this job has some manual steps to rename the distribution jobs that are created when the replication is recreated. So to fully automate the job, I thought of automating some how to rename the distribution jobs.

First I would try to explain how the jobs are created when replication is created or recreated.

When transactional replication is created either using GUI (wizard) or recreated using scripts, the respective agent (snapshot, distribution and log reader) jobs are created with their default naming conventions. For example:

Snapshot Agent:
[PublisherServerName]-PublisherDB-PublisherDB- [# of time replication script/wizard run on this server]

Log reader Agent:
[PublisherServerName]-PublisherDB- [# of time replication script/wizard run on this server]

Distribution Agent:
[publisherserverName]-publisherDB-SubsriberServerName [# of times subscribers created run on this server]

[Ref: http://rdbmsexperts.com/Blogs/?p=40 . Thanks to Vinay for his help !]

This shows that there is no control of creating our own job names (for better management and maintenance).

There are two ways of renaming the distribution jobs:

1. On the distribution server, double click the respective job names and then rename them. Uh! that’s a manual way of doing and it can be tricky or prone to mistakes and being a DBA, its not smart way to do things !
2. The second way is creating a custom script as below:

In the distribution database, there is a system table called dbo.MSdistribution_agents that gives the info about the distribution agent with respect to sysjobs.

Below is the script that I used to rename the distribution jobs ( we have multiple publisher – subscribers.)

declare @jobID1 uniqueidentifier 
select @jobID1=job_id from [dist_db_name].dbo.MSdistribution_agents a,master.sys.servers s
where a.publisher_db='[pub_db_name]'  and
a.subscriber_db='[subscriber_db_name]' and
a.subscriber_id=s.server_id and
s.name='subscriber_server_name'

print @jobID1  -- this will print the jobID of the distribution job 

select job_id, name from msdb.dbo.sysjobs where job_id = @jobID1   -- this will give the jobID as well as job name of  the distribution job that we will be renaming

–-rename the distribution agent job that is created to new job name

exec msdb.dbo.sp_update_job @job_id = @jobID1, @new_name = ‘ New_meaning_ful_name’

To sum up above method:

-- Get the job ID for the distribution job created
declare @jobID1 uniqueidentifier

select @jobID1=job_id from [dist_db_name].dbo.MSdistribution_agents a,master.sys.servers s
where a.publisher_db='[pub_db_name]'  and
a.subscriber_db='[subscriber_db_name]' and
a.subscriber_id=s.server_id and
s.name='subscriber_server_name'

–-rename the distribution agent job that is created to new job name

exec msdb.dbo.sp_update_job @job_id = @jobID1, @new_name = ‘ New_meaning_ful_name’

In the same way we can rename the job for snapshot and logreader agent as well with MSlogreader_agents & MSsnapshot_agents tables respectively.

Hope this helps !

Thanks,

\\K

Strange issue with database mail in clustered environment - not working tried everything

Scenario: We have a 4 node clustered environment having windows server 2008 64bit with SQL server 2005 SP3 enterprise edition.

Problem: The issue was that out of 4 servers in a clustered environment, on only one server the database mail was not working.

Approach:

Step1: Check that the Database mail is enabled using Surface Area Configuration. Then I rebuilt the Profiles and Accounts within the SQL Database Mail. I sent test messages after the rebuild and didn't see anything logged. I used the troubleshoot option on the test message send page and found some stored procedures which allow you to check to see if the queue is active and to start it. I both confirmed it was active, and sent it another start command, but still don't see email getting sent. I did see that the DB Mail start command was logged in the event logs, but nothing other than that.

Below is what was done:

--The following query will show database mail is working correctly
SELECT * FROM msdb.dbo.sysmail_sentitems order by sent_date desc

No results are seen after running the above command.


SELECT * FROM msdb.dbo.sysmail_allitems order by sent_date desc

WHEN I RUN above COMMAND I SEE THAT ALL THE MESSAGES ARE IN AN "failed" send_status

--The following query will show Database mail is started and running
SELECT * FROM msdb.dbo.sysmail_event_log order by log_date desc

WHEN I RUN THIS COMMAND I CAN SEE DB MAIL IS STARTED, BUT I DON'T SEE ANY SUCCESSFULLY SENT MESSAGES LIKE I DO WHEN I RUN IT ON ANY OF THE OTHER DB SERVERS

--If Database mail was having problems, we would see failed items in this view.
SELECT * FROM msdb.dbo.sysmail_unsentitems

WHEN I RUN THIS COMMAND I SEE THAT ALL THE MESSAGES ARE IN AN "unsent" send_status

Then the FIREWALL and SMTP relay were working fine or not were checked and they were all working fine !!

Then I GOOGLED and found "http://social.msdn.microsoft.com/Forums/en-US/sqldatabaseengine/thread/fd210efa-9c01-439e-8573-4f25a4177b08", but this did not work !!

Then the decision was taken to open a ticket with MS... spent 2.5 hours on the phone with Microsoft trying to figure out what's going on with DB Mail.

At this point they took some traces, other diagnostic data ... and yes they were able to figure the REAL problem ....

When you select MSDB and run the following to show DB Mail config:

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

USE msdb

select * from sysmail_configuration

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

...We can see that the following is set (which MS says would have been done manually as the default is to NOT use a config file):

------------------------------------------------------------------
ReadFromConfigurationFile 1 Send mail from mail server in configuration file
------------------------------------------------------------------

With this option enabled there MUST be a configuration file that tells the DB Mail exe what Server\Instance to send the mail to. This file existed (I don't know if it has existed from the beginning, who created it, etc...), but it was not in the correct location.

The file was in:

C:\Program Files\Microsoft SQL Server\90\Tools\Binn

...and needed to be in (MSSQL.3 since registry shows [server_name] being the 3rd instance):

C:\Program Files\Microsoft SQL Server\MSSQL.3\MSSQL\Binn

Once this file was moved into the proper location, we were able to define the correct server name:

------------------------------------------------------------------
Contents of config file: "DatabaseMail90.exe.config"
------------------------------------------------------------------

configuration
appSettings
add key="DatabaseServerName" value ="[server_name]" /
add key="DatabaseName" value ="msdb" /
/appSettings
/configuration

------------------------------------------------------------------
Once this had been properly configured and the file put into the correct location we were able to successfully send mail

Since this is a 4-node SQL Cluster we also had to copy the "DatabaseMail90.exe.config" file over to the other 3 nodes to:

\\[server_name]\Program Files\Microsoft SQL Server\MSSQL.3\MSSQL\Binn
\\[server_name]\Program Files\Microsoft SQL Server\MSSQL.3\MSSQL\Binn
\\[server_name]\Program Files\Microsoft SQL Server\MSSQL.3\MSSQL\Binn

...so that if this instance fails over to any other node it will continue to function.


This is how the Strange database mail issue was fixed ... Ah.. it was with the config file .... and lastly got resolved !!!