GetIP

Get IP address from T-SQL. 

Requires xp_cmdshell to be enabled.  If this is a problem for your environment, consider enabling it, run the proc, then disable it.

NOTE:  if there is more than one IP address, the output will indicate that:

                10.1.10.92 (IPs found: 2)

In this event, you will need to add some code to differentiate which one you need.

Use Admin

go

IF Object_id('dbo.GetIP') Is Not Null

      DROP PROC dbo.GetIP

go

CREATE PROC [dbo].[GetIP]

      @IP varchar(30) OUTPUT,

      @Debug bit = 0

AS

/*    DATE        AUTHOR            REMARKS

      2/15/13           PPaiva            Initial creation.

     

      USAGE

            -- Get IP

            DECLARE @ip varchar(30)

            Exec GetIP @ip OUTPUT

            SELECT @ip IP

            -- Get IP with debug

            DECLARE @ip varchar(30)

            Exec GetIP @ip OUTPUT, 1

            SELECT @ip IP

     

      DEBUG

            --Exec sp_configure 'xp_cmdshell', 1

            --reconfigure

            SELECT *

            FROM ServerInfo

     

*/

SET NOCOUNT ON

DECLARE @s varchar(100),

            @NumIPs int

SET @s = 'ipconfig'

CREATE TABLE #ipconfig(

      ID int NOT NULL IDENTITY(1, 1) PRIMARY KEY,

      Col1 varchar(1000)

      )

INSERT INTO #ipconfig

Exec xp_cmdshell @s

DECLARE @PosColon int

IF @Debug = 1

      SELECT *

      FROM #ipconfig

     

DELETE

FROM #ipconfig

WHERE Col1 Not Like '%ipv4%'

   OR Col1 Is Null

SELECT @NumIPs = Count(*)

FROM #ipconfig

SELECT TOP 1 @PosColon = CharIndex(':', Reverse(Col1))

FROM #ipconfig

ORDER BY ID desc

SELECT TOP 1 @ip = LTrim(Right(Col1, @PosColon - 1))

FROM #ipconfig

ORDER BY ID desc

IF @NumIPs > 1

      SET @ip = @ip + ' (IPs found: ' + Convert(varchar, @NumIPs) + ')'

IF @Debug = 1

      SELECT @PosColon 'PosColon (reverse)'

     

      

FixServerName

Many like to use the global variable @@ServerName when you need to provide the instance name of your SQL Server.  However, if the machine has been renamed after SQL Server was installed, this variable will be incorrect – it will have the previous instance name of SQL Server.

ServerProperty('ServerName')) will always return the correct instance name.  But it is a bit clumsy to use because its datatype is sql_variant, so you have to first convert it to char or varchar.

This proc will identify and fix the problem.  A service stop/start is required after running the fix.

Use Admin

go

IF Object_ID('dbo.FixServerName') Is Not Null

      DROP PROC dbo.FixServerName

go

CREATE PROC dbo.FixServerName

      @Debug bit = 1

AS

/*    DATE        AUTHOR            REMARKS

      2/10/13           PPaiva            Initial creation.

     

      DESCRIPTION

            Determines if @@ServerName is the same as ServerProperty('ServerName').

                  If not, then the server has likely been renamed after SQL Server

                  was installed. 

                 

            To fix, run this with @Debug = 0, then stop/start the SQL Server service.

     

      USAGE

            FixServerName           -- Examine only, don't fix

            FixServerName 0         -- Fix

     

*/

DECLARE @ServerNameGlobalVar varchar(50),

            @ServerNameProperty varchar(50),

            @Msg varchar(1000)

SET @ServerNameGlobalVar = @@ServerName + 'h'

SET @ServerNameProperty = Convert(varchar(50), ServerProperty('ServerName'))

IF @ServerNameGlobalVar = @ServerNameProperty

      BEGIN

            SET @Msg = '@@ServerName has the correct value.  You can use @@ServerName whenever you need '

                        + Char(13) + 'to provide the SQL instance name.  Nothing to fix.'

                        + Char(13) + '                    @@ServerName = ' + @ServerNameGlobalVar

                        + Char(13) + '    ServerProperty(''ServerName'') = ' + @ServerNameProperty

            Print @Msg

      END

     

ELSE

      BEGIN

            IF @Debug = 1

                  BEGIN

                        SET @Msg = '@@ServerName does NOT have the correct value. Needs to be fixed.'

                                    + Char(13) + '                    @@ServerName = ' + @ServerNameGlobalVar

                                    + Char(13) + '    ServerProperty(''ServerName'') = ' + @ServerNameProperty

                                    + Char(13) + 'To fix, run the following, then stop/start the SQL Server service.'

                                    + Char(13) + '    Exec FixServerName 0'

                        Print @Msg

                  END                          

                 

            ELSE             

                  BEGIN

                        SET @Msg = 'Exec master.dbo.sp_dropserver ''' + @@ServerName + ''''

                        Print @Msg

                        Exec(@Msg)

                        SET @Msg = 'Exec master.dbo.sp_addserver ''' + @ServerNameProperty + ''', ''local'''

                        Print @Msg

                        Exec(@Msg)

                       

                        SET @Msg = 'Need to stop/start the SQL Server service, or reboot, for change to take effect.'

                        Print ''

                        Print @Msg

                  END

                 

      END

      

SearchInCode

Need to search for a string contained in a proc, view, function, or trigger?  You can use sys.syscomments and JOIN to sysobjects, but it is nice to do all the JOINing beforehand and simply use a nice object dedicated to searching.

USE Admin

GO

IF Object_ID('dbo.SearchInCode') Is Not Null

      DROP PROC dbo.SearchInCode

go

CREATE PROC dbo.SearchInCode

                        @DB varchar(50),

                        @Sort bit = 1,

                        @SearchText1 varchar(200),

                        @SearchText2 varchar(200) = Null,

                        @SearchText3 varchar(200) = Null,

                        @ShowSql bit = 0

AS

/*    DATE        AUTHOR            REMARKS

      1/8/13           PPaiva            Initial creation.

     

     

      DESCRIPTION

            For a given database, lists any procs, functions,

                  triggers or views that contain @SearchText1,

                  AND optionally @SearchText2,

                  AND optionally @SearchText3. 

      USAGE

            Exec Admin..SearchInCode 'Admin', 1, 'File'

            Exec Admin..SearchInCode 'Sopa', 1, 'EmployeeID'

            Exec Admin..SearchInCode 'Sopa', 1, 'EmployeeID', 'log'

            Exec Admin..SearchInCode 'Sopa', 1, 'EmployeeID', 'log', 'Activity', 1

*/

SET NOCOUNT ON

IF Object_ID('tempdb.dbo.##SearchInCode') Is Not Null

      DROP TABLE ##SearchInCode

CREATE TABLE ##SearchInCode(

      ID int NOT NULL IDENTITY(1, 1) PRIMARY KEY,

      Server varchar(100),

      DB varchar(200),

      ObjType varchar(50),

      SchemaName sysname,

      ObjName sysname,

      Text varchar(max),

      FullName varchar(300),

      SearchForTextRunDate datetime NOT NULL DEFAULT GetDate()

      )

DECLARE @Sql varchar(2000)

SET @Sql =

'SELECT ''' + @@ServerName + ''' Server,

            ''' + @DB + ''' DB,

            ot.Description ObjType,

            s.Name SchemaName,

            o.Name ObjName,

            c.Text,

            ''[' + @DB + '].['' +  s.name + ''].['' + o.name + '']'' FullName

FROM [' + @DB + '].dbo.syscomments c

JOIN [' + @DB + '].sys.objects o

      ON c.ID = o.object_id

LEFT JOIN Admin.dbo.infraObjectType ot

      ON ot.Type = o.Type COLLATE SQL_Latin1_General_CP1_CI_AS

JOIN [' + @DB + '].sys.schemas s

      ON s.schema_id = o.schema_id

WHERE 1 = 1'

IF @SearchText2 Is Null AND @SearchText3 Is Null

      SET @Sql = @Sql + '

  AND c.Text Like ''%' + @SearchText1 + '%'''

ELSE IF @SearchText3 Is Null

      SET @Sql = @Sql + '

  AND c.Text Like ''%' + @SearchText1 + '%''

  AND c.Text Like ''%' + @SearchText2 + '%'''

ELSE

      SET @Sql = @Sql + '

  AND c.Text Like ''%' + @SearchText1 + '%''

  AND c.Text Like ''%' + @SearchText2 + '%''

  AND c.Text Like ''%' + @SearchText3 + '%'''

IF @Sort = 1

      SET @Sql = @Sql + '

ORDER BY ot.Description, Substring(o.name, 1, 1)'

IF @ShowSql = 1

      Print @Sql

INSERT INTO ##SearchInCode (Server, DB, SchemaName, ObjType, ObjName, Text, FullName)

      Exec(@Sql)

Print '

SELECT *

FROM ##SearchInCode

ORDER BY 1

'

SELECT *

FROM ##SearchInCode

ORDER BY 1

vwLogInfoSummary - show Virtual Log Files per database

In the last post on Virtual Log Files, a procedure was given, PopLogInfo, to populate table LogInfo. 

Here, a view is presented to make best use of that table, to see how many VLFs a given database has.

IF object_id('dbo.vwLogInfoSummary') Is Not Null

      DROP VIEW dbo.vwLogInfoSummary

GO

CREATE VIEW vwLogInfoSummary

AS

/*    DATE        AUTHOR            REMARKS    

      12/15/12    PPaiva            Initial creation.

     

      SELECT *

      FROM LogInfo

     

      SELECT *

      FROM vwLogInfoSummary

      ORDER BY NumVLFs desc

*/

SELECT      Server,

                  DB,

                  FileID,

                  Status,

                  StatusDesc,

                  COUNT(*) NumVLFs,

                  InsertDate

      FROM LogInfo

      WHERE StatusDesc = 'Reusable'

      GROUP BY Server, DB, FileID, Status, StatusDesc, InsertDate

Virtual Log Files – a sometimes forgotten performance bottleneck

If the number of virtual log files (VLFs) for a given database gets too high, say more than 50 or so, then there will be a performance hit due to fragmentation.  Whenever the "auto-grow" for a log file kicks in, more VLFs are created.  The best prevention is to ensure that the auto-grow value is not set too low.  The default value is 10% in SQL 2005, 2008, 2008R2, and 2012.  Depending on how often you add data to your database, this value may be appropriate or way too low.  Also be careful of the initial size.  The default value is 1 Mbyte, which could be disastrous if it is not increased at database creation.

•          What is a log file?

–        guarantees data integrity of the database and for data recovery

–        it is what makes ACID, in part, possible

•          atomicity

•          concurrency

•          isolation level

•          durability

•          A database has at least one transaction log file

•          Each transaction log file is comprised of VLFs

–        no fixed size

–        no fixed number of files

•          Make sure initial size and auto-grow is set correctly

–        Try to minimize the number of times a files grows

•          Some growth is of course ok

•          To determine number of VLFs, run

DBCC LogInfo(DatabaseName)

•          50 or less is ok

•          over 50:  use SHRINKFILE to reduce the number of virtual log files.

BACKUP LOG databasename TO devicename

DBCC SHRINKFILE(TransactionLogLogicalFilename, TRUNCATEONLY)

ALTER DATABASE databasename

MODIFY FILE

(

      NAME = TransactionLogLogicalFilename

    , SIZE = NewTotalSize

)

Now, for the real power of using code to help with database administration.  Create a procedure that will store the VLF info for all databases.  You can schedule this to run daily, and even set up alerts to be notified if the number of VLFs exceeds a threshold value, say 50 or 100.

See next post for a handy view which will show how many VLFs per database.

USE Admin

GO

CREATE PROC dbo.PopLogInfo

      @ShowSql bit= 0

AS

/*    DATE        AUTHOR            REMARKS

      10/16/12    PPaiva            Initial creation.

     

      DESCRIPTION

            Truncates and populates LogInfo using DBCC LogInfo.

           

      DEBUG

            SELECT *

            FROM LogInfo

            -- Number of VLFs per database

            SELECT Server, DB, COUNT(*) Qty

            FROM LogInfo

            GROUP BY Server, DB

            ORDER BY 3 desc, 1, 2

            SELECT status, COUNT(*)

            FROM LogInfo

            GROUP BY status;

            SELECT TOP 5 *

            FROM vwServerDBFile

            DBCC LogInfo ('AmRept')

     

      USAGE

            PopLogInfo 1

            PopLogInfo

*/   

SET NOCOUNT ON

-- DROP TABLE dbo.LogInfo

IF OBJECT_ID('dbo.LogInfo') Is Null

      CREATE TABLE LogInfo(

            ID int NOT NULL IDENTITY(1, 1) CONSTRAINT pk_LogInfo PRIMARY KEY CLUSTERED,

            Server varchar(30) NULL,

            DB sysname NULL,

            FileID int NOT NULL ,

            MBytes As Convert(decimal(8, 2), (SizeBytes/1000000.)),

            StartOffsetBytes bigint NOT NULL,

            FSeqNo bigint NOT NULL,

            Status int NOT NULL,    -- can be 0 or 2.  0 = reusable, 2 = not reusable

            StatusDesc  AS (case when Status=(0) then 'Reusable' when Status=(2) then 'Not Reusable' else '(undefined)' end),

            Parity int NOT NULL,    -- can be 64 or 128.  Value switches upon reuse.

            CreateLSN Numeric(25, 0) NOT NULL,  -- 0 indicates an original file, when database was created.

            SizeBytes bigint NOT NULL,

            dbID int NULL,

            InsertDate datetime CONSTRAINT def_LogInfoInsertDate DEFAULT GetDate()

            )

SELECT name, database_id

INTO #DBs

FROM sys.databases

DECLARE @MaxDB sysname,

            @DB sysname,

            @dbID int,

            @s varchar(500)

           

SELECT  @MaxDB = MAX(Name),

            @DB = ''

FROM #DBs        

           

IF @ShowSql = 0

      TRUNCATE TABLE LogInfo       

     

     

WHILE @DB < @MaxDB

      BEGIN

            -- Get next DB

            SELECT @DB = Min(Name)

            FROM #DBs

            WHERE Name > @DB       

            -- Get corresponding dbID

            SELECT @dbID = database_id

            FROM #DBS

            WHERE Name = @DB

            SET @s = 'INSERT INTO LogInfo (FileID, SizeBytes, StartOffsetBytes, FSeqNo, Status, Parity, CreateLSN)

      Exec (''DBCC LogInfo(''''' + @DB + ''''')'')

UPDATE LogInfo

SET DB = ''' + @DB + ''',

      dbID = ' + Convert(varchar, @dbID) + '

WHERE DB Is Null

'

            IF @ShowSql = 1

                  Print @s

            ELSE

                  Exec(@s)         

     

     

      END

UPDATE LogInfo

SET Server = Convert(varchar(30), SERVERPROPERTY('ServerName'))

WHERE Server Is Null