Thursday, October 18, 2018

When was the Windows server last booted?

Many times I must look into sys.databases to determine the last SQL Server service restart.  The fastest way to do that is just look at your tempdb create date:

SELECT * FROM sys.databases


SELECT create_date FROM sys.databases WHERE [name] = 'tempdb'

But, today I need to know when the Windows server was rebooted.  Very easy, and I wanted to share it with you.  Open up a cmd prompt and type 'net statistics server'.  

Voila!  See the 'Statistics since 10/18/2018 1:03:54 PM'  -- that is your last server boot time.

Happy SQL Server-ing!

Thursday, August 30, 2018

Property Owner is not available for Database

One of my customers just sent me an email, said he could not look at the properties of his database in SSMS.  When he tried to, he received this error:

Curious.  I've actually never seen that before, so I ran sp_helpdb, and it told me the owner was <UNKNOWN>.  Again, very odd... but also very quick to fix.

           USE DatabaseName;
           EXEC sp_changedbowner 'sa';

I him know he could access his database, and then I used this to look into sys.databases to check out the other database owners --

           SELECT suser_sname( owner_sid ),name,state_desc
           FROM sys.databases

Turns out nearly every one of their databases are owned by domain logins.  You've heard all of the warnings about that, right?  If the domain login that owns the database is removed, disabled, or simply cannot be resolved in AD, this will cause problems exactly like this one.  This is also a good reason not to have SQL Server Agent jobs owned by domain logins.  It opens the door for too many problems... that can be easily avoided.

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: