full log file - 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 2000 Forums

SQL Server Administration (2000)

full log file

Author

Topic

rookie_sql
Constraint Violating Yak Guru

443 Posts

Posted - 2010-01-13 : 04:48:14

Hi i've used the following code to shrink the log file

USE DatabaseName
GO
DBCC SHRINKFILE(<TransactionLogName>, 1)
BACKUP LOG <DatabaseName> WITH TRUNCATE_ONLY
DBCC SHRINKFILE(<TransactionLogName>, 1)
GO

But am still getting the full log file error the the log file is full

jackv
Master Smack Fu Yak Hacker

2179 Posts

Posted - 2010-01-13 : 05:43:18

If it's an emergency scenario :
DBCC SHRINKFILE (logfile, truncateonly )
BACKUP LOG database name WITH TRUNCATE_ONLY

otherwise , do not use this method for regular backups , file menagement. As ability to recover will be impaired

Jack Vamvas
--------------------
http://www.ITjobfeed.com

Kristen
Test

22859 Posts

Posted - 2010-01-13 : 08:25:05

"But am still getting the full log file error the the log file is full"

Is your Log file set to allow growth, or is it fixed size? That might be your problem.


DBCC SHRINKFILE (logfile, truncateonly )
BACKUP LOG database name WITH TRUNCATE_ONLY

I would advocate also doing an immediate FULL backup after this - otherwise I think that TLog backups may not resume UNTIL the next FULL Backup? (and if they do resume they are probably not usable in the absence of a Full backup?)

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts

Posted - 2010-01-13 : 10:38:13

Is there an old open transaction? Or replication?

Post results of
DBCC OPENTRAN

--
Gail Shaw
SQL Server MVP

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts

Posted - 2010-01-13 : 10:39:36

quote:
Originally posted by jackv
DBCC SHRINKFILE (logfile, truncateonly )

It may be worth noting that truncateonly is ignored when shrinking a log file. It's only a valid option for data files.

--
Gail Shaw
SQL Server MVP

Kristen
Test

22859 Posts

Posted - 2010-01-13 : 10:59:14

Thanks, I was just quoting JackV and it was Jack's subsequent "BACKUP LOG database name WITH TRUNCATE_ONLY" that I was worried about

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts

Posted - 2010-01-13 : 15:16:30

Sorry, I'll go edit who I quoted there.

--
Gail Shaw
SQL Server MVP

jackv
Master Smack Fu Yak Hacker

2179 Posts

Posted - 2010-01-14 : 01:47:45

GilaMonster , thanks for clarifying . It should be:
DBCC SHRINKFILE (logfile, 1 )
BACKUP LOG database name WITH TRUNCATE_ONLY

Jack Vamvas
--------------------
http://www.ITjobfeed.com

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts

Posted - 2010-01-14 : 04:52:05

quote:
Originally posted by jackv

GilaMonster , thanks for clarifying . It should be:
DBCC SHRINKFILE (logfile, 1 )

That's worse.

Firstly, if you're going to truncate the log, makes sense to do it before the shrink, so that the shrink can reclaim some space.

Second, the log should not be shrunk as small as possible. It should be shrunk to a reasonable size for the frequency of transactions and the log backup interval. Shrinking to 1 just ensures that there will be lots of autogrows in the immediate future.

Oh, and it goes without saying, after truncating the log, take a full backup immediately to restart the log chain

--
Gail Shaw
SQL Server MVP

jackv
Master Smack Fu Yak Hacker

2179 Posts

Posted - 2010-01-14 : 06:58:08

In theory, if you wanted to shrink it to the minimum size possible , you could use DBCC SHRINKFILE (logfile, 1 ). I'm making no assumptions about the nature of how the user is managing their environment -

Jack Vamvas
--------------------
http://www.ITjobfeed.com

jackv
Master Smack Fu Yak Hacker

2179 Posts

Posted - 2010-01-14 : 07:02:56

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2010-01-14 : 11:53:58

If you do shrink it down to a small number such as 1MB, ensure you autogrow at a decent size to avoid the VLF recovery issue. One of the DBAs here had a database take 9 days to recover at startup due to 80k VLFs. On medium-large databases, the autogrow should be set to 8GB according to Microsoft.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

"Let's begin with the premise that everything you've done up until this point is wrong."

Kristen
Test

22859 Posts

Posted - 2010-01-14 : 12:07:15

Interesting Tara. What are VLFs, and if a lot of them are created can that be fixed after-the-fact?

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2010-01-14 : 13:17:07

Here's my co-worker's reply about VLFs:
Here is another article on this from the Microsoft CSS SQL engineering team http://blogs.msdn.com/psssql/archive/2009/05/21/how-a-log-file-structure-can-affect-database-recovery-time.aspx

The CSS recommendation seems to be to have no more than 1000 VLFs per logfile. Having too few VLFs is potentially an issue too, and Kim's article you link to discusses that issue. Her recommendation (for databases with a large logfile requirement) is to have VLFs created in 512MB chunks instead of 4Gb or 8GB chunks. To achieve this you would need to grow the logfile in 8GB increments.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

"Let's begin with the premise that everything you've done up until this point is wrong."

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2010-01-14 : 13:18:05

Another link: http://www.sqlskills.com/BLOGS/KIMBERLY/post/Transaction-Log-VLFs-too-many-or-too-few.aspx

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

"Let's begin with the premise that everything you've done up until this point is wrong."

Kristen
Test

22859 Posts

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

Thanks Tara.

P.S. These things will keep me in Smoke and Mirrors for years, as a consultant. I do wonder if it really has to be like that though ...

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources