Do user database settings affect maintenance job times for system databases?

Question

I have an SQL Server 2008 and for maintenance solutions I use Ola Hallengren scripts. Recently, out of 15 databases I have on this server, I made two changes in three of them (not system databases, but user databases). I removed mirroring and changed recovery model from full to simple. This done running sql commands on SQL Server Management Studio.

From the day I made these changes, I noticed strange behavior on Ola Hallengren's sql job for integrity check and full backup on system databases. To understand, while 2 minutes were enough to run and finish the job, now it needs approximately 10 minutes for the same job. As a result, at this time I/O increases on SQL server and it gives timeouts.

I don't know and I didn't find anywhere if running the following commands could possible influence system databases.

ALTER DATABASE <database_name> SET PARTNER OFF
and
USE master ;
ALTER DATABASE <database_name> SET RECOVERY SIMPLE ;

where database_name is the users database I made the change, not the systems.

Is it possible made those changes affect anything on system databases?

Answer 1

Those changes will have no affect on the amount of time system backups take to run.

As a first step I would change the job schedules so that the Integrity checks and System backups are not running at the same time.

Also make sure that there are no other jobs running at the same time as either of these.

If you don't have monitoring in place, jump on the server when the backups are running, have a look and see what load the server is under. You can also try moving the Fulls back by an hour or so and see if the time change makes a difference.

Answer 2

After one week investigation on Sql Server and having taken offline 3 more databases, it seems that I finally fixed the problem by rebuilding indexes on msdb system database. I took a look at fragmentation of indexes and most of them were over 50% average. Rebuilding indexes, sql job for integrity and full backup system databases came back to normal time completion.

The problem remains because I didn't define the root cause and why after rebuilding indexes problem seems to be solved.