跟踪数据库中执行时间超过1.5秒的语句及SP，导入数据库
============================================================================
--新建两个目录 D:\InOut\TraceDB D:\InOut\TraceLog\
--建数据库，建跟踪执行时间超过1.5秒的语句及SP
--建作业，每天在固定时间将跟踪文件导入数据库
--============================================================================
USE [master]
GO
/****** Object: Database [TraceDB] Script Date: 2017/2/15 11:16:02 ******/
CREATE DATABASE [TraceDB]
CONTAINMENT = NONE
ON PRIMARY
( NAME = N‘TraceDB‘, FILENAME = N‘D:\inout\TraceDB\TraceDB.mdf‘ , SIZE = 102400KB , MAXSIZE = UNLIMITED, FILEGROWTH = 102400KB )
LOG ON
( NAME = N‘TraceDB_log‘, FILENAME = N‘D:\inout\TraceDB\TraceDB_log.ldf‘ , SIZE = 20416KB , MAXSIZE = 2048GB , FILEGROWTH = 102400KB )
GO
--ALTER DATABASE [TraceDB] SET COMPATIBILITY_LEVEL = 120
--GO
IF (1 = FULLTEXTSERVICEPROPERTY(‘IsFullTextInstalled‘))
begin
EXEC [TraceDB].[dbo].[sp_fulltext_database] @action = ‘enable‘
end
GO
ALTER DATABASE [TraceDB] SET ANSI_NULL_DEFAULT OFF
GO
ALTER DATABASE [TraceDB] SET ANSI_NULLS OFF
GO
ALTER DATABASE [TraceDB] SET ANSI_PADDING OFF
GO
ALTER DATABASE [TraceDB] SET ANSI_WARNINGS OFF
GO
ALTER DATABASE [TraceDB] SET ARITHABORT OFF
GO
ALTER DATABASE [TraceDB] SET AUTO_CLOSE OFF
GO
ALTER DATABASE [TraceDB] SET AUTO_SHRINK OFF
GO
ALTER DATABASE [TraceDB] SET AUTO_UPDATE_STATISTICS ON
GO
ALTER DATABASE [TraceDB] SET CURSOR_CLOSE_ON_COMMIT OFF
GO
ALTER DATABASE [TraceDB] SET CURSOR_DEFAULT GLOBAL
GO
ALTER DATABASE [TraceDB] SET CONCAT_NULL_YIELDS_NULL OFF
GO
ALTER DATABASE [TraceDB] SET NUMERIC_ROUNDABORT OFF
GO
ALTER DATABASE [TraceDB] SET QUOTED_IDENTIFIER OFF
GO
ALTER DATABASE [TraceDB] SET RECURSIVE_TRIGGERS OFF
GO
ALTER DATABASE [TraceDB] SET ENABLE_BROKER
GO
ALTER DATABASE [TraceDB] SET AUTO_UPDATE_STATISTICS_ASYNC OFF
GO
ALTER DATABASE [TraceDB] SET DATE_CORRELATION_OPTIMIZATION OFF
GO
ALTER DATABASE [TraceDB] SET TRUSTWORTHY OFF
GO
ALTER DATABASE [TraceDB] SET ALLOW_SNAPSHOT_ISOLATION OFF
GO
ALTER DATABASE [TraceDB] SET PARAMETERIZATION SIMPLE
GO
ALTER DATABASE [TraceDB] SET READ_COMMITTED_SNAPSHOT OFF
GO
ALTER DATABASE [TraceDB] SET HONOR_BROKER_PRIORITY OFF
GO
ALTER DATABASE [TraceDB] SET RECOVERY FULL
GO
ALTER DATABASE [TraceDB] SET MULTI_USER
GO
ALTER DATABASE [TraceDB] SET PAGE_VERIFY CHECKSUM
GO
ALTER DATABASE [TraceDB] SET DB_CHAINING OFF
GO
ALTER DATABASE [TraceDB] SET FILESTREAM( NON_TRANSACTED_ACCESS = OFF )
GO
ALTER DATABASE [TraceDB] SET TARGET_RECOVERY_TIME = 0 SECONDS
GO
ALTER DATABASE [TraceDB] SET DELAYED_DURABILITY = DISABLED
GO
EXEC sys.sp_db_vardecimal_storage_format N‘TraceDB‘, N‘ON‘
GO
USE [TraceDB]
GO
/****** Object: UserDefinedFunction [dbo].[Split] Script Date: 2017/2/15 11:16:02 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
--表值函数用以截取字符串
--如果为其添加一列主键id,则其顺序就会固定了
create FUNCTION [dbo].[Split](@text NVARCHAR(max))
RETURNS @tempTable TABLE(value NVARCHAR(1000))
AS
BEGIN
DECLARE @StartIndex INT --开始查找的位置
DECLARE @FindIndex INT --找到的位置
DECLARE @Content VARCHAR(4000) --找到的值
--初始化一些变量
SET @StartIndex = 1 --T-SQL中字符串的查找位置是从1开始的
SET @FindIndex=0
--开始循环查找字符串逗号
WHILE(@StartIndex <= LEN(@Text))
BEGIN
--查找字符串函数 CHARINDEX 第一个参数是要找的字符串
-- 第二个参数是在哪里查找这个字符串
-- 第三个参数是开始查找的位置
--返回值是找到字符串的位置
SELECT @FindIndex = CHARINDEX(‘,‘ ,@Text,@StartIndex)
--判断有没找到 没找到返回0
IF(@FindIndex =0 OR @FindIndex IS NULL)
BEGIN
--如果没有找到就表示找完了
SET @FindIndex = LEN(@Text)+1
END
--截取字符串函数 SUBSTRING 第一个参数是要截取的字符串
-- 第二个参数是开始的位置
-- 第三个参数是截取的长度
SET @Content =SUBSTRING(@Text,@StartIndex,@FindIndex-@StartIndex)
--初始化下次查找的位置
SET @StartIndex = @FindIndex+1
--把找的的值插入到要返回的Table类型中
INSERT INTO @tempTable (Value) VALUES (@Content)
END
RETURN
END
GO
/****** Object: Table [dbo].[CommandLog] Script Date: 2017/2/15 11:16:02 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[CommandLog](
[ID] [int] IDENTITY(1,1) NOT NULL,
[DatabaseName] [sysname] NULL,
[SchemaName] [sysname] NULL,
[ObjectName] [sysname] NULL,
[ObjectType] [char](2) NULL,
[IndexName] [sysname] NULL,
[IndexType] [tinyint] NULL,
[StatisticsName] [sysname] NULL,
[PartitionNumber] [int] NULL,
[ExtendedInfo] [xml] NULL,
[Command] [nvarchar](max) NOT NULL,
[CommandType] [nvarchar](60) NOT NULL,
[StartTime] [datetime] NOT NULL,
[EndTime] [datetime] NULL,
[ErrorNumber] [int] NULL,
[ErrorMessage] [nvarchar](max) NULL,
CONSTRAINT [PK_CommandLog] PRIMARY KEY CLUSTERED
(
[ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
/****** Object: Table [dbo].[TraceLog] Script Date: 2017/2/15 11:16:02 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[TraceLog](
[RowNumber] [int] IDENTITY(0,1) NOT NULL,
[EventClass] [int] NULL,
[Duration] [bigint] NULL,
[TextData] [ntext] NULL,
[SPID] [int] NULL,
[BinaryData] [image] NULL,
[CPU] [int] NULL,
[EndTime] [datetime] NULL,
[ObjectName] [nvarchar](128) NULL,
[StartTime] [datetime] NULL,
[Reads] [bigint] NULL,
[Writes] [bigint] NULL,
[DataBaseName] [nvarchar](256) NULL,
[ApplicationName] [nvarchar](256) NULL,
[HostName] [nvarchar](256) NULL,
PRIMARY KEY CLUSTERED
(
[RowNumber] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
/****** Object: StoredProcedure [dbo].[CommandExecute] Script Date: 2017/2/15 11:16:02 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[CommandExecute]
@Command nvarchar(max),
@CommandType nvarchar(max),
@Mode int,
@Comment nvarchar(max) = NULL,
@DatabaseName nvarchar(max) = NULL,
@SchemaName nvarchar(max) = NULL,
@ObjectName nvarchar(max) = NULL,
@ObjectType nvarchar(max) = NULL,
@IndexName nvarchar(max) = NULL,
@IndexType int = NULL,
@StatisticsName nvarchar(max) = NULL,
@PartitionNumber int = NULL,
@ExtendedInfo xml = NULL,
@LogToTable bit,
@Exec bit
AS
BEGIN
SET NOCOUNT ON
DECLARE @StartMessage nvarchar(max)
DECLARE @EndMessage nvarchar(max)
DECLARE @ErrorMessage nvarchar(max)
DECLARE @ErrorMessageOriginal nvarchar(max)
DECLARE @StartTime datetime
DECLARE @EndTime datetime
DECLARE @StartTimeSec datetime
DECLARE @EndTimeSec datetime
DECLARE @ID int
DECLARE @Error int
DECLARE @ReturnCode int
SET @Error = 0
SET @ReturnCode = 0
----------------------------------------------------------------------------------------------------
--// Check core requirements //--
----------------------------------------------------------------------------------------------------
IF @LogToTable = 1 AND NOT EXISTS (SELECT * FROM sys.objects objects INNER JOIN sys.schemas schemas ON objects.[schema_id] = schemas.[schema_id] WHERE objects.[type] = ‘U‘ AND schemas.[name] = ‘dbo‘ AND objects.[name] = ‘CommandLog‘)
BEGIN
SET @ErrorMessage = ‘The table CommandLog is missing. Download https://ola.hallengren.com/scripts/CommandLog.sql.‘ + CHAR(13) + CHAR(10) + ‘ ‘
RAISERROR(@ErrorMessage,16,1) WITH NOWAIT
SET @Error = @@ERROR
END
IF @Error <> 0
BEGIN
SET @ReturnCode = @Error
GOTO ReturnCode
END
----------------------------------------------------------------------------------------------------
--// Check input parameters //--
----------------------------------------------------------------------------------------------------
IF @Command IS NULL OR @Command = ‘‘
BEGIN
SET @ErrorMessage = ‘The value for the parameter @Command is not supported.‘ + CHAR(13) + CHAR(10) + ‘ ‘
RAISERROR(@ErrorMessage,16,1) WITH NOWAIT
SET @Error = @@ERROR
END
IF @CommandType IS NULL OR @CommandType = ‘‘ OR LEN(@CommandType) > 60
BEGIN
SET @ErrorMessage = ‘The value for the parameter @CommandType is not supported.‘ + CHAR(13) + CHAR(10) + ‘ ‘
RAISERROR(@ErrorMessage,16,1) WITH NOWAIT
SET @Error = @@ERROR
END
IF @Mode NOT IN(1,2) OR @Mode IS NULL
BEGIN
SET @ErrorMessage = ‘The value for the parameter @Mode is not supported.‘ + CHAR(13) + CHAR(10) + ‘ ‘
RAISERROR(@ErrorMessage,16,1) WITH NOWAIT
SET @Error = @@ERROR
END
IF @Error <> 0
BEGIN
SET @ReturnCode = @Error
GOTO ReturnCode
END
----------------------------------------------------------------------------------------------------
--// Log initial information //--
----------------------------------------------------------------------------------------------------
SET @StartTime = GETDATE()
SET @StartTimeSec = CONVERT(datetime,CONVERT(nvarchar,@StartTime,120),120)
IF @LogToTable=0
BEGIN
SET @StartMessage = ‘Date and time: ‘ + CONVERT(nvarchar,@StartTimeSec,120) + CHAR(13) + CHAR(10)
SET @StartMessage = @StartMessage + ‘Command: ‘ + @Command
IF @Comment IS NOT NULL SET @StartMessage = @StartMessage + CHAR(13) + CHAR(10) + ‘Comment: ‘ + @Comment
SET @StartMessage = REPLACE(@StartMessage,‘%‘,‘%%‘)
RAISERROR(@StartMessage,10,1) WITH NOWAIT
END
IF @LogToTable = 1
BEGIN
INSERT INTO dbo.CommandLog (DatabaseName, SchemaName, ObjectName, ObjectType, IndexName, IndexType, StatisticsName, PartitionNumber, ExtendedInfo, CommandType, Command, StartTime)
VALUES (@DatabaseName, @SchemaName, @ObjectName, @ObjectType, @IndexName, @IndexType, @StatisticsName, @PartitionNumber, @ExtendedInfo, @CommandType, @Command, @StartTime)
END
SET @ID = SCOPE_IDENTITY()
----------------------------------------------------------------------------------------------------
--// Execute command //--
----------------------------------------------------------------------------------------------------
IF @Mode = 1 AND @Exec = 1
BEGIN
EXECUTE(@Command)
SET @Error = @@ERROR
SET @ReturnCode = @Error
END
IF @Mode = 2 AND @Exec =1
BEGIN
BEGIN TRY
EXECUTE(@Command)
END TRY
BEGIN CATCH
SET @Error = ERROR_NUMBER()
SET @ReturnCode = @Error
SET @ErrorMessageOriginal = ERROR_MESSAGE()
SET @ErrorMessage = ‘Msg ‘ + CAST(@Error AS nvarchar) + ‘, ‘ + ISNULL(@ErrorMessageOriginal,‘‘)
RAISERROR(