Asger Blond
ab-mi at post3.tele.dk
Wed Sep 10 18:09:08 CDT 2008
Just a second comment for Arthur: You wrote: "Now we back up the log (only the log) four times a day and nightly do a dbcc shrinkfile" To my mind a DBCC SHRINKFILE is an emergency command which should NOT be included in an automated administrative task. DBCC SHRINKFILE will free truncated file-space to the operating system. This may be a reasonable action if the log-file has been bloating. Otherwise it certainly is not: freeing truncated file-space to the operation system will force SQL Server to allocate new file-space for the log as needed, and this will be a great invitation for file fragmentation at the disk level. Asger -----Oprindelig meddelelse----- Fra: dba-sqlserver-bounces at databaseadvisors.com [mailto:dba-sqlserver-bounces at databaseadvisors.com] På vegne af Asger Blond Sendt: 11. september 2008 00:18 Til: 'Discussion concerning MS SQL Server' Emne: Re: [dba-SQLServer] full transaction logs Hi Susan and Arthur, The transaction is only truncated if you: 1. Set the database to SIMPLE RECOVERY MODEL. This recovery model equals the obsolete command SET TRUNCATE ON CHECKPOINT ON, meaning that the log is automatically truncated when a checkpoint is performed (normally every 1 minute). However, setting the database to SIMPLE RECOVERY MODEL will not allow you to make backups of the transaction log - so this is not a reasonable choice for an OLTP-database. OR: 2. Perform a manual truncation of the log issuing the command BACKUP LOG <mydatabase> WITH TRUNCATE_ONLY. This command should only be used as an emergency if the database log for some reason is bloating. OR: 3. Perform a log-backup at regular intervals. Notice that only a log-backup will truncate the log, a full or differential backup won't. Of these options I normally would prefer the last for an OLTP-database. If I'm not wrong this may be an answer to Arthur's statement: "I don't know what I'm doing right." Asger -----Oprindelig meddelelse----- Fra: dba-sqlserver-bounces at databaseadvisors.com [mailto:dba-sqlserver-bounces at databaseadvisors.com] På vegne af Arthur Fuller Sendt: 10. september 2008 22:32 Til: Discussion concerning MS SQL Server Emne: Re: [dba-SQLServer] full transaction logs I know that some people have received a message that their transaction log is full, but I personally never have. I don't know what I'm doing right. Recently we changed our strategy on transaction logs. Now we back up the log (only the log) four times a day and nightly do a dbcc shrinkfile, so the living log is peanuts compared to what it used to be. Before I got here there was a nightly process that dropped a table and then recreated it and did thousands of inserts. As a result, the log file was huge -- way larger than the 18 gig db itself. We changed that process dramatically and the log file shrank dramatically too. Arthur On Wed, Sep 10, 2008 at 4:33 PM, Susan Harkins <ssharkins at gmail.com> wrote: > Are full transaction logs still a problem in 2005 and 2008? I'm just > wondering if they've done anything automatic to warn the administrator or > to > avoid the problem altogether. > > Susan H. > _______________________________________________ dba-SQLServer mailing list dba-SQLServer at databaseadvisors.com http://databaseadvisors.com/mailman/listinfo/dba-sqlserver http://www.databaseadvisors.com _______________________________________________ dba-SQLServer mailing list dba-SQLServer at databaseadvisors.com http://databaseadvisors.com/mailman/listinfo/dba-sqlserver http://www.databaseadvisors.com