An error occurred while executing batch. Error message is: Exception of type 'System.OutOfMemoryException' was thrown."

Question

I have one table(Sql server 2014) with 60 million data. while fetching the data i am getting

"An error occurred while executing batch. Error message is: Exception of type 'System.OutOfMemoryException' was thrown."

Can any one please help here?

Trillion

Can you show us the code that throws this error ? – Depredate

select * from tablename – Trillion

You never, ever want to do SELECT * FROM tablename .. do you have a WHERE clause on that? You are essentially pulling down the entire table. – Bookbinder

Try searching for this ssms out of memory exceptions – Garish

yes i am using where clause but i have to fetch number of count of ID column. – Trillion

You should show your query – Olla

Are you planning to fetch 60 million rows to the screen and then scroll to the end? – Ostosis

This happens to me (even with small queries) since I often leave Management Studio open for days. I solve it by reopening the program or switching to "Results to text" (instead of grid) – Prader

@MukundM select count(Id) FROM tablename – Manducate

Answer 1

Close and re-open SQL Management Studio and see if that takes care of it. Mine does this from time to time even on small queries if I leave it open for too long or have too many query windows open.

Raynor

I had 12 query windows opened remotely, I RDP the server directly and it worked for me. re-opening SSMS was the solution. – Cavy

I had a similar issue instead of using select * from the table. Selecting some columns helped to resolve the issue. – Outsail

Answer 2

Solution 1 : Close and re-opening SQL Server Management Studio and try querying.

Solution 2:

Eglantine

Answer 3

Looks like SQL Management Studio memory leaking for long running sessions. Restart of it helps to solve this issue

Behling

Answer 4

If you have opened so many new query editors in the SSMS, that can also cause this error. Try closing unnecessary tabs in SSMS.

Imaginative

Answer 5

This exception is caused by SSMS process which is occupied by long running queries.Don't worry about it , you can try next time and run your query on next tab.

Thumbscrew

Answer 6

In my case I was having problems even with tables with 8 rows. This worked for me:

• Deleting Temp file present on %temp% location. If the problem still happens restart SSMS.

This happened due to unused temp files.

Blah

Answer 7

After I deleted the temp file then it worked. Delete the file permanently Ctrl + Shift + Delete

Macaronic