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:

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;

IF NOT EXISTS(

      SELECT 1 FROM @MailStatus

      WHERE CurrentState = 'STARTED'

      )

      BEGIN

            EXECUTE msdb..sysmail_start_sp; -- START IT, IF NEEDED

            EXEC msdb..sp_send_dbmail

              @profile_name = 'SQL_MailProfile',

              @recipients = somebody@somewhere.com',

              @subject='SERVERNAME  Archive Job ',

              @body='The daily Archive Job has completed successfully.'

      END

      ELSE 

      BEGIN

            EXEC msdb..sp_send_dbmail

              @profile_name = 'SQL_MailProfile',

              @recipients = 'somebody@somewhere.com',

              @subject='SERVERNAME  Archive Job ',

              @body='The daily Archive Job has completed successfully.'

      END

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.  

https://docs.microsoft.com/en-us/sql/relational-databases/system-stored-procedures/sysmail-help-status-sp-transact-sql

Arithmetic overflow error converting IDENTITY to data type int.

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

Message

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.  

SELECT

   tables.TABLE_NAME,

   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

FROM

   INFORMATION_SCHEMA.TABLES tables

WHERE

   OBJECTPROPERTY(OBJECT_ID(tables.TABLE_SCHEMA + '.' + tables.TABLE_NAME), 'TableHasIdentity') = 1

   AND tables.TABLE_TYPE = 'BASE TABLE';

...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:
https://docs.microsoft.com/en-us/sql/t-sql/database-console-commands/dbcc-checkident-transact-sql

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);

SELECT

       TextData,

       StartTime,

       EndTime,

       CASE WHEN Duration IS NOT NULL THEN Duration/1000000.00 ELSE Duration END AS DurationInSeconds,

       DatabaseName,

       ApplicationName,

       SPID AS SessionId,

       ObjectName,

       Error,

       EventClass AS EventNumber,

       RowCounts,

       CPU AS CPUInMiliseconds,

       ServerName

FROM

       master..fn_trace_gettable(

           'C:\Users\aparahnevi\MForceTrace20171026.trc', DEFAULT)

WHERE

       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:

    https://docs.microsoft.com/en-us/sql/relational-databases/system-functions/sys-fn-trace-gettable-transact-sql

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

       j.name [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_date%100, js.last_run_time/10000,js.last_run_time/100%100, 

           js.last_run_time%100,0) END [LastRunDatetime]

   FROM

       dbo.sysjobservers js left outer join msdb.dbo.sysjobs j

         ON jS.job_id = j.job_id

   WHERE

       j.[enabled] = 1

   ORDER BY

       j.[name]

Should give you back something like this:

More details here for sysjobs and sysjobservers:

https://docs.microsoft.com/en-us/sql/relational-databases/system-tables/sql-server-agent-tables-transact-sql

SQL Server -- failure to change the database owner?

One of my customers sent me this error today -- 

     Msg 15110, Level 16, State 1, Line 14

     The proposed new database owner is already a user or aliased in the database.

Said he was trying to change the database owner for a new application.  I asked him to show me what he did, and he sent me this screenshot:

The correction here is fairly simple -- Drop the svcUSR from the database, and add it back in with the ALTER AUTHORIZATION statement.  Like this:

     USE utility;

     DROP USER svcUSR;

     -- add it back in AND change the dbowner

     ALTER AUTHORIZATION ON DATABASE::utility TO svcUSR;    

Very quick, very easy.  Take a look at this for more details regarding ALTER AUTHORIZATION:

      https://docs.microsoft.com/en-us/sql/t-sql/statements/alter-authorization-transact-sql

How to move SQL Server databaes files to a new drive

I've been moving a lot of databases around this week for a new customer, so I thought I'd provide an example for you.  See below to move BOTH your data and log files to a new location, for your SQL Server database.

     /* Where are the files now? */

     USE MAPSampleDB;

     EXEC sp_helpfile

     /* Take exclusive access to the database. */

     ALTER DATABASE MAPSampleDB

     SET OFFLINE WITH ROLLBACK IMMEDIATE;

     /* In Windows Explorer, copy/paste your files to new location. */

     /* Run both of these statements to move both files to a new location. */

     -- data file

     ALTER DATABASE MAPSampleDB

     MODIFY FILE (

            name = 'MAPbeta_SampleDB',

            filename = 'D:\MSSQL\Data\MAPbeta_SampleDB.mdf'

     );

      

     -- log file 

     ALTER DATABASE MAPSampleDB

     MODIFY FILE (

            name = 'MAPbeta_SampleDB_log',

            filename = 'E:\MSSQL\Log\MAPbeta_SampleDB_log.ldf'

     );

     /* Bring your database back online. */

     ALTER DATABASE MAPSampleDB

     SET ONLINE;

     /* Where are you files now?  */

     USE MAPSampleDB;

     EXEC sp_helpfile

If you've got more than one data file, just handle it just like the first one. It really is that simple. Take a look at this for more information on the FILE and FILEGROUP options for ALTER DATABASE:

https://docs.microsoft.com/en-us/sql/t-sql/statements/alter-database-transact-sql-file-and-filegroup-options

How do I change the db owner for a lot of databases?

Good question.  Executing sp_changedbowner a few times is not that big of a deal, but it can become a bit tedious if you're changing it for 10, 20 or 36 databases -- like I did for a customer today.  Just a short post, but I wanted to share it with you.  

You may know, sp_changedbowner has been deprecated.  It's still there, but it will be pulled out in a future release.  The replacement is ALTER AUTHORIZATION, which is what I have used in this post.  Check it out, let me know what you think.

Because there were so many to change, the first thing I did was to write them all into a temp table. This way, we would be able to go back quickly and correct matters, if needed. 

SELECT name [database],suser_sname(owner_sid) [owner]

INTO DBA.dbo.DatabasesWithOwners

FROM sys.databases

WHERE suser_sname(owner_sid) <> 'sa'

Just a safety belt, but your table will be loaded like this:

For the next step, you'd run this against that table you just created -- or against sys.databases, if you did not create the temp table, like this:

SELECT 'ALTER AUTHORIZATION ON DATABASE::' + QUOTENAME(name) + ' TO [sa];'

FROM sys.databases

WHERE suser_sname(owner_sid) <> 'sa' 

You'll get something back like this:

Cut and paste that out into a new query window, and that's that.  The one real quandary, as I told my customer, is knowing whether you have any applications out there that actually depend upon that database ownership belonging to whatever domain user it was previously owned by.  That is why I created the above temp table... and also why it is not recommended to use a domain user's login for database ownership.  But that is another discussion.

Until next time, please take a look at this for more information on ALTER AUTHORIZATION:

    https://technet.microsoft.com/en-us/library/ms187359(v=sql.105).aspx