uspLoadDiskInfo: Save disk info to the database

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'dbo.uspLoadDiskInfo') AND type in (N'P', N'PC'))
DROP PROCEDURE dbo.uspLoadDiskInfo
GO

--------------------------------------------------------------------------------
-- =============================================
-- Author: Simon You
-- Create date: Feb 2018
-- Description:
-- =============================================
create procedure dbo.uspLoadDiskInfo
as
begin
set nocount on;

declare @result int = 0
declare @Date datetime = GETDATE()
declare
@StoHistoryHeaderId StoHistoryHeaderId

declare @DiskInfo table(
Drive Drive,
DriveName Name,
TotalSpaceInMB FileSize,
FreeSpaceInMB FileSize
)

INSERT INTO @DiskInfo(Drive, DriveName, TotalSpaceInMB, FreeSpaceInMB)
SELECT DISTINCT
Drive = LEFT(dovs.volume_mount_point, 1),
DriveName = dovs.logical_volume_name,
TotalSpaceInMB = dovs.total_bytes / 1048576.0,
FreeSpaceInMB = dovs.available_bytes / 1048576.0
FROM sys.master_files mf
CROSS APPLY sys.dm_os_volume_stats(mf.database_id, mf.FILE_ID) dovs

declare @DbDriveMap table(
DBName varchar(100),
Drive varchar(10)
)
INSERT INTO @DBDriveMap(DBName, Drive)
SELECT
DBName = DB_NAME(mf.database_id),
left(dovs.volume_mount_point, 1) AS Drive
FROM sys.master_files mf
CROSS APPLY sys.dm_os_volume_stats(mf.database_id, mf.FILE_ID) dovs
where mf.[type] = 0
and mf.file_id = 1

DECLARE @DbSpace table(
DBName varchar(100) ,

DataSizeMB float,
LogSizeMB float,
UnallocatedSpaceMB float,

ReservedKB int,
DataKB int,
IndexSizeKB int,
UnusedKB int
)

INSERT INTO  @DbSpace
EXECUTE sys.sp_MSforeachdb 'USE [?];
select
DBName = DB_NAME(),

DataSizeMB = CAST(sf.DbSize / 128.0 AS numeric(20, 2)),
LogSizeMB = CAST(sf.LogSize / 128.0 AS numeric(20, 2)),
UnallocatedSpaceMB = CAST((CASE WHEN sf.DbSize >= pg.ReservedPages THEN (sf.DbSize - pg.ReservedPages) / 128.0 ELSE 0 END) AS numeric(20, 2)),

ReservedKB = pg.ReservedPages * 8,
DataKB = pg.Pages * 8,
IndexSizeKB = (pg.UsedPages - pg.Pages) * 8,
UnusedKB = (pg.ReservedPages - pg.UsedPages) * 8
FROM
(
select
DbSize = SUM(CAST(case when status & 64 = 0 then size else 0 end AS bigint)),
LogSize = SUM(CAST(case when status & 64 <> 0 then size else 0 end AS bigint))
from dbo.sysfiles
) sf,
(
select
ReservedPages = SUM(a.total_pages),
UsedPages = SUM(a.used_pages),
Pages = SUM(CASE
When it.internal_type IN (202,204,211,212,213,214,215,216) Then 0
When a.type <> 1 Then a.used_pages
When p.index_id < 2 Then a.data_pages
Else 0
END
)
from sys.partitions p
join sys.allocation_units a on p.partition_id = a.container_id
left join sys.internal_tables it on p.object_id = it.object_id
) pg
'

MERGE dbo.StoDrive AS tgt
USING (select top 100 PERCENT * from @DiskInfo order by Drive) AS src
ON tgt.Drive = src.Drive and tgt.IsActive = 1
WHEN MATCHED THEN
UPDATE SET TotalSpaceInMB = src.TotalSpaceInMB
WHEN NOT MATCHED BY TARGET THEN
INSERT (Drive, DriveName, TotalSpaceInMB) VALUES (Drive, DriveName, TotalSpaceInMB);

MERGE dbo.StoDb AS tgt
USING (select top 100 PERCENT DBName from @DbDriveMap order by DBName) AS src
ON tgt.DBName = src.DBName and tgt.IsActive = 1
WHEN NOT MATCHED BY TARGET THEN
INSERT (DBName) VALUES (DBName);

MERGE dbo.StoDriveDBMap AS tgt
USING (select StoDrive.StoDriveId,  StoDb.StoDbId
from @DBDriveMap map, dbo.StoDb, dbo.StoDrive
where map.DBName = StoDb.DBName and StoDb.IsActive = 1
and map.Drive = StoDrive.Drive and StoDrive.IsActive = 1
) AS src
ON tgt.StoDriveId = src.StoDriveId and tgt.StoDbId = src.StoDbId and tgt.IsActive = 1
WHEN NOT MATCHED BY TARGET THEN
INSERT (StoDriveId, StoDbId) VALUES (StoDriveId, StoDbId);

BEGIN TRY
begin transaction

INSERT INTO dbo.StoHistoryHeader(RunDate) values (@Date)
SELECT @StoHistoryHeaderId = SCOPE_IDENTITY();

INSERT INTO dbo.StoHistoryDriveDetail(StoHistoryHeaderId, StoDriveId, FreeSpaceInMB)
SELECT
@StoHistoryHeaderId,
sd.StoDriveId,
di.FreeSpaceInMB
from @DiskInfo di
join StoDrive sd on di.Drive = sd.Drive and sd.IsActive = 1

INSERT INTO dbo.StoHistoryDbDetail(StoHistoryHeaderId, StoDbId, DBSizeMB, LogSizeMB, UnallocatedSpaceMB, ReservedKB, DataKB, IndexSizeKB, UnusedKB)
select
@StoHistoryHeaderId,
db.StoDbId,
sd.DataSizeMB,
sd.LogSizeMB,
sd.UnallocatedSpaceMB,
sd.ReservedKB,
sd.DataKB,
sd.IndexSizeKB,
sd.UnusedKB
from @DbSpace sd
join StoDB db on sd.DBName = db.DBName and db.IsActive = 1

commit transaction
END TRY
BEGIN CATCH
declare @ErrorMessage nvarchar(max), @ErrorSeverity int, @ErrorState int;
    select
@ErrorMessage = ERROR_MESSAGE(), --+ ' Line ' + cast(ERROR_LINE() as nvarchar(5)),
@ErrorSeverity = ERROR_SEVERITY(),
@ErrorState = ERROR_STATE();

if @@TRANCOUNT > 0
begin
rollback transaction
end

    raiserror (@ErrorMessage, @ErrorSeverity, @ErrorState);
set @result = 1
END CATCH

return @result
end
--------------------------------------------------------------------------------
GO

--exec uspLoadDiskInfo

/*
select * from StoHistoryHeader
select * from StoHistoryDriveDetail
select * from StoHistoryDbDetail order by stoHistoryHeaderId desc
*/