| Author |
Topic |
|
WJHamel
Aged Yak Warrior
651 Posts |
 Posted - 2011-11-03 : 17:09:39
|
| Using sql server 2008R2, developer edition, all the latest available updates, patches, and hotfixes i could find. Hardware is 3.4 ghz I7-2600 CPU with 8GB RAM. Without fail, on a daily basis, when either trying to run, parse, or even save large queries, i get "cannot....", "because of insufficient available memory". Rarely is more than an excel sheet, outlook, and Toad for Sql Server ever running concurrently. Just curious if anyone is aware of any other hotfixes that are out in the wild which might be addressing any known memory leaks with SQL2k8R2.ThanksJames |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
WJHamel
Aged Yak Warrior
651 Posts |
Posted - 2011-11-04 : 09:01:31
|
| I'm not sure. How would i check this? The queries that typically crash as in excess of 800,000 lines. They are large INSERT/SELECT style queries, typically with robust Case Expressions embedded. |
 |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
WJHamel
Aged Yak Warrior
651 Posts |
Posted - 2011-11-07 : 16:06:53
|
| Microsoft SQL Server 2008 R2 (RTM) - 10.50.1600.1 (X64) Apr 2 2010 15:48:46 Copyright (c) Microsoft Corporation Developer Edition (64-bit) on Windows NT 6.1 <X64> (Build 7601: Service Pack 1)And from my system report:Intel Core i7-2600 CPU @3.40GHz8.00 GB RAM (7.88 GB Usable)64-bit Operating SystemWindows 7 EnterpriseService Pack 1 |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
Transact Charlie
Master Smack Fu Yak Hacker
3451 Posts |
Posted - 2011-11-07 : 16:26:38
|
quote:
Originally posted by WJHamel
I'm not sure. How would i check this? The queries that typically crash as in excess of 800,000 lines. They are large INSERT/SELECT style queries, typically with robust Case Expressions embedded.
Batch them. If you have a massive 800,000 line batch then.......Just split this into multiple batches (maybe 500 insert statements at a time.)Charlie===============================================================Msg 3903, Level 16, State 1, Line 1736The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION |
|
|
|
WJHamel
Aged Yak Warrior
651 Posts |
Posted - 2011-11-08 : 09:24:50
|
| Tara - Done. thanks. We'll see if that helps. In the meantime, i've already taken to batching these large scripts out to 25% of their bulk size. That seems to be the workaround for now. |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2011-11-08 : 12:14:27
|
| Can you just stick "GO" in every 500 lines, or so (as part of your export from Excel)? That's what we do |
|
|
|
WJHamel
Aged Yak Warrior
651 Posts |
Posted - 2011-11-08 : 12:45:18
|
| I've done that, but still get the crashes before the script is done. I should also add, that, with batching these out to smaller scripts, i still have to shut down and restart Mgmt studio after running two scripts @25% size or by the 3rd or 4th script, i will get the same crashes. This is not specific to mgmt studio per se, as i get the same error when i execute my scripts throught Toad or any other 3rd party app. I've not executed any of these monster scripts since implementing Tara's suggestion. I probably won't get a chance to for a week or more now as my current project is done and waiting for first review. |
|
|
|
Transact Charlie
Master Smack Fu Yak Hacker
3451 Posts |
Posted - 2011-11-08 : 13:45:40
|
| bulk insert or BCP is probably the way to go here in the long term. Rather than running a gazillion insert statements.It's a bit more work but it will make this problem go away.Charlie===============================================================Msg 3903, Level 16, State 1, Line 1736The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION |
|
|
|
Sachin.Nand
2937 Posts |
Posted - 2011-11-08 : 22:56:02
|
quote:
Originally posted by Transact Charlie
bulk insert or BCP is probably the way to go here in the long term. Rather than running a gazillion insert statements.It's a bit more work but it will make this problem go away.Charlie===============================================================Msg 3903, Level 16, State 1, Line 1736The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION
Totally agree with TC on this.We to had the same issues when we used to migrate the data from prod to QA or development enviroment.We had this third part tool which used to generate individual huge Insert statement for no of rows in the table and not to mention the huge .sql files somtimes in excess of 700 MBWe then started exporting the data using BCP to csv(tab delimited) and then import it using BULK INSERT.It was so quick,easy and manageable.PBUH |
|
|
|
WJHamel
Aged Yak Warrior
651 Posts |
Posted - 2011-11-09 : 06:58:13
|
| Ugh, that is EXACTLY where i am at this time. I've been using RazorSQL to generate the inserts from Excel table exports of the client's data. I've seen other DBA's using the bcp process but went the quick and dirty route myself instead, avoiding learning the bcp process. It now appears it's time to throw myself into that direction as well. My biggest anxiety about it is, in these conversions, i usually have to write a number of case expressions, that i build in the syntax of the excel table, that then export to the SQL statement. How easy/difficult is it to customize the bcp statement to accomodate that kind of shuffling of the original data? Also, what learning resources do you recommend i look at to make this transition?thanksJames |
|
|
|
Sachin.Nand
2937 Posts |
Posted - 2011-11-09 : 07:39:13
|
Just mention the query statement in the bcp parameter.Something like thisBCP "Your query" queryout FilepathName -c -U username -P password -S servername PBUH |
|
|
|
WJHamel
Aged Yak Warrior
651 Posts |
Posted - 2011-11-09 : 07:47:39
|
| Oh. So the BCP statement can pull from multiple other queries? |
|
|
|
Sachin.Nand
2937 Posts |
Posted - 2011-11-09 : 11:27:37
|
Yes it can.Something like thisbcp "select * from table1 t1 inner join table2 t2 on t1.id=t2.id" queryout..... PBUH |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2011-11-09 : 14:17:49
|
" i usually have to write a number of case expressions, that i build in the syntax of the excel table, that then export to the SQL statement. How easy/difficult is it to customize the bcp statement to accomodate that kind of shuffling of the original data? "We tend to import the "raw data" into a temporary table and then "Process"/"manipulate" that data into a fresh temporary table (or update it in-situ in the original temporary table), and then finally "import" the cleaned-up data.I add two columns (to TempTable) for ErrorNumber / ErrorMessage so that during a clean-up UPDATE we can "log" an error state/message. Rows with existing error messages can be excluded from further updates - thus the first error is preserved.We then end up with either some or no error messages. If error messages are present we can abort the whole run, or only import the "clean" rows and print out the error messages for "humans" to resolve..For example:UPDATE MyTempTableSET ErrorNumber=1, ErrorMessage='MyStringDateColumn:invalid date'WHERE IsDate(MyStringDateColumn)=0 AND ErrorNumber IS NULL -- No earlier errors found, yet |
|
|
|
WJHamel
Aged Yak Warrior
651 Posts |
Posted - 2011-11-09 : 15:25:22
|
| Happy to report that the suggestion to tweak the memory locks seems to have fixed the errors i was seeing previously with large insert queries. I ran a beefy query today that went off without a hitch. Nonetheless, i'm digging in to learning to use BCP and format files to get this data inserted instead of these large queries generated by razorSQL. Even with the processes i've been using, it has always followed the processes of first putting that data into temp tables and grooming it there. I like the idea of the extra columns to log the errors generated. I will definitely be using that. |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
|
Constant "insufficient memory errors" -
Tara Kizer