Stored Procedure Running into Issues

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Stored Procedure Running into Issues

Author

Topic

Dexter
Starting Member

5 Posts

Posted - 2005-09-02 : 14:18:02

I have a stored procedure which joins two huge tables and then tries inserting a large amount of data from this into a table in the database. However, when I execute this stored procedure I get this error message even before the SP has executed completely. The SP runs for some 8 minutes, without any results, before I see the error.

Can anyone suggest as to how I can handle the situation, provided that I have to insert all the data into the stored procedure.

Thanks in advance

-Dex

Dexter
Starting Member

5 Posts

Posted - 2005-09-02 : 14:19:17

Sorry that I missed out putting the error message. This is the error that I'm getting:
Could not allocate new page for database 'TEMPDB'. There are no more pages available in filegroup DEFAULT. Space can be created by dropping objects, adding additional files, or allowing file growth.

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2005-09-02 : 14:26:55

I believe it's a disk space thing. Either you're running out of room on your drive, or tempdb is not allowed to grow. Maybe tempdb is allowed to grow and you've run out of space because the tempdb transaction log is huge. If the transaction log is huge you can truncate it with backup log with truncate only (see Books Online).

Another possibility...Is this a new sproc? If it's never run successfully, you may have missed a join criteria or something that would generate way more data than you're intending.

EDIT:
quick way to check drive space: exec master..xp_fixeddrives
check tempdb properties with: exec sp_helpdb tempdb

Be One with the Optimizer
TG

Dexter
Starting Member

5 Posts

Posted - 2005-09-02 : 23:33:46

Hi TG

Ya! It is a new proc and it runs fine when I filter the data for a particular 'Disttributor' (a field in the table). So I believe the join conditions are OK. But then, I'll try out all that you have suggested. And then get back... :$

Thanks for your Suggestions

Dex

Dexter
Starting Member

5 Posts

Posted - 2005-09-05 : 00:45:44

Hi TG,

I checked for the join logics, They seem to be fine. Even I tried out shrinking the tempdb log from 43 MB to 2 MB. But then next time I run the SP, it agains gives the same error message and then the tempdb log also grows to 7 MB. The problem is I guess the bulk of data that has to be selected and then inserted.

Kindly suggest as to how I can get out of this. Any alternatives are most welcome.

Thanks in advance
Dex

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2005-09-05 : 09:18:57

What are the results for the 2 things I suggested you look at:

exec sp_helpdb tempdb
(look at the maxsize column of 2nd result set. should be "unlimited")
also look at filenames to see which drive they are located on.

exec master..xp_fixedDrives (available space on drives that contain tempdb files)

I suspect one of these 2 things is the culprit. However, if the db is set to unlimited and there is plenty of available space, then see if you can change your insert logic work on "chunks" of data at a time. ie: 10 chunks of 10 percent of the data at a time.

Also test your joins by processing more than 1 (but say not more than 10) Distributors. Make sure the results are what you expect.

Be One with the Optimizer
TG

Dexter
Starting Member

5 Posts

Posted - 2005-09-06 : 09:12:45

Here are the outputs for the queries you asked to check:
For exec master..xp_fixeddrives:
--------------------------------
C 550
E 0
F 1690
G 1624

exec sp_helpdb tempdb:
----------------------------------
tempdev 1 e:\program files\microsoft sql server\ PRIMARY 6176448 KB Unlimited 10% data only

templog 2 e:\program files\microsoft sql server\ NULL 7616 KB Unlimited 10% log only

What do you suggest now.

Thanks
Dex

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2005-09-06 : 09:24:58

Well, I guess you see the problem. No space left on E drive (where tempdb files are).

So...You've got a couple choices. Delete files or move that are no longer needed, add new storage space to that server, or get a new machine. Hopefully, there are a bunch of old backups or someother large files that are on that drive that you no longer need.

Be One with the Optimizer
TG

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources