MSSQL2008 R2 Express

I get this error

Error: 9002, Severity: 17, State: 4.
The transaction log for database 'abc' is full. To find out why space in the log cannot be reused, see the log_reuse_wait_desc column in sys.databases

The databases are in Simple recovery model. It is also on the same disk; sharing the limited space with OS and data files.

From this thread , I gather, there's probably nothing I can do given the server it's on. However, I am curious as to what happens when we get that error - are records not getting inserted / updated / deleted?

Thanks

Ami,

Try this:

SELECT name,log_reuse_wait_desc FROM sys.databases;

The log_reuse_wait_desc column contains the reason why the SQL Server currently can't reuse the log file of that database.

There are 10 reasons:

NOTHING
CHECKPOINT
LOG_BACKUP
ACTIVE_BACKUP_OR_RESTORE
ACTIVE_TRANSACTION
DATABASE_MIRRORING
REPLICATION
DATABASE_SNAPSHOT_CREATION
LOG_SCAN
OTHER_TRANSIENT

The first one, NOTHING, means that there are still free virtual log files available. The last one, OTHER_TRANSIENT, is currently not used. That leaves eight real reasons.

Berimi,

The database mentioned in the log has 'NOTHING' on it right now. The error was generated at different times yesterday. Correct me if I'm wrong; but, if I don't run the query right at the time the error occurs, I will always get 'NOTHING', correct?

thanks

The query does only show what the transaction log error reason is. No more.

Hi ,

can check any schedule job on database?

shrink database log file.

What is database log size ?

MSSQL2008 R2 Express

I get this error

Error: 9002, Severity: 17, State: 4.
The transaction log for database 'abc' is full. To find out why space in the log cannot be reused, see the log_reuse_wait_desc column in sys.databases

The databases are in Simple recovery model. It is also on the same disk; sharing the limited space with OS and data files.

From this thread , I gather, there's probably nothing I can do given the server it's on. However, I am curious as to what happens when we get that error - are records not getting inserted / updated / deleted?

Thanks

There is some scheduled task which while running fills up the logs and since logs gets filled and cannot write anything the task/query fails now since it is simple recovery eventually checkpoint is fired and space is reutalized and when you run query as pointed it shows nothing.

Moral of story you need to run this command when scheduled task actually is failing otherwise its difficult to track it.

The reaosn can also be you are really facing space crunch as you already pointed all transactions are fully logged so whatever you are running requires some space to log but due to space crunch is not able to complete.

Look for time when this erro came and then correlate it with SQl agent jobs timings , indexrebuild, stats update ,ETL process. Become detective and find out

Plus Simple RM is same as full what is difference is log truncation sym

Max size for that DB log is set at 8gb. As I have mentioned, that server only has 1 drive and the log file location is sharing it with data and OS. And that drive only have 10gb left. So, increasing the log file size is really an option.

This is SQL Express. There are no jobs running - transactions are from the applications. Unfortunately, I do not have access to the applications. However, I will make note of the time and check on it.

Thank you for all your assistance.

• Edited by Ami2013 12 hours 19 minutes ago