April 17, 2009 at 2:44 pm
I'm not a dba, but I'm requesting a new database be created and the dba is asking for the size of the transaction log file needed. I'm guessing the db to be around 70-75GB, but there won't be any updating to it - it will solely be used for querying. It's being replicated over from the production server. Therefore, I'm not sure what size transaction log file to tell them? I'm assuming "transactions" are DML (Insert/Update/etc.)? If so, then would it hurt to have the size smaller than the db? Or is the size of the transaction log file also affected by queries and/or temp tables that get dropped at the end of the query?
Thanks for any help!!
April 17, 2009 at 3:20 pm
If it's a subscriber set the recovery mode to SIMPLE, you can let the log manage itself. Maybe you want to put a cap on growth. If you needed more than 2GB I would be surprised, but work on the safe side and set the cap to 5GB with 500MB increments.
Pop in an alert should the log grow to more than 4GB and let 'er fly.
April 17, 2009 at 3:20 pm
tell him to put the db in simple recovery.
let it grow as needed for the initial load of the database then shrink it back down once done.
April 17, 2009 at 3:31 pm
Thanks so much everyone!!!
April 18, 2009 at 11:58 am
lk4772 (4/17/2009)
the dba is asking for the size of the transaction log file needed.
No, SQL server will chose the size of the transaction log file. A dba should know that, the only thing he should worry about is to control the size of the tlog file. You would not specify the size of a transaction log file as part of your specifications. That would be wierd.
It's being replicated over from the production server.
Are you using Replication or Bakcup and restore method to update this database from the production server?
I'm assuming "transactions" are DML (Insert/Update/etc.)?
Transactions are any operations that are done against the database not just DML, could be DDL too.
If so, then would it hurt to have the size smaller than the db?
No, large transaction files does not affect the performance of the sql server AFAIK. only a huge file might affect, but it more relates to more on managing the disk space efficiently not on a performance hit.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply