SQL Server memory and Log questions

Question

SQL Server memory and Log questions

Godin

Old Hand

Points: 331
More actions
May 8, 2007 at 11:09 am

#69805

Hi,
Sorry, I initially posted this in the wrong forum.
I'm new at using SQL Server 2000. I have a couple of basic questions related to it's administration. This is a Production system that we inherited
1) This is a Windows 2003 using VMWARE, it's has only 512M of memory allocated to it. To me this seems a bit low, but users indicated that they do not see any performance problems. I checked some of the "Performance" stats like Buffer Cahce Hit ratio but they look OK. This system supports about 30 concurrent users, what is usually recommended in term of memory and how can this be monitered/justified ?
2) We currently have FULL as a recovery model and do a complete backup every night of the Data and Transactiong logs, this was shceduled via the Database Maintenance Plan.
a) I believe that the log will get truncated automatically, right ? Also is there any way to see the unused/free space in the log file.
b) The Master and Msdb does not backup the Transaction logs, even if it's mentionned as part of the Maintenance plan,this intended behaviour, right ?
Thanks in advance

Viewing 4 posts - 1 through 3 (of 3 total)

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

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 1

I can help answer part of your questions, mainly question 2.

The transaction log is not truncated by either full or differential backups. To truncate the transaction log, you need to run a backup log. As you have the database setup in full recovery model, I am assuming you may want to be able to restore the database to any point in taime should there be a failure. If so, you need to schedule periodic transaction log backups during the day. How often depends on your business rules and how much activity occurs on the database.

As for master and msdb, both of these system databases are using the simple recovery model and transaction log backups can not be done in this model. Also, I am fairly sure you can't change the model on master and possibly not on msdb.

hth!

mngong SSCertifiable Points: 6167 More actions · Answer 2

Without knowing your application you could definitely utilize more memory than 512M..

Mike

Shawn McGehee Ten Centuries Points: 1239 More actions · Answer 3

It is definately hard to tell if your needs will be supported at 512MB of memory since we do not know how the application/load will impact the server. Running performance monitor and checking the server during usage will give you a better idea. Pay attention to cache hit percentages, the higher the better.