SQL: Audit to find identical schedules on multiple jobs

\ by

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],
		[sjs].[schedule_id]
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.

Posted in: SQL, SQLAgent \ Tagged:

SQL: Start in single user mode and run commands.

\ by

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.
Posted in: SQL, SQL Server

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

\ by

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]
GO
CHECKPOINT;
GO
DBCC DROPCLEANBUFFERS;
GO
DBCC FREEPROCCACHE
GO
DBCC FREESYSTEMCACHE ('ALL');
GO
DBCC FREESESSIONCACHE;
GO
DBCC SHRINKFILE ('tempdev8' , emptyfile)
GO
ALTER DATABASE [tempdb] REMOVE FILE [tempdev8]
GO

 

Posted in: SQL, SQL Server

SQL Configuration: Auto Update Stats Async Enabled

\ by

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:

ALTER DATABASE dbName SET AUTO_UPDATE_STATISTICS ON
ALTER DATABASE dbName SET AUTO_UPDATE_STATISTICS_ASYNC ON

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.

Posted in: Configuration, SQL Server

Msg 3241- Restore SQL Backup error

\ by 
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.

 

Posted in: ERRORs, SQL Server

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

\ by

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

[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.Smo")

 

[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.

Posted in: PowerShell

SQL: Read from the registry

\ by

Now that you have your SQL Agent accounts set up to use DatabaseMail let’s check the registry.

--
-- We have to know the output values to prevent return nothing
--
DECLARE @nvcREG_SZ NVARCHAR(4000),
    @nREG_DWORD INT,
    @vbREG_BINARY VARBINARY

EXEC [master].[dbo].[xp_instance_regread]
    N'HKEY_LOCAL_MACHINE',
    N'SOFTWARE\Microsoft\MSSQLServer\SQLServerAgent',
    N'UseDatabaseMail',
    @nREG_DWORD OUTPUT
SELECT  @nREG_DWORD AS [UseDatabaseMail]

EXEC [master].[dbo].[xp_instance_regread]
    N'HKEY_LOCAL_MACHINE',
    N'SOFTWARE\Microsoft\MSSQLServer\SQLServerAgent',
    N'DatabaseMailProfile',
    @nvcREG_SZ OUTPUT
SELECT  @nvcREG_SZ AS [DatabaseMailProfile]

The nice thing about xp_instance_regread is it will figure out what instance of SQL you are running and so e.g.

 N'HKEY_LOCAL_MACHINE',
 N'SOFTWARE\Microsoft\MSSQLServer\SQLServerAgent'

becomes

 N'HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL11.MSSQLSERVER\SQLServerAgent'
Posted in: SQL, SQLAgent

SQLAgent: Test email an operator

\ by

Create an operator

USE [msdb]
GO

EXEC [msdb].[dbo].[sp_add_operator]
    @name = N'DBA',
    @enabled = 1,
    @weekday_pager_start_time = 90000,
    @weekday_pager_end_time = 180000,
    @saturday_pager_start_time = 90000,
    @saturday_pager_end_time = 180000,
    @sunday_pager_start_time = 90000,
    @sunday_pager_end_time = 180000,
    @pager_days = 0,
    @email_address = N'TheDBAEmail@mycompany.org',
    @category_name = N'[Uncategorized]'
GO

Then test it.

--Send email to an operator called, "DBA', using the profile you created called, "SQLProfile".
USE [msdb];
GO

EXEC [dbo].[sp_notify_operator]
    @profile_name = N'SQLProfile',
    @name = N'DBA',
    @subject = N'Test Notification',
    @body = N'This is a test of notification via e-mail.';
GO

Also check to see if it was sent. Sometimes takes 10-15 seconds to appear…


SELECT  TOP 10 *
FROM    [msdb].[dbo].[sysmail_sentitems]
ORDER BY 1 DESC

And while we are here let’s make ourselves the failsafe operator.

EXEC [master].[dbo].[sp_MSsetalertinfo]
    @failsafeoperator = N'DBA'
GO 
EXEC [master].[dbo].[sp_MSsetalertinfo]
    @notificationmethod = 1
GO
Posted in: SQLAgent \ Tagged: