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

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

Oops, transaction log created too large (SS2005)

Show all headers | View raw

SQL Server 2005: I've created about 30 databases with 
a transaction log size much too large.  I think this is 
because I used Management Studio to generate a create-script
of an existing database and then modified it, but (?)
the script was generated with the original database 
/current/ transaction log size (several GB) instead of
existing default (1 MB).  And, yes, this is a problem.

The actual data is fairly small, and I do have options 
of (possibly) dropping, re-creating, and re-populating
the databases, or using tools "SQL Compare" and 
"SQL Data Compare" to duplicate the contents into a 
new database and /then/ dropping the old one in each 
case.  Incidentally, if anyone has a handy script to 
rename/move a live database /and/ all of its files 
(filegroups brilliantly named PRIMARY and INDEX - 
don't be like us, don't use keywords!), I would like 
to have such a script.  Otherwise I'll try to write 
one: my one for SQL Server 2000 doesn't work any more.

What I /believe/ I can't do, is reset the transaction
log size directly to smaller than the original value,
/including/ by shrinking, truncating, backing up and
restoring, /or/ by adding a new transaction log and 
then deleting the first one - because I /think/ that
you can't do /that/, either.  Am I, sadly, right?
Are there any other clever tricks to consider?

TIA

Robert Carnegie

Back to comp.databases.ms-sqlserver | Previous | Next — 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