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;

New features in SQL Server 2008

Direct from Microsoft: Microsoft Technet Page

The ones that will affect me soonest are:
1. Filestream data type to allow the delivey of documents directly to the .NET apps
2. The date data type. The old datetime was a dinosaur and needed to go. It will be replaced by datetime2.

Enjoy

All Database full backup

/*
This script is to loop through all non-temp databases and create a full backup in the
designated directory. It also verifies that the backup when complete. Each database
will be place in their own directory.

The master.dbo.xp_create_subdir only works with SQL Server 2005. It can be removed for 2000 if the directory already exists.
*/

USE [master]

GO

set @filePath = N'\\Server Backup Location\' + @dbName;

DECLARE @tDatabases TABLE

( databaseId int

--, DATABASE_NAME varchar(25)

);

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'); --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)

declare @filepath nvarchar(50)

set @dbname = db_name(@dbId);

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;

Interesting Blog Post

As a person who frequently has to review and clean up others TSQL I always spend a lot of time formatting to review. Additionally, I can have to format it to the clients TSQL standard. This is a script for colour formatting the output to a file that will be easier to read and distribute. It does require command execution from the SQL Server instance and this is something that I do not give to many of the servers I manage so I can't quickly test it hear.

I am putting hear so I can try it as I think it could solve several issues:

Simple Talk Website - Prettifier




----------------
Now playing: American Public Media - Marketplace Money for September 12, 2008
via FoxyTunes

Guarantee a temp table is dropped

/*
The pupose of this post is to demonstrate the correct method of creating a temp table.
The key is to make sure that the drop statements are wrapped with an if statement and
placed both prior to table creation and after its use.
This is another argument for using temp table variables and common table expressions
*/

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

CREATE TABLE #tModelItems(
ItemNbr varchar (15),
QuantityPer decimal(11,3),
GroupCode char(1),
GroupCodeDesc varchar(50),
DrawingInd char(10),
);

--Use table

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


----------------
Now playing: NPR - 0808243: Car Talk 0835 August 30, 2008
via FoxyTunes

Raising errors in the history

/*
I have been looking into raising customer errors in a large collection of file copy routines I am creating to provide better logging for a collection of PDFs we use for various websites. This is a start of what I am looking for and will be added to as I find new features.

The error will be posted in the SSIS history so it will be easier to find when doing the morning audit.

*/

Dim filename As String = Dts.Variables("PDF_filename").Value.ToString
Dim sourceFilePath As String = Dts.Variables("SourceFilePath").Value.ToString
Dim destFilePath As String = Dts.Variables("DestinationFilePath").Value.ToString
Dim destFilePathArchive As String = Dts.Variables("DestinationFilePathArchive").Value.ToString
Dim purgeSourceFile As String = CBool(Dts.Variables("PurgeSourceFile").Value.ToString)
Dim ArchiveFilePath As String = Dts.Variables("archivePathExtension").Value.ToString

Dim sourceFileName As String = sourceFilePath & "\" & filename
Dim destFileName As String = destFilePath & "\" & filename
Dim destFileNameArchive As String = destFilePathArchive & "\" & filename

Dim CopyDest As String = destFilePath & ArchiveFilePath & "\" & filename

Try
File.SetAttributes(destFileName, FileAttributes.Normal)
'Make sure that if the file exists at the destination it is not read only
If File.Exists(CopyDest) Then
File.SetAttributes(CopyDest, FileAttributes.Normal)
End If
File.Copy(destFileName, CopyDest, True)
File.SetLastWriteTime(CopyDest, Now())
File.Copy(sourceFileName, destFileName, True)
Catch ex As Exception
'An error occurred.
Dts.Events.FireError(0, "Error in file copy", _
ex.Message & ControlChars.CrLf & ex.StackTrace, _
String.Empty, 0)
Dts.TaskResult = Dts.Results.Failure
End Try

Joining 2 files in SSIS with an outer join

This post is to demonstrate the step necessary to perform an outer join on 2 record sets. the goal of this join was to find all expiring transactions from an authorization table. Outer join them to any settlements against the transaction. Then finally create an in memory record set that can be used to create an email to notify the users.


This leads me to complain about the stupid syntax for if then in the derived column expression editor as shown below:

[Expression the create a true false result] ? [Then data value] : [Else data value]

I have no idea where this came from and it took me forever to find this. This is the link to MSDN.

This can all be accomplish with a single transact SQL statement using a temp table variable and this makes it look really complicated.