Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / Microsoft SQL Server Новый топик    Ответить
Топик располагается на нескольких страницах: [1] 2   вперед  Ctrl      все
 Удаление таблиц  [new]
Den94
Member

Откуда:
Сообщений: 32
Доброго времени суток.
Мучает следующая ситуация: в базе есть некоторые таблицы, которые совершенно точно уже не используются и которые хочется удалить. Но их удаление приводят к диким блокировкам в базе, причём они никак не связаны с этими таблица, либо я эту связь не вижу. Восстановление этих таблиц эти блокировки снимает. Уважаемые, если предположение с чем это может быть связано?
5 авг 15, 08:59    [17976759]     Ответить | Цитировать Сообщить модератору
 Re: Удаление таблиц  [new]
AlanDenton
Member [скрыт]

Откуда:
Сообщений: 1004
А что депенденсы говорят?
5 авг 15, 09:26    [17976838]     Ответить | Цитировать Сообщить модератору
 Re: Удаление таблиц  [new]
Den94
Member

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

Ничего, кроме использования в таких же устаревших процедурах, которые не запускаются 100%
5 авг 15, 09:30    [17976863]     Ответить | Цитировать Сообщить модератору
 Re: Удаление таблиц  [new]
AlanDenton
Member [скрыт]

Откуда:
Сообщений: 1004
"которые совершенно точно уже не используются", "кроме использования в таких же устаревших процедурах"... фразы немного противоречят друг другу. На худой конец есть такое понятие как WITH SCHEMABINDING.

Пример. Есть COMPUTED столбец для таблицы А в нем используется скалярная функция с SCHEMABINDING. Внутри скалярки - выборка из таблицы Б. Таблицу Б Вы не удалите.

Смотрите на зависимости...

Также может быть вариант - когда кто-то намеренно лочит таблицу. Например, вот таким запросом:

BEGIN TRANSACTION
		
	DECLARE @t TINYINT;
	SELECT TOP(0) @t = id
	FROM .... WITH(HOLDLOCK, TABLOCKX)

	...

COMMIT TRANSACTION
5 авг 15, 09:39    [17976916]     Ответить | Цитировать Сообщить модератору
 Re: Удаление таблиц  [new]
Glory
Member

Откуда:
Сообщений: 104751
Den94
Но их удаление приводят к диким блокировкам в базе

И что же блокируют эти дикие блокировки ?

Den94
Восстановление этих таблиц эти блокировки снимает.

Т.е. несуществующий объект продолжает что-то блокировать ?
5 авг 15, 09:45    [17976954]     Ответить | Цитировать Сообщить модератору
 Re: Удаление таблиц  [new]
Den94
Member

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

Нет, никаких ограничений и подстановок на столбцах нет, и таблицы не используются в активных запросах совершенно точно. Удалил все триггеры, ключи - пока всё нормально. В обоих таблицах присутствуют индекс со странными названием типа _dta_index_CASH_PAYMENTS_7_1924917929__K4_K1 и _dta_index_DOCTOR_CONSULT_JOBS_7_640773390__K2_3, удалить которые пока не могу ибо база активно используется. Я что-то не знаю про индексы?
5 авг 15, 09:48    [17976966]     Ответить | Цитировать Сообщить модератору
 Re: Удаление таблиц  [new]
AlanDenton
Member [скрыт]

Откуда:
Сообщений: 1004
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED

SELECT
	T.request_session_id,
	MAX(R.blocking_session_id) AS blocking_session_id,
	DB_NAME(T.resource_database_id) AS [database],
	O.object_id,
	ISNULL(SCHEMA_NAME(O.schema_id), OBJECT_SCHEMA_NAME(P.object_id)) AS [Schema],
	ISNULL(O.name, OBJECT_NAME(P.object_id)) AS [Object],
	T.request_mode,
	T.resource_type,
	T.resource_subtype,
	COUNT(*) AS [Lock Number],
	T.request_status,
	(SELECT
		TEXT
	FROM sys.dm_exec_sql_text(MAX(R.sql_handle)))
	AS query,
	S.host_name,
	S.program_name,
	MAX(J.Job) AS [Job],
	S.login_name
FROM sys.dm_tran_locks AS T
LEFT JOIN sys.partitions AS P ON P.hobt_id = T.resource_associated_entity_id
LEFT JOIN sys.dm_exec_sessions AS S ON T.request_session_id = S.session_id
LEFT JOIN sys.all_objects AS O ON O.object_id = T.resource_associated_entity_id
LEFT JOIN sys.dm_exec_requests AS R ON R.session_id = S.session_id
OUTER APPLY (
	SELECT
	CASE PATINDEX('%(Job 0x%', S.program_name)
		WHEN 0 THEN NULL
		ELSE (SELECT
				name
			FROM msdb.dbo.sysjobs AS J
			WHERE [job_id] = CONVERT(UNIQUEIDENTIFIER, CONVERT(BINARY(16), SUBSTRING(S.program_name,
			PATINDEX('%(Job 0x%', S.program_name) + 5, 35), 1), 0))
	END
) AS J ([Job])
WHERE resource_database_id = DB_ID()
AND resource_type NOT IN ('DATABASE', 'FILE', 'METADATA')
AND T.request_session_id <> @@spid
GROUP BY	T.request_session_id,
			T.resource_type,
			T.resource_subtype,
			DB_NAME(T.resource_database_id),
			O.object_id,
			ISNULL(SCHEMA_NAME(O.schema_id), OBJECT_SCHEMA_NAME(P.object_id)),
			ISNULL(O.name, OBJECT_NAME(P.object_id)),
			S.host_name,
			S.program_name,
			S.login_name,
			T.request_mode,
			T.request_status,
			T.request_owner_type


может скажите хоть что это за "блокировки"...
5 авг 15, 09:53    [17976995]     Ответить | Цитировать Сообщить модератору
 Re: Удаление таблиц  [new]
AlanDenton
Member [скрыт]

Откуда:
Сообщений: 1004
_dta... это индексы от Database Tuning Engine Advisor
5 авг 15, 10:00    [17977043]     Ответить | Цитировать Сообщить модератору
 Re: Удаление таблиц  [new]
o-o
Guest
AlanDenton
На худой конец есть такое понятие как WITH SCHEMABINDING.
...
Таблицу Б Вы не удалите.
...
Также может быть вариант - когда кто-то намеренно лочит таблицу.

Den94
их удаление приводят к диким блокировкам в базе
... Восстановление этих таблиц эти блокировки снимает

ТС пишет, что он их удаляет, а потом восстанавливает.
т.е. они удаляются, и когда удалены, "все блокируется"
какое же SCHEMABINDING, если можно удалить?
и какое блокирование того, чего уже нет?

я вижу только "диверсионный" ход развития событий:
некий джоб проверяет наличие определенных таблиц,
и если хоть одной из них нет, начинает намеренно блокировать все таблицы подряд в рандомном порядке
кодом из примера AlanDenton-a.
другой джоб проверяет, не вернулись ли таблицы на место. если да, то убивает первый джоб.

на фантастические вопросы фантастические же ответы.
Den94
Я что-то не знаю про индексы?

вы не знаете, что такое блокировки.
ну, может и про индексы тоже, не исключено
5 авг 15, 10:05    [17977082]     Ответить | Цитировать Сообщить модератору
 Re: Удаление таблиц  [new]
Den94
Member

Откуда:
Сообщений: 32
Удалял скриптом, построенным через ApexSQL Diff. Сейчас удалял поэтапно индексы, ключи и пр. через саму Managment Studio, пока вроде всё нормально. Возможно, неправильно строился скрипт. Касаемо блокировок, они возникали в местах абсолютно никак не связанных с этими таблицами даже идеалогически, например блокировалось выполнение функции типа:
ALTER FUNCTION [dbo].[GetUnreadMessagesCount]
(
@UserID int = 0
)
RETURNS int
AS BEGIN
RETURN
(
SELECT COUNT(m.ID)
FROM imt_MessageRecipients mr
JOIN imt_Messages m ON m.id = mr.MessageID
WHERE
mr.IsRead = 0
AND
mr.RecipientUserID = @UserID
AND
COALESCE(m.ActiveFrom,m.DateCreate)<=GetDate()
)
END

Диверсии исключены, потому что система с этими рудиментами разворачивалась лично мной на чистом сервере
5 авг 15, 10:14    [17977132]     Ответить | Цитировать Сообщить модератору
 Re: Удаление таблиц  [new]
Glory
Member

Откуда:
Сообщений: 104751
Den94
Касаемо блокировок, они возникали в местах абсолютно никак не связанных с этими таблицами даже идеалогически, например блокировалось выполнение функции типа:

Вы вообще в курсе, что такое блокировка в MSSQL ?
5 авг 15, 10:17    [17977153]     Ответить | Цитировать Сообщить модератору
 Re: Удаление таблиц  [new]
Den94
Member

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

просвещайте
5 авг 15, 10:19    [17977167]     Ответить | Цитировать Сообщить модератору
 Re: Удаление таблиц  [new]
Glory
Member

Откуда:
Сообщений: 104751
Den94
Glory,

просвещайте

Круто. Т.е. вы где-то что-то увидели и решили, что это блокировки, а теперь вас надо просветить, что же вы на самом деле увидели ?
5 авг 15, 10:20    [17977174]     Ответить | Цитировать Сообщить модератору
 Re: Удаление таблиц  [new]
AlanDenton
Member [скрыт]

Откуда:
Сообщений: 1004
Вот с этого можно было и начать... ApexSQL Diff... Вы должны же быть в курсе, что данный тулы сторонние страдают своей философией относительно того что и как делать.

Ручная правка лично для меня всегда по-надежнее.

И если честно, "просвещайте" слишком не по теме. Это все есть в гугле. Просто почитайте.
Все мы тут на работе и не обязаны выступать менторами по очевидным вопросам - что такое индекс, блокировка...
5 авг 15, 10:21    [17977182]     Ответить | Цитировать Сообщить модератору
 Re: Удаление таблиц  [new]
Den94
Member

Откуда:
Сообщений: 32
Ок, даже если предположить, что я не знаю, что такое блокировка, поиск по этому же форуму по борьбе с ними выдал ссылку на поделку с названием SQLBlock, которая пару лет давала результат, сопоставимый с моим понятием блокировок. Это не так или не стоит использовать эту программу с благодарностями на 9 страниц?
Я как бы и спрашивал в чём может быть причина, потому что у меня немного потерялась вера в свои знания и опыт после подобных непонятных конкретно мне происшествий, и после гуглений, и после поиска по этим форумам. И спрашивал я потому что, ответов там не нашёл. Честно говоря, ответы в виде "я то знаю в чём дело, но тебе не скажу ибо сам дурак" не очень помогают.
В любом случае всем спасибо за ответы
5 авг 15, 10:34    [17977265]     Ответить | Цитировать Сообщить модератору
 Re: Удаление таблиц  [new]
Glory
Member

Откуда:
Сообщений: 104751
Den94
Ок, даже если предположить, что я не знаю, что такое блокировка,

Тогда почему вы не привели списко ваших "диких блокировок" ?
Особенно интресуют блкоировки "до восстановления таблицы"

Den94
поиск по этому же форуму по борьбе с ними

А почему вы решили, что с блокировками надо бороться ?

Den94
Я как бы и спрашивал в чём может быть причина

Блокировки - это стандартный механизм работы сервера. Любое действие в MSSQL порождает какие-то блокировки.

Den94
Честно говоря, ответы в виде "я то знаю в чём дело, но тебе не скажу ибо сам дурак" не очень помогают.

Ваше описание ваших ощущений с использованием терминов, которые вы не понимаете, тоже не фонтан
5 авг 15, 10:38    [17977293]     Ответить | Цитировать Сообщить модератору
 Re: Удаление таблиц  [new]
AlanDenton
Member [скрыт]

Откуда:
Сообщений: 1004
Чтобы понять в чем была причина нужно как минимум сделать ее репро. Если мы говорили о блокировках, то это результаты выборки что я Вам дал.

Потом разговор зашел о туле. Скрипт, который он генерит Вы не предоставили.

Очень сложно Вам в таком случае помочь. Даже при оном желании.
5 авг 15, 10:39    [17977301]     Ответить | Цитировать Сообщить модератору
 Re: Удаление таблиц  [new]
o-o
Guest
Den94
Диверсии исключены, потому что система с этими рудиментами разворачивалась лично мной на чистом сервере

и где логика?
то же самое могут сказать разворачиватели системы, где я сейчас "разворачиваюсь".
каждый разворачивает свое, тщательно это скрывая.
или вы намекаете, что вы -- моноюзер?
никто-никто не имеет доступ к серверу, совсем?

ну и про "блокирование функции".
вы видели спид-блокировщик и посмотрели, кто и чем занимался?

лучше скажите нам, что вы вообще понимаете под "блокировкой".
выполнение ф-ции длилось больше часа?
а в таблице imt_Messages -- 20 Гиг и внутри лежат "сообщения" типа ntext?
да я такое воспроизведу и без всяких удалений левых таблиц
5 авг 15, 10:43    [17977327]     Ответить | Цитировать Сообщить модератору
 Re: Удаление таблиц  [new]
Den94
Member

Откуда:
Сообщений: 32
Ок, хотелось бы всё таки разобраться в этом странном поведении. Вот скрипт удаления из ApexSQL Diff:
/* ------------------------------------------------------------

DESCRIPTION: Schema Synchronization Script for Object(s)

tables:
[dbo].[DOCTOR_CONSULT_JOBS]

Make (local).Intermed_sam_update Equal (local).Intermed_sam

AUTHOR: [Insert Author Name]

DATE: 05.08.2015 10:49:09

LEGAL: 2015 [Insert Company Name]

------------------------------------------------------------ */

SET NOEXEC OFF
SET ANSI_WARNINGS ON
SET XACT_ABORT ON
SET IMPLICIT_TRANSACTIONS OFF
SET ARITHABORT ON
SET NOCOUNT ON
SET QUOTED_IDENTIFIER ON
SET NUMERIC_ROUNDABORT OFF
SET CONCAT_NULL_YIELDS_NULL ON
SET ANSI_NULLS ON
SET ANSI_PADDING ON
GO

GO
-- Dropping all permissions for the table [dbo].[DOCTOR_CONSULT_JOBS]
Print 'Dropping all permissions for the table [dbo].[DOCTOR_CONSULT_JOBS]'
REVOKE SELECT
ON [dbo].[DOCTOR_CONSULT_JOBS]
TO [DICTIONARY_READER]
GO

BEGIN TRAN
GO

-- Drop Primary Key PK_DOCTOR_CONSULT_JOBS from DOCTOR_CONSULT_JOBS
Print 'Drop Primary Key PK_DOCTOR_CONSULT_JOBS from DOCTOR_CONSULT_JOBS'
GO
ALTER TABLE [dbo].[DOCTOR_CONSULT_JOBS] DROP CONSTRAINT [PK_DOCTOR_CONSULT_JOBS]
GO

IF @@ERROR<>0 OR @@TRANCOUNT=0 BEGIN
IF @@TRANCOUNT>0
ROLLBACK

SET NOEXEC ON
END
GO
-- Drop Foreign Key FK_DOCTOR_CONSULT_JOBS_DOCTOR_CONSULT from DOCTOR_CONSULT_JOBS
Print 'Drop Foreign Key FK_DOCTOR_CONSULT_JOBS_DOCTOR_CONSULT from DOCTOR_CONSULT_JOBS'
GO
IF OBJECT_ID(N'[FK_DOCTOR_CONSULT_JOBS_DOCTOR_CONSULT]') IS NOT NULL
BEGIN
ALTER TABLE [dbo].[DOCTOR_CONSULT_JOBS] DROP CONSTRAINT [FK_DOCTOR_CONSULT_JOBS_DOCTOR_CONSULT]
END
GO

IF @@ERROR<>0 OR @@TRANCOUNT=0 BEGIN
IF @@TRANCOUNT>0
ROLLBACK

SET NOEXEC ON
END
GO
-- Drop Foreign Key FK_DOCTOR_CONSULT_JOBS_DICT_MAIN_JOB from DOCTOR_CONSULT_JOBS
Print 'Drop Foreign Key FK_DOCTOR_CONSULT_JOBS_DICT_MAIN_JOB from DOCTOR_CONSULT_JOBS'
GO
IF OBJECT_ID(N'[FK_DOCTOR_CONSULT_JOBS_DICT_MAIN_JOB]') IS NOT NULL
BEGIN
ALTER TABLE [dbo].[DOCTOR_CONSULT_JOBS] DROP CONSTRAINT [FK_DOCTOR_CONSULT_JOBS_DICT_MAIN_JOB]
END
GO

IF @@ERROR<>0 OR @@TRANCOUNT=0 BEGIN
IF @@TRANCOUNT>0
ROLLBACK

SET NOEXEC ON
END
GO
-- Drop Index _dta_index_DOCTOR_CONSULT_JOBS_7_640773390__K2_3 from DOCTOR_CONSULT_JOBS
Print 'Drop Index _dta_index_DOCTOR_CONSULT_JOBS_7_640773390__K2_3 from DOCTOR_CONSULT_JOBS'
GO
DROP INDEX [_dta_index_DOCTOR_CONSULT_JOBS_7_640773390__K2_3] ON [dbo].[DOCTOR_CONSULT_JOBS]
GO

IF @@ERROR<>0 OR @@TRANCOUNT=0 BEGIN
IF @@TRANCOUNT>0
ROLLBACK

SET NOEXEC ON
END
GO
-- Drop Table DOCTOR_CONSULT_JOBS
Print 'Drop Table DOCTOR_CONSULT_JOBS'
GO
DROP TABLE [dbo].[DOCTOR_CONSULT_JOBS]
GO

IF @@ERROR<>0 OR @@TRANCOUNT=0 BEGIN
IF @@TRANCOUNT>0
ROLLBACK

SET NOEXEC ON
END
GO

IF @@TRANCOUNT>0
COMMIT

SET NOEXEC OFF

По блокировкам, к сожалению, могу показать только xml-лог из истории той же SQL Blocks, подвешивать работающую базу не очень хочется
5 авг 15, 10:52    [17977376]     Ответить | Цитировать Сообщить модератору
 Re: Удаление таблиц  [new]
Glory
Member

Откуда:
Сообщений: 104751
Den94
могу показать только xml-лог из истории той же SQL Blocks, подвешивать работающую базу не очень хочется

Чего вам не хочется делать ? Посмотреть список блокировок ? Потому что это может "подвесить работающую базу" ?
5 авг 15, 10:54    [17977389]     Ответить | Цитировать Сообщить модератору
 Re: Удаление таблиц  [new]
Den94
Member

Откуда:
Сообщений: 32
o-o,

Если бы проблема была на одном сервере, я бы ещё поверил, но она повторяется на системах, развёрнутых в разных городах страны, которые знать друг о друге не знают, и верится с трудом, что некий "злоумышленник" бегает по этим серверам и пакостничает. Ок, примем уже, что я не знаю, что такое блокировка, если от этого будет легче. Ситуация такая: есть таблица, которая не используется 100%, она пуста и с ней никто ничего не делает. После её удаления, зависает вся система в плане скорости выполнения запросов, которые до этого выполняются без проблем. Если у Вас получится воспроизвести подобное - дайте знать, может быть удастся понять в какую сторону копать . Пока разговор не конструктивен
5 авг 15, 11:01    [17977462]     Ответить | Цитировать Сообщить модератору
 Re: Удаление таблиц  [new]
Den94
Member

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

сейчас этих блокировок нет, осталась только их история в виде xml-фйалов
5 авг 15, 11:03    [17977477]     Ответить | Цитировать Сообщить модератору
 Re: Удаление таблиц  [new]
Glory
Member

Откуда:
Сообщений: 104751
Den94
После её удаления, зависает вся система в плане скорости выполнения запросов, которые до этого выполняются без проблем.

И причем тут блокировки ?

Den94
Если у Вас получится воспроизвести подобное - дайте знать, может быть удастся понять в какую сторону копать . Пока разговор не конструктивен

Конструктивним для вас по всей видимости будет такой разговор, когда кто-то узнает, чего же ждет ваша "зависшая система" и объяснит вам как ее починить ?

Den94
сейчас этих блокировок нет, осталась только их история в виде xml-фйалов

Еще раз. Завершенный коненкт не может ничего блокировать. Чтобы бы до этого в этом коннекте не делалось.
5 авг 15, 11:06    [17977500]     Ответить | Цитировать Сообщить модератору
 Re: Удаление таблиц  [new]
o-o
Guest
Den94
Если бы проблема была на одном сервере, я бы ещё поверил, но она повторяется на системах, развёрнутых в разных городах страны, которые знать друг о друге не знают, и верится с трудом, что некий "злоумышленник" бегает по этим серверам и пакостничает.

ну так создатель системы сам мог такое заложить, вот везде и воспроизводится
Den94
Если у Вас получится воспроизвести подобное - дайте знать, может быть удастся понять в какую сторону копать . Пока разговор не конструктивен

да запросто. вместо джоба, чтоб не совсем было видно, можно DDL-триггер присобачить,
а что еще лучше -- event notifications.
каким вам способом воспроизвести?
назовем это "не трожь мои таблицы!"
5 авг 15, 11:11    [17977529]     Ответить | Цитировать Сообщить модератору
 Re: Удаление таблиц  [new]
Den94
Member

Откуда:
Сообщений: 32
Glory
Конструктивним для вас по всей видимости будет такой разговор, когда кто-то узнает, чего же ждет ваша "зависшая система" и объяснит вам как ее починить ?

В этом вопрос как бы и был. Я точно знаю, что система ничего не ждёт не идеологически, не программно (по крайне мере в видимом мне коде). Как можно узнать, где можно посмотреть или подслушать, что она может ещё ждать?
5 авг 15, 11:13    [17977547]     Ответить | Цитировать Сообщить модератору
Топик располагается на нескольких страницах: [1] 2   вперед  Ctrl      все
Все форумы / Microsoft SQL Server Ответить