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

Re: SIMPLE Recovery Model Log file growth

From	"Bob Barrows" <reb01501@NOSPAMyahoo.com>
Newsgroups	comp.databases.ms-sqlserver
Subject	Re: SIMPLE Recovery Model Log file growth
Date	2012-10-30 18:22 -0400
Organization	A noiseless patient Spider
Message-ID	<k6pjuu$4o9$1@dont-email.me> (permalink)
References	(5 earlier) <XnsA0FAA0E70E28EYazorman@127.0.0.1> <k6jnqa$rf3$1@dont-email.me> <17CdnaWZsLRDOBDNnZ2dnUVZ8s-dnZ2d@bt.com> <XnsA0FAEDAAB5AF1Yazorman@127.0.0.1> <n7adnfO2cLRk1A3NnZ2dnUVZ8tWdnZ2d@bt.com>

Show all headers | View raw

Cathy wrote:
> Perhaps I am confusing the issue by providing too much details and
> getting things sidetracked
>
> Lets forget about how many files there are and how many databases.
>
> My question is as before
>
> what is the simplest to
> 1. close any open transactions in the log file

Run CHECKPOINT

> 2. take it offline if needed

Not needed.

> 3. Truncate it if needed

BACKUP WITH TRUNCATE_ONLY

> 4. Shrink the database (sorry I realise my error now saying logfile
> before)

NEVER shrink a database. You can shrink a log file if you think you know 
what size it should be (a good rule of thumb is twice the size of the 
largest table in your database, but that might be too large - there's some 
trial and error involved) but never shrink a database's data file.
http://www.sqlskills.com/blogs/paul/post/why-you-should-not-shrink-your-data-files.aspx

> 5. backup the database
You've done nothing to necessitate backing up the database as long as you 
already have a good backup schedule.

> 6. bring the database online again

Again. Not needed
>
> Imagine this is one database (whether simple of full recovery model)
> I am hoping for a single TSQL Script I can use
>
Personallly, I use SSMS - you can tell it to script your actions if you must 
have a script. But as Erland told you, the scripts will be different for 
each database log you work on.
Oh, and did I mention that the only files you should consider shrinking are 
the log files? I hope I made it clear that data files should never be shrunk 
...

Back to comp.databases.ms-sqlserver | Previous | Next — Previous in thread | Next in thread | Find similar

Thread

SIMPLE Recovery Model Log file growth "Cathy" <Cathy@Nospam.com> - 2012-10-27 22:46 +0100
  Re: SIMPLE Recovery Model Log file growth Erland Sommarskog <esquel@sommarskog.se> - 2012-10-28 00:07 +0200
    Re: SIMPLE Recovery Model Log file growth "Cathy" <Cathy@Nospam.com> - 2012-10-28 07:59 +0000
      Re: SIMPLE Recovery Model Log file growth Erland Sommarskog <esquel@sommarskog.se> - 2012-10-28 10:58 +0100
        Re: SIMPLE Recovery Model Log file growth "Cathy" <Cathy@Nospam.com> - 2012-10-28 13:23 +0000
          Re: SIMPLE Recovery Model Log file growth Erland Sommarskog <esquel@sommarskog.se> - 2012-10-28 15:49 +0100
            Re: SIMPLE Recovery Model Log file growth "Bob Barrows" <reb01501@NOSPAMyahoo.com> - 2012-10-28 12:51 -0400
              Re: SIMPLE Recovery Model Log file growth "Cathy" <Cathy@Nospam.com> - 2012-10-28 21:38 +0000
                Re: SIMPLE Recovery Model Log file growth Erland Sommarskog <esquel@sommarskog.se> - 2012-10-28 23:21 +0100
                Re: SIMPLE Recovery Model Log file growth "Cathy" <Cathy@Nospam.com> - 2012-10-30 21:43 +0000
                Re: SIMPLE Recovery Model Log file growth "Bob Barrows" <reb01501@NOSPAMyahoo.com> - 2012-10-30 18:22 -0400
                Re: SIMPLE Recovery Model Log file growth Erland Sommarskog <esquel@sommarskog.se> - 2012-10-30 23:37 +0100

csiph-web