Log file full in sql server 2012

Error Occured in :Loln_spcustinstlmntdueUpToMonthMessage : The transaction log for database 'SHRICITYREPORT' is full due to 'LOG_BACKUP'. Rollback Succesfully

how can i retify this issue in my local maching iam running my sp  shows log file full.
kowsika deviAsked:
Who is Participating?
 
Pawan KumarDatabase ExpertCommented:
Thanks Nitin.  Yes it is deprecated.

Updated. i thought it was 2008.

BACKUP LOG databasename 
TO DISK = 'Path\NameofthelogFile.TRN'
GO

DBCC SHRINKFILE (YourLogicalLogFileName, 30 ) 

BACKUP DATABASE databasename TO DISK = 'Path\backupName.BAK'

Open in new window

0
 
Pawan KumarDatabase ExpertCommented:
For now truncate the log by shrinking it after that take a full database backup. Steps are

--

USE yourDBName
BACKUP LOG yourDBName WITH TRUNCATE_ONLY

DBCC SHRINKFILE (YourLogicalLogFileName, 30 ) 

BACKUP DATABASE databasename TO DISK = 'Path\backupName.BAK'

--

Open in new window


Going forward you need to take regular log backup with your database backups.
1
 
Nitin SontakkeDeveloperCommented:
@Pawan,

I thought, WITH TRUNCATE_ONLY is deprecated since 2008. Could you please confirm.

http://www.sqlserver-dba.com/2012/04/backup-log-with-truncate_only-is-discontinued.html
0
Improve Your Query Performance Tuning

In this FREE six-day email course, you'll learn from Janis Griffin, Database Performance Evangelist. She'll teach 12 steps that you can use to optimize your queries as much as possible and see measurable results in your work. Get started today!

 
Nakul VachhrajaniTechnical Architect, Capgemini IndiaCommented:
If this is on your local machine, do you even need to use the full recovery model? If you do not need point-in-time recovery for this database, consider changing the recovery model to Simple recovery.

NOTE: Even if you use Simple recovery, your log file may still grow if you use long running transactions, perform operations on a large number of rows without committing, etc.
0
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
Better find a solution that will solve your issue for best and not only in the immediate moment.
You should understand first why this issue is happening. It's happening because your database is configured for Full Recovery Model and this means that your transaction log will be highly used so expect that it will grow, meaning that you should store the transaction log file in a disk with enough space to let the file grow and that you should set the autogrow for the database files accordlying.
Regular transaction log backups will help to keep the file at a constant size so better controlled.
1
 
ZberteocCommented:
If that is in your local machine then change the database Recovery Model to Simple. Right click on Database node > Properties > Options > On the right panel next to Recovery model: choose Simple > Click OK. Now you can shrink the log file with this command:

dbcc shrinkfile(<logical_log_file_name>,0)

You get the <logical_log_file_name> from the Properties windows, as above, under the File section. In Simple recovery mode the log file will be kept under control by the SQL server.
0
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
I wouldn't set the recovery model to simple without performing a full or transactional backup before as you will be unable to restore to a point in time before the recovery model change.
And you should return setting the recovery model to full after the shrink as you'll loose the ability off recovery to a point in time if you don't do that.
Again, those are solutions to solve momentaneously the issue, meaning that the issue will happen again soon. If you want to do it for good, follow by previous comment.
0
 
Pawan KumarDatabase ExpertCommented:
Question abandoned.
Closing via accepting these comments as solution.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.