Can anyone 'crash course' me in SQL maintenance?

Tech Tip: Click here to run a free scan for Windows Errors and optimize PC performance

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.

-Ryan
.

Relevant Pages

  • Re: Can anyone crash course me in SQL maintenance?
    ... Let's see if we can get an SQL expert to guide you ... ... I use redgate's SQL Backup to do a nightly backup, ... Log file size: 18g ... log as I'm not quite sure how transactions get committed to the SQL ...
    (microsoft.public.windows.server.sbs)
  • Re: Database/Logs dont shrink with Maintenance Task
    ... Only a shrink file can do that but it may require a backup to get ... Other wise it has to keep growing to make room for more transactions. ... the log file smaller--whether you shrink or not? ...
    (microsoft.public.sqlserver.setup)
  • Re: does this backup sequence commit all data to the database
    ... Then you shrink the log file and backup the database. ... space taken up by committed transactions is freed, ... I understand that "backup log testdb to disk = '...'" will backup the ...
    (comp.databases.ms-sqlserver)
  • Re: truncate transaction log
    ... The physical size of the log file will not shrink when you truncate the ... size that will hold all of the transactions in between log backups. ... time the log backup will truncate the committed transactions ...
    (microsoft.public.sqlserver.server)
  • Re: transaction log backup & file size
    ... of 'system transactions' (only guessing, ... Need smaller SQL2K backup files? ... > alter database BBH set single_user with rollback immediate ... > /*that one breakes log sequence as I understand, ...
    (microsoft.public.sqlserver.server)