Proc keeps going even after error???

[pandatime]

Hi,

A proc was running today, doing an insert to a table. At a certain point, the disk space filled up so the insert statement couldn't complete. SQL Server then issued an error to that effect (as shown in my job history), but the proc kept running (as evidenced by other actions that occurred after the error).

Shouldn't the proc abort after a serious error such as this one? Or am I forced to use a try-catch block here?

Thanks much

 

[SQLSister]

You should not do a multistep insert/update/delete proc without a try catch block and explicit transactions and an explicit rollback if any step fails. This is important for data integrity.

If I'm doing something complicated, I also find it helpful to create a table variable and store each step as it happens and the error if it errors out, then insert this to a loggin table at the end and also inthe catch block after the rollback. If you do this in a table variable, it will not get lost when the rollback is issued as it would if you directly inserted to a table or temp table.

"NOTHING is more important in a database than integrity." ESquared

 

[pandatime]

Thanks. I can only say in my defense I did not write this code

However, I am still wondering why the proc did not stop execution at the point. I would think running out of disk space is a pretty serious error?

 

[jbenson001]

The only thing I can think of is if the proc is running on DataBase A(possibly on another server) and say Inserting on DataBase B, the fact that DataBase B is full, won' stop the proc from running and exectuing actions against DBs that are not full.

 

[mrdenny]

A procedure won't stop unless the level of the error being thrown terminates the batch and the session. Apparently a disk full message doesn't do this.

Denny
MVP
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / SQL 2005 BI / SQL 2008 DBA / SQL 2008 DBD / SQL 2008 BI / MWSS 3.0: Configuration / MOSS 2007: Configuration)
MCITP (SQL 2005 DBA / SQL 2008 DBA / SQL 2005 DBD / SQL 2008 DBD / SQL 2005 BI / SQL 2008 BI)

My Blog