From: reb01501@NOyahooSPAM.com   
      
   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/.   
      
   --- SoupGate-Win32 v1.05   
    * Origin: you cannot sedate... all the things you hate (1:229/2)