put tempdb in memory

Hi all,
We have 4GB of memory on the server and have a 2GB
database. Is there a way to either put our database or
the tempdb in memory? We are runnning into disk queues.
(disks are configured with RAID1+0. disk configuration is
optimized.)
thanks in advance,
Anna> We have 4GB of memory on the server and have a 2GB
> database. Is there a way to either put our database or
> the tempdb in memory?
Support for this configuration went away with SQL Server 6.5 (for good
reason). Surely there are better uses for your RAM than storing data.|||Although you cannot place a database into memory you can pin a table into
memory with the DBCC PINTABLE statement (see BOL). Pinning tables into
memory is not recommended.|||Thank you so much, Bianca and Aaron!!!
>--Original Message--
>Hi all,
>We have 4GB of memory on the server and have a 2GB
>database. Is there a way to either put our database or
>the tempdb in memory? We are runnning into disk queues.
>(disks are configured with RAID1+0. disk configuration is
>optimized.)
>thanks in advance,
>Anna
>.
>

purpose of master and tempdb databases

Hi all,
COuld you pls explain the purpose of master database and tempdb. Can anyone compare this with an oracle database.
Thanks and regards,
Retna
The system databases are well documented in Books Online. Below is a good start:
http://msdn.microsoft.com/library/de...r_da2_1lcx.asp
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
"Retna" <anonymous@.discussions.microsoft.com> wrote in message
news:A6BD97A2-FE72-4801-B52C-4E9FA44D8DF6@.microsoft.com...
> Hi all,
> COuld you pls explain the purpose of master database and tempdb. Can anyone compare this with an oracle
database.
> Thanks and regards,
> Retna
|||Hi,
Add on to the article mentioned by Tiber , for comparison with Oracle,
SQL Server Oracle
-- --
Master Database System Table Space
Tempdb Temporary tablespace (Where all sort, group, temp
tables operations resides)
So each Oracle database will have a System Table space and Temp table space
during installation itself. This is same as
Master and Tempdb databases in SQL server
Thanks
Hari
MCDBA
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:O0SFiQaMEHA.1556@.TK2MSFTNGP10.phx.gbl...
> The system databases are well documented in Books Online. Below is a good
start:
>
http://msdn.microsoft.com/library/de...us/architec/8_
ar_da2_1lcx.asp[vbcol=seagreen]
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
>
> "Retna" <anonymous@.discussions.microsoft.com> wrote in message
> news:A6BD97A2-FE72-4801-B52C-4E9FA44D8DF6@.microsoft.com...
anyone compare this with an oracle
> database.
>
|||Thanks all.
|||You'll find a lot of info in Books Online and googling. Needless to say,
it's a bit more complicated than this, but...
master is used by SQL Server to store meta data assoicated with the server.
You COULD put user objects here but it is not normally a best practice.
tempdb is recreated from scratch each time the server starts. It's used to
store work tables created by SQL for processing a query and temp tables (#,
##, etc...) you create will go here.
Brian Moran
Principal Mentor
Solid Quality Learning
SQL Server MVP
http://www.solidqualitylearning.com
"Retna" <anonymous@.discussions.microsoft.com> wrote in message
news:858D968F-7C80-4BA4-8E53-F3CF07B53AC1@.microsoft.com...
> Thanks all.
>
sql

purpose of master and tempdb databases

Hi all
COuld you pls explain the purpose of master database and tempdb. Can anyone compare this with an oracle database
Thanks and regards
RetnaThe system databases are well documented in Books Online. Below is a good start:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/architec/8_ar_da2_1lcx.asp
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
"Retna" <anonymous@.discussions.microsoft.com> wrote in message
news:A6BD97A2-FE72-4801-B52C-4E9FA44D8DF6@.microsoft.com...
> Hi all,
> COuld you pls explain the purpose of master database and tempdb. Can anyone compare this with an oracle
database.
> Thanks and regards,
> Retna|||Hi,
Add on to the article mentioned by Tiber , for comparison with Oracle,
SQL Server Oracle
-- --
Master Database System Table Space
Tempdb Temporary tablespace (Where all sort, group, temp
tables operations resides)
So each Oracle database will have a System Table space and Temp table space
during installation itself. This is same as
Master and Tempdb databases in SQL server
Thanks
Hari
MCDBA
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:O0SFiQaMEHA.1556@.TK2MSFTNGP10.phx.gbl...
> The system databases are well documented in Books Online. Below is a good
start:
>
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/architec/8_
ar_da2_1lcx.asp
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
>
> "Retna" <anonymous@.discussions.microsoft.com> wrote in message
> news:A6BD97A2-FE72-4801-B52C-4E9FA44D8DF6@.microsoft.com...
> > Hi all,
> > COuld you pls explain the purpose of master database and tempdb. Can
anyone compare this with an oracle
> database.
> > Thanks and regards,
> > Retna
>|||Thanks all|||You'll find a lot of info in Books Online and googling. Needless to say,
it's a bit more complicated than this, but...
master is used by SQL Server to store meta data assoicated with the server.
You COULD put user objects here but it is not normally a best practice.
tempdb is recreated from scratch each time the server starts. It's used to
store work tables created by SQL for processing a query and temp tables (#,
##, etc...) you create will go here.
--
Brian Moran
Principal Mentor
Solid Quality Learning
SQL Server MVP
http://www.solidqualitylearning.com
"Retna" <anonymous@.discussions.microsoft.com> wrote in message
news:858D968F-7C80-4BA4-8E53-F3CF07B53AC1@.microsoft.com...
> Thanks all.
>

purpose of master and tempdb databases

Hi all,
COuld you pls explain the purpose of master database and tempdb. Can anyone
compare this with an oracle database.
Thanks and regards,
RetnaThe system databases are well documented in Books Online. Below is a good st
art:
http://msdn.microsoft.com/library/d...r />
_1lcx.asp
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
"Retna" <anonymous@.discussions.microsoft.com> wrote in message
news:A6BD97A2-FE72-4801-B52C-4E9FA44D8DF6@.microsoft.com...
> Hi all,
> COuld you pls explain the purpose of master database and tempdb. Can anyone compa
re this with an oracle
database.
> Thanks and regards,
> Retna|||Hi,
Add on to the article mentioned by Tiber , for comparison with Oracle,
SQL Server Oracle
-- --
Master Database System Table Space
Tempdb Temporary tablespace (Where all sort, group, temp
tables operations resides)
So each Oracle database will have a System Table space and Temp table space
during installation itself. This is same as
Master and Tempdb databases in SQL server
Thanks
Hari
MCDBA
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:O0SFiQaMEHA.1556@.TK2MSFTNGP10.phx.gbl...
> The system databases are well documented in Books Online. Below is a good
start:
>
http://msdn.microsoft.com/library/d...-us/architec/8_
ar_da2_1lcx.asp
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
>
> "Retna" <anonymous@.discussions.microsoft.com> wrote in message
> news:A6BD97A2-FE72-4801-B52C-4E9FA44D8DF6@.microsoft.com...
anyone compare this with an oracle[vbcol=seagreen]
> database.
>|||Thanks all.|||You'll find a lot of info in Books Online and googling. Needless to say,
it's a bit more complicated than this, but...
master is used by SQL Server to store meta data assoicated with the server.
You COULD put user objects here but it is not normally a best practice.
tempdb is recreated from scratch each time the server starts. It's used to
store work tables created by SQL for processing a query and temp tables (#,
##, etc...) you create will go here.
Brian Moran
Principal Mentor
Solid Quality Learning
SQL Server MVP
http://www.solidqualitylearning.com
"Retna" <anonymous@.discussions.microsoft.com> wrote in message
news:858D968F-7C80-4BA4-8E53-F3CF07B53AC1@.microsoft.com...
> Thanks all.
>

Purging TempDB

Hi DB Experts

I have a question on shrinking tempDB. Currently, I am using MS SQL 2005 Server, I have a software that used SQL DB but I had created a separate database to store my data. The tempDB is totally not used at all.

Problem - Whenever I export data into my created database on MS SQL server, tempDB also grew, I noticed that the files grew so large that it crashed the server. I am running 50GB free space on my drive where by the MS SQL server was installed.

Question - May I know are there any solution to shrink or freeze the growth of tempDB size?

Best regards

TEWCT

This statement

The tempDB is totally not used at all.

is totally wrong!

TempDb is constantly used as a staging area (or 'scratchpad') when gathering data for queries.

TempDb will continue to grow as long as there is a need. You can take the server off line (or stop the server service) and remove the TempDb database, and it will be recreated when the server starts up.

|||

You should in fact size your tempdb appropriately to increase performance.

If the export is causing your tempdb to grow to 50GB you should carefully look at the queryplans used in the export (or you are exporting a massive amount of data).

WesleyB

Visit my SQL Server weblog @. http://dis4ea.blogspot.com

|||

Here's some more info on how to properly plan for TempDB sizing and also how to troubleshoot when it grows too large. The second link also includes instructions on how to limit the size of TempDB.

http://msdn2.microsoft.com/en-us/library/ms176029(sql.90).aspx

http://msdn2.microsoft.com/en-us/library/ms175527(SQL.90).aspx

Hope that helps,

-Steve

|||

Hi Steve and guys

Greetings. Thks for your advices & responses. Apologized, I am not a SQL guru. Am I right to say tempDB will definitely be used even if I created a separated DB for my application; even when I perform any simple query or update? My growth rate is about 500MB per day which I have been monitoring it over 3 days for now.

I had also performed some research from MSDN library that Steve had adviced, it seemed that it is a Microsoft methodology that SQL server behaviour;

i) TempDB size will definitely grow;

ii) DB tuning is required

iii) Factor more disk space for the growth?

iv) Restarting SQL service on regular basis

I would like to know if there is any specific way other then to re-provision and solve this SQL server tempDB growing issue?

|||

In a production server, the growth of TempDb 'should' eventually stablize. There may be regularly scheduled reports, etc., that require a large amount of space. There may be 'regular' data import/exports (ETL) activities that will require a large amount of space.

After your database is in production, and the initial 'load' activity has subsided, you may be able to reduce the size of TempDb by restarting the SQL Service -but if there is extensive data querying for reports or ETL activities, it may just grow again. So give it time, determine the 'appropriate' size, and let it be.

|||

Also have a look at this article:

How to shrink the tempdb database in SQL Server
http://support.microsoft.com/kb/307487/en-us

|||Hi guys. Thanks for your information. I will try that out and update at a later stage.

Public user; tempdb permissions

We have financial application that uses SQL 2000 as the backend database. As part of the setup we were told that the "public" user account needed "full permissions" to the tempdb.

So, I right-click on the tempdb and go the the properties and then to the permissions and give "public" rights for all options.

Everytime the system is restarted after MS critical updates or any other reason the "public" user is reset back to the default settings and I have to go back in and reset the permissions for "public" in the tempdb.

Anyone know why this is happening? We have a production box and a development box and they both do the same thing.

Thanks for any insight.just curious, but did they give you any reason for having to do that?

I can't think of any reason that you'd have to. Have you tried just leaving it alone? If so, do the users get any kind of errors?

also curious as to the application that's requiring you to do something like that|||The application is Portia from Thomson Financial. It is a portfolio management app.

If the public user does not have rights to the tempdb then some reports that users run will return no data and other problems like funny application error messages.

They didn't give a reason why this is required, but I am guessing it is a cover for poor application design.|||your guess is correct. my hunch is they create permanent objects in tempdb (each user upon being added to a database is automatically enrolled into public group of that database. in properties window of tempdb they require permissions given to public to create all objects, not sure about other functions like backup, etc.)|||You can't backup a TempDb !! All users have permissions in a temp db and only they can access the temp table created with "#" Prefix. However , a Permanent temp table created by one user is owned by that user and behaves like any other table in a regular database . All those permissions and object would be gone after the SQL is restarted because tempdb , as the name says is a temporary database and is recreated each time SQl Server restarts|||a permanent object cannot be created in a database, including tempdb, unless a user is explicitly granted permissions to create that type of object or the user belongs to the database group/role or the server fixed role that has those permissions. in this discussion the vendor requires public to have permissions to create certain objects in tempdb. of course it gets recreated every time the service restarts! that's not the question! the question is how to make it so that granting permissions will not be the chore to do every time it happens. i'd go with a startup procedure that would grant the required permissions to public.|||I think that besides creating a startup procedure to grant the rights you need to call that company and tell them that thier developers need to take a look at what they are doing and add 'reboot server and test functionality again' to thier QA process. I sure hope that's an alpha or beta release of that software... what a freakin' nightmare.

why in the world wouldn't they just create the objects in the database they are working in? or store those objects in another database if they had some concern about the objects being there.

sorry to sound ranty... that kind of stuff just plain bothers me|||you're right on man, right on!|||Though I never tested it , but objects created in Model, would they be created in tempdb when system restarts ? OR Tempdb doesn't copy model structure ?|||Originally posted by aashu
Though I never tested it , but objects created in Model, would they be created in tempdb when system restarts ? OR Tempdb doesn't copy model structure ?

BOL:

The model database is used as the template for all databases created on a system. When a CREATE DATABASE statement is issued, the first part of the database is created by copying in the contents of the model database, then the remainder of the new database is filled with empty pages. Because tempdb is created every time SQL Server is started, the model database must always exist on a SQL Server system.|||If that is the case , Why not put all those users and permissions in Model !!