SQL Jobs

Another change in jobs: and upcoming events

SQL Padre Career, SQL Jobs, SQL Saturday

Recently I was forced to make a job change. 

I am now working for a reputable local bank as a DBA and I am no longer a consultant.  l enjoyed my time as a consultant even though I never could get used to the idea of being “billable”.  And putting the fate of your job in the hands of other individuals (sales department) was nerve racking to say the least.  But I learned valuable skills and information from my time as a consultant.

I hope to continue participating in the SQL community.  In fact, I am presenting my newest presentation “You want me to Isolate What? An Introduction into Data Integrity and Isolation Levels of SQL Server” THIS Saturday at SQL Saturday Pensacola #617.  Of course I will upload my slide deck and demos after this weekend.

And you can catch me in two weeks at SQL Saturday Houston #651.

Hope to see you there!

Where is the key to the LOCK?

SQL Padre SQL 2005, SQL 2008, SQL 2012, SQL Jobs

This past Friday evening, a simple data import process (that has been working for several months), decided to not work correctly.  Because there was no “early warning” type of notifications systems on the SQL 2005 server, the SQL Agent Job was actually running when Monday morning rolled around.

Using Idera’s SQL Check, I quickly noticed that there was a process that had a lock on a table that was causing some MAJOR problems.  Four different SQL processes was waiting on this lock.  And of course my supervisor was getting calls left and right from end users because their applications were not working.

I had to figure out what was going on.  I was able to get a list of processes and which one was causing all the problem with the sp_who2 command.

sp_who2'active'

However, this returned a “bias” HOST NAME.  I am not really sure why. But I confirmed with networking that the returned hostname and IP address do not exist on our network, but that is for another day.

I needed to know an accurate way to get all the information I needed at that moment when a job wrongs long.  This code is probably providing way more information than I really need, but sometimes more is better.  Even though the “host_name” is returning a “bogus” host name; the client_net_address is not!  This valid IP address is very important to locating the culprit machine.

SELECT 
    C.session_id
    ,C.client_net_address
    ,S.host_name
    ,S.Client_version
    ,S.client_interface_name
    ,S.login_name
    ,S.original_login_name
    ,db_name(R.database_id) AS [DB Name]
    ,R.status
    ,R.command
    ,R.blocking_session_id
    ,R.wait_type
    ,R.percent_complete
    ,wait_resource
    ,C.net_transport
    ,C.protocol_type
    ,C.auth_scheme
    ,C.last_read
    ,C.last_write
    ,S.cpu_time
    ,S.memory_usage
    ,(S.total_elapsed_time/1000) AS [Total Sec]
    ,S.lock_timeout
FROM sys.dm_exec_connections AS C
JOIN sys.dm_exec_requests AS R
    ON C.session_id=R.session_id
JOIN sys.dm_exec_sessions AS S
    ON C.session_id=S.session_id
ORDER BY session_id

Using Thomas LaRock’s  HOW TO: Find Currently Running Long SQL Agent Jobs, I setup up a simple 2 step SQL Agent job to check for 1) long running jobs and 2) session information (using the code above).  A simple “save results to txt file” allows me to export the results to and study them later.

USE [msdb]
GO
/****** Object:  Job [job_WH_MonitorDatabase]    Script Date: 10/30/2012 13:57:18 ******/
BEGIN TRANSACTION
DECLARE @ReturnCode INT
SELECT @ReturnCode = 0
/****** Object:  JobCategory [xx] Script Date: 10/30/2012 13:57:18 ******/
IF NOT EXISTS (SELECT name FROM msdb.dbo.syscategories WHERE name=N'xxx' AND category_class=1)
BEGIN
EXEC @ReturnCode = msdb.dbo.sp_add_category @class=N'JOB', @type=N'LOCAL', @name=N'xxx'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

END

DECLARE @jobId BINARY(16)
EXEC @ReturnCode =  msdb.dbo.sp_add_job @job_name=N'job_MonitorDatabase', 
        @enabled=1, 
        @notify_level_eventlog=0, 
        @notify_level_email=2, 
        @notify_level_netsend=0, 
        @notify_level_page=0, 
        @delete_level=0, 
        @description=N'’, 
        @category_name=N'xx', 
        @owner_login_name=N'INFOSYS\xx', 
        @notify_email_operator_name=N'xx', @job_id = @jobId OUTPUT
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
/****** Object:  Step [Monitor]    Script Date: 10/30/2012 13:57:18 ******/
EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'Monitor', 
        @step_id=1, 
        @cmdexec_success_code=0, 
        @on_success_action=3, 
        @on_success_step_id=0, 
        @on_fail_action=2, 
        @on_fail_step_id=0, 
        @retry_attempts=0, 
        @retry_interval=0, 
        @os_run_priority=0, @subsystem=N'TSQL', 
        @command=N'exec usp_LongRunningJobs', 
        @database_name=N'master', 
        @flags=0
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
/****** Object:  Step [RecordProcesses]    Script Date: 10/30/2012 13:57:18 ******/
EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'RecordProcesses', 
        @step_id=2, 
        @cmdexec_success_code=0, 
        @on_success_action=1, 
        @on_success_step_id=0, 
        @on_fail_action=2, 
        @on_fail_step_id=0, 
        @retry_attempts=0, 
        @retry_interval=0, 
        @os_run_priority=0, @subsystem=N'TSQL', 
        @command=N'select
    sysp.spid AS [SPID]
    ,sysp.status AS [Status]
    ,sysp.hostname AS [Host Name]
    ,cn.client_net_address AS [Client IP]
    ,db_name(sysp.dbid) AS [DB Name]
    ,convert(sysname, rtrim(sysp.loginame)) as [Login]
    ,sysp.program_name AS [Program]
    ,sysp.cmd AS [CMD Status]
    ,sysp.cpu AS [CPU usage]
    ,sysp.physical_io [IO]
    ,sysp.blocked AS [Blocked By]
    ,sysp.waitresource AS [Wait Resource]
from master.dbo.sysprocesses AS sysp
INNER JOIN sys.dm_exec_connections AS cn 
    ON sysp.spid=cn.session_ID
WHERE sysp.dbid > 4 AND sysp.cmd <> ''AWAITING COMMAND''
ORDER BY sysp.hostname', 
        @database_name=N'master', 
        @output_file_name=N'F:\SQLAgent_Logs\RunningProcesses.txt', 
        @flags=2
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_update_job @job_id = @jobId, @start_step_id = 1
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobschedule @job_id=@jobId, @name=N'Daily', 
        @enabled=1, 
        @freq_type=4, 
        @freq_interval=1, 
        @freq_subday_type=4, 
        @freq_subday_interval=10, 
        @freq_relative_interval=0, 
        @freq_recurrence_factor=0, 
        @active_start_date=20121030, 
        @active_end_date=99991231, 
        @active_start_time=0, 
        @active_end_time=235959
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @jobId, @server_name = N'(local)'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
COMMIT TRANSACTION
GOTO EndSave
QuitWithRollback:
    IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTION
EndSave:

Thanks for your time!  I will let you know how it turns out.

How to leave a job…and begin a new one.

SQL Padre SQL Jobs

After 4 years with my current company as a ASP.NET/VB.NET Developer, a SharePoint Admin and a MS SQL DBA (I was a “jack of all trades”), I have decided it is time to move on and concentrate on a specific career path.  Tomorrow is my last day and I begin work with an IT Consultant firm as a full-time SQL DBA.  Truly I have to tell you I have not been as excited about a career change in a long while.

But how does one leave a DBA job in a position where it will not “break” after you are gone?  After reading Alan Cranfield’s article, Leaving a SQL Server DBA Job Gracefully, I started to wonder if I had done everything I could to ensure the systems would still run after my domain account would be disabled.  (I never want to burn any bridges).  Being responsible for so many different type of systems (not just SQL), I decided to expound on Alan’s article.

So these are the steps, that I took to ensure (crossing my fingers), that all the systems that I am currently responsible for would continue to work after my account is disabled.

Development Projects – always tried to use Domain security groups to control web access, not personal accounts.  Odds are, security groups will not be removed from AD, but I bet you my first paycheck that my AD account will be disabled before the door closes behind me.  But to be sure, I did a search of all my code to ensure my account was not in the code controlling security anywhere.

SharePoint Projects – as the SharePoint admin for my company, of course I was the Site Collection Administrator for all our SharePoint sites.  Once my replacement was identified, using the Central Administration site, I changed each individual site using the “Site Collection Administrators” page found under the SharePoint Site Management heading.  I know this is the tedious way and STSADM.EXE probably would be faster, but I am such a visual guy, that it always seems easier for me to use Central Administration site, to ensure accuracy.  It also ensures that John Doe’s request for access email will be sent to the correct person.

SQL Projects – using Alan’s suggestion and example scripts, I went through all production databases, to ensure I was not the “owner” of any objects.  Surprisingly, I found a few and changed them to the SA account to prevent system from failing.

And of course the key to this for me was doing all this before I actually left, so if I actually missed something, I was still around to show my replacement how to fix it, or I just fixed it myself!

New Job

Here’s the fun stuff….Starting a new job can always be nerve racking; however I have learned over the years (and through many jobs) that odds are the people who hired you already like you; otherwise they wouldn’t have hired you.  In a past life (as a Food Service Manager), I didn’t hire anyone that I got a “bad vibe” from when I interviewed them. 

A new job means new learning opportunities.  I am by no means an “expert” at anything, so I always look to learn from my supervisors, colleagues and even subordinates (if I had them).   I am never to old to learn something new, and either should you.  Embrace the opportunity to learn as much as you can from the people around you (and always Blog what you learn).  Be nice and good things always comes to those who are patient.

I am excited about my new career choice.  I not only get to focus my career path on a specific skill set (MS SQL) and give my self an opportunity to obtain MS certifications, I also get a little extra money in the process.  How bad is that?