Master.sys.master_files WHERE Database_id = 2 &&<&&> Tempdb.sys.database_files
Oct 30, 2007
We had an issue last night where tempdb ran out of space on one of our servers. Upon investigation I found that SSMS was reporting only one data file assigned to tempdb. I knew this couldn't be the case and dove into the system tables to figure out what is going on. I found what appears to be data corruption. There are a number of file records that appear in the master.sys.master_database_files that do not appear in the sys.database_files table in tempdb.
I tried to add files that it showed already existed and they of course errored out. I restarted SQL Server to get tempdb rebuilt and that did not work either.
Has anyone else ever ran into this? What was the resolution?
Here are some queries to demonstrate what I am seeing. Terrible formatting I know, but I don't know of a better way.
/*------------------------
select * from master.sys.master_files where database_id = 2
select * from tempdb.sys.database_files
------------------------*/
database_id file_id file_guid type type_desc data_space_id name physical_name state state_desc size max_size growth is_media_read_only is_read_only is_sparse is_percent_growth is_name_reserved create_lsn drop_lsn read_only_lsn read_write_lsn differential_base_lsn differential_base_guid differential_base_time redo_start_lsn redo_start_fork_guid redo_target_lsn redo_target_fork_guid backup_lsn
----------- ----------- ------------------------------------ ---- ------------------------------------------------------------ ------------- -------------------------------------------------------------------------------------------------------------------------------- ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ----- ------------------------------------------------------------ ----------- ----------- ----------- ------------------ ------------ --------- ----------------- ---------------- --------------------------------------- --------------------------------------- --------------------------------------- --------------------------------------- --------------------------------------- ------------------------------------ ----------------------- --------------------------------------- ------------------------------------ --------------------------------------- ------------------------------------ ---------------------------------------
2 1 NULL 0 ROWS 1 tempdev E:MSSQL$S1MSSQL.2MSSQLDATA empdb.mdf 0 ONLINE 524288 1280000 128000 0 0 0 0 0 NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL
2 2 NULL 1 LOG 0 templog E:MSSQL$S1MSSQL.2MSSQLDATA emplog.ldf 0 ONLINE 128000 268435456 64000 0 0 0 0 0 NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL
2 3 01E3A12D-9CC1-44B6-B077-947C45BD5FDC 0 ROWS 1 tempdb_Data_01 K:MSSQL$S1Data empdb empdb_Data_01.ndf 0 ONLINE 524288 -1 0 0 0 0 0 0 50000000011100001 NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL
2 4 4E91B5E8-1EE9-4DA1-985F-A37CB7262BC6 0 ROWS 1 tempdb_Data_02 K:MSSQL$S1Data empdb empdb_Data_02.ndf 0 ONLINE 524288 -1 0 0 0 0 0 0 50000000013400001 NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL
2 5 125B73CC-A6EB-464E-A8B8-50269EFD1311 0 ROWS 1 tempdb_Data_03 K:MSSQL$S1Data empdb empdb_Data_03.ndf 0 ONLINE 524288 -1 0 0 0 0 0 0 50000000015700001 NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL
2 6 6C4CDA80-4AF0-42FE-BFCF-4E6AEF97956A 0 ROWS 1 tempdb_Data_04 K:MSSQL$S1Data empdb empdb_Data_04.ndf 0 ONLINE 524288 -1 0 0 0 0 0 0 50000000018000001 NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL
2 7 360F5EE6-5207-402E-BFFC-88F2B4532DD2 0 ROWS 1 tempdev_11 S:MSSQLDATATEMP empdev_11.ndf 0 ONLINE 512000 1280000 128000 0 0 0 0 0 1347000000109400212 NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL
2 8 60F262B1-32AC-433C-95CE-8450BBFA2247 0 ROWS 1 tempdev_12 S:MSSQLDATATEMP empdev_12.ndf 0 ONLINE 512000 1280000 128000 0 0 0 0 0 1348000000169600151 NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL
2 9 A18FA498-37A6-4A52-ABA6-C915FD50123E 0 ROWS 1 tempdev_13 S:MSSQLDATATEMP empdev_13.ndf 0 ONLINE 512000 1280000 128000 0 0 0 0 0 1349000000001600060 NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL
2 10 2DC2BCCC-3DD8-45D3-B454-5DB88D0B6783 0 ROWS 1 tempdb_Data_05 K:MSSQL$S1Data empdb empdb_Data_05.ndf 0 ONLINE 524288 1024000 65536 0 0 0 0 0 50000000020400001 NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL
2 11 06A09B39-09C7-41C6-B11A-BEC1785039B0 0 ROWS 1 tempdb_Data_06 K:MSSQL$S1Data empdb empdb_Data_06.ndf 0 ONLINE 524288 1024000 65536 0 0 0 0 0 50000000022700001 NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL
2 12 14F1019E-B3FE-4B7C-827B-880D3E0C430C 0 ROWS 1 tempdev2 I:MSSQLDATATEMP empdev2.ndf 0 ONLINE 256000 640000 65536 0 0 0 0 0 103000000037600078 NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL
2 13 743D3EC0-8011-4ECC-8CA6-256C65B37FC5 0 ROWS 1 tempdev3 I:MSSQLDATATEMP empdev3.ndf 0 ONLINE 256000 640000 65536 0 0 0 0 0 103000000043000001 NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL
2 14 968C0C01-95C9-4EA2-97B6-0DEFBBB8FEB5 0 ROWS 1 tempdev4 J:MSSQLDATATEMP empdev4.ndf 0 ONLINE 256000 640000 65536 0 0 0 0 0 108000000039000001 NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL
2 15 D4551A7F-3998-41E3-81B5-627168CBE77A 0 ROWS 1 tempdev5 J:MSSQLDATATEMP empdev5.ndf 0 ONLINE 256000 640000 65536 0 0 0 0 0 108000000041400001 NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL
2 16 701E5614-E61C-46CB-8D23-F8726B6B1F4F 0 ROWS 1 tempdev6 S:MSSQLDATATEMP empdev6.ndf 0 ONLINE 256000 640000 65536 0 0 0 0 0 114000000013600081 NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL
2 17 B4AB50DF-8004-4CBA-B97F-683BD8791FF4 0 ROWS 1 tempdev7 S:MSSQLDATATEMP empdev7.ndf 0 ONLINE 256000 640000 65536 0 0 0 0 0 114000000019200001 NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL
2 18 C0A50693-6311-4F33-B20B-6C030CC4F65F 0 ROWS 1 tempdev_14 S:MSSQLDATATEMP empdev_14.ndf 0 ONLINE 512000 1280000 128000 0 0 0 0 0 1349000000279900070 NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL
2 19 800B86A7-142F-46C9-984F-8673CEFE2DDD 0 ROWS 1 tempdev_01 S:MSSQLDATATEMP empdev_01.ndf 0 ONLINE 512000 -1 128000 0 0 0 0 0 1350000000553600232 NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL
2 20 4A008D1B-89E1-4F07-AB5C-CADBFB6ABE44 0 ROWS 1 tempdev01 F:MSSQLDATA empdb empdev01.ndf 0 ONLINE 524288 -1 128 0 0 0 0 0 78000000008100001 NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL
2 21 B6B63467-4179-47FD-A226-58639B92F402 0 ROWS 1 tempdev03 T:MSSQLDATATEMP empdev03.ndf 0 ONLINE 524288 -1 0 0 0 0 0 0 234000000253600164 NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL
2 22 2A075CBA-C600-47D9-B6B1-8231743EC5AF 0 ROWS 1 tempdev04 T:MSSQLDATATEMP empdev04.ndf 0 ONLINE 524288 -1 128 0 0 0 0 0 234000000262600001 NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL
2 23 EDAFB1FB-3B3D-4F7E-B335-8056F2FA9EAD 0 ROWS 1 tempdev1 F:MSSQL$S1DATA empdb empdev1.ndf 0 ONLINE 524288 -1 0 0 0 0 0 0 453000006813300252 NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL
2 27 1BFE36F9-B621-4B7C-99C8-0C262ADFFEF9 0 ROWS 1 tempdev02 F:MSSQLDATA empdb empdev02.ndf 0 ONLINE 524288 -1 0 0 0 0 0 0 430000003795400001 NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL
2 29 7C8D9FDF-E2D9-4274-BC3D-B2EC060C4B39 0 ROWS 1 tempdev_02 S:MSSQLLOGTEMPDB empdev_02.ndf 0 ONLINE 524288 -1 0 0 0 0 0 0 4030000039691100043 NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL
2 31 60973FF9-A35F-433C-9E58-D29F00E4D5A7 0 ROWS 1 tempdev_03 S:MSSQLLOGTEMPDB empdev_03.ndf 0 ONLINE 524288 -1 0 0 0 0 0 0 4033000048392400207 NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL
2 32 506E7E19-4D0A-47D8-A687-C9658500F367 0 ROWS 1 tempdev_04 S:MSSQLDATATEMP empdev_04.ndf 0 ONLINE 524288 -1 0 0 0 0 0 0 4033000048449300130 NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL
2 33 4DFD4A82-FD5C-4CEE-8939-C01C596B3714 0 ROWS 1 tempdev_05 S:MSSQLDATATEMP empdev_05.ndf 0 ONLINE 524288 -1 0 0 0 0 0 0 4033000048515100001 NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL
2 34 B6A34F7D-7FBD-460F-B6FF-ACED575FE2D4 0 ROWS 1 tempdev _06 S:MSSQLDATATEMP empdev _06.ndf 0 ONLINE 524288 -1 0 0 0 0 0 0 4033000048659000118 NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL
2 35 4B33C871-B52E-4514-8EBB-E856F151A96D 0 ROWS 1 tempdev_07 S:MSSQLDATATEMP empdev_07.ndf 0 ONLINE 524288 -1 0 0 0 0 0 0 4033000049131800087 NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL
2 36 40575107-6C34-408A-BF04-DDF7D71935BE 0 ROWS 1 tempdev_08 S:MSSQLDATATEMP empdev_08.ndf 0 ONLINE 524288 -1 0 0 0 0 0 0 4033000049137600001 NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL
2 37 C1DCC4CF-740A-4EB6-AF2E-7B105477251F 0 ROWS 1 tempdev_09 S:MSSQLDATATEMP empdev_09.ndf 0 ONLINE 524288 8192000 524288 0 0 0 0 0 4035000024325400202 NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL
(32 row(s) affected)
file_id file_guid type type_desc data_space_id name physical_name state state_desc size max_size growth is_media_read_only is_read_only is_sparse is_percent_growth is_name_reserved create_lsn drop_lsn read_only_lsn read_write_lsn differential_base_lsn differential_base_guid differential_base_time redo_start_lsn redo_start_fork_guid redo_target_lsn redo_target_fork_guid backup_lsn
----------- ------------------------------------ ---- ------------------------------------------------------------ ------------- -------------------------------------------------------------------------------------------------------------------------------- ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ----- ------------------------------------------------------------ ----------- ----------- ----------- ------------------ ------------ --------- ----------------- ---------------- --------------------------------------- --------------------------------------- --------------------------------------- --------------------------------------- --------------------------------------- ------------------------------------ ----------------------- --------------------------------------- ------------------------------------ --------------------------------------- ------------------------------------ ---------------------------------------
1 NULL 0 ROWS 1 tempdev E:MSSQL$S1MSSQL.2MSSQLDATA empdb.mdf 0 ONLINE 524288 1280000 128000 0 0 0 0 0 NULL NULL NULL NULL 95000000084000037 11815719-7EBA-453D-B93C-1CA083FADA76 2007-10-30 10:00:02.263 NULL NULL NULL NULL NULL
2 NULL 1 LOG 0 templog E:MSSQL$S1MSSQL.2MSSQLDATA emplog.ldf 0 ONLINE 128000 268435456 64000 0 0 0 0 0 NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL
(2 row(s) affected)
View 7 Replies
ADVERTISEMENT
Feb 4, 2008
I had a procedure that created a filegroup and file, did some other work and then (theoretically) renamed the filegroup and file. Well, the DB got locked up the last time I tried to run it and the rollback was only partly successful. I now have a file with one name in master_files and another name in database_files. Is there a spiffy way of fixing this? I'm thinking all I need to do is (re)rename the file.
[Edit]
Some more examinaion shows that this discrepancy only appears on the mirrored server, not the principal.
View 3 Replies
View Related
Nov 12, 2007
Hi All,
I am truncating Transactionlog€¦ following the steps mentioned in MSKB.. what is the problem?
Use master
Go
Backup log xxx with truncate_only
go
Dbcc shrinkfile(xxx_Log)
Go
Msg 8985, Level 16, State 1, Line 1
Could not locate file 'xxx_Log' for database 'master' in sys.database_files.
The file either does not exist, or was dropped.
Please help me.
Thanks in advance
View 31 Replies
View Related
Jan 29, 2008
I have a install of SQL 2005 that is about 5 months old. The tempdb.mdf and master.mdf files have ballooned to a huge size almost filling my hard drive. How do I reset them to a reasonable size?
View 14 Replies
View Related
Nov 3, 2003
When SQLserver2K was installed it placed master, model, msdb, tempdb data files in the installation location (i.e. C:Program Files....). This puts pressure on the C: drive, which also holds the page/swapfile. I want to move at least the tempdb location to the new 'Default data directory' and log directory we set after installation (i.e. E:MSSQLData).
How do I get tempdb to relocate to E: given that it gets recreated each time SQLserver starts?
TIA,
Al
View 1 Replies
View Related
Sep 30, 2014
Is it possible to encrypt Master database and tempdb? On executing below query result is showing temdb is encrypted.
SELECT db_name(database_id), encryption_state, percent_complete, key_algorithm, key_length
FROM sys.dm_database_encryption_keys
View 1 Replies
View Related
Jul 20, 2005
I am interested to hear if people think it would be a good idea to movethe Master & TempDB to a different HD.Here is my DB Server's set up:1. Processor: (1) AMD XP 28002. 1st HD (IDE 0) is the system & boot drive3. (3) SCSI HD make up a hardware RAID level 0 (striped withoutparity)solution - these striped drives are just for my working DBs4. (1) SCSI HD that's not doing anything.I want to put the Master & TempDB on the SCSI HD that's not doinganything. Would that be the best place for it for maximum performance orshould I put in the striped array. I am leaning more towards putting onthe SCSI HD that's not doing anything. What do you all think?Ed*** Sent via Developersdex http://www.developersdex.com ***Don't just participate in USENET...get rewarded for it!
View 1 Replies
View Related
May 7, 2008
hi folks
I€™ve had a SQL 2000 server restored (using Veritas 9.1) to another server also running SQL 2000. I now have a collation problem.
When trying to add users to a database, I receive a "Error 446: Cannot resolve collation conflict for equal to operation" message.
I noted that tempdb and user tables collation don€™t match - so I was going to change the user tables. However, the master collation doesnt match tempdb, model or msdb.
Server is set to SQL_Latin1_General_CP1_CI_AS
Master - SQL_Latin1_General_CP1_CI_AS
tempdb, model, msdb - Latin1_General_CI_AS
I was under the impression that when tempdb was recreated it inherited the master database's collation? This doesn€™t seem to be happening.
any ideas?
much appreciated
N.
View 7 Replies
View Related
Jul 20, 2006
Hi Guys,
Since,You cannot mirror the master, msdb, tempdb, or model databases.What will happen if i create new login,change existing security profile and new jobs,change of existing job on princicpal db. how these will be mirrored to other server and in case failover, how it will treat.
Thaks
View 1 Replies
View Related
Jul 14, 2004
Has anyone seen the SQL Server error:
"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
View 11 Replies
View Related
Jun 19, 2007
I got a File with sales orders and their details.
Step 1. First I am filtering the Sales Order information and inserting it in my Sales Orders table.
Step 2.Then I am filtering the details from the sales Order and inserting them in the respective table.
My Problem is that the Sales Order File does not contain the Sales Order key (ID), this is generated by the SQL Server. How can get it in order to use it in the second step? I need it because it is a foreign key in the details table.
Any Idea?
View 4 Replies
View Related
Mar 25, 2008
Hello,
I'm new to SQL and need help with a query. Not sure if this is the right place.
I have 2 tables, one MASTER and one DETAIL.
The MASTER table has a masterID, name and the DETAIL table has a detailID, masterID, and value columns.
I want to return a populated MASTER table with entries based on the DETAIL.value.
SELECT MASTER.*
FROM MASTER
WHERE DETAIL.value > 3
This is a simplified version of my problem. I can't figure out how to set the relationship between MASTER.masterID and DETAIL.masterID. If I do an INNER JOIN, the number of results are based on the number of DETAIL entries. I only want one entry per MASTER entry.
Hope this makes sense.
How can I do this?
GrkEngineer
View 9 Replies
View Related
Nov 2, 2015
We already integrated different client data to MDS with MS Excel plugin, now we want to push back updated or new added record to source database. is it possible do using MDS? Do we have any background sync process to which automatically sync data to and from subscriber and MDS?
View 4 Replies
View Related
Jun 1, 2005
TempDB is one of the databases equipped with MSSQL Server by default.What is the purpose of it?Why do we use this temp database?
View 4 Replies
View Related
Apr 9, 2002
Hi,
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?
It's extremely urgent.
Thanks
View 1 Replies
View Related
Apr 16, 2001
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
View 1 Replies
View Related
Jun 27, 2001
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?
Thanks
Lena
View 4 Replies
View Related
May 10, 2000
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
View 2 Replies
View Related
Feb 2, 2001
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?
Thanks. JT
View 2 Replies
View Related
Jan 17, 2001
We want to shrink the size of our TEMPDB , Does anyone know how to without blowing it away and re creating it.
Also, our tempdb expands to occupy all available space on the server, the space is not released until the server is restarted.
Any ideas would be appreciated.
View 3 Replies
View Related
Oct 18, 2000
Hi why I cannot backup the tempdb. Is this a normal or there is something that I am doing wrong.
Ali
View 1 Replies
View Related
Apr 6, 2000
Hi
I get a message:
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 just want my SQL Server back. Please help!
Thanks
Betty Lee
View 4 Replies
View Related
Apr 20, 2000
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!!
View 2 Replies
View Related
Aug 29, 2000
The tempdb has grown to over 2 GB on our DB. There are no user tables or SP in it. Is there a way to clean up and are there any consequences to this?
View 4 Replies
View Related
Jan 13, 2000
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.
Any ideas???
View 1 Replies
View Related
Jan 22, 2003
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.
Thanks for the help
View 2 Replies
View Related
Nov 3, 1999
Hi,
I moved my tempdb (2MB) to RAM and restarted the server. It did restart but when I tried to connect thru ISQL, it gave me an ERROR -
A connection could not be established to <server-name> - DB-Library. Unable to Connect.
What could be the possible reason.
To move tempdb to RAM, do i just have to change the values of the MEMORY and TEMPDB IN RAM parms and restart or do I have to do anything else.
Please help.
Thanks
Nishant
View 2 Replies
View Related
Mar 18, 1999
Hi
When should I put tempdb in RAM and when should I not ?
Thanks in advance
Vijay
View 3 Replies
View Related
Feb 24, 1999
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?
View 2 Replies
View Related
Dec 4, 2006
Hi All,
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.
Thanks
View 2 Replies
View Related
Aug 19, 1998
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.
View 1 Replies
View Related
Nov 1, 1998
System: Pentium II - Dual 450. 13 GIG RAID. 128 MB RAM.
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.
View 2 Replies
View Related
