Hi All,
I have 2 tables. both tables hold db file sizes.
I want the difference of sizes using tsql. As of now, I am using excel for this.
During prod refresh activity to non-prod environments , we are falling short of drive spaces.
So, Checking for script to find size differences using tsql.
Providing sample data below:
CREATE TABLE [dbo].[prod_db_size](
[Srvname] [nvarchar](128) NULL,
[DbName] [nvarchar](128) NULL,
[FileName] [sysname] NOT NULL,
[physical_name] [nvarchar](260) NULL,
[CurrentSizeGB] [numeric](18, 2) NULL
) ON [PRIMARY]
GO
INSERT [dbo].[prod_db_size] ([Srvname], [DbName], [FileName], [physical_name], [CurrentSizeGB]) VALUES (N'PRODSRV', N'PRODDB', N'PRODDB_prm', N'G:\MSSQL\Data\PRODDB\PRODDB_prm.mdf', CAST(5.25 AS Numeric(18, 2)))
GO
INSERT [dbo].[prod_db_size] ([Srvname], [DbName], [FileName], [physical_name], [CurrentSizeGB]) VALUES (N'PRODSRV', N'PRODDB', N'PRODDB_log', N'K:\MSSQL\Log\PRODDB\PRODDB_log.ldf', CAST(283.44 AS Numeric(18, 2)))
GO
INSERT [dbo].[prod_db_size] ([Srvname], [DbName], [FileName], [physical_name], [CurrentSizeGB]) VALUES (N'PRODSRV', N'PRODDB', N'PRODDB_dat', N'G:\MSSQL\Data\PRODDB\PRODDB_dat.ndf', CAST(3559.67 AS Numeric(18, 2)))
GO
INSERT [dbo].[prod_db_size] ([Srvname], [DbName], [FileName], [physical_name], [CurrentSizeGB]) VALUES (N'PRODSRV', N'PRODDB', N'PRODDB_ind', N'G:\MSSQL\Data\PRODDB\PRODDB_ind.ndf', CAST(598.97 AS Numeric(18, 2)))
GO
----------------------------------
CREATE TABLE [dbo].[dev_db_size](
[Srvname] [nvarchar](128) NULL,
[DbName] [nvarchar](128) NULL,
[FileName] [sysname] NOT NULL,
[physical_name] [nvarchar](260) NULL,
[CurrentSizeGB] [numeric](18, 2) NULL
) ON [PRIMARY]
GO
INSERT [dbo].[dev_db_size] ([Srvname], [DbName], [FileName], [physical_name], [CurrentSizeGB]) VALUES (N'DEVSRV', N'DEVDB', N'DEVDB_prm', N'V:\MSSQL\Data\DEVDB\DEVDB_prm.mdf', CAST(5.25 AS Numeric(18, 2)))
GO
INSERT [dbo].[dev_db_size] ([Srvname], [DbName], [FileName], [physical_name], [CurrentSizeGB]) VALUES (N'DEVSRV', N'DEVDB', N'DEVDB_log', N'I:\MSSQL\Log\DEVDB\DEVDB_log.ldf', CAST(10.00 AS Numeric(18, 2)))
GO
INSERT [dbo].[dev_db_size] ([Srvname], [DbName], [FileName], [physical_name], [CurrentSizeGB]) VALUES (N'DEVSRV', N'DEVDB', N'DEVDB_dat', N'E:\MSSQL\Data\DEVDB\DEVDB_dat.ndf', CAST(3400.58 AS Numeric(18, 2)))
GO
INSERT [dbo].[dev_db_size] ([Srvname], [DbName], [FileName], [physical_name], [CurrentSizeGB]) VALUES (N'DEVSRV', N'DEVDB', N'DEVDB_ind', N'V:\MSSQL\Data\DEVDB\DEVDB_ind.ndf', CAST(571.52 AS Numeric(18, 2)))
GO
SELECT * FROM [prod_db_size]
SELECT * FROM [dev_db_size]
--expected output with new colum "size_diff"
SELECT *,size_diff FROM [dev_db_size]
Kind Regards,
Bob
November 21, 2022 at 9:46 am
Hi,
please post Code as Code should be postet.
It is quiet difficult to read.
USE msdb
GO
DBCC sqlperf (logspace)
This ist just am sample, how code should looks like.
Kind regards,
Andreas
SELECT d.*, p.CurrentSizeGB - d.CurrentSizeGB size_diff
FROM [dev_db_size] d
INNER JOIN [prod_db_size] p
ON RIGHT(p.FileName, 3) = RIGHT(d.FileName, 3);
November 21, 2022 at 3:22 pm
Thank you Jonathan.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply