How do I shrink log files in SQL SERVER 2012

I have a SQL Server 2012 express addition with many databases that the log files have not been backed up in a long time.  One log file is 1.1 TB.  Anyway, after I backup the databases and log files.  How do I skrink the log files?  When I try I will get this message:
"Cannot shrink log file 2 (SS_NCShared_Vehicle_Log) because the logical log file located at the end of the file is in use."

Any advice would be welcome.  I've been away from being a DBA for a long time.   I've had these problems before but it was a long time ago and on a different version of SQL Server.
Jack SeamanAsked:
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.

arnoldCommented:
Use db can to check whether you still gave an active transaction that is the cause of your issue.

Dbcc opentran(database)

In an express version and without a regular backup, why is the db settings are for full?
Using a simple might limit the growth, but since you seem to have an active transaction holding it up,

What is accessing the db.  See if you can reset/restart those services, in order to let that transaction event to close at which point the log can be shrunk.
0
Jack SeamanAuthor Commented:
How can I shrink a log file without backing it up first?  The one that is 1.1TB will take too long.
0
arnoldCommented:
Change the mode from full to simple. Make sure to terminate/cycle the applications that monitor or write to the db. Then you can truncate the log.

As long as there is an active transaction, you will not be able to shrink it.

What is the output of Dbcc opentran (database name)
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
Creating Active Directory Users from a Text File

If your organization has a need to mass-create AD user accounts, watch this video to see how its done without the need for scripting or other unnecessary complexities.

Jack SeamanAuthor Commented:
When I run this:
Dbcc opentran (SS_NCRecords_Property)
I get this:
No active open transactions.
DBCC execution completed. If DBCC printed error messages, contact your system administrator.


So, I think I can shrink the log file.  However when I run this:
USE [SS_NCRecords_Property]
GO
DBCC SHRINKFILE (N'SS_NCRecords_Property_Log' , 50)
GO

I will get this:
Cannot shrink log file 2 (SS_NCRecords_Property_Log) because the logical log file located at the end of the file is in use.

(1 row(s) affected)
DBCC execution completed. If DBCC printed error messages, contact your system administrator.

Any ideas?
0
arnoldCommented:
why do you see the need to run use, ?
you need to allow trancate.

DBCC SHRINKFILE (N'SS_NCRecords_Property_Log', 50, TRUNCATEONLY)

Change the recovery mode to simple since you are not regularly backing up the transaction log.

The purpose of the full recovery mode deals with backing up the DB and the transaction log on a regular basis to make a recovery to a point in time possible.
make sure you backup the DB right before you run the truncate.
then decide whether you still need the full recovery method and if so, make sure to use windows scheduler with powershell cmdlets to backup the DB/transaction log on a regular basis while at the same time managing the backup files (deleting old ones to avoid running out of space where those backup files are stored)
0
Jack SeamanAuthor Commented:
I was able to shrink the log files.  Changed the recovery method to simple.  One daily full backup is fine for these databases.
0
arnoldCommented:
Simple means no point in time restore/recovery.  The max potential loss is one entire day when ......

Glad I could help.

To manage transaction log growth, one runs transaction log backups once, twice if every couple of hours.........
This will help manage the size of the transaction log or its growth.
1
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
Windows Server 2012

From novice to tech pro — start learning today.