1

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?

  • It sounds unlikely that the changes you described could be the cause of the symptoms you describe. What sizes are the databases on this server? Has anything else changed on the server? Also, what else is running at the time that you are running the integrity check and backup jobs? – Simon Byles Jan 23 '16 at 20:54
  • At that time, only the integrity checks and full backup of System Databases are running. As for the size of databases, msdb is about 1GB, tempdb the same and model,master about 10MB. Just to say that for the same sizes, while 2 minutes needed for all of them, now it needs about 10 minutes. From what I know, nothing has changed to the server. – user20092013 Jan 24 '16 at 6:33
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.

0

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.