CODEWITHSUNDEEP
databasesql-server-2008
kinkajou
kinkajou

Reputation: 3728

Sql Server 2008 transaction log Error

select log_reuse_wait_desc from sys.databases where name = 'mydb'

enter image description here

1.  LOG_BACKUP

All update and insert query throws :

ODBC Error: ODBC RC=-1, ODBC SQLState=37000, DBMS RC=9002, DBMS Msg=[Microsoft][ODBC SQL Server Driver][SQL Server]The transaction log for database 'mydb' is full. To find out why space in the log cannot be reused, see the log_reuse_wait_desc column in sys.databases. Operation canceled

My query: First I delete and insert data into STATUS TABLE:

String insertQuery = "insert into "+dbmsName+"."+schemaName+".status(siteId,Severity) values(?,?)";
String deleteQuery = "delete from "+dbmsName+"."+schemaName+".status";

Now I select from status table and update live table:

 String updateQuery = "update "+dbmsName+"."+schemaName+".live set status = ? where new_site_id = ?";  
 String updateAllQuery = "update "+dbmsName+"."+schemaName+".live set status = site_status where new_site_id = ?";

Now I can't even use any other update queries too.

How can I solve this issue?

Upvotes: 0

Views: 2001

Answers (1)

Mitch Wheat
Mitch Wheat

Reputation: 300769

"The transaction log for database 'mydb' is full" - that's the problem.

You need to free up disk space. Until you do that, you won't ba able to do much.

Do you have a regular T-LOG maintenance schedule? If you are in FULL recovery mode and have no backups running, then the transaction will simply continue growing.

To shrink the transaction log for your database (Don't do this normally, just when encountering your current situation):

Has a maximum size been set for your database? Run this to find out:

sp_helpdb mydb
go

Update: You should perform a transaction log back up. You probably have to back it up more than once. After you back up the transaction log, try shrinking it.

Upvotes: 1

Related Questions