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
ServerNameDatabaseNameDatabaseSizeDatabaseSizeTO_DATETO_DATE_TOTALSIZEFROM_DATEFROM_DATE_TOTALSIZEGrowthSizeDiff_MB
PUXKPIREPCRIFCORE7432.0007255.0002020-03-129985.5632020-03-039173.438812.125
PUXKPIREPIXSAP316.000316.0002020-03-12436.7502020-03-03436.7500.000
PUXKPIREPIXSSRSDB41.00041.0002020-03-125242.0002020-03-034092.0001150.000
PUXKPIREPManufacturingIntelligence Application Data12936.00012936.0002020-03-1223184.0002020-03-0323184.0000.000
PUXKPIREPReportServer72.00072.0002020-03-12144.0002020-03-03144.0000.000
PUXKPIREPReportServerTempDB8.0008.0002020-03-1216.0002020-03-0316.0000.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'
ServerNameDatabaseNameDatabaseSizeLogSizeTotalSizeTrackDate
PUXESTMNCRIFCORE17666.0006623.75024289.7502020-03-02
PUXESTMNHoneywell.Intuition.Base456.00072.000528.0002020-03-02
PUXESTMNHoneywell.MES.AssetTask.DataModel.AssetTaskDataModel72.00072.000144.0002020-03-02
PUXESTMNHoneywell.MES.EventProcessor.DataModel.EventProcessorModel8.0008.00016.0002020-03-02
PUXESTMNHoneywell.MES.OLEDBPlugin.DataModel.OLEDBPluginModel8.0008.00016.0002020-03-02
PUXESTMNHoneywell.MES.Operations.DataModel.OperationsDB328.0005064.0005392.0002020-03-02
PUXESTMNHoneywell.MES.Shift.DataModel.ShiftModel8.00072.00080.0002020-03-02
PUXESTMNInventoryInterface72.0008.00080.0002020-03-02
PUXESTMNIXSAP102884.3754835.938107720.3132020-03-02
PUXESTMNIXSAP_TEST23656.8133.88323660.6952020-03-02
PUXESTMNCRIFCORE17692.0006623.75024315.7502020-03-03
PUXESTMNHoneywell.Intuition.Base456.00072.000528.0002020-03-03
PUXESTMNHoneywell.MES.AssetTask.DataModel.AssetTaskDataModel72.00072.000144.0002020-03-03
PUXESTMNHoneywell.MES.EventProcessor.DataModel.EventProcessorModel8.0008.00016.0002020-03-03
PUXESTMNHoneywell.MES.OLEDBPlugin.DataModel.OLEDBPluginModel8.0008.00016.0002020-03-03
PUXESTMNHoneywell.MES.Operations.DataModel.OperationsDB328.0005064.0005392.0002020-03-03
PUXESTMNHoneywell.MES.Shift.DataModel.ShiftModel8.00072.00080.0002020-03-03
PUXESTMNInventoryInterface72.0008.00080.0002020-03-03
PUXESTMNIXSAP102884.3754835.938107720.3132020-03-03
PUXESTMNIXSAP_TEST23656.8133.88323660.6952020-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