Could not allocate space for object 'dbo.Large Object Storage System object

Hi Folks,
Actually am running some batch of sql statements in a loop.
Initially it Runs good but then ina middle am getting an exception
"Could notallocate space for object 'dbo.Large Object Storage System object: 443201315799040' in database 'tempdb' becausethe 'PRIMARY' filegroup is full. Create disk space by deleting unneeded files,dropping objects in the filegroup, adding additional files to the filegroup, orsetting autogrowth on for existing files in the file group."
Well i found to know that There is some insufficient space in tempdb Can any one suggest me the steps to overcome this Issue.
Thanks
Regards
N.Balaji
balajihexaware@gmail.com

Hi,
you can do a few things (it's hard to be very specific since you didn't post the queries). Let's start with the following list:
1. Make sure you have more free space on the drive which hosts the TEMPDB. Increase the TEMPDB size. If you can't have more free space on a particular drive, you can always move the TEMPDB to a different drive. You can also have more than one database file for TEMPDB which can (and should) be on different drives.
2. Increase the AUTOGROW setting if necessary. Do not set the MAXIMUM SIZE if that's Ok.
3. Be aware of heavily logged operations like deleting too many rows from big tables in a single go - consider doing it in smaller chunks.
4. Make sure there are no uncommitted transactions.
5. Check the sql statements: try to reduce the transaction scope - make them more granular. Have more GOs in the code if that doesn't affect the data consistency.
6. Make sure the existing indexes are useful for the queries. You could even consider creating some temp indexes while running the queries and then dropping them afterwards.
7. If you have too much code and it's difficult to find the part which is responsible for the most of the growth, there are queries you can run to dig deeper and find the 'bad' ones. Please note that it can be a cumulative effect too so that no particular one is solely responsible for the growth.
Hope this helps.
Thanks,
Varsham

Great suggestions, Varsham!
To expand on #3...

[quote user="Varsham"]3. Be aware of heavily logged operations like deleting too many rows from big tables in a single go - consider doing it in smaller chunks.[/quote]
If you're dumping data into a temporary table, performing some processes, and then deleting those records, consider using TRUNCATE TABLE instead. This process is not logged and would execute quickly. Also consider using a user-defined table instead of a temporary table, and dropping the work table when you're done.
Of course, this is may not be applicable to your process. Could you post some pseudo-code of your process?