Wednesday, June 20, 2018

Query to list each SQL Server database with owner

Just a quick one.  I've advised a customer of the complications of using domain logins for database owners, and they've agreed to change the owner to sa for all databases where necessary.  So, in prep for said change, I wrote a quick query to list all databases with owners, so I could see which ones required changed.  This statement will return back your databases w/owners for quick review.

-- list databases w/owners 
       name [Database],
       suser_sname(owner_sid) [Owner]
       database_id > 4

Should return something for you a little like this:

Saturday, May 12, 2018

Where is the 'Execute Task' option in my SSIS package?

Yesterday I showed a developer how to execute the SSIS package components selectively within Visual Studio.  Just right click the task within the Control Flow tab, and choose 'Execute Task'.

Very simple, except then I received this email from him:

    I don’t see the “execute task” option—am I doing it wrong or is it permissions?

No.  It's not permissions.  The problem here is that he only had the package open.  The 'Execute Task' option will only appear if you've opened the Project or Solution first, and then opened the package.

Within VS, go to File, Open,  Project/Solution, and browse to the appropriate location for project (.dtproj) or solution (.sln) file.  Once that is open, make sure the Solution Explorer is visible on the right, and open the package from SSIS Packages.  He did that, and the 'Execute Task' option reappeared.

Friday, May 11, 2018

How to query all of the named instances for a SQL Server?

Easy sneasy.  I'm performing a health check for a new customer over the weekend, and when I logged into the server today, I was greeted by a named instance that I wasn't aware of.  Heck.  I didn't know there any named instances!  So now I'm asking myself how many there are, and which ones I should be auditing.

This is just a quick piece just to list the named instances for any given SQL Server.  Super easy.  Check it out and let me know what you think.

-- how many instances are there?
DECLARE @Instances TABLE (
       Value VARCHAR(100),
       InstanceName VARCHAR(100),
       Data VARCHAR(100)

INSERT @Instances
EXECUTE xp_regread
  @rootkey = 'HKEY_LOCAL_MACHINE',
  @key = 'SOFTWARE\Microsoft\Microsoft SQL Server',
  @value_name = 'InstalledInstances' 

-- return your data
SELECT InstanceName FROM @Instances

These are my results:


Friday, May 4, 2018

Which SQL Server tables have triggers?

I performed a quick health check for a customer last night, and this was one of the performance-related findings:

      The [Insite] database has 16 triggers.

Not necessarily a problem, but it certainly could be. 😕  This is the query that I've modeled to go in and find the tables with any triggers, and to list the trigger type, and whether they are disabled... hopefully.

lySE Insite;
SELECT TriggerName,
    USER_NAME(so.uid) TriggerOwner,
    USER_NAME(so2.uid) TableSchema,
    OBJECT_NAME(so.parent_obj) TableName,
    OBJECTPROPERTY(, 'ExecIsUpdateTrigger') IsUpdate,
    OBJECTPROPERTY(, 'ExecIsDeleteTrigger') IsDelete,
    OBJECTPROPERTY(, 'ExecIsInsertTrigger') IsInsert,
    OBJECTPROPERTY(, 'ExecIsAfterTrigger') IsAfter,
    OBJECTPROPERTY(, 'ExecIsInsteadOfTrigger') IsInsteadOf,
    OBJECTPROPERTY(, 'ExecIsTriggerDisabled') IsDisabled
       sysobjects so INNER JOIN sysobjects so2
        ON so.parent_obj = so2.Id
       so.type = 'TR'

Pretty easy.  Your resultset will be something like this:

Monday, April 30, 2018

The data types ntext and varchar are incompatible in the equal to operator.

Today I loaded a trace file into a table so that I could analyze some long running queries.  This is the statement that I used to upload the trc file to a table:

    --read trace file using fn_trace_gettable
    SELECT *
    INTO DBA..HREmployees_Trace
    FROM fn_trace_gettable('G:\HREmployees_Trace_31.trc', default) Trace

And then this is the statement that I used to find the records for the problem query that we were troubleshooting:

    SELECT StartTime,EndTime,TextData,*
    FROM dbo.HREmployees_Trace
    WHERE TextData = 'select * from HREmployees where Status = "A"'

But, it immediately failed w/this error:

    Msg 402, Level 16, State 1, Line 1
    The data types ntext and varchar are incompatible in the equal to operator.

Why am I getting that error?  Well, the answer is very simple.  The next and varchar are not compatible when compared with each other using equal sign.  If you look at that table I created when loading the trace file (.trc), you can see that TextData is created as NTEXT.

Fastest workaround?   Use LIKE instead of EQUAL (=), like this:

    SELECT StartTime,EndTime,TextData,*
    FROM dbo.HREmployees_Trace

    WHERE TextData LIKE '%select * from HREmployees where Status = %'

And you see there... it's no longer failing to collect from the NTEXT column.

Take a look at this for a little more detail on sys.fn_trace_gettable:

Wednesday, April 18, 2018

When was the last transaction log backup taken?

Good question.  I was reviewing a customer's backup status today, and I needed to answer that question -- when was the last tranlog backup run?  It is a fast statement, and I have included the recovery model in with each database, so that you can see which ones are FULL recovery, and where the backups may be missing.

          d.recovery_model_desc [RecoveryModel],
          MAX(b.backup_finish_date) [BackupDate]
          master.sys.databases d LEFT OUTER JOIN msdb..backupset b
             ON b.database_name =
             AND b.type = 'L'
     GROUP BY 

     ORDER BY 

Your resultset will be a little something like this:

If the database recovery models are correct, then you may need to get some backups into place on that Orders database.  

See this for more details on SQL Server's transaction log backups:

Monday, November 20, 2017

Database Mail is stopped. Use sysmail_start_sp to start Database Mail.

I built a  new job recently for one of my customers.  Just a quick archive job which moves data from A to B, and sends a notification to an application Team after it has completed. ... or I should say it is supposed to send a notification via email.  The first run of that job failed with this message:

Executed as user: DOMAINNAME\sqlservice. Mail not queued. Database Mail is stopped. Use sysmail_start_sp to start Database Mail. [SQLSTATE 42000] (Error 14641).  The step failed.

Database Mail is stopped?  Sure isn't supposed to be.  I did a bit of research, and it seems this is not entirely uncommon.  As a workaround, I decided to check first to see if Database Mail was running, and then to start it, if needed.  Very simple, and it will prevent any failures sending notifications from the job, going forward.

DECLARE @MailStatus TABLE (CurrentState VARCHAR(10))
INSERT @MailStatus
EXEC msdb.dbo.sysmail_help_status_sp;

      SELECT 1 FROM @MailStatus
      WHERE CurrentState = 'STARTED'
            EXECUTE msdb..sysmail_start_sp; -- START IT, IF NEEDED

            EXEC msdb..sp_send_dbmail
              @profile_name = 'SQL_MailProfile',
              @recipients =',
              @subject='SERVERNAME  Archive Job ',
              @body='The daily Archive Job has completed successfully.'
            EXEC msdb..sp_send_dbmail
              @profile_name = 'SQL_MailProfile',
              @recipients = '',
              @subject='SERVERNAME  Archive Job ',
              @body='The daily Archive Job has completed successfully.'

Using that sysmail_help_status_sp, we can very easily check the status of the mail FIRST, and then respond accordingly.  Take a look at this for more detail from MSFT on sysmail_help_status_sp.  

Friday, November 3, 2017

Arithmetic overflow error converting IDENTITY to data type int.

One of my customer's SQL Server Agent jobs failed today with this error:

Executed as user: MAJNT\sqlservice. Arithmetic overflow error converting IDENTITY to data type int. [SQLSTATE 22003] (Error 8115)  Arithmetic overflow occurred. [SQLSTATE 01000] (Error 3606).  The step failed.

We know these arithmetic overflows occur when a data value exceeds the datatype of the column it is going into.  In this case, the overflow occurred on an integer IDENTITY column, which means our value exceeded 2147483647 --  wow!  But how do we know which table the overflow occurred in?

Easy peasy.  This query will return all tables with IDENTITY columns from your database, WITH their current IDENTITY values.  

   IDENT_SEED(tables.TABLE_SCHEMA + '.' + tables.TABLE_NAME) Seed,
   IDENT_INCR(tables.TABLE_SCHEMA + '.' + tables.TABLE_NAME) Increment,
   IDENT_CURRENT(tables.TABLE_SCHEMA + '.' + tables.TABLE_NAME) Current_Identity
   OBJECTPROPERTY(OBJECT_ID(tables.TABLE_SCHEMA + '.' + tables.TABLE_NAME), 'TableHasIdentity') = 1

...and your results:

No more data is going into this table until the problem is resolved, so you may consider running something like this on a scheduled basis.  Then you can be aware of the values before they exceed their maximums allowed.

What are your current options?  
  1. Archive the data, reseed the IDENTITY and begin taking in new records.
  2. Alter the IDENTITY column from INT to BIGINT, increasing our maximum range value to 9,223,372,036,854,775,807.

See this for more details regarding the IDENTITY value, checking it and reseeding it:

Monday, October 30, 2017

How do you open a SQL Server Profiler trace file?

"Do you see anything in those traces? I am not sure I know how to read them properly, how do you open the file?"

A question from one of my customers today.  Last week they began having unexplained server disconnects early each morning at about 1:30AM.  I reviewed the system -- SQL Server and Windows logs, current and previous, as well as general state of the server and databases -- and I saw no problems.  I decided to setup a Profiler Trace to run during the time in question, hoping to capture the cause of the disconnects.

Well, I scheduled and ran the trace...  and now we need to read it.  :)

Very easily done using the fn_trace_gettable.  Here I've sampled a quick SELECT star and another more specific SELECT from the trace output.

       SELECT * FROM master..fn_trace_gettable(
        'C:\Users\aparahnevi\MForceTrace20171026.trc', DEFAULT);

       CASE WHEN Duration IS NOT NULL THEN Duration/1000000.00 ELSE Duration END AS DurationInSeconds,
       SPID AS SessionId,
       EventClass AS EventNumber,
       CPU AS CPUInMiliseconds,
           'C:\Users\aparahnevi\MForceTrace20171026.trc', DEFAULT)
       TextData IS NOT NULL
       AND Duration IS NOT NULL;

That's it!  As I said, pretty quick and easy.  Here's a quick look at the output:

I'll take some time later to post instructions for how I actually scheduled the trace.

Take a look at this for more details on fn_trace_gettable:

Friday, August 25, 2017

SQL Server Agent - Query the last run status of your jobs

Do you know how to check the last run status of your scheduled Agent jobs?  In SSMS you can go to SQL Server Agent / Jobs, and then right click the job and choose to 'View History', like this:

... but, if you've got a lot of jobs to check, that's going to take quite a bit of time.  

This script is just a very quick query to collect the status, or outcome of the last time each of your Agent jobs ran.

   USE msdb
   SELECT [JobName],
       CASE WHEN js.last_run_outcome = 0 THEN 'FAIL'
               WHEN js.last_run_outcome = 1 THEN 'SUCCESS'
               WHEN js.last_run_outcome = 3 THEN 'CANCEL' END [Outcome],
       js.last_outcome_message [OutcomeMessage],
       CASE WHEN js.last_run_date > 0
         THEN DATETIMEFROMPARTS(js.last_run_date/10000,js.last_run_date/100%100,
           js.last_run_time%100,0) END [LastRunDatetime]
       dbo.sysjobservers js left outer join msdb.dbo.sysjobs j
         ON jS.job_id = j.job_id
       j.[enabled] = 1

Should give you back something like this:

More details here for sysjobs and sysjobservers: