How can we help you today? How can we help you today?
ryanmccauley
Activity overview Badges (0) Posts (1) Comments (2)

Activity overview

Latest activity by ryanmccauley

I can't provide a backup, since it's our financial database, but I've replicated the problem with an empty database, so you should be able to reproduce it. I've uploaded the files you'll need to http://www.trycatchfinally.net/Download ... Crash1.zip, and to cause this behavior, you'll need a linked server. Here are the steps to follow: 1. Create a linked server 2. On the linked server, in any database, run "Create Tables.sql" to create these two tables (fixing db name as necessary). 3. On your main SQL Server, in any database, run "Create Views.sql" to create two views that pull data from that linked server (fixing linked server and db names as necessary). 4. On your main server, when in the master database, load "Script.sql" (fixing linked server and db names as necessary). SSMS will behave normally. 5. Switch your active database to the one containing the two views. SSMS will stop responding and RAM allocation will climb without stopping. Let me know if you have any questions, or if this does not replicate the issue for you. In production, the local server is SQL2005 and the linked server is SQL2000, but I've replicated with SQL2005 for both. / comments
  1. Community
  2. SQL Prompt 4 (Early Access Program)
  3. OutOfMemoryException when analyzing new script
I can't provide a backup, since it's our financial database, but I've replicated the problem with an empty database, so you should be able to reproduce it. I've uploaded the files you'll need to ht...
0 votes
I have 3GB of RAM in my laptop, and about half of it free. If I watch task manager after I load the script, I can see the amount of RAM allocated climb up until there's none left, at which point I get the exception. Also, I notice that it maxes out one of the two processor cores in my laptop when it's frozen, so my laptop stays responsive while SSMS is locked up. When I watch more carefully, it looks like it gets stuck on the initial "Fetching script information" for the database. The script loads fine if I'm currently in the "master" database, but as soon as I switch to a user database, SSMS locks up and my memory usage starts to climb until it hits the limit of my RAM, and then I get the exception box. I've attached the script below, and I'm happy to send the schema for the referenced tables if it's necessary. IF OBJECT_ID('tempdb..#Actual') IS NOT NULL Drop Table #Actual IF OBJECT_ID('tempdb..#Budget') IS NOT NULL Drop Table #Budget --///////////////////////////////////////////////////////////////////////ACTUAL//////////////////////////////////////////////////////////////////////////////////////////////// SELECT max(vw_gltrxdet.seg2_code) System, sum(Case When vw_gltrx.date_applied <= DateDiff ("d",'1/1/1900','5/31/2009')+693596 THEN vw_gltrxdet.balance ELSE 0 END) Actual_Total, max('xxx') Status, max('xxx') Project_Name, max('xxx') Project_Type, Case When vw_gltrxdet.reference_code <> '' Then vw_gltrxdet.reference_code When vw_gltrxdet.seg1_code between '101750' and '101751' then vw_gltrxdet.seg2_code + '-' + Right(CONVERT(VARCHAR, '5/31/2009',1),1) + '991' When vw_gltrxdet.seg1_code between '101752' and '101759' then vw_gltrxdet.seg2_code + '-' + Right(CONVERT(VARCHAR, '5/31/2009',1),1) + '992' When vw_gltrxdet.seg1_code between '101760' and '101763' then vw_gltrxdet.seg2_code + '-' + Right(CONVERT(VARCHAR, '5/31/2009',1),1) + '991' When vw_gltrxdet.seg1_code = '101764' then vw_gltrxdet.seg2_code + '-' + Right(CONVERT(VARCHAR, '5/31/2009',1),1) + '994' When vw_gltrxdet.seg1_code = '101765' then vw_gltrxdet.seg2_code + '-' + Right(CONVERT(VARCHAR, '5/31/2009',1),1) + '995' When vw_gltrxdet.seg1_code = '101766' then vw_gltrxdet.seg2_code + '-' + Right(CONVERT(VARCHAR, '5/31/2009',1),1) + '996' Else 'ERROR' End 'Project' INTO #Actual FROM financials.dbo.vw_gltrx vw_gltrx INNER JOIN financials.dbo.vw_gltrxdet vw_gltrxdet ON vw_gltrx.journal_ctrl_num=vw_gltrxdet.journal_ctrl_num WHERE vw_gltrxdet.reference_code = '' AND vw_gltrx.posted_flag=1 AND vw_gltrx.date_applied>=DateDiff ("d",'1/1/1900',DATEADD(yy, DATEDIFF(yy,0,'5/31/2009'), 0))+693596 AND vw_gltrx.date_applied<=DateDiff ("d",'1/1/1900','5/31/2009')+693596 Group By Case When vw_gltrxdet.reference_code <> '' Then vw_gltrxdet.reference_code When vw_gltrxdet.seg1_code between '101750' and '101751' then vw_gltrxdet.seg2_code + '-' + Right(CONVERT(VARCHAR, '5/31/2009',1),1) + '991' When vw_gltrxdet.seg1_code between '101752' and '101759' then vw_gltrxdet.seg2_code + '-' + Right(CONVERT(VARCHAR, '5/31/2009',1),1) + '992' When vw_gltrxdet.seg1_code between '101760' and '101763' then vw_gltrxdet.seg2_code + '-' + Right(CONVERT(VARCHAR, '5/31/2009',1),1) + '991' When vw_gltrxdet.seg1_code = '101764' then vw_gltrxdet.seg2_code + '-' + Right(CONVERT(VARCHAR, '5/31/2009',1),1) + '994' When vw_gltrxdet.seg1_code = '101765' then vw_gltrxdet.seg2_code + '-' + Right(CONVERT(VARCHAR, '5/31/2009',1),1) + '995' When vw_gltrxdet.seg1_code = '101766' then vw_gltrxdet.seg2_code + '-' + Right(CONVERT(VARCHAR, '5/31/2009',1),1) + '996' Else 'ERROR' End UPDATE #ACTUAL SET Actual_Total = 5000000 WHERE PROJECT like '224-999%' --///////////////////////////////////////////////////////////////////////BUDGET//////////////////////////////////////////////////////////////////////////////////////////////// SELECT ct_budgetdtl.code_system System, ct_budgetdtl.code_reference Project, 'SYSTEM' Fund, ct_budgetdtl.amt_remaining Budget, ct_budgetdtl.budget_year Budget_Year, ct_budgetdtl.description Project_Name, 'BUDGET' Status, ct_budgetdtl.reserved_varchar_1 Project_Type INTO #Budget FROM financials.dbo.ct_budgetdtl ct_budgetdtl WHERE (ct_budgetdtl.budget_year = year('5/31/2009') AND ct_budgetdtl.amt_remaining <> 0) --ADD Below Budget////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////// Insert Into #Budget Select System, Project, 'SYSTEM' Fund, 0 Budget, year({?Month_Ending}) Budget_Year, Project_Name, 'Budget' Status, Project_Type FROM #Actual WHERE Project like '%994' or Project like '%995' or Project like '%996' Select a.Actual_Total, a.Actual_Total / CPE.Total_CPE Allocation_Percent, Case when cpe.id = '994' Then (Select budget from #budget where project like '102-_750') when cpe.id = '995' Then (Select budget from #budget where project like '102-_950') when cpe.id = '996' Then (Select budget from #budget where project like '102-_720') Else 0 End Allocation_Net, Case When cpe.total_cpe > (Case when cpe.id = '994' Then (Select budget from #budget where project like '102-_750') when cpe.id = '995' Then (Select budget from #budget where project like '102-_950') when cpe.id = '996' Then (Select budget from #budget where project like '102-_720') Else 0 End) THEN (Case when cpe.id = '994' Then (Select budget from #budget where project like '102-_750') when cpe.id = '995' Then (Select budget from #budget where project like '102-_950') when cpe.id = '996' Then (Select budget from #budget where project like '102-_720') Else 0 End) * a.Actual_Total / CPE.Total_CPE ELSE a.Actual_Total END Allocated, b.Project, b.Budget, cpe.total_cpe, cpe.id INTO #Allocation FROM #Budget b LEFT OUTER JOIN #Actual a ON b.Project = a.Project LEFT OUTER JOIN (Select right(project,3) id, sum(actual_total) Total_CPE FROM #Actual WHERE Project like '%994' or Project like '%995' or Project like '%996' Group By right(project,3)) CPE On right(a.Project,3) = CPE.id WHERE b.Project like '%994' or b.Project like '%995' or b.Project like '%996' or b.Project like '102-_750' or b.Project like '102-_720' or b.Project like '102-_950' Select * from #Allocation / comments
  1. Community
  2. SQL Prompt 4 (Early Access Program)
  3. OutOfMemoryException when analyzing new script
I have 3GB of RAM in my laptop, and about half of it free. If I watch task manager after I load the script, I can see the amount of RAM allocated climb up until there's none left, at which point I ...
0 votes
OutOfMemoryException when analyzing new script
  1. Community
  2. SQL Prompt 4 (Early Access Program)
As soon as I load a particular script, SSMS locks up for about 5 minutes, and then I get an Out of memory exception. I've included the exception text below. Though I can't send a copy of the databa...
2 followers 4 comments 0 votes
Total activity
4
Last activity
Member since
Following
0 users
Followed by
0 users
Votes
0
Subscriptions
1