Comments
4 comments
-
Hello,
Thank you for reporting this issue.
It would be helpful if we can get some information to debug this issue.
Can you let me know your system RAM spec and the size of the SQL script that causes the exception? Also, it would be useful to know how many different linked servers/databases are used in the script and the size of the databases used.
If we could get a copy of the script that would be splendid! Else the above details would be of use.
Thanks,
Tanya
Project Manager
Red Gate Software Ltd -
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 -
Hello,
Thank you very much for giving us the script.
But we are unable to replicate the issue with the script...any chances wherein we can get the snapshot of the database where you get the exception?
Regards,
Tanya
Project Manager
Red Gate Software Ltd -
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.
Add comment
Please sign in to leave a comment.
************** Exception Text **************
System.OutOfMemoryException: Exception of type 'System.OutOfMemoryException' was thrown.
at RedGate.SqlPrompt.Parser.NodeBase..ctor(ITreeNode parent, Int32 parentState, SqlGrammar grammar)
at RedGate.SqlPrompt.Parser.TerminalNode..ctor(IToken`2 token, ITreeNode parent, Int32 parentState, SqlGrammar grammar)
at RedGate.SqlPrompt.Parser.TerminalNode.ConstructNode(IToken`2 token, ITreeNode parent, Int32 parentState, SqlGrammar grammar)
at RedGate.SqlPrompt.Parser.TreeConstructor.FeedToken(IToken`2 nextToken, Int32 skippedTokens)
at RedGate.SqlPrompt.Parser.SqlParser.Parse(String source, Int32 offset, String& filter, IDatabaseCandidate& db)
at RedGate.SqlPrompt.Parser.SqlParser.GetCandidateList(String source, Int32 offset, Boolean caseSensitive)
at RedGate.SqlPrompt.Engine.NewEngine.SqlPrompt4Engine.GetCandidates(Int32 offset)
at RedGate.SqlPrompt.Engine.PromptEngineEmulator.get_GetSuggestions()
at RedGate.SqlPrompt.Engine.PromptEngineEmulator.get_IsWildcardExpansionAvailable()
at RedGate.SQLPrompt.CommonUI.Editor.EditorWindowBase.OnCaretPoitionChanged()
at RedGate.SQLPrompt.CommonVS.Editor.VSEditorWindow.OnTextViewCommandExec(TextViewMonitor sender, EventArgs e)
at RedGate.SQLPrompt.CommonVS.Editor.TextViewMonitor.Microsoft.VisualStudio.OLE.Interop.IOleCommandTarget.Exec(Guid& pguidCmdGroup, UInt32 nCmdID, UInt32 nCmdexecopt, IntPtr pvaIn, IntPtr pvaOut)