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?
Best Answer
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.