SQL Server Performance Forum – Threads Archive

first time backup of transaction logs

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!

Hi Wendy The easiest way is to to do a full database backup.
For a beginner i would recommend enterprise manager right click on the database and select all task – then select backup database.
make sure complete backup is selected and then click add and choose a location and name for the backup. Then select the option tab and make sure remove inactive entries from transaction log is selected.
Click ok. Then u can execute dbcc shrinkfile (sales_log, 100) Or use enterprise manager again – database right click – all task – shrink database – files – select log file and shrink. Hope that helps.
A good practice is to do full database backups everynight and depending on your transaction log growth regular tlog backups. Cheers

Check: http://www.sql-server-performance.com/backup_restore_tuning.asp
http://www.sql-server-performance.c…/default.aspx?a=260&template=printarticle.htm Luis Martin
Moderator
SQL-Server-Performance.com One of the symptoms of an approaching nervous breakdown is the belief that one’s work is terribly important
Bertrand Russell
All postings are provided â€œAS ISâ€ with no warranties for accuracy.

Wendy, Reading this and another post from you I would suggest you review the Database Maintenance Plans that are in place via the Enterprise Manager. A Database Maintenance Plan Wizard allows you to walk through the basics of establishing a maintenance plan and lets you just click buttons for the major activites and establish schedules. It could be that there is a maintenance plan in place that doesn’t backup transaction log, or perhaps no plan in place at all. Once you see what is being done you can then determine the next step. Via the Wizard you will be able to quickly establish a backup of the database and/or the logs on whatever schedule you would like to.

You can backup database using enterprise managar or you can execute: backup database [sales] to <your backup device>
after the script mentioned. For more details find backup database topic in Books Online.

Before that you need to check are there any uncommitted transactions in the log by using DBCC OPENTRAN and as suggested by Mmarovic you must use Books Online for more information on basic rules of Tlog. Satya SKJ
Contributing Editor & Forums Moderator
http://www.SQL-Server-Performance.Com
This posting is provided â€œAS ISâ€ with no rights for the sake of knowledge sharing.

]]>