Moved the blog to Azure - New Site

This will be the last post on this blog ... The blog have moved to http://sqlsybase.azurewebsites.net/ Thanks for visiting. Cheers !

BCP for Sybase ASE

As I promised to keep my blog green with cool stuff, below is my first posting for 2012.

Scenario: We had a weird problem with Sybase ASE 1503 just for one database where in if we try to dump a database, ASE will throw stack trace. This was weird as out of all the databases on the server, this was the only one having problem. I will call this database as “problem_db”.

Approach: First and the most common approach was to restart backup server which did not helped much and the stack trace was still thrown in the error log.

Secondly, we contacted Sybase and asked for help with this situation and they recommended to delete statistics and recreate them back and we ran dbcc to make sure there were no errors reported. We followed that and still the situation was same.

Until Sybase comes back to us with a proper fix, we decided to bcp out the data and bcp in the data to a new database. Lets call this new database as “healthy_db”

Below are the scripts that I used for BCP out and BCP in the data. More info on bcp command can be found here.

Below parameters are quiet useful for BCPing the data into the database:

Parameter	Explanation
-n	performs the copy operation using native (operating system) formats. Specifying the -n parameter means bcp will not prompt for each field. Files in native data format are not human-readable.
-Y	specifies that character-set conversion is disabled in the server, and is instead performed by bcp on the client side when using bcp IN.
-E	bcp reads the Identity value from the data file and sends it to the server which inserts the value into the table.
-T	allows you to specify, in bytes, the maximum length of text or image data that Adaptive Server sends. The default is 32K. If a text or an image field is larger than the value of -T or the default, bcp does not send the overflow. You can find this value by doing: SELECT @@TEXTSIZE
-e	This is the error log file created by bcp and it will log only the first 10 rows that caused the errors as the default for bcp is 10 errors and after that bcp will fail.

Note that ASE was running 1503 on a Solaris platform.

-- BCP OUT SCRIPT

select 'bcp problem_db.dbo.' + name + ' out /bcp_out_files/' + name + '.out -SSERVER_NAME -Usa -PSTRONG_PASSWORD -n'
from sysobjects
where type='U'
order by name

Now for the bcp in script, there are two different scripts used for Tables having Identity columns and the ones which did not have Identity columns:

-- BCP IN SCRIPT

--  (this will be for tables not having identity columns)
select 'LANG=C' + char(10)+'bcp healthy_db.dbo.' + so.name + ' in /bcp_out_files/' + so.name + '.out -SSERVER_NAME -Usa -PSTRONG_PASSWORD -n -Y -T 64512 -e /bcp/bcp_error.out' + char(10) +
'echo '+ so.name +'  done ...' from sysobjects so where so.name not in (
select so.name
from sysobjects so, syscolumns sc
where so.id = sc.id
and sc.status & 0x80 = 0x80)
and so.type = 'U'
order by so.name

--  (this will be for tables having identity columns, –E parameter is used for bcp)
select 'LANG=C' + char(10)+'bcp healthy_db.dbo.' + so.name + ' in /bcp_out_files/' + so.name + '.out -SSERVER_NAME -Usa -PSTRONG_PASSWORD -n -Y -E -T 64512 -e /bcp/bcp_error.out' + char(10) +
'echo '+ so.name +'  done ...'
from sysobjects so, syscolumns sc
where so.id = sc.id
and sc.status & 0x80 = 0x80
and so.type = 'U'
order by so.name

The above scripts will generate code for bcp out and in to run using isql (command prompt) on Solaris server.

Also, the schema and other dependent objects were created before hand.

HTH,

\\K

Back to writing blog …

I have been very busy with job duties as now learning Sybase ASE and taking on new responsibilities as SQL server and Sybase DBA.

I was not able to allocate time to keep my blog alive for almost a year.

This is my first post in 2012. And want to keep this as habit of regularly updating my blog with both MS SQL Server and Sybase.

Hope you will enjoy reading new cool stuff on this blog.

Many thanks,

SQL_Quest

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