VLFs and MODIFY FILE failed - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

SQL Server Administration (2008)

VLFs and MODIFY FILE failed

Author

Topic

influent
Constraint Violating Yak Guru

367 Posts

Posted - 2012-01-27 : 12:23:58

I'm trying to use the script below to reduce the number of virtual log files in the MyDb log file, but I get an error that says
"Msg 5039, Level 16, State 1, Line 8
MODIFY FILE failed. Specified size is less than or equal to current size."

Shouldn't setting it to Simple let me reduce the size to near zero? I'm confused...

USE [MyDb];
ALTER DATABASE [MyDb] SET RECOVERY SIMPLE;
DBCC SHRINKFILE (N'MyDb_log' , 0, TRUNCATEONLY);
DBCC SHRINKFILE (N'MyDb_log' , 0);
ALTER DATABASE [MyDb] SET RECOVERY FULL;
ALTER DATABASE [MyDb] MODIFY FILE (NAME = N'MyDb_log', SIZE = 341MB);
ALTER DATABASE [MyDb] MODIFY FILE (NAME = N'MyDb_log', SIZE = 682MB);
ALTER DATABASE [MyDb] MODIFY FILE (NAME = N'MyDb_log', SIZE = 1023MB);

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts

Posted - 2012-01-27 : 14:05:13

No, setting simple recovery model is not a guarantee that you'll shrink the log to 0 (btw, shrink with truncateonly is for data files, not log file. Leave the option out.)

You could have active transactions, replication, mirroring queue, pending checkpoint, current database backup and probably a couple other things I've forgotten.

--
Gail Shaw
SQL Server MVP

influent
Constraint Violating Yak Guru

367 Posts

Posted - 2012-01-27 : 15:28:54

Thanks Gail. I took the script from here: http://www.realworlddba.com/post/2011/06/07/Virtual-Log-Files-VLF-And-SQL-Server-Performance.aspx

Can you think of a way to make it work as intended?

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts

Posted - 2012-01-27 : 16:35:44

Find out what's holding the log active, resolve that, then shrink and regrow.

p.s. those sizes you're regrowing to are odd... There's nothing magical about 50 VLFs. Take a read through this: http://www.sqlskills.com/BLOGS/KIMBERLY/post/8-Steps-to-better-Transaction-Log-throughput.aspx

--
Gail Shaw
SQL Server MVP

influent
Constraint Violating Yak Guru

367 Posts

Posted - 2012-01-27 : 17:17:43

Actually I had read that article and was making sure it was a much lower VLF count, but I'm really just in the testing phase right now, playing with a copy of the production db that's used for testing. I added 500 MB to the file size in the script and then it worked, on the last MODIFY FILE anyway.

How do I see what's holding the log active? Since it never gets used (just recreated and populated on a nightly basis) I wouldn't think there'd be much of anything holding it active.

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts

Posted - 2012-01-27 : 17:29:10

[url]http://www.sqlservercentral.com/articles/Transaction+Log/72488/[/url]

--
Gail Shaw
SQL Server MVP

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources