nav[aria-label="Primary Navigation"] { padding: 0; & ul { list-style: none; width: 100%; display: flex; flex-direction: row; justify-content: start; align-items: start; gap: 30px; padding: 0; & li { margin: 0; } & ul li { list-style: none; } } }

Delete rows from AO_2C4E5C_MAILITEMCHUNK table

Jeff Moden_eds9ja

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.

Accepted answers

All comments

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:

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

Jeff Moden_eds9ja

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.

Jeff Moden_eds9ja

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.

Welcome!

It looks like you're new here. Sign in or register to get started.

Quick Links

Find more posts tagged with

server

jira

jira-server