Cant shrink log file.

Hi,
We have the following message returned from a scheduled task:

Message
Executed as user: NMR1\sqltask. Cannot shrink log file 2 (DB_Log) because requested size (5120000KB) is larger than the start of the last logical log file. [SQLSTATE 01000] (Message 9007)  DBCC execution completed. If DBCC printed error messages, contact your system administrator. [SQLSTATE 01000] (Message 2528).  The step succeeded.

The step is part of a maintenance task that includes backup DB, log log shrink and index maintenance. The DB is in replication. How can I shrink the log.

Any help would be appreciated.
Thanks
Morpheus7Asked:
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.

Kamal KhaleefaInformation Security SpecialistCommented:
Is it always on?
pjamCommented:
Try this in Query:
ALTER DATABASE [databasename] SET RECOVERY SIMPLE
GO
 
DBCC SHRINKDATABASE ([databasename], 5)
GO
worked for me in SQL2005 when C:\ was full
PadawanDBAOperational DBACommented:
That message makes me wonder if you have transaction log backups configured.  If you do, it could be a case of open replication transactions, gumming up the works.  Also, I notice you mentioned that a transaction log shrink is part of your maintenance plan?  Under absolutely, positively *NO* circumstances should you do that.  You either need to move the database into simple recovery model and have no point in time restore capabilities or leave it in full recovery model and take more frequent transaction log backups.  You are hosing your transaction log performance by having a regularly scheduled shrink operation (logical fragmentation and an explosion of VLFs in the transaction log - especially if you are pre-SQL 2014).

Edit:  Here's a pretty good walkthrough on how to do some basic troubleshooting on this: http://blogs.technet.com/b/mdegre/archive/2011/09/04/unable-to-shrink-the-transaction-log.aspx - please use caution with manually marking all transactions as successfully replicated (it can be a little like playing with gasoline around a bonfire)

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
10 Tips to Protect Your Business from Ransomware

Did you know that ransomware is the most widespread, destructive malware in the world today? It accounts for 39% of all security breaches, with ransomware gangsters projected to make $11.5B in profits from online extortion by 2019.

Morpheus7Author Commented:
Hi,

We do a transaction log backup every hour, which is accompanied by a log shrink.

It would seem that this in not the thing to do then?
PadawanDBAOperational DBACommented:
The transaction log backup should mark the VLFs as available for re-use and prevent it from growing (except in cases where you have giant transactions in that hour - but you would probably want your transaction log to be around that size anyway to prevent auto-growths).  Is your transaction log growing in a never-ending fashion or is this just as a function of wanting to keep it as small as possible?  As an aside, a good starting point for the size of your transaction log is usually, at a minimum, the size of your largest table or 20-25% of the database =)
Kamal KhaleefaInformation Security SpecialistCommented:
is your sql server configured alwayson technology or is there replication onthe db ?

if so take full backup"recovery option"
then transact backup it will free the size
Morpheus7Author Commented:
We are not configured for always on. The db forms part of a 4 node replication topology.
Kamal KhaleefaInformation Security SpecialistCommented:
take full backup with recovery on primary node
shrink the database
Jason clarkDBA FreelancerCommented:
Try Shrinking the log file using
dbcc shrinkfile

Open in new window

and Then truncate it using
Backup databaseName with truncate_only

Open in new window

, then again shrink the log file

may this help you.
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.