Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / Microsoft SQL Server Новый топик    Ответить
 Автоматический перевод баз в offline  [new]
das-ich
Member

Откуда:
Сообщений: 17
Всем привет.
Написал вот такой скрипт, который переводит указанную базу в offline если к ней нет подключений.

use tempdb
DECLARE @Mybase varchar(50);
DECLARE @SQLText VARCHAR(MAX);
SET @Mybase = 'test';
SET @SQLText = 'alter DATABASE ' + @Mybase + ' set offline';
CREATE TABLE #sp_who2 (SPID INT,Status VARCHAR(255),
      Login  VARCHAR(255),HostName  VARCHAR(255), 
      BlkBy  VARCHAR(255),DBName  VARCHAR(255), 
      Command VARCHAR(255),CPUTime INT, 
      DiskIO INT,LastBatch VARCHAR(255), 
      ProgramName VARCHAR(255),SPID2 INT, 
      REQUESTID INT) 
INSERT INTO #sp_who2 EXEC sp_who2
IF
(SELECT  TOP 1    SPID 
FROM        #sp_who2
WHERE      DBName = @Mybase ) > 0
BEGIN 
	print 'users connected';
end
else
begin
	print 'stopped db';
	exec(@SQLText)
	print @SQLText
end
go
DROP TABLE #sp_who2
go


Он отрабатывает отлично.
Но мне надо, чтоб переводились все базы которые в данный момент не используются.
Добавил cursor и вот какой вышел скрипт:
DECLARE @DB_NAME VARCHAR (50)
DECLARE @CURSOR CURSOR
DECLARE @SQLText VARCHAR(MAX);
SET @SQLText = 'alter DATABASE ' + @DB_NAME + ' set offline';
SET @CURSOR  = CURSOR SCROLL
FOR
SELECT name
FROM master ..sysdatabases where name not in (SELECT name
FROM master ..sysdatabases where name = 'master' or name = 'model'
or name = 'msdb' or name = 'tempdb')
OPEN @CURSOR

FETCH NEXT FROM @CURSOR INTO @DB_NAME
WHILE @@FETCH_STATUS = 0
BEGIN
	print @DB_NAME
CREATE TABLE #sp_who2 (SPID INT,Status VARCHAR(255),
      Login  VARCHAR(255),HostName  VARCHAR(255), 
      BlkBy  VARCHAR(255),DBName  VARCHAR(255), 
      Command VARCHAR(255),CPUTime INT, 
      DiskIO INT,LastBatch VARCHAR(255), 
      ProgramName VARCHAR(255),SPID2 INT, 
      REQUESTID INT) 
INSERT INTO #sp_who2 EXEC sp_who2
IF
(SELECT  TOP 1    SPID 
FROM        #sp_who2
WHERE      DBName = @DB_NAME ) > 0
BEGIN 
	print @DB_NAME
	print ''users connected";
end
else
begin
	print @DB_NAME
	print 'stopped db';
	print @SQLText
	exec(@SQLText)
end
DROP TABLE #sp_who2
FETCH NEXT FROM @CURSOR INTO @DB_NAME
END
CLOSE @CURSOR


Он пишет, что отработал нормально, но при этом не переводит базы offline.
как я понимаю не отрабатывает exec(@SQLText).
Даже print @SQLText не выводит значение, хотя в первом скрипте выводит.

Подскажите, пожалуйста, где ошибка, почему не отрабатывает, а то моих знаний не хватает чтоб это понять.
24 июл 15, 11:41    [17929158]     Ответить | Цитировать Сообщить модератору
 Re: Автоматический перевод баз в offline  [new]
o-o
Guest
ну блин еще AUTOCLOSE базе выставьте.
автоматизированнее некуда
24 июл 15, 11:42    [17929173]     Ответить | Цитировать Сообщить модератору
 Re: Автоматический перевод баз в offline  [new]
komrad
Member

Откуда:
Сообщений: 5249
das-ich,

1) вот это порнография:
SELECT name
FROM master ..sysdatabases where name not in (SELECT name
FROM master ..sysdatabases where name = 'master' or name = 'model'
or name = 'msdb' or name = 'tempdb'


2) @SQLText выставляется один раз, в начале скрипта, превращаясь в NULL
3) зачем кучу раз создавать временную таблицу в курсоре?
4) почему просто не проверять sys.dm_exec_sessions ?
   select * from sys.dm_exec_sessions where db_name(database_id)='master'


и главное: нафига вам это надо? базы обратно сами поднимаются?
24 июл 15, 11:57    [17929315]     Ответить | Цитировать Сообщить модератору
 Re: Автоматический перевод баз в offline  [new]
o-o
Guest
komrad
и главное: нафига вам это надо? базы обратно сами поднимаются?

я скажу, зачем так гадят, например.
у нас ето делается каждую ночь.
они мне испортили сбор статистики по использованию индексов
(sys.dm_db_index_usage_stats)
каждый божий день сбрасывают своим переводом в оффлайн.
пришлось с ними пообщаться.
оказывается, это такой способ выгнать из базы аксессников.
аксессников задолбали оставлять свой аксесс открытым на ночь.
а переводители в оффлайн не знали о существовании SINGLE_USER.
24 июл 15, 12:07    [17929391]     Ответить | Цитировать Сообщить модератору
 Re: Автоматический перевод баз в offline  [new]
komrad
Member

Откуда:
Сообщений: 5249
o-o
оказывается, это такой способ выгнать из базы аксессников.
аксессников задолбали оставлять свой аксесс открытым на ночь.


а аксесс самоподключается? может просто отстреливать сессии?

o-o
переводители в оффлайн не знали о существовании SINGLE_USER.

уже ничему не удивляюсь

например, в одном большом банке дедлоки лечили рестартом сиквела... правда не ДБА, а саппорт
24 июл 15, 12:24    [17929479]     Ответить | Цитировать Сообщить модератору
 Re: Автоматический перевод баз в offline  [new]
das-ich
Member

Откуда:
Сообщений: 17
komrad,

1. я вот так нашел в инете, заработало и хорошо.
2. @SQLText в первом скрипте так же стоит и выполняется. может его надо куда пониже написать?
3. нашел в инете, заработало. я и оставил
4. так сначала надо взять имя базы и потом проверять по всем.

я глубоко не программер, все куски нахватался в инете и объединил. но вот не заработало до конца

У нас есть сервер для разработчиков 1Сных, там около 40 баз, которые периодически обновляем. и все объемом около 20ТБ.
на уровне файловой системы настроена дедупликация, но она не работает пока файл используются.
если их переводить в офлайн, то сразу достаточно места освобождается, но просто останавливать sql нельзя, так как часть разрабов работает и по ночам.
естественно на продуктивах такой ерундой не занимаемся.
24 июл 15, 12:44    [17929590]     Ответить | Цитировать Сообщить модератору
 Re: Автоматический перевод баз в offline  [new]
komrad
Member

Откуда:
Сообщений: 5249
das-ich
на уровне файловой системы настроена дедупликация, но она не работает пока файл используются.
если их переводить в офлайн, то сразу достаточно места освобождается, но просто останавливать sql нельзя, так как часть разрабов работает и по ночам.

дедупликация? по файлам баз данных?

а как вы отличаете работающего разраба от спящего с открытой сессией?
24 июл 15, 12:55    [17929646]     Ответить | Цитировать Сообщить модератору
 Re: Автоматический перевод баз в offline  [new]
o-o
Guest
да у вас же не инициализировано @DB_NAME.
и ваш @SQLText сразу превращается в NULL.
ну выполните самое начало, побавив еще 1 принт:
DECLARE @DB_NAME VARCHAR(50)  -- null
DECLARE @CURSOR CURSOR
DECLARE @SQLText VARCHAR(MAX);
SET @SQLText = 'alter DATABASE ' + @DB_NAME + ' set offline'; -- null!!!
print @SQLText;

ну и потом вы это @SQLText не трогаете совсем, оно и не меняется
24 июл 15, 13:03    [17929672]     Ответить | Цитировать Сообщить модератору
 Re: Автоматический перевод баз в offline  [new]
komrad
Member

Откуда:
Сообщений: 5249
das-ich
естественно на продуктивах такой ерундой не занимаемся.


тогда хотя бы как-то так :

declare @dbs table (dbname varchar(255))
declare @db varchar(255)
declare @cmd varchar(255)

insert into @dbs (dbname)
select name from sys.databases where database_id>4 and state_desc='ONLINE'

while exists (select top 1 1 from @dbs)
	begin
		select top 1 @db=dbname from @dbs

		if exists (select top 1 1 from sys.dm_exec_sessions where db_name(database_id)=@db)
			print @db + ': connected sessions'
		else
			begin
				select @cmd='alter database '+quotename(@db)+' set offline;'
				print @cmd
				/*exec (@cmd)*/
			end
		delete @dbs where dbname=@db
		select @db=''
		select @cmd=''
	end


для перевода в оффлайн раскоментируйте exec (@cmd)
24 июл 15, 13:14    [17929725]     Ответить | Цитировать Сообщить модератору
 Re: Автоматический перевод баз в offline  [new]
o-o
Guest
komrad,
в sys.dm_exec_sessions нет database_id
24 июл 15, 13:19    [17929749]     Ответить | Цитировать Сообщить модератору
 Re: Автоматический перевод баз в offline  [new]
komrad
Member

Откуда:
Сообщений: 5249
o-o
komrad,
в sys.dm_exec_sessions нет database_id

мда, это только в 12-ом появилось
24 июл 15, 13:31    [17929827]     Ответить | Цитировать Сообщить модератору
 Re: Автоматический перевод баз в offline  [new]
komrad
Member

Откуда:
Сообщений: 5249
komrad
o-o
komrad,
в sys.dm_exec_sessions нет database_id

мда, это только в 12-ом появилось


тогда так :

declare @dbs table (dbname varchar(255))
declare @db varchar(255)
declare @cmd varchar(255)

insert into @dbs (dbname)
select name from sys.databases where database_id>4 and state_desc='ONLINE'

while exists (select top 1 1 from @dbs)
	begin
		select top 1 @db=dbname from @dbs

		if exists (select top 1 1 from sys.sysprocesses where db_name(dbid)=@db)
			print @db + ': connected sessions'
		else
			begin
				select @cmd='alter database '+quotename(@db)+' set offline;'
				print @cmd
				/*exec (@cmd)*/
			end
		delete @dbs where dbname=@db
		select @db=''
		select @cmd=''
	end
24 июл 15, 13:35    [17929863]     Ответить | Цитировать Сообщить модератору
 Re: Автоматический перевод баз в offline  [new]
o-o
Guest
sys.sysprocesses
вот. а еще говорят гадости в сторону 2000-ого
а по 2012-ый лучше бы молчали, раз нормально переписать не сумели.

и кстати.
так можно до бесконечности ждать, одного аксессника пусти и дело сделано :)
24 июл 15, 13:40    [17929895]     Ответить | Цитировать Сообщить модератору
 Re: Автоматический перевод баз в offline  [new]
das-ich
Member

Откуда:
Сообщений: 17
komrad,

Не как не отличаем, просто сказали, в административном порядке, что в ночь на среду не работать и выходить из базы.
но уж если кто не вышел, то тогда базу не отключаем.
Уже просто сними напарывались, что они и письма не читают, и собственно руководство не слушают и в итоге теряют то что не сохранили из-за этого, пинать конечно им только на самих себя, но все таки решили, что пусть уж если кто не выйдет, то не чего не потеряет.

komrad, спасибо огромное, все отработает.
24 июл 15, 13:51    [17929975]     Ответить | Цитировать Сообщить модератору
 Re: Автоматический перевод баз в offline  [new]
komrad
Member

Откуда:
Сообщений: 5249
o-o
а по 2012-ый лучше бы молчали, раз нормально переписать не сумели.

это не в мой адрес, я правильно понимаю? ;)
24 июл 15, 13:59    [17930026]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить