{"id":305,"date":"2016-02-03T17:10:02","date_gmt":"2016-02-03T08:10:02","guid":{"rendered":"http:\/\/www.onepage.co.kr\/wordpress\/?p=305"},"modified":"2016-04-01T00:43:04","modified_gmt":"2016-03-31T15:43:04","slug":"set-database-from-single-user-mode-to-multi-user","status":"publish","type":"post","link":"https:\/\/www.onepage.co.kr\/wordpress\/index.php\/2016\/02\/03\/set-database-from-single-user-mode-to-multi-user\/","title":{"rendered":"Set database from SINGLE USER mode to MULTI USER"},"content":{"rendered":"

\u200b<\/a><\/p>\n

Set database from SINGLE USER mode to MULTI USER<\/div>\n
<\/div>\n
use master<\/div>\n
GO<\/div>\n
<\/div>\n
declare @sql as varchar(20), @spid as int<\/div>\n
<\/div>\n
select @spid = min(spid) from master..sysprocesses where dbid = db_id(‘sample’) and spid != @@spid<\/div>\n
<\/div>\n
while (@spid is not null)<\/div>\n
<\/div>\n
begin<\/div>\n
<\/div>\n
print ‘Killing process ‘ + cast(@spid as varchar) + ‘ …’<\/div>\n
<\/div>\n
set @sql = ‘kill ‘ + cast(@spid as varchar)<\/div>\n
<\/div>\n
exec (@sql)<\/div>\n
<\/div>\n
select<\/div>\n
@spid = min(spid)<\/div>\n
from<\/div>\n
master..sysprocesses<\/div>\n
where<\/div>\n
dbid = db_id(‘sample’)<\/div>\n
and spid != @@spid<\/div>\n
end<\/div>\n
<\/div>\n
ALTER DATABASE DB_NAME SET MULTI_USER; GO<\/div>\n
<\/div>\n
———————————————–<\/div>\n
select<\/div>\n
\u00a0 \u00a0 d.name,<\/div>\n
\u00a0 \u00a0 d.dbid,<\/div>\n
\u00a0 \u00a0 spid,<\/div>\n
\u00a0 \u00a0 login_time,<\/div>\n
\u00a0 \u00a0 nt_domain,<\/div>\n
\u00a0 \u00a0 nt_username,<\/div>\n
\u00a0 \u00a0 loginame<\/div>\n
from sysprocesses p<\/div>\n
\u00a0 \u00a0 inner join sysdatabases d<\/div>\n
\u00a0 \u00a0 \u00a0 \u00a0 on p.dbid = d.dbid<\/div>\n
where d.name = ‘sample’<\/div>\n
<\/div>\n
GO<\/div>\n
<\/div>\n
<\/div>\n
You can add the option to rollback your change immediately.<\/div>\n
ALTER DATABASE DB_NAME<\/div>\n
SET MULTI_USER<\/div>\n
WITH ROLLBACK IMMEDIATE<\/div>\n
<\/div>\n
<\/div>\n
<\/div>\n
USE master;<\/div>\n
GO<\/div>\n
<\/div>\n
ALTER DATABASE sample<\/div>\n
SET SINGLE_USER<\/div>\n
WITH ROLLBACK IMMEDIATE;<\/div>\n
<\/div>\n
GO<\/div>\n
<\/div>\n
ALTER DATABASE sample<\/div>\n
SET READ_ONLY;<\/div>\n
<\/div>\n
GO<\/div>\n
<\/div>\n
ALTER DATABASE sample<\/div>\n
SET MULTI_USER;<\/div>\n
<\/div>\n
GO<\/div>\n
<\/div>\n
–Make Database Read Only<\/div>\n
USE [master]<\/div>\n
GO<\/div>\n
ALTER DATABASE sample SET READ_ONLY WITH NO_WAIT<\/div>\n
GO<\/div>\n
<\/div>\n
–Make Database Read\/Write<\/div>\n
USE [master]<\/div>\n
GO<\/div>\n
ALTER DATABASE sample SET READ_WRITE WITH NO_WAIT<\/div>\n
GO<\/div>\n","protected":false},"excerpt":{"rendered":"

\u200b Set database from SINGLE USER mode to MULTI USER use master GO declare @sql as varchar(20), @spid as int select @spid = min(spid) from master..sysprocesses where dbid = db_id(‘sample’) and spid != @@spid while (@spid is not null) begin print ‘Killing process ‘ + cast(@spid as varchar) + ‘ …’ set @sql = ‘kill […]<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[5],"tags":[],"class_list":["post-305","post","type-post","status-publish","format-standard","hentry","category-mssql"],"_links":{"self":[{"href":"https:\/\/www.onepage.co.kr\/wordpress\/index.php\/wp-json\/wp\/v2\/posts\/305","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.onepage.co.kr\/wordpress\/index.php\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.onepage.co.kr\/wordpress\/index.php\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.onepage.co.kr\/wordpress\/index.php\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/www.onepage.co.kr\/wordpress\/index.php\/wp-json\/wp\/v2\/comments?post=305"}],"version-history":[{"count":1,"href":"https:\/\/www.onepage.co.kr\/wordpress\/index.php\/wp-json\/wp\/v2\/posts\/305\/revisions"}],"predecessor-version":[{"id":306,"href":"https:\/\/www.onepage.co.kr\/wordpress\/index.php\/wp-json\/wp\/v2\/posts\/305\/revisions\/306"}],"wp:attachment":[{"href":"https:\/\/www.onepage.co.kr\/wordpress\/index.php\/wp-json\/wp\/v2\/media?parent=305"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.onepage.co.kr\/wordpress\/index.php\/wp-json\/wp\/v2\/categories?post=305"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.onepage.co.kr\/wordpress\/index.php\/wp-json\/wp\/v2\/tags?post=305"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}