Feeds:
Posts
Comments

Archive for October, 2011

--===========================Monitor DB Size Growth Report In SQL Server 2005=======================

USE [master]
GO

CREATE DATABASE [DBA_Activities]
GO

--====================================================================================================

USE [DBA_Activities]
GO

CREATE TABLE [dbo].[Get_DB_Details_DBA](
	[Server_Name] [varchar](100) NULL,
	[Database_Name] [varchar](100) NULL,
	[Logical_File_Name] [sysname] NOT NULL,
	[Physical_File_Name] [nvarchar](520) NULL,
	[File_Size_MB] [int] NULL,
	[Status] [sysname] NOT NULL,
	[Recovery_Mode] [sysname] NOT NULL,
	[Free_Space_MB] [int] NULL,
	[Free_Space_Per] [varchar](7) NULL,
	[Get_Date] [datetime] NULL
) ON [PRIMARY]

--====================================================================================================

USE [DBA_Activities]
GO

CREATE  PROC   [dbo].[Get_Database_Size_Growth]
(
     @dtFromDate DateTime=Null,
     @dtToDate DateTime=Null,
     @Option Varchar(10)='DAY_WISE' --DAY_WISE,MONTH_WISE,YEAR_WISE

)
AS

BEGIN

Declare @SQL Varchar(Max)

Declare @intFromMonth int
Declare @intToMonth int

Declare @intFromYear int
Declare @intToYear int

Declare @intI int
Declare @intDiff int

Declare @dtDate datetime

If @dtFromDate Is Null And @dtToDate Is Null
   Begin
      Set @dtFromDate = (Select Min(Get_Date) from Get_DB_Details_DBA)
      Set @dtToDate= (Select Max(Get_Date) from Get_DB_Details_DBA)
   End

If @dtFromDate Is Null
   Begin
      Set @dtFromDate = (Select Min(Get_Date) from Get_DB_Details_DBA)
   End

If @dtToDate Is Null
   Begin
      Set @dtToDate= (Select Max(Get_Date) from Get_DB_Details_DBA)
   End

If @dtFromDate > @dtToDate
   Begin
      RAISERROR ('From date should be less than To date.',10,1)
      Return
   End

If @intFromMonth < 1 Or @intFromMonth > 12
   Begin
      RAISERROR ('From Date Month between 1 to 12.',10,1)
      Return
   End

If @intToMonth < 1 Or @intToMonth > 12
   Begin
      RAISERROR ('To Date Month between 1 to 12.',10,1)
      Return
   End

If @intFromYear < 1753 Or @intFromYear > 9999
   Begin
      RAISERROR ('From Date Year between 1 to 12.',10,1)
      Return
   End

If @intToYear < 1753 Or @intToYear > 9999
   Begin
      RAISERROR ('From Date Year between 1 to 12.',10,1)
      Return
   End

Set @SQL = 'Select Database_Name'

If @Option = 'DAY_WISE'

   Begin

     Set @dtDate = @dtFromDate

     While (@dtDate <= @dtToDate)

       Begin
         Set @SQL = @SQL + ',(Sum(Case When Get_Date=' + char(39) + CONVERT(VARCHAR(10), @dtDate, 110) + char(39) + ' Then File_Size_MB Else Null End)/1024.0) As ' + char(39) + REPLACE(CONVERT(VARCHAR(11), @dtDate, 106), ' ', '-') + char(39)
         Set @dtDate = @dtDate + 1
       End

   End

If @Option = 'MONTH_WISE'

   Begin

      Set @intFromMonth = Month(@dtFromDate)
      Set @intToMonth = Month(@dtToDate)

      Set @intFromYear = Year(@dtFromDate)
      Set @intToYear = Year(@dtToDate)

      --Set @dtFromDate = (CONVERT(DATETIME, (CONVERT(Varchar(4),@intFromYear) + '-' + CONVERT(Varchar(2),@intFromMonth) +  '-' + '01') ,20))
      --Set @dtToDate = (CONVERT(DATETIME, (CONVERT(Varchar(4),@intToYear) + '-' + CONVERT(Varchar(2),@intToMonth) +  '-' + '01') ,20))

      Set @intI = 0
      Set @intDiff = (DateDiff(mm,@dtFromDate,@dtToDate))

      Set @dtDate = @dtFromDate

      While (@intI <= @intDiff)

         Begin
           Set @dtDate= (DateAdd(mm,@intI,@dtFromDate))
           Set @SQL = @SQL + ',(Sum(Case When Get_Date=' + char(39) + CONVERT(VARCHAR(10), @dtDate, 110) + char(39) + ' Then File_Size_MB Else Null End)/1024.0) As ' + char(39) + REPLACE(CONVERT(VARCHAR(11), @dtDate, 106), ' ', '-') + char(39)
           Set @intI=@intI+1
         End

   End

If @Option = 'YEAR_WISE'

   Begin

      Set @intFromYear = Year(@dtFromDate)
      Set @intToYear = Year(@dtToDate)

      --Set @dtFromDate = (CONVERT(DATETIME, (CONVERT(Varchar(4),@intFromYear) + '-' + 'Jan' +  '-' + '01') ,20))
      --Set @dtToDate = (CONVERT(DATETIME, (CONVERT(Varchar(4),@intToYear) + '-' + 'Jan' +  '-' + '01') ,20))

      Set @intI = 0
      Set @intDiff = (DateDiff(yy,@dtFromDate,@dtToDate))

      Set @dtDate = @dtFromDate

      While (@intI <= @intDiff)

         Begin
           Set @dtDate= (DateAdd(yy,@intI,@dtFromDate))
           Set @SQL = @SQL + ',(Sum(Case When Get_Date=' + char(39) + CONVERT(VARCHAR(10), @dtDate, 110) + char(39) + ' Then File_Size_MB Else Null End)/1024.0) As ' + char(39) + REPLACE(CONVERT(VARCHAR(11), @dtDate, 106), ' ', '-') + char(39)
           Set @intI=@intI+1
         End

   End

Set @SQL = @SQL + 'From Get_DB_Details_DBA Group By Database_Name Order by Database_Name'
EXECUTE(@SQL)

END

GO

--====================================================================================================

USE [msdb]
GO

/****** Object:  Job [Get_DB_Details_DBA]    Script Date: 10/04/2011 14:40:30 ******/
BEGIN TRANSACTION
DECLARE @ReturnCode INT
SELECT @ReturnCode = 0
/****** Object:  JobCategory [Database Maintenance]    Script Date: 10/04/2011 14:40:30 ******/
IF NOT EXISTS (SELECT name FROM msdb.dbo.syscategories WHERE name=N'Database Maintenance' AND category_class=1)
BEGIN
EXEC @ReturnCode = msdb.dbo.sp_add_category @class=N'JOB', @type=N'LOCAL', @name=N'Database Maintenance'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

END

DECLARE @jobId BINARY(16)
EXEC @ReturnCode =  msdb.dbo.sp_add_job @job_name=N'Get_DB_Details_DBA',
		@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'Database Maintenance',
		@owner_login_name=N'sa', @job_id = @jobId OUTPUT
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
/****** Object:  Step [Step1]    Script Date: 10/04/2011 14:40:30 ******/
EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'Step1',
		@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'DECLARE @DBSizeDetails TABLE
(Server_Name VARCHAR(100),
Database_Name VARCHAR(100),
Logical_File_Name sysname,
Physical_File_Name NVARCHAR(520),
File_Size_MB INT,
Status sysname,
Recovery_Mode sysname,
Free_Space_MB INT,
Free_Space_Per VARCHAR(7),
Get_Date datetime)

DECLARE @command VARCHAR(5000)
SELECT @command = ''Use ['' + ''?'' + ''] SELECT
@@servername As Server_Name,
'' + '''''''' + ''?'' + ''''''''+ '' As Database_Name,
sysfiles.name As Logical_File_Name,
sysfiles.filename AS Physical_File_Name,
CAST(sysfiles.size/128.0 AS int) As File_Size_MB,
CONVERT(sysname,DatabasePropertyEx(''''?'''',''''Status'''')) AS Status,
CONVERT(sysname,DatabasePropertyEx(''''?'''',''''Recovery'''')) AS Recovery_Mode,
CAST(sysfiles.size/128.0 - CAST(FILEPROPERTY(sysfiles.name, '' + '''''''' +
''SpaceUsed'' + '''''''' + '' ) AS int)/128.0 AS int) AS Free_Space_MB,
CAST(100 * (CAST (((sysfiles.size/128.0 - CAST(FILEPROPERTY(sysfiles.name,
'' + '''''''' + ''SpaceUsed'' + '''''''' + '' ) AS int)/128.0)/(sysfiles.size/128.0))
AS decimal(4,2))) AS varchar(8)) + '' + '''''''' + ''%'' + '''''''' + '' AS Free_Space_Per,
GETDATE() as Get_Date FROM dbo.sysfiles''
INSERT INTO @DBSizeDetails
(Server_Name, Database_Name, Logical_File_Name, Physical_File_Name, File_Size_MB, Status,
Recovery_Mode, Free_Space_MB, Free_Space_Per, Get_Date)
EXEC sp_MSForEachDB @command

INSERT INTO Get_DB_Details_DBA (Server_Name, Database_Name, Logical_File_Name, Physical_File_Name, File_Size_MB, Status,Recovery_Mode, Free_Space_MB, Free_Space_Per,Get_Date)
SELECT
Server_Name, Database_Name, Logical_File_Name, Physical_File_Name, File_Size_MB, Status,
Recovery_Mode, Free_Space_MB, Free_Space_Per,CONVERT(CHAR(10), Get_Date, 101) FROM @DBSizeDetails Where Database_Name Not In (''master'',''msdb'',''tempdb'',''model'')
',
		@database_name=N'DBA_Activities',
		@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'Sche1',
		@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=20101029,
		@active_end_date=99991231,
		@active_start_time=200000,
		@active_end_time=235959,
		@schedule_uid=N'3b2022cd-96b6-42df-8cab-d3be37d711b4'
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

--====================================================================================================

USE [DBA_Activities]
GO

Exec Get_Database_Size_Growth
Exec Get_Database_Size_Growth '05-May-2011'
Exec Get_Database_Size_Growth '05-May-2011','05-Jun-2011'
Exec Get_Database_Size_Growth '05-May-2011','05-Oct-2011','MONTH_WISE'
Exec Get_Database_Size_Growth NULL,NULL,'MONTH_WISE'
Exec Get_Database_Size_Growth '05-May-2011','05-May-2012','YEAR_WISE'

--====================================================================================================

Read Full Post »