For background information, see https://msdn.microsoft.com/en-us/library/ms175575(v=sql.110).aspx
Step 1. make sure SQL Server Agent is set to Replace Tokens (Properties|Alert System), need to stop and re-start to take effect for the change.
Step 2. the tokenized codes cannot be parsed by SSMS, the values are replaced at runtime.
Step 3. I created a job with 2 steps: (1) back up the log when the log is 70% full, and (2) audit the log backup information in an auditing table.
---create the audit table----
CREATE TABLE MyJobLog (
ServerName SYSNAME
, Instance SYSNAME
, JobId UNIQUEIDENTIFIER
, ErrorNumber INT
, ErrorSeverity INT
, ErrorDate DATETIME DEFAULT GETDATE()
, ErrorMessage NVARCHAR(MAX)
)
GO
---Insert the following code with tokens for job step 2------
INSERT MyJobLog (
ServerName
, Instance
, JobId
, ErrorNumber
, ErrorSeverity
, ErrorMessage
,ErrorDate
)
VALUES (
'$(ESCAPE_SQUOTE(A-SVR))'
, '$(ESCAPE_SQUOTE(INST))'
, $(ESCAPE_SQUOTE(JOBID))
, $(ESCAPE_SQUOTE(A-ERR))
, $(ESCAPE_SQUOTE(A-SEV))
, '$(ESCAPE_SQUOTE(A-MSG))'
, '$(ESCAPE_SQUOTE(DATE))'
)
---Use the following code to increase the log and make sure the log backup occurs when the performance condition is met-------
DBCC SQLPERF('LOGSPACE')
WHILE 1=1
INSERT DBO.BIGTABLE VALUES('1')
---check the job history, it should have two steps succeed--------
Database Mail notification to an operator
I set up an alert to back up the log when the log is 70% full, and want to notify the operator.
The job did not run in the 1st time because I did not do a full backup first.
The DB Mail part did not work either.
Solution:
1. I forgot to set the SQL Server Agent property for email communication, so enable it
2. Need to stop and re-start the SQL Server Agent!
The job did not run in the 1st time because I did not do a full backup first.
The DB Mail part did not work either.
Solution:
1. I forgot to set the SQL Server Agent property for email communication, so enable it
2. Need to stop and re-start the SQL Server Agent!
Dynamic SQL execution - EXEC @SQL vs. EXEC (@SQL)
-- EXEC @SQL vs. EXEC (@SQL)
--Without the brackets, the assumption is that the content of
the variable is a stored procedure name
--the original codes===
--find all of the indexes in the current database whose
fragmentation is over 50%
SELECT OBJECT_NAME (object_id),*
FROM sys.dm_db_index_physical_stats(
DB_ID()
, DEFAULT--object ID
I want to view
, DEFAULT -- Index ID
, DEFAULT
, DEFAULT
) x
WHERE x.avg_fragmentation_in_percent > 50
--AND
x.index_id =1
--ORDER BY
x.avg_fragmentation_in_percent DESC
GO
SET NOCOUNT ON
GO
/*Some columns can be rebuild online, others cannot such as
varchar(max) etc.
Tables with these BLOB or CLOB columns must be rebuilt
offline.
Offline rebuld is more thorough, effcient, but not always
possible
Do the online build possible to keep the business on,
Warning: temp db intensive
if not possible, have to be offline
Offline rebuilt just affects the table, not other tables in
the database.
*/
--build a temp table
DECLARE @Tables TABLE (
ID INT IDENTITY(1,1)
,
SchemaName SYSNAME
,
TableName SYSNAME
,
CanBuildOnline BIT
)
-- All tables that can be rebuilt online:
INSERT @Tables (SchemaName, TableName,
CanBuildOnline)
SELECT SCHEMA_NAME(t.schema_id), t.name, 1
FROM sys.tables t
WHERE NOT EXISTS (
SELECT * FROM sys.columns c
WHERE c.object_id = t.object_id
AND ( -- SELECT *
FROM sys.types
c.system_type_id
IN (34, 35, 99, 241)
--34=iamge,
35=text, 99=ntext,241=xml
OR (c.system_type_id
IN (165, 167, 231) AND c.max_length
= -1)
--165=varbinary,
167=varchar, 231=sysname, -1=max
)
)
AND--if we want
to build tables with 50% and higher fragmentation
t.object_id IN (
SELECT x.object_id
FROM sys.dm_db_index_physical_stats(
DB_ID()
, DEFAULT
, DEFAULT
, DEFAULT
, DEFAULT
) x
WHERE x.avg_fragmentation_in_percent > 50)
-- All tables that must be rebuild offline:
INSERT @Tables (SchemaName, TableName,
CanBuildOnline)
SELECT SCHEMA_NAME(t.schema_id), t.name, 0
FROM sys.tables t
WHERE EXISTS (
SELECT * FROM sys.columns c
WHERE c.object_id = t.object_id
AND ( -- SELECT *
FROM sys.types
c.system_type_id
IN (34, 35, 99, 241)
OR (c.system_type_id
IN (165, 167, 231) AND c.max_length
= -1)
)
)
DECLARE @Id INT
, @Sql NVARCHAR(1000)
,
@Schema SYSNAME
, @Table
SYSNAME
,
@CanBuildOnline BIT
SELECT @Id = MIN(ID) FROM @Tables
WHILE @Id IS NOT NULL
BEGIN
SELECT
@Schema = SchemaName
, @Table
=
TableName
,
@CanBuildOnline = CanBuildOnline
FROM
@Tables
WHERE ID=@Id
SET @Sql = 'ALTER INDEX
ALL ON [' + @Schema + '].[' + @Table
+ ']
REBUILD WITH(ONLINE
= '
+ CASE
@CanBuildOnline WHEN 1 THEN 'ON' ELSE 'OFF' END
+ ' )'
PRINT @Sql
EXEC @Sql
DELETE
@Tables WHERE ID=@Id
SET @Id = NULL
SELECT @Id = MIN(ID) FROM
@Tables
END
--===the new codes====
--find all of the indexes in the current database whose
fragmentation is over 50%
SELECT OBJECT_NAME (object_id),*
FROM sys.dm_db_index_physical_stats(
DB_ID()
, DEFAULT--object ID
I want to view
, DEFAULT -- Index ID
, DEFAULT
, DEFAULT
) x
WHERE x.avg_fragmentation_in_percent > 50
--AND
x.index_id =1
--ORDER BY
x.avg_fragmentation_in_percent DESC
GO
SET NOCOUNT ON
GO
/*Some columns can be rebuild online, others cannot such as
varchar(max) etc.
Tables with these BLOB or CLOB columns must be rebuilt
offline.
Offline rebuld is more thorough, effcient, but not always
possible
Do the online build possible to keep the business on,
Warning: temp db intensive
if not possible, have to be offline
Offline rebuilt just affects the table, not other tables in
the database.
*/
--build a temp table
DECLARE @Tables TABLE (
ID INT IDENTITY(1,1)
,
SchemaName SYSNAME
,
TableName SYSNAME
,
CanBuildOnline BIT
)
-- All tables that can be rebuilt online:
INSERT @Tables (SchemaName, TableName,
CanBuildOnline)
SELECT SCHEMA_NAME(t.schema_id), t.name, 1
FROM sys.tables t
WHERE NOT EXISTS (
SELECT * FROM sys.columns c
WHERE c.object_id = t.object_id
AND ( -- SELECT *
FROM sys.types
c.system_type_id
IN (34, 35, 99, 241)
--34=iamge,
35=text, 99=ntext,241=xml
OR (c.system_type_id
IN (165, 167, 231) AND c.max_length
= -1)
--165=varbinary,
167=varchar, 231=sysname, -1=max
)
)
AND--if we want
to build tables with 50% and higher fragmentation
t.object_id IN (
SELECT x.object_id
FROM sys.dm_db_index_physical_stats(
DB_ID()
, DEFAULT
, DEFAULT
, DEFAULT
, DEFAULT
) x
WHERE x.avg_fragmentation_in_percent > 50)
-- All tables that must be rebuild offline:
INSERT @Tables (SchemaName, TableName,
CanBuildOnline)
SELECT SCHEMA_NAME(t.schema_id), t.name, 0
FROM sys.tables t
WHERE EXISTS (
SELECT * FROM sys.columns c
WHERE c.object_id = t.object_id
AND ( -- SELECT *
FROM sys.types
c.system_type_id
IN (34, 35, 99, 241)
OR (c.system_type_id
IN (165, 167, 231) AND c.max_length
= -1)
)
)
DECLARE @Id INT
, @Sql NVARCHAR(1000)
,
@Schema SYSNAME
, @Table
SYSNAME
,
@CanBuildOnline BIT
SELECT @Id = MIN(ID) FROM @Tables
WHILE @Id IS NOT NULL
BEGIN
SELECT
@Schema = SchemaName
, @Table
=
TableName
,
@CanBuildOnline = CanBuildOnline
FROM
@Tables
WHERE ID=@Id
SET @Sql = 'ALTER INDEX
ALL ON [' + @Schema + '].[' + @Table
+ ']
REBUILD WITH(ONLINE
= '
+ CASE
@CanBuildOnline WHEN 1 THEN 'ON' ELSE 'OFF' END
+ ' )'
PRINT @Sql
EXEC (@Sql)
/*
DELETE
@Tables WHERE ID=@Id
SET @Id = NULL
SELECT @Id = MIN(ID) FROM
@Tables
*/
END
Subscribe to:
Posts
(
Atom
)