using Ola's backup strategy , there is a job databaseBackup - system databases full, as you can see on the picture below. this job is scheduled to run once a week, on a sunday morning. that is all good, has been running for a while. Now I need to change it to run every day. that works fine using ssms, scripting the change and applying it. however, as I need to get this done in dozens of servers, I will need a script that finds out what is the schedule, and change it and re-apply it to the job I have not achieved it but I wonder if someone has already done that.

I have been working on a query to show me the running jobs. I got it right. the script is below (enjoy):

DECLARE @JOB_NAME SYSNAME=NULL

	SELECT  job.name
			,job.job_id
            ,StepName=CASE WHEN ja.Last_Executed_Step_ID IS NULL THEN js.Step_Name ELSE js2.Step_Name END
			,job.originating_server
			,ja.run_requested_date

			,[Step Executing]=CASE WHEN ja.last_executed_step_id IS NULL
								 THEN 'Step 1 executing'
								 ELSE 'Step ' + convert(varchar(20),  last_executed_step_id + 1)
									  + ' executing'
							 END

            ,[Running For] = CASE WHEN RADHE.YEARS > 0 THEN CAST( RADHE.YEARS AS VARCHAR(20) ) + ' Anni ' ELSE '' END + 
			                CASE WHEN RADHE.MONTHS> 0 THEN CAST( RADHE.MONTHS AS VARCHAR(20) ) + ' Mesi ' ELSE '' END + 
			                CASE WHEN RADHE.WEEKS> 0 THEN CAST( RADHE.WEEKS AS VARCHAR(20) ) + ' Settimane ' ELSE '' END + 
			                CASE WHEN RADHE.DAYS> 0 THEN CAST( RADHE.DAYS AS VARCHAR(20) ) + ' Giorni ' ELSE '' END + 
			                CASE WHEN RADHE.HOURS> 0 THEN CAST( RADHE.HOURS AS VARCHAR(20) ) + ' Ore ' ELSE '' END + 
			                CASE WHEN RADHE.MINUTES> 0 THEN CAST( RADHE.MINUTES AS VARCHAR(20) ) + ' Minuti ' ELSE '' END + 
			                CASE WHEN RADHE.SECONDS> 0 THEN CAST( RADHE.SECONDS AS VARCHAR(20) ) + ' Secondi ' ELSE '' END 
			                          


	FROM    msdb.dbo.sysjobs_view job WITH(NOLOCK)

			INNER JOIN msdb.dbo.sysjobactivity ja WITH(NOLOCK)
			        ON job.job_id = ja.job_id

			INNER JOIN msdb.dbo.syssessions sess  WITH(NOLOCK)
			        ON sess.session_id = ja.session_id

			INNER JOIN ( 
			
			                SELECT   MAX(agent_start_date) AS max_agent_start_date
				             FROM     msdb.dbo.syssessions WITH(NOLOCK)

				       ) sess_max ON sess.agent_start_date = sess_max.max_agent_start_date


			LEFT OUTER JOIN msdb.dbo.SysJobSteps js WITH (nolock) 
			             ON (job.Job_ID = js.Job_ID
			            AND ISNULL(ja.Last_Executed_Step_ID, job.Start_Step_ID) = js.Step_ID)

            LEFT OUTER JOIN msdb.dbo.SysJobSteps js2 WITH (nolock) 
			             ON (js.Job_ID = js2.Job_ID
                        AND (js.On_Success_Step_ID = js2.Step_ID
                         OR (js.On_Success_Action = 3 AND js.Step_ID + 1 = js2.Step_ID)))

           CROSS APPLY(


								SELECT      FLOOR ( UpTime / 31207680 ) AS YEARS
										,   FLOOR( (UpTime / 2600640 ) - FLOOR ( UpTime / 31207680  )  * 12 ) AS MONTHS
										,   FLOOR( (UpTime / 604800 ) - FLOOR ( UpTime / 2600640 )  * 4.3 ) AS WEEKS
										,   FLOOR( (UpTime / 86400 ) - FLOOR( UpTime / 604800 ) * 7 ) AS DAYS
										,   FLOOR( ( UpTime / 3600 ) - FLOOR( UpTime / 86400 ) * 24 ) AS HOURS
										,   FLOOR( ( UpTime / 60 )   - FLOOR( UpTime / 3600 ) * 60 ) AS MINUTES
										,   UpTime - FLOOR( UpTime / 60 ) * 60 AS SECONDS

								FROM        ( 
								
								                SELECT DATEDIFF(SECOND, ja.run_requested_date, GETDATE()) AS UpTime 
												  FROM  msdb.dbo.sysjobactivity Govinda WITH(NOLOCK)
												WHERE   Govinda.job_id     = ja.job_id
												  AND   Govinda.session_id = sess.session_id


												
										     ) AS RadheX


		              ) Radhe 

	WHERE   run_requested_date IS NOT NULL
			AND stop_execution_date IS NULL
			AND ((@JOB_NAME IS NULL) OR (job.name LIKE @JOB_NAME))

this is the result I get: That is all correct as my current environment and time, however, the duration is of the entire job. this one hour and something refers to how long the job has been running, not only this particular step that shrinks the database. How can I find out, the total job duration (as it is) and the duration specifically for the step that is currently running?

I have an environment that we want a full backup once a week (if we fail we can tolerate up to 2 weeks) but the diffs have to be running every day, every day but not the days when the full is scheduled to run . to achieve this I have create a first step called check day on my diff backup job and I put there something like this:

DECLARE @DateInt INT = CAST(CONVERT(VARCHAR(8),GETDATE(),112) AS INT)

DECLARE @NextFullBU INT = ( SELECT next_run_date
                            FROM sysjobschedules b
                            JOIN sysjobs A
                            ON A.job_id = B.job_id
                            JOIN sysschedules s
                            ON b.schedule_id = s.schedule_id
                            WHERE a.name = 'DatabaseBackup - USER_DATABASES - FULL'
                            AND s.name = 'DatabaseBackup - USER_DATABASES - FULL'
                          )

IF @DateInt = @NextFullBU
BEGIN
  RAISERROR ('This job must not execute today because of the FULL backup.',16,127)
END

However, that marks the job as failed, which is not in reality. how can I work around that?

We have an SQL Server 2019 instance (let's call it 'sample_instance') with server collation of Turkish_CI_AS. When I try to create a job, SSMS creates the part of the script that adds the target server as following:

EXEC msdb.dbo.sp_add_jobserver @job_name=N'_test_job',
                               @server_name = N'SAMPLE_INSTANCE'

The problem is that the uppercase of letter 'i' is 'İ' according to the server collation. And this incompatibility between 'i' and 'I' results in problem as the title. I have tried the following:

EXEC sp_dropserver 'sample_instance';
GO
EXEC sp_addserver 'SAMPLE_INSTANCE', 'local';
GO

However, the results were the same. I tried restarting the service and didn't change the results. The latest cumulative update for SQL Server 2019 is installed and I'm using the latest version of SSMS. I can use '**Script Action to New Query Window**' and change the script to

EXEC msdb.dbo.sp_add_jobserver @job_name=N'_test_job',
                               @server_name = N'sample_instance'

and that way it works but I'm trying to find a permanent solution. I cannot change the server collation as all the databases are Turkish_CI_AS and changing the collation could result in bigger problems. Are there any suggestions as to how I could fix this?

Trying to restore MSDB to a replacement server and getting this message: > Msg 3168, Level 16, State 1, Line 4 The backup of the system database on the device e:\sqlbackup\2024-02-27_msdb_DEV.bak cannot be restored because it was created by a different version of the server (**15.00.4335**) than this server (**15.00.4355**). Msg 3013, Level 16, State 1, Line 4 RESTORE DATABASE is terminating abnormally.

I am restoring a database, this database is very big and it has 6 backup files (meaning the backup is split into 6 smaller files) I can see an indication of that using the restore labelonly > MediaFamilyId uniqueidentifier Unique identification number for the media family. > MediaSequenceNumber int Sequence number of this media in the media family. here are the restore commands: restore filelistonly FROM DISK = '\\MY_BACKUP_SERVER\SQLBackups\MY_SERVER\MY_DATABASE\FULL\MY_SERVER_MY_DATABASE_FULL_20231215_180002_6.bak' restore labelonly FROM DISK = '\\MY_BACKUP_SERVER\SQLBackups\MY_SERVER\MY_DATABASE\FULL\MY_SERVER_MY_DATABASE_FULL_20231215_180002_6.bak' restore headeronly FROM DISK = '\\MY_BACKUP_SERVER\SQLBackups\MY_SERVER\MY_DATABASE\FULL\MY_SERVER_MY_DATABASE_FULL_20231215_180002_6.bak' My question is, how can I find (in this case 6) files inside msdb? Basically a query to get these 6 - the last full backup - restore these (6) files.

how to write script to see running jobs in SQL Server with Job Start Time? SELECT sj.name, sja.run_requested_date, CONVERT(VARCHAR(12), sja.stop_execution_date-sja.start_execution_date, 114) Duration FROM msdb.dbo.sysjobactivity sja INNER JOIN msdb.dbo.sysjobs sj ON sja.job_id = sj.job_id WHERE sja.run_requested_date IS NOT NULL ORDER BY sja.run_requested_date desc;

I'm having some problens with SQL Database mail, basicaly anything that I try to do gives me an error like this one below: >Transaction aborted when accessing versioned row in table 'dbo.sysmail_profile' in database 'msdb'. Requested versioned row was not found. Your tempdb is probably out of space. Please refer to BOL on how to configure tempdb for versioning. The error is when I try to delete a profile from database mail, but if I run a query related to change/create a profile, it gives me an error similar to this one. EXECUTE msdb.dbo.sysmail_delete_profile_sp @profile_name = 'Notification'; My tempdb is not full and there is lot of space in the tempdb and in the disk, I also tried to restart the database, but it did not work. Any ideas about what this error can be? I'm kinda stuck with this problem and could not find a solution.

I'm using the below T-SQL query to determine the date of the last full database backup and also return the size and location of the backup file. My problem is that it won't return any data at all for databases that have had no backups or for which there is no backup history for. Ideally, I'd want to modify the query so that all databases are returned, regardless of whether or not there's any backup history for them. Can anyone advise how the below query could be modified to accommodate this? WITH LastBackUp AS ( SELECT bs.database_name, bs.backup_size, bs.backup_start_date, bmf.physical_device_name, Position = ROW_NUMBER() OVER( PARTITION BY bs.database_name ORDER BY bs.backup_start_date DESC ) FROM msdb.dbo.backupmediafamily bmf JOIN msdb.dbo.backupmediaset bms ON bmf.media_set_id = bms.media_set_id JOIN msdb.dbo.backupset bs ON bms.media_set_id = bs.media_set_id WHERE bs.[type] = 'D' AND bs.is_copy_only = 0 ) SELECT database_name AS [Database], CAST(backup_size / 1048576 AS DECIMAL(10, 2) ) AS [BackupSizeMB], backup_start_date AS [Last Full DB Backup Date], physical_device_name AS [Backup File Location] FROM LastBackUp WHERE Position = 1 ORDER BY [Database];

I was having a look at my sql server jobs and proxies on an specific server in the production environment. I pretty much have an script to check the status of the jobs but in case I have to troubleshoot why a job failed on this particular server it has been using the following settings: - log to table - append output to existing entry in table - include step output in history But no file has been specified, so when I click on View to check that has been going on, I get and out of memory exception, as the table is too big. the way I find out the size of this log file (which I am not sure where is stored): use msdb go declare @job_id UNIQUEIDENTIFIER ,@job_name sysname select @job_name= N'DBA - my job name' exec sp_help_jobsteplog @job_name=@job_name there is basically one for each step in the job as you can see on the picture below: =================================== Exception of type 'System.OutOfMemoryException ' was thrown. (Microsoft.SqlServer.Smo) ------------------------------ Program Location: at Microsoft.SqlServer.Management.Smo.Agent.JobStep.EnumLogs() at Microsoft.SqlServer.Management.SqlManagerUI.JobStepAdvancedLogging.ReadStepLogToFile(JobStep step) at Microsoft.SqlServer.Management.SqlManagerUI.JobStepAdvancedLogging.viewTableLog_Click(Object sender, EventArgs e) TITLE: Microsoft SQL Server Management Studio ------------------------------ Exception of type 'System.OutOfMemoryException' was thrown. (Microsoft.SqlServer.Smo) ------------------------------ BUTTONS: OK ------------------------------ My question would be: How do I trim this log file ? I know I could simply set the long to an external text file, but that would only be my second option.

Going through https://www.sqlshack.com/understanding-database-recovery-models/ I see that there are three types of recovery models: - SIMPLE - FULL - BULK_Logged But for the table msdb.dbo.backupset I can see the recovery_model column can be null. In what case would this take a NULL value. How to create a backup such that it falls into this case.

Have a server with regular db backups but strangely sp_delete_backuphistory has never been scheduled so msdb is bigger than expected. Backup history goes back 5 years in that table but only needs last 30 days of backup history. If we run sp_delete_backuphistory keeping only 30 days of history, is there any risk for the transaction log running out of space? MSDB is using simple recovery model. MSDB log file is currently 388544kb on C drive. C drive has 45gb free.

I am using sql Server 2008 R2. We are using below query for reducing the msdb size. use msdb exec sp_delete_backuphistory '01-MAR-2020' When i execute this query in server whose msdb size is 15GB no rows were deleted. Some testing server with lesser rows it worked fine. Why is it not working ? How to reduce the msdb size is there any other method?

i'm an accidental DBA for a company and having trouble figuring out this permissions situation. We have a new 2017 SQL server that I would like to get our report writer / analysts access to a few of the databases. She is in an "analyst" AD group with another analyst. That group is setup at the server level login's with a few of the databases checked off (not MSDB) in the user mapping. While trying to query the databases she should have access to, she gets the same exact errors described in this post (including the MSDB datababase part), **but the other analyst does not**: We can't find the 2 users setup individually anywhere on the server where one would have different access than the other. We also checked other groups to make sure they were not in them. Is it possible it's a way her computer/SSMS is configured? We changed her default SSMS database to a database she should have access to. The resolution in the article I linked suggests to grant connect access to the public role or individual users/groups in MSDB but when testing that, she could view data in that DB and had more access than we would like. I thought I read connect should only grant her enough access to connect to the DB but not see anything, however that didn't seem to be the case. Any suggestions on what we're missing? Let me know what what additional details you may need. Thank you, Dave

Everytime we upgrade a SQL Server installation (service pack or cumulative update), **msdb** is changed from FULL recovery mode to SIMPLE recovery mode. I understand that the **msdb** database doesn't change much and it is common to have it in SIMPLE, but I'm wondering why the installer is changing it. It is causing errors in my backup software. Here is an extract from the installation logs:

2020-02-15 03:06:38.25 spid8s      Starting execution of MSDB.SQL
2020-02-15 03:06:38.25 spid8s      ----------------------------------
2020-02-15 03:06:38.35 spid8s      Configuration option 'allow updates' changed from 0 to 1. Run the RECONFIGURE statement to install.
2020-02-15 03:06:38.35 spid8s      Configuration option 'allow updates' changed from 0 to 1. Run the RECONFIGURE statement to install.
2020-02-15 03:06:38.56 spid8s      Checking the size of MSDB...
2020-02-15 03:06:39.41 spid8s       
2020-02-15 03:06:39.41 spid8s      Setting database option TRUSTWORTHY to ON for database 'msdb'.
2020-02-15 03:06:39.41 spid8s      Setting database option RECOVERY to SIMPLE for database 'msdb'.
2020-02-15 03:06:39.46 spid8s      ----------------------------------
2020-02-15 03:06:39.46 spid8s      Finished execution of MSDB.SQL
2020-02-15 03:06:39.46 spid8s      ----------------------------------
2020-02-15 03:06:39.46 spid8s      -----------------------------------------
2020-02-15 03:06:39.46 spid8s      Starting execution of MSDB_VERSIONING.SQL
2020-02-15 03:06:39.46 spid8s      -----------------------------------------
2020-02-15 03:06:39.47 spid8s      -----------------------------------------
2020-02-15 03:06:39.47 spid8s      Finished execution of MSDB_VERSIONING.SQL
2020-02-15 03:06:39.47 spid8s      -----------------------------------------

I have Availability Group of 3 servers, and transaction log backups for databases in Availability Group are taken on a secondary replica, each 1 minute It was working like this for several months >Now MSDB on this secondary replica is 6 GB data file size (mdf) and is only 4% full and log file size is 14.2 GB (ldf) and is 7% full I can only remember that I made MSDB 500 MB both data and log files in the beginning when server was setup... I thought log file grew because of often transaction log backups and FULL recovery mode - but no, MSDB has SIMPLE recovery mode on that server So it left me wondering, what caused both data and log file growth for MSDB ?

I copied a bunch of jobs from an HA server to a regular server and I need to delete only the first step( checks for the primary) on these jobs not all jobs. Any suggestion? this is the query I used to find them SELECT a.job_id ,name ,enabled ,b.command ,b.step_name ,b.step_id FROM msdb.dbo.sysjobs a INNER JOIN msdb.dbo.sysjobsteps b ON a.job_id = b.job_id where step_name like '%check prima%'

In a production server (Microsoft SQL Server 2014), the file MSDBData.mdf ballooned up to more than 450 GB, eating almost all the space in the server (only 38 GB remaining). First thing, I created a backup of MSDB on a network share (via _SSMS -> activities -> backup_). Then I tried to reduce the size of the file (via SSMS -> activities-> Shrink -> file), but the shrinking is stalled and no disk space has been released. The shrinking dialog window is frozen since hours. In the activities monitoring of SSMS, in processes monitoring the shrinking appears to be in SUSPENDED status. In Event viewer there are several events i.e.

(source MSSQLSERVER, eventid 847) beginning with

 1. Time-out occurred while waiting for latch: class 'FGCB_ADD_REMOVE'
 2. Time-out occurred while waiting for latch: class 'FCB'

The question: is it safe to close the dialog window of SSMS to stop the shrinking of MSDB, in order to carry out further investigation?

I have created a role in my current SQL Server 2012 database with db_datareader, db_datawriter, and execute permissions. A procedure in the current db calls sp_start_job in msdb. How can I grant access to my database role to execute the procedure in msdb? I tried executing the procedure in my current database which calls sp_start_job as owner and the user who is a member the user defined role is still not able to execute the procedure. I have made the server role public for the login, and mapped it to msdb database, but I'm not able to grant permission. The command I'm trying to execute is: GRANT EXECUTE ON OBJECT::[msdb].[dbo].[sp_start_job] TO [db_executor] When I execute from msdb, the error I get is: >Cannot find the user 'db_executor', because it does not exist or you do not have permission'. When I execute from the database in which the user defined role, db_executor is created, it throws this error: >You can only grant or revoke permissions on objects in the current database. I don't want to add users to SQLAgentOperatorRole. The idea is to group all users in one role within the app's database and the members of this role should be able to run a procedure in the database which calls sp_start_job.

good morning. we currently are running on SQL server 2008 r2 and going to Sql server 2016. we are told we cannot do an in place upgrade and will have to restore all SSIS and jobs manually (script, etc) b/c cannot directly restore MSDB. My question - can I backup the msdb, restore on a new, blank database, and then in place upgrade that msdb to SQL Server 2016....after that is complete, backup and restore that upgraded msdb back to the new 2016 instance where it belongs? I'm not sure if this would work as I'm sure msdb contains more info than just jobs and ssis, so I'm afraid it would hose the environment. Thoughts?