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
       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%100js.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


Wednesday, July 26, 2017

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:

Friday, June 23, 2017

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:

Monday, January 23, 2017

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























Thursday, January 5, 2017

How do I know if Full-Text Search is installed?

One of my customers asked me this question yesterday. Is SQL Server Full-Text search installed? Any way to know if it's being used?  This is what I told him.

If this first SELECT statement returns 1 on any of your SQL Server instances, that tells us that Full-Text Search was included in the installation.

          SELECT FULLTEXTSERVICEPROPERTY('IsFullTextInstalled')

That only means it was installed.  It does not mean that it is in use.  This next query will look for fulltext catalogs in any of your user databases.  If it finds a catalog, then we could say that it is being used, or at least that it was used at some point in time.  If nothing is returned, that means it was never put to use.


-- create temp table
CREATE TABLE #fulltextinfo (
  DatabaseName VARCHAR(128),
  [FulltextCatalogName] VARCHAR(128));

-- check each db
INSERT #fulltextinfo (DatabaseName,FulltextCatalogName)
EXEC sp_MSforeachdb 'USE ? SELECT ''?'', name FROM sys.fulltext_catalogs;'

-- see if we found any
SELECT * FROM #fulltextinfo

-- cleanup temp table
DROP TABLE #fulltextinfo;



That's pretty much it, though I'd encourage you take a look at this reference for much more detail on sys.fulltext_catalogs:

Thursday, October 27, 2016

Rename a SQL Server database AND its data and log files

Just renamed a database for a customer, and I wanted to share the logic with all of you. When we rename a database, it only effects the database name itself.  It does not change the data or log file names. I HATE it when the data/log file names don't match the db name! Consistently named objects are faaaar easier to manage, imo.  :-)  You can use this method to rename the database AND its data and log files.  Easy peasy.  In this example, the 'InformDVODV' database is renamed to 'InformDVOPD'.

-- get your db filenames
USE InformDVODV; -- current database name
EXEC sp_helpfile

-- physical names and paths
-- only change the data/log file names in the FILENAME value
ALTER DATABASE InformDVODV -- don't change
MODIFY FILE (
       NAME = 'InformDVODV', -- don't change
       FILENAME = 'E:\MSSQL\DATA\InformDVOPD.mdf' -- change this
);
ALTER DATABASE InformDVODV -- don't change
MODIFY FILE (
       NAME = 'InformDVODV_log', -- don't change
       FILENAME = 'O:\MSSQL\Log\InformDVOPD_log.ldf' -- change this
);

-- logical names
-- only change the NEWNAME value
ALTER DATABASE InformDVODV MODIFY FILE (
NAME = InformDVODV,
NEWNAME = InformDVOPD -- change this
);
ALTER DATABASE InformDVODV MODIFY FILE (
NAME = InformDVODV_log,
NEWNAME = InformDVOPD_log -- change this
);

-- now we rename the database
USE master; 
GO 
ALTER DATABASE InformDVODV 
Modify Name = InformDVOPD; 
GO 

-- check your newly renamed files
USE InformDVOPD; -- new db name
EXEC sp_helpfile


That's it!  Important to know, you cannot do this if users are in the database.  If there are active connections, you will need to kill them before attempting the rename. Run this to kill any connections by setting the database to single user mode:

USE [master];
GO
-- disconnect all existing sessions 
ALTER DATABASE InformDVODV SET SINGLE_USER WITH ROLLBACK IMMEDIATE
GO

 And you'll run this after you've renamed the database:

-- change to multi-user
ALTER DATABASE InformDVOPD SET MULTI_USER
GO


That's it!  Follow the sequence and double-check your filenames, this will work fine. I would recommend taking a look at both of these for more details as well:

ALTER DATABASE

RENAME DATABASE