My DW database is about 20GB size in data file and nearly 120GB in log file.
Data staleness is until midnight (so "yesterday's news") and multiple databases are being replicated daily from production servers. I'm also transforming the data at this time to get more analytical/synthetic/ information in one place for various consumers (accounts, marketing, management reports), so even complete data loss means about 30 min setback for all
Stored Procs to re-run and repopulate all tables again. That means, yes, that point in time recovery is not (and will not be) a requirement.
Obviously then, the schema itself is much more valuable than the data and that's what must be protected (and is). Nonetheless, the DB is in full recovery mode and backups are being run daily, in addition to ad-hoc ones when I expect to be "breaking things".
It's possible the
Snapshot isolation I've enabled on it had big impact on the size as well, but ultimately it proved pointless as I'm also using data across 12 databases (for now) and setting them all in similar way is impossible (various compatibility levels; don't want to meddle with that). So happy to switch it off again.
But, having said all that, I can't afford having the database inaccessible for long, as a number of people use it to do their work, even if it's still in early development phase. SSRS runs several dozens of subscriptions off it as well (RS is on a different server, if that's helps). It is also being exposed to a BI software for a number of web users (Tableau), so if we don't disrupt that, too, it would be good.
Obviously - again - large log files are due to large number of queries being cancelled, erroring out and/or simply crashing. And since I'm the only user who has insert/update/delete permissions, which is mostly done outside of working hours anyway, transactions are committed already for a quite a while.
So, my question is: how do I safely shrink the database to something more compact? And definitely in a "rinse and repeat" pattern for foreseeable future? No point wasting space to backup data I'm not really required to keep.
Additional info: this also may apply to the remaining 12 DBs (replicated from production servers).
Main reason for asking is that I may need the database to be more portable so that I can work on it offline and since other DBs are already quite large space really counts. And I'm not really a DBA, so I'm not sure of the best course here.
After reading linked articles and answers to similar question there is one, additional question that bugs me: If i do a log backup (as only then log files will stop growing) and then delete those backups (as I don't really need them - or do I?) will the log file start growing again? Do I understand correctly that I need to shrink and switch to simple recovery model afterwards to prevent that?