space in the log cannot be reused

Why i got this message and how to solve it:
com.microsoft.sqlserver.jdbc.SQLServerException: The transaction log for database 'gridaudit41' is full. To find out why space in the log cannot be reused, see the log_reuse_wait_desc column in sys.databases
sitijaafarAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Raja Jegan RSQL Server DBA & Architect, EE Solution GuideCommented:
>> The transaction log for database 'gridaudit41' is full

Just take a Full backup or Transactional log backup now of your database 'gridaudit41' to get rid of this error message..
0
Raja Jegan RSQL Server DBA & Architect, EE Solution GuideCommented:
0
sitijaafarAuthor Commented:
How to take a full backup or Transactional log backup?
0
Newly released Acronis True Image 2019

In announcing the release of the 15th Anniversary Edition of Acronis True Image 2019, the company revealed that its artificial intelligence-based anti-ransomware technology – stopped more than 200,000 ransomware attacks on 150,000 customers last year.

Raja Jegan RSQL Server DBA & Architect, EE Solution GuideCommented:
1. Connect to your Server in SSMS
2. Right Click your Database
3. Choose Tasks -> Backup
4. Choose Backup Type as Full or Transactional Log( Do a full backup itself)
5. At the bottom, choose the destination where to save this Backup
6. Click ok..
0
sitijaafarAuthor Commented:
Based on link you give rrjegan17 I find out that can move the log. so if I want move the log is right the below command I used? For example I want move log for gridaudit41 database.

ALTER DATABASE gridaudit41 MODIFY FILE ( NAME = gridaudit41 , FILENAME = 'new_path\os_file_name' )
0
sitijaafarAuthor Commented:
For the backup type it's only have an option full and differential.
0
Raja Jegan RSQL Server DBA & Architect, EE Solution GuideCommented:
>> Based on link you give rrjegan17 I find out that can move the log.

That's not a permanent solution for your scenario.

>> For the backup type it's only have an option full and differential.

Then Recovery model of your database is set to Simple.
And hence I asked you to do a Full backup since Transactional Log backup is applicable to database with recovery mode set to Full.

Kindly try taking full backup and let me know your comments.
0
sitijaafarAuthor Commented:
That's means I need to change the recovery model of database to Full. So, how can I change it?
0
sitijaafarAuthor Commented:
I already change to full
0
Raja Jegan RSQL Server DBA & Architect, EE Solution GuideCommented:
>> That's means I need to change the recovery model of database to Full.

No, I didn't mentioned that..
Either taking a Full Backup or Transactional Log Backup would help resolve the issue and since your database recovery mode is Simple, you have only one option ie., to take a Full backup..

If recovery model was Full, then you would have two options either take a Full backup or transactional log backup..
Hope this clarifies..
0
sitijaafarAuthor Commented:
Previously the database recovery model is simple and the backup type is full.  But still get the error message.
0
sitijaafarAuthor Commented:
So, I suspect the C drive is not enough space, and I want to change the log file to D drive.
0
sitijaafarAuthor Commented:
But i'm not sure this command is right or not

ALTER DATABASE gridaudit41 MODIFY FILE ( NAME = 'what should I put here' , FILENAME = 'new_path\os_file_name' )
0
Raja Jegan RSQL Server DBA & Architect, EE Solution GuideCommented:
OK. syntax is correct and execute the below query and replace 'what should I put here' with that.

use gridaudit41
select name, type_desc from sys.database_files
where type_desc = 'LOG'
0
sitijaafarAuthor Commented:
I got an error message when execute that command:

Invalid usage of the option LOG in the CREATE/ALTER DATABASE statement.


command:
ALTER DATABASE gridaudit41 MODIFY FILE ( NAME = 'use gridaudit41
select name, type_desc from sys.database_files
where type_desc = 'LOG'' , FILENAME = 'D:\MSSQL LOG' );
0
Raja Jegan RSQL Server DBA & Architect, EE Solution GuideCommented:
Its not like that..
Run this query separately in SSMS:
use gridaudit41
select name, type_desc from sys.database_files
where type_desc = 'LOG'

And replace the result for the NAME value, say somewhat ilke gridaudit41_log, then

ALTER DATABASE gridaudit41 MODIFY FILE ( NAME = 'gridaudit41_log' , FILENAME = 'D:\MSSQL LOG' );
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Chris MConsulting - Technology ServicesCommented:
You can truncate your log file this way:

BACKUP LOG [gridaudit41] WITH TRUNCATE_ONLY;

This should truncate your log and make it smaller.
Ensure to do a full backup after this.

If you want to manually shrink your log file to say 12 MB, use DBCC SHRINKFILE as follows:

DBCC SHRINKFILE(N'gridaudit41_log', 12);

To obtain the log file names, just run the following statement:
SP_HELPFILE;

The usage column should show "log only" for the log files while data files have "data only"
All the best.

0
Chris MConsulting - Technology ServicesCommented:
Since you're running in "Full" recovery model, ensure to setup a maintenance plan to backup your transaction logs to disk.
This way, your log files will not grow too big.

You can set it to run transaction log backups every 2 hours for a less busy database or every 30 minutes in case you have a very busy database.

Also check the upper limit of your file sizes in database properties and ensure that you have a size that can accomodate your database's transactional traffic.

Expand your disk size where the database is seated in case you often run out of hard disk space or add another drive and place your new transaction logs there (even new data files).

Let me know if you need any more help on database optimisation and file management.

Regards,
Chris Musasizi
0
grayeCommented:
So, why not do precisely what the error message suggests, and run a query to determine why the event log is full?
So, issue the following command to see the current "wait state" of each log file
      Use msdb
      select name, log_reuse_wait, log_reuse_wait_desc from sys.databases
The most likely reason a shrink operation failed is shown in the following codes:
LOG_BACKUP - the log file has performed a "wrap around" and the free space is now in the middle of the file.  Rerun a log backup so that it moves the free space towards the beginning or end of the file.
ACTIVE_TRANSACTION - there is an "open transaction" that's holding the log file hostage.  Use the DBCC OPENTRAN command to find out what's going on (and perhaps kill the process).
CHECKPOINT - there has been no database checkpoint in the period of time covered by the transaction logs.  Manually run the CHECKPOINT command or perform a database backup.
 
Take a look at the following link for a more detailed explanation of each code: http://msdn.microsoft.com/en-us/library/ms345414(SQL.90).aspx 
0
sitijaafarAuthor Commented:
The solution is I move the log to D drive which have a bigger space.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2005

From novice to tech pro — start learning today.