Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Rough calculation of RAM I should have. 3

Status
Not open for further replies.

HestonJames

Programmer
Aug 11, 2008
187
GB
Guys,

How would you guestimate the amount of RAM that is required for your instance of SQL server to run efficiently, I've found a few discussions on the web about it with one or two suggesting that you should use the datafile size for your database as a rough gauge as to how much RAM you need in the machine, is that correct?

In this particular scenario we have a single database attached to the server which has a data file of about 1.8GB and log file of about 5.6GB.

Presumably we want enough memory for SQL to be able to store any data which its handling for queries so that it doesn't start caching on the disk (which is what I think is happening at the moment). Is there any nice way of seeing how large the datasets my queries are generating are?

I'd be interested to hear your thoughts on this.

Heston
 
For a database that size you'll need at least 3 gigs of RAM.

You might want to backup your transaction log. For a database 1.8 Gigs in size the log shouldn't need to be 5.6 Gigs in size.

3 Gigs of RAM will give your server room for caching the data as well as plenty of room for the procedure cache.

Denny
MVP
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / SQL 2008 Implementation and Maintenance / Microsoft Windows SharePoint Services 3.0: Configuration / Microsoft Office SharePoint Server 2007: Configuration)
MCITP Database Admin (SQL 2005/2008) / Database Dev (SQL 2005)

My Blog
 
Hello Mr Denny,

Thanks for the info on that, certainly makes very good sense. Now, I've not been told about backing up the transaction log before, is this something which should be done as a regular task? How do I do it? Where should I be storing the backup of the file?

At the moment we're only running 2GB of RAM on the system and much of that is taken by other services, this is no doubt contributing to the slow performance we're seeing on occasions. As part of our new server build I'll be sure to slap in a whole stack of memory.

Cheers,

Heston
 
I would start with 4GB on the server with SQL configured to use 3GB of it, leaving 1GB for the OS and other services.
 
MDXer, thank you. I'm looking into this stuff now.

Could you guys please give me a little advice on that transaction log which I've got, how exactly should I go about backing it up? Should this be a scheduled task on the system which runs regularly?

I'd appreciate your thoughts on the best way to manage that databases back. Lets for the moment assume that we're backing up to disk, what's the best way to back it up?

From reading around a little most people seem to run incremental backups daily or something like that with the log file being truncated at the end of each backup?

I'd really appreciate some 'dummies guide' style advice on it. :-D I'm an app developer not a DBA but unfortunately this is my responsibility so I'd best start learning :)

Heston
 
You can figure the full backup of a DB to roughly the same size as the DB if you use Native SQL backups.

Thanks

J. Kusch
 
Jay,

Thanks for getting back to me mate. I used that script to create the backup jobs but wasn't really happy as I wasn't sure quite what it was doing so I removed all the tasks from the Agent.

I used the Maintenance Wizard in SSMS to create a daily backup task which with any luck should see me through, after running once I saw the database and the transaction logs shrink right down so I'm happy with that for now!

Thank you again guys,

Heston
 
If you don't backup your transaction log regularly then it will begin to grow again.

If you don't need to be able to restore the database to a specific point in time, then change the database recovery level from FULL to SIMPLE.

Denny
MVP
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / SQL 2008 Implementation and Maintenance / Microsoft Windows SharePoint Services 3.0: Configuration / Microsoft Office SharePoint Server 2007: Configuration)
MCITP Database Admin (SQL 2005/2008) / Database Dev (SQL 2005)

My Blog
 
Hi MrDenny,

The current plan I have in place see's a full backup every night and then transaction log backups every hour, this is because the data grows so regularly that I need to thorough backup of it. This has been running over night and seems to work nicely.

The only thing I'm yet to do it configure SQLAgent mail so that it send me notifications of its backup tasks, just so I can keep an eye on things. From what I saw yesterday it wanted to use outlook to post mails, this seems like a horrible idea, is there a way just to give SQL server a bunch of mail server details and have it post direct?

Thanks for your suggestions guys,

Heston
 
You can roll your own emailer via a job by leveraging xp_smtp_sendmail.

Thanks

J. Kusch
 
If you are using SQL 2000 you can use xp_smtp_sendmail (google for it) to send mail without having Outlook installed.

If you are using SQL 2005 and newer you should be using Database Mail which uses SMTP to send emails.

Denny
MVP
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / SQL 2008 Implementation and Maintenance / Microsoft Windows SharePoint Services 3.0: Configuration / Microsoft Office SharePoint Server 2007: Configuration)
MCITP Database Admin (SQL 2005/2008) / Database Dev (SQL 2005)

My Blog
 
Thank you both,

Denny, I'm using 2k5 so I'll go on a hunt around for Database Mail configuration and get that little puppy setup.

Heston
 
Denny, or anyone else.

I've been playing around with this but not quite got it all working. I've done the following:

1) Configured database mail and sent a test mail to myself using it, this works great :)

2) Set SQL Agent to use the mail profile I just created in database mail.

3) Created a SQL Agent operator for myself using my email address.

4) Setup email notification to that operator for when 'the task completes' for my backup job.

5) Run the backup job.

However, I don't get any email notification sent through to me :-(

Am I missing something here? I know the Database Mail is configured correctly because it sends me the test emails just fine, notifications from SQL Agent are not coming through though.

Cheers,

Heston
 
Scratch that ;-)

I noticed in the error logs that it was trying to send an email and no mail session was set, despite me having set one :) I quickly restarted the Agent service and it started sending emails as expected!!

Thanks,

Heston
 
Yep, Agent restart is still required.

Denny
MVP
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / SQL 2008 Implementation and Maintenance / Microsoft Windows SharePoint Services 3.0: Configuration / Microsoft Office SharePoint Server 2007: Configuration)
MCITP Database Admin (SQL 2005/2008) / Database Dev (SQL 2005)

My Blog
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top