SQL Server Management Studio can't handle large files - by 7AE0DCEF-8457-4105-8D56-B9ABD8898AD4

Status : 

  Won't Fix<br /><br />
		Due to several factors the product team decided to focus its efforts on other items.<br /><br />
		A more detailed explanation for the resolution of this particular item may have been provided in the comments section.


46
2
Sign in
to vote
ID 269566 Comments
Status Closed Workarounds
Type Suggestion Repros 3
Opened 4/9/2007 7:35:34 AM
Duplicates 331214 Access Restriction Public

Description

The inability of SSMS to handle large (e.g., 50MB+) script files has been significantly reduced its utility and been a hindrance to productivity.  In working with large database update scripts, I was trying to take advantage of regex search and replace for enhancing the scripts.  But, on a PC with 1GB RAM, I was unable to save the changes, encountering the error message "The operation could not be completed. Not enough storage is available to complete this operation.".  And I was frequently unable to copy and paste usefully large portions of the changes, getting the error "The selection is too large to copy to the clipboard. Please make a smaller selection and try again."

In both scenarios, SS2000 Query Analyzer consistently handled these operations without issue -- even when SSMS (SqlWb.exe) was still running and consuming significant memory.
Sign in to post a comment.
Posted by Kenmdavisjr_ on 8/31/2014 at 4:55 PM
Still a problem in SSMS 2014. Trying to open a 2 G script file.
Posted by Rob Davis on 6/23/2014 at 6:20 AM
Just hit this problem with a 52mb file with 200,000 lines, each doing an IF NOT EXISTS ... INSERT INTO ... in SQL Server Management Studio 11.0.3128

Running the command line tool is not appropriate for the release processes currently in place.

How many lines can be run per execution?

Is this about number of lines, number of characters, numbers of executable statements?

I have tried SET IMPLICIT_TRANSACTIONS OFF to no avail.
Posted by Dean Grande on 3/16/2014 at 6:16 PM
This is still a problem in SSMS 2012 running on an x64 Win8 with 6GB RAM.

I have an 85MB sql script containing 14,000 insert statements.

I tried placing a GO after each 100 statements and it did not help, which was surprising. I understood that the GO statement was used (ie. parsed) by SSMS so that it was effectively submitting smaller chunks to the server for execution. source: http://technet.microsoft.com/en-us/library/ms188037.aspx

I get an error when I try to execute the whole script.

The only way I can run the script is to highlight a few hundred rows and execute just those (which is time consuming).


Help -> About
-------------------------
Microsoft SQL Server Management Studio                        11.0.3128.0
Microsoft Analysis Services Client Tools                        11.0.3128.0
Microsoft Data Access Components (MDAC)                        6.3.9600.16384
Microsoft MSXML                        3.0 4.0 6.0
Microsoft Internet Explorer                        9.11.9600.16521
Microsoft .NET Framework                        4.0.30319.34011
Operating System                        6.3.9600

Posted by Craig Humphrey on 10/14/2012 at 8:09 PM
This is still a problem in SSMS 2008R2 SP1 running on an x86 Win7 with 4Gig RAM.

In my case, I generated an ALTER script from an SSAS database, which was 300,000+ lines and now I can't save it.

Worked just fine on a x64 machine with 4Gig RAM however.
Posted by JasonMS88 on 4/17/2012 at 9:28 AM
you had a problem with a 50MB file? consider yourself lucky, my script is only 864KB, with a large update of like 12k lines, as soon as I load the script, SSMS dies
Posted by tofutim on 8/31/2011 at 1:19 PM
As for me, I had a script (350MB) that I was able to load into SSMS and edit. However, I could not save the said sql to be able to run SqlCmd. Out of storage! Do I really have to resort to editing using XEmacs?
Posted by Microsoft on 5/10/2011 at 3:32 PM
Hi everyone -
Thanks for writing in. I'm the new program manager in charge of this area. We have done some looking in to this and, unfortunately, it's not something that's going to get fixed right away. That being said we are looking at a full fix in a future release. We understand that there are a number of limitations around large files that we'd like to fix once and for all.

As far as the 'won't fix', in this case, it means we have added a feature to our backlog to fix this. I hope that helps answer the question. We do also appreciate the up votes. That helped make the case for why this is important.

Thanks
-Sam Hughes
Posted by DB007 on 2/8/2011 at 2:44 AM
We are told to "watch this space" this then gets marked as: Resolved -as Won't Fix -??

It is a problem, even on large memory machines. SSMS has not been designed to work with large open files, when compared to the old SQL Query Analyser no problems were encountered with large files.
Its been more of the case of two steps forward, three backwards when moving from SQL QA -> SSMS.
Posted by SQLHawk on 8/5/2010 at 7:38 AM
Is there a fix for this issue yet? It's been over 3 years since this was first reported.... how long will it take to see a fix?
Posted by rhchin on 1/8/2010 at 1:01 PM
Same problem here:

===================================
Cannot execute script.
===================================

Exception of type 'System.OutOfMemoryException' was thrown. (mscorlib)

------------------------------
Program Location:

at Microsoft.Win32.Win32Native.CopyMemoryUni(StringBuilder pdst, IntPtr psrc, IntPtr sizetcb)
at System.Runtime.InteropServices.Marshal.PtrToStringUni(IntPtr ptr)
at Microsoft.SqlServer.Management.UI.VSIntegration.ShellTextBuffer.GetText(Int32 startPosition, Int32 chars)
at Microsoft.SqlServer.Management.UI.VSIntegration.ShellTextBuffer.get_Text()
at Microsoft.SqlServer.Management.UI.VSIntegration.ShellCodeWindowControl.GetSelectedTextSpan()
at Microsoft.SqlServer.Management.UI.VSIntegration.Editors.ScriptEditorControl.GetSelectedTextSpan()
at Microsoft.SqlServer.Management.UI.VSIntegration.Editors.ScriptAndResultsEditorControl.OnExecScript(Object sender, EventArgs a)

And when SQLCMD is used:

C:\>sqlcmd -E -S<ServerName> -d<dbName> -iD:\<inputfile>
Changed database context to '<dbName>'.
Sqlcmd: Error: Scripting error.
Posted by mpavlik on 11/7/2009 at 8:05 AM
I am using MS SQL 2005 64-bit edition SP3 on Windows 7 with 6GB RAM. Task manager says I am only using 2.5 GB RAM, but I get the following error when trying to run a 180MB file:
TITLE: Microsoft SQL Server Management Studio
------------------------------

Cannot execute script.

------------------------------
ADDITIONAL INFORMATION:

Exception of type 'System.OutOfMemoryException' was thrown. (mscorlib)


When will this be fixed?
Posted by Hopelessly Optimistic on 3/30/2009 at 7:43 AM
I encountered this message both in SSMS & SQLCMD while executing a SQL script with over 100K results. Is there a work around besides breaking my query into smaller chunks?
Posted by Lyme on 3/7/2008 at 8:44 PM
I have encountered the same problem http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=2936842&SiteID=1
Posted by Microsoft on 4/9/2007 at 10:53 PM
Hi rthorington,

Thanks for reporting this issue. We will be considering to fix this issue in our future releases. Watch out this space for further updates.

Regards
Vinod