June 30, 2015 at 12:24 am
Hi Every one ,
Hope all of you are doing well,
The problem that we are facing is that, for this particular table [2766_SalesDW].[Price][FactDealMetric]
When we are moving records from the source table to this table, due to the space constraint, it is overwriting the new records on the old records.
So, after a number of records are reached it starts overwriting existing records.
We identified since this table gets out of space after a certain number of records, the existing records get overwritten.
Thanks
June 30, 2015 at 2:55 am
SQL will not overwrite existing rows because of a table's size. Look at whatever is doing the insert into the table, from SQL's side, if it's told to do an insert it will do an insert and if the database is out of space you'll get an error.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
June 30, 2015 at 4:27 am
In addition to what Gail mentioned: SQL has no space restriction on the table level. The space is defined at database/filegroup level. When the autogrowth setting on the file(s) of the database/filegroup is off or when the disk is full the used space within the database can hit the max. size of the database/filegroup. This will generate an error like "Could not allocate space for object '{object_name}' in database '{database_name}' because the '{filegroup_name}' filegroup is full"
Which feature, tool or application are you using to insert the rows into the table?
June 30, 2015 at 6:51 am
Hi Everyone ,
Thanks for your quick response .
Here is the problem .
1) Using merge script while inserting data into the table (Sql server 2012).
2) After inserting a particular set of data which is based on Dateid column .
i am inserting another set of data i.e based on another Dateid .
3) Count of earlier set of data is getting reduced .
June 30, 2015 at 7:03 am
Check the script, see what it's doing. If there are fewer rows after than before, then something in the script you are running is deleting data. Probably your merge script has a section which deletes old rows.
SQL will not automatically remove data from a table.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
June 30, 2015 at 7:28 am
I Feel there is no problem with the code . below Approach i am using for other tables but
everything looks fine there .
Issue is only with one tables . below is the script
DECLARE @TableName AS VARCHAR(MAX),
@StartTime AS DATETIME,
@EndTime AS DATETIME,
@TotalImpactedRows AS INT,
@dateid int = 20130905
set @TableName = '[2766_SalesDW].[Price].[FactDealMetric]'
set @StartTime = getdate();
MERGE [2766_SalesDW].[Price].[FactDealMetric] AS FACTD
USING
(
SELECT
fact.dateid as Dateid,
deal.[Dealkey] as DEALkeyid
,fact.[METRICKEY]
,fact.amtvalues as [MetricValue]
,fact.[Percentage]
,deal.CreatedDttm
,deal.CreatedBy
,deal.UpdatedBy
,deal.UpdatedDttm
FROM [2766_SalesDW].[Price].[vwFactDealMetricAmtPercentage] fact with (nolock)
inner join [2766_SalesDW].[Price].[FactDeal] deal with (nolock) on
fact.dateid = deal.datekey
and fact.[Dealid] = deal.DealKey
where deal.Datekey = @dateid
) AS vwFactDeal
ON factd.METRICKEY = vwFactDeal.METRICKEY and factd.dealkey = vwFactDeal.dealkeyid
/* Updating FactDealMetric table for existing Data based on Dealkey,Metrickey */
WHEN MATCHED THEN UPDATE
SET
FACTD.DAtekey = vwFactDeal.Dateid,
FACTD.DEALKEY = vwFactDeal.[Dealkeyid],
FACTD.METRICKEY = vwFactDeal.METRICKEY,
FACTD.METRICVALUE = vwFactDeal.MetricValue,
FACTD.PERCENTAGE = vwFactDeal.Percentage,
FACTD.CreatedDttm = vwFactDeal.CreatedDttm,
FACTD.CreatedBy = vwFactDeal.CreatedBy,
FACTD.UpdatedBy = vwFactDeal.UpdatedBy,
FACTD.UpdatedDttm = vwFactDeal.UpdatedDttm
/* Inserting Data in FactDealMetric table for non existing */
WHEN NOT MATCHED THEN
INSERT
(DAtekey,DEALKEY,METRICKEY,METRICVALUE,PERCENTAGE,CreatedDttm,CreatedBy,UpdatedBy,UpdatedDttm)
values
(vwFactDeal.dateid,vwFactDeal.DEALkeyid,vwFactDeal.Metrickey,vwFactDeal.MetricValue,vwFactDeal.Percentage,
vwFactDeal.CreatedDttm,vwFactDeal.CreatedBy,vwFactDeal.UpdatedBy,vwFactDeal.UpdatedDttm);
set @TotalImpactedRows = @@rowcount
set @EndTime = getdate()
insert into [2766_SalesDW].Price.ConversionLog ([Datekey],[Table Name],[StartTime],[EndTime],[Execution time(HH/MM/SS)],[TotalImpactedRows],SourceCount)
select @dateid as Datekey , @TableName as [Table Name] ,@StartTime as [Start Time],@EndTime as [End Time] ,
substring(cast(CAST(@EndTime-@StartTime as TIME) as varchar),1,8) as [Execution time(HH/MM/SS)],
@TotalImpactedRows as [Impacted Records], 'N/A'
June 30, 2015 at 7:39 am
Then there's a delete running somewhere else, from a job or another user or something.
SQL will not delete data by itself. If you have rows disappearing, someone or some job is running a delete.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
July 1, 2015 at 12:59 pm
Strange thing is there is no such issue ..
no jobs are running which are deleting data
July 1, 2015 at 1:46 pm
Whether a MERGE performs an INSERT, UPDATE, or DELETE depends on the logic you have coded in the MATCHED clause.
Based on the code you posted earlier, it hinges on this:
".. ON factd.METRICKEY = vwFactDeal.METRICKEY
and factd.dealkey = vwFactDeal.dealkeyid .."
Probably what's going on is that the MERGE statement is performing an UPDATE due to previously inserted keys, rather than performing the INSERT you anticipated.
Also, the source of your MERGE is a view called vwFactDealMetricAmtPercentage for which we don't know the SELECT statement. Consider how the previous insertion of rows into the target table affects what rows are subsequently returned from this view.
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho
July 2, 2015 at 2:13 pm
Hi Eric M Russell,
I think you are right and i feel i got the answer ,
Let me incorporate the changes Thanks a lot buddy,
Next I will be posting issue on Performance of views and need all of yours valuable inputs
Thanks everyone .. Cheers 😉
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply