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

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

Re: SIMPLE Recovery Model Log file growth

Show all headers | View raw

Cathy (Cathy@Nospam.com) writes:
> 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
> 2. take it offline if needed

ALTER DATABASE db SET SINGLE_USER WITH ROLLBACK IMMEDIATE

This will serve both steps.

> 3. Truncate it if needed

ALTER DATABSE db SET RECOVERY SIMNLE

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

The database, incluiding the data file? That is something you should be even 
more restrictive with. It will cause fragmentation. It's only a meaningful
operation if you have deleted lots of data that you never ever will
add again.

Shrinking the log file is meaningful if you have identified why the
log file was excessively large. But if log the file is big, because
it needs to be that big, it's pointless to shrink it. 

> 5. backup the database

BACKUPD DATABASE...

> 6. bring the database online again

ALTER DATABASE db SET MULTI_USER
 
> Imagine this is one database (whether simple of full recovery model)
> I am hoping for a single TSQL Script I can use
 
The problem is that you are asking the wrong question. You first need
to identify why the log is big. Then you can take action to correct 
that. 

Once that has been done, you can run the CHECKPOINT command. You can also
use DBCC LOGINFO to see that the active VLFs are in the beginning of the 
log. You can achieve this by running DBCC SHRINKFILE twice with a CHECKPOINT 
in between.

On the other hand there is not really any need of setting the database
in single user mode or back it up only because you shrink the log file.

-- 
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx

Back to comp.databases.ms-sqlserver | Previous | Next — Previous 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