0

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.

EDIT:

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?

  • So... you're taking a daily log backup? – Erik Darling Apr 24 '17 at 14:08
  • @sp_BlitzErik AFAIK: no. Only full backups. – AcePL Apr 24 '17 at 14:10
  • 2
    Oh boy. So you're in full recovery and you've never taken a log backup? – Erik Darling Apr 24 '17 at 14:13
  • 1
    Look at the first answer of this question about 'How to manage sql log files'. – SqlWorldWide Apr 24 '17 at 14:26
  • 1
    In the politest way possible: judging by some of the things you said in your question, you have many, many misgivings about how transaction logs and backups work. I'd look at the link that @SqlWorldWide posted, as well as this question: dba.stackexchange.com/questions/29829/… – Erik Darling Apr 24 '17 at 16:23
2

Switch to SIMPLE recovery mode.

FULL recovery mode is for when you need point-in-time recovery, and requires that you take frequently transaction log backups.

Since you've stated (in the comments) that you don't need point-in-time recovery, and that you can rebuild the entire thing in a matter of 30 minutes, just switch to simple.

The transaction log file will still be used by in-process transactions, but when those complete/commit (or are cancelled/rolled back) the tran log space is marked for re-use.

Once you've made the change, you can recover some of the additional space by using SHRINKFILE:

DBCC SHRINKFILE (NAME = 'mydatabase_log', 5000)

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.