Introduction

In part one, I described the SQL Server Farm reporting in the general terms of the methodology used. In this article, I will review in detail the data gathering process used at my site for space and database properties.

After each example you will see a step by step description that corresponds to the numbers found on the right in the proc or script.

Space Procedure

In order to gather state and space information from each of our servers, we created a stored procedure that resides on each of the servers

that we wished to collect this data from. This procedure will run on the server and provide it's data back in a result set. The procedure

is as follows:

create procedure [dbo].[article_dbinfo] 
as 
SET NOCOUNT ON
declare @v_min integer;
declare @v_max integer;
declare @v_size integer;
declare @v_pagesize integer;
declare @v_name nvarchar(128);
declare @v_str nvarchar(2000);
create table #temp_db_info (                                  /* (1) */id int identity,
DBRunDate datetime,
DBServer nvarchar(30),
dbname nvarchar(128),
crdate datetime,
cmptlevel integer,
dbsize integer,
dbcollation nvarchar(128),
IsAutoClose integer,
IsAutoCreateStatistics integer,
IsAutoUpdateStatistics integer,
IsInStandBy integer,
DBRecovery nvarchar(30),
DBStatus nvarchar(30),
DBUpdate nvarchar(30),
DBUserAccess nvarchar(30)
)
 /*
    Get all database names 
 */insert into #temp_db_info (DBRunDate,DBServer,dbname,crdate,cmptlevel)    /* (2) */select getdate(),@@servername,name,crdate,cmptlevel
from sysdatabases
where name not in ('master',
                   'model',
                   'monitor',
                   'msdb',
                   'northwind',
                   'pubs',
                   'tempdb')
select @v_min = min(id) from #temp_db_info                                /* (3) */select @v_max = max(id) from #temp_db_info
/*
   Get pagesize
*/select @v_pagesize = 8196
/* 
   Loop through all databases and get current states
*/while @v_min <= @v_max
begin
  select @v_name = dbname from #temp_db_info where id = @v_min            /* (4) */  
  update #temp_db_info
   set dbcollation =  cast(DATABASEPROPERTYEX(@v_name,'Collation')
                           as nvarchar(128)),
     IsAutoClose = cast(DATABASEPROPERTYEX(@v_name,'IsAutoClose') as int),
     IsAutoCreateStatistics = cast(DATABASEPROPERTYEX(@v_name
                                   ,'IsAutoCreateStatistics') as int),
     IsAutoUpdateStatistics = cast(DATABASEPROPERTYEX(@v_name
                                   ,'IsAutoUpdateStatistics') as int),
     IsInStandBy = cast(DATABASEPROPERTYEX(@v_name,'IsInStandBy') as int),
     DBRecovery =  cast(DATABASEPROPERTYEX(@v_name,'Recovery') as nvarchar(30)),
     DBStatus =  cast(DATABASEPROPERTYEX(@v_name,'Status') as nvarchar(30)),
     DBUpdate =  cast(DATABASEPROPERTYEX(@v_name,'Updateability') as nvarchar(30)),
     DBUserAccess = cast(DATABASEPROPERTYEX(@v_name,'UserAccess') as nvarchar(30))
    where id = @v_min
/*
   Sum up all files usage for the database                                /* (5)  */ 
*/    
  select @v_str = 'update #temp_db_info set dbsize = 
                  (select (sum(cast(size as decimal(31,0))))*' + 
                   cast(@v_pagesize as NVarchar(20)) + '/1048576 from ' + 
                   quotename(@v_name, N'[') + N'.dbo.sysfiles) WHERE id ='+
                   cast(@v_min as nvarchar(10))
/*
   Can't get sizes of database if it's offline
*/  
  IF (select DBStatus from #temp_db_info where id = @v_min) = 'ONLINE'    /* (6)  */   EXECute (@v_str)
 else
   update #temp_db_info set dbsize=-1 where id = @v_min
  select @v_min = @v_min+1                                                /* (7)  */ 
end
                                                                          /*  (8)  */select  DBRunDate,
           DBServer,
           dbname,
           crdate,
           cmptlevel,
           dbsize,
           dbcollation,
           IsAutoClose,
           IsAutoCreateStatistics,
           IsAutoUpdateStatistics,
           IsInStandBy,
           DBRecovery,
           DBStatus,
           DBUpdate,
           DBUserAccess
 from #temp_db_info
  order by id
drop table #temp_db_info
end

Description of procedure processing:

1. Create a temporary table to hold state and size information for each database on this server. Separate columns were used

   for each database property to allow for the flexibility of filtering by property in the reports.

2. Seed the temporary table with a row for each database, avoiding the ones supplied by Microsoft.
3. Select the minimum and maximum database identity values in the temporary table. This will be used to control a loop as well as additional

   updates for property and size information.

4. Get the database name from the temporary table and update all of the desired property information for that database in the

   table.

5. Set up an update statement to sum up all of the file sizes for this database and save it in MB. Since the column size in

   sysfiles is supplied as the number of pages, the pagesize in bytes is multiplied with that value which is then

   divided by the byte value of one MB.

6. Before executing the update, verify that the database state is online. This avoids an error since databasename.dbo.sysfiles cannot

   be queried if the database is offline. If online, the update from above is executed, if not a placeholder value of -1 is provided.

7. Advance to the next identity value for the loop. Drop out of loop at the last database.
8. Provide result set with current status (property information and sizes).

As mentioned in part one, this procedure will be driven by a job setup on the repository server.

Repository Server objects

On the repository server a permanent table was created that parallels the temporary one found in the space stored procedure to store the info from each server:

CREATE TABLE [dbo].[article_state_history](
[DBdateonly] [varchar](10) NULL,
[DBRunDate] [datetime] NULL,
[DBServer] [nvarchar](30) NULL,
[dbname] [nvarchar](128) NULL,
[crdate] [datetime] NULL,
[cmptlevel] [int] NULL,
[dbsize] [int] NULL,
[dbcollation] [nvarchar](128) NULL,
[IsAutoClose] [int] NULL,
[IsAutoCreateStatistics] [int] NULL,
[IsAutoUpdateStatistics] [int] NULL,
[IsInStandBy] [int] NULL,
[DBRecovery] [nvarchar](30) NULL,
[DBStatus] [nvarchar](30) NULL,
[DBUpdate] [nvarchar](30) NULL,
[DBUserAccess] [nvarchar](30) NULL
) ON [PRIMARY]

As shown in part one, there is a server list table that is used to direct a scheduled job to the servers it needs to call the space procedure from. For ease of demonstration, that table is described here:

CREATE TABLE article_servers(
dbserver sysname NOT NULL,
oksw     int     NULL
) ON [PRIMARY]
GO
INSERT INTO article_servers (dbserver, oksw)
 values('SERVER1',1)
GO
INSERT INTO article_servers (dbserver, oksw)
 values('SERVER2',0)
GO
INSERT INTO article_servers (dbserver, oksw)
 values('SERVER3',1)
GO
etc...

Repository Job

Currently we schedule a job to run on the repository server once a day, on off hours, to collect space and property information from our server farm. The job consists of a step to drive the space procedure and a step that deletes data from article_state_history that is older than 45 days. The script running in the job is as follows:

SET NOCOUNT ON
declare @v_min int
declare @v_str varchar(100)
declare @v_dbsrv_print varchar(20)
declare @v_ERROR_NUMBER int
declare @v_ERROR_SEVERITY int
declare @v_ERROR_STATE int
declare @v_ERROR_PROCEDURE nvarchar(126)
declare @v_ERROR_LINE int
declare @v_ERROR_MESSAGE nvarchar(4000)
create table #looptable (id int identity(1,1), dbserver varchar(20))    /* (1) */create table #temp_state (
DBRunDate datetime,
DBServer nvarchar(30),
dbname nvarchar(128),
crdate datetime,
cmptlevel integer,
dbsize integer,
dbcollation nvarchar(128),
IsAutoClose integer,
IsAutoCreateStatistics integer,
IsAutoUpdateStatistics integer,
IsInStandBy integer,
DBRecovery nvarchar(30),
DBStatus nvarchar(30),
DBUpdate nvarchar(30),
DBUserAccess nvarchar(30)
)
insert into #looptable                                                  /* (2)  */ select dbserver from article_servers where oksw = 1
select @v_min = coalesce(min(id),0) from #looptable 
while @v_min > 0                                                        /* (3)  */begin
  
select @v_str = 'EXEC [' + dbserver  
           +'].master.dbo.article_dbinfo' from #looptable where id = @v_min    
  begin try
    insert into #temp_state exec (@v_str)                               /*  (4)  */     
  end try
  begin catch
    SELECT
        @v_ERROR_NUMBER = ERROR_NUMBER(),
        @v_ERROR_SEVERITY = ERROR_SEVERITY(),
        @v_ERROR_STATE = ERROR_STATE(),
        @v_ERROR_PROCEDURE = ERROR_PROCEDURE(),
        @v_ERROR_LINE = ERROR_LINE(),
        @v_ERROR_MESSAGE = ERROR_MESSAGE()
   end catch
   
  delete from #looptable where id = @v_min                              /*  (5) */  select @v_min = coalesce(min(id),0) from #looptable
end

insert into article_state_history                                       /* (6)  */select 
cast(datepart(yyyy,dbrundate) as varchar(4))+'-'+
       cast(datepart(mm,dbrundate) as varchar(2))+'-'+
       cast(datepart(dd,dbrundate) as varchar(2)) as rundateonly,
DBRunDate,
DBServer,
dbname,
crdate,
cmptlevel,
dbsize,
dbcollation,
IsAutoClose,
IsAutoCreateStatistics,
IsAutoUpdateStatistics,
IsInStandBy,
DBRecovery,
DBStatus,
DBUpdate,
DBUserAccess
 from #temp_state tmp 
go
drop table #looptable
go
drop table #temp_state
go

Description of script processing:

1. Create temporary tables to be used by the job. #looptable drives the execution loop to gather data from each server and

   #temp_state holds the data gathered from each server execution of the space procedure.

2. Seed #looptable with all of the servers whose OKSW is set to enabled.
3. Begin loop. Create execute string by using the dbserver column from #looptable resulting in a four part name for the

   procedure.

4. Insert data from remote procedure into temporary table.
5. Delete row from #looptable and get new minimum value for next server.
6. Insert all server data for this run into the reporting table. The derived value for column rundateonly is used for filtering/grouping.

Conclusion

Using the tools we already have we can pull data in this fashion onto a common repository for easy reference via SSRS. I hope this article provided a more detailed view of how any SQL Server shop can gather database, instance or performance data for centralized reporting.