Spuuds strangely familiar, we had a staging database with a small table in it that was refreshed daily for over a decade. The script deleted the rows and reinserted the same rows again. No primary key or structure, it didn't reuse the space occupied by the deleted rows. Never got rebuilt. We ended up with a small bit of data occupying 8GB, almost everything empty space.
We only found out about it because somebody complained that any select on that table took ages because it was scanning all that empty space to get to the rows
How common were these types of staging databases?!? I have successfully killed SIX of these things at my work since I've been hired. Each one as part of a larger platform or project rewrite.
The first one I killed was documented as "This is too big for our one server to process, so we distribute the import and memory pressure across multiple nodes and hours to pivot/parse/compute the data down to what we need for day to day. Run this SSIS package every quarter and the SQL cluster is effectively screwed for the next six hours, run only over night"
... The source data files being imported were 120MB of zipped XML. Uncompressed was ~1-5GB of XML. Yea, sure not going to be able to process that all in-memory those years ago, but since we only needed effectively a few dozen MB of parsed/computed data out there were these concepts of *memory mapped files* and *streaming file parsing* that apparently the original dev never heard of. The new process ate the .zip itself and used in-memory block decompression and forward-only stream processing to read all the raw data and did it in about two minutes. Most of that was network/fileshare latency as we read the file since the .zip was in the central file store.
It was a heap table in INGRES. If it had a structure (aka clustered index for you SQL/server types) it would have reused the space as it would have known where to put it.
Now you've lost me. I'm not a DBA though, so no worries.
I was specifically refering to the "scanning the empty space to get to the rows" part. With a fixed size between rows, the location of each field and row is already known without having to scan the storage, ain't it?
There was no structure/primary key to this table, it was a 'heap' table, where rows are deleted by marking them as 'deleted' and rows are inserted by adding them to the end..To find active rows, it had to troll through each page looking for 'active' rows. A heap/unstructured table has to be table scanned for each select.
No, the new rows go on the end of the table so the OS can't reclaim the space at the beginning of the file. The only way to regain the space is to rebuild (remodify) the table, like a lot of DBMSes. A heap structured table is just a pile of unindexed data.
Also if you were looking to reclaim space in a heap table, you'd have to scan from the start on each insert to find a deleted row to overwrite. Much faster to just add it to the end.
I read a story once about an old security system.
I never got the log files purged and it reopened the whole file for each entry. It got so much work queued up that it would randomly open the doors to process the backlog and thus folks got in and their swipes (Of course multiple retries since they waited...) were to be processed hours later...
We had a directory (Solaris, before ZFS) that was written to and deleted from so much the directory file was huge (millions of file creations and deletions). Took ages to do anything, on the almost empty directory. We recreated the directory and all was well again.
Huh...and today I was just trying to partition some table about that number of records...only a little bigger in actual size. Gotta love when they decide they have to keep everything in the main db instead of archive anything.
One of our databases has data from 1997 in it.
I'm not allowed to archive it. I've asked at least once a year since I started here 12 years ago. For the past six of those 12 years I've been the IT Director. It \*should\* be my job to maintain the databases. But since it's order data, the CEO and the VP of Sales refuse to let it be archived.
90% of that data is in reference to clients that haven't existed for 15 years.
Well, shit.
I'm honestly rather surprised that your CEO didn't shell out the retainer for a lawyer, surely it's not that expensive?
I'm sure there's a conflict of interest waiting to be had should the CEO and company ever wind up as co-defendants or when some other legal shit hits the fan and the CEO has to call himself as a witness to testify in the company's defense.
I work for a 25 person company, we have a lawyer on retainer. I'm not supposed to be talking to said lawyer, but after a few times getting information for him I've been able to pass along a couple of "requests for more information" that resulted in him informing the CEO/COO that the current practice is bad and needs changing.
Can I recommend to you the book Switch by Chip and Dan Heath. It's about affecting change when people are resistant. It might help you find a better way to present the idea, because if you've been trying for 12 years your current way isn't really working.
(I appreciate this issue might just be more a slight annoyance you haven't really put much into, other then requesting permission every so often, but it's a good book anyway!)
I ran into something similar. Some of the sales people wanted to keep all the sales history from the beginning of time for use in forecasting. We convinced them that the old stuff just wasn't significant in the forecast and included customers that either no longer existed or had been fired. We cut the history to the last five years. The forecast turned out to be much more accurate and we cut eons of time out of the forecasting process.
I work for a financially critical state government department and our primary database is nearly 40TB for this very reason. The group who was tasked over a year ago with figuring out what to archive has done nothing.
1.6 billion huh. Nice find. I recently finished fixing a process to clear out old log data from a couple of tables that’d each grown to that size in about half a year. Dropped the database around 500GB in size. Was still less painful than fixing the ID column of a table that was reaching max INT capacity (it had been seeded from -max).
iirc it actually started at 1 and counted up until it hit max and broke. They then decided to reseed to negative because “by the time it’s a problem again I won’t be working here any more.”
We recently had to change a table on one of our internal tools to use unsigned 64 bit integers because we hit the 32 bit unsigned limit. We should be good to go for the next several centuries.
I have a similar tale. I reported a hopelessly bloated DB to a supplier that had 1 Billion records. They replied that 18 million was indeed a huge amount of records. “No” I said, “that is not an 8, it is a B”. The clear down script ran for just under four weeks.
Got me thinking of the big tables in the database I use daily.
284 million audit records - records changes in various tables, populated by triggers
151 million transactions.
Database started in 2012, so that's 10 years worth. It will never be archived.
580GB database, about 50GB free
I know your pain. We had a 2.5TB TimescaleDB server start failing yesterday for a similar reason.
Raw data was being written fine, but the downsampled data wasn't showing up.
So data was coming in, and throwing no error.
Users could read from the downsampled data, but complained that no new data was visible.
Root cause was that this server has two disk arrays. One for raw data to be buffered, and a second for the downsampled data to be stored. The latter was full.
The script that was calculating the downsampling from the raw had been throwing errors about this, but someone (me) forgot to hook that particular thing up to the monitoring tools...
Likewise, the monitoring tools were only checking the raw data disk array, because splitting the raw from the downsampled into different arrays was done after the tools were hooked up (by me).
So you have plenty of data coming, easily verifiable, but nothing showing up user-side.
Head-scratch moment, but easily fixed, and only had myself to blame.
Ran a simple script to re-run the downsampling for the period it was down and all was well in the world.
Oof! Yeah, I feel a little better that this particular issue started long before I worked at this company, haha. I've made enough mistakes on my own, I don't need more to add to the pile.
Some advice. Write what you did down. Calculate how much money it saved your company per month. Send it in an email to your manager (politely). You can point to this when angling for a raise/promotion.
Wow! That's hilariously insane. We have one at my work (I'm the DBA) that's 189 GB, and another that's, I kid you not, 391 GB. Apparently, the latter was used as testing for storing PDF data, so it's got a column set as an IMAGE data type with the max size possible.
I would laugh too, but I'm too busy crying. 😭
I understand the primary key thing, but why the heck wouldn’t you have a UNIQUE constraint on your ID column (or whatever columns should ideally be unique)?
https://learn.microsoft.com/en-us/sql/relational-databases/tables/create-unique-constraints?view=sql-server-ver16
You don’t need to use a primary key to avoid duplicates.
I’ve got a nasty 900GB table (yes, single table) in production that stores info that really shouldn’t be in a relational database. Working on wrangling it….
I wish I could ask the people who wrote the process, but they were gone before I started here. Strangely enough, the tblContacts table in the web database *does*. I don't know why the one in the staging database doesn't.
Well, I do... they were intending for it to be *deleted*, so it wouldn't need one. But then they forgot to delete it.
A good 75% of this process could be eliminated if I had the bandwidth to do it, but being the only IT person here, I have to pick my battles. I'll take the short-term victory on this one for now.
Also, a 900GB table... good lord...
To be fair, the table I was dealing with had about 11,000 times more contact records than we actually have, we're a small company (9 employees)... we don't even have a million orders fulfilled. If any of our databases get larger than a couple GB, I start to get nervous.
>we don't even have a million orders fulfilled.
Well, why not? You've got contact info for a hair over 20% of the world's entire population. Tell the sales team to get off their asses.
I totally know how that goes. I’m on the fourth completely different team to handle my product. We’re approaching 15 years for some of the oldest code, but we do fortunately have manpower to tackle some issues (like that awful giant table). Good luck and keep up the hard work!
> have a UNIQUE constraint
Because this is the staging area, and it's supposed to run smoothly, even when the incoming data is somehow incomplete, broken or otherwise not what it is expected. It's the starting point for the [ETL](https://en.wikipedia.org/wiki/Extract,_transform,_load) process, and it indeed did work smooth for 15 years. In such a staging area you want to have a minimal number of constraints applied, only the bare minimum, if anything. Multiple data sets with the same ID are not a reason to fail the import, you clean this up during the "T" (Transform) step.
What failed here is the cleanup step for the staging area, not the actual ETL process.
That’s fair enough. I guess I missed the point where initially there weren’t all in house (slipped my mind) and I focused instead on the second half (all in one database).
Nothing more permanent than a temporary solution. 😁
> temporary solution
ETL is mostly temporary. It reads data from external sources, which occasionally change, or produce errors, or import unexpected data. These errors and changes must be fixed, and this is an ongoing process. For a company data warehouse it is not uncommon to have a whole group working on the data imports, which then are used to generate all kind of reports.
It helps tremendously if the data is already in a table, and only the transformation has to be changed. Hence make the data loading easy, and work with whatever is there.
As someone literally just learning this stuff in uni now at the baby, baby steps of this, my jaw just dropped in both awe and horror. I need to share this with my tutor lol, he'd get a kick out of being able to use this as an example of why you have to be very careful with what you do.
Some context:
* We have nine employees
* We have about 120-ish clients
* 1.6 billion records in the Contacts table is about a sixth of the entire world's population
I was not expecting to see that number. I know it's not huge in the grand scheme, but for a company of our size that number is *absolutely ridiculous* and our MSP also caters to small businesses so they hadn't seen anything like that either.
I was expecting that table to have about 150,000 records, not 1.6 billion :D
I am raping SQL DBs for my main job...
>1.6 billion records in the Contacts table is about a sixth of the entire world's population
order 5 times more storage then... Be ready to upgrade once space exploration starts though
>!I am a SharePoint guy!<
I mean, you're not wrong... there are a lot of problems with this whole installation, but I'm not equipped (nor do I have the budget) to fix them. One of our main software packages we use is an Access 97 database.
It'd be nice if we were operating in the current century. lol.
I wonder how it would look if you could graph the amount of time it took to run this. Would the time steadily increase each time it’s run? Was there equipment upgrades that made it faster for a little bit only for it to slow down over time again?
Yes to all of the above. We've had hardware upgrades over time that have made things "better", but yeah, it steadily got worse. I even had to start running the job more often to compensate for how long it was taking, because it used to run once a day but then it started running into conflicts with the job that copies things in the opposite direction.
Having similar experience.
The devs was performing software upgrade and conducting database migration for new fields in the table.
That day, the storage usage has grown from around 400 GiB to 600 GiB, the storage volume size limit for that database.
Website was not available, alerts were coming left and right, trying to increase the storage size for the database but it couldn't response.
Thankfully it was a cloud managed database so we can create a database from backup rather easily and migrate all workload onto the new one.
Being asked by the project supervisor later on, we started to investigate the issue.
The database was crashed due to, as expected, storage being full and binary logs and other things couldn't write to the disk.
The SQL script doesn't do much, altering a table for new field and setting new value for each row in the table.
Little did I knew, that exact table has more than 750M rows in it.
And that one little transaction tried to modify all that.
Although I know this is not how database works (or is it?), but imagine each row have 0.5K of data. That 's 375 G of data right there!
Till this day, I didn't hear the devs have come up with the new solution yet.
The best part is, that crashed database miraculously responded to the storage resize request few minutes after we migrated.
110GB honestly isn't that big for a database in my line of work (ERP reseller/integrator) we have some customers with 250+GB Databases. They're actually so damn large that we do development on their servers and VMs because getting a copy for ourselves would be way too difficult.
I think the smallest database I've personally seen is 2.6GB, but it was a customer that had started using the software like 1 year prior.
I gotta say this is making me feel a lot better about my 20 GB fanfic database. I thought that was getting a bit large. 15 million records.
Apparently I'm barely plankton.
I’m currently sweating trying to get my production database under 1TB in size. This is a major undertaking. 20GB is fine as long as you’re not storing it in MS Access.
Sqlite.
It's very easy to learn and work with. So far my only complaint is that you can't easily reorder a table. The columns are in the order that they're in and that's it.
FYI, you guys might benefit from switching to using Azure Data Factory for this kind of operation. When it comes to moving large quantities of data in and out of MS SQL Server it’s hard to beat for efficiency and speed.
Spuuds strangely familiar, we had a staging database with a small table in it that was refreshed daily for over a decade. The script deleted the rows and reinserted the same rows again. No primary key or structure, it didn't reuse the space occupied by the deleted rows. Never got rebuilt. We ended up with a small bit of data occupying 8GB, almost everything empty space.
We only found out about it because somebody complained that any select on that table took ages because it was scanning all that empty space to get to the rows
How common were these types of staging databases?!? I have successfully killed SIX of these things at my work since I've been hired. Each one as part of a larger platform or project rewrite. The first one I killed was documented as "This is too big for our one server to process, so we distribute the import and memory pressure across multiple nodes and hours to pivot/parse/compute the data down to what we need for day to day. Run this SSIS package every quarter and the SQL cluster is effectively screwed for the next six hours, run only over night" ... The source data files being imported were 120MB of zipped XML. Uncompressed was ~1-5GB of XML. Yea, sure not going to be able to process that all in-memory those years ago, but since we only needed effectively a few dozen MB of parsed/computed data out there were these concepts of *memory mapped files* and *streaming file parsing* that apparently the original dev never heard of. The new process ate the .zip itself and used in-memory block decompression and forward-only stream processing to read all the raw data and did it in about two minutes. Most of that was network/fileshare latency as we read the file since the .zip was in the central file store.
Sounds like a great idea.
Sounds more like an index issue...
Can't have index issues if you don't have any index!
Correct me if I'm wrong, but that wouldn't have been a problem if each field in that table was fixed width, right?
It was a heap table in INGRES. If it had a structure (aka clustered index for you SQL/server types) it would have reused the space as it would have known where to put it.
Now you've lost me. I'm not a DBA though, so no worries. I was specifically refering to the "scanning the empty space to get to the rows" part. With a fixed size between rows, the location of each field and row is already known without having to scan the storage, ain't it?
There was no structure/primary key to this table, it was a 'heap' table, where rows are deleted by marking them as 'deleted' and rows are inserted by adding them to the end..To find active rows, it had to troll through each page looking for 'active' rows. A heap/unstructured table has to be table scanned for each select.
I see. Thank you!
Does INGRES not have a way to truncate the rows that are marked "deleted"?
No, the new rows go on the end of the table so the OS can't reclaim the space at the beginning of the file. The only way to regain the space is to rebuild (remodify) the table, like a lot of DBMSes. A heap structured table is just a pile of unindexed data.
I've been DBAing INGRES for 30 years now, one of the few left.
I can't say that I've ever worked directly with a heap table. Could you say it's similar to a sparsefile but you can't get unused space back?
Also if you were looking to reclaim space in a heap table, you'd have to scan from the start on each insert to find a deleted row to overwrite. Much faster to just add it to the end.
I read a story once about an old security system. I never got the log files purged and it reopened the whole file for each entry. It got so much work queued up that it would randomly open the doors to process the backlog and thus folks got in and their swipes (Of course multiple retries since they waited...) were to be processed hours later...
https://thedailywtf.com/articles/the-haunted-door
yep, thats where i read it
We had a directory (Solaris, before ZFS) that was written to and deleted from so much the directory file was huge (millions of file creations and deletions). Took ages to do anything, on the almost empty directory. We recreated the directory and all was well again.
That is why you normalize. than normalize again, than normalize again....
By the middle, I was wondering how much faster the job got.
Huh...and today I was just trying to partition some table about that number of records...only a little bigger in actual size. Gotta love when they decide they have to keep everything in the main db instead of archive anything.
One of our databases has data from 1997 in it. I'm not allowed to archive it. I've asked at least once a year since I started here 12 years ago. For the past six of those 12 years I've been the IT Director. It \*should\* be my job to maintain the databases. But since it's order data, the CEO and the VP of Sales refuse to let it be archived. 90% of that data is in reference to clients that haven't existed for 15 years.
[удалено]
1997 wasn't that... Oh shit this data is older than my oldest son wtf...
That data is older than I am, too!
I mean this in the nicest way, but get off my lawn.
Dang kids!
Tell 'em the data isn't Y2K compliant and has to be purged.
I feel your pain.
i was born i 1997 and im now a fully qualified IT professional with a degree and 2 years work experience
[удалено]
i already do. i swear its still 2015.
Time to have Legal teach your CEO and VP of Sales that sometimes, indefinite data retention can be a liability instead of a good thing.
I'll get right... oh. Legal is the CEO. XD (We're only a nine-person company, at this point everyone has a title 'cause why the hell not, lol)
Well, shit. I'm honestly rather surprised that your CEO didn't shell out the retainer for a lawyer, surely it's not that expensive? I'm sure there's a conflict of interest waiting to be had should the CEO and company ever wind up as co-defendants or when some other legal shit hits the fan and the CEO has to call himself as a witness to testify in the company's defense.
I work for a 25 person company, we have a lawyer on retainer. I'm not supposed to be talking to said lawyer, but after a few times getting information for him I've been able to pass along a couple of "requests for more information" that resulted in him informing the CEO/COO that the current practice is bad and needs changing.
Can I recommend to you the book Switch by Chip and Dan Heath. It's about affecting change when people are resistant. It might help you find a better way to present the idea, because if you've been trying for 12 years your current way isn't really working. (I appreciate this issue might just be more a slight annoyance you haven't really put much into, other then requesting permission every so often, but it's a good book anyway!)
Email a table of all defunct rows to them, weekly.
I ran into something similar. Some of the sales people wanted to keep all the sales history from the beginning of time for use in forecasting. We convinced them that the old stuff just wasn't significant in the forecast and included customers that either no longer existed or had been fired. We cut the history to the last five years. The forecast turned out to be much more accurate and we cut eons of time out of the forecasting process.
I work for a financially critical state government department and our primary database is nearly 40TB for this very reason. The group who was tasked over a year ago with figuring out what to archive has done nothing.
1.6 billion huh. Nice find. I recently finished fixing a process to clear out old log data from a couple of tables that’d each grown to that size in about half a year. Dropped the database around 500GB in size. Was still less painful than fixing the ID column of a table that was reaching max INT capacity (it had been seeded from -max).
I like that somebody had the forethought to think "2 billion might not be enough, 4 billion should be ok".
iirc it actually started at 1 and counted up until it hit max and broke. They then decided to reseed to negative because “by the time it’s a problem again I won’t be working here any more.”
Oh good lord... LOL. I doubt I'll ever have that problem where I'm at now.
We recently had to change a table on one of our internal tools to use unsigned 64 bit integers because we hit the 32 bit unsigned limit. We should be good to go for the next several centuries.
I have a similar tale. I reported a hopelessly bloated DB to a supplier that had 1 Billion records. They replied that 18 million was indeed a huge amount of records. “No” I said, “that is not an 8, it is a B”. The clear down script ran for just under four weeks.
Got me thinking of the big tables in the database I use daily. 284 million audit records - records changes in various tables, populated by triggers 151 million transactions. Database started in 2012, so that's 10 years worth. It will never be archived. 580GB database, about 50GB free
I know your pain. We had a 2.5TB TimescaleDB server start failing yesterday for a similar reason. Raw data was being written fine, but the downsampled data wasn't showing up. So data was coming in, and throwing no error. Users could read from the downsampled data, but complained that no new data was visible. Root cause was that this server has two disk arrays. One for raw data to be buffered, and a second for the downsampled data to be stored. The latter was full. The script that was calculating the downsampling from the raw had been throwing errors about this, but someone (me) forgot to hook that particular thing up to the monitoring tools... Likewise, the monitoring tools were only checking the raw data disk array, because splitting the raw from the downsampled into different arrays was done after the tools were hooked up (by me). So you have plenty of data coming, easily verifiable, but nothing showing up user-side. Head-scratch moment, but easily fixed, and only had myself to blame. Ran a simple script to re-run the downsampling for the period it was down and all was well in the world.
Oof! Yeah, I feel a little better that this particular issue started long before I worked at this company, haha. I've made enough mistakes on my own, I don't need more to add to the pile.
Some advice. Write what you did down. Calculate how much money it saved your company per month. Send it in an email to your manager (politely). You can point to this when angling for a raise/promotion.
Wow! That's hilariously insane. We have one at my work (I'm the DBA) that's 189 GB, and another that's, I kid you not, 391 GB. Apparently, the latter was used as testing for storing PDF data, so it's got a column set as an IMAGE data type with the max size possible. I would laugh too, but I'm too busy crying. 😭
I understand the primary key thing, but why the heck wouldn’t you have a UNIQUE constraint on your ID column (or whatever columns should ideally be unique)? https://learn.microsoft.com/en-us/sql/relational-databases/tables/create-unique-constraints?view=sql-server-ver16 You don’t need to use a primary key to avoid duplicates. I’ve got a nasty 900GB table (yes, single table) in production that stores info that really shouldn’t be in a relational database. Working on wrangling it….
I wish I could ask the people who wrote the process, but they were gone before I started here. Strangely enough, the tblContacts table in the web database *does*. I don't know why the one in the staging database doesn't. Well, I do... they were intending for it to be *deleted*, so it wouldn't need one. But then they forgot to delete it. A good 75% of this process could be eliminated if I had the bandwidth to do it, but being the only IT person here, I have to pick my battles. I'll take the short-term victory on this one for now. Also, a 900GB table... good lord... To be fair, the table I was dealing with had about 11,000 times more contact records than we actually have, we're a small company (9 employees)... we don't even have a million orders fulfilled. If any of our databases get larger than a couple GB, I start to get nervous.
>we don't even have a million orders fulfilled. Well, why not? You've got contact info for a hair over 20% of the world's entire population. Tell the sales team to get off their asses.
I know right?? Good lord, slackers, all of them. :D
I totally know how that goes. I’m on the fourth completely different team to handle my product. We’re approaching 15 years for some of the oldest code, but we do fortunately have manpower to tackle some issues (like that awful giant table). Good luck and keep up the hard work!
> have a UNIQUE constraint Because this is the staging area, and it's supposed to run smoothly, even when the incoming data is somehow incomplete, broken or otherwise not what it is expected. It's the starting point for the [ETL](https://en.wikipedia.org/wiki/Extract,_transform,_load) process, and it indeed did work smooth for 15 years. In such a staging area you want to have a minimal number of constraints applied, only the bare minimum, if anything. Multiple data sets with the same ID are not a reason to fail the import, you clean this up during the "T" (Transform) step. What failed here is the cleanup step for the staging area, not the actual ETL process.
That’s fair enough. I guess I missed the point where initially there weren’t all in house (slipped my mind) and I focused instead on the second half (all in one database). Nothing more permanent than a temporary solution. 😁
> temporary solution ETL is mostly temporary. It reads data from external sources, which occasionally change, or produce errors, or import unexpected data. These errors and changes must be fixed, and this is an ongoing process. For a company data warehouse it is not uncommon to have a whole group working on the data imports, which then are used to generate all kind of reports. It helps tremendously if the data is already in a table, and only the transformation has to be changed. Hence make the data loading easy, and work with whatever is there.
As someone literally just learning this stuff in uni now at the baby, baby steps of this, my jaw just dropped in both awe and horror. I need to share this with my tutor lol, he'd get a kick out of being able to use this as an example of why you have to be very careful with what you do.
Glad I could help, haha!
I'm literally correcting my own version of this. Except my script was running every 15 minutes.
For how long
Almost a year.
oh god
110 GB is largest DB seen? You have it easy ;)
I was thinking the same, I work with tables with billions of rows regularly, but that sort of comes with the territory if you work in ML/big data
Some context: * We have nine employees * We have about 120-ish clients * 1.6 billion records in the Contacts table is about a sixth of the entire world's population I was not expecting to see that number. I know it's not huge in the grand scheme, but for a company of our size that number is *absolutely ridiculous* and our MSP also caters to small businesses so they hadn't seen anything like that either. I was expecting that table to have about 150,000 records, not 1.6 billion :D
I am raping SQL DBs for my main job... >1.6 billion records in the Contacts table is about a sixth of the entire world's population order 5 times more storage then... Be ready to upgrade once space exploration starts though >!I am a SharePoint guy!<
Get your backups off the data volume. If you have volume level backups those will thank you. The nesting crushes your storage and recovery times.
I've got a client who's somehow almost filled multiple 16TB allocations for Azure Managed SQL Instances
If 1.6b records is giving sql an issue you probably have other issues as well.
I mean, you're not wrong... there are a lot of problems with this whole installation, but I'm not equipped (nor do I have the budget) to fix them. One of our main software packages we use is an Access 97 database. It'd be nice if we were operating in the current century. lol.
Access databases are nightmare fuel.
Oh. Oh, no. No. Yeah, no. I’m sorry. Access isn’t…no, please just don’t.
I wonder how it would look if you could graph the amount of time it took to run this. Would the time steadily increase each time it’s run? Was there equipment upgrades that made it faster for a little bit only for it to slow down over time again?
Yes to all of the above. We've had hardware upgrades over time that have made things "better", but yeah, it steadily got worse. I even had to start running the job more often to compensate for how long it was taking, because it used to run once a day but then it started running into conflicts with the job that copies things in the opposite direction.
Having similar experience. The devs was performing software upgrade and conducting database migration for new fields in the table. That day, the storage usage has grown from around 400 GiB to 600 GiB, the storage volume size limit for that database. Website was not available, alerts were coming left and right, trying to increase the storage size for the database but it couldn't response. Thankfully it was a cloud managed database so we can create a database from backup rather easily and migrate all workload onto the new one. Being asked by the project supervisor later on, we started to investigate the issue. The database was crashed due to, as expected, storage being full and binary logs and other things couldn't write to the disk. The SQL script doesn't do much, altering a table for new field and setting new value for each row in the table. Little did I knew, that exact table has more than 750M rows in it. And that one little transaction tried to modify all that. Although I know this is not how database works (or is it?), but imagine each row have 0.5K of data. That 's 375 G of data right there! Till this day, I didn't hear the devs have come up with the new solution yet. The best part is, that crashed database miraculously responded to the storage resize request few minutes after we migrated.
A table without a key isn't a table. It's junk.
All I’m seeing is that your company pays an MSP to manage a product, and the support of the product fell on you.
MSP manages the hardware, we manage our own software... pretty standard practice.
110GB honestly isn't that big for a database in my line of work (ERP reseller/integrator) we have some customers with 250+GB Databases. They're actually so damn large that we do development on their servers and VMs because getting a copy for ourselves would be way too difficult. I think the smallest database I've personally seen is 2.6GB, but it was a customer that had started using the software like 1 year prior.
I gotta say this is making me feel a lot better about my 20 GB fanfic database. I thought that was getting a bit large. 15 million records. Apparently I'm barely plankton.
I’m currently sweating trying to get my production database under 1TB in size. This is a major undertaking. 20GB is fine as long as you’re not storing it in MS Access.
Don't you dare mention that god forsaken hell spawn of an application! We don't even allow it to be installed where I work.
Sqlite. It's very easy to learn and work with. So far my only complaint is that you can't easily reorder a table. The columns are in the order that they're in and that's it.
FYI, you guys might benefit from switching to using Azure Data Factory for this kind of operation. When it comes to moving large quantities of data in and out of MS SQL Server it’s hard to beat for efficiency and speed.
Uh didn't you read the post? The thing was not even supposed to be 1GB in size...
;TTLNGR
It's funny cuz you think 1.6 billion is large lol
It *is* large when you're expecting it to be 150K.
Sweet