Re: sql server table sizes

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: Log Shipping: question about secondary dB mode?
    ... Regular transaction log backups will allow the committed and backed up portion of the log file to be reused for new transactions. ... And last to recover the db you restore the last log file using the WITH RECOVERY option and you should be all set. ... all the backup and restore jobs are proceeding without errors. ...
    (microsoft.public.sqlserver.tools)
  • Re: How can I back up a log-shipped database?
    ... This means that a later log backup from the production database will not just be ... able to add the log records to the log-shipped database, because the transaction log has been ... It's clear I don't understand the whole RECOVERY business. ...
    (microsoft.public.sqlserver.server)
  • Re: newbie backup question
    ... Your data will be moved to MDF file after the checkpoint, ... You can run the below command to truncate the transaction log, ... Even if you backup the transaction log, ... not be useful since you turned the recovery to SIMPLE. ...
    (microsoft.public.sqlserver.setup)
  • Re: SQL 2005 Shrink issue
    ... When you perform a Transaction Log Backup, passive virtual logs in your transaction log file are deleted. ... you can change your database's recovery model to SIMPLE when you want to get rid of your passive virtual logs in your transaction log file. ...
    (microsoft.public.sqlserver.tools)
  • Re: Shrinking Transaction Log
    ... Do you even backup the transaction log? ... and then use DBCC SHRINKFILE to get the file size down. ... But that won't keep this from happening all over again if the cause was using a recovery model that keeps transactions around and a recovery strategy that does nothing with the log. ... Using the SQL GUI to do a transaction log backup is it the 'Remove ...
    (microsoft.public.sqlserver.tools)
Loading