May 27, 2008 at 5:33 pm
Is anyone having script that will check for database user access and if it is in any other state(single_user, restricted_user) apart from multi_access then it will put the database in multi_user mode.
Thanks,
Manu
May 27, 2008 at 6:51 pm
Here you are but it does do more than you have requested. Note that RECOVERY is set to SIMPLE.
IF OBJECT_ID('tempdb..#AlterSQL') is not null drop table #AlterSQL
create table #AlterSQL
(AlterSQLnvarchar(4000)
)
insert into #AlterSQL
(AlterSQL)
select AlterSQL
FROM(
selectdb.name
,'ALTER DATABASE [' + db.name + '] SET MULTI_USER' as AlterSQL
from master.dbo.sysdatabases db
where DATABASEPROPERTYEX(db.name,'UserAccess') <> 'MULTI_USER'
and db.name not in ('master','model','tempdb','msdb')
) as X
IF 0 <> (select count(*) from #AlterSQL)
BEGIN
exec sp_execresultset @cmd = 'select AlterSQL from #AlterSQL'
truncate table #AlterSQL
END
insert into #AlterSQL
(AlterSQL)
select AlterSQL
FROM(
selectdb.name
,'ALTER DATABASE [' + db.name + '] SET READ_WRITE' as AlterSQL
from master.dbo.sysdatabases db
where DATABASEPROPERTYEX(db.name,'UserAccess') <> 'READ_WRITE'
and db.name not in ('master','model','tempdb','msdb')
) as X
IF 0 <> (select count(*) from #AlterSQL)
BEGIN
exec sp_execresultset @cmd = 'select AlterSQL from #AlterSQL'
truncate table #AlterSQL
END
insert into #AlterSQL
(AlterSQL)
select AlterSQL
FROM(
-- Turn off options
selectdb.name
,'ALTER DATABASE [' + db.name + '] SET AUTO_CLOSE OFF'as AlterSQL
from master.dbo.sysdatabases db
where DATABASEPROPERTYEX(db.name,'IsAutoClose') = 1
and DATABASEPROPERTYEX(db.name,'Status') = 'Online'
union all
selectdb.name
,'ALTER DATABASE [' + db.name + '] SET AUTO_SHRINK OFF'
from master.dbo.sysdatabases db
where DATABASEPROPERTYEX(db.name,'IsAutoShrink') = 1
and DATABASEPROPERTYEX(db.name,'Status') = 'Online'
union all
-- Turn ON options
selectdb.name
,'ALTER DATABASE [' + db.name + '] SET TORN_PAGE_DETECTION ON'
from master.dbo.sysdatabases db
where DATABASEPROPERTYEX(db.name,'IsTornPageDetectionEnabled') = 0
and DATABASEPROPERTYEX(db.name,'Status') = 'Online'
union all
selectdb.name
,'ALTER DATABASE [' + db.name + '] SET AUTO_CREATE_STATISTICS ON'
from master.dbo.sysdatabases db
where DATABASEPROPERTYEX(db.name,'IsAutoCreateStatistics') = 0
and DATABASEPROPERTYEX(db.name,'Status') = 'Online'
union all
selectdb.name
,'ALTER DATABASE [' + db.name + '] SET AUTO_UPDATE_STATISTICS ON'
from master.dbo.sysdatabases db
where DATABASEPROPERTYEX(db.name,'IsAutoUpdateStatistics') = 0
and DATABASEPROPERTYEX(db.name,'Status') = 'Online'
union all
selectdb.name
,'ALTER DATABASE [' + db.name + '] SET RECOVERY SIMPLE'
from master.dbo.sysdatabases db
where DATABASEPROPERTYEX(db.name,'RECOVERY') <> 'SIMPLE'
and DATABASEPROPERTYEX(db.name,'Status') = 'Online'
) as X
whereX.name not in ('master','model','tempdb','msdb')
IF 0 <> (select count(*) from #AlterSQL)
BEGIN
exec sp_execresultset @cmd = 'select AlterSQL from #AlterSQL'
END
SQL = Scarcely Qualifies as a Language
May 28, 2008 at 5:50 pm
This script will work for me. Thanks a lot Carl.
Manu Jaidka
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply