March 11, 2020 at 4:42 am
DECLARE
@FROMDATE SMALLDATETIME,
@TODATE SMALLDATETIME,
@SERVER_NAME NVARCHAR(150)
SELECT a.ServerName,a.DatabaseName,a.DatabaseSize,b.DatabaseSize,a.TrackDate as Todate_Size,a.TotalSize,b.TrackDate as FromDate_Size ,b.TotalSize,
(a.TotalSize-b.TotalSize) AS GrowthSizeDiff
FROM
(SELECT ServerName, DatabaseName , DatabaseSize , LogSize , TotalSize , TrackDate =(CONVERT (DATE, TrackDate, 103))
FROM Database_Size_Tracking WHERE servername = @SERVER_NAME
AND (CONVERT (DATE, TrackDate, 103)) =(CONVERT (DATE, @TODATE, 103))) A,
(SELECT ServerName, DatabaseName , DatabaseSize , LogSize , TotalSize , TrackDate =(CONVERT (DATE, TrackDate, 103))
FROM Database_Size_Tracking WHERE servername =@SERVER_NAME
AND (CONVERT (DATE, TrackDate, 103)) =(CONVERT (DATE, @FROMDATE, 103))) B
WHERE a.DatabaseName=b.DatabaseName AND a.ServerName=b.ServerName
exec [dbo].[SP_DATAGROWTH_ANANLYSIS] '10-03-2020','02-03-2020','DFGPUXESTMN'
Msg 8146, Level 16, State 2, Procedure SP_DATAGROWTH_ANANLYSIS, Line 0 [Batch Start Line 33]
Procedure SP_DATAGROWTH_ANANLYSIS has no parameters and arguments were supplied.
could anyone suggest me what is the wrong in SP? but Query executing fine.
March 11, 2020 at 5:09 am
Where's your CREATE PROCEDURE statement? Sounds like you're trying to create a stored procedure that has a few parameters...
Where's your CREATE PROCEDURE statement? Sounds like you're trying to create a stored procedure that has a few parameters...
CREATE PROCEDURE MyProcedure
@FROMDATE SMALLDATETIME,
@TODATE SMALLDATETIME,
@SERVER_NAME NVARCHAR(150)
AS
SELECT a.ServerName,a.DatabaseName,a.DatabaseSize,b.DatabaseSize,a.TrackDate as Todate_Size,a.TotalSize,b.TrackDate as FromDate_Size ,b.TotalSize,
(a.TotalSize-b.TotalSize) AS GrowthSizeDiff
FROM
(SELECT ServerName, DatabaseName , DatabaseSize , LogSize , TotalSize , TrackDate =(CONVERT (DATE, TrackDate, 103))
FROM Database_Size_Tracking WHERE servername = @SERVER_NAME
AND (CONVERT (DATE, TrackDate, 103)) =(CONVERT (DATE, @TODATE, 103))) A,
(SELECT ServerName, DatabaseName , DatabaseSize , LogSize , TotalSize , TrackDate =(CONVERT (DATE, TrackDate, 103))
FROM Database_Size_Tracking WHERE servername =@SERVER_NAME
AND (CONVERT (DATE, TrackDate, 103)) =(CONVERT (DATE, @FROMDATE, 103))) B
WHERE a.DatabaseName=b.DatabaseName AND a.ServerName=b.ServerName;
March 11, 2020 at 12:14 pm
even better - put some default values against the parameters just in case someone doesn't know to pass a parameter.. but get it to log those executions
MVDBA
March 12, 2020 at 5:44 am
procedure has been created and execute that with parameter like but no values are return and history table Database_Size_Tracking records are avilable.
Exec SP_DATAGROWTH_ANANLYSIS '02-03-2020','11-03-2020','PUXKPIREP'
CREATE PROCEDURE [dbo].[SP_DATAGROWTH_ANANLYSIS]
(
@FROMDATE DATETIME,
@TODATE DATETIME,
@SERVER_NAME NVARCHAR(150)
)
as begin
SELECT a.ServerName,a.DatabaseName,a.DatabaseSize,b.DatabaseSize,a.TrackDate as TO_DATE,a.TotalSize as TO_DATE_TOTALSIZE,b.TrackDate as FROM_DATE ,b.TotalSize as FROM_DATE_TOTALSIZE,
(a.TotalSize-b.TotalSize) AS GrowthSizeDiff_MB
FROM
(SELECT ServerName, DatabaseName , DatabaseSize , LogSize , TotalSize , TrackDate =(CONVERT (DATE, TrackDate, 103))
FROM MIIS_Database_Size_Tracking WHERE servername = @SERVER_NAME
AND (CONVERT (DATE, TrackDate, 103)) =(CONVERT (DATE, @TODATE, 103))) A,
(SELECT ServerName, DatabaseName , DatabaseSize , LogSize , TotalSize , TrackDate =(CONVERT (DATE, TrackDate, 103))
FROM MIIS_Database_Size_Tracking WHERE servername =@SERVER_NAME
AND (CONVERT (DATE, TrackDate, 103)) =(CONVERT (DATE, @FROMDATE, 103))) B
WHERE a.DatabaseName=b.DatabaseName AND a.ServerName=b.ServerName
end
GO
March 12, 2020 at 6:13 am
Not sure what's wrong. I get what you're doing... Get the "start date" and "end date" sizes for each database on a given server, subtract, and get the growth.
Can you post some data (munge the server names and database names if you want. The names aren't important.)
March 12, 2020 at 7:18 am
SELECT a.ServerName,a.DatabaseName,a.DatabaseSize,b.DatabaseSize,a.TrackDate as TO_DATE,a.TotalSize as TO_DATE_TOTALSIZE,b.TrackDate as FROM_DATE ,b.TotalSize as FROM_DATE_TOTALSIZE,
(a.TotalSize-b.TotalSize) AS GrowthSizeDiff_MB
FROM
(SELECT ServerName, DatabaseName , DatabaseSize , LogSize , TotalSize , TrackDate =(CONVERT (DATE, TrackDate, 103))
FROM Database_Size_Tracking WHERE servername ='PUXKPIREP'
AND (CONVERT (DATE, TrackDate, 103)) =(CONVERT (DATE, '12-03-2020', 103))) A,
(SELECT ServerName, DatabaseName , DatabaseSize , LogSize , TotalSize , TrackDate =(CONVERT (DATE, TrackDate, 103))
FROM Database_Size_Tracking WHERE servername ='PUXKPIREP'
AND (CONVERT (DATE, TrackDate, 103)) =(CONVERT (DATE, '03-03-2020', 103))) B
WHERE a.DatabaseName=b.DatabaseName AND a.ServerName=b.ServerName
ServerName DatabaseName DatabaseSize DatabaseSize TO_DATE TO_DATE_TOTALSIZE FROM_DATE FROM_DATE_TOTALSIZE GrowthSizeDiff_MB
PUXKPIREP CRIFCORE 7432.000 7255.000 2020-03-12 9985.563 2020-03-03 9173.438 812.125
PUXKPIREP IXSAP 316.000 316.000 2020-03-12 436.750 2020-03-03 436.750 0.000
PUXKPIREP IXSSRSDB 41.000 41.000 2020-03-12 5242.000 2020-03-03 4092.000 1150.000
PUXKPIREP ManufacturingIntelligence Application Data 12936.000 12936.000 2020-03-12 23184.000 2020-03-03 23184.000 0.000
PUXKPIREP ReportServer 72.000 72.000 2020-03-12 144.000 2020-03-03 144.000 0.000
PUXKPIREP ReportServerTempDB 8.000 8.000 2020-03-12 16.000 2020-03-03 16.000 0.000
Base table data
select ServerName, DatabaseName , DatabaseSize , LogSize , TotalSize , TrackDate =(CONVERT (date, trackdate, 103))
from Database_Size_Tracking where servername ='PUXESTMN'
and TrackDate between '2020-03-02' and '2020-03-04'
ServerName DatabaseName DatabaseSize LogSize TotalSize TrackDate
PUXESTMN CRIFCORE 17666.000 6623.750 24289.750 2020-03-02
PUXESTMN Honeywell.Intuition.Base 456.000 72.000 528.000 2020-03-02
PUXESTMN Honeywell.MES.AssetTask.DataModel.AssetTaskDataModel 72.000 72.000 144.000 2020-03-02
PUXESTMN Honeywell.MES.EventProcessor.DataModel.EventProcessorModel 8.000 8.000 16.000 2020-03-02
PUXESTMN Honeywell.MES.OLEDBPlugin.DataModel.OLEDBPluginModel 8.000 8.000 16.000 2020-03-02
PUXESTMN Honeywell.MES.Operations.DataModel.OperationsDB 328.000 5064.000 5392.000 2020-03-02
PUXESTMN Honeywell.MES.Shift.DataModel.ShiftModel 8.000 72.000 80.000 2020-03-02
PUXESTMN InventoryInterface 72.000 8.000 80.000 2020-03-02
PUXESTMN IXSAP 102884.375 4835.938 107720.313 2020-03-02
PUXESTMN IXSAP_TEST 23656.813 3.883 23660.695 2020-03-02
PUXESTMN CRIFCORE 17692.000 6623.750 24315.750 2020-03-03
PUXESTMN Honeywell.Intuition.Base 456.000 72.000 528.000 2020-03-03
PUXESTMN Honeywell.MES.AssetTask.DataModel.AssetTaskDataModel 72.000 72.000 144.000 2020-03-03
PUXESTMN Honeywell.MES.EventProcessor.DataModel.EventProcessorModel 8.000 8.000 16.000 2020-03-03
PUXESTMN Honeywell.MES.OLEDBPlugin.DataModel.OLEDBPluginModel 8.000 8.000 16.000 2020-03-03
PUXESTMN Honeywell.MES.Operations.DataModel.OperationsDB 328.000 5064.000 5392.000 2020-03-03
PUXESTMN Honeywell.MES.Shift.DataModel.ShiftModel 8.000 72.000 80.000 2020-03-03
PUXESTMN InventoryInterface 72.000 8.000 80.000 2020-03-03
PUXESTMN IXSAP 102884.375 4835.938 107720.313 2020-03-03
PUXESTMN IXSAP_TEST 23656.813 3.883 23660.695 2020-03-03
Hi posted some sample records.
March 12, 2020 at 6:45 pm
What if you modified the search to be something like
WHERE [TrackDate] > @SomeDateTime1 AND [TrackDate] <= DATEADD(day,1,@SomeDateTime1)
OR [TrackDate] > @SomeDateTime2 AND [TrackDate] <= DATEADD(day,1,@SomeDateTime2)
Then wouldn't you get only 2 records? Then you could just use LEAD/LAG to refer to the "other" record, and do the math that way. because you're basically comparing two points in time, getting the size for each, and then doing a little math. I think Lynn Pettis has an article on here somewhere about useful date functions... if you used that I think you'd be in business.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply