The ideal scenario for database mirroring would be to have the principal and secondary server instances be exact clones of each other from a hardware and disk space standpoint. This would ensure that the performance on the mirror instance is the same as the principal instance. But due to multiple constraints, this is not always possible. I recently had a question on how to move a secondary data file to a different physical location on the mirror instance. The catch here is that the physical data file locations on the principal and mirror instances are not the same. Now this can be tricky if not attempted or tested in your environment. The Microsoft SQLCAT(Twitter|Blog) team has a blog post on how to move the transaction log of a mirrored database to a secondary location. We shall use the same logic for the data file as well.
- If the server on which the data file relocation has to be done is not the mirror server currently, then failover the mirrored database so that the server on which the OS File has to be physically moved is the current Mirror.
- Change to High Performance mode if running under High Safety.
- Execute the ALTER DATABASE statement to move the data file to the new location on the mirror instance.
- Pause Mirroring to prevent any automatic failover while OS File Copy is happening.
- Stop the Mirror instance.
- Copy the file across to the new location (OS File Copy).
- Start the Mirror Instance.
- Resume mirroring and switch back to High Safety if the mirroring was running under High Safety. The mirroring should continue without any issues. Failback to original server if needed.
If the data file locations are the same on the principal and mirror and the data files need to be moved to different locations on both the server instances, then this is much easier. You would need to fire the ALTER DATABASE command on the principal server instance and then move the data files to the new location after failing over the database. The new data file location would take effect only after the server instance is restarted.
One of the Maintenance Plan Tasks available is the History Cleanup Task which can help you keep the MSDB database size in check. This Maintenance Plan Task uses the MSDB Stored Procedures sp_delete_backuphistory and sp_purge_jobhistory to cleanup the historical information inside the MSDB system tables.
I have seen multiple scenarios where trying to delete all the historical data has proved to be fatal (because the log file grows disproportionately when the autogrow setting is set to percentage growth and due to large number of logged operations) . In such scenarios, you are left with two choices:
1. Use the Maintenance Plan GUI to modify the oldest date for the History Cleanup Task for shorter durations and perform the cleanup by executing the job for shorter intervals.
2. Use the script below to perform what the Cleanup Task would do.
The script below would give a the list of commands to be executed. If you are concerned about the size of your MSDB transaction log file during the course of this purge operation, use the Checkpoint (if MSDB is in SIMPLE recovery which is the Default. Can’t think of a reason why someone would want it to be full.) piece which is currently commented in the below batch to keep the transaction log in size. Checkpoint operation under Simple Recovery model truncates the transaction log file.
The script can be downloaded from TechNet also in case you face any formatting issues with the script above. Download Link: http://gallery.technet.microsoft.com/ScriptCenter/en-us/b3e2e276-b14a-406d-b67a-d8cbd9e1eabe
-- Declare Local Variables
declare @mindate datetime, @oldest_date datetime, @sql varchar(8000), @currdate datetime, @oldestdate datetime -- Find out the oldest date from the Backup Set table
select @mindate = min(backup_start_date) from msdb..backupset
set @currdate = @mindate + 7
set @oldestdate = '2009-11-04 00:00:00.000' -- Modify this to the date till which you want your msdb history purged while
-- Begin a while loop to generate the commands to purge the MSDB entries
while (@currdate <= @oldestdate)
set @sql = 'EXEC msdb..sp_delete_backuphistory ''' + cast(@currdate as varchar(20)) + ''''
set @sql = 'EXEC msdb..sp_purge_jobhistory @oldest_date = ''' + cast(@currdate as varchar(20)) + ''''
-- Optional if you are running out of space in MSDB
--print 'use msdb' + char(13) + 'checkpoint'
-- Increment value and move on to the next date
set @currdate = @currdate + 7 -- The time interval can be modified to suit your needs end
-- End of while loop
set @sql = 'EXEC msdb..sp_delete_backuphistory ''' + cast(@oldestdate as varchar(20)) + ''''
set @sql = 'EXEC msdb..sp_purge_jobhistory @oldest_date = ''' + cast(@oldestdate as varchar(20)) + ''''
When a SQL Server 2000 Database Maintenance Plan is executed, you find that the following message in printed in the Maint Plan .OUT file (Found in the SQL Server LOG folder):
The backup was not performed since data verification errors were found
[Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 5058: [Microsoft][ODBC SQL Server Driver][SQL Server]Option ‘SINGLE_USER’ cannot be set in database ‘MASTER’. [Microsoft][ODBC SQL Server Driver][SQL Server]sp_dboption command failed.  Database master: Check Data and Index Linkage… [Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 7919: [Microsoft][ODBC SQL Server Driver][SQL Server]Repair statement not processed. Database needs to be in single user mode.
This happens because you had selected the option of "Attempt to repair any minor problems
" (refer above pic). This option needs CHECKDB
to be executed with the database in SINGLE USER mode. If you have users connected to the database during that time, the database cannot be put into SINGLE USER
mode due to which the Integrity Check
task will fail. This will prevent backups from occurring on the database and you would see the above messages in the log files.
DBCC CHECKDB (<database name>, repair_fast) executed by <account name> found 0 errors and repaired 0 errors. Elapsed time: 0 hours 0 minutes 50 seconds.
If you look in the SQL Server ERRORLOG, you will find that the CHECKDB is executed with REPAIR_FAST flag. This requires Single User mode. Another note to keep in mind is if you check the Integrity Check option and "Attempt to repair any minor problems", then exclude MASTER, MSDB and MODEL databases from the Plan.