(Some context first because while I hope the questions are general enough we're always told the solutions depend on context. There's no DBA in this scenario.)
This is a [very small office] database, used 5 days a week, 8 hours per day, by a handful of clients running a program that mostly does listings and small numbers of inserts/updates at each time.
The database is a single file, has around 25 tables, and it currently runs to 20 GB, 19 of which are used by the same table, which is the one with a blob (
There is a daily backup of the data file, but in all of its more than 10 years there has never been any maintenance, other than moving it from server to server - it started on SQL Server 2005 / Windows SBS 2003 or something, has changed 3 times, and is now on SQL Server 2019 / Windows 2019.
I thought the 20 GB was too much (it's not a problem for backups, the system is fast enough), but when I saw the number of records on the table with the blob I concluded that it's about 300 KB per blob, which seems fair. It would be a great candidate for compression or off-database storing, but I do not control the program that uses the database.
The index for that table is 96% fragmented. The standard advice would be to do a rebuild and update statistics, but I have a number of questions and here they are, finally, I hope helpful to all:
Does rebuilding the index not risk inflating the DB file size by a large factor? (even tho it's never been shrunk, and the current size seems to be filled with valid data rather than empty) I'm not keen on bumping the DB up to, say, 25 GB, especially given that data is seldom deleted from this DB, only added. I could tolerate a 1 or 2 GB increase; my issue is that I have no idea what to expect.
Given I have loads of idle time to use, is there a disadvantage to just do a reorganize? From the description it sounds like a less intrusive operation and only less recommended because it's an older, slower process during which the DB will be less responsive.
Can I combine a reorganize with updating the statistics, or does this question betray I really have no idea how indexes work?
Given the usage pattern of the database, is it a risk not having backups of the logs and such?