I have been investigating the rapid consumption of the Active Data pool & found a significant chunk of the utilisation came from TDP for SQL & am seeking to understand why this is the case.
Investigation into the backup (via select * from backups where node_name like '<nameofsqlnode>') for the most recent backup indicaited (I have removed meta in these examples):
Servername Servername\instance\data\0001 4 ACTIVE_VERSION FILE \dbname\log\20150304034632\000016F0.20150304034632000016F0\ 1 1323458584 2015-03-04 03:46:32.000000
Servername Servername\instance\data\0001 4 ACTIVE_VERSION FILE \dbname\log\20150304034632\000016F0\ 1323458583 2015-03-04 03:46:32.000000
It appears both of these log files are the same, with the only diffrence being an appended datestamp on the second one. The day post the full backup, the same files look like this:
Servername Servername\instance\data\0001 4 ACTIVE_VERSION FILE \dbname\log\20150304034632\000016F0.20150304034632000016F0\ 1 1323458584 2015-03-04 03:46:32.000000
Servername Servername\instance\data\0001 4 INACTIVE_VERSION FILE \dbname\log\20150304034632\000016F0\ 1323458583 2015-03-04 03:46:32.000000 2015-03-04 23:58:34
or
Servername Servername\instance\data\0001 4 INACTIVE_VERSION FILE \dbname\full\ 1273669747 2015-01-29 23:31:26.000000 2015-01-30 23:32:43.000000
Servername Servername\instance\data\0001 4 ACTIVE_VERSION FILE \dbname\full.2015012923312600001818\ 1 1273669749 2015-01-29 23:31:26.000000
This same pattern occurs across the board for all 35 Days (the configured retention period) and as some of these systems run log backups every 15 mins it results in a significant amount of data sitting in the active pool.
I also tested with the active pool disabled & see the same behaviour, it also occurs with TDP for SQL 6.4 - 7.1.1.
The documentation does not contain a lot on information on the active/inactive process, I was under the impression that a Full Backup would inactivate all of the backups that occured prior.
Investigation into the backup (via select * from backups where node_name like '<nameofsqlnode>') for the most recent backup indicaited (I have removed meta in these examples):
Servername Servername\instance\data\0001 4 ACTIVE_VERSION FILE \dbname\log\20150304034632\000016F0.20150304034632000016F0\ 1 1323458584 2015-03-04 03:46:32.000000
Servername Servername\instance\data\0001 4 ACTIVE_VERSION FILE \dbname\log\20150304034632\000016F0\ 1323458583 2015-03-04 03:46:32.000000
It appears both of these log files are the same, with the only diffrence being an appended datestamp on the second one. The day post the full backup, the same files look like this:
Servername Servername\instance\data\0001 4 ACTIVE_VERSION FILE \dbname\log\20150304034632\000016F0.20150304034632000016F0\ 1 1323458584 2015-03-04 03:46:32.000000
Servername Servername\instance\data\0001 4 INACTIVE_VERSION FILE \dbname\log\20150304034632\000016F0\ 1323458583 2015-03-04 03:46:32.000000 2015-03-04 23:58:34
or
Servername Servername\instance\data\0001 4 INACTIVE_VERSION FILE \dbname\full\ 1273669747 2015-01-29 23:31:26.000000 2015-01-30 23:32:43.000000
Servername Servername\instance\data\0001 4 ACTIVE_VERSION FILE \dbname\full.2015012923312600001818\ 1 1273669749 2015-01-29 23:31:26.000000
This same pattern occurs across the board for all 35 Days (the configured retention period) and as some of these systems run log backups every 15 mins it results in a significant amount of data sitting in the active pool.
I also tested with the active pool disabled & see the same behaviour, it also occurs with TDP for SQL 6.4 - 7.1.1.
The documentation does not contain a lot on information on the active/inactive process, I was under the impression that a Full Backup would inactivate all of the backups that occured prior.