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

First, transactions are committed from the log to the data more or less as soon as the system can do it. The lag time would rarely exceed a few seconds unless you have a very busy system. There is nothing you could do to make SQL Server dump logged transactions before they get committed to the data. The log is not there as a storage buffer. When you issue a tansaction against the database, it is first written to the log and only then written into the data. That accomplishes a few goals:

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.

b) The log file gives you the possibility of recovering the data as it existed at any point in time. So if someone accidentally deletes important data, you can restore a copy of the database exactly as it was 1 second before that mistake.

c) If someone pulled the plug from the server while database activity was occurring, the log file lets the system recover completely.

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.

There are two kinds of sql backups. A regular backup is backing up the data. A log backup copies the active portion of the log file to the backup, and then marks that portion of the log as inactive so it can be reused. You need to be doing both kinds of backups, with appropriate parameter settings for the log backup. I think it's well worth read the portions of SQL Server's Books On Line (bol) that explain backups and database size. 'Backup log with truncate' is NOT what you want. That just throws away the log data. The command has been deprecated in SQL 2005, meaning it will be dropped eventually.

A couple of things you could check. Make sure log backups are happening. If they don't, the log file keeps growing. If you have a maintenance plan that shrinks the db while logging is set to Full mode, the log file grows because it logs all the activity during the db shrink. So while it might seem counter-intuitive, shrinking the db makes it get bigger because while it shrinks the data files, it increases the log files.

Finally, if this is a line-of-business application and you're not that familiar with SQL Server, it would probably be worth hiring a consultant to review your backup and maintenance procedures. It should only be a few hours work if that's all they have to look at.

Paul Shapiro

"Ryan" <mindflux98@xxxxxxxxx> wrote in message news:21dda0ac-47b4-437b-acb3-0ec0062ff853@xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
I've got an SBS 2003 Prem SP3 w/ SQL (SP4) server that I 'maintain'.
I use redgate's SQL Backup to do a nightly backup, a weekly backup and
transaction logs during business hours. I also have SQLEM do a
maintenance rebuild-index w/ 20% freespace every week.

My problem:

Database Size: 20G
Used space: 10

Log file size: 18g
used space: 10g.

As you can see I have a LOT of dead space. I'm hesitant to shrink the
log as I'm not quite sure how transactions get committed to the SQL
DB. It seems as if they linger in the log for some time before being
committed.

How can I keep my LOG file and DB file from growing out of
proportion. I really don't see the need for the files to be twice as
big as the data in them. I would think that would lead to poor
performance.

I've read the BOL on dbcc shrinkfile and 'backup log' with
truncate_file (seen here: http://support.microsoft.com/kb/272318)

When I shrank the log it shrunk a LOT (the data in the log, not the
logsize itself).. and then backup log truncated all the free space off
the end.

I'm just hoping when I did it I didn't lose any uncommitted
transactions? I can't seem to find anything that really states how
long transactions sit uncommitted.

.

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)
  • 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: Problem with large transactionfile.
    ... Full backup of the database does not truncate log file. ... If you see transactions with status=2 at the end of the list you will need ...
    (microsoft.public.sqlserver.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)