Skip to main content

Posts

Showing posts from 2019

Script to move SQL Server System Database Files

One issue that can occur when using VM snapshots to back up a server with large database volumes is that the server gets paused when the snapshots are consolidated. We like to refer to this a "stunning" because it sounds worse, and it can cause really bad things if you are using clusters and/or availability groups. If a cluster node gets stunned for more than a few seconds, the other node will think that it needs to fail over. Additionally, when the stun is over, the node that was stunned comes back to life right where it left off, and if it was the owner when it was stunned, all this has to be sorted out, and honestly, after working with Microsoft Cluster for twenty years, you just don't want to put it in this situation unless there's no way to avoid it. So, your backup strategy changes, and you set your large volumes as independent disks so that they are ignored by the VM snapshots. This lets you backup the OS and any other small volumes without a stun. Your

SQL Diagnostics Manager - Script to Disable Maintenance Mode on All Servers

We use Idera's SQL Diagnostics Manager and there is a defect where monitored servers are sometimes not taken out of maintenance mode as specified by the schedule. As a workaround, I wrote the following script that runs each morning to log which servers are in maintenance mode, and disables maintenance mode on all servers. The use of the -MMRecurring switch on Set-SQLdmMonitoredInstance disables maintenance mode and keeps the maintenance mode schedule enabled. add-pssnapin sqldmsnapin $LogFile = "D:\Jobs\DisableMaintenanceModeAll\MMEnabledLog.txt" # Log any servers that are currently in maintenance mode $MMEnabled = invoke-sqlcmd -serverinstance -Query "USE SQLdmRepository; SELECT InstanceName FROM MonitoredSQLServers WHERE MaintenanceModeEnabled = 1" if( $MMEnabled.ItemArray.Count -gt 0 ) { add-content -Value " " -path $LogFile add-content -Value "================================================" -path $LogFile add-content -Value &qu