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

Tech-Archive recommends: Fix windows errors by optimizing your registry

Let's see if we can get an SQL expert to guide you ...

--
Kevin Weilbacher [SBS MVP]
"The days pass by so quickly now, the nights are seldom long"
*

"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.

-Ryan

.

Relevant Pages

  • Re: Can anyone crash course me in SQL maintenance?
    ... There is nothing you could do to make SQL Server dump logged transactions before they get committed to the data. ... If you take a nightly full db backup, that would leave you with 24 hours of vulnerability to data loss. ... The log file can be much smaller than the data since it only records the transactions that occur. ... So if someone accidentally deletes important data, you can restore a copy of the database exactly as it was 1 second before that mistake. ...
    (microsoft.public.windows.server.sbs)
  • Re: Can anyone crash course me in SQL maintenance?
    ... I use redgate's SQL Backup to do a nightly backup, ... On SQL Server clusters with several hundred databases, ... Now rename the log file. ... Back up the transaction log files regularly to delete the inactive ...
    (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)