Re: Transaction Log Size

From: Steven Hutchinson (it1_at_taysidefire.gov.uk)
Date: 11/24/04 

Date: Wed, 24 Nov 2004 12:26:47 -0000

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: 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: Backups have Shadow Copy Problems
    ... and restarted the server. ... suggested and changed the recovery model to simple on the one database called ... I understand the issue to be: the backup task failed ... You back up data from a volume that contains a Microsoft SQL Server ...
    (microsoft.public.windows.server.sbs)
  • Re: Enterprise Manager Newbie Question
    ... a SQL backup is not a simple copy of the database files. ... Is it possible to write a script that one could run from a workstation and ...
    (microsoft.public.sqlserver.tools)
  • 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)
  • Re: Does NTBackup backup SQL Express?
    ... schedule backups of the SQL databases which were written to a backup ... models you would have to backup and flush things manually. ... if you load the backup set does the database appear to be ... I've always scripted my SQL Server backups separatly. ...
    (microsoft.public.windows.server.sbs)