Transaction log sizing of read-only databases

Question

Transaction log sizing of read-only databases

keymoo

SSCrazy

Points: 2975
More actions
February 10, 2016 at 7:12 am

#399705

What is best practice for sizing a read-only database transaction log. There may be some cross-database joins. My hunch is that the transaction log can be tiny. I have a 1.2 TB Read-Only database and I need to size the transaction log, it is currently 200 GB and I could do with the space. If SQL Server needs any work tables it will use tempdb right? Any tips?

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Hugo Kornelis SSC Guru Points: 64790 More actions · Answer 1

keymoo (2/10/2016)
What is best practice for sizing a read-only database transaction log. There may be some cross-database joins. My hunch is that the transaction log can be tiny. I have a 1.2 TB Read-Only database and I need to size the transaction log, it is currently 200 GB and I could do with the space. If SQL Server needs any work tables it will use tempdb right? Any tips?

If the database is really read-only (meaning: you have set it to read only using ALTER DATABASE and you will get an error if you try to change anything), then the transaction log can indeed be tiny. You are right that worktables, just as temmporary tables and tale variables, will be created (and logged) in tempdb.

I am trying to think if there is any log activity generated at all in a read-only database. Perhaps for registering backups? Other than that, I don't know.

However, if you database is read only in the meaning of "I think there are no modifications", then you probably need a larger log. There might be activites going on that you are not aware of. (Including internal jobs).

Hugo Kornelis, SQL Server/Data Platform MVP (2006-2016)
Visit my SQL Server blog: https://sqlserverfast.com/blog/
SQL Server Execution Plan Reference: https://sqlserverfast.com/epr/

keymoo SSCrazy Points: 2975 More actions · Answer 2

Thanks for the reply Hugo, no the database is in READ_ONLY mode, i.e. as you said with the ALTER DATABASE statement.

Steve Jones - SSC Editor SSC Guru Points: 736212 More actions · Answer 3

I side with Hugo here, you shouldn't need the log file. In fact, you probably can shrink this down as low as possible for your database. I'd guess 1MB would work.

However, keep in mind that if you discover you want to change anything, add an index, rebuild indexes, etc., then you need the log file for these operations. I might leave something of a log file just in case I want to change anything. The size there probably depends on any possible actions you may take.

Ed Wagner SSC Guru Points: 287026 More actions · Answer 4

If it's truly read-only, then I'd guess that all index rebuilds and other optimizations should have already been done. Since you don't have any changes being written, I'm with Hugo and Steve - shrink the log down to 1 MB and call it good. I'd imagine you already have it in simple recovery mode.

Tally Tables - Performance Personified
String Splitting with True Performance
Best practices on how to ask questions

Jeff Moden SSC Guru Points: 1004343 More actions · Answer 5

Ed Wagner (2/10/2016)
If it's truly read-only, then I'd guess that all index rebuilds and other optimizations should have already been done. Since you don't have any changes being written, I'm with Hugo and Steve - shrink the log down to 1 MB and call it good. I'd imagine you already have it in simple recovery mode.

I can't help but think about how much "free space" may be tied up in the Read_Only file.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)