Re: sql server table sizes

Mike Epprecht:

The database that I just started to work with has a simple recovery model.
The actual production database is 5 gigs while the transaction log table is
98 gigs. I can not figure out how the transaction log became so large. There
has never been a backup taken of the transaction log. How can the transaction
log keep growing if the transaction log is never used by the recovery model?

Thanks!

"Mike Epprecht (SQL MVP)" wrote:

> Hi
>
> Look at http://msdn.microsoft.com/library/en-us/adminsql/ad_bkprst_565v.asp
>
> If I were you, I would not back the log up, but rather set the recovery mode
> the Simple so it gets truncated.
> Then do your shrinks.
> Set the DB back the Full recovery
> Then a full DB backup
> Regularly schedule a transaction log backup using the DB Maintenance Plan
> Wizard.
>
> The T-SQL would be
> BACKUP LOG <dbname>
> TO DISK="drive:\path\filename.ext"
>
> See SQL Server Books Online for more Information.
>
> --
> --------------------------------
> Mike Epprecht, Microsoft SQL Server MVP
> Zurich, Switzerland
>
> IM: mike@xxxxxxxxxxxx
>
> MVP Program: http://www.microsoft.com/mvp
>
> Blog: http://www.msmvps.com/epprecht/
>
> "Wendy Elizabeth" <WendyElizabeth@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in
> message news:C2A1BE29-87E9-4945-A182-F4D19982E758@xxxxxxxxxxxxxxxx
> >I am relatively new to sql server 2000. I have a transaction log that is 98
> > gigs in size that has never been backed up. This transaction log file is
> > too
> > big and needs to be decreased in size.
> > Can you tell me how I would make my first full backup of this transaction
> > log file?
> >
> > To shrink the transaction log would i do something like:
> >
> > Use [sales]
> > Go
> > alter database [sales] set recovery simple
> > go
> > dbcc shrinkfile (sales_log, 100)
> > go
> > checkpoint
> > go
> > dbcc shrinkfile (sales_log, 100)
> > alter database [sales] set recovery full
> > go
> >
> > Then how would I do the full backup of the transaction log again?
> >
> > Let me know.
> >
> > Thanks!
> >
>
>
>
.

Relevant Pages

  • Re: Transaction log full
    ... in frequent intervals based on your transaction volume. ... to control your LDF file growth as well as transaction log backups will help ... Alternately if the recovery model is "SIMPLE" then you do not want to backup ...
    (microsoft.public.sqlserver.setup)
  • Re: Very large LDF
    ... If a database is backed up using the 'Complete Backup' option via ... You will have to backup the transaction log seperately. ... Check the Recovery model you are using for that database. ...
    (microsoft.public.sqlserver.security)
  • Re: Maintenance plan layout
    ... Whether or not you backup the transaction log or not depends ... upon your business needs in terms of backup and restore. ... But again, which recovery model you ...
    (microsoft.public.sqlserver.setup)
  • Re: Question about sql job, SQL 7 versus upgrade to SQL 2000
    ... See "Selecting a Recovery Model" in Books On Line. ... now with Simple recovery model transaction log file is ... truncated your job can't perform the log backup and fails. ... (or truncate it explicitly). ...
    (microsoft.public.sqlserver.server)
  • Re: Question about sql job, SQL 7 versus upgrade to SQL 2000
    ... See "Selecting a Recovery Model" in Books On Line. ... now with Simple recovery model transaction log file is ... truncated your job can't perform the log backup and fails. ... (or truncate it explicitly). ...
    (microsoft.public.sqlserver.tools)
Loading