Reputation: 646
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: 5750
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
Reputation: 8043
You Can Truncate the transaction log. use the below query
BACKUP LOG databasename WITH TRUNCATE_ONLY
DBCC SHRINKFILE ( databasename_Log, 1)
Upvotes: 2