October 30, 2020 at 12:05 pm
I do the same thing with transaction log backups... if the "log_reuse_wait_desc" in the sys.databases view contains "NOTHING" for a database, then nothing in the database has changed and I skip that backup.
If you combine the latter tidbit of knowledge with your report, it turns out that I have several databases that haven't suffered any modifications for well over 30 days and I'm going to have to get after some people. 😀
Hmm, are you sure about that? Doesn't that flag only tell if you SQL Server was unable to reuse log space that has not yet been backed up? For example, say a log backup occurs and it frees, say, 5GB of log space. If another 2GB of table data is updated, since there is room to write that data to the available log space, wouldn't the reuse wait flag still be 0, even though subsequent table mods have occurred?! That is, as long as a VLF is available to write to, SQL won't flip on the reuse flag.
Or am I misunderstanding how SQL works in this area?
SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.
October 30, 2020 at 3:47 pm
Jeff Moden wrote:I do the same thing with transaction log backups... if the "log_reuse_wait_desc" in the sys.databases view contains "NOTHING" for a database, then nothing in the database has changed and I skip that backup.
If you combine the latter tidbit of knowledge with your report, it turns out that I have several databases that haven't suffered any modifications for well over 30 days and I'm going to have to get after some people. 😀
Hmm, are you sure about that? Doesn't that flag only tell if you SQL Server was unable to reuse log space that has not yet been backed up? For example, say a log backup occurs and it frees, say, 5GB of log space. If another 2GB of table data is updated, since there is room to write that data to the available log space, wouldn't the reuse wait flag still be 0, even though subsequent table mods have occurred?! That is, as long as a VLF is available to write to, SQL won't flip on the reuse flag.
Or am I misunderstanding how SQL works in this area?
If the database is in simple recovery model - I would expect the transaction log to state NOTHING as soon as a checkpoint has occurred. If you are checking to see if you need to backup the transaction log only and not the database - then if it says NOTHING and hasn't changed in more than 30 days and the database is not in simple recovery model...then nothing in the database has changed.
I would not use this method to determine whether or not I needed to backup the database - but it could be used to determine if the transaction log needs to be backed up. With that said...if there are so few transactions in a given day then I would look at changing the database to simple and performing a full once a week and differentials every nn hours instead.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
October 30, 2020 at 5:01 pm
ScottPletcher wrote:Jeff Moden wrote:I do the same thing with transaction log backups... if the "log_reuse_wait_desc" in the sys.databases view contains "NOTHING" for a database, then nothing in the database has changed and I skip that backup.
If you combine the latter tidbit of knowledge with your report, it turns out that I have several databases that haven't suffered any modifications for well over 30 days and I'm going to have to get after some people. 😀
Hmm, are you sure about that? Doesn't that flag only tell if you SQL Server was unable to reuse log space that has not yet been backed up? For example, say a log backup occurs and it frees, say, 5GB of log space. If another 2GB of table data is updated, since there is room to write that data to the available log space, wouldn't the reuse wait flag still be 0, even though subsequent table mods have occurred?! That is, as long as a VLF is available to write to, SQL won't flip on the reuse flag.
Or am I misunderstanding how SQL works in this area?
If the database is in simple recovery model - I would expect the transaction log to state NOTHING as soon as a checkpoint has occurred. If you are checking to see if you need to backup the transaction log only and not the database - then if it says NOTHING and hasn't changed in more than 30 days and the database is not in simple recovery model...then nothing in the database has changed.
I would not use this method to determine whether or not I needed to backup the database - but it could be used to determine if the transaction log needs to be backed up. With that said...if there are so few transactions in a given day then I would look at changing the database to simple and performing a full once a week and differentials every nn hours instead.
Not always correct. You can have a long-running transaction on a db in simple recovery. A checkpoint can only truncate the log of completed transactions.
It's easy enough to test whether a table update causes SQL to set the reuse value to something other than NOTHING:
CREATE DATABASE test_tlog;
ALTER DATABASE [test_tlog] SET RECOVERY SIMPLE;
CREATE TABLE dbo.table1 ( col1 int NULL );
SELECT name, log_reuse_wait_desc FROM sys.databases WHERE name = 'test_tlog';
SET NOCOUNT ON
DECLARE @int int
SET @int = 1
WHILE @int <= 500
BEGIN
UPDATE dbo.table1 SET col1 = @int
SET @int = @int + 1
END /*WHILE*/
SET NOCOUNT OFF
DBCC LOGINFO; --verify that at least one log record is marked as "in use" (Status = 2)
SELECT name, log_reuse_wait_desc FROM sys.databases WHERE name = 'test_tlog';
DROP DATABASE test_tlog;
SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.
November 3, 2020 at 1:52 pm
Thanks.
Viewing 4 posts - 16 through 18 (of 18 total)
You must be logged in to reply to this topic. Login to reply