Generating log file shrinks

We had a situation where we needed to shrink all log files for all databases on a couple of drives preparatory to moving all the files to a new LUN which would then be mounted as the same drive letter. This was being done to correct improperly aligned partitions on the drives causing performance issues.

When disk partitions are not properly aligned there is a significant performance degradation (30-40%) on the disk, see Chad Miller's blog post on this for more detail. Additionally, correcting this will reduce throughput to the SAN array which can benefit overall performance of the array.

I don't normally recommend (and would typically strongly oppose) shrinking files, but in this case we had a limited time frame to make this move & shrinking these files meant 150GBs less data to move which was a make or break for the timeline we were allowed to complete the work in.

Since these were all log files I choose to shrink them all to 1MB for maximum benefit. For the sake of paranoia (I like to double/triple/quadruple check scripts prior to running in Production) I had it generate the statements to run & then ran them manually. To customize for your drives you would just change the T-SQL defined in the @sql variable. The script is here.

Searching for a peice of text across a DB

Awhile back I had the need to search for a specific peice of text across a database. After playing with the code I came up with the below stored proc. I placed the SP in our "Scripting/Administation" DB. It takes a param of database & the value to find, you can use wildcards in the value to find. The SP returns the table, column & number of occurences of the searched value.

USE DBAScriptDB
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

CREATE PROC dbo.usp_SearchTextAcrossDB
    @DBToSearch VARCHAR(512),
    @ValToFind VARCHAR(512)
AS
/*    --------------------------------------------------------
        Author  :    
        Date    : 
        Purpose : Search across all Tables/Columns in any DB for a text value. 
                            Wildcards may be used in @ValToFind.
        DB(s)        : DBAScriptDB, all DBs
        Modification History
        Date        Programmer         Description
        --------    -------------     ----------------   
   
*/    ---------------------------------------------------------
    SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
    
    SET NOCOUNT ON
    
    DECLARE @errTxt VARCHAR(256),
                    @objName VARCHAR(192),
                    @own VARCHAR(384), 
                    @tbl VARCHAR(384), 
                    @col VARCHAR(384),
                    @sqlSrch VARCHAR(8000),
                    @sqlFld VARCHAR(8000)
    
    CREATE TABLE #Results
        (
            Table_Name VARCHAR(384),
            Column_Name VARCHAR(384),
            Occurrence_Count INT      
        )

    CREATE TABLE #TblFlds
        (
            TblOwner VARCHAR(384),
            TblName VARCHAR(384),
            ColName VARCHAR(384)
        )

    SELECT    @objName = 'dbo.usp_SearchTextAcrossDB'

    SELECT @sqlFld = 
        'INSERT INTO #TblFlds (TblOwner, TblName, ColName)' + CHAR(10) +
        '    SELECT u.name, o.name, ''['' + c.name + '']''' + CHAR(10) +
        '    FROM ' + @DBToSearch + '.dbo.syscolumns c (NOLOCK)' + CHAR(10) +
        '        INNER JOIN ' + @DBToSearch + '.dbo.sysobjects o (NOLOCK) ON c.id = o.id' + CHAR(10) +
        '        INNER JOIN ' + @DBToSearch + '.dbo.sysusers u (NOLOCK) ON o.uid = u.uid' + CHAR(10) +
        '        INNER JOIN ' + @DBToSearch + '.dbo.systypes t (NOLOCK) ON c.xusertype = t.xusertype' + CHAR(10) +
        '    WHERE o.type = ''U''' + CHAR(10) +
        '                AND (t.name = ''varchar'' OR t.name = ''char'' OR t.name = ''nvarchar'' OR t.name = ''nchar'' OR t.name = ''text'' OR t.name = ''ntext'' OR t.name = ''sysname'')' + CHAR(10) +
        '    ORDER BY 1, 2'

    EXEC (@sqlFld)
    
    WHILE EXISTS (SELECT TblOwner FROM #TblFlds)
        BEGIN
            SELECT @own = (SELECT TOP 1 TblOwner FROM #TblFlds)
            
            SELECT @tbl = (SELECT TOP 1 TblName FROM #TblFlds WHERE TblOwner = @own)
            
            SELECT @col = (SELECT TOP 1 ColName FROM #TblFlds WHERE TblOwner = @own AND TblName = @tbl)
            
            DELETE #TblFlds 
            WHERE TblOwner = @own
                AND TblName = @tbl 
                AND ColName = @col
        
            IF @col IS NOT NULL
                BEGIN
                    SET @sqlSrch = 'SELECT ''' + @own + '.' + @tbl + ''', ''' + @col + ''', COUNT(*) FROM ' + @DBToSearch + '.' + @own + '.' + @tbl + ' (NOLOCK) WHERE ' + @col + ' LIKE ''' + @ValToFind + ''''
                    
                    INSERT INTO #Results (Table_Name, Column_Name, Occurrence_Count)
                        EXEC (@sqlSrch)
                END
            
            IF (SELECT COUNT(TblOwner) FROM #TblFlds) <= 0
                BREAK
        END

    IF @@ERROR <> 0
        BEGIN
            SET @errTxt = 'Exception: ' + @objName + ' occured in SELECT Statement'
            RAISERROR(@errTxt, 1, 2)
        END

    SELECT Table_Name, Column_Name, Occurrence_Count 
    FROM #Results 
    WHERE Occurrence_Count > 0 
    ORDER BY 1

    DROP TABLE #TblFlds, #Results
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO