Articles for the Month of July 2013

Quote of the Day

“It is not the critic who counts, not the one who points out how the strong man stumbled or how the doer of deeds might have done them better. The credit belongs to the man who is actually in the arena, whose face is marred with sweat and dust and blood; who strives valiantly; who errs and comes short again and again; who knows the great enthusiasms, the great devotions, and spends himself in a worthy cause; who, if he wins, knows the triumph of high achievement; and who, if he fails, at least fails while daring greatly, so that his place shall never be with those cold and timid souls who know neither victory nor defeat.”

Franklin D. Roosevelt

MSSQL – sp_who3, A better sp_who2

As a DBA, sp_who2 is a procedure that I use on a daily basis. It’s a good procedure, but you always have to copy the entire result into excel sheet and then sort through it to find the SPID of the session that you were looking for in the first place. And sometimes, you just don’t have the time to do that. So there are two things you can do to solve this issue. First, you can get the result of the sp_who2 into a @table and then look through there (which is way easier than using excel), or you can create your own procedure that accepts a parameter as the DBName and filters the outcome based on that. Let me show you both and you can choose whichever works for you best.

1. Sort through the result of sp_who2 via @table

So, if all you want to do is find out what processes are running against a specific database, you can copy and paste the following query and execute it. Make sure to edit line 35 and replace <DATABASE NAME GOES HERE> with the name of the database you have.


DECLARE @whotbl TABLE
    (
      SPID        INT    NULL
     ,Status    VARCHAR(50)    NULL
     ,Login        SYSNAME    NULL
     ,HostName    SYSNAME    NULL
     ,BlkBy        VARCHAR(5)    NULL
     ,DBName    SYSNAME    NULL
     ,Command    VARCHAR(1000)    NULL
     ,CPUTime    INT    NULL
     ,DiskIO    INT    NULL
     ,LastBatch VARCHAR(50)    NULL
     ,ProgramName VARCHAR(200)    NULL
     ,SPID2        INT    NULL
     ,RequestID INT    NULL
     )

     INSERT INTO @whotbl
     EXEC sp_who2

    SELECT W.*
          ,CommandText = sql.text
          ,ExecutionPlan   = pln.query_plan
          ,ObjectName  = so.name
          ,der.percent_complete
          ,der.estimated_completion_time
          --,CommandType =der.command
      FROM @whotbl  W
 LEFT JOIN sys.dm_exec_requests der
        ON der.session_id = w.SPID
       OUTER APPLY SYS.dm_exec_sql_text (der.sql_handle) Sql
       OUTER APPLY sys.dm_exec_query_plan (der.plan_handle) pln
 LEFT JOIN sys.objects so
        ON so.object_id = sql.objectid
      WHERE DBName = '<DATABASE NAME GOES HERE>'

2. Create a better sp_who2 procedure

If you will be using this more than once, it’s better to create a procedure that you can pass the name of the database to it and it could return the result to you. If that’s the case, copy and paste the following and execute it to create the procedure:


IF OBJECT_ID('sp_who3','P') IS NOT NULL DROP PROC sp_who3

GO
CREATE PROC sp_who3 @DBNAME nvarchar(100) = NULL
AS

    DECLARE @whotbl TABLE
    (
      SPID        INT    NULL
     ,Status    VARCHAR(50)    NULL
     ,Login        SYSNAME    NULL
     ,HostName    SYSNAME    NULL
     ,BlkBy        VARCHAR(5)    NULL
     ,DBName    SYSNAME    NULL
     ,Command    VARCHAR(1000)    NULL
     ,CPUTime    INT    NULL
     ,DiskIO    INT    NULL
     ,LastBatch VARCHAR(50)    NULL
     ,ProgramName VARCHAR(200)    NULL
     ,SPID2        INT    NULL
     ,RequestID INT    NULL
     )

     INSERT INTO @whotbl
     EXEC sp_who2

    SELECT W.*
          ,CommandText = sql.text
          ,ExecutionPlan   = pln.query_plan
          ,ObjectName  = so.name
          ,der.percent_complete
          ,der.estimated_completion_time
          --,CommandType =der.command
      FROM @whotbl  W
 LEFT JOIN sys.dm_exec_requests der
        ON der.session_id = w.SPID
       OUTER APPLY SYS.dm_exec_sql_text (der.sql_handle) Sql
       OUTER APPLY sys.dm_exec_query_plan (der.plan_handle) pln
 LEFT JOIN sys.objects so
        ON so.object_id = sql.objectid
      WHERE DBName = ISNULL(@DBNAME,DBName)

go

You can use the procedure similar to sp_who2 (without argument) or with a @DBNAME = ‘ ‘ argument, like bellow:

sp_who3 @DBNAME = '<DATABASE NAME GOES HERE>'

Obviously, the credit for the code goes to someone else. I just thought of improving it a bit. Hope it helps you.

ORACLE – Kill, stop and resume datapump expdp and impdp jobs

Since datapump export and important utilities are both command-line driven, it is easy to forget that they are runnig in the background. So sometimes you need to kill, cancel or restart the expdp or impdp job. Keep reading to see what you need to do to achieve this:

1) Identify the datapump jobs (or check the status of your job)

You can do 2 things:

a) If you assigned a name to job_name parameter of your expdp or impdp command, then you can use the name

b) You can do a select from dba_datapump_jobs to get a list of all the jobs that are currnetly running (sqlplus)

SELECT owner_name, job_name, operation, job_mode, state FROM dba_datapump_jobs;

OWNER_NAME     JOB_NAME              OPERATION   JOB_MODE    STATE
----------     --------------------  ----------  ----------  ------------
SYS            TS2_expdp_July4       EXPORT      FULL        EXECUTING

2) Killing or stopping a running datapump job

a) To kill a job (no chance of resuming it later on)
press Ctrl-c to show the datapump prompt and then type KILL_JOB

Processing object type DATABASE_EXPORT/SCHEMA/TABLE/TABLE_DATA
[Ctrl-c]
Export> KILL_JOB

b) To Stop a job (you can resume it later)
press Ctrl-c to show the datapump prompt and then type STOP_JOB[=IMMEDIATE]

Processing object type DATABASE_EXPORT/SCHEMA/TABLE/TABLE_DATA
[Ctrl-c]
Export> STOP_JOB=IMMEDIATE
Are you sure you wish to stop this job ([yes]/no): yes

NOTE: Sometimes the job is killed but it’s not removed from the dba_datapump_jobs table. In that case you need to drop the table:

DROP TABLE SYS.TS2_expdp_July4;
Table dropped.

3) Resuming a stopped job

If you know the job_name parameter then use it, if you don’t do a select from dba_datapump_jobs

SELECT owner_name, job_name, operation, job_mode, state FROM dba_datapump_jobs;

OWNER_NAME  JOB_NAME              OPERATION   JOB_MODE    STATE
----------  --------------------  ----------  ----------  ------------
SYS         TS2_expdp_July4       EXPORT      FULL NOT    RUNNING

Now we can ATTACH to the job using it as a parameter to the expdp or impdp command, and a lot of gibberish is shown:

$ expdp system ATTACH=TS2_expdp_July4