August 5, 2009 at 12:02 am
Dear Fellas,
I have to insert more than 4000 record (approximately no. of insertion statements goes up to 10,000) in a single transaction using UNION ALL statement. But the problem is during the insertion process there are other transaction going on that database and also on the Same table.
When the transaction starts of 4000+ record the error shows after approx 2000 row insertion. The error is Transaction can not be complete because of insufficient memory.
I use an insert statement as following insert statement :
INSERT INTO [dbo].[XYZ_TABLE] (ID,NAME_)
SELECT 1,'A'
UNION ALL
SELECT 2,'B'
What i want to delete the T-Log . But if i do so then i cannot recover the database if something happens to the other Transactions. I have complete recovery model db.
Suggest something what should be a best way to do that??
Regards,
Sashikanta Mishra!!
August 5, 2009 at 1:12 am
Hello Sashikanta,
Will be very helpfull if you put the complete error message you got.
You are mentioning the following error : "Transaction can not be complete because of insufficient memory." but after also you mention that you want to delete the T-LOG. Why did you mention this?... it is because also you have a log full?
I see this choices:
1. First, check the memory of the server, because y
1 Choice: You do not mention nothing about your backup log, so, an option is, before you start to copy of the 4000 rows, you can perform a backup log, then immediatly start with the copy.
2 Choice: Change the recovery model to Bulk-logged, change your select as following
SELECT 1,'A'
INTO TBL_TEMP
UNION ALL
SELECT 2,'B'
INSERT INTO [dbo].[XYZ_TABLE] (ID,NAME_)
select * from TBL_TEMP
when SQL Server is in RM - bulk logged , it logs in a minimum way the bulk opeations, SELECT INTO is a bulk operation so, it will be minimum logged whiel the first insert is running. Just remember that in case of crash, you can recover your database until the end of the bulk activity.
3. Choice: also I think, that perhaps the error of insuficient memory is because the SELECT with UNION is a heavy task for your client where the sleect is executed. So you can use temporary table and then a insert for the target table, instead of insert select union.
Regards,
Victor Alvarez
http://sqlpost.blogspot.com
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply