Delete rows from AO_2C4E5C_MAILITEMCHUNK table

[Jeff Moden]

Greetings.  First timer here so please let me know if I've posted this question in the wrong forum and what the correct one is.  Thanks and glad to "meet" you.

The folks that setup our Jira system forgot to setup an auto-delete for the EMails.  As a result, we're not even setup for a default delete of 45 days and, as a result of that, the AO_2C4E5C_MAILITEMCHUNK in our Jira database (on SQL Server 2016) is now occupying a whopping 127 GB, most of which is contained as LOB Data.

Just turning the delete functionality of Jira on would crush the log file with deletes not to mention it taking a month of Sundays to actually do more than a year's worth of deletes so that's pretty much out of the question until I can knock the size down.

I've checked to see if any other tables are dependent (Foreign Keys) on the AO_2C4E5C_MAILITEMCHUNK table and there are none.

I'm also a real handy DBA and I know how to do such massive deletes in a minimally logged fashion and I've already checked to make sure there are no unprocessed emails in the emails that I want to delete (more than 45 days old).

My question is, can I do such a delete directly/manually using T-SQL without it causing any damage to the system that I might not know about?

And, just to be sure, no... I'm NOT asking how to do it in T-SQL.  I've got that figured out.  I just need to be sure that I'm not going to break anything on the system if I do the deletes.

Thanks for your help, folks  I really appreciate it.

[Nic Brough -Adaptavist-]

Welcome to the Atlassian Community!

Standard advice - never even look at a Jira database.  It's not for reporting, it's certainly not for editing, and you really need to be an expert in Jira database structures and usage before you do it.

So the things I would recommend thinking about:

• Do you know what this table is for?  
• If so, are you sure it is working. or being used, "correctly"?  (From memory, this table shouldn't got back for more than 6ish weeks of mail - are you really getting that much mail?  Has some external system gone into a loop with Jira?  A human written a bad script?)
• It is a big table, but, given the way you're using it, are you sure that it won't just rapidly refill?  (and given you've destroyed the conversations, the next question is "consistently?")
• And following on from that, are you sure you understand the damage you will do?  And your people are ok with it?

But, that table, if I understand it right, won't break much if you hoover a few things out.  It will break some conversations in email and updates, but only in the sense that people might miss some stuff, not an "it's crashed" thing.

There are three things you absolutely must do if you're going to do this:

1. Stop Jira.  Never write anything to an active Jira database
2. Take a full backup of the database and run a restore to prove it worked
   1. (run your SQL)
3. Restart Jira and run a full locking re-index

[Jeff Moden]

Thanks for the reply.  I appreciate your standard advice and, so, to answer some of your questions...

If you look at my question, I said nothing about reporting.  I know what the table is for (it's stores emails until they can be processed and I know the table that uses it without it being FK'd and how to determine if any email has not yet been processed.

I also stated that we need to whittle it down to size before we enabled the auto-delete functionality so, yeah... I'm pretty sure it's not just going to grow again because the auto-deletes will take care of it after they get that setup.

And I have to look at the Jira database because I'm the DBA responsible for backups, index maintenance, statistics maintenance and much more.

And the purpose of my question was, since Jira sets up to delete from it if the settings are correct is (and it defaults to 45 days, which is just a touch over 6 weeks)... is there anything I missed? 

And, yep... I don't work on active systems.... I would, indeed, have them take the system down.  And, being a DBA, I do know to take the appropriate backups before taking such a wholesale action.  We have a "Deployment protocol" for doing such things where if the proverbial poo hits the fan during regression testing, I just need to press the F5 button to start the restore code that has already been written to support such a moment.

It's also my understanding that this table only contains emails and not conversations on the tickets themselves.  That being said, I'll check on that again so thanks for bringing that up.

And finally, I was asking because I was trying to make sure that there would be no damage that I do. I hate doing restores. :D

[Jeff Moden]

I especially thank you for the tip in the last half of your Item 3. 

[Nic Brough -Adaptavist-]

I know the answer was a bit long winded and covered some stuff I suspect you had already thought of, but I try to write for the wider audience - not just the questioner, but other people who might land here.

Last thing I want is someone seeing only the last couple of lines and running ahead with this without thinking through the first half of the post, and hence trashing part of their system!

Doing this will cause some damage, but it will be limited to Jira not knowing about recent email conversations on the issues,

But nothing that might lead you to needing to restore databases, just explain to uses that their email chains might be a bit wonky.

[Jeff Moden]

Oh... I totally get that and it's much appreciated.  I frequently do the same on SQLServerCentral.com.  I just wanted you to know that I'm not a "casual user" of databases and I do follow a rather rigid set of steps for any kind of a data modification/deletion including everything you recommended and more.

And, to be sure, I'd be leaving a rolling (for example) 45 days of emails in the table and also have a plan to be able to restore rows for tickets if there becomes a need.

Thanks you again, I appreciate it.

[Jeff Moden]

Just a follow up... which is good news and bad news...

First, it turns out that they do have the "auto-delete feature enabled" and the system is auto-magically deleting rows from both the AO_2C4E5C_MAILITEM and the AO_2C4E5C_MAILITEMCHUNK tab;es (I verified that after a conversion of the Created_TimeStamp column from an msUNIX TimeStamp to a DATETIME2(3) and got the MIN and some counts, etc, etc).  For the month of August, there are just a bit under 48,000 emails.  If we stay at that same rate, the Jira database isn't going to get much bigger and the INT datatype should last about another 37 Hundred YEARs before it runs out of room (we all love it when that happens, right?).  So, there's nothing for me to actually do... the system is already doing what I had intended.

The bad news is that it's yet another "history/log" type of table where the data never changes but, if you're doing nightly backups, you're backing up data that will never change, every night.  They didn't even consider using the COMPRESS function, which would whittle things down by about 2/3rds the size. 

The other bad news is that the number of emails per day is growing.  The xxxx table occupied 110 GB just a few short months ago... it now sits at 147 GB.  That's about 1/7th of a TB.

The good news is, the load on our backups for this table is trivial compared to our other systems and we're in the planning stages for changing from Nightly FULL and conditional 30 minute transaction log file backups to only Weekly FULL backups, Nightly DIF backups, and the current conditional 30 minute transaction log file backups.

For those interested, "conditional" means only if something was added to the log file.

Anyway, thanks to Nic for the reply and I hope this helps others.

BTW... anyone that does UNIX TimeStamp conversions using /1000 because of milliseconds is doing it incorrectly.  It's a part of the "2038" problem, most make a mistake and it becomes DATETIME, and that causes a rounding issue that can sometimes take you into the next day.  The proper conversion (which I actually started writing an article about a couple of weeks ago because so many people are doing it incorrectly) in SQL Server/T-SQL is ....

SELECT Converted = DATEADD(ms,Created_TimeStamp%86400000,DATEADD(dd,Created_TimeStamp/86400000,CONVERT(DATETIME2(3),'1970')))
,Created_TimeStamp --for a visual comparison
FROM dbo.AO_2C4E5C_MAILITEM
;

If you neglect to use the CONVERT in that, you'll end up with DATETIME, which will produce rounding errors whenever the units part of the UNIX TimeStamp is a "9".

[Nic Brough -Adaptavist-]

This is one of the things I think Mike and Scott didn't plan for, it feels like they weren't expecting the level of success they've had, and they never imagined people would use Jira systems on the scale a lot of us do in the long run. 

I don't blame them.  I've done the same.  As your product grows, there's always something more important to look at.

The short way I explain it - Jira uses a database as a data store.  It doesn't make use of anything more than the most basic functions of a database, and doesn't think about how it could do it better! 

[Jeff Moden]

I will say that Jira is pretty awesome and I don't pass out compliments easily.  It has some "nuances" that take some getting used to (I hated it at first) but it's real handy when it comes to projects, project management, and project reporting.  The interface with Tempo also works a treat.  There's nuances there, as well, but I don't know software that doesn't.  If you look at (for example), MS Word and Excel, they still have problems that have existed since Bill Gates got Blue screened trying to give a demo in Boston in the '80's.  (I was actually there when it happened).