I'm randomly getting this error while running a script...
Exception calling "ExecuteNonQuery" with "1" argument(s): "An exception occurred while executing a Transact-SQL statement or batch." --> An exception occurred while executing a Transact-SQL statement or batch. --> Error: 802, Severity: 17, State: 20. (Params:). The error is printed in terse mode because there was error during formatting. Tracing, ETW, notifications etc are skipped.
print @@Version gives me:
Microsoft SQL Server 2008 R2 (RTM) - 10.50.1600.1 (X64) Apr 2 2010 15:48:46 Copyright (c) Microsoft Corporation Standard Edition (64-bit) on Windows NT 6.1 <X64> (Build 7601: Service Pack 1)
The "server" is running win-7, 64-bit, 8GB ram, i5-2400 @ 3.10GHz. When I get there error SQL Server is left in a fubared state. I have to restart the service before I can re-attempt to run the script. From what I can tell this may, or may not be a memory issue. If so is there any way to limit SQL Server so it doesn't go fubar? Speed isn't an issue (relatively) just stopping this error so my scripts work all the time is more important.
Is there a way to force SQL Server to free its unused memory or to defrag its memory so when it request more there is a large enough block of it there to use?