February 18, 2018 at 1:11 pm
Hi,
I am currently using Ola Hallengren's backup solution for my backups on a SQL Server 2016 Standard instance. I have a full backup one day a week, differential every other day of the week, then hourly t-log backups. There are two hours each morning--6am and 7am--that the log backups are like 3000 times the size of what the t-log backups normally are. It seems to be a pattern and I'm trying to figure out what's going on in the system at these hours that's causing such large logs to be created. I have way more of a database developer background than administrator, so I'm curious to know what might be the best way to find out what's causing this. I'm not sure if there's a way to retroactively see what was happening at those times or if I have to catch the activity in the moment. There's so much information and so many suggested queries to run to diagnose, I'm not sure which to pay attention to and which are false positives. Does anyone have any ideas on a good way to approach this?
Thank you in advance,
Mike
EDIT: I should also add a few more details:
1) The diff job that runs six out of seven days of the week has the following steps:
STEP_NUMBER STEP_NAME
1 DatabaseBackup - USER_DATABASES - DIFF
2 DatabaseBackup - SYSTEM_DATABASES - FULL
2) The full backup that runs once a week has the following steps:
STEP_NUMBER STEP_NAME
1 sp_delete_backuphistory
2 sp_purge_jobhistory
3 CommandLog Cleanup
4 Output File Cleanup
5 DatabaseIntegrityCheck - USER_DATABASES
6 IndexOptimize - USER_DATABASES
7 DatabaseBackup - USER_DATABASES - FULL
8 DatabaseIntegrityCheck - SYSTEM_DATABASES
9 DatabaseBackup - SYSTEM_DATABASES - FULL
The t-log backups that are quite large happen to be in the couple of hours following these jobs. I thought that perhaps the index optimization might be causing the subsequent two backups to be large, but the index optimization is only happening on one day in the week while the two large t-log backups are happening every day. I can't rule this out completely, but it seems odd that every day I'd need to plan for two gigantic transaction log backups because of db maintenance while the others t-log backups are a much smaller and more manageable...then again, I might be way wrong and just need to expand the disk size...
Mike Scalise, PMP
https://www.michaelscalise.com
February 18, 2018 at 2:36 pm
Okay, I'm not a DBA, so take this with a grain of salt.
What if you had a table that held the size of the log over the course of the day...CREATE TABLE LogSizeStats (
LogSize DECIMAL(10,2),
TimeCheck DATETIME
);
GO
Then executed something like this in a job that ran every N minutes...INSERT INTO LogSizeStats (LogSize, TimeCheck)
SELECT
GETDATE() AS LogTime
,(total_log_size_in_bytes - used_log_space_in_bytes)*1.0/1024/1024 AS [free log space in MB]
FROM sys.dm_db_log_space_usage;
Then you'd have to correlate that with what's going on on your server. What's running when - using Profiler or something like it.(Can this be done with Extended Events?)
Then you'd just correlate the growth spikes with stored procedures etc are running just before the spike happens.
February 18, 2018 at 4:30 pm
Mike Scalise - Sunday, February 18, 2018 1:11 PMHi,I am currently using Ola Hallengren's backup solution for my backups on a SQL Server 2016 Standard instance. I have a full backup one day a week, differential every other day of the week, then hourly t-log backups. There are two hours each morning--6am and 7am--that the log backups are like 3000 times the size of what the t-log backups normally are. It seems to be a pattern and I'm trying to figure out what's going on in the system at these hours that's causing such large logs to be created. I have way more of a database developer background than administrator, so I'm curious to know what might be the best way to find out what's causing this. I'm not sure if there's a way to retroactively see what was happening at those times or if I have to catch the activity in the moment. There's so much information and so many suggested queries to run to diagnose, I'm not sure which to pay attention to and which are false positives. Does anyone have any ideas on a good way to approach this?
Thank you in advance,
Mike
EDIT: I should also add a few more details:
1) The diff job that runs six out of seven days of the week has the following steps:
STEP_NUMBER STEP_NAME
1 DatabaseBackup - USER_DATABASES - DIFF
2 DatabaseBackup - SYSTEM_DATABASES - FULL2) The full backup that runs once a week has the following steps:
STEP_NUMBER STEP_NAME
1 sp_delete_backuphistory
2 sp_purge_jobhistory
3 CommandLog Cleanup
4 Output File Cleanup
5 DatabaseIntegrityCheck - USER_DATABASES
6 IndexOptimize - USER_DATABASES
7 DatabaseBackup - USER_DATABASES - FULL
8 DatabaseIntegrityCheck - SYSTEM_DATABASES
9 DatabaseBackup - SYSTEM_DATABASES - FULLThe t-log backups that are quite large happen to be in the couple of hours following these jobs. I thought that perhaps the index optimization might be causing the subsequent two backups to be large, but the index optimization is only happening on one day in the week while the two large t-log backups are happening every day. I can't rule this out completely, but it seems odd that every day I'd need to plan for two gigantic transaction log backups because of db maintenance while the others t-log backups are a much smaller and more manageable...then again, I might be way wrong and just need to expand the disk size...
Have you checked the SQL Server Agent jobs to see if any other jobs are running at those times?
--Jeff Moden
Change is inevitable... Change for the better is not.
February 19, 2018 at 12:47 pm
pietlinden - Sunday, February 18, 2018 2:36 PMOkay, I'm not a DBA, so take this with a grain of salt.
What if you had a table that held the size of the log over the course of the day...CREATE TABLE LogSizeStats (
LogSize DECIMAL(10,2),
TimeCheck DATETIME
);
GO
Then executed something like this in a job that ran every N minutes...INSERT INTO LogSizeStats (LogSize, TimeCheck)
SELECT
GETDATE() AS LogTime
,(total_log_size_in_bytes - used_log_space_in_bytes)*1.0/1024/1024 AS [free log space in MB]
FROM sys.dm_db_log_space_usage;Then you'd have to correlate that with what's going on on your server. What's running when - using Profiler or something like it.(Can this be done with Extended Events?)
Then you'd just correlate the growth spikes with stored procedures etc are running just before the spike happens.
Thanks for the suggestion! I'm actually thinking of doing something like this with sp_WhoIsActive...running it every minute or so and logging activity to help get a look into what's going on at a given period of time...
Mike Scalise, PMP
https://www.michaelscalise.com
February 19, 2018 at 12:52 pm
Jeff Moden - Sunday, February 18, 2018 4:30 PMMike Scalise - Sunday, February 18, 2018 1:11 PMHi,I am currently using Ola Hallengren's backup solution for my backups on a SQL Server 2016 Standard instance. I have a full backup one day a week, differential every other day of the week, then hourly t-log backups. There are two hours each morning--6am and 7am--that the log backups are like 3000 times the size of what the t-log backups normally are. It seems to be a pattern and I'm trying to figure out what's going on in the system at these hours that's causing such large logs to be created. I have way more of a database developer background than administrator, so I'm curious to know what might be the best way to find out what's causing this. I'm not sure if there's a way to retroactively see what was happening at those times or if I have to catch the activity in the moment. There's so much information and so many suggested queries to run to diagnose, I'm not sure which to pay attention to and which are false positives. Does anyone have any ideas on a good way to approach this?
Thank you in advance,
Mike
EDIT: I should also add a few more details:
1) The diff job that runs six out of seven days of the week has the following steps:
STEP_NUMBER STEP_NAME
1 DatabaseBackup - USER_DATABASES - DIFF
2 DatabaseBackup - SYSTEM_DATABASES - FULL2) The full backup that runs once a week has the following steps:
STEP_NUMBER STEP_NAME
1 sp_delete_backuphistory
2 sp_purge_jobhistory
3 CommandLog Cleanup
4 Output File Cleanup
5 DatabaseIntegrityCheck - USER_DATABASES
6 IndexOptimize - USER_DATABASES
7 DatabaseBackup - USER_DATABASES - FULL
8 DatabaseIntegrityCheck - SYSTEM_DATABASES
9 DatabaseBackup - SYSTEM_DATABASES - FULLThe t-log backups that are quite large happen to be in the couple of hours following these jobs. I thought that perhaps the index optimization might be causing the subsequent two backups to be large, but the index optimization is only happening on one day in the week while the two large t-log backups are happening every day. I can't rule this out completely, but it seems odd that every day I'd need to plan for two gigantic transaction log backups because of db maintenance while the others t-log backups are a much smaller and more manageable...then again, I might be way wrong and just need to expand the disk size...
Have you checked the SQL Server Agent jobs to see if any other jobs are running at those times?
Jeff,
I have looked at the other jobs and unfortunately there isn't another one that's running at the same time (or close to the same time). I do have to alter my original statement, though:
"...two large t-log backups are happening every day"
It looks like the two large t-logs are happening after the integrity check and index re-orgs/re-builds on the one day a week that I do those things and not every day. So I'm wondering if it points more to those operations than anything else... If that's the case, it doesn't seem there'd be anything to do. It's not that I'd want to omit those steps or anything, and maybe I just have to live with the fact that I'll have two gigantic t-log backups each week, followed by a ton of normal sized ones...
Mike Scalise, PMP
https://www.michaelscalise.com
February 19, 2018 at 1:18 pm
Mike Scalise - Monday, February 19, 2018 12:52 PMJeff Moden - Sunday, February 18, 2018 4:30 PMMike Scalise - Sunday, February 18, 2018 1:11 PMHi,I am currently using Ola Hallengren's backup solution for my backups on a SQL Server 2016 Standard instance. I have a full backup one day a week, differential every other day of the week, then hourly t-log backups. There are two hours each morning--6am and 7am--that the log backups are like 3000 times the size of what the t-log backups normally are. It seems to be a pattern and I'm trying to figure out what's going on in the system at these hours that's causing such large logs to be created. I have way more of a database developer background than administrator, so I'm curious to know what might be the best way to find out what's causing this. I'm not sure if there's a way to retroactively see what was happening at those times or if I have to catch the activity in the moment. There's so much information and so many suggested queries to run to diagnose, I'm not sure which to pay attention to and which are false positives. Does anyone have any ideas on a good way to approach this?
Thank you in advance,
Mike
EDIT: I should also add a few more details:
1) The diff job that runs six out of seven days of the week has the following steps:
STEP_NUMBER STEP_NAME
1 DatabaseBackup - USER_DATABASES - DIFF
2 DatabaseBackup - SYSTEM_DATABASES - FULL2) The full backup that runs once a week has the following steps:
STEP_NUMBER STEP_NAME
1 sp_delete_backuphistory
2 sp_purge_jobhistory
3 CommandLog Cleanup
4 Output File Cleanup
5 DatabaseIntegrityCheck - USER_DATABASES
6 IndexOptimize - USER_DATABASES
7 DatabaseBackup - USER_DATABASES - FULL
8 DatabaseIntegrityCheck - SYSTEM_DATABASES
9 DatabaseBackup - SYSTEM_DATABASES - FULLThe t-log backups that are quite large happen to be in the couple of hours following these jobs. I thought that perhaps the index optimization might be causing the subsequent two backups to be large, but the index optimization is only happening on one day in the week while the two large t-log backups are happening every day. I can't rule this out completely, but it seems odd that every day I'd need to plan for two gigantic transaction log backups because of db maintenance while the others t-log backups are a much smaller and more manageable...then again, I might be way wrong and just need to expand the disk size...
Have you checked the SQL Server Agent jobs to see if any other jobs are running at those times?
Jeff,
I have looked at the other jobs and unfortunately there isn't another one that's running at the same time (or close to the same time). I do have to alter my original statement, though:
"...two large t-log backups are happening every day"
It looks like the two large t-logs are happening after the integrity check and index re-orgs/re-builds on the one day a week that I do those things and not every day. So I'm wondering if it points more to those operations than anything else... If that's the case, it doesn't seem there'd be anything to do. It's not that I'd want to omit those steps or anything, and maybe I just have to live with the fact that I'll have two gigantic t-log backups each week, followed by a ton of normal sized ones...
Index Reorgs are an absolute log pig no matter the Recovery Model. Index rebuilds provide a double whammy in the Full Recovery model because, for any index over 128 extents (that's just 8MB), the old index will stay in place while the new one rebuilds unless you get tricky a bit plus the rebuilds are fully logged in the Full Recovery model.
--Jeff Moden
Change is inevitable... Change for the better is not.
February 19, 2018 at 2:00 pm
Mike Scalise - Monday, February 19, 2018 12:47 PMpietlinden - Sunday, February 18, 2018 2:36 PMThen you'd have to correlate that with what's going on on your server. What's running when - using Profiler or something like it.(Can this be done with Extended Events?)Then you'd just correlate the growth spikes with stored procedures etc are running just before the spike happens.
Thanks for the suggestion! I'm actually thinking of doing something like this with sp_WhoIsActive...running it every minute or so and logging activity to help get a look into what's going on at a given period of time...
Are the transaction log files growing during these times or are they always big and just filling up more over these hours? If the transaction log files are growing, that information actually gets captured in the default trace (EventClass 92 = Data File, 93 = Log File), which you can see like this:DECLARE @path NVARCHAR(260);
SELECT @path = REVERSE(SUBSTRING(REVERSE([path]), CHARINDEX('\', REVERSE([path])), 260)) + N'log.trc'
FROM sys.traces
WHERE is_default = 1;
SELECT td.DatabaseName, td.Filename, te.name AS Event, (IntegerData*8)/1024 AS Change_MB, td.StartTime, td.EndTime,
td.LoginName, td.HostName, td.ApplicationName, td.spid, td.ClientProcessID, td.IsSystem, td.SqlHandle, td.TextData
FROM sys.fn_trace_gettable(@path, DEFAULT) td
INNER JOIN sys.trace_events te ON td.EventClass = te.trace_event_id
WHERE td.EventClass IN (92,93)
ORDER BY td.StartTime;
February 20, 2018 at 7:25 am
Jeff Moden - Monday, February 19, 2018 1:18 PMMike Scalise - Monday, February 19, 2018 12:52 PMJeff Moden - Sunday, February 18, 2018 4:30 PMMike Scalise - Sunday, February 18, 2018 1:11 PMHi,I am currently using Ola Hallengren's backup solution for my backups on a SQL Server 2016 Standard instance. I have a full backup one day a week, differential every other day of the week, then hourly t-log backups. There are two hours each morning--6am and 7am--that the log backups are like 3000 times the size of what the t-log backups normally are. It seems to be a pattern and I'm trying to figure out what's going on in the system at these hours that's causing such large logs to be created. I have way more of a database developer background than administrator, so I'm curious to know what might be the best way to find out what's causing this. I'm not sure if there's a way to retroactively see what was happening at those times or if I have to catch the activity in the moment. There's so much information and so many suggested queries to run to diagnose, I'm not sure which to pay attention to and which are false positives. Does anyone have any ideas on a good way to approach this?
Thank you in advance,
Mike
EDIT: I should also add a few more details:
1) The diff job that runs six out of seven days of the week has the following steps:
STEP_NUMBER STEP_NAME
1 DatabaseBackup - USER_DATABASES - DIFF
2 DatabaseBackup - SYSTEM_DATABASES - FULL2) The full backup that runs once a week has the following steps:
STEP_NUMBER STEP_NAME
1 sp_delete_backuphistory
2 sp_purge_jobhistory
3 CommandLog Cleanup
4 Output File Cleanup
5 DatabaseIntegrityCheck - USER_DATABASES
6 IndexOptimize - USER_DATABASES
7 DatabaseBackup - USER_DATABASES - FULL
8 DatabaseIntegrityCheck - SYSTEM_DATABASES
9 DatabaseBackup - SYSTEM_DATABASES - FULLThe t-log backups that are quite large happen to be in the couple of hours following these jobs. I thought that perhaps the index optimization might be causing the subsequent two backups to be large, but the index optimization is only happening on one day in the week while the two large t-log backups are happening every day. I can't rule this out completely, but it seems odd that every day I'd need to plan for two gigantic transaction log backups because of db maintenance while the others t-log backups are a much smaller and more manageable...then again, I might be way wrong and just need to expand the disk size...
Have you checked the SQL Server Agent jobs to see if any other jobs are running at those times?
Jeff,
I have looked at the other jobs and unfortunately there isn't another one that's running at the same time (or close to the same time). I do have to alter my original statement, though:
"...two large t-log backups are happening every day"
It looks like the two large t-logs are happening after the integrity check and index re-orgs/re-builds on the one day a week that I do those things and not every day. So I'm wondering if it points more to those operations than anything else... If that's the case, it doesn't seem there'd be anything to do. It's not that I'd want to omit those steps or anything, and maybe I just have to live with the fact that I'll have two gigantic t-log backups each week, followed by a ton of normal sized ones...
Index Reorgs are an absolute log pig no matter the Recovery Model. Index rebuilds provide a double whammy in the Full Recovery model because, for any index over 128 extents (that's just 8MB), the old index will stay in place while the new one rebuilds unless you get tricky a bit plus the rebuilds are fully logged in the Full Recovery model.
Jeff,
Thanks for the information. Are you referring to online index rebuilds as the double whammy? If so, isn't that only an Enterprise feature? Regardless, are the old ones deleted automatically once the new ones are created? I know you said it's all fully logged, so is there any good way to prevent some of this extra activity from bloating the t-log backups? I know you said there's ways to do some trickery, but I'm just really wanting to do what makes sense and is safe...If there's nothing more to do, then so be it...
Thanks,
Mike
Mike Scalise, PMP
https://www.michaelscalise.com
February 20, 2018 at 7:31 am
Chris Harshman - Monday, February 19, 2018 2:00 PMMike Scalise - Monday, February 19, 2018 12:47 PMpietlinden - Sunday, February 18, 2018 2:36 PMThen you'd have to correlate that with what's going on on your server. What's running when - using Profiler or something like it.(Can this be done with Extended Events?)Then you'd just correlate the growth spikes with stored procedures etc are running just before the spike happens.
Thanks for the suggestion! I'm actually thinking of doing something like this with sp_WhoIsActive...running it every minute or so and logging activity to help get a look into what's going on at a given period of time...
Are the transaction log files growing during these times or are they always big and just filling up more over these hours? If the transaction log files are growing, that information actually gets captured in the default trace (EventClass 92 = Data File, 93 = Log File), which you can see like this:
DECLARE @path NVARCHAR(260);
SELECT @path = REVERSE(SUBSTRING(REVERSE([path]), CHARINDEX('\', REVERSE([path])), 260)) + N'log.trc'
FROM sys.traces
WHERE is_default = 1;SELECT td.DatabaseName, td.Filename, te.name AS Event, (IntegerData*8)/1024 AS Change_MB, td.StartTime, td.EndTime,
td.LoginName, td.HostName, td.ApplicationName, td.spid, td.ClientProcessID, td.IsSystem, td.SqlHandle, td.TextData
FROM sys.fn_trace_gettable(@path, DEFAULT) td
INNER JOIN sys.trace_events te ON td.EventClass = te.trace_event_id
WHERE td.EventClass IN (92,93)
ORDER BY td.StartTime;
Chris,
Thank you. This is very interesting. Here are my results for the database in question. I'm not exactly sure how to interpret this. Can you help me understand what it indicates?
DatabaseName Filename Event Change_MB StartTime EndTime
mydatabase mydatabase_log01 Log File Auto Grow 2149 2018-02-09 23:22:16.750 2018-02-09 23:22:44.800
mydatabase mydatabase_log01 Log File Auto Grow 2364 2018-02-11 05:17:02.333 2018-02-11 05:17:37.237
mydatabase mydatabase_log01 Log File Auto Grow 2601 2018-02-11 05:41:17.990 2018-02-11 05:41:54.767
mydatabase mydatabase_log01 Log File Auto Grow 2861 2018-02-11 05:54:54.140 2018-02-11 05:55:34.870
mydatabase mydatabase_log01 Log File Auto Grow 3147 2018-02-11 06:12:02.763 2018-02-11 06:12:48.667
mydatabase mydatabase_log01 Log File Auto Grow 3462 2018-02-11 06:35:04.557 2018-02-11 06:35:54.093
mydatabase mydatabase_log01 Log File Auto Grow 3808 2018-02-12 23:24:10.850 2018-02-12 23:25:04.240
mydatabase mydatabase_log01 Log File Auto Grow 4189 2018-02-15 23:21:57.480 2018-02-15 23:22:52.953
mydatabase mydatabase_log01 Log File Auto Grow 4608 2018-02-18 05:47:27.050 2018-02-18 05:48:29.620
mydatabase mydatabase_log01 Log File Auto Grow 5069 2018-02-18 06:13:41.223 2018-02-18 06:14:55.630
Mike Scalise, PMP
https://www.michaelscalise.com
February 20, 2018 at 8:16 am
Did the query return anything in the columns LoginName or ApplicationName? That would help pinpoint who or what is happening that caused the log to grow. With the results you show here, you at least have the time the log file grew, but you still would need to match it up to what was executing then. If it's a scheduled SQL Agent job, you would see something like this for ApplicationName:
SQLAgent - TSQL JobStep (Job 0x475A3D830555AE4F854CCB63761ED284 : Step 1)
And that job id can be matched to MSDB tables like this:SELECT * FROM dbo.sysjobs
WHERE job_id = 0x475A3D830555AE4F854CCB63761ED284
February 20, 2018 at 8:26 am
Mike Scalise - Tuesday, February 20, 2018 7:31 AMChris,Thank you. This is very interesting. Here are my results for the database in question. I'm not exactly sure how to interpret this. Can you help me understand what it indicates?
DatabaseName Filename Event Change_MB StartTime EndTime
mydatabase mydatabase_log01 Log File Auto Grow 2149 2018-02-09 23:22:16.750 2018-02-09 23:22:44.800
mydatabase mydatabase_log01 Log File Auto Grow 2364 2018-02-11 05:17:02.333 2018-02-11 05:17:37.237
mydatabase mydatabase_log01 Log File Auto Grow 2601 2018-02-11 05:41:17.990 2018-02-11 05:41:54.767
mydatabase mydatabase_log01 Log File Auto Grow 2861 2018-02-11 05:54:54.140 2018-02-11 05:55:34.870
mydatabase mydatabase_log01 Log File Auto Grow 3147 2018-02-11 06:12:02.763 2018-02-11 06:12:48.667
mydatabase mydatabase_log01 Log File Auto Grow 3462 2018-02-11 06:35:04.557 2018-02-11 06:35:54.093
mydatabase mydatabase_log01 Log File Auto Grow 3808 2018-02-12 23:24:10.850 2018-02-12 23:25:04.240
mydatabase mydatabase_log01 Log File Auto Grow 4189 2018-02-15 23:21:57.480 2018-02-15 23:22:52.953
mydatabase mydatabase_log01 Log File Auto Grow 4608 2018-02-18 05:47:27.050 2018-02-18 05:48:29.620
mydatabase mydatabase_log01 Log File Auto Grow 5069 2018-02-18 06:13:41.223 2018-02-18 06:14:55.630
Above results show the logfile of database [mydatabase] is growing roughly every other day. This is not normal behavior unless the database is new and did not had a full load yet. In a normal situation the logfile will grow to a certain size (over a few days or maybe a month) and stay at that size. Each time a log back-up is taken, all logging about finished transactions is flushed from the file and that space will be re-used by future transactions.
So can you make sure:
* are log backups taken on this database?
* when was the last log backup taken?
* is the logfile being shrunk periodically? --> if so: turn it off !!
February 20, 2018 at 9:14 am
Mike Scalise - Tuesday, February 20, 2018 7:25 AMJeff Moden - Monday, February 19, 2018 1:18 PMMike Scalise - Monday, February 19, 2018 12:52 PMJeff Moden - Sunday, February 18, 2018 4:30 PMMike Scalise - Sunday, February 18, 2018 1:11 PMHi,I am currently using Ola Hallengren's backup solution for my backups on a SQL Server 2016 Standard instance. I have a full backup one day a week, differential every other day of the week, then hourly t-log backups. There are two hours each morning--6am and 7am--that the log backups are like 3000 times the size of what the t-log backups normally are. It seems to be a pattern and I'm trying to figure out what's going on in the system at these hours that's causing such large logs to be created. I have way more of a database developer background than administrator, so I'm curious to know what might be the best way to find out what's causing this. I'm not sure if there's a way to retroactively see what was happening at those times or if I have to catch the activity in the moment. There's so much information and so many suggested queries to run to diagnose, I'm not sure which to pay attention to and which are false positives. Does anyone have any ideas on a good way to approach this?
Thank you in advance,
Mike
EDIT: I should also add a few more details:
1) The diff job that runs six out of seven days of the week has the following steps:
STEP_NUMBER STEP_NAME
1 DatabaseBackup - USER_DATABASES - DIFF
2 DatabaseBackup - SYSTEM_DATABASES - FULL2) The full backup that runs once a week has the following steps:
STEP_NUMBER STEP_NAME
1 sp_delete_backuphistory
2 sp_purge_jobhistory
3 CommandLog Cleanup
4 Output File Cleanup
5 DatabaseIntegrityCheck - USER_DATABASES
6 IndexOptimize - USER_DATABASES
7 DatabaseBackup - USER_DATABASES - FULL
8 DatabaseIntegrityCheck - SYSTEM_DATABASES
9 DatabaseBackup - SYSTEM_DATABASES - FULLThe t-log backups that are quite large happen to be in the couple of hours following these jobs. I thought that perhaps the index optimization might be causing the subsequent two backups to be large, but the index optimization is only happening on one day in the week while the two large t-log backups are happening every day. I can't rule this out completely, but it seems odd that every day I'd need to plan for two gigantic transaction log backups because of db maintenance while the others t-log backups are a much smaller and more manageable...then again, I might be way wrong and just need to expand the disk size...
Have you checked the SQL Server Agent jobs to see if any other jobs are running at those times?
Jeff,
I have looked at the other jobs and unfortunately there isn't another one that's running at the same time (or close to the same time). I do have to alter my original statement, though:
"...two large t-log backups are happening every day"
It looks like the two large t-logs are happening after the integrity check and index re-orgs/re-builds on the one day a week that I do those things and not every day. So I'm wondering if it points more to those operations than anything else... If that's the case, it doesn't seem there'd be anything to do. It's not that I'd want to omit those steps or anything, and maybe I just have to live with the fact that I'll have two gigantic t-log backups each week, followed by a ton of normal sized ones...
Index Reorgs are an absolute log pig no matter the Recovery Model. Index rebuilds provide a double whammy in the Full Recovery model because, for any index over 128 extents (that's just 8MB), the old index will stay in place while the new one rebuilds unless you get tricky a bit plus the rebuilds are fully logged in the Full Recovery model.
Jeff,
Thanks for the information. Are you referring to online index rebuilds as the double whammy? If so, isn't that only an Enterprise feature? Regardless, are the old ones deleted automatically once the new ones are created? I know you said it's all fully logged, so is there any good way to prevent some of this extra activity from bloating the t-log backups? I know you said there's ways to do some trickery, but I'm just really wanting to do what makes sense and is safe...If there's nothing more to do, then so be it...
Thanks,
Mike
No. Online index rebuilds provide a triple whammy because they build a third behind-the-scenes table to remember all of the transactions while the index rebuild is in process.
On the "trickery" thing, I'll try to provide something more detailed after work tonight.
--Jeff Moden
Change is inevitable... Change for the better is not.
February 20, 2018 at 9:15 am
Two things. One, it appears that your log growth is set to a percentage rather than a fixed size. Second, you may want to look at how many virtual log files (vlf's) you have. I don't have the link handing for this second one but I am sure someone out there will provide it.
February 20, 2018 at 10:38 am
Here's a link to a script to find the count of VLFs per database:
https://gallery.technet.microsoft.com/scriptcenter/SQL-Script-to-list-VLF-e6315249
February 20, 2018 at 11:51 am
First make sure your transaction log is reasonably sized, unless it's filling your disks you don't want to be constantly resizing it down. A good starting value is ~ 25% of the data size if it's a large database, make sure you are growing in MB not %.
To resolve this problem you have a couple options:
#1 Add a secondary schedule to your log backup for ~ 10 minutes and disable it, next add two steps to your index maintenance job, one right before Ola is called that enables the 10 minute log backup schedule and one right after Ola's step that disables the 10 minute schedule (call sp_update_schedule and set enabled to 0 to disable and 1 to enable). This will cause you to backup logs every 10 minutes during index maintenance and every hour when you aren't doing index maintenance. This will help keep your log from filling up and having to grow.
#2 To decrease transaction log usage you could switch to BULK LOGGED during index maintenance, similar approach to #1 above, you add a step right before Ola's step that sets recovery to BULK LOGGED and a step right after that sets it to FULL, as soon as you go back to full you want to run a TLOG backup, you can do that by adding another step that calls sp_start_job to start your transaction log backup.
You technically can do both #1 and #2 if you need to, I'd start with #1.
Viewing 15 posts - 1 through 15 (of 20 total)
You must be logged in to reply to this topic. Login to reply