December 20, 2013 at 7:32 am
Hi,
We run a tran log backup on a 2008 Std Edition instance for each DB in full recovery mode, every 1 hour. We also run 'update statistics' at 4am each day & 3:30am at weekends. When I view the output of the update stats (as we print a listing) and look at the stats systems tables, I can see they all update fine on the 3:30am run. However for the 4am run, when it clashes with the tran log backup, the stats job updates the stats for the simple recovery mode DBs but when it gets to the first full recovery mode DB, the Agent job completes ok but it doesn't update the stats for that DB (first full recovery DB) or subsequent ones. Again I can see this from the output and stats tables.
Now I can, and will, alter the stats job timing so it doesnt run on the hour, but I was interested to know why this actually happens. The code we use to update the stats is a cursor (BELOW)
The DBs are small so a fullscan is no problem. We have the same issue on other servers. I'd just like to know whats going on under the hood to prevent it updating the stats when a tran log is running.
DECLARE UPDATESTATS CURSOR FOR
SELECT name FROM master.sys.databases where database_id > 4 and state = 0 and is_read_only = 0
OPEN UPDATESTATS
FETCH NEXT FROM UPDATESTATS INTO @dbname
WHILE (@@FETCH_STATUS = 0)
BEGIN
If @dbname is null
Begin
Print 'Null Value'
End
Else
Begin
PRINT getdate()
PRINT 'Update Statistics in ' + @dbname
SELECT @execmd = 'USE ' + @dbname + ' Exec sp_MSforeachtable ''Update Statistics ? with FULLSCAN'''
EXEC(@execmd)
PRINT ''
End
FETCH NEXT FROM UPDATESTATS INTO @dbname
END
Thank-you
December 20, 2013 at 11:19 am
Not sure why it would fail. The worst thing that would happen is you're blocked for the duration of the backup process, but even that shouldn't cause any issues. Are you seeing an error at any point, in the log or anywhere else? Does your backup kill connections or something prior to running? That's not the kind of behavior I would expect.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
December 20, 2013 at 11:36 am
I have had issues where a reindex/update stats would cause a t-log backup from occurring. So what I do is disable the t-log backup before the reindex begins then enable it when it is done.
This code shuts the job off:
declare @Job_Id uniqueidentifier
declare @Name sysname
declare @Enabled tinyint
declare @On tinyint = 1
declare @Off tinyint = 0
SELECT top 1 @Job_Id = job_id
,@Name = [name]
,@Enabled = [enabled]
FROM [msdb].[dbo].[sysjobs]
WHERE [name] LIKE '%Transaction%'
and [name] LIKE '%Log%'
and [name] like '%Backup%'
if @Job_Id is not null
begin
EXEC msdb.dbo.sp_update_job
@job_Id = @Job_Id,
@enabled = @Off;
end
This code turns it on:
declare @Job_Id uniqueidentifier
declare @Name sysname
declare @Enabled tinyint
declare @On tinyint = 1
declare @Off tinyint = 0
SELECT top 1 @Job_Id = job_id
,@Name = [name]
,@Enabled = [enabled]
FROM [msdb].[dbo].[sysjobs]
WHERE [name] LIKE '%Transaction%'
and [name] LIKE '%Log%'
and [name] like '%Backup%'
if @Job_Id is not null
begin
EXEC msdb.dbo.sp_update_job
@job_Id = @Job_Id,
@enabled = @On;
end
I have 2 TSQL objects in my Maintenance Plan, one before the Rebuild Index and one after. Also the connector between the Rebuild Indexes and Turn On T-Log backup is set to "Completion" and not the default "Success". This insures the t-log backup job will always be turned on regardless if the Rebuild Indexes is successful or if it fails.
Kurt
Kurt W. Zimmerman
SR DBA
Lefrak Organization
New York, NY
http://www.linkedin.com/in/kurtwzimmerman
December 23, 2013 at 2:47 am
Thanks for your input both. The scripts look great but I'm keen to understand the underlying cause too. I'm not getting any errors and have even traced it using Profiler, it just ends by updating the last of the simple mode DBs then just ends normally??
December 23, 2013 at 7:25 am
Hi Simon,
I have experienced a similar problem and it was down to the type of cursor being used. Some cursors will reflect data changes made to the rows in the result set as you move through the cursor meaning some rows are no longer visible. For example, a transaction log backup can result in sys.databases being updated (the log_reuse_wait and log_reuse_wait_desc columns can be updated e.g., from 'LOG_BACKUP' to 'NOTHING'), resulting in the underlying row being updated (i.e. deleted and inserted at the row level) by SQL, and hence no longer visible to the cursor.
BOL under DECLARE CURSOR explains:
Changes to nonkey values in the base tables, either made by the cursor owner or committed by other users, are visible as the owner scrolls around the cursor. If a row is deleted, an attempt to fetch the row returns an @@FETCH_STATUS of -2. Updates of key values from outside the cursor resemble a delete of the old row followed by an insert of the new row. The row with the new values is not visible, and attempts to fetch the row with the old values return an @@FETCH_STATUS of -2.
In our case, we monitored @@FETCH_STATUS before and after the final FETCH NEXT statement in our cursor and found it returned -2 and exited the cursor when a non-key column in the base table was updated while the cursor was still running (to a row it hadn't yet selected). We then added FAST_FORWARD to the cursor definition which forces the cursor to write it's input result-set to tempdb and changes to the underlying base table were no longer no reflected in the cursor input result-set.
Hope this helps.
Jason
December 23, 2013 at 8:08 am
I just thought I'd further add that I came across an application where my nightly index rebuild would fail ever so often. Turns out the application would produce temporary work tables my index job was picking up. As a good application should operate these temporary work tables would then be deleted after they were no longer used. Well my index job would pick these tables up but when it came time for the indexes were to be rebuilt the tables were gone, causing my job to fail.
To resolve this I simply put in a check to see if the table existed before performing the index rebuild. It solved my problem.
The initial creation of the list of tables within the database presented nothing where by I would have excluded them in the first place. Otherwise I would have selected out those temporary tables.
FWIW.
Kurt
Kurt W. Zimmerman
SR DBA
Lefrak Organization
New York, NY
http://www.linkedin.com/in/kurtwzimmerman
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply