RAISERROR Behavior Question

I have a RAISERROR statement being displayed to the screen prior to a print statement, however the print statement was executed before the RAISERROR statement. Why would this happen? The print statement is not part of any conditional logic. It is executed sequentially followed by an IF statement that generates the RAISERROR.

Step 1. Loop through all Databases and dynamically run DBCC ShowContig
Step 2. End the loop and Print "SCANNING COMPLETED"
Step 3. Declare Cursor to read DBCC ShowContig results
Step 4. Check current time to see if it is ok to continue processing
If it is NOT ok, RAISERROR
Else begin DBReindex process
Step 5. Close and Deallocate Cursor

If I replace RAISERROR with a print statement, everything prints in order. What gives?

DaveIf you post the code it would be easier to find solution. However, If you are not saving the value of @.@.error in a variable it resets to Zero when you go back to check it again .

In other words You should

Declare @.err int
And while checking @.@.error use Select @.err = @.@.error and then check @.err value which will stay stored . I am not sure if this is what you needed to know .. Like I said , code posting might help|||The logic itself works fine so posting the code most likely won't help. The RAISERROR is generated after a conditional statement checking the length of a local variable. There is no need to check @.@.ERROR at that time. The only issue is why a RAISERROR gets sent to the screen before a print statement when the print statement executed first. I was told a few hours ago that Print statements are first sent to the buffer and its possible a RAISERROR does not hit the buffer. That may explain why it appears first.

Thanks, Dave|||what's the severity level and state values? i've experimented for a couple of minutes with different severity levels including 20 and up, at which point the print statement does not get displayed at all.|||Raiserror ("My error message", 16, 1) with log

Dave|||i put print 'test' before your raiserror and got this:

test
Server: Msg 50000, Level 16, State 1, Line 2
My error message|||I'll be testing all of the error routines again this week. Once I recreate the issue I'll see if the code is small enough to post.

Thanks, Dave

Quick question on sp_spaceused

I executed sp_spaceused on the db ang got following results
database_name:myDB
database_size: 1017.75 MB
unallocated space :104.13 MB

reserved : 309752 KB
data : 306832 KB
index_size : 1936 KB
unused : 984 KB

If we add reserved,data,index_size and unused up , we will get around 600MB.But the database has size of 1G. Could anyone tell me why?Just recalculated the size, it seems that
reserved = data+index_size+unused = 309752KB

The database_size is around 1G while reserved data is 300MB.|||Have you "shrunk" the database yet?|||sp_spaceused Seems to sometimes report against the database itself and NOT the DB's transaction log.

Database_size : translog and DB
unallocated space: DB only
reserved=data+index+unused: DB only

your missing the specific transactionlog used/unused info.

try sp_helpdb|||Thank you for your help.My boss want me to maintain the size of DB less than 200MB. My rough idea is to get size info from a system SP and determine whether the size exceeds the limit. I don't know which benchmark I should use in this case. Should I take translog into consideration?|||If I remember correctly (though I've never done it, myself)You can set the max limit of the database - that way, it won't grow past that limit|||Is your boss worried about disk space utilization ? if so, then you absolutely need to consider the transaction log (and perhaps any backups still on the server?).

I'd stear clear of specifically limiting the database size, as you app may start generating errors (when you're not around) because it hit the ceiling. Monitoring is safer, but it may mean that you occasionally go over the limit imposed by your boss.

To keep the size down, you should put a maintenance plan in place to perform periodic backups and then shrink the db. If you just shrink without doing the backup, your transaction log wont shrink because it needs a checkpoint to shrink from.|||Personally I wouldn't bother with the shrink. Chances are if you've grown the database to size X then it will return to that and more. Better off losing disk space so you can have a faster system. I only mentioned shrink cause it brings the actual files sizes down to something like whats actually required...right now.|||I heard of too many people with 15GB+ transaction logs for 100MB databases because they never perform any regular DB maintenance...|||heh. I've seen a 2 gig of ram usage from a 300mb database because of the use of unnecessary views.

The transaction logs should clear after you back up the db. Unless you have it all wired up to leave it alone.|||15 GB Tx logs and not backing up <gulp>|||<gulp/> is right.

when their server crashed, it took over 1/2 hour for SQL to "recover".