Saturday, January 26, 2013

1 Alter database set offline take forever …

There are few options after setting the database offline.

 

Alter database set offline

This is the most basic form for the syntax.

alter database "AdventureWorks" set offline 

This statement would attempt to set the database in the restrict mode but would wait any active processes to complete. This includes if user just simply run “use database” or just there is the internal processes “AUTO_UPDATE_STATISTICS_ASYNC”.  If this database option  “AUTO_UPDATE_STATISTICS_ASYNC” is set, it is possible to block the alter database command.

To check whether the AUTO_UPDATE_STATISTICS_ASYNC is set, we can not find it from sp_helpdb or sp_dboption. We need to check the sys.database table.

ALTER DATABASE "AdventureWorks2" SET AUTO_UPDATE_STATISTICS ON
GO
ALTER DATABASE "AdventureWorks2" SET AUTO_UPDATE_STATISTICS_ASYNC ON
GO
sp_helpdb "AdventureWorks2"
GO
select name,is_auto_update_stats_on,is_auto_update_stats_async_on from sys.databases where name='AdventureWorks2'
Go
sp_dboption "AdventureWorks2"
Go

image

If there is active user connection block the command ( we can find it base on sp_who), just kill the session, and the alter database would continue to bring the database offline.

alter database set offline with no_wait 

alter database "AdventureWorks2" set offline with no_wait  
When use with NO_WAIT option, the alter database command will fail with error if there is any active user connection. So the command would not be hang forever.
image

ALTER DATABASE SET OFFLINE WITH ROLLBACK IMMEDIATE

alter database "AdventureWorks2" set offline WITH ROLLBACK IMMEDIATE 
image

This would terminate all the user connection, processes and set the database offline.

1 comments:

  1. True information, isn't it? Exists many opinions about which app is better to use. Now offline form software https://form.com/form-software/offline-forms/ became more easier that you may think about. Works on all iOS, Android and Windows devices, timesheets / daily field tickets, incident reporting, safety assessment and compliance and many other installations... Offline access to electronic forms and critical documents changes everything.

    ReplyDelete

 

SQL Panda Copyright © 2011 - |- Template created by O Pregador - |- Powered by Blogger Templates