Groups | Search | Server Info | Keyboard shortcuts | Login | Register [http] [https] [nntp] [nntps]

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

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

Show all headers | 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