Loading ...
Sorry, an error occurred while loading the content.

Re: DB statistics per server - script

Expand Messages
  • msflinxusa
    This goes against my Database Statistics DB. If you would like the Create DB script, please IM me at msflinx@hotmail.com and I will send you the files. Yes --
    Message 1 of 5 , Apr 5, 2004
    • 0 Attachment
      This goes against my Database Statistics DB. If you would like the
      Create DB script, please IM me at msflinx@... and I will
      send you the files.

      Yes -- I do. Here is the script:

      **********************************************************88

      CREATE PROCEDURE usp_Server_And_Database_DataGathering AS

      DECLARE @CurrentDate as datetime
      DECLARE @ServerName as varchar(50)
      DECLARE @SQLServerID as int
      DECLARE @dbName as varchar(50)
      DECLARE @DBID as int
      DECLARE @MDFSize as bigint
      DECLARE @LDFSize as bigint
      DECLARE @MDFFilePath as varchar(200)
      DECLARE @LDFFilePath as varchar(200)
      DECLARE @UsedSpace as bigint
      DECLARE @RowCount as int
      DECLARE @SQL as varchar(500)
      DECLARE @ConcantenatedTableStructure as varchar(100)
      DECLARE @String as varchar(50)
      DECLARE @Debug as bit

      SET NOCOUNT ON

      --Initiate Varibles
      SET @Debug = 0 --Turn Debug Off
      SET @UsedSpace = 0

      /*
      --Debugging Variables
      SET @Debug = 1 --Turn Debug On
      */

      SET @CurrentDate = getdate()

      IF @Debug = 1
      Print 'Date Set'

      CREATE TABLE TempHolding
      (
      hldNumber bigint,
      hldFilePath varchar(200)
      )

      IF @Debug = 1
      Print 'TempHolding Created'

      --Create a Cursor of All Active SQL Servers of Interest
      DECLARE sqlServers_Cursor CURSOR
      FOR SELECT SQLServerID, ServerName
      FROM SQLServerNames
      WHERE ActiveServer = 1

      IF @Debug = 1
      Print 'sqlServers_Cursor Created'

      OPEN sqlServers_Cursor

      IF @Debug = 1
      Print 'sqlServers_Cursor Opened'

      --Get First SQL Server
      FETCH NEXT
      FROM sqlServers_Cursor
      INTO @SQLServerID, @ServerName

      IF @Debug = 1
      Print 'sqlServers_Cursor Populated'

      --Continue this loop until no more SQL Server entries available
      WHILE @@FETCH_STATUS = 0
      BEGIN
      SET @ConcantenatedTableStructure = @ServerName
      + '.Master.dbo.SysDatabases'

      IF @Debug = 1
      Print @ConcantenatedTableStructure

      SET ANSI_NULLS OFF
      SET ANSI_WARNINGS OFF

      --Create SQL statement to get list of
      --New Database Names for Current SQL Server
      SET @SQL = 'INSERT INTO DBNames(SQLServerID, DatabaseName)
      SELECT cast(' + cast(@SQLServerID as varchar(20))
      + ' as int), Name
      FROM ' + @ConcantenatedTableStructure + '
      WHERE sid <> 0x01
      AND Name not in (SELECT DatabaseName
      FROM DBNames
      WHERE SQLServerID = cast(' + cast
      (@SQLServerID as varchar(20)) + ' as int))'


      SET ANSI_NULLS ON
      SET ANSI_WARNINGS ON

      IF @Debug = 1
      Print @SQL

      --Insert New Database Names into DBNames Table for Current SQL
      Server
      EXEC(@SQL)

      IF @Debug = 1
      Print 'Created DBNames Table'

      SET ANSI_NULLS OFF
      SET ANSI_WARNINGS OFF

      --Create SQL statement to update DBNames Table
      --Database Names as no longer active
      SET @SQL = 'UPDATE DBNames
      SET ActiveDatabase = 0
      WHERE DatabaseName not in (SELECT Name
      FROM ' +
      @ConcantenatedTableStructure + '
      WHERE sid <> 0x01)
      AND SQLServerID = cast(' + cast(@SQLServerID as
      varchar(20)) + ' as int)'

      SET ANSI_NULLS ON
      SET ANSI_WARNINGS ON

      IF @Debug = 1
      Print @SQL

      --Run Update Statement
      EXEC(@SQL)

      IF @Debug = 1
      Print 'Updated ActiveDatabases in DBNames Table'

      --Create Cursor of Active Database Names for Current Server
      DECLARE dbNames_Cursor CURSOR
      FOR SELECT DBID, DatabaseName
      FROM dbNames
      WHERE SQLServerID = @SQLServerID
      AND ActiveDatabase = 1

      IF @Debug = 1
      Print 'Created dbNames_Cursor'

      OPEN dbNames_Cursor

      IF @Debug = 1
      Print 'Open dbNames_Cursor'

      --Get First Entry in Active Database Names Cursor
      FETCH NEXT
      FROM dbNames_Cursor
      INTO @DBID, @dbName

      IF @Debug = 1
      Print 'Load dbNames_Cursor'

      --Continue this loop until no more Active Database Name
      entries available
      WHILE @@FETCH_STATUS = 0
      BEGIN
      DELETE
      FROM TempHolding

      IF @Debug = 1
      Print 'Empty TempHolding'

      SET @ConcantenatedTableStructure = @ServerName + '.' +
      @dbname + '.dbo.SysFiles'
      SET @String = '(' + char(39) + '%.MDF%' + char(39) + ')'

      IF @Debug = 1
      Print @ConcantenatedTableStructure + @String

      SET ANSI_NULLS Off

      --Create SQL Statement to Get Size and Location of MDF
      File for Current Database Name
      SET @SQL = 'INSERT INTO TempHolding (hldNumber,
      hldFilePath)
      SELECT Size, FileName
      FROM ' + @ConcantenatedTableStructure + '
      WHERE FileName like ' + @String

      SET ANSI_NULLS On

      IF @Debug = 1
      Print @SQL

      --Run SQL Statement
      EXEC(@SQL)

      IF @Debug = 1
      Print 'Size Loaded'

      --Transfer MDF Results to Holding Variables
      SELECT @MDFSize = hldNumber*8/* Convert from 8192 byte
      pages to K */,
      @MDFFilePath = hldFilePath
      FROM TempHolding

      --Insert MDF Size into MDFSizeInfo Table
      INSERT INTO MDFSizeInfo (DBID, CurrentSize, AddedOn)
      SELECT @dbID, @MDFSize, @CurrentDate

      --Add MDF Size to Used Space Variable
      Set @UsedSpace = @UsedSpace + @MDFSize

      IF @Debug = 1
      Print 'MDFSize = ' + cast(@MDFSize as varchar(20))

      DELETE
      FROM TempHolding

      SET @String = '(' + char(39) + '%.LDF%' + char(39) + ')'

      IF @Debug = 1
      Print @String

      --Create SQL Statement to Get Size and Location of MDF
      File for Current Database Name
      SET @SQL = 'INSERT INTO TempHolding (hldNumber,
      hldFilePath)
      SELECT Size, FileName
      FROM ' + @ConcantenatedTableStructure + '
      WHERE FileName like ' + @String

      --Run SQL Statement
      EXEC(@SQL)

      --Transfer LDF Results to Holding Variables
      SELECT @LDFSize = hldNumber*8/* Convert from 8192 byte
      pages to K */,
      @LDFFilePath = hldFilePath
      FROM TempHolding

      --Insert LDF Size into MDFSizeInfo Table
      INSERT INTO LDFSizeInfo (DBID, CurrentSize, AddedOn)
      SELECT @dbID, @LDFSize, @CurrentDate

      --Add LDF Size to Used Space Variable
      Set @UsedSpace = @UsedSpace + @LDFSize

      IF @Debug = 1
      Print 'LDFSize = ' + cast(@LDFSize as varchar(20))

      --Check to see if any information for this Database
      --already in the DBFilePath table
      SELECT @RowCount = count(*)
      FROM dBFilePath
      WHERE DBID = @dbID

      --Insert or Update Information into
      --DBFilePath Table based on RowCount Results
      IF @RowCount > 0
      BEGIN
      UPDATE DBFilePath
      SET MDF_FilePath = @MDFFilePath,
      LDF_FilePath = @LDFFilePath
      WHERE DBID = @DBID
      END
      ELSE
      BEGIN
      INSERT INTO DBFilePath (MDF_FilePath,
      LDF_FilePath, DBID)
      SELECT @MDFFilePath, @LDFFilePath, @DBID
      END

      IF @Debug = 1
      Print 'Calling usp_TableDataGathering'

      EXEC usp_TableDataGathering @CurrentDate, @ServerName,
      @dbName, @DBID

      SET ANSI_NULLS OFF

      IF @Debug = 1
      Print 'Finished Calling usp_TableDataGathering'

      FETCH NEXT
      FROM dbNames_Cursor
      INTO @DBID, @dbName

      IF @Debug = 1
      Print 'Load Next dbNames_Cursor'

      END

      CLOSE dbNames_Cursor
      DEALLOCATE dbNames_Cursor

      --Add UsedSpace Information to SQLServerInfo Table
      INSERT INTO SQLServerInfo(SQLServerID, UsedSpace, AddedOn)
      SELECT @SQLServerID, @UsedSpace, @CurrentDate

      --Reset UsedSpace Variable for Next Server
      SET @UsedSpace = 0

      --Get Next Server Info
      FETCH NEXT
      FROM sqlServers_Cursor
      INTO @SQLServerID, @ServerName

      IF @Debug = 1
      Print 'Load Next sqlServers_Cursor'

      END

      CLOSE sqlServers_Cursor
      DEALLOCATE sqlServers_Cursor

      DROP TABLE TempHolding





      GO
    • Kris Sellers
      In addition, you also need this script, as it is called in the previous script. ... CREATE PROCEDURE usp_TableDataGathering ( @CurrentDate datetime,
      Message 2 of 5 , Apr 5, 2004
      • 0 Attachment
        In addition, you also need this script, as it is called in the
        previous script.

        --------------------------------------------------

        CREATE PROCEDURE usp_TableDataGathering
        (
        @CurrentDate datetime,
        @ServerName varchar(50),
        @dbName varchar(50),
        @DBID int
        )
        AS

        SET NOCOUNT ON

        DECLARE @TableName as varchar(50)
        DECLARE @TableID as int
        DECLARE @OldTableID as int
        DECLARE @SQLServerID as int
        DECLARE @RowCount as bigint
        DECLARE @SQL as varchar(500)
        DECLARE @ConcantenatedTableStructure as varchar(100)
        DECLARE @Debug as bit

        SET @Debug = 0 --Turn Debug Off

        /*
        --Debugging Variables
        SET @Debug = 1 --Turn Debug On
        DECLARE @CurrentDate datetime
        DECLARE @ServerName varchar(50)
        DECLARE @dbName varchar(50)
        DECLARE @DBID int
        SET @CurrentDate = getdate()
        SET @ServerName = 'DSSDev'
        SET @dbName = 'Monitor'
        SET @DBID = 18
        */

        SET @ConcantenatedTableStructure = @ServerName + '.' + @dbname
        + '.dbo.SysObjects'

        IF @Debug = 1
        PRINT @ConcantenatedTableStructure

        SET @SQL = 'UPDATE TableNames
        SET ActiveTableName = 0
        FROM TableNames a
        JOIN (SELECT Name, ID
        FROM ' + @ConcantenatedTableStructure + '
        WHERE xtype = ' + char(39) + 'U' + char(39) + '
        AND Name <> ' + char(39) + 'dtproperties' +
        char(39) + '
        AND Name <> ' + char(39) + 'TempHolding' + char
        (39) + ') b
        ON b.Name = a.TableName
        WHERE DBID = cast(' + cast(@DBID as varchar(20)) + ' as
        int)
        AND ID <> SQLSystemID'


        IF @Debug = 1
        PRINT @SQL

        EXEC(@SQL)

        IF @Debug = 1
        PRINT 'InActive Table Names Updated'

        SET @SQL = 'INSERT INTO TableNames(DBID, TableName, SQLSystemID)
        SELECT cast(' + cast(@DBID as varchar(20)) + ' as int),
        Name, ID
        FROM ' + @ConcantenatedTableStructure + '
        WHERE xtype = ' + char(39) + 'U' + char(39) + '
        AND Name <> ' + char(39) + 'dtproperties' + char(39) + '
        AND Name <> ' + char(39) + 'TempHolding' + char(39) + '
        AND Name not in (SELECT TableName
        FROM TableNames
        WHERE DBID = cast(' + cast(@DBID as
        varchar(20)) + ' as int)
        AND ActiveTableName = 1)'
        IF @Debug = 1
        PRINT @SQL

        EXEC(@SQL)

        IF @Debug = 1
        PRINT 'New Table Names Inserted'

        SELECT TOP 1 @TableID = TableID, @TableName = TableName,
        @SQLServerID = SQLSystemID
        FROM TableNames
        WHERE DBID = @DBID
        AND ActiveTableName = 1
        ORDER BY TableID

        IF @Debug = 1
        BEGIN
        PRINT 'TableID = ' + cast(@TableID as varchar(10))
        PRINT 'TableName = ' + @TableName
        PRINT 'SQLServerID = ' + cast(@SqLServerID as varchar(10))
        END

        WHILE @TableID > 0
        BEGIN
        SET @ConcantenatedTableStructure = @ServerName + '.' + @dbname
        + '.dbo.sysindexes'

        IF @Debug = 1
        PRINT @ConcantenatedTableStructure

        DELETE
        FROM TempHolding

        SET @SQL = 'INSERT INTO TempHolding (hldNumber)
        SELECT max(RowCnt)
        FROM ' + @ConcantenatedTableStructure + '
        WHERE ID = cast(' + cast(@SQLServerID as varchar
        (20)) + ' as int)'

        IF @Debug = 1
        PRINT @SQL

        EXEC(@SQL)

        IF @Debug = 1
        PRINT 'Row Count Inserted'

        /* EXEC sp_configure 'remote query timeout', 0

        reconfigure with override
        */
        SELECT @RowCount = hldNumber
        FROM TempHolding

        INSERT INTO TableInfo (TableID, CurrentRowCount, AddedOn)
        SELECT @TableID, Coalesce(@RowCount, 0), @CurrentDate

        SET @OldTableID = @TableID

        SELECT TOP 1 @TableID = TableID, @TableName = TableName,
        @SQLServerID = SQLSystemID
        FROM TableNames
        WHERE DBID = @DBID
        AND ActiveTableName = 1
        AND TableID > @OldTableID
        ORDER BY TableID

        IF @OldTableID = @TableID
        BEGIN
        SET @TableID = -1
        END
        END





        GO
      Your message has been successfully submitted and would be delivered to recipients shortly.