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

Oops, transaction log created too large (SS2005)

Path	csiph.com!newsfeed.hal-mli.net!feeder3.hal-mli.net!newsfeed.hal-mli.net!feeder1.hal-mli.net!npeer01.iad.highwinds-media.com!news.highwinds-media.com!feed-me.highwinds-media.com!postnews.google.com!glegroupsg2000goo.googlegroups.com!not-for-mail
From	rja.carnegie@gmail.com
Newsgroups	comp.databases.ms-sqlserver
Subject	Oops, transaction log created too large (SS2005)
Date	Mon, 11 Jun 2012 08:00:25 -0700 (PDT)
Organization	http://groups.google.com
Lines	31
Message-ID	<c591c0fe-0d39-4a94-9868-8cf7305b08ee@googlegroups.com> (permalink)
NNTP-Posting-Host	194.83.173.135
Mime-Version	1.0
Content-Type	text/plain; charset=ISO-8859-1
X-Trace	posting.google.com 1339427807 13239 127.0.0.1 (11 Jun 2012 15:16:47 GMT)
X-Complaints-To	groups-abuse@google.com
NNTP-Posting-Date	Mon, 11 Jun 2012 15:16:47 +0000 (UTC)
Complaints-To	groups-abuse@google.com
Injection-Info	glegroupsg2000goo.googlegroups.com; posting-host=194.83.173.135; posting-account=dELd-gkAAABehNzDMBP4sfQElk2tFztP
User-Agent	G2/1.0
X-Received-Bytes	2306
Xref	csiph.com comp.databases.ms-sqlserver:1112

Show key headers only | 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