Re: Transaction Log Size

Tech-Archive recommends: Fix windows errors by optimizing your registry

From: Uri Dimant (urid_at_iscar.co.il)
Date: 11/24/04 

Date: Wed, 24 Nov 2004 16:35:36 +0200

Steven
To be or not to be that is a question :)

DBCC SHRINKFILE does not shrink a file past the size needed to store the
data. For example if 80 percent of the pages are used in a 10 MB data file
and a target size of the DBCC SHRINKFILE statement is 6 shrink the file to
only 8MB ,not 6 MB

"Steven Hutchinson" <it1@taysidefire.gov.uk> wrote in message
news:ehHxzCj0EHA.4004@tk2msftngp13.phx.gbl...
> To shrink or not to shrink..
>
> Because of the amount of space and time this transaction log is taking
with
> backups, I have attempted to shrink the transaction log
>
> However, the message returned from SQL Query Analyzer is:
>
> Cannot shrink log file 2 (SharepointTFB_log) because all logical log files
> are in use
>
> When I look at the physical files in the user database data path, the
> orginal file has shrunk to around 2 Gb but there are now 2 additional
files
> (database + transaction log) with fake appended to the name.
>
> Is this normal?
> "Mark Allison" <mark@no.tinned.meat.mvps.org> wrote in message
> news:uD1hGdi0EHA.3588@TK2MSFTNGP14.phx.gbl...
> > Steven,
> >
> > To add to Tibor's message, you will generally come up against fewer
> > problems if you backup your databases and logs natively, then get
> > CommVault to backup the backup files.
> >
> > So, only get CommVault to backup your MSSQL\BACKUP directory if that is
> > where all your native backups go.
> >
> > --
> > Mark Allison, SQL Server MVP
> > http://www.markallison.co.uk
> >
> > Looking for a SQL Server replication book?
> > http://www.nwsu.com/0974973602m.html
> >
> >
> > Steven Hutchinson wrote:
> >> Thanks for all your replies.
> >>
> >> We currently use a 3rd party backup agent to backup our SQL databases
and
> >> transaction logs (Commvault Galaxy iDataAgent for SQL) and the database
> >> is in Full Recovery mode. It appears that the transaction logs are not
> >> reducing in size after these regular backups are taking place.
> >>
> >> Because we are using a 3rd party agent to backup the database, do I
still
> >> need to issue the BACKUP LOG command before using DBCC SHRINKFILE?
> >>
> >> Should I get in touch with Commvault to query this or is this normal
> >> behaviour if you use a 3rd party backup agent instead of the native sql
> >> backup?
> >>
> >>
> >> "John Bandettini" <JohnBandettini@discussions.microsoft.com> wrote in
> >> message news:DC5C5FA6-1ED9-4CDA-9640-E3F5FA90A0EF@microsoft.com...
> >>
> >>>Steven
> >>>
> >>>here are some articles on how to shrink your transaction log.
> >>>
> >>>INF: How to Shrink the SQL Server 7.0 Transaction Log
> >>>http://support.microsoft.com/default.aspx?scid=kb;en-us;256650
> >>>
> >>>INF: Shrinking the Transaction Log in SQL Server 2000 with DBCC
> >>>SHRINKFILE
> >>>http://support.microsoft.com/default.aspx?scid=kb;en-us;272318
> >>>
> >>>http://www.mssqlserver.com/faq/logs-shrinklog.asp
> >>>
> >>>I would guess that you have your database/databases in Full recovery
mode
> >>>and you have not been performing Transaction log backups.
> >>>
> >>>You need to either set them to simple recovery mode or schedule regular
> >>>transaction log backups.
> >>>
> >>>To check the recovery mode of your databases, right click on the
database
> >>>in
> >>>Enterprise Manager and choose options. You can chnage the mode from the
> >>>window that will open.
> >>>
> >>>To perform transaction log backups (and database ones) you can use a
> >>>wizard
> >>>to set them up or you can create jobs manually and use the backup
> >>>command.
> >>>(See BOL for format)
> >>>
> >>>Hope this helps
> >>>
> >>>John
> >>>
> >>>"Steven Hutchinson" wrote:
> >>>
> >>>
> >>>>Is it possible to reduce the size of the transaction log for a
specific
> >>>>database. At the moment one of our databases has a database of size of
> >>>>16Gb
> >>>>and a transaction log of 43Gb!!
> >>>>
> >>>>As you might guess I am new to SQL Server but this doesn't seem right?
> >>>>
> >>>>Any help would be greatly appreciated..
> >>>>
> >>>>
> >>>>
> >>
> >>
>

Relevant Pages

  • Re: How to PURGE a transaction log?
    ... This doesn't shrink the actual file size. ... Make sure you understand backup and restore architecture for 6.5 *really* ... make sure you understand the database architecture regarding database ... doing dummy transactions and DUMP TRANSACTION until the log have moved ...
    (microsoft.public.sqlserver.setup)
  • Re: SQL 2005 + MOSS 2007 + Size config LOG
    ... If you leave the database in full recovery model, then you will need to modify your maintenance plans to include another job that runs every hour that backs up the transaction log. ... Once you have that job setup and running, you can run the shrinkfile and shrink the file. ... The file will grow again to handle the number of transactions you have between each transaction log backup. ...
    (microsoft.public.sqlserver.server)
  • Re: SQL 2005 Shrink issue
    ... Check out this scenario to see what happens in case you perform only full backup: ... in this scenario if your database corrupts you'd lose only 1 hour of data at most. ... To restore your database to its most recent point, you'd first restore the full backup and then the log backups one after another. ... Of course you can shrink your database and log file when your database in FULL Recovery Model. ...
    (microsoft.public.sqlserver.tools)
  • Re: Shrink file
    ... You can certainly use it but it won't do any good if there is even a single page at the end of the data file since the shrink always happens from the end of the file inwards and Truncate will not move pages. ... We can use the truncate option for log file. ... We had 1.4T database with almost 500GB empty space, ... >>> filehas how much free space, and run DBCC SHRINKFILE accordingly. ...
    (microsoft.public.sqlserver.server)
  • Re: big msdb
    ... Because they aren't cleaning up backup and job history, ... have espoused here for a long time, "shrinking the database" is not really ... Are you going to shrink the database, and then NEVER expect to be ...
    (microsoft.public.sqlserver.setup)