RipatDBA, extra tables

Ripat DBA

Contact

Server viewer

Database

Download

Change menu

Extra tables

Examples

Q & A

License info

Version history

SQL articles

Extra tables for more reports.
Last changed: 31 March 2024

Create tables

To create more reports some extra tables should be created.

After these scripts are executed there will be 4 extra tables.
These tables should be filled with data every day.
This can be done by running SQL jobs daily on the remote servers. The monitor server collects the information once a day with a linked server.
It can also be collected with PowerShell scripts.

The tables which are needed to show a historical view for the diskspace, database growth, the last serverinformation and whether a server is up to date with server updates
are mentioned here below.
See Examples for reports generated with this 4 tables.

Create four extra tables in the database ServerViewer.
-- Drives
-- DatabaseSize
-- ServerInfo
-- ServicePackInfo

Drives

Create the table 'Drives', this contains the diskspace. Fill this daily. See SQL articles.

USE [ServerViewer]
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Drives](
    [DriveId] [int] IDENTITY(1,1) NOT NULL,
    [ServerName] [varchar](40) NULL,
    [Drive] [char](1) NULL,
    [TotalSize] [int] NULL,
    [FreeSpace] [int] NULL,
    [FreespaceTimestamp] [datetime] NULL,
    [PercentFree] [int] NULL,
    [Date] [datetime] NULL
) ON [PRIMARY]
GO

CREATE NONCLUSTERED INDEX [IDX_Drives] ON [dbo].[Drives]
(
    [DriveId] ASC
) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO
SET ANSI_PADDING ON
GO

CREATE NONCLUSTERED INDEX [IDX_Drives_FreespaceTimestamp_3] ON [dbo].[Drives]
(
    [FreespaceTimestamp] ASC
)
INCLUDE ([DriveId],
    [ServerName],
    [Drive]) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO
SET ANSI_PADDING ON
GO

CREATE NONCLUSTERED INDEX [IDX_Drives_FreespaceTimestamp_5] ON [dbo].[Drives]
(
    [FreespaceTimestamp] ASC
)
INCLUDE ([ServerName],
    [Drive],
    [FreeSpace],
    [TotalSize],
    [PercentFree]) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO

ALTER TABLE [dbo].[Drives] ADD CONSTRAINT [DF_Drives_Date] DEFAULT (getdate()) FOR [Date]
GO

DatabaseSize

Create the table 'DatabaseSize', this contains the database usage and size. Fill this daily.

USE [ServerViewer]
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[DatabaseSize](
    [DatabaseSizeId] [int] IDENTITY(1,1) NOT NULL,
    [ServerName] [varchar](40) NULL,
    [ScanDate] [datetime] NOT NULL,
    [FileName] [varchar](255) NULL,
    [DbName] [varchar](255) NULL,
    [Name] [varchar](255) NULL,
    [SizeMB] [float] NULL,
    [UsedMB] [float] NULL,
    [FreeMB] [float] NULL,
    [FreePercent] [float] NULL,
    [Date] [datetime] NULL
) ON [PRIMARY]
GO
SET ANSI_PADDING ON
GO

CREATE NONCLUSTERED INDEX [IDX_DatabaseSize_DbName_5] ON [dbo].[DatabaseSize]
(
    [DbName] ASC
)
INCLUDE ([ServerName],
    [ScanDate],
    [FileName],
    [SizeMB],
    [UsedMB]) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO
SET ANSI_PADDING ON
GO

CREATE NONCLUSTERED INDEX [IDX_DatabaseSize_ServerName_5] ON [dbo].[DatabaseSize]
(
    [ServerName] ASC
)
INCLUDE ([ScanDate],
    [FileName],
    [DbName],
    [SizeMB],
    [UsedMB]
) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO

ALTER TABLE [dbo].[DatabaseSize] ADD CONSTRAINT [DF_DatabaseSize_Date] DEFAULT (getdate()) FOR [Date]
GO

ServerInfo

Create the table 'ServerInfo', this contains information about the server. Fill this daily.

ServicePackInfo

Create the table 'ServicePackInfo', update when a new SP or CU or SU or GDR or Hotfix is released.

Example content for table Drives for Server1 for two days:

Example content for table DatabaseSize for Server1 for two days:

Example content for table ServerInfo for Server1 for five days:

The info comes from sys.dm_os_sys_info.

Example content for table ServicePackInfo for 2017 and 2019:

See Examples for the reports created with these tables.

Ripat DBA

Contact