I usually run into using Alter statements in my daily DBA work but cannot end up remembering commands (DBA ADHD). So decided to write about them and share which will help others and also help me memorize.
ALTER DATABASE Command:
ALTER DATABASE command as described by Microsoft, a command that modifies a database, or the files and filegroups associated with the database. Adds or removes files and filegroups from a database, changes the attributes of a database or its files and filegroups, changes the database collation, and sets database options. Also it can also modify user permissions.
ALTER DATABASE(T-SQL)
I use it in following scenarios
Setting Database to Single User Mode:
This setting is very useful when you are trying to get exclusive access to database to do the following
- Restoring Database
- Deleting Databases
- Changing Containment Type (SQL 2012)
- Changing Recovery Model
- Making changes to database to get exclusive access
The format is very simple
ALTER DATABASE <database_name>
SET SINGLE USER
This will prevent any new connections to be made but any active connections will block SINGLE USER command. Sometimes there are background processes that will block this command indefinitely. The command can be issued with NO_WAIT but it will fail if there are active connections and will not cause indefinite blocking. Other will option is
ALTER DATABASE <database_name>
SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
ROLLBACK IMMEDIATE will rollback any transactions which are running on the SQL Server database and bring it to single user mode. Basically it will drop all connections at the same time and anything that has not been committed will be dropped. I would recommend contacting your users before issuing this command to prevent any data loss.
If you know there are transaction that will be completed in certain amount of time, a time can be added to be allowed to be blocked and then be executed after it is reached.
ALTER DATABASE <database_name>
SET SINGLE_USER WITH ROLLBACK AFTER 120 SECONDS;
ROOKIE MISTAKE: I have made rookie mistakes with SINGLE USER command and hoping that you don’t have to deal with it. Always try to issue ALTER COMMAND along with the change you are making to prevent any application taking over your single user connection and blocking you. For example
USE [master]
ALTER DATABASE [database_name] SET SINGLE_USER WITH ROLLBACK IMMEDIATE
RESTORE DATABASE [database_name] FROM DISK = N'F:\database_name.bak' WITH FILE = 1, NOUNLOAD, REPLACE, STATS = 5
I have had this happen to me when I was trying to restore production database. I set the database to SINGLE USER mode and then try to issue restore command. The application that use the database made the connection within a second and I lost access to it. I had to figure out which session was making connection to the database using sp_who and sp_who2. You can also use Adam Mechanic sp_whoisactive to find the session. As I killed one session, another session sprung up within a second. I had to request system administrator to shut the application to prevent it from happening.
When you are completed with restoring, database can be changed to multi user mode by issuing
USE [master]
ALTER DATABASE [database_name]
SET MULTI_USER;
GO
I hope this is helpful to you and please feel free to add your comments.
Thanks!
