Shrink log files isssue

Question

Shrink log files isssue

balbirsinghsodhi

SSCrazy Eights

Points: 9210
More actions
June 9, 2008 at 1:52 pm

#122957

I am getting following error when I try to shrink transaction log files. It was working fine before but when I separated transaction log files into different files I get following error.
I use following command to shrink log file
use IDX
go
BACKUP LOG IDX WITH TRUNCATE_ONLY
go
DBCC SHRINKFILE (IDX_Log1, 1)
GO
There are nine log files from idx_log1 to idx_log9.
Msg 3023, Level 16, State 2, Line 2
Backup and file manipulation operations (such as ALTER DATABASE ADD FILE) on a database must be serialized. Reissue the statement after the current backup or file manipulation operation is completed.
Msg 3013, Level 16, State 1, Line 2
BACKUP LOG is terminating abnormally.
Msg 3023, Level 16, State 2, Line 1
Backup and file manipulation operations (such as ALTER DATABASE ADD FILE) on a database must be serialized. Reissue the statement after the current backup or file manipulation operation is completed.
Any help will be appreciated.

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

GSquared SSC Guru Points: 260824 More actions · Answer 1

Judging by the error, I'm going to hazard that another backup or file operation was already running on that log file.

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

MANU-J. SSC-Dedicated Points: 31126 More actions · Answer 2

Just check whether any file related operations like Autogrow, autoshrink or any other backup statements are in execution in the background on this database.

Manu

balbirsinghsodhi SSCrazy Eights Points: 9210 More actions · Answer 3

Thanks guys,

I am able to shrink the log files now after stopping SQL Server Services.

Miks SSC-Addicted Points: 489 More actions · Answer 4

Hello

I am also getting the same error, and it use to work fine before.

i have checked if there are anything running,but maybe I am missing something.

Please can you assist me in checking as am still new to this,also is there any other

way to truncate without stopping the services,as this is a live server.

any help would be really appreciated.

thanks

Sam

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 5

Why do you want to shrink your log files?

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Miks SSC-Addicted Points: 489 More actions · Answer 6

I need to shrink the files cos of space issues.

but not to worry i managed to get it to work.

the problem was the legato Backup was running on the server

thanks

Miks

EdVassie SSC Guru Points: 60472 More actions · Answer 7

You should not shrink a database file or a log file if you expect it to grow again within 3 months. Shrinking a database mdf file causes internal disorganisation, and repeated shrinking and growing of any database file causes NTFS fragmentation. Both of these will harm your SQL Server performance.

If your log file grows to a particular size every day or every week, then you need to get enough disk space to cope with this file size.

If your database is in Full recovery, the commands you give will break the log backup chain and you will not be able to recover to any point after you truncated the log. Forcing a log truncation like you are doing should only be done in an emergency situation where other possibilities have been ruled out.

You need to look at your recovery SLA to decide what your backup strategy should be. If the business expects you to recover the database to the point of failure, you must never truncate a transaction log.

It is worth reading what Books Online (BOL) says about recovery modes, and about Full and Log backups. People on this forum can help you design a backup strategy, but you need to do some research to work out why what you are currently doing is bad.

Original author: https://github.com/SQL-FineBuild/Common/wiki/ 1-click install and best practice configuration of SQL Server 2019, 2017 2016, 2014, 2012, 2008 R2, 2008 and 2005.

When I give food to the poor they call me a saint. When I ask why they are poor they call me a communist - Archbishop Hélder Câmara