Re: Transaction Log Size

From: Tibor Karaszi (tibor_please.no.email_karaszi_at_hotmail.nomail.com)
Date: 11/24/04 

Date: Wed, 24 Nov 2004 16:21:11 +0100

Use DBCC LOGINFO to check whether the virtual log files are in use or not. Also, the KB articles I
listed in my article has detailed info on shrinking of the transaction log. 

-- 
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"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: Backups have Shadow Copy Problems
    ... with volume Shadow Copy error 800423f4. ... You back up data from a volume that contains a Microsoft SQL Server ... The recovery model of the SQL Server database is configured to use an ... It just ensures backup will continue without reporting the error. ...
    (microsoft.public.windows.server.sbs)
  • Re: Large Transaction Log Backup after Database Backup
    ... script and see for yourself that the database backup doesn't truncate the ... ALTER DATABASE steve SET RECOVERY FULL ... But my understanding was that the transaction log was> truncated after a full database backup. ...
    (microsoft.public.sqlserver.server)
  • Re: Transaction Log Size
    ... Backup database does not empty the transaction log files. ... How to Shrink the SQL Server 7.0 Transaction Log ...
    (microsoft.public.sqlserver.server)
  • Disaster Averted?
    ... Some process, unknown at this point, flooded a transaction log to the point ... the .mdf file was 0 bytes according to EM. ... Following the backup, I ... the database was now Suspect! ...
    (microsoft.public.sqlserver.server)
  • Re: backup advise
    ... When I run a backup of a database, does it automatically clean out the ... No. Backing up the database and backing up the transaction log are two ... Erland Sommarskog, SQL Server MVP, esquel@xxxxxxxxxxxxx ...
    (comp.databases.ms-sqlserver)
Loading