What will happen to the night sky brightness over very long timescales?

It will become progressively darker, potentially reaching a 'Big Freeze.'

Contrary to the idea that accumulating starlight will eventually brighten the sky, the long-term prognosis is increasing darkness. While new stars continually form, the accelerating expansion of space drives the most distant galaxies away from us faster and faster. Light emitted by these receding sources today will never reach us, effectively shrinking the volume of the universe that can contribute visible photons to our future observations. Coupled with the eventual exhaustion of fuel for all existing and future stars, this leads toward an ultimate state known as the 'Big Freeze' or 'heat death,' characterized by a fading visible sky as remaining light is redshifted beyond detectability.