urgent SQL Server database/log backup problem 1

[peterve]

Hi,

I have a database (about 9Gb)
The logfiles are 27Gb
I want to set up a backup schema (using the embedded backup jobs)
that :

makes 2 full backups of the database per week
makes a daily backup of the transactional log

My biggest concern is the transactional log
When I click the option to remove unused entries from the log, will it clean up the transactional log (as soon as I did a full backup of the database) ?
(I want to make sure that I only have to backup the transaction logs between the two days I will backup the database, AND that the transaction logs are cleaned up the first time I run the backup of the log after I full backed up the database...)

Is this how it works ?
What would happen if the logfile takes all remaining disk space ?
When backing up the database to disk (to another disk), it gives me an error..
so I can't backup the database anymore, which means that backing up the transaction log will not change anything to the size of the log file...

Can I delete the logfiles,
take a full backup of the database,
recreate a new logfile
and set up the backup schema for both database and logfile ?

How can I solve this ? ---------------------------------------------------------------------
I have not failed, I've just found 10,000 ways that don't work
---------------------------------------------------------------------
Peter Van Eeckhoutte
peter.ve@pandora.be
*:->* Did this post help? Click below to let me know !

 

[tlbroadbent]

You asked, "Can I delete the logfiles, take a full backup of the database, recreate a new logfile and set up the backup schema for both database and logfile?"

The answer is yes. Check this link, first.

Quickly Shrinking the Transaction Log

http://www.swynk.com/friends/krishnan/tranlogshrink.asp

I recommend the following links for additional information on shrinking the transaction log and database.

NOTE: MOst of these links are for SQL 2000. The information can usually be applied to SQL 7 also. Sometimes, the pages have links to a similar document for SQL 7.

Shrinking the Transaction Log

http://support.microsoft.com/support/kb/articles/Q272/3/18.ASP

http://msdn.microsoft.com/library/en-us/architec/8_ar_da2_1uzr.asp

Shrinking Databases

http://msdn.microsoft.com/library/en-us/createdb/cm_8_des_03_3vvp.asp

Development of a backup strategy is critical. The strategy chosen will vary from site to site and from database to database. For example, transaction backups for an OLTP DB will certainly be more frequent than for a DB that is only updated a few times per day. You would be wise to review backup topics in SQL BOL. I recommend the following links for discussion of backup strategies.

http://msdn.microsoft.com/library/en-us/adminsql/ad_bkprst_63eh.asp

http://www.swynk.com/friends/sjones/BackupStrategy.asp

http://www.swynk.com/friends/smirnov/backupsapp.asp

http://www.sqlmag.com/Articles/Index.cfm?ArticleID=9629

http://gethelp.devx.com/techtips/thesqlpro/10min/10min1100/10min1100.asp

http://gethelp.devx.com/techtips/thesqlpro/10min/10min1000-1.asp

Terry L. Broadbent
FAQ183-874 contains tips for posting questions in these forums.
NOTE: Reference to the FAQ is not directed at any individual.