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

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

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

Show all headers | View raw

rja.carnegie@gmail.com wrote:
> 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.
>
I'm not sure I understand the problem. You can shrink the transaction log
files easily enough, either using the SSMS context menu (Tasks>Shrink>Files)
or DBCC SHRINKFILE if you'd rather do it via scripts.

> 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.

Ummm, is this a different problem? Shrinking transaction log files does not
require the creation of new databases ...

>  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.
>
It's not something I use a script for, since moving database files requires
operating system work. I will typically detach the database, either using
the context menu in SSMS or the sp_detach_db system procedure. Then perform
the tasks in the operating system required - copying the mdf, ndf, ldf etc.
files to where they need to be. The reattach the database using the context
menu in SSMS or the sp_attach_db system procedure. A simple rename can be
accomplished by detaching and specifying a new name when reattaching.

Alternatively, you can restore a backup of the database to a database with a
different name, and drop the original.


> 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?
>
I would start by looking up the topic in Books Online rather than relying on
/belief/.

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