Murphy realmente é um canalha... Estou ministrando treinamentos de SQL Server para um cliente e ontem iríamos usar um database snapshot que se encaixa perfeitamente em um cenário. para validarmos nosso script, criamos um snapshot e depois fomos removê-lo antes do primeiro uso.
DROP DATABASE XYZSnap
...
Minidump e service down. Divertido não é?!
Nos minutos seguintes tudo em pé funcionando corretamente, encontramos registro no connect e hoje reproduzi com SQL Server 2012 e, com uma variação de comportamento, no 2014.
Abri um item no Connect (https://connect.microsoft.com/SQLServer/feedback/details/1658842), por favor votem se conseguir reproduzir (não em produção, ok?!).
Deixo o texto original que escrevi para referência...
==========================================================
I found this connect item that is exactly what I went thru yesterday in a customer: https://connect.microsoft.com/SQLServer/feedback/details/474486/sql-server-assertion-file-tmpilb-cpp-line-1306-failed-assertion-null-m-ptaskmain-sos-task-getcurrent-m-ptaskmain
When dropping a database snapshot, a DDL trigger uses a linked server to delete a record from a control table. Then a minidump is generated and the service crashes. The SQL Server version is 2008 R2 (SP2) - 10.50.4321.0 (X64).
Using my machine with some instances I could reproduce the problem configuring a linked server from SQL Server 2012 to 2014 and dropping a database snapshot. Here is the beginning of the SQLDumpXXXX.txt:
Computer type is Intel(R) Core(TM) i7-2670QM CPU @ 2.20GHz.
Bios Version is DELL - 2
8 X64 level 8664, 2 Mhz processor (s).
Windows NT 6.2 Build 9200 CSD .
Memory
MemoryLoad = 32%
Total Physical = 16278 MB
Available Physical = 11058 MB
Total Page File = 18746 MB
Available Page File = 12232 MB
Total Virtual = 134217727 MB
Available Virtual = 134200260 MB
**Dump thread - spid = 0, EC = 0x000000019943AB90
***Stack Dump being sent to C:\Program Files\Microsoft SQL Server\MSSQL11.INST2012\MSSQL\LOG\SQLDump0008.txt
* *******************************************************************************
*
* BEGIN STACK DUMP:
* 08/12/15 08:44:42 spid 48
*
* Location: tmpilb.cpp:1301
* Expression: NULL == m_pTaskMain || SOS_Task::GetCurrent() == m_pTaskMain
* SPID: 48
* Process ID: 5644
From SQL Server 2014 with a linked server to SQL Server 2012 the behavior is different. The connection is lost, a minidump is generated, but SQL Server doesn't goes down. The drawback of this is that now I can see an exclusive lock on the database, owned by resquest_session_id -2 (DTC is envolved) and request_owner_type EXCLUSIVE_TRANSACTION_WORKSPACE.
Process ID 52 attempted to unlock a resource it does not own: DATABASE: 20 . Retry the transaction, because this error may be caused by a timing condition. If the problem persists, contact the database administrator.
Msg 0, Level 20, State 0, Line 15
A severe error occurred on the current command. The results, if any, should be discarded.
***Stack Dump being sent to C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\LOG\SQLDump0010.txt
* *******************************************************************************
*
* BEGIN STACK DUMP:
* 08/12/15 09:37:56 spid 16
*
* Location: tmpilb.cpp:1301
* Expression: NULL == m_pTaskMain || SOS_Task::GetCurrent() == m_pTaskMain
* SPID: 16
* Process ID: 7840
In this machine I got the following SQL Server versions. Haven't tried with other builds.
Microsoft SQL Server 2014 - 12.0.2480.0 (X64)
Microsoft SQL Server 2012 - 11.0.5058.0 (X64)
The original connect item was closed as "as Not Reproducible", hopefully you will be able to reproduce this one.
/*
Control database on the server that the linked server points to.
*/
--DROP DATABASE ControlDB
CREATE DATABASE ControlDB
USE ControlDB
GO
-- DROP TABLE DatabaseList
CREATE TABLE DatabaseList
( ID INT IDENTITY NOT NULL
, Name VARCHAR(256) NOT NULL)
GO
USE master
GO
/*
Server where the triggers and linked server are configured.
Authentication is made using a specific security context (SQL Server authentication)
*/
USE master
GO
CREATE TRIGGER [ddl_trig_database]
ON ALL SERVER
FOR CREATE_DATABASE
AS
SET ANSI_PADDING ON
PRINT 'Database Created.'
DECLARE @name Varchar(255)
-- CommandText is wrong in the implementation
SELECT @name = EVENTDATA().value('(/EVENT_INSTANCE/TSQLCommand/CommandText)[1]','nvarchar(max)')
SET @name = replace(@name, 'Create Database ', '')
SET @name = RTRIM(ltrim(@name))
print @name
-- Effectively we don't need to insert nothing
insert into [LUTIXPS\INST2012].ControlDB.dbo.DatabaseList (Name) values (@name)
GO
-- DISABLE TRIGGER [ddl_trig_database] ON ALL SERVER
ENABLE TRIGGER [ddl_trig_database] ON ALL SERVER
GO
CREATE TRIGGER [ddl_trig_database_drop]
ON ALL SERVER
FOR DROP_DATABASE
AS
--PRINT 'Database Droped.'
DECLARE @name Varchar(255)
SELECT @name = EVENTDATA().value('(/EVENT_INSTANCE/TSQLCommand/CommandText)[1]','nvarchar(max)')
print @name + char(13)
SET @name = replace(@name, 'Drop Database ', '')
print @name
Delete [LUTIXPS\INST2012].ControlDB.dbo.DatabaseList WHERE Name = @name
GO
-- DISABLE TRIGGER [ddl_trig_database_drop] ON ALL SERVER
ENABLE TRIGGER [ddl_trig_database_drop] ON ALL SERVER
GO
-- DROP DATABASE Test
CREATE DATABASE Test
-- SELECT * FROM sys.master_files
CREATE DATABASE TestSnap
ON PRIMARY (NAME = 'Test', FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\DATA\Test_SNAP.mdf')
AS SNAPSHOT OF Test
DROP DATABASE testSnap
GO
==========================================================
Abraços
Luciano Caixeta Moreira - {Luti}
luciano.moreira@srnimbus.com.br
www.twitter.com/luticm
www.srnimbus.com.br
Nenhum comentário:
Postar um comentário