Mark Breen
marklbreen at gmail.com
Wed May 25 03:30:58 CDT 2011
Hi Francisco,
Thanks for your response.
I have a scientific explanation and also ridiculously silly explanation, I
will start with the ridiculous one first.
*Ridiculous Explanation*
After years of trying to shrink databases, and years of not fully
understanding the way the log file works (may not still fully understand
it), when I discovered how I could trash the log file, I think I
subliminally feel I am taking revenge on all the large log files I had in
the past. So this heavy handed approach allows me to show SQL Server
transaction logs who is really boss.
*More Realistic Explanation*
Nowadays, the first thing I do with a db is to switch the recovery option to
Simple. It is a solution to years of enormous log files.
In the past 15 year however, before either a) I discovered simple recovery
mode or b) maybe simple recovery was not in SQL 6.5 / 7.0 I struggled with
large log files. As you say, with an important db, the first thing we want
to do is to back it up, but with enormous log files, I often struggled to
get the db to backup. In the case that the drive was already full, it was
even worse.
In SQL 7 days, if you recall, there was a special iterative script that we
used to run to fill the log file pages to a sufficient extent that we could
get it to properly reduce in size.
To be honest, I do not use this method to make it easier to carry around db
files, actually, to move a db, I always do a backup and restore - should I
consider detach and attach ? Is it better? What are the main differences
between detaching, copying and attaching vs backing up and copying and
restoring ?
The main situation I do it is when I have a big log file because I forget to
switch to Simple Recovery. I just quickly mentally check I have recent
backups - usually I do - and the trash the log file and continue working.
As I was writing this, I googled With Truncate Only, and came across this
http://sqlserverpedia.com/blog/sql-server-backup-and-restore/backup-log-with-truncate_only-like-a-bear-trap/
Here is the opening paragraph
*It’s somewhat akin to asking, “What’s the best way to cut my hand off to
free myself from this bear trap before I starve to death in the wilderness?”
Well, you shouldn’t be sticking your hand in bear traps to begin with, but
if you find your hand in a bear trap, ANY way to get out of it is a good
way. Pocket knife, teeth, band saw, whatever it takes.*
Following this email, I intend to experiment with With Truncate Only, but
may I ask, is there actually much difference between remove the actualy file
and doing a truncate and then a shrink? I suppose the shrink with 10%
ensures you save time re-building the file size up to 10%.
In summary, after often struggling to shrink a log file, the simplicity of
simply removing it and letting SQL Server manage the re-creation really
appeals to me.
Thanks
Mark
*
*
On 24 May 2011 15:46, Francisco Tapia <fhtapia at gmail.com> wrote:
> 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
> >
> >
> _______________________________________________
> dba-SQLServer mailing list
> dba-SQLServer at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/dba-sqlserver
> http://www.databaseadvisors.com
>
>