CODEWITHSUNDEEP
sqlsql-serverssms
Cookie Monster
Cookie Monster

Reputation: 646

SQL server- Transaction log for database is full

I am trying to insert some data into a table in my database in sql server. It has huge amount of data, I am talking about millions of records.

I kept getting error 9002

The transaction log for database 'GCVS2' is full. To find out why space in the log cannot be reused, see the log_reuse_wait_desc column in sys.databases.

When I tried inserting data yesterday, it was fine with no problem, although it did take some time.

I tried it again today but kept getting this error. I checked the log file for my database, and it's auto increment is set to 10% ,unlimited. Is there any way to fix this?

Upvotes: 0

Views: 5760

Answers (2)

Vivek Kumar Singh
Vivek Kumar Singh

Reputation: 3350

You will need to check the Recovery mode of your database. Put it in Full Recovery mode. After that, make sure there is a transaction log backup in place for your database. You will need to dig through it and make a Maintenance plan, depending upon how critical your data is. That will be the long term solution.

For time being you can shrink your log files using following DBCC command - 

BACKUP LOG  DBName WITH TRUNCATE_ONLY
DBCC SHRINKFILE (  DBNameLog, 1)

Or you can do it through Object Explorer. Refer to this link for details. But you will have to set your Database to Simple Recovery model to use the Shrink command

Upvotes: 1

Jayasurya Satheesh
Jayasurya Satheesh

Reputation: 8043

You Can Truncate the transaction log. use the below query

BACKUP LOG  databasename  WITH TRUNCATE_ONLY

DBCC SHRINKFILE (  databasename_Log, 1)

Check here for more details

Upvotes: 2

Related Questions