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?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
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.

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
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

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
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

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
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
SQL

From novice to tech pro — start learning today.