1

We have "AdfsConfiguration" and "AdfsArtifactStore" databases for our ADFS deployment. I keep running into disk space issues due to the full and transaction log backups being taken on the server.

Right now, these two databases are in Full recovery model - but I wonder if I can just switch to Simple. Are transaction log backups going to be important in these two databases?

I have searched a lot but can't really understand whether these DBs need actually need the trans log backups or if just a nightly full backup is good enough.

Also, I understand the general rules of thumb for deciding between the two models (SIMPLE or FULL recovery model for databases?) for I just can't come to and understand on what these ADFS databases are really doing to make the decision.

This is a screenshot of the "restore" GUI screen. it shows how I have log backups taken every 30 min:

enter image description here

This basically means our RPO is 30 min? And then the full backup ensures ALL previous transactions are recoverable if they occurred before it was taken (in other words... I don't need to have log backup files from before the point in time in which the full backup was taken, right?)

I am not using compression (only available in Enterprise Edition right?)

I have thought about daily DIFFs and weekly FULLs. I honestly just seem to read online of a lot of people doing nightly FULLs. All instances I inherited were already set up to do nightly FULLs and I haven't seen any that do daily DIFFs (not that this means I shouldn't make a change of course).

Honestly I'm one of those "accidental" DBAs (am more of a developer). It's been overwhelming to actually try to get up to speed on everything I need to understand and put into practice.

Improve this question
3
  • 1
    On another topic, hopefully you are running DBCC CHECKDB commands against these databases on a frequent basis so you can be proactive in catching any kind of corruption early enough.
    – Scott Hodgin - Retired
    Commented Mar 15, 2017 at 20:22
  • Let us continue this discussion in chat.
    – Scott Hodgin - Retired
    Commented Mar 15, 2017 at 20:24
  • techrepublic.com/blog/the-enterprise-cloud/… said: "Many customers were upset that this feature was only available with the Enterprise edition of SQL Server 2008. With the R2 release of SQL Server 2008, the Standard edition now has the compressed backup feature." For what it is worth.
    – RLF
    Commented Mar 15, 2017 at 20:49

1 Answer 1

Reset to default
1

When ADFS is installed using the built-in Windows Information Database (which runs an embedded SQL Server on the AD DC), these databases are installed in SIMPLE recovery model. You could possibly take that as an indication that it would be OK to run them in SIMPLE model in your own environment, but that is up to you.

In general, if you're taking a full SQL backup daily, you should be OK discarding any historical transaction log backups prior to the full backup to save on disk space. But this depends on your need to be able to restore the database to any point in time in the past. If your policy requires you to be able to restore the database to a specific point in time, then you'll likely need to hang onto all those full, diff, and tlog backups that comprise your RPO.

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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