Поделиться через

What’s the meaning of different States for error 9002?

  • Статья

I just happened to find that when the log manager raises error 9002 “The transaction log for database '%s' is full. To find out why space in the log cannot be reused, see the log_reuse_wait_desc column in sys.databases” the State can vary and is loaded with meaning.

The value reflects whatever was the holdup reason for that database as exposed via the log_reuse_wait column in sys.databases at the time the error was raised.

So, even though the error invites you to go and query sys.databases to understand what was the holdup reason, there’s no need for. You can just match the value returned as State in your case with one in the list of possible values as documented in sys.databases for the log_reuse_wait column.

It has been like that since SQL Server 2005, but it took me a while to notice it was there. Smile

Comments

  • Anonymous
    October 31, 2012
    LOL. I totally didn’t know that either and you say it’s been there since 2005?! I knew about the log_reuse_wait_desc but not the state value. How cool is that. Thanks [as always] Nacho!! Cheers, kt