Re: Can anyone 'crash course' me in SQL maintenance?

On Feb 16, 7:43 am, "Paul Shapiro" <p...@xxxxxxxxxxxxxxxxxxxxxxx>
wrote:


a) The log file lets you backup data in smaller chunks while still having
the possibility of frequent backups. If you take a nightly full db backup,
that would leave you with 24 hours of vulnerability to data loss. If full
backups take 2 hours, you couldn't possibly do a full backup more than once
every two hours. Full backups take a lot of server resources, so you'd like
to avoid doing them while many users are working. The log file can be much
smaller than the data since it only records the transactions that occur. SO:
a typical plan is a nightly or weekly full backup, with log file backups
every hour, or maybe even every few minutes depending on how much data
you're willing to lose.


Paul,

Thanks. My current backup strategy is as follows:
Nightly backup at 3 AM.
Weekly Backup on Sunday at 5AM
transaction log backups hourly during business hours

I never shrink the database itself, as like you said it just
increases the log size. What I'm trying to figure out is why the free
space in both the log and the database is so large? The log file says
it contains 10G of data, but the actual size is 20G (10G of free
space), the data stats are much the same. 10G of data, 10g of free
space, or there about.

Obviously 20G of system resources being used to 'free' space is a
waste (or it is to me). Why does the log file not shrink
appropriately? After each transaction log backup the system should
mark it as complete and can be overwritten or erased .. but this
seemingly doesn't happen.

The log file space marked as "used" is holding the log data since the last
transaction log backup. Assuming your database is in Full logging mode, the
active portion of the backup log continues to grow until you backup the log.
A backup log with 10GB suggests either HUGE activity on a 20GB database. And
actually I'm not sure from what you show if the data is 20GB, or 2 GB? Does
your 20GB database size include the data file and the log file? In that case
if the log file is 18GB the data is only 2GB.

According to SQLEM my database is 10GB of data within it, but the file
size is 20GB. The log file says there's 10GB of data within it and
10GB of free space (20GB total). So the sum of my DB and LOG are 40GB,
but the free space between them is 20GB iteself!

Something with my plan must not be right. If the used portion of the
log is how much data has been written since the last Transaction
backup. There are 10 incremental T-LOG backups that occur throughout
the day, the last one at 8PM. After 8PM we have little to no
activity. I would think log would show that there is only a few
hundred megs(if that much even) of used log space after 8PM.





.

Relevant Pages

  • Transaction log problems
    ... just before you backup the ... database change the recovery model to ... Simple, shrink the log file then change it back to Full, ... >transaction log and it is causing us some problems. ...
    (microsoft.public.sqlserver.server)
  • Re: Unable to attach the database
    ... BUT NEVER DELETE A LOG FILE! ... If you have a clean backup of the database, ... good understanding of SQL Server and how to work from this situation. ...
    (comp.databases.ms-sqlserver)
  • What is the best practice to create Maintenance Plans
    ... There is not a definitive answer to the question of which backup is better. ... the database can be recovered to the point ... Full Recovery or Bulk-Logged Recovery model. ... Transaction Log backup for master database is not allowed. ...
    (microsoft.public.sqlserver.security)
  • Re: Stanby cannot be moved forward
    ... It very much looks like by opening the database you have switched the ... a log file. ... A backup is taken from production server to backup server. ...
    (comp.databases.oracle.server)
  • Re: does this backup sequence commit all data to the database
    ... The database is in FULL recovery mode. ... During the day you BACKUP the LOG to files. ... At night you TRUNCATE the log "to flush the log file of committed ...  Then you shrink the log file and backup the database. ...
    (comp.databases.ms-sqlserver)
Loading