A previous DBA has set up good maintenance plans etc. so everything is
being properly backed up (well, I think it is)
A common cause of unruly log files is that the database is in the FULL
recovery model but regular transaction log backups are not scheduled. Since
committed transactions won't get removed from the log until the log backup,
manual action is required to reduce the log size.
If you need to minimize data loss, the best approach is to schedule regular
log backups to run periodically between full database backups. You can
include this as part of a database maintenance plan. If more data loss is
acceptable (i.e. your plan is to simply restore from the last full backup),
you can use the SIMPLE recovery model so that committed data are
automatically removed from the log and you don't need to bother with log
backups.
In any case, the log should be sized to accommodate the activity between log
backups (FULL recovery) or the largest transaction (SIMPLE). The high-water
mark of this log space is simply the cost of doing business. IMHO, log file
shrinking should be done only after the log grows due to unusual activity.
I wouldn't schedule log shrinking because automating the process will only
hide the underlying cause.
Keep in mind that the number one responsibility of a DBA (or one that wears
a DBA hat) is to implement and test a backup/recovery plan. If you haven't
already done so, I suggest you run a restore test. You don't want any
surprises when you need to do it for real.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"William" <wi***********@hotmail.comwrote in message
news:11**********************@i3g2000cwc.googlegro ups.com...
I've been tasked with taking over the support for a client's SQL Server
database. I'm not a DBA by profession, I'm a software developer who
uses SQL Server as a database designer.
The clients have reported that the server is running out of disk space
and examination shows that the log files for several of the databases
are at 5Gb or more.
After reading around the subject I suggested the following sequence of
operations:
-- Select the name of the database you want to shrink
USE MyDB
-- Dump unwanted transactions
dump tran MyDB with truncate_only
-- Get the name of the logfile
SELECT * from sysfiles
-- Having examined the rows returned by this use the log file....
-- Shrink the file to required size (in MB)
DBCC SHRINKFILE('MyDB_log', 10)
Is this a reasonable approach? Please bear in mind that I'm pretty new
to this, and I have many other tasks to do besides manage the server.
A previous DBA has set up good maintenance plans etc. so everything is
being properly backed up (well, I think it is)
If this IS a good approach, would it be reasonable to do this on, say,
a monthly basis as a scheduled job? Obviously the step
SELECT * from sysfiles
which gives us the physical name of the log file would be removed and
the job would operate explicitly on each log file for each database in
turn.
Many thanks for reading.
William Balmer.
