Sql Auditing Changes Table Design

/*
This is the start of documenting the process by which I collect information about changes in the database. This is done through ddl triggers and I keep a copy of all of the changes. This step is creating an audit table in the AuditDB. I always give myself one database on every server to collect this kind of information. The later posts will have the stored procedure to populate this table
*/

USE [AuditDB]
GO
/****** Object: Table [dbo].[Common_SP_Events] Script Date: 06/02/2009 16:48:51 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Common_SP_Events](
[EventType] [nvarchar](25) NULL,
[EventTime] [datetime] NULL CONSTRAINT [DF_Common_SP_Events_EventTime] DEFAULT (getdate()),
[LoginName] [nvarchar](50) NULL,
[UserName] [nvarchar](25) NULL,
[ObjectName] [nvarchar](50) NULL,
[ObjectType] [nvarchar](25) NULL,
[Command] [nvarchar](max) NULL,
[FullFile] [xml] NULL
) ON [PRIMARY];

CREATE CLUSTERED INDEX [cdx_Common_SP_Events_EventTime] ON [dbo].[Common_SP_Events]
(
[EventTime] DESC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF
, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON
, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY];

SQL 2005 Database Mail

I put this here so I have a standard template for sending mail. This script assumes there is a public email account with the name [Instance Name]Admin to send the email. The coalesce statement first checks if there is an instance name. The function will return NULL is it is the default. The second value will then insert the machine name to generate the user name. This is included to allow the same email script to be used on multiple servers especially test and production with no changes to the script.

There are many more options i

DECLARE @profile varchar(100)
DECLARE @EmailTo varchar(128)
DECLARE @Subjectvarchar(128)
DECLARE @Body varchar(max)
DECLARE @BodyType varchar(25)

SELECT @profile = CAST(COALESCE(SERVERPROPERTY ( 'InstanceName' ), SERVERPROPERTY ( 'ServerName')) AS varchar(80)) + 'Admin'
SET @EmailTo = 'address@domain.com'
SET @Subject = 'test'
SET @Body = 'test'
SET @BodyType = 'HTML' --TEXT

EXEC msdb.dbo.sp_send_dbmail
@profile_name = @profile,
@recipients = @EmailTo ,
@subject = @Subject,
@body = @Body ,
@body_format = @BodyType


----------------
Now playing: NPR - 0905136: NPR: 05-16-2009 Wait Wait... Don't Tell Me!

SP2 Upgrade Issue

Had an issue while updating SQL Server 2005 RTM to SP2. The SISS packages would start and then go into a weird suspended mode.

The history error message was:
Step 1 of job 'Inventory Reconciliation Job' (0x2070EF085C1D99489E13145AC7D55282) cannot be run because the SSIS subsystem failed to load. The job has been suspended.

The answer was :

http://support.microsoft.com/?kbid=914171

The SQL that fixed it was:
use msdb
go

select * from msdb.dbo.syssubsystems;
/*
--useful bit commented out here
delete from msdb.dbo.syssubsystems
exec msdb.dbo.sp_verify_subsystems 1
*/
go

SQL Check DB

/*
This is the routine I use for running check DB. It simply loops through the master miles and runs check DB. There is an optional parameter to suppress info messages.
*/

USE [master]
GO

DECLARE @tDatabases TABLE
( databaseId int
);

INSERT INTO @tDatabases ( databaseId )
SELECT DISTINCT s_mf.database_id
FROM sys.databases s_mf
WHERE s_mf.state = 0 -- ONLINE
AND has_dbaccess(db_name(s_mf.database_id)) = 1 -- Only look at databases to which we have access
AND NOT [NAME] IN ('tempdb', 'master', 'model', 'msdb' )--eliminate temp db
;

DECLARE cursor_usage CURSOR FOR SELECT databaseId FROM @tDatabases;

DECLARE @dbId INT;

OPEN cursor_usage;
FETCH NEXT FROM cursor_usage INTO @dbId;

WHILE @@FETCH_STATUS = 0
BEGIN
PRINT db_name(@dbId);
DECLARE @dbname nvarchar(50)
SET @dbname = db_name(@dbId);
DBCC CHECKDB (@dbName);

FETCH NEXT FROM cursor_usage INTO @dbId;
END;

CLOSE cursor_usage;
DEALLOCATE cursor_usage;

SQL User Backup Scipt Update and Fixed

/*
Fixed Backup script
The root path was not set causing the directories to not be set correctly.

Again the master.dbo.xp_create_subdir is only available in 2005 and 2008

*/

USE [master]
GO

DECLARE @rootpath NVARCHAR(100);
set @rootPath = N'\[Your backup locationand a]\';

DECLARE @tDatabases TABLE
( databaseId int
);

INSERT INTO @tDatabases ( databaseId )
SELECT DISTINCT
s_mf.database_id
from sys.master_files s_mf
where s_mf.state = 0 -- ONLINE
AND has_dbaccess(db_name(s_mf.database_id)) = 1 -- Only look at databases to which we have access
AND NOT db_name(s_mf.database_id) in ('tempdb', 'master', 'msdb', 'model'); --eliminate temp db


DECLARE cursor_usage CURSOR FOR SELECT databaseId FROM @tDatabases;

DECLARE @dbId int;
OPEN cursor_usage;

FETCH NEXT FROM cursor_usage INTO @dbId;

WHILE @@FETCH_STATUS = 0
BEGIN
PRINT db_name(@dbId);
DECLARE @Filepath NVARCHAR(500);
declare @dbname nvarchar(50)
set @dbname = db_name(@dbId);

SET @FilePath = @RootPath + @dbName;
PRINT @FilePath

EXECUTE master.dbo.xp_create_subdir @filepath;

declare @fileName nvarchar(128)
declare @fullPath nvarchar(255)

--set the date format yyyymmddhhmm for the file name
declare @date nvarchar(10)
declare @hour nvarchar(4)
declare @minute nvarchar(4)

--ensure hour is 2 digit
set @hour = CAST(datepart(hh,getdate()) AS nvarchar(2))
if len(@hour) = 1
set @hour = '0' + CAST(datepart(hh,getdate()) AS nvarchar(2))

--ensure hour is 2 digit
set @minute = CAST(DATEPART(mi, GetDate())AS nvarchar(2))

if len(@minute) = 1
set @minute = '0' + CAST(DATEPART(mi, GetDate())AS nvarchar(2))

--set the filename
select @filename = @dbName + N'_backup_' + CONVERT( nvarchar(30), GetDate(), 112 ) + @hour + @minute + N'.bak';
select @fullPath = @filePath + '\' + @fileName
print @fileName;

BACKUP DATABASE @dbname TO DISK = @fullpath WITH NOFORMAT, NOINIT, NAME = @fileName, SKIP, REWIND, NOUNLOAD, STATS = 5;


--run the verify
declare @backupSetId as int
select @backupSetId = position from msdb.dbo.backupset where database_name=@dbname and backup_set_id=(select max(backup_set_id) from msdb.dbo.backupset where database_name=@dbname )

if @backupSetId is null
begin
declare @errMsg nvarchar(128)
select @errMsg = N'Verify failed. Backup information for database ' + @dbname + N' not found.'
raiserror(@errMsg, 16, 1);
end

RESTORE VERIFYONLY FROM DISK = @fullpath WITH FILE = @backupSetId, NOUNLOAD, NOREWIND

FETCH NEXT FROM cursor_usage INTO @dbId;
END;

CLOSE cursor_usage;
DEALLOCATE cursor_usage;

SSIS Transfer Database task

Just a few notes about the use of the transfer database control task in SSIS 2005.

We had several problems with the logins being corrupted when the database was placed in the new instances. It appears that the task attempts to match the users to the instances users as one of its' steps. If it cannot find the user names in the new instance it corrupts the dbo users and schema.

The simple solution is to make sure that all users exist in the new instance. The transfer database task will then match the DBID of the instance users. This saves you from having to run sp_change_users_login to match the DBID from the new database to the instance

Thanks you Sam for this one.

Improved SQL Index Update Script

I have modified this script to be compatible with the Share Point server database I am now supporting. This script replaces the previous one completely. This is used in conjunction with a script that feeds the database information for every database to it. I normally place it in master so I do not have to add it to every database.

USE [master]
GO/****** Object: StoredProcedure [dbo].[DbUtil_ALTER_INDEX] Script Date: 06/05/2008 16:18:35 ******/
SET ANSI_NULLS ON
GOSET QUOTED_IDENTIFIER ON
GO-------------------------------------------------------------------------------------------
-- OBJECT NAME : DbUtil_ALTER_INDEX

-- AUTHOR: Alan T
-- BASED ON A SCRIPT: Tara Kizer

---- DATE : February 27, 2007
--:2008 - 06- 06 Updated to run online and offline
--:2008-11-12 Added Share Point support
--:2009-01-13 Added a trimmer to make sure the database name is good

-- INPUTS : @dbName - name of the database
-- @statsMode - mode to use for sys.dm_db_index_physical_stats
-- @defragType - REORGANIZE (INDEXDEFRAG) or REBUILD (DBREINDEX)
-- @minFragPercent - minimum fragmentation level
-- @maxFragPercent - maximum fragmentation level
-- @minRowCount - minimum row count
-- @PerformOnlineOnly - Forces to rebuild to be online for enterprise ed. without 1 = true
--
-- OUTPUTS : None
--
-- DEPENDENCIES : sys.dm_db_index_physical_stats, sys.objects,
-- sys.schemas, sys.indexes, sys.partitions
--
-- DESCRIPTION : Defragments indexes
/*
EXEC DbUtil_ALTER_INDEX
@dbName = 'DatabaseName',
@statsMode = 'SAMPLED',
@defragType = 'REBUILD',
@minFragPercent = 10,
@maxFragPercent = 100,
@minRowCount = 1000
, @PerformOnlineOnly = 1
*/
-------------------------------------------------------------------------------------------
CREATE PROC [dbo].[DbUtil_ALTER_INDEX]
(
@dbName sysname
, @statsMode varchar(8) = 'SAMPLED'
, @defragType varchar(10) = 'REORGANIZE'
, @minFragPercent int = 25
, @maxFragPercent int = 100
, @minRowCount int = 0
, @PerformOnlineOnly bit = 1
)
AS
SET NOCOUNT ON
DECLARE @i int
DECLARE @objectId int
DECLARE @objectName sysname
DECLARE @indexId int
DECLARE @indexName sysname
DECLARE @schemaName sysname
DECLARE @partitionNumber int
DECLARE @partitionCount int
DECLARE @recordCount bigint
DECLARE @sql nvarchar(4000)
DECLARE @edition int
DECLARE @parmDef nvarchar(500)
SELECT @i = 1, @edition = CONVERT(int, SERVERPROPERTY('EngineEdition'))
--Make sure the temp table does not exist
IF EXISTS(SELECT name FROM tempdb.dbo.sysobjects WHERE type = N'U' AND name like N'#FragIndex%') DROP TABLE #FragIndex;
SELECT
IDENTITY(int, 1, 1) AS FragIndexId,
[object_id] AS ObjectId,
index_id AS IndexId,
avg_fragmentation_in_percent AS FragPercent,
record_count AS RecordCount,
partition_number AS PartitionNumberINTO #FragIndexFROM sys.dm_db_index_physical_stats (DB_ID(@dbName), NULL, NULL, NULL, @statsMode)WHERE avg_fragmentation_in_percent > @minFragPercentAND avg_fragmentation_in_percent < @maxFragPercent AND index_id > 0AND record_count > @minRowCountORDER BY ObjectId
SELECT
@objectId = ObjectId,
@indexId = IndexId,
@recordCount = RecordCountFROM #FragIndexWHERE FragIndexId = @i
--Make sure that the DB name is trimmed otherwise the procedure will failSELECT @dbName = LTRIM(RTRIM(@dbName))

WHILE @@ROWCOUNT <> 0
BEGIN
SET @sql = '
SELECT @objectName = o.[name], @schemaName = s.[name]
FROM [' + @dbName + '].sys.objects o
JOIN [' + @dbName + '].sys.schemas s
ON s.schema_id = o.schema_id
WHERE o.[object_id] = @objectId'
SET @parmDef = N'@objectId int, @objectName sysname OUTPUT, @schemaName sysname OUTPUT'

EXEC sp_executesql
@sql, @parmDef, @objectId = @objectId,
@objectName = @objectName OUTPUT, @schemaName = @schemaName OUTPUT

SET @sql = 'SELECT @indexName = [name]
FROM [' + @dbName + '].sys.indexes
WHERE [object_id] = @objectId AND index_id = @indexId AND is_disabled = 0'

SET @parmDef = N'@objectId int, @indexId int, @indexName sysname OUTPUT'
EXEC sp_executesql
@sql, @parmDef, @objectId = @objectId, @indexId = @indexId,
@indexName = @indexName OUTPUT

SET @sql = 'SELECT @partitionCount = COUNT(*)
FROM [' + @dbName + '].sys.partitions WHERE [object_id] = @objectId AND index_id = @indexId'
SET @parmDef = N'@objectId int, @indexId int, @partitionCount int OUTPUT'
EXEC sp_executesql
@sql, @parmDef, @objectId = @objectId, @indexId = @indexId,
@partitionCount = @partitionCount OUTPUT
SET @sql = 'ALTER INDEX [' + @indexName + '] ON [' + @dbName + '].[' +
@schemaName + '].[' + @objectName + '] ' + @defragType
-- Developer and Enterprise have the ONLINE = ON option for REBUILD
IF @defragType = 'REBUILD' AND @edition = 3 AND @PerformOnlineOnly = 1 SET @sql = @sql + ' WITH (ONLINE = ON)'
ELSE IF @defragType = 'REBUILD' AND @edition = 3 AND @PerformOnlineOnly = 0 SET @sql = @sql + ' WITH (ONLINE = OFF)'
IF @partitionCount > 1 SET @sql = @sql + ' PARTITION = ' + CAST(@partitionNumber AS varchar(10))
print @sql
BEGIN TRY
EXEC (@SQL)
END TRY
BEGIN CATCH
print 'ERROR1 : ' + cast(ERROR_LINE() as nvarchar(10)); print 'ERROR2 : ' + ERROR_MESSAGE();

RETURN -1;END CATCH;


SET @i = @i + 1
SELECT
@objectId = ObjectId,
@indexId = IndexId,
@recordCount = RecordCountFROM #FragIndexWHERE FragIndexId = @iEND

IF EXISTS(SELECT name FROM tempdb.dbo.sysobjects WHERE type = N'U' AND name like N'#FragIndex%') DROP TABLE #FragIndex;

return 0;