узнать lsn
select * from backupset where database_name like ('WMPRD')
select * from backupset where database_name like ('WMPRD')
-- вычисляем какой процесс вызывает блокировку sp_whoisactive @find_block_leaders = 1, @output_column_list = '[dd%][session_id][sql_text][login_name][wait_info][cpu%][temp%][block%][reads%][writes%][context%][physical%][used_memory][status][open_tran_count][percent%][host_name][database_name][program_name][start_time][login_time][request_id][collection_time]', @destination_table = 'tsd1.dbo.[www_AxaptaInfoBoardWhoIsActive]' -- конец
Задание запускать раз в минуту
SELECT * FROM [tsd1].[dbo].[www_AxaptaInfoBoardWhoIsActive] WHERE CONVERT(CHAR(8),COLLECTION_time,8) BETWEEN '07:00:00' AND '18:00:00' AND datepart(YEAR,start_time) BETWEEN 2015 AND 2015 and datepart(month,start_time) BETWEEN 08 AND 08 and datepart(day,start_time) BETWEEN 20 AND 21 ORDER BY start_time
USE [master] GO /****** Object: StoredProcedure [dbo].[rkur_gooddba_whd] Script Date: 08/18/2015 10:13:03 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO -- ============================================= -- Author: Ruslan Kurianov -- Create date: 2015-05-29 -- Description: Сбор информации о базах данных на сервере -- Version: 0.1 -- ============================================= CREATE PROCEDURE [dbo].[rkur_gooddba_whd] -- Add the parameters for the stored procedure here @sn nvarchar(32)= 'xxx', @dbn nvarchar(32)='rkur_dbastat', @schn nvarchar(32)='dbo', @tbn nvarchar(64)='gooddba_whd' AS BEGIN -- SET NOCOUNT ON added to prevent extra result sets from -- interfering with SELECT statements. SET NOCOUNT ON; if @sn <> 'xxx' BEGIN declare @whd nvarchar(128) set @whd='['+@sn+'].['+@dbn+'].['+@schn+'].['+@tbn+']' IF OBJECT_ID('tempdb..#tmpGood_DBA_project') IS NOT… Читать далее
USE [master] GO /****** Object: StoredProcedure [dbo].[rkur_sendsms_job_status] Script Date: 08/18/2015 10:10:59 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO -- ============================================= -- Author: Ruslan Kurianov -- Create date: 2014-12-19 -- Description: agent mssql job emiltosms -- version: 0.1 -- ============================================= CREATE PROCEDURE [dbo].[rkur_sendsms_job_status] -- Add the parameters for the stored procedure here @text nvarchar(70) AS BEGIN -- SET NOCOUNT ON added to prevent extra result sets from -- interfering with SELECT statements. SET NOCOUNT ON; if len(@text)<=0 begin print 'не указан текст сообщения' print ' ' end ELSE BEGIN -- списки рассылки declare @all nvarchar(128) declare @dba nvarchar(128) --… Читать далее
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)… Читать далее
SELECT * FROM таблица WHERE CONVERT(CHAR(8),поледаты,8) BETWEEN '11:00:00' AND '18:00:00'
SELECT * FROM sys.server_principals
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
create table #t (m ntext) insert #t(m) exec master..xp_cmdshell 'osql -L' select case ltrim(cast(m as varchar(100))) when '(local)' then cast(serverproperty('MachineName') as varchar(100)) else ltrim(cast(m as varchar(100))) end from #t
select * into [destinationTable] from [sourceTable] where 0 = 1