Menu
I would like to know what the impact would be by using the with rollback immediate
Basically, in case in production the ALTER DATABASE db_name SET ENABLE_BROKER hangs and not working due to connection issue.
And I know that, ALTER DATABASE db_name SET ENABLE_BROKER with rollback immediate will always work.
But what is the possible impact for this statement really ? Is it going to affect production ? Assume production db is constantly busy doing stuff.
727
The ROLLBACK IMMEDIATE command tells the SQL Server that if it can't complete the command right away, then the other pending transactions should be rolled back. The NO_WAIT command has the opposite effect as ROLLBACK IMMEDIATE. If the ALTER DATABASE transaction can't be completed then that transaction is terminated.
If your database is stuck in EMERGENCY mode, try to look for the last backup to restore the database and recover its information. If the backup is corrupt or unavailable, use a SQL recovery tool like Stellar Repair for MS SQL to restore your database to its original state.
To change the option settings for a databaseIn Object Explorer, connect to a Database Engine instance, expand the server, expand Databases, right-click a database, and then click Properties. In the Database Properties dialog box, click Options to access most of the configuration settings.
The with rollback immediate clause of alter database essentially kills any active process in the database. So to answer your question of whether or not it will affect production, the answer is "very yes".
128
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
