Перейти к содержимому

Mssql

Все по MSsql

Хранимая процедура отправки сообщения

USE [master]
GO

/****** Object:  StoredProcedure [dbo].[rkur_send_job_status]    Script Date: 08/18/2015 10:08:58 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

-- =============================================
-- Author:                    Ruslan Kurianov
-- Create date: 2013-07-17
-- Description:          agent mssql job email
-- version: 0.3
-- =============================================
CREATE PROCEDURE [dbo].[rkur_send_job_status]

-- статус OK или ERROR
@job_status nvarchar(5) = 'OK',

-- имя задания в агенте mssql
@job_name nvarchar(256),

-- краткое описание задания,понятное человеку
@job_desc nvarchar(256),

-- Имя профиля в MSSQL, с которого надлежит отослать сообщение
@dbmail_profile_name sysname = N'atrans',

-- Список с разделителями (точки с запятыми), содержащий адреса электронной почты, по которым будут рассылаться сообщения
@dbmail_@recipients varchar(max)
… Читать далее

Как сделать фрагментацию индексов и обновление статистики

USE tsd1;

GO

-- Find the average fragmentation percentage of all indexes

-- in the HumanResources.Employee table. 

SELECT a.index_id, name, avg_fragmentation_in_percent

FROM sys.dm_db_index_physical_stats (DB_ID(N'tsd1'), OBJECT_ID(N'dbo.PRICELISTDATA_TSD'), NULL, NULL, NULL) AS a

    JOIN sys.indexes AS b ON a.object_id = b.object_id AND a.index_id = b.index_id; 

GO

 

Сжать лог у всех баз на сервере

--select * from [master].[sys].[databases]

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

 

declare @name nvarchar(512)

declare @cmd nvarchar(512)

DECLARE @Cursor CURSOR

SET @Cursor = CURSOR FOR

select   name from [master].[sys].[databases]

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

 

 

 

OPEN @Cursor

FETCH NEXT FROM @Cursor INTO @name

 

WHILE (@@FETCH_STATUS = 0)

BEGIN

--print @name

 

set @cmd = 

'USE ['+@name+']

GO

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

GO'

 

print @cmd

--exec (@cmd)

 

FETCH NEXT FROM @Cursor INTO @name

end

 

перевод всех бд в simple

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 = 'ALTER DATABASE '+@db+' SET RECOVERY SIMPLE WITH NO_WAIT'
print (@sql);
FETCH NEXT FROM cursor_size_srv INTO @db
END
CLOSE cursor_size_srv
DEALLOCATE cursor_size_srv

 

ресторинг n баз из одного бэкапа под разными именами

declare @name nvarchar(56)

declare @cmd nvarchar(512)

DECLARE @Cursor CURSOR

SET @Cursor = CURSOR FOR

select '0'+right(servername,2) as name from s0055.rkur_dbastat.dbo.ServerName

where is_disable = 0

--and servername = 's1027'

 

OPEN @Cursor

FETCH NEXT FROM @Cursor INTO @name

 

WHILE (@@FETCH_STATUS = 0)

BEGIN

print @name

set @cmd = 'RESTORE DATABASE [planograms'+@name+'] 

FROM DISK= N''H:\MSSQL\Backup\etalon_planograms_20150112.bak'' 

WITH  FILE = 1 

,MOVE  N''Planograms'' TO N''H:\MSSQL\Data\planograms'+@name+'.mdf''

,MOVE N''Planograms_log'' TO N''H:\MSSQL\Data\planograms'+@name+'_1.ldf''

,  NOUNLOAD

,  STATS = 10

'

print @cmd

--exec (@cmd)

 

FETCH NEXT FROM @Cursor INTO @name

end

 

Как посмотреть файл трассировки в самом sql

SELECT top 50 *
FROM fn_trace_gettable(N'\\192.168.0.38\Backup\Trace_sql_serverov\ho_report\TracE20150822            000000.trc',DEFAULT)
where CONVERT(CHAR(8),StartTime,8) BETWEEN '09:00:00' and '23:00:00'
and not ApplicationName in ('NULL','Symantec Backup Exec™ for Windows Servers')
AND ApplicationName NOT LIKE  ('SQLAgent - TSQL%')
AND ApplicationName NOT LIKE  ('DatabaseMail%')
AND textdata not LIKE  ('RESTORE%')
ORDER BY duration  DESC
SELECT *

FROM fn_trace_gettable(N'C:\TraceFiles\LongRunningQueries.trc',DEFAULT);