Capture content from Messages Tab SSMS

Question

I am running the following query to shrink logfile of a database.

DBCC shrinkfile('Sample_log',0) 

My concern is that, even if shrinking fails, query will run without any error. Only a message will be shown in message tab. And this error cannot be caught through error handling also.

Cannot shrink log file 2 () because requested size () is larger than the start of the last logical log file.

(1 row(s) affected)

DBCC execution completed. If DBCC printed error messages, contact your system administrator.

enter image description here

I have around 200 databases to shrink the files and I have wrote a script to shrink all files at once. But it is quiet hard to scroll through message tab and find which all files failed.

So, is there any option to capture content from message tab and insert into a table? Or is there any option to handle this kind of error ?

Answer 1

it depends the language you use. if you are using dot net (whether by C#/VB or powershell), you can setup the event to receive the message

yourSQLConnection.FireInfoMessageEventOnUserErrors = true;
yourSQLConnection.InfoMessage += yourSQLConnection_InfoMessage;


private static void yourSQLConnection_InfoMessage(object sender, System.Data.SqlClient.SqlInfoMessageEventArgs e)
{
    // do whatever you want
}