October 8, 2009 at 1:01 pm
Hello all,
I am part of a team that is working on a system migration from one system to another. The one piece of the migration involves moving data from one sql db to another. We are using SSIS for the data migration.
For testing purposes there is a need to constantly remove the data from the destination system and reload it from the source system. It is roughly 65-70 (about 40 colums) million records. We have a stored procedure which performs the delete. It's you basic delete from statement. The issue we are running into is that the constant deletion and insertion keeps filling up the transaction log. I believe our DBAs clear it and we have the issue again eventually. The DBAs recommended doing 4 or 5 smaller deletions versus one big one which he says uses more log space.
We put in a fix that involves doing it at the SSIS level using the OLE DB Command which of course deletes the records individually as they go through the buffer. But this is really really slow.
Now I'm no DBA, I'm a programmer, but I just find it hard to believe that the DBA's suggestion is the solution to the problem. I'm sure there are companies out there that delete/insert more than 75 million records constantly and don't have this problem. Does his solution make sense to any of you? If not, what do you think is the best solution to this problem?
I'm also including the error message as well. See below:
Could not allocate space for object 'dbo.CLAIM_DETAIL'.'idx_CLAIM_DETAIL_7' in database 'Claim' because the 'PRIMARY' filegroup is full.
System.Reflection.TargetInvocationException: Exception has been thrown by the target of an invocation. ---> System.Data.SqlClient.SqlException: Could not allocate space for object 'dbo.CLAIM_DETAIL'.'idx_CLAIM_DETAIL_7' in database 'Claim' because the 'PRIMARY' filegroup is full. Create disk space by deleting unneeded files, dropping objects in the filegroup, adding additional files to the filegroup, or setting autogrowth on for existing files in the filegroup. at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection) at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection) at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj) at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj) at System.Data.SqlClient.SqlBulkCopy.WriteToServerInternal() at System.Data.SqlClient.SqlBulkCopy.WriteRowSourceToServer(Int32 columnCount) at System.Data.SqlClient.SqlBulkCopy.WriteToServer(IDataReader reader) at ST_5ac526b9a72549848404d935762da591.vbproj.ScriptMain.Main() --- End of inner exception stack trace --- at System.RuntimeMethodHandle._InvokeMethodFast(Object target, Object[] arguments, SignatureStruct& sig, MethodAttributes methodAttributes, RuntimeTypeHandle typeOwner) at System.RuntimeMethodHandle.InvokeMethodFast(Object target, Object[] arguments, Signature sig, MethodAttributes methodAttributes, RuntimeTypeHandle typeOwner) at System.Reflection.RuntimeMethodInfo.Invoke(Object obj, BindingFlags invokeAttr, Binder binder, Object[] parameters, CultureInfo culture, Boolean skipVisibilityChecks) at System.Reflection.RuntimeMethodInfo.Invoke(Object obj, BindingFlags invokeAttr, Binder binder, Object[] parameters, CultureInfo culture) at System.RuntimeType.InvokeMember(String name, BindingFlags bindingFlags, Binder binder, O
Thanks,
Strick
October 8, 2009 at 1:17 pm
I would just truncate the table.
October 8, 2009 at 1:18 pm
This does not look like the transaction log is full. It seems like the your Data file is full. Did you run out of Hard disk space or is there a CAP set for the data file to grow?
This statement
Could not allocate space for object 'dbo.CLAIM_DETAIL'.'idx_CLAIM_DETAIL_7' in database 'Claim' because the 'PRIMARY' filegroup is full
It is the Index that is having issues.
-Roy
October 8, 2009 at 1:23 pm
stricknyn (10/8/2009)
Could not allocate space for object 'dbo.CLAIM_DETAIL'.'idx_CLAIM_DETAIL_7' in database 'Claim' because the 'PRIMARY' filegroup is full.
That's not a log full error. That's saying that the primary data file is full. Either it's filled the drive or it's set not to autogrow. Increase the size of the primary data file and you should be fine.
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
October 8, 2009 at 2:26 pm
GoofyKC (10/8/2009)
I would just truncate the table.
Truncating will delete all the records. We can't delete every record. The delete statement has a where clause.
October 8, 2009 at 2:33 pm
Roy Ernest (10/8/2009)
This does not look like the transaction log is full. It seems like the your Data file is full. Did you run out of Hard disk space or is there a CAP set for the data file to grow?This statement
Could not allocate space for object 'dbo.CLAIM_DETAIL'.'idx_CLAIM_DETAIL_7' in database 'Claim' because the 'PRIMARY' filegroup is full
It is the Index that is having issues.
Very interesting. Now I have to figure out a way to tell the DBA he's wrong without stepping on his toes...
October 8, 2009 at 2:33 pm
I hope you read Gails and my statement. That is where your solution is right now.
-Roy
October 8, 2009 at 2:37 pm
Roy Ernest (10/8/2009)
I hope you read Gails and my statement. That is where your solution is right now.
Yeah, both statements are what I'm going to communicate to our DBA. Thanks for your help!
Strick
October 8, 2009 at 2:42 pm
stricknyn (10/8/2009)
Very interesting. Now I have to figure out a way to tell the DBA he's wrong without stepping on his toes...
"Sorry to bother you, I just noticed this error we're having refers to the Primary Filegroup. I didn't think logs were part of filegroups. Do you perhaps know what it means?'
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
October 8, 2009 at 2:44 pm
Gail, that is a politically correct way to confront the DBA... 🙂 My hats off to you. 😀
-Roy
October 8, 2009 at 2:56 pm
Roy Ernest (10/8/2009)
Gail, that is a politically correct way to confront the DBA... 🙂 My hats off to you. 😀
I used to be a developer remember. A developer that knew SQL better than anyone on the DBA team.
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
October 8, 2009 at 4:47 pm
One question I haven't seen asked in this post is "Are the log and data files on the same drive?". If they are, then in theory the log could be filling up the drive to the point that the data files can no longer auto-grow and it really is a log problem and not a data file problem. If that's the case, then I'd have to look at the data recovery model, frequency of the log backups etc...
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply