Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

Server Farm Reporting - Part 2

By Mark Tierney,

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.

Total article views: 2796 | Views in the last 30 days: 3
 
Related Articles
FORUM

Error converting data type nvarchar to bigint

Error converting data type nvarchar to bigint

FORUM

Error converting data type nvarchar to float

Error converting data type nvarchar to float

FORUM

nvarchar -int

nvarchar -int

FORUM

Select syntax error

Select syntax error

FORUM

Database mirroring error in sql server 2008

Database mirroring error in sql server 2008

Tags
 
Contribute

Join the most active online SQL Server Community

SQL knowledge, delivered daily, free:

Email address:  

You make SSC a better place

As a member of SQLServerCentral, you get free access to loads of fresh content: thousands of articles and SQL scripts, a library of free eBooks, a weekly database news roundup, a great Q & A platform… And it’s our huge, buzzing community of SQL Server Professionals that makes it such a success.

Join us!

Steve Jones
Editor, SQLServerCentral.com

Already a member? Jump in:

Email address:   Password:   Remember me: Forgotten your password?
Steve Jones