Database Growth

USE DBATasks;
GO
IF OBJECT_ID(N'vwAdmin_DB_List') IS NOT NULL
DROP VIEW [dbo].[vwAdmin_DB_List]
GO
CREATE VIEW [dbo].[vwAdmin_DB_List]
AS
     SELECT sd.[name] AS 'DBName',
            SUBSTRING(SUSER_SNAME(sd.[owner_sid]), 1, 24) AS 'Owner'
     FROM master.sys.databases sd
            WHERE HAS_DBACCESS(sd.[name]) = 1
                  AND sd.[is_read_only] = 0
                  AND sd.[state_desc] = 'ONLINE'
                  AND sd.[user_access_desc] = 'MULTI_USER'
                  AND sd.[is_in_standby] = 0;
GO
IF OBJECT_ID(N'Admin_DBGrowthSnapDetail', N'U') IS NULL
BEGIN  
CREATE TABLE [dbo].[Admin_DBGrowthSnapDetail]
([DBID]          [INT] NOT NULL,
 [FileID]        [INT] NOT NULL,
 [FileType]      [INT] NOT NULL,
 [SizeMB]        [INT] NOT NULL,
 [UsedMB]        [INT] NULL,
 [DBNAME]        [NVARCHAR](200) NULL,
 [FileNAME]      [NVARCHAR](128) NULL,
 [FileGroupName] [SYSNAME] NULL,
 DataSpaceID     SMALLINT,
 SnapDate        [DATETIME2](7)
);
END
GO


--Stored Proc #3
IF OBJECT_ID(N'spAdmin_DBGrowth') IS NOT NULL
DROP PROCEDURE dbo.spAdmin_DBGrowth
GO
CREATE PROCEDURE dbo.spAdmin_DBGrowth
AS
SET NOCOUNT ON
     BEGIN
         INSERT INTO dbo.Admin_DBGrowthSnapDetail
                SELECT mf.database_id,
                       mf.file_id,
                       mf.type,
                       --To convert size in MB
                       CEILING(mf.size * 1.0 / 128),
                       NULL,
                       DB_NAME(mf.database_id),
                       mf.name,
                       NULL,
                       NULL,
                       SYSDATETIME()
                FROM sys.master_files mf
                       ORDER BY mf.database_id,
                                mf.file_id;
         DECLARE @DBNAME NVARCHAR(255);
         DECLARE @DBID INT;
         DECLARE @SQL NVARCHAR(2000);
         DECLARE cDatabases CURSOR
         FOR SELECT UPPER(name),
                    database_id
             FROM sys.databases sd(NOLOCK)
                  INNER JOIN dbo.vwAdmin_DB_List vw ON sd.name = vw.DBName;
         OPEN cDatabases;
         FETCH NEXT FROM cDatabases INTO @DBNAME, @DBID;
         WHILE @@FETCH_STATUS = 0
             BEGIN
                 SET @SQL = N'use ['+@DBNAME+']
          UPDATE A
             SET  UsedMB = (ceiling(S.size/128) - ceiling(S.size/128.0 - CAST(FILEPROPERTY(S.name, ''SpaceUsed'') AS int )/128.0))
, FileGroupName = ISNULL(fg.name,''LogFile'')
, DataSpaceID = fg.data_space_id
FROM DBATasks.dbo.Admin_DBGrowthSnapDetail A (NOLOCK)
JOIN sys.database_files S ON
               A.DBID = '+CONVERT(NVARCHAR(10), @DBID)+'
               AND A.FileID = S.file_id LEFT JOIN sys.filegroups fg on fg.data_space_id = S.data_space_id;';
                 EXEC sp_executesql
                      @SQL,
                      N'@DBNAME nvarchar(128),  @DBID int',
                      @DBNAME,
                      @DBID;
                 FETCH NEXT FROM cDatabases INTO @DBNAME, @DBID;
             END;
         CLOSE cDatabases;
         DEALLOCATE cDatabases;
     END;
GO
USE [msdb]
GO

IF EXISTS (SELECT 1 FROM msdb..sysjobs where name like 'DBA_DBGrowthDetail')
EXEC msdb.dbo.sp_delete_job @job_name=N'DBA_DBGrowthDetail', @delete_unused_schedule=1
GO

BEGIN TRANSACTION
DECLARE @ReturnCode INT
SELECT @ReturnCode = 0
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'DBA_DBGrowthDetail',
@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
EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'DBGrowth',
@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'spAdmin_DBGrowth',
@database_name=N'DBATasks',
@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'Sunday Schedule',
@enabled=1,
@freq_type=8,
@freq_interval=1,
@freq_subday_type=1,
@freq_subday_interval=0,
@freq_relative_interval=0,
@freq_recurrence_factor=1,
@active_start_date=20010820,
@active_end_date=99991231,
@active_start_time=223000,
@active_end_time=235959
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


--ROLLBACK
--DROP VIEW [dbo].[vwAdmin_DB_List]
--DROP TABLE [dbo].[Admin_DBGrowthSnapDetail]
--DROP PROCEDURE dbo.spAdmin_DBGrowth
--EXEC msdb.dbo.sp_delete_job @job_name=N'DBA_DBGrowthDetail', @delete_unused_schedule=1

vwAdmin_DB_List

CREATE VIEW [dbo].[vwAdmin_DB_List]
AS


SELECT
sd.[name] AS 'DBName'
, SUBSTRING(SUSER_SNAME(sd.[owner_sid]), 1, 24) AS 'Owner'
FROM
master.sys.databases sd
WHERE
HAS_DBACCESS(sd.[name]) = 1
AND sd.[is_read_only] = 0
AND sd.[state_desc] = 'ONLINE'
AND sd.[user_access_desc] = 'MULTI_USER'
AND sd.[is_in_standby] = 0

select s.name, t.name, i.name, c.name from sys.tables t
inner join sys.schemas s on t.schema_id = s.schema_id
inner join sys.indexes i on i.object_id = t.object_id
inner join sys.index_columns ic on ic.object_id = t.object_id
inner join sys.columns c on c.object_id = t.object_id and
        ic.column_id = c.column_id

where i.index_id > 0    
 and i.type in (1, 2) -- clustered & nonclustered only
 and i.is_primary_key = 0 -- do not include PK indexes
 and i.is_unique_constraint = 0 -- do not include UQ
 and i.is_disabled = 0
 and i.is_hypothetical = 0
 and ic.key_ordinal > 0

order by ic.key_ordinal