Архив метки: лог

20Июн 2016

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

Автор: kmosk ⋅ Оставить комментарий ⋅ Aside

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

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

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

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

16Дек 2015

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

Автор: kmosk ⋅ Оставить комментарий

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

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]

Создаём триггер для 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

Тригер для 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'

Тригер для 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

Тригер для 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

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

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

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

Июнь 2019
Пн	Вт	Ср	Чт	Пт	Сб	Вс
« Дек
					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