SQL Server out of memory

Question

I got this error running a query that goes against 2 tables with combined 50k rows.

An error occurred while executing batch. 
Error message is: Insufficient memory to continue the execution of the program.

How do I get around this?

Edit

I get this when I run DBCC MEMORYSTATUS

Msg 2571, Level 14, State 1, Line 1
User 'XXXX\YYYY' does not have permission to run DBCC memorystatus.

New Edit

The server worked fine the next morning without reboots or anything else.

Answer 1

Can you check what is the growth setting for temp db and for that particular Database?

Please ensure that the logfiles of temp db and that database are not set to restricted growth?

Can you check the available memory of the system while you are running the batch?

Answer 2

What is the query? Did you accidentally do a cross join?

Answer 3

Is it error 701, 801 or 802?

You need to investigate what is consuming the memory, see:

• How to use the DBCC MEMORYSTATUS command to monitor memory usage on SQL Server 2005
• INF: Using DBCC MEMORYSTATUS to Monitor SQL Server Memory Usage for SQL 2000.