alert audit backup bashhistory bat centos email hp hyper-v ilo info job jobs linux log mail mssql prtg script shiping sms sql SQL Server Profiler sqript symantec Symantec Backup Exec tar tempdb windows xml архивирование бэкап задание индекс индексы лог размер реиндекс сеть смс таблица трасировка фрагментация хабр хранимая процедура

Июль 2017
Пн	Вт	Ср	Чт	Пт	Сб	Вс
« Июн
	1	2
3	4	5	6	7	8	9
10	11	12	13	14	15	16
17	18	19	20	21	22	23
24	25	26	27	28	29	30
31

лог отмеченных сообщений

Шринк логов (сжать логи) у всех баз на сервере

20.06.2016, Categories Mssql Comments Нет комментариев

Необходимо сжать логи у всех баз на сервере

declare @db nvarchar(10)
declare @sql nvarchar(256)
declare cursor_size_srv cursor for
SELECT  --@@SERVERNAME AS Server ,
        name AS DBName 
        --recovery_model_Desc AS RecoveryModel ,
        --Compatibility_level AS CompatiblityLevel ,
        --create_date ,
        --state_desc
FROM    sys.databases
where name not in ('master','msdb','model','tempdb') 
--and recovery_model_desc = 'full'
ORDER BY Name; 
 
OPEN cursor_size_srv
FETCH NEXT FROM cursor_size_srv INTO @db
WHILE (@@FETCH_STATUS=0)
BEGIN
set @sql = 'USE '+@db+'
GO
DBCC SHRINKFILE (N'''+@db+'_log'' , 0, TRUNCATEONLY)
GO'
print (@sql);
FETCH NEXT FROM cursor_size_srv INTO @db
END
CLOSE cursor_size_srv
DEALLOCATE cursor_size_srv

declare @db nvarchar(10)

declare @sql nvarchar(256)

declare cursor_size_srv cursor for

SELECT --@@SERVERNAME AS Server ,

name AS DBName

--recovery_model_Desc AS RecoveryModel ,

--Compatibility_level AS CompatiblityLevel ,

--create_date ,

--state_desc

FROM sys.databases

where name not in ('master','msdb','model','tempdb')

--and recovery_model_desc = 'full'

ORDER BY Name;

OPEN cursor_size_srv

FETCH NEXT FROM cursor_size_srv INTO @db

WHILE (@@FETCH_STATUS=0)

BEGIN

set @sql = 'USE '+@db+'

DBCC SHRINKFILE (N'''+@db+'_log'' , 0, TRUNCATEONLY)

GO'

print (@sql);

FETCH NEXT FROM cursor_size_srv INTO @db

END

CLOSE cursor_size_srv

DEALLOCATE cursor_size_srv

Результат скопировать и выполнить в новом запросе

USE rss1
GO
DBCC SHRINKFILE (N'rss1_log' , 0, TRUNCATEONLY)
GO
USE rss10
GO
DBCC SHRINKFILE (N'rss10_log' , 0, TRUNCATEONLY)
GO
USE rss11
GO
DBCC SHRINKFILE (N'rss11_log' , 0, TRUNCATEONLY)
GO
USE rss12
GO
DBCC SHRINKFILE (N'rss12_log' , 0, TRUNCATEONLY)
GO
USE rss13
GO
DBCC SHRINKFILE (N'rss13_log' , 0, TRUNCATEONLY)
GO
USE rss14
GO
DBCC SHRINKFILE (N'rss14_log' , 0, TRUNCATEONLY)
GO
USE rss16
GO
DBCC SHRINKFILE (N'rss16_log' , 0, TRUNCATEONLY)
GO
USE rss17
GO
DBCC SHRINKFILE (N'rss17_log' , 0, TRUNCATEONLY)
GO
USE rss18
GO
DBCC SHRINKFILE (N'rss18_log' , 0, TRUNCATEONLY)
GO
USE rss2
GO
DBCC SHRINKFILE (N'rss2_log' , 0, TRUNCATEONLY)
GO
USE rss20
GO
DBCC SHRINKFILE (N'rss20_log' , 0, TRUNCATEONLY)
GO
USE rss21
GO
DBCC SHRINKFILE (N'rss21_log' , 0, TRUNCATEONLY)
GO
USE rss22
GO
DBCC SHRINKFILE (N'rss22_log' , 0, TRUNCATEONLY)
GO
USE rss23
GO
DBCC SHRINKFILE (N'rss23_log' , 0, TRUNCATEONLY)
GO
USE rss24
GO
DBCC SHRINKFILE (N'rss24_log' , 0, TRUNCATEONLY)
GO
USE rss25
GO
DBCC SHRINKFILE (N'rss25_log' , 0, TRUNCATEONLY)
GO
USE rss26
GO
DBCC SHRINKFILE (N'rss26_log' , 0, TRUNCATEONLY)
GO
USE rss27
GO
DBCC SHRINKFILE (N'rss27_log' , 0, TRUNCATEONLY)
GO
USE rss28
GO
DBCC SHRINKFILE (N'rss28_log' , 0, TRUNCATEONLY)
GO
USE rss29
GO
DBCC SHRINKFILE (N'rss29_log' , 0, TRUNCATEONLY)
GO
USE rss31
GO
DBCC SHRINKFILE (N'rss31_log' , 0, TRUNCATEONLY)
GO
USE rss33
GO
DBCC SHRINKFILE (N'rss33_log' , 0, TRUNCATEONLY)
GO
USE rss34
GO
DBCC SHRINKFILE (N'rss34_log' , 0, TRUNCATEONLY)
GO
USE rss38
GO
DBCC SHRINKFILE (N'rss38_log' , 0, TRUNCATEONLY)
GO
USE rss39
GO
DBCC SHRINKFILE (N'rss39_log' , 0, TRUNCATEONLY)
GO
USE rss4
GO
DBCC SHRINKFILE (N'rss4_log' , 0, TRUNCATEONLY)
GO
USE rss40
GO
DBCC SHRINKFILE (N'rss40_log' , 0, TRUNCATEONLY)
GO
USE rss43
GO
DBCC SHRINKFILE (N'rss43_log' , 0, TRUNCATEONLY)
GO
USE rss46
GO
DBCC SHRINKFILE (N'rss46_log' , 0, TRUNCATEONLY)
GO
USE rss47
GO
DBCC SHRINKFILE (N'rss47_log' , 0, TRUNCATEONLY)
GO
USE rss48
GO
DBCC SHRINKFILE (N'rss48_log' , 0, TRUNCATEONLY)
GO
USE rss5
GO
DBCC SHRINKFILE (N'rss5_log' , 0, TRUNCATEONLY)
GO
USE rss50
GO
DBCC SHRINKFILE (N'rss50_log' , 0, TRUNCATEONLY)
GO
USE rss51
GO
DBCC SHRINKFILE (N'rss51_log' , 0, TRUNCATEONLY)
GO
USE rss53
GO
DBCC SHRINKFILE (N'rss53_log' , 0, TRUNCATEONLY)
GO
USE rss54
GO
DBCC SHRINKFILE (N'rss54_log' , 0, TRUNCATEONLY)
GO
USE rss56
GO
DBCC SHRINKFILE (N'rss56_log' , 0, TRUNCATEONLY)
GO
USE rss57
GO
DBCC SHRINKFILE (N'rss57_log' , 0, TRUNCATEONLY)
GO
USE rss57_temp
GO
DBCC SHRINKFILE (N'rss57_temp_log' , 0, TRUNCATEONLY)
GO
USE rss58
GO
DBCC SHRINKFILE (N'rss58_log' , 0, TRUNCATEONLY)
GO
USE rss6
GO
DBCC SHRINKFILE (N'rss6_log' , 0, TRUNCATEONLY)
GO
USE rss62
GO
DBCC SHRINKFILE (N'rss62_log' , 0, TRUNCATEONLY)
GO
USE rss63
GO
DBCC SHRINKFILE (N'rss63_log' , 0, TRUNCATEONLY)
GO
USE rss66
GO
DBCC SHRINKFILE (N'rss66_log' , 0, TRUNCATEONLY)
GO
USE rss7
GO
DBCC SHRINKFILE (N'rss7_log' , 0, TRUNCATEONLY)
GO
USE rss8
GO
DBCC SHRINKFILE (N'rss8_log' , 0, TRUNCATEONLY)
GO
USE rss9
GO
DBCC SHRINKFILE (N'rss9_log' , 0, TRUNCATEONLY)
GO

100

101

102

103

104

105

106

107

108

109

110

111

112

113

114

115

116

117

118

119

120

121

122

123

124

125

126

127

128

129

130

131

132

133

134

135

136

137

138

139

140

141

142

143

144

145

146

147

148

149

150

151

152

153

154

155

156

157

158

159

160

161

162

163

164

165

166

167

168

169

170

171

172

173

174

175

176

177

178

179

180

181

182

183

184

185

186

187

188

USE rss1

DBCC SHRINKFILE (N'rss1_log' , 0, TRUNCATEONLY)

USE rss10

DBCC SHRINKFILE (N'rss10_log' , 0, TRUNCATEONLY)

USE rss11

DBCC SHRINKFILE (N'rss11_log' , 0, TRUNCATEONLY)

USE rss12

DBCC SHRINKFILE (N'rss12_log' , 0, TRUNCATEONLY)

USE rss13

DBCC SHRINKFILE (N'rss13_log' , 0, TRUNCATEONLY)

USE rss14

DBCC SHRINKFILE (N'rss14_log' , 0, TRUNCATEONLY)

USE rss16

DBCC SHRINKFILE (N'rss16_log' , 0, TRUNCATEONLY)

USE rss17

DBCC SHRINKFILE (N'rss17_log' , 0, TRUNCATEONLY)

USE rss18

DBCC SHRINKFILE (N'rss18_log' , 0, TRUNCATEONLY)

USE rss2

DBCC SHRINKFILE (N'rss2_log' , 0, TRUNCATEONLY)

USE rss20

DBCC SHRINKFILE (N'rss20_log' , 0, TRUNCATEONLY)

USE rss21

DBCC SHRINKFILE (N'rss21_log' , 0, TRUNCATEONLY)

USE rss22

DBCC SHRINKFILE (N'rss22_log' , 0, TRUNCATEONLY)

USE rss23

DBCC SHRINKFILE (N'rss23_log' , 0, TRUNCATEONLY)

USE rss24

DBCC SHRINKFILE (N'rss24_log' , 0, TRUNCATEONLY)

USE rss25

DBCC SHRINKFILE (N'rss25_log' , 0, TRUNCATEONLY)

USE rss26

DBCC SHRINKFILE (N'rss26_log' , 0, TRUNCATEONLY)

USE rss27

DBCC SHRINKFILE (N'rss27_log' , 0, TRUNCATEONLY)

USE rss28

DBCC SHRINKFILE (N'rss28_log' , 0, TRUNCATEONLY)

USE rss29

DBCC SHRINKFILE (N'rss29_log' , 0, TRUNCATEONLY)

USE rss31

DBCC SHRINKFILE (N'rss31_log' , 0, TRUNCATEONLY)

USE rss33

DBCC SHRINKFILE (N'rss33_log' , 0, TRUNCATEONLY)

USE rss34

DBCC SHRINKFILE (N'rss34_log' , 0, TRUNCATEONLY)

USE rss38

DBCC SHRINKFILE (N'rss38_log' , 0, TRUNCATEONLY)

USE rss39

DBCC SHRINKFILE (N'rss39_log' , 0, TRUNCATEONLY)

USE rss4

DBCC SHRINKFILE (N'rss4_log' , 0, TRUNCATEONLY)

USE rss40

DBCC SHRINKFILE (N'rss40_log' , 0, TRUNCATEONLY)

USE rss43

DBCC SHRINKFILE (N'rss43_log' , 0, TRUNCATEONLY)

USE rss46

DBCC SHRINKFILE (N'rss46_log' , 0, TRUNCATEONLY)

USE rss47

DBCC SHRINKFILE (N'rss47_log' , 0, TRUNCATEONLY)

USE rss48

DBCC SHRINKFILE (N'rss48_log' , 0, TRUNCATEONLY)

USE rss5

DBCC SHRINKFILE (N'rss5_log' , 0, TRUNCATEONLY)

USE rss50

DBCC SHRINKFILE (N'rss50_log' , 0, TRUNCATEONLY)

USE rss51

DBCC SHRINKFILE (N'rss51_log' , 0, TRUNCATEONLY)

USE rss53

DBCC SHRINKFILE (N'rss53_log' , 0, TRUNCATEONLY)

USE rss54

DBCC SHRINKFILE (N'rss54_log' , 0, TRUNCATEONLY)

USE rss56

DBCC SHRINKFILE (N'rss56_log' , 0, TRUNCATEONLY)

USE rss57

DBCC SHRINKFILE (N'rss57_log' , 0, TRUNCATEONLY)

USE rss57_temp

DBCC SHRINKFILE (N'rss57_temp_log' , 0, TRUNCATEONLY)

USE rss58

DBCC SHRINKFILE (N'rss58_log' , 0, TRUNCATEONLY)

USE rss6

DBCC SHRINKFILE (N'rss6_log' , 0, TRUNCATEONLY)

USE rss62

DBCC SHRINKFILE (N'rss62_log' , 0, TRUNCATEONLY)

USE rss63

DBCC SHRINKFILE (N'rss63_log' , 0, TRUNCATEONLY)

USE rss66

DBCC SHRINKFILE (N'rss66_log' , 0, TRUNCATEONLY)

USE rss7

DBCC SHRINKFILE (N'rss7_log' , 0, TRUNCATEONLY)

USE rss8

DBCC SHRINKFILE (N'rss8_log' , 0, TRUNCATEONLY)

USE rss9

DBCC SHRINKFILE (N'rss9_log' , 0, TRUNCATEONLY)

Триггер для sql 2005

16.12.2015, Categories Mssql Comments Нет комментариев

Создаем таблицу под лог

USE [master]
GO
/****** Объект:  Table [dbo].[DDLTriggerAlterLogin]    Дата сценария: 02/25/2013 15:22:00 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[DDLTriggerAlterLogin](
	[EventType] [nvarchar](100) NULL,
	[ObjectName] [nvarchar](2000) NULL,
	[ObjectType] [nvarchar](2000) NULL,
	[LoginName] [nvarchar](max) NULL,
	[LoginType] [nvarchar](max) NULL,
	[SPID] [nvarchar](max) NULL,
	[PostTime] [datetime] NULL,
	[host] [nvarchar](128) NULL
) ON [PRIMARY]

USE [master]

/****** Объект: Table [dbo].[DDLTriggerAlterLogin] Дата сценария: 02/25/2013 15:22:00 ******/

SET ANSI_NULLS ON

SET QUOTED_IDENTIFIER ON

CREATE TABLE [dbo].[DDLTriggerAlterLogin](

[EventType] [nvarchar](100) NULL,

[ObjectName] [nvarchar](2000) NULL,

[ObjectType] [nvarchar](2000) NULL,

[LoginName] [nvarchar](max) NULL,

[LoginType] [nvarchar](max) NULL,

[SPID] [nvarchar](max) NULL,

[PostTime] [datetime] NULL,

[host] [nvarchar](128) NULL

) ON [PRIMARY]

Создаём триггер для ALTERDATABASE

/****** Object:  DdlTrigger [DDLTriggerAlterDatabase]    Script Date: 03/28/2016 10:59:50 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO


-- =============================================

-- 1) исключение срабатываний для служебной учетной записи head_offive\mssqlserver, log shipping

-- =============================================
CREATE TRIGGER [DDLTriggerAlterDatabase] ON ALL SERVER AFTER ALTER_DATABASE AS

DECLARE @eventdata NVARCHAR(MAX)
DECLARE @loginname NVARCHAR(MAX)

SET @eventdata = (SELECT EVENTDATA().value('(/EVENT_INSTANCE/LoginName)[1]','nvarchar(max)'))
SET @loginname = 'HEAD_OFFICE\MSSQLServer'
IF ( @eventdata!=@loginname )
BEGIN
INSERT INTO master.dbo.DDLTriggerAlterLogin
SELECT 
EVENTDATA().value('(/EVENT_INSTANCE/EventType)[1]', 'nvarchar(100)') 'EventType' ,
EVENTDATA().value('(/EVENT_INSTANCE/ObjectName)[1]', 'nvarchar(2000)') 'ObjectName' ,
EVENTDATA().value('(/EVENT_INSTANCE/ObjectType)[1]', 'nvarchar(2000)') 'ObjectType' ,
EVENTDATA().value('(/EVENT_INSTANCE/LoginName)[1]','nvarchar(max)') 'LoginName',
EVENTDATA().value('(/EVENT_INSTANCE/TSQLCommand/CommandText)[1]','nvarchar(max)') 'LoginType',
EVENTDATA().value('(/EVENT_INSTANCE/SPID)[1]','nvarchar(max)') 'SPID',
EVENTDATA().value('(/EVENT_INSTANCE/PostTime)[1]','datetime') 'PostTime',
HOST_NAME() AS 'host'
END



GO

SET ANSI_NULLS OFF
GO

SET QUOTED_IDENTIFIER OFF
GO

ENABLE TRIGGER [DDLTriggerAlterDatabase] ON ALL SERVER
GO

/****** Object: DdlTrigger [DDLTriggerAlterDatabase] Script Date: 03/28/2016 10:59:50 ******/

SET ANSI_NULLS ON

SET QUOTED_IDENTIFIER ON

-- =============================================

-- 1) исключение срабатываний для служебной учетной записи head_offive\mssqlserver, log shipping

-- =============================================

CREATE TRIGGER [DDLTriggerAlterDatabase] ON ALL SERVER AFTER ALTER_DATABASE AS

DECLARE @eventdata NVARCHAR(MAX)

DECLARE @loginname NVARCHAR(MAX)

SET @eventdata = (SELECT EVENTDATA().value('(/EVENT_INSTANCE/LoginName)[1]','nvarchar(max)'))

SET @loginname = 'HEAD_OFFICE\MSSQLServer'

IF ( @eventdata!=@loginname )

BEGIN

INSERT INTO master.dbo.DDLTriggerAlterLogin

SELECT

EVENTDATA().value('(/EVENT_INSTANCE/EventType)[1]', 'nvarchar(100)') 'EventType' ,

EVENTDATA().value('(/EVENT_INSTANCE/ObjectName)[1]', 'nvarchar(2000)') 'ObjectName' ,

EVENTDATA().value('(/EVENT_INSTANCE/ObjectType)[1]', 'nvarchar(2000)') 'ObjectType' ,

EVENTDATA().value('(/EVENT_INSTANCE/LoginName)[1]','nvarchar(max)') 'LoginName',

EVENTDATA().value('(/EVENT_INSTANCE/TSQLCommand/CommandText)[1]','nvarchar(max)') 'LoginType',

EVENTDATA().value('(/EVENT_INSTANCE/SPID)[1]','nvarchar(max)') 'SPID',

EVENTDATA().value('(/EVENT_INSTANCE/PostTime)[1]','datetime') 'PostTime',

HOST_NAME() AS 'host'

END

SET ANSI_NULLS OFF

SET QUOTED_IDENTIFIER OFF

ENABLE TRIGGER [DDLTriggerAlterDatabase] ON ALL SERVER

Тригер для ALTERLOGIN

CREATE TRIGGER DDLTriggerAlterLogin ON ALL SERVER AFTER ALTER_LOGIN AS
INSERT INTO master.dbo.DDLTriggerAlterLogin
SELECT 
EVENTDATA().value('(/EVENT_INSTANCE/EventType)[1]', 'nvarchar(100)') 'EventType' ,
EVENTDATA().value('(/EVENT_INSTANCE/ObjectName)[1]', 'nvarchar(2000)') 'ObjectName' ,
EVENTDATA().value('(/EVENT_INSTANCE/ObjectType)[1]', 'nvarchar(2000)') 'ObjectType' ,
EVENTDATA().value('(/EVENT_INSTANCE/LoginName)[1]','nvarchar(max)') 'LoginName',
EVENTDATA().value('(/EVENT_INSTANCE/LoginType)[1]','nvarchar(max)') 'LoginType',
EVENTDATA().value('(/EVENT_INSTANCE/SPID)[1]','nvarchar(max)') 'SPID',
EVENTDATA().value('(/EVENT_INSTANCE/PostTime)[1]','datetime') 'PostTime',
host_name() as 'host'
GO
CREATE trigger DML_DDLTriggerAlterLogin ON master.dbo.DDLTriggerAlterLogin AFTER INSERT AS
EXECUTE msdb.dbo.sp_send_dbmail
   @profile_name  = 'atrans',        
   @recipients  = 'email',
   @subject  = 'сработал триггре DDLTriggerAlterLogin',
	@body = 'select 
EventType
,ObjectName
,ObjectType
,LoginName
,LoginType
,SPID
,posttime
,host 
from master.dbo.DDLTriggerAlterLogin'

CREATE TRIGGER DDLTriggerAlterLogin ON ALL SERVER AFTER ALTER_LOGIN AS

INSERT INTO master.dbo.DDLTriggerAlterLogin

SELECT

EVENTDATA().value('(/EVENT_INSTANCE/EventType)[1]', 'nvarchar(100)') 'EventType' ,

EVENTDATA().value('(/EVENT_INSTANCE/ObjectName)[1]', 'nvarchar(2000)') 'ObjectName' ,

EVENTDATA().value('(/EVENT_INSTANCE/ObjectType)[1]', 'nvarchar(2000)') 'ObjectType' ,

EVENTDATA().value('(/EVENT_INSTANCE/LoginName)[1]','nvarchar(max)') 'LoginName',

EVENTDATA().value('(/EVENT_INSTANCE/LoginType)[1]','nvarchar(max)') 'LoginType',

EVENTDATA().value('(/EVENT_INSTANCE/SPID)[1]','nvarchar(max)') 'SPID',

EVENTDATA().value('(/EVENT_INSTANCE/PostTime)[1]','datetime') 'PostTime',

host_name() as 'host'

CREATE trigger DML_DDLTriggerAlterLogin ON master.dbo.DDLTriggerAlterLogin AFTER INSERT AS

EXECUTE msdb.dbo.sp_send_dbmail

@profile_name = 'atrans',

@recipients = 'email',

@subject = 'сработал триггре DDLTriggerAlterLogin',

@body = 'select

EventType

,ObjectName

,ObjectType

,LoginName

,LoginType

,SPID

,posttime

,host

from master.dbo.DDLTriggerAlterLogin'

Тригер для CRATEDATABASE

/****** Object:  DdlTrigger [DDLTriggerCreateDatabase]    Script Date: 03/28/2016 11:01:45 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO
CREATE TRIGGER [DDLTriggerCreateDatabase] ON ALL SERVER AFTER CREATE_DATABASE AS
INSERT INTO master.dbo.DDLTriggerAlterLogin
SELECT 
EVENTDATA().value('(/EVENT_INSTANCE/EventType)[1]', 'nvarchar(100)') 'EventType' ,
EVENTDATA().value('(/EVENT_INSTANCE/ObjectName)[1]', 'nvarchar(2000)') 'ObjectName' ,
EVENTDATA().value('(/EVENT_INSTANCE/ObjectType)[1]', 'nvarchar(2000)') 'ObjectType' ,
EVENTDATA().value('(/EVENT_INSTANCE/LoginName)[1]','nvarchar(max)') 'LoginName',
EVENTDATA().value('(/EVENT_INSTANCE/TSQLCommand/CommandText)[1]','nvarchar(max)') 'LoginType',
EVENTDATA().value('(/EVENT_INSTANCE/SPID)[1]','nvarchar(max)') 'SPID',
EVENTDATA().value('(/EVENT_INSTANCE/PostTime)[1]','datetime') 'PostTime',
HOST_NAME() AS 'host'


GO

SET ANSI_NULLS OFF
GO

SET QUOTED_IDENTIFIER OFF
GO

ENABLE TRIGGER [DDLTriggerCreateDatabase] ON ALL SERVER
GO

/****** Object: DdlTrigger [DDLTriggerCreateDatabase] Script Date: 03/28/2016 11:01:45 ******/

SET ANSI_NULLS ON

SET QUOTED_IDENTIFIER ON

CREATE TRIGGER [DDLTriggerCreateDatabase] ON ALL SERVER AFTER CREATE_DATABASE AS

INSERT INTO master.dbo.DDLTriggerAlterLogin

SELECT

EVENTDATA().value('(/EVENT_INSTANCE/EventType)[1]', 'nvarchar(100)') 'EventType' ,

EVENTDATA().value('(/EVENT_INSTANCE/ObjectName)[1]', 'nvarchar(2000)') 'ObjectName' ,

EVENTDATA().value('(/EVENT_INSTANCE/ObjectType)[1]', 'nvarchar(2000)') 'ObjectType' ,

EVENTDATA().value('(/EVENT_INSTANCE/LoginName)[1]','nvarchar(max)') 'LoginName',

EVENTDATA().value('(/EVENT_INSTANCE/TSQLCommand/CommandText)[1]','nvarchar(max)') 'LoginType',

EVENTDATA().value('(/EVENT_INSTANCE/SPID)[1]','nvarchar(max)') 'SPID',

EVENTDATA().value('(/EVENT_INSTANCE/PostTime)[1]','datetime') 'PostTime',

HOST_NAME() AS 'host'

SET ANSI_NULLS OFF

SET QUOTED_IDENTIFIER OFF

ENABLE TRIGGER [DDLTriggerCreateDatabase] ON ALL SERVER

Тригер для DROPDATABASE

/****** Object:  DdlTrigger [DDLTriggerDropDatabase]    Script Date: 03/28/2016 11:03:21 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO
CREATE TRIGGER [DDLTriggerDropDatabase] ON ALL SERVER AFTER DROP_DATABASE AS
INSERT INTO master.dbo.DDLTriggerAlterLogin
SELECT 
EVENTDATA().value('(/EVENT_INSTANCE/EventType)[1]', 'nvarchar(100)') 'EventType' ,
EVENTDATA().value('(/EVENT_INSTANCE/ObjectName)[1]', 'nvarchar(2000)') 'ObjectName' ,
EVENTDATA().value('(/EVENT_INSTANCE/ObjectType)[1]', 'nvarchar(2000)') 'ObjectType' ,
EVENTDATA().value('(/EVENT_INSTANCE/LoginName)[1]','nvarchar(max)') 'LoginName',
EVENTDATA().value('(/EVENT_INSTANCE/TSQLCommand/CommandText)[1]','nvarchar(max)') 'LoginType',
EVENTDATA().value('(/EVENT_INSTANCE/SPID)[1]','nvarchar(max)') 'SPID',
EVENTDATA().value('(/EVENT_INSTANCE/PostTime)[1]','datetime') 'PostTime',
HOST_NAME() AS 'host'


GO

SET ANSI_NULLS OFF
GO

SET QUOTED_IDENTIFIER OFF
GO

ENABLE TRIGGER [DDLTriggerDropDatabase] ON ALL SERVER
GO

/****** Object: DdlTrigger [DDLTriggerDropDatabase] Script Date: 03/28/2016 11:03:21 ******/

SET ANSI_NULLS ON

SET QUOTED_IDENTIFIER ON

CREATE TRIGGER [DDLTriggerDropDatabase] ON ALL SERVER AFTER DROP_DATABASE AS

INSERT INTO master.dbo.DDLTriggerAlterLogin

SELECT

EVENTDATA().value('(/EVENT_INSTANCE/EventType)[1]', 'nvarchar(100)') 'EventType' ,

EVENTDATA().value('(/EVENT_INSTANCE/ObjectName)[1]', 'nvarchar(2000)') 'ObjectName' ,

EVENTDATA().value('(/EVENT_INSTANCE/ObjectType)[1]', 'nvarchar(2000)') 'ObjectType' ,

EVENTDATA().value('(/EVENT_INSTANCE/LoginName)[1]','nvarchar(max)') 'LoginName',

EVENTDATA().value('(/EVENT_INSTANCE/TSQLCommand/CommandText)[1]','nvarchar(max)') 'LoginType',

EVENTDATA().value('(/EVENT_INSTANCE/SPID)[1]','nvarchar(max)') 'SPID',

EVENTDATA().value('(/EVENT_INSTANCE/PostTime)[1]','datetime') 'PostTime',

HOST_NAME() AS 'host'

SET ANSI_NULLS OFF

SET QUOTED_IDENTIFIER OFF

ENABLE TRIGGER [DDLTriggerDropDatabase] ON ALL SERVER

отключение триггера

disable trigger DML_DDLTriggerAlterLogin ON master.dbo.DDLTriggerAlterLogin
go
enable trigger DML_DDLTriggerAlterLogin ON master.dbo.DDLTriggerAlterLogin

disable trigger DML_DDLTriggerAlterLogin ON master.dbo.DDLTriggerAlterLogin

enable trigger DML_DDLTriggerAlterLogin ON master.dbo.DDLTriggerAlterLogin

disable trigger DDLTriggerAlterLogin ON ALL SERVER
go
enable trigger DDLTriggerAlterLogin ON ALL SERVER

disable trigger DDLTriggerAlterLogin ON ALL SERVER

enable trigger DDLTriggerAlterLogin ON ALL SERVER

AdminBD

Мета

Categories

Свежие записи

Свежие комментарии

лог отмеченных сообщений

Шринк логов (сжать логи) у всех баз на сервере

Триггер для sql 2005