September 28, 2010 at 7:23 am
Hi everyone,
We currently have a SQL 2008 Enterprise instance chugging along no problem. We recently upgraded it from SQL 2005 to 2008, and the application associated with it tripled it's activity, thus increasing the transaction log sizes and index reorganizations and rebuilds (both which have not been an issue).
The main problem is we have a separate database that acts as a repository for a third party auditing application. The way it does the bulk of it's auditing is that it reads the transaction logs from the audited source and enters that data into it's repository, purging records older than 1.5 years.
Recently, with the upgrade, there was an unusual spike of this repository growth. After digging a bit, we found that the audit was capturing INSERT's (which is good, as it is supposed to audit inserts/updates/deletes) but it's auditing Inserts in the context of an index rebuild job as well...which sucks. I'm working with the developer on how to filter this out, but I guess that at the most basic level, does an index rebuild re-insert records into a table?
Gaby________________________________________________________________"In theory, theory and practice are the same. In practice, they are not." - Albert Einstein
September 28, 2010 at 7:50 am
GabyYYZ (9/28/2010)
We currently have a SQL 2008 Enterprise instance chugging along no problem. We recently upgraded it from SQL 2005 to 2008, and the application associated with it tripled it's activity, thus increasing the transaction log sizes and index reorganizations and rebuilds (both which have not been an issue).The main problem is we have a separate database that acts as a repository for a third party auditing application. The way it does the bulk of it's auditing is that it reads the transaction logs from the audited source and enters that data into it's repository, purging records older than 1.5 years.
Recently, with the upgrade, there was an unusual spike of this repository growth. After digging a bit, we found that the audit was capturing INSERT's (which is good, as it is supposed to audit inserts/updates/deletes) but it's auditing Inserts in the context of an index rebuild job as well...which sucks. I'm working with the developer on how to filter this out, but I guess that at the most basic level, does an index rebuild re-insert records into a table?
As far as I remember the answer is "no" for non-clustered indexes and "yes" for clustered indexes where the leaf level is physically located inside the target table.
_____________________________________
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at Amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.September 28, 2010 at 7:50 am
If I am not mistaken the Primary key Index or clustered is physical storage, so yes it will rebuild-Specifying a low fill factor on pages could help a bit.
On a non-clustered index the pages are external to the physical location and these serve as a pointer to the actual data page.
September 28, 2010 at 7:52 am
beat by 27 sec. 🙂
September 28, 2010 at 9:28 am
shanu.hoosen (9/28/2010)
beat by 27 sec. 🙂
That was close 😀
_____________________________________
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at Amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.September 30, 2010 at 12:02 pm
Thanks everyone. At least it will give me a point of reference with the application developers. I am working on auxiliary scripts to purge the offending rows daily which I can isolate easily enough. The first time will take awhile, but afterwards, the daily purge should be straightforward, preferably after the daily pull from audited database to repository occurs.
If anyone has used the third party software, it's called Lumigent AuditDB, and was curious if anyone else had this issue (they're currently trying to replicate this issue).
Cheers.
Gaby________________________________________________________________"In theory, theory and practice are the same. In practice, they are not." - Albert Einstein
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply