[dba-SQLServer] Attach a database without its log file

Mark,
I completely understand that while developing you may only wish to carry
around the db file, especially if you are delivering a new database design
to a client.  What I don't understand is why you would choose to use this
method to mitigate log file size in a normal day-to-day scenario?  I am
having a hard time thinking about what would justify stopping my database to
detach it, only to re-attach it just to create a small transaction log file?

If while developing you don't want the log file to get out of control you
would then configure in enterprise manager (sql server 2000) or management
studio (sql server 2005+) your database to be setup with the "Simple"
recovery option.  This would then bypass storing any changes in the tlog
before making it to the database and thus keeping your tlog small to begin
with.

If you are in a production environment and you ran some might big updates
and now need to recover your database size, then why not just use the tools
built within sql server?  Ideally you would first run a full backup of the
database, then backup the log with the truncate only flag, followed by the
shrink file command as such:

BACKUP LOG myFavoriteDB WITH TRUNCATE_ONLY
DBCC SHRINKFILE ('myFavoriteDB_log', 10)

Where 10 is the new size in MB of the transaction log.  remember ideally in
a production environment you'd want the transaction log to be about 10% size
of the database file, however there are exceptions.


just wondering...
-Francisco
<http://bit.ly/sqlthis>

On Tue, May 24, 2011 at 3:29 AM, Mark Breen <marklbreen at gmail.com> wrote:

> Hello Arthur / John
>
> And it is a great way to truncate the huge log file.  I use this trick all
> the time.
>
> just detach the db, then rename or delete the log file and then re-attach
> the db, and tell the GUI attach dialog to remove the 'missing' log file.
>  Hey presto you have a nice small log file again
>
> Mark
>
>
> On 23 May 2011 19:55, jwcolby <jwcolby at colbyconsulting.com> wrote:
>
> > yep, it creates a new one.
> >
> > John W. Colby
> > www.ColbyConsulting.com
> >
> >
> > On 5/23/2011 2:25 PM, Arthur Fuller wrote:
> >
> >> Is it possible to attach a database without its log file? It occurred to
> >> me
> >> that I could create an empty database, then copy its log file to a new
> >> name,
> >> then attach the database, but I haven't tried it yet.
> >>
> >> TIA,
> >> Arthur
> >> _______________________________________________
> >> dba-SQLServer mailing list
> >> dba-SQLServer at databaseadvisors.com
> >> http://databaseadvisors.com/mailman/listinfo/dba-sqlserver
> >> http://www.databaseadvisors.com
> >>
> >>
> >>  _______________________________________________
> > dba-SQLServer mailing list
> > dba-SQLServer at databaseadvisors.com
> > http://databaseadvisors.com/mailman/listinfo/dba-sqlserver
> > http://www.databaseadvisors.com
> >
> >
> _______________________________________________
> dba-SQLServer mailing list
> dba-SQLServer at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/dba-sqlserver
> http://www.databaseadvisors.com
>
>