Monitoring and Reporting Sqlserver Databases Growth For All Sql Instance.

Posted: March 19, 2011 in Sqlserver

 

Requirement:

I had a requirement to monitor the Growth of all the databases of all the sqlserver instances in network and create the report for higher management.

Solution:

After trying many things and I got a simple and the best solution to get this job done.

To record all the databases growth in your network all you need is to do is follow the below steps.

You need a Master server something like DBA admin server which monitors all the sqlservers in network.

  1. Create Linked server in all your sqlserver instances in network.

For example: ABC is my Master server

1.      XYZ is my network SqlServer

2.      PQR is my another Network Sqlserver

Create Linked server ABC in XYZ and PQR network server.

2.    Create a Table in your monitoring database of Master server

3.    Create a sql agent job on all the network sqlserver to capture and insert the data in master server table.

All can be done easily if you are working with SQLserver2008 using Central Management Server(CMS)

/*****——————————-Code to Create Linked server——————————————****/

USE [master]
GO
/****** Object:  LinkedServer [ABC]    Script Date: 03/04/2011 05:05:37 ******/
EXEC master.dbo.sp_dropserver @server=N’ABC’, @droplogins=’droplogins’
GO

USE [master]
GO
EXEC master.dbo.sp_addlinkedserver @server = N’ABC’, @srvproduct=N’SQL Server’

GO
EXEC master.dbo.sp_serveroption @server=N’ABC’, @optname=N’collation compatible’, @optvalue=N’false’
GO
EXEC master.dbo.sp_serveroption @server=N’ABC’, @optname=N’data access’, @optvalue=N’true’
GO
EXEC master.dbo.sp_serveroption @server=N’ABC’, @optname=N’dist’, @optvalue=N’false’
GO
EXEC master.dbo.sp_serveroption @server=N’ABC’, @optname=N’pub’, @optvalue=N’false’
GO
EXEC master.dbo.sp_serveroption @server=N’ABC’, @optname=N’rpc’, @optvalue=N’true’
GO
EXEC master.dbo.sp_serveroption @server=N’ABC’, @optname=N’rpc out’, @optvalue=N’true’
GO
EXEC master.dbo.sp_serveroption @server=N’ABC’, @optname=N’sub’, @optvalue=N’false’
GO
EXEC master.dbo.sp_serveroption @server=N’ABC’, @optname=N’connect timeout’, @optvalue=N’0′
GO
EXEC master.dbo.sp_serveroption @server=N’ABC’, @optname=N’collation name’, @optvalue=null
GO
EXEC master.dbo.sp_serveroption @server=N’ABC’, @optname=N’lazy schema validation’, @optvalue=N’false’
GO
EXEC master.dbo.sp_serveroption @server=N’ABC’, @optname=N’query timeout’, @optvalue=N’0′
GO
EXEC master.dbo.sp_serveroption @server=N’ABC’, @optname=N’use remote collation’, @optvalue=N’true’
GO
USE [master]
GO
EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname = N’ABC’, @locallogin = NULL , @useself = N’False’, @rmtuser = N’sysdba’, @rmtpassword = N’xxxxxx’
GO

/***——————Create Base Table in Monitoring Database of Master server———–***/
CREATE TABLE [dbo].[tblDatabaseGrowth](
[DBName] [varchar](200) NULL,
[Size] [varchar](20) NULL,
[Owner] [varchar](50) NULL,
[DBid] [int] NULL,
[Created] [datetime] NULL,
[Status] [varchar](4000) NULL,
[Compatability] [int] NULL,
[PollDate] [datetime] NULL,
[ServerName] [varchar](50) NULL
) ON [PRIMARY]

GO

SET ANSI_PADDING OFF
GO

ALTER TABLE [dbo].[tblDatabaseGrowth] ADD  CONSTRAINT [DF_tblDatabaseGrowth_PollDate]  DEFAULT (getdate()) FOR [PollDate]
GO

ALTER TABLE [dbo].[tblDatabaseGrowth] ADD  DEFAULT (”XX”) FOR [ServerName]
GO

/*****—————-Code to create Sql agent job in all the network server———————- **/

USE [msdb]
GO
/****** Object:  Job [DBA_DatabaseGrowthMonitor]    Script Date: 03/04/2011 08:36:14 ******/
EXEC msdb.dbo.sp_delete_job @job_name=’DBA_DatabaseGrowthMonitor’, @delete_unused_schedule=1
GO

USE [msdb]

GO

/****** Object:  Job [DBA_DatabaseGrowthMonitor]    Script Date: 03/04/2011 08:32:15 ******/
BEGIN TRANSACTION
DECLARE @ReturnCode INT
SELECT @ReturnCode = 0
/****** Object:  JobCategory [[Uncategorized (Local)]]]    Script Date: 03/04/2011 08:32:16 ******/
IF NOT EXISTS (SELECT name FROM msdb.dbo.syscategories WHERE name=N'[Uncategorized (Local)]’ AND category_class=1)
BEGIN
EXEC @ReturnCode = msdb.dbo.sp_add_category @class=N’JOB’, @type=N’LOCAL’, @name=N'[Uncategorized (Local)]’
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

END

DECLARE @jobId BINARY(16)
EXEC @ReturnCode =  msdb.dbo.sp_add_job @job_name=N’DBA_DatabaseGrowthMonitor’,
@enabled=1,
@notify_level_eventlog=0,
@notify_level_email=0,
@notify_level_netsend=0,
@notify_level_page=0,
@delete_level=0,
@description=N’No description available.’,
@category_name=N'[Uncategorized (Local)]’,
@owner_login_name=N’sa’, @job_id = @jobId OUTPUT
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
/****** Object:  Step [DatabaseGrowthMonito]    Script Date: 03/04/2011 08:32:16 ******/
EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N’DatabaseGrowthMonito’,
@step_id=1,
@cmdexec_success_code=0,
@on_success_action=1,
@on_success_step_id=0,
@on_fail_action=2,
@on_fail_step_id=0,
@retry_attempts=0,
@retry_interval=0,
@os_run_priority=0, @subsystem=N’TSQL’,
@command=N’USE tempdb
GO

/****** Object:  Table [dbo].[#tblDatabaseGrowth]    Script Date: 03/04/2011 08:02:33 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

SET ANSI_PADDING ON
GO

CREATE TABLE [dbo].[#tblDatabaseGrowth](
[DBName] [varchar](200) NULL,
[Size] [varchar](20) NULL,
[Owner] [varchar](50) NULL,
[DBid] [int] NULL,
[Created] [datetime] NULL,
[Status] [varchar](4000) NULL,
[Compatability] [int] NULL,
[PollDate] [datetime] NULL,
[ServerName] [varchar](50) NULL
) ON [PRIMARY]

GO

SET ANSI_PADDING OFF
GO

ALTER TABLE [dbo].[#tblDatabaseGrowth] ADD  CONSTRAINT [DF_tblDatabaseGrowth_PollDate]  DEFAULT (getdate()) FOR [PollDate]
GO

ALTER TABLE [dbo].[#tblDatabaseGrowth] ADD  DEFAULT (”XX”) FOR [ServerName]
GO

—Insertion of SP_Helpdb data of database in temp table
Insert into #tblDatabaseGrowth
(DBname,size,owner,DBId,created,status,compatability)
exec sp_helpdb

–Setting varable to store servername which will put an update for serverName

Declare @sql nvarchar(25)
Set @sql=@@servername
Update  #tblDatabaseGrowth set ServerName=@sql where ServerName like ”%X%”
–select @sql
Select DBName,Size,Owner,DBID,Created,Status,
Compatability,PollDate,ServerName
from #tblDatabaseGrowth (nolock)

–Insert the actual data into master server by using linked server query.
Insert ABC.DBA_Admin.dbo.tblDatabaseGrowth
(DBname,size,owner,DBid,created,status,compatability,ServerName)
Select DBName,Size,Owner,DBID,Created,Status,Compatability,ServerName
from #tblDatabaseGrowth

–Drop temp table
Drop table #tblDatabaseGrowth
–Select DBName,Size,Owner,DBID,Created,Status,Compatability,PollDate,ServerName  from  dba_admin.dbo.tblDatabaseGrowth (nolock)
–truncate table dba_admin.dbo.tblDatabaseGrowth
‘,
@database_name=N’master’,
@flags=0
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_update_job @job_id = @jobId, @start_step_id = 1
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobschedule @job_id=@jobId, @name=N’DBGrowthMonitor’,
@enabled=1,
@freq_type=4,
@freq_interval=1,
@freq_subday_type=1,
@freq_subday_interval=0,
@freq_relative_interval=0,
@freq_recurrence_factor=0,
@active_start_date=20110303,
@active_end_date=99991231,
@active_start_time=0,
@active_end_time=235959
–@schedule_uid=N’22b56d17-5138-496c-a721-c3bbaad554f7′
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @jobId, @server_name = N'(local)’
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
COMMIT TRANSACTION
GOTO EndSave
QuitWithRollback:
IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTION
EndSave:

GO

———————Reporting can be done using Excel Pivot table———————————————-

All you need to do is create a sql connection in MS Excel and display the result in the form of Pivot Table


Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s