Groups > comp.databases.ms-sqlserver > #1117

Re: Oops, transaction log created too large (SS2005)

Path	csiph.com!usenet.pasdenom.info!aioe.org!eternal-september.org!feeder.eternal-september.org!mx04.eternal-september.org!.POSTED!not-for-mail
From	Erland Sommarskog <esquel@sommarskog.se>
Newsgroups	comp.databases.ms-sqlserver
Subject	Re: Oops, transaction log created too large (SS2005)
Date	Tue, 12 Jun 2012 12:29:37 +0200
Organization	Erland Sommarskog
Lines	41
Message-ID	<XnsA0707F182E5F3Yazorman@127.0.0.1> (permalink)
References	<c591c0fe-0d39-4a94-9868-8cf7305b08ee@googlegroups.com> <XnsA06FEF801AFC8Yazorman@127.0.0.1> <8cb96aef-fc62-4fe0-b2d6-acc42233422a@googlegroups.com> <XnsA0706B307A0F4Yazorman@127.0.0.1>
Mime-Version	1.0
Content-Type	text/plain; charset=windows-1252
Content-Transfer-Encoding	8bit
Injection-Info	mx04.eternal-september.org; posting-host="nBFDv6s1VJQDuF1w6hpX2A"; logging-data="16671"; mail-complaints-to="abuse@eternal-september.org"; posting-account="U2FsdGVkX1/SaObP+lf//t7dxVMqRJ3G"
User-Agent	Xnews/2006.08.24 Mime-proxy/2.1.c.0 (Win32)
Cancel-Lock	sha1:ex5df2jg0mVWGO8Xc9nU1mDW468=
Xref	csiph.com comp.databases.ms-sqlserver:1117

Show key headers only | View raw

Erland Sommarskog (esquel@sommarskog.se) writes:
> I'll ask around to see if there is a better trick that saves you from
> recreating the database.

Paul was quick to answer, here is what we had to say:

...................................................................
You can't shrink file ID 2 to be smaller than the first VLF. You can add 
a second file, but you can never remove file ID 2.

One thing he can try is to detach the database, delete the log file and 
then attach using ATTACH_REBUILD_LOG to create a new log file. Of 
course, keeping a copy of the log file, plus a db backup in case 
something goes wrong.

The absolute easiest thing to do is to create a database snapshot for 
the database and then immediately revert to snapshot. That will reset 
the log to be 0.5MB with two VLFs - it's a bug - a nasty, nasty bug - 
but in this case is helpful. See 
http://www.sqlskills.com/BLOGS/PAUL/post/Bug-reverting-from-a-database-
snapshot-shrinks-the-transaction-log-to-05MB.aspx

Be aware that the snapshot route breaks the log backup chain so he'll 
need to take a new diff or full backup to restart the chain.

I'd go route 2 in his case.
..................................................................

The last one is not the first you would think of, but sometimes bugs
works for you instead of against you. As I recall you need Enterprise
and Developer Edition to do database snapshots.

-- 
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx

Back to comp.databases.ms-sqlserver | Previous | Next — Previous in thread | Next in thread | Find similar

Thread

Oops, transaction log created too large (SS2005) rja.carnegie@gmail.com - 2012-06-11 08:00 -0700
  Re: Oops, transaction log created too large (SS2005) "Bob Barrows" <reb01501@NOyahooSPAM.com> - 2012-06-11 11:44 -0400
  Re: Oops, transaction log created too large (SS2005) Erland Sommarskog <esquel@sommarskog.se> - 2012-06-11 23:32 +0200
    Re: Oops, transaction log created too large (SS2005) rja.carnegie@gmail.com - 2012-06-11 20:22 -0700
      Re: Oops, transaction log created too large (SS2005) Erland Sommarskog <esquel@sommarskog.se> - 2012-06-12 10:32 +0200
        Re: Oops, transaction log created too large (SS2005) Erland Sommarskog <esquel@sommarskog.se> - 2012-06-12 12:29 +0200
          Re: Oops, transaction log created too large (SS2005) rja.carnegie@gmail.com - 2012-06-13 07:15 -0700

csiph-web