SQL: Test SQLAgentReaderRole role in msdb to allow viewing jobs

Sometimes a developer or analyst needs to see SQL Agent jobs or its history.

Create a login, add to role, open SSMS and log in as that login which has no other permissions, and view jobs, check history, and notice everything is grayed out.

CREATE LOGIN jperryx WITH PASSWORD = '1anGxxp22'

USE [msdb]
CREATE USER [jperryx] FOR LOGIN [jperryx] 
ALTER ROLE [SQLAgentReaderRole] ADD MEMBER [jperryx]

After testing yourself, and knowing what they can do with this access, you can decide if you should grant it or not.

You can find more about it here.

Now clean up.

DROP USER jperryx
DROP LOGIN jperryx

SQL: Find database users who don’t have a login

Find users who don’t have a login. These are real orphans. We find by name because they might be out of sync by id. We assume that’s already been checked.
One caution is some users may have login access via an Active Directory group. Do not run the DROP statements without testing the impact.

SELECT  [sp].[name] AS [LoginName],
        [dp].[name] AS [UserName],
   --     'DROP SCHEMA [' + QUOTENAME([dp].[name]) + '];' + CHAR(10) +
        'DROP USER ' + QUOTENAME([dp].[name]) + ';'
FROM    [sys].[database_principals] [dp]
LEFT OUTER JOIN [sys].[server_principals] [sp]
ON      [sp].[sid] = [dp].[sid]							-- sid could be incorrectly mismatched 
        AND CHARINDEX([dp].[name], [sp].[name]) > 0		-- here we are trying to match by login NAME which makes sense. Occasionally users are created without domain so catch those too.
WHERE   [dp].[type] IN ('U', 'G')
        AND [dp].[principal_id] <> 1
        AND [sp].[name] IS NULL
ORDER BY [UserName]

SQL: Audit to find identical schedules on multiple jobs

At one point it surprised me to see a SQL Agent job schedule in more than one job. A lot of things can cause this, especially if you script and clone jobs.

Here’s the audit you can use to find the jobs which share the exact same schedule. If you change it in one place, ooops, it changes everywhere!

Audit to find identical schedules on multiple jobs

SELECT  [j].[name] [JobName],
        [s].[name] [DupScheduleName],
FROM    [msdb].[dbo].[sysjobschedules] [sjs]
JOIN    [msdb].[dbo].[sysschedules] [s]
ON      [s].[schedule_id] = [sjs].[schedule_id]
JOIN    [msdb].[dbo].[sysjobs] [j]
ON      [j].[job_id] = [sjs].[job_id]
WHERE   [sjs].[schedule_id] IN (SELECT   [sjs].[schedule_id]
                               FROM     [msdb].[dbo].[sysjobschedules] [sjs]
                               JOIN     [msdb].[dbo].[sysschedules] [s]
                               ON       [s].[schedule_id] = [sjs].[schedule_id]
                               GROUP BY [sjs].[schedule_id]
                               HAVING   COUNT(*) > 1)

After you find them, you will have to drop the dups and recreate using a different name. Possibly just recreating and getting a new schedule_id would work as well.

SQL: Start in single user mode and run commands.

Change the SQL server service for SQL Server 2012 2014 to this
adding -m to the startup parameters.

Before you do that you really want to

  • Get everyone off the server.
  • Disable SQL Agent service changing to manual
  • Close any SSMS object explorer windows you or other administrators have open.
    • Remember it’s single user mode.
  • Stop the services, for SQL and any others which may automatically reconnect once SQL is back up.
  • Possibly leave SSMS open with a query window with the commands you want to run in single user mode.

Click and restart the SQL service. After it has restarted [re-]open a SSMS query window and run the commands.

Once done

  • Stop SQL Service
  • Change the properties removing the -m from the startup parameters
  • Change SQL Agent to autostart and or whatever else you had to change to aget SQL into single user mode.

SQL: Msg 2555 Cannot move all contents of file “tempdev8″ to other places to complete the emptyfile operation.

SQL Server 2014. Suddenly we can’t seem to remove extra tempdb files (which were added after we reduced CPU).

I’m getting this error:

DBCC SHRINKFILE: Page 9:505072 could not be moved because it is a work table page.
Msg 2555, Level 16, State 1, Line 94
Cannot move all contents of file "tempdev8" to other places to complete the emptyfile operation.
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
Msg 5042, Level 16, State 1, Line 95
The file 'tempdev8' cannot be removed because it is not empty.

Since it was a test server I could try to bounce the server. But even on restart it still occurred. The only fix was to put SQL into single user mode and run the following.

USE [tempdb]
DBCC SHRINKFILE ('tempdev8' , emptyfile)
ALTER DATABASE [tempdb] REMOVE FILE [tempdev8]


SQL Configuration: Auto Update Stats Async Enabled

Brent Ozar is the best starting place for “Auto Update Stats Async Enabled” and has the reference links for further research. He says:

We recommend setting it back to the default (disabled) unless you’ve got evidence that delays caused by statistics updates are causing you problems.

Tara Kizer warns:

Here is how to enable the option:


Enabling the async option affects your ability to put a database into single-user mode.  The option must be disabled to put a database into single-user mode as the async option uses a background thread which takes a connection in the database.

Since I’ll probably forget changing the setting and we don’t have problems at the moment with rogue queries, and we sometimes change to SINGLE USER MODE, I’ll pass.

Msg 3241- Restore SQL Backup error

RESTORE filelistonly
FROM DISK = N'E:\MSSQL\My2012Backup'

Ooops. Trying to restore a more current database backup on an older server got me this error.

Msg 3241, Level 16, State 13, Line 2
The media family on device 'E:\MSSQL\My2012backup' is incorrectly formed. SQL Server cannot process this media family.
Msg 3013, Level 16, State 1, Line 2
RESTORE FILELIST is terminating abnormally.

Run the restore command on a newer version of SQL fixes the error.


Load the latest .NET assembly installed in your system, e.g. for SQL SMO

Load the latest .NET assembly for that PowerShell version using the following command:



[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SMO") | out-null
 $SMOserver = New-Object ('Microsoft.SqlServer.Management.Smo.Server') -argumentlist $SrcServer
 $WhichVersion = $SMOserver.Version.Major

This command will grab the latest SMO installed in your system. You can also use this method with PowerShell 2.0 and later. More here.