лог — AdminBD

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

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 [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

Читать далее →

Пн	Вт	Ср	Чт	Пт	Сб	Вс
« Мар
						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

Архив метки: �оУ

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

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