"TempDB in RAM. This option allows SQL Server to process the results of intermediate database operation in memory rather than on the hard disk. If you have enough memory available, using TempDB in RAM can dramatically improve performance of sorting, GROUP BY calculations, joins and queries that require temporary work tables"
Hi, that was a quote from MS white paper over the internet... Does that mean that I can get faster query output once I used the temp DB in Ram... what other things which I do not know that I may worry about if I use tempDB in Ram...
when allocating some ram to the tempdb, does that mean that I am not going to use it ... Please advice
I'm trying to build a sproc with a where cluse that looks at a boolean field. I need to say if [Primary] is 1 then show all primary phones. If [Primary] is 0 or Null then return all records. I have it started like this;
WHERE dbo.tblPhones.[Primary]=Isnull(@PrimOrNot,dbo.tblPhones.[Primary]=1) Or dbo.tblPhones.[Primary]=0
I am developing an application that uses SQL Server 2000 for the back end. I am at the stage where some modules in the app can be tested while I finish development on some others. I run my own tests against SQL Server running on my own PC but for other people to test I have set up another server with SQL Server 2000 and have restored my database there.
My question is as follows: I would like any changes to my database (structure and data) to be replicated on the test server's database (not necessarily immediately, but without much delay). I've heard the buzz words (log shipping, replication, etc) but would like some advice on the best way to proceed. At the moment I don't need any data back from the test server and I don't particularly care if test data on that server is lost although these may become issues later on.
I need to pass a parameter to a stored procedure that allows the user to select all store numbers or a combination thereof. The portion that selects all works fine. I am have trouble with the later. My code looks like this:
IF @StartDate IS NULL BEGIN Set @StartDate = GetDate() Set @EndDate = (GetDate ()+90) END Declare @StoreList TABLE ( CenterID int )
-- Get our Centers IF ( @Center = 'ALL' ) or ( @Center IS NULL ) BEGIN INSERT @StoreList SELECT DISTINCT POS_Site_ID FROM LVIGP.dbo.POS40108 (nolock) END ELSE BEGIN
INSERT @StoreList SELECT DISTINCT POS_Site_ID FROM LVIGP.dbo.POS40108 (nolock) WHERE POS_Site_ID = @Center END
the first if statement returns all of the store number like this CenterID 001 002 010 024 057
the second if returns nothing I tried using coalesce but it returns the store number like this: CenterID 001, 002, 010, 024, 057 This does not work with the rest of my program.
Wondering if anyone has pro's and cons for choosing a vehicle to build front end applications for a SQL Server 2000 back end.
Historically we used Access 97 with VBA code to create client side ODBC connections. In Code we would issue stored procedures through a connection string. This worked fine.
With Microsoft moving onward our company is transitioning to supported products. So DAO is dead.
My colleagues like Access 2000 and have imported some 97 databases doing small re-writes to make them work.
Everything I read says we should be interacting with SQL using ADO.
The other problem is not all users have admin rights on their machines, so adding application extensions for VB6 is a problem. We might be able to change this.
Should we just build with Access 2000 from scratch using ADO to avoid JET?
Should we use VB6?
Is there another application we should be looking at?
I am searching for a resolution. I did a few searches on this site and could not gain the exact answer. I apologize is this thread is a duplicate.
I have written a report containing Multi-Values for Reporting Services. When a user selects the values for the report parameters, the checked values continue to be selected after the report is completed.
Is there a way to clear these parameters?
Some of our users run the same report more than once during a single web session.
I have to create an expression that makes bold the text for some possibilities, otherwise the font will keep same, is there any staff for creating an expression like "IF ID_Name IN (x,y,z...) THEN bla-bla" like querying in SQL? Cuz I have to use multiple possibilites for same condition
I'm building a hosted website and I am using SQL 2005. The DBA for the host has told me that i can not encrypt a symmetric key with a certificate, when using that symmetric key for encryption. As i read that this method provided optimum performance/ security for encrypting columns of data.
The DBA told me i can use a cert or a symmetric key for encryption. I have searched for comparisons and found a blog entry by Laurentiu Cristofor comparing certs with asymmetric keys. Which leads me to believe that certs and asymm are very different than symmetric keys.
My question is which is the best choice in a hosted environment for column encryption, a cert or symmetric key. Which is more secure? Does one offer a significant performance (dis)advantage?
Has anyone encounted a problem with Reporting Services (2005) with the following scenario: A report with a dropdown parameter allowing a Select All choice -
If the selection list is fairly long and Select All is selected - if the user clicks the View Report button the selection list is completely reset (i.e. all items are unselected). This seems to happen if the user quickly clicks the View Report button immediately after clicking the Select All box on the parameter dropdown.
This happens inconsistently - some users experience it frequently while others do not see it at all (using the same parameter values, etc.)
"tempdb is skipped. You cannot run a query that requires tempdb"?
We're running a .Net web application with a SQL Server 2000 backend, and we get the error intermittently. Restarting the SQL Server service seems to fix it, as it causes tempdb to be rebuilt, but this isn't a long term solution. Any direction or hints would be greatly appreciated. Thanks! - Mike
THis is sql server 6.5 question. I have tempdb data device size default 2 MB, which has completely filled up. I am trying to expand data device to it. I created new device tempdb_data_ext (250 MB) and tried to expand tempdb data device. But everytime I do it, it ends up adding space to tempdb log device. How can I expand tempdb data device?
Hi, How can I control the growth of tempdb in SQl server.It's growing like anything. CAn I create some alerts or jobs and what those alerts/job are supposed to do? All help appreciated. Jai
Hello! This is error message I discovered in NT even viewer: c:MSSQL7DATATEMPDB.MDF: Operating system error 112(There is not enough space on the disk.) encountered.
In SQL Server error log the errors are: Error: 1101, Severity: 17, State: 10
Could not allocate new page for database 'TEMPDB'. There are no more pages available in filegroup DEFAULT. Space can be created by dropping objects, adding additional files, or allowing file growth..
Currently tempdb rezides on C drive and it's almost out of space. What should I do? Detach tempdb and then move to different drive? What's the procedure?
TEMPDB in one of our production servers does not clear up so every three to four weeks I have to restart NT. Nothing like this happens on any of the other three servers. Does anybody know where I should look at to correct the problem. I sure would appreciate it. Thanks Shashu
I have never done this before and thought I would ask. Is it possible to detach the tempdb database, move it to another drive or partition, and then re-attach it? What would be the downside or side-affects to doing such a thing?
Error : 933, Severity: 22, State: 1 Logical page 258 of the log encountered while retrieving highest timestamp in database 'tempdb' is not the last page of the log and we are not currently recovering that database.
I use sqlserver -T4022 to start my SQL Server since it will not start with out it. When I start sqlserver without the option, it tells me that
Error : 615, Severity: 21, State: 1 Unable to find database table id = 2, name = 'tempdb'.
I need to move tempdb to another drive,also increase the size.Largest database is 15GB.Can anyone suggest the size and also the exact commands to move.Do I need to backup the databases before I do this task?If SP1 is not installed,will it be o.k for me for this tempdb problem.If we have a larger tempdb like 4GB,will it effect anything?...Urgent!!
I read an article on this site by Michael Hotek re "Basic SQL Server 6.5 Configuration Options". In the paragraph about TempDB he says that you should always avoid using Temp tables in stored procs. I use this feature a lot when trying to do "not in" type queries (I filter out a portion of a larger table and then use the "not in" on the temp table rather than the entire table.) Is there a better way to run a Not in query. I have the table well indexed (i think) but it seems to do a full table scan if I use the entire table.
Our Tempdb.mdf file is 11 gigs. I have tried several things to shrink this but with no luck. Does anybody have a suggestion on how I can free up that space. I have tried to re-start Sql but that didn't do anything. I thought that there was a bug, if the files got above 4 gig that sql wouldn't clear them, but I could be wrong
I thought I could detach it, and attach a new file, but makes me nervous without knowing if that’s correct.
I am trying to configure a 6.5 server to set the tempdb to run off disk. I reset the tempdb in ram = 0 in the configuration, and restarted the service, but it left it as running in ram, with 0 configured. I then rebooted the server, and it still left the tempdb in ram. Any ideas?
I am trying to get some information about tempdb database. I've tried BOL but I couldn't find a whole lot of info. I am trying to find out what size should tempdb be to not to cause problems. Also, I am trying to shrink tempdb by using shrink database option in EM, but it only shrinks the tempdb transaction log not the datafile, and I don't know why it is happening.
We are using Peoplesoft HRMS version 7.0 on NT Server 4.0 and SQL Server 6.5, service packs on both up-to-date.
The server is a HP LX Pro with 4 200 MHz 1MB cache processors with 2,560 MB of RAM and 18 9GB drives configured with different combinations of RAID 1, 1+0, and 5.
The database is 2.3GB in actual size with the specific size of 6GB for data and 2GB for log on separate RAIDs.
We would like to use tempdb in RAM to boost the speed of complex queries that use the tempdb for intermediate working tables.
We would like to know of any experience of running tempdb in RAM, issues to overcome, of how to setup.
There are articles and books not recommending using tempdb in RAM. Comments anyone?
Note: Have looked in SQL Server DBA Survival Guide, and SQL Server Unleashed, along with the normal MS Documentation, technet, online books, and knowledge base searches.
Everything I've ever heard or seen says not to put tempDB in RAM, citing reasons of negligible benefit or even worse performance as it steals memory from SQL's cache memory.
My issue is I need to take anywhere from 500 to 3000 records (games played) in a batch every five minutes from a table, ordered by game id. There's no guarantee of sequential order to how the records are inserted, so I need to do an ORDER BY in my query. I also do a stored procedure for ranking that requires an ORDER BY.
The performance difference between the two is staggering. 1000 records with a 200 mb tempdb database (not in RAM) takes about 300 seconds. With a 16 MB tempdb database IN ram, it takes just under 127 seconds. Only problem? I'm constantly getting, after a few batches have run, "out of space in syslogs for tempdb ram messages. "
Is there a way to net such performance from tempdb without putting it in RAM, and if not, is there a way to explicity clear out tempdb. Thanks for any help.
I have a problem with Tempdb database on SQL 6.5 server.....when i create a temporary table, i block other users in other databases. Actually i run a stored procedure which creates a temporary table and then the procedure insets some values of a table from my test database. When the spored procedure is executed, users in other databases also get blocked and no new user will be allowd to log on to the server. When i check as to what the other users are executing, i find that they are also creting or droping a temp table. So i will have to either kill my SP or let it finish and continue blocking other users.
As of now, i am not able to find a reason for that and i try to run the Stored procedure in offpeak hrs.
I have not played around much with Temp tables in SQL 6.5. Pl do suggest me as to what to do....
Some details you would be interested on :
Server - SQL Server 6.5 Front End - Great Plains Size of Tempdb - Data Space Available( It says shared with data) database_name database_size unallocated space ------------------------------ ------------------ ------------------ tempdb 1502.00 MB -6301.82 MB
I have a dumb ? When I go in to expand the tempdb to at least 25mb req. for the migration It is actually expanding the Log size & the Db available size still says 1.76mb.
