SQL Server 2000 - TempDB Problem?

Jan 29, 2006

Hi,

I believe our problem is related to tempDB on the specific server but I would like to know if anybody has come across a similar issue.

We have an SQL statement similar to the following.

BEGIN TRANSACTION

CREATE #tableA

INSERT #tableA SELECT (expression A)

INSERT #tableA SELECT (expression B) WHERE (condition)

COMMIT TRANSACTION

First, let me briefly expand on the second INSERT as this may help when reading the points below.

INSERT #tableA
SELECT ...
FROM ... INNER JOIN tableB
WHERE NOT EXISTS (SELECT ... FROM #tableA WHERE #tableA.columnA = tableB.columnB)

This script works fine on all of our servers except one, which is why I believe tempDB may be involved. After an analysis of the problem, we have the following results,

- If we remove the Transaction, the script succeeds.
- If we leave the Transaction and remove either the first or second INSERT, the script succeeds.

- If we leave the Transaction and both Insert statements and remove the WHERE (condition) from the second Insert, the script succeeds.

- If we reduce the row counts from all source tables concerned by 90%, the script still does not succeed.
- The script had succeeded the week before on the server in question.

Finally, if we replace #tableA with tableA, the script succeeds.

Any help on this would be greatly appreciated.

Thanks.

View 3 Replies


ADVERTISEMENT

Tempdb Maintenance - SQL Server 2000

Dec 14, 2006

Can someone please explain what the tempdb database is responsible for in SQL Server 2000. The database and log file has grown extremely large and I cannot backup this table for some reason. Thanks.

View 1 Replies View Related

Sql Server 2000 - Spid Using Most Tempdb Space ?

Jan 29, 2008

Hi There

In Sql Server 2005 sys.dm_db_task_space_usage is great for tracking down temdb space issues.

I have tempdb space issues in sql server 2000, how do i get the same info in SS2000 ? IE: What spid is using the most tempdb space ?

Thanx

View 1 Replies View Related

SQL Server 2000 TempDB User Losing Permissions

Jan 15, 2007

I am supporting a system that needs to allow users to have access to TempDB.

I set these users up using the GUI, but whenever the server is restarted, these users permissions are wiped out and the db_owner permission is lost and I have to manually go in and apply the permissons for the database to work again, it happens on most reboots but not all.

Is there anyway to keep these users permissions when the server is rebooted?

Your help is most appriciated.

P.S Could I create a stored procedure that when ever the Server is rebooted the procedure would recreate these permissions?

If I need to do this how would I go about doing this?

View 8 Replies View Related

MSSQL 2000, ODBC, Tempdb, Locking

May 14, 2002

Hi, I wonder if anyone can shed some light on this issue -

We have a client/server program for MSSQL7 and MSSQL2000.
When running client/server - we have our own broker, etc,
the program ends up locking itself on SQL2000; this is not a problem on SQL7.
We noticed that it seems some system tables locking tempdb.
We also noticed that SQL2000 no longer has insert-into/bulkcopy option like SQL7. But, here we don't have temporary tables at all. It's just that we found the lockings in tempdb caused by some sysobjects, sysindexes, etc.
Also, this does not happen to all the systems we have. Some of our machine does not have the self-lock problem; most of the machines have this problem though.

If we don't run client/server, then the program works fine.
Same logic, the difference is when we use client/server logic to connect to the remote SQLServer 2000 server, the locking might happen on most of our machines.

Anyone is familiar with tempdb and locking issues, please provide some hint??? Is it possible that there are some system parameters that we can set to avoid the problem. We haven't found out why some of our machines do not have problem, while most of the machines have this locking problem.

The program is written using ODBC.

Many thanks in advance.

HIBA International

View 2 Replies View Related

Tempdb Is Skipped. You Cannot Run A Query That Requires Tempdb

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

Urgent, How Much The TempDB Should Be In The New Server

Dec 28, 1999

Hi,
I am installing the SQL server 7.0 as a production server and as soon as the server is started, within a week the date will be around 10 millinon rows, so I want to know how much space should I allocate for the tempdb and what should be the incremental percentage of the the tempdb(New server).

thanks in advance,
Madhu.

View 2 Replies View Related

DB Engine :: Server TempDB Set On RAM Disk

Oct 23, 2015

I try to find some feedback regarding setting the TempDB files on a RAM disk.Specifically I am looking for "production results" that could show the difference/benefit of such an usage.The tests on physical server and VM I already made have shown a boost in overall SQL Server 2012 performance on SQL Server instances housing data for SharePoint 2013 and Dynamics AX 2012 R2.Graphic below show differences between 5 different configuration on the same physical server:

- Physical HD:  Server with local HD
- Physical SANEX1PRD: Server with TempDB files stored on a low-end SAN
- Physical SAN1: Server with TempDB files stored on a high-end SAN (around 100000 IOps)
- Physical SAN1 Jumbo: same setup with Jumbo Frame activated on NIC and DB engine
- Physical RAMdrive: with TempDB files stored on a 16 GB soft RAM drive within OS memory

Results were really impressive for the DB engine housing Dynamics AX data. My colleagues from the SharePoint team told me it also boosted a bit overall SharePoint performances but they did not have any baseline comparison to show.If you have some feedback, results, links, whatever I am interested.Indeed before setting this to all our SQL Server 2012 instances I y rather collect some *real world* feedback.

View 2 Replies View Related

How To Start SQL Server When Tempdb Files Were Relocated?

Nov 22, 2005

It's been a long time since I've tried this, but I have a SQL Server that needs to be restored (including master) to a server whose drives and corresponding folders match the source server, with the exception of tempdb. When SQL Server initially starts I believe it will fail since it cannot find tempdb. I just don't recall if it fails to startup or if it starts up reporting errors and recreates tempdb in the same location as master. Does anyone recall the steps needed to point SQL Server to the new location of tempdb?

Dave

View 1 Replies View Related

SQL Server 2008 :: TempDB Size Hovering Around 45%

Jul 27, 2015

My prod server (only default instance) is configured TempDB 1024 MB data and Log 200MB. when I run 'sqlperf logspace' it shows most of time around 45% 'log space used'. There nothing going on the instance when I ran 'whoisactive' and select * from sys.sysprocesses where dbid = 2!!!

So my questions are is this normal to see log space around 45%, how to find what what CAUSED the tempdb log space to grow 45%? Is there something to do about it?

View 6 Replies View Related

SQL Server 2014 :: How To Overcome From The Tempdb Spill Over

Aug 19, 2015

Here with i have attached the execution plan, In that i have tempdb spill over in sort operation. i have created index to sort even it needs temp db spill over. [URL]....

View 9 Replies View Related

SQL 2012 :: Blocking In Report Server TempDB

Sep 21, 2015

I am seeing select * from sys.sysprocess where blocked<>0 is always the report server

ReportServer.dbo.GetSessionData;1 is blocked by ReportServer.dbo.WriteLockSession;1

We have different reporting servers using same database.

Is it require to rebuild indexes on report server and report server temp db on daily basis?

View 0 Replies View Related

SQL Server Admin 2014 :: TempDB Is Full

Oct 6, 2015

Message: Executed as user: NT AUTHORITYSYSTEM. The transaction log for database 'tempdb' is full. To find out why space in the log cannot be reused, see the log_reuse_wait_desc column in sys.databases [SQLSTATE 42000] (Error 9002). The step failed in my sql server agent job i have the above error, this type of errors i got some of multiple jobs.

View 9 Replies View Related

SQL Server 2005 SP2 -- Error With ASP.Net Session State Using TempDB

Mar 14, 2007

I have an ASP.Net (C# 2.0) application that has been using SQL Server 2005 Standard Edition with Service Pack 1 to hold the session state in a testing environment.  Currently, the session state is being stored in TempDB, rather than the ASPState database.  This has worked very well for us until yesterday.  We installed SQL Server 2005 Service Pack 2, as well as the Critical Update for Service Pack 2 (KB933508).  Once the SQL server was rebooted, I got the following error message when I tried to access the web application.
The SELECT permission was denied on the object 'ASPStateTempApplications', database 'tempdb', schema 'dbo'.The SELECT permission was denied on the object 'ASPStateTempApplications', database 'tempdb', schema 'dbo'.The INSERT permission was denied on the object 'ASPStateTempApplications', database 'tempdb', schema 'dbo'. 
In the web.config file for the application, I have a SQL username and password defined that can access the ASPState database.  To correct this issue, I had to give this user db_datareader and db_datawriter access to tempDB.
 
Has anyone else run across this problem, and is it related to SQL Server 2005 Service Pack 2?

View 1 Replies View Related

SQL Server Admin 2014 :: Transaction Log And TempDB Calculations

Mar 13, 2014

Is there a formula for calculating how expensive a transaction will be in terms of disk space used before its run. I dont want it accurate to the MB, but rough enough so I can determine how much additional space to assign to a transaction log or SAN volume.

Currently we're reindexing ~25billion rows, nothing too wide, say 12 columns consisting of 1 varchar(50) and the rest ints, bits and money.Roughly speaking if I reindex the clustered index on an int indetity, (with sort_in_tempdb) how would I calculate the the disk space used?

View 2 Replies View Related

SQL 2012 :: Where Does Server Store Information About TempDB Configurations

May 29, 2015

Whenever SQL Server get restarted, tempdb gets recreated with its last configuration.

let me know where SQL Server store tempdb configurations? How does it know how many Tempdb files it needs to create on restart?

View 2 Replies View Related

SQL Server 2014 :: Eliminate Used TempDB To Spill Data

Jul 10, 2015

I've been trying to make the following query more performant by breaking it up into smaller pieces.

SELECT MT.A3+MT.A4 AS A34,MT.A3
-- ,M.*
FROM
Master_TAB M
JOIN (SELECT M.A1,t3.A3,t3.A4,M.A6,M.A2,ROW_NUMBER() OVER (PARTITION BY A1,A6,A3,A4 ORDER BY A5 DESC) AS rownum

[Code] ....

I know that the Spill is caused by the Sort but I can't remove the sort (sort can't be done in front end). My master table had 1.7 million rows and almost 200 columns (bad design? I know but can't be changed as there's too much that would be affected) every row is little over 1KB

Here's my attempt...

-- MASTER_TAB has 1.7 million rows and 50 columns
CREATE TABLE [dbo].[tmp_ABC](
[A1] [varchar](13) NOT NULL,
[A2] [varchar](5) NOT NULL,
[A3] [varchar](4) NOT NULL,
[A4] [varchar](4) NOT NULL,
[A5] [int] NULL
) ON [PRIMARY]

[Code] ...

This is the Query that is causing the Spill (in reality I'm supposed to bring back all 200 columns fro the master table but for debug purposes I limited the columns)

Select c.A3+c.A4 as A34, c.A3, c.A1
-- M.*
from tmp_DEF c
join MASTER_TAB M on M.A1 = c.A1 and M.A2 = c.A2
order by c.A3, C.A4

if I just run the following I get no spill:

Select c.A3+c.A4 as A34, c.A3, c.A1
from tmp_DEF c
order by c.A3, C.A4

as soon as I add the Master table as a Join I get the Spill...

I read many articles, tried many suggested things (creating indexes... clustered, non-clustered) without success. Maybe I'm totally in Left Field and should enhance the performance going another route?

View 9 Replies View Related

SQL 2012 :: Blocking Frequently Report Server TempDB

Aug 27, 2015

How to solve the below blocking issue

One of our production server is configured with Always on with sql cluster.

We are getting frequently blocking

Waiting Resource Key Type: Key
WaitType: LCK_M_S
WaitResourceDatabaseName : ReportServerTempDB
WaitingSessionProgramName: Report Server
BlockingSessionProgramName: Report Server
WaitCommandType: Select
WaitingCommandText: Create Procedure dbo.checksessionLoak @sessionID...

View 1 Replies View Related

TempDb Drops User Account On Server Restart?

Sep 11, 2007

Please forgive my ignorance, I am by no means a SQL Expert, but have encountered a strange issue.

I have 6 SQL Servers, Primarily SQL 2005 (one older SQL 2000) all loaded on Windows Server 2003 SP1.

We use the servers for a proprietary database that we created which is the backend to a software package we sell.

The issue I have is: We have added a Security account to the servers, and in one case we have granted rights for this account to the TempDB system database. However, whenever we restart this server SQL drops this user account, thus severing connectivity for the app that is relying on that account.

I have set the account as DB-Owner etc, but nothing sems to keep it on re-start.

Any input would be greatly appreciated.

View 4 Replies View Related

VSNL: Problem With The Creation A Tempdb On SQL Server 2005

Mar 30, 2007

We have a problem with the creation a tempdb on SQL Server 2005 startup



We lost the Q: drive in which tempdb had a datafile

We can't attach a Q: drive because we are using a cluster



When we boot up, here what we get:



tempdb..sysfiles:

fid filename

1 I:Microsoft SQL ServerMSSQL.3MSSQLDATA empdb.mdf
2 I:Microsoft SQL ServerMSSQL.3MSSQLDATA emplog.ldf



master..sysaltfiles

fid filename

1 I:Microsoft SQL ServerMSSQL.3MSSQLDATA empdb.mdf
2 I:Microsoft SQL ServerMSSQL.3MSSQLDATA emplog.ldf
3 P:MSSQLData empdb1.ndf
4 R:MSSQLData empdb2.ndf
5 S:MSSQLData empdb3.ndf
6 T:MSSQLData empdb4.ndf
7 P:MSSQLData empdb_1.ndf
8 P:MSSQLData empdb_11.ndf
9 P:MSSQLData mp1.ndf
10 R:MSSQLData mp2.ndf
11 P:MSSQLData emp1.ndf
12 Q:MSSQLData emp2.ndf
13 R:MSSQLData emp3.ndf
14 S:MSSQLData emp4.ndf
15 S:MSSQLData mp3.ndf
16 T:MSSQLData mp4.ndf
17 P:MSSQLData mp_1.ndf
18 R:MSSQLData mp_2.ndf
19 S:MSSQLData mp_3.ndf
20 T:MSSQLData mp_4.ndf



We've tried to use the 'allow update' option but it won't work. I can't remove lines from sysaltfiles.



We need tempdb to use 4 other devices for performance but it won't remember it's definition because it failed each time. Here the log entry:

CREATE FILE encountered operating system error 21(The device is not ready.) while attempting to open or create the physical file 'Q:MSSQLData emp2.ndf'.



Now I found information on how to resolve this on 2000:

http://groups.google.ca/group/microsoft.public.sqlserver.server/browse_thread/thread/eb365f82650d78d1/7c30472ae0a11448%237c30472ae0a11448



By using a trace flag but I don't see how I can use this on 2005 ! Can someone help me ?





View 3 Replies View Related

SQL Server 2008 :: Initial Size For TempDB Data And Log File?

Sep 12, 2011

We have installed SQL Server 2008 R2 SP1 instance and it's having Share Point 2010 databases.

We have 2 dedicated drives for Tempdb on SAN with 50 GB space. Both tempdb data & log files are created with default size. I would like to presize them.

What are the best values to start with?

U ->Tempdbdata having tempdb.mdf file
V->Tempdblog having templog.ldf file

View 9 Replies View Related

SQL Server 2012 :: Limitation Of Number Of Objects In TempDB Database?

Dec 9, 2014

how to know the limitation of number of objects(Maximum no.of objects allow tempdb database) in a tempdb database?

View 2 Replies View Related

SQL Server Admin 2014 :: TempDB Configuration For 24 Logical Cores

Aug 27, 2015

I have a server with 2 processors, 12 cores each making it a 24 core logical system.

My tempdb has 4 datafiles and 4 logfiles created.

I know creating more number of log files in tempdb may not make sense, but how many tempdb data files are supposed to be created?

Should it be 24?

View 4 Replies View Related

SQL Server 2008 :: TempDB Datafile Initial File Sizes Changed?

Mar 18, 2015

I have an instance with 4 datafiles for tempdb each set at initial size of 4G and growth rate of 100MB. After some time the initial file sizes seem to have changed automatically. They now read 3962,100,3688 and 2847 respectively. Is this something done by SQL Server itself? I cannot imagine that it was done manually.

I don't think there was a restart after the initial sizes of 4G were set, could this be related to the problem?

View 1 Replies View Related

Reporting Services :: SSRS Report Server TempDB Growing Unusually?

Aug 25, 2015

SQL Server 2008, Tempdb ("ReportServerTempDB")of the report server is growing enormously over 20gb in 3 days. All our reports drive from  stored procedures which is a different server from Reportservertempdb. We have no report subscriptions.

We store no snapshots. All we do is run the reports.

I don't know where I start to investigate the issue.

View 2 Replies View Related

Can I Keep Sql Server 2000 If Upgrade Win 2000 To Win 2003 (was Sql Server 2000)

Feb 24, 2005

Hello, i have a question that the sql server 2000 is install in window 2000 server. If i want to update to window 2003. Is that any problem in sql server 2000. I am worry about whether we will have problem after update. What i need to do? Many thanks.

View 5 Replies View Related

SQL Server Admin 2014 :: How To Allocate The File Sizes (Disk Partition Alignment For Tempdb)

Jun 29, 2015

I have question about tempdb needs to be configured 100GB 64kb block size.its fresh installation.

how to allocate the file sizes.still im not sure how many log files needs to be created with 100GB equals to 64KB block size.

what is 64KB block size and how to divide the logfiles 64KB into the 100gb or 50GB?

what is 64 KB cluster has 128 sectors?

tempdb drives should be formatted with a 64K allocation? how many files needs to created for good performance with 50GB or 100GB? ot 1TB

View 3 Replies View Related

SQL SERVER 2000: In Which Format The Datetime Will Be Stored In Sql Server 2000?

Feb 28, 2008

Hi All,
I would like to know, how the datetime will be stored in the sqlserver datetime column.
Because some time i am giving the date in dd/mm/yyyy and sometime mm/dd/yyyy.
while give the date in mm/dd/yyyy works fine but not in the another case. and also while i execute a query on query analyser it shows the datetime in
yyyy/mm/dd format.
So anyone can please tell me how the dates will be stored in the datetime column of sqlserver database?
Thanks in Advance.
Regards,
Dhanasekaran. G

View 2 Replies View Related

Upgrading SQL Server 2000 Standard To SQL Server 2000 Enterprise

Sep 14, 2004

I am currently running SQL Server 2000 Standard on my production system, and I am looking to upgrade the system to Windows 2000 Adv. Server. I would also like to upgrade SQL Server 2000 Standard to SQL Server 2000 Enterprise to utilize more than 2GB of memory. Can anyone tell me what is the best way to upgrade the system, and please provide some feedback on your experiences with the upgrade. Thanks in advance.

View 2 Replies View Related

Sql Server 2000 Linked Server To Access 97/2000 Incompatability

Jul 20, 2005

We are experiencing a problem with Sql Server 2000 linking to anAccess 97 file. We have two machines that link to this .mdb file, andwe recently upgraded one to newer hardware, SP3a, MDAC 2.8, etc. Thelink on this upgraded machine no longer works, giving this message:Server: Msg 7399, Level 16, State 1, Line 1OLE DB provider 'Microsoft.Jet.OLEDB.4.0' reported an error.[OLE/DB provider returned message: Cannot open a database created witha previous version of your application.]OLE DB error trace [OLE/DB Provider 'Microsoft.Jet.OLEDB.4.0'IDBInitialize::Initialize returned 0x80004005: ].The link on the older machine still works. We decided to tryconverting a copy of the file to Access 2000 to see if the newerpatches/drivers/whatever no longer supported 97. We set up a link onboth machines to this file, and they both work. However, on theupgraded machine, the following error is receievedServer: Msg 7399, Level 16, State 1, Line 1OLE DB provider 'Microsoft.Jet.OLEDB.4.0' reported an error.[OLE/DB provider returned message: System resource exceeded.]OLE DB error trace [OLE/DB Provider 'Microsoft.Jet.OLEDB.4.0'ICommandText::Execute returned 0x80004005: ].when making 1-3 connections to the the linked server, while the oldermachine supports at least 7 simultaneous queries connecting to thelinked server and still hasn't produced that error.Does anyone have any idea if there is a known issue with linking toAccess 97/2000 files under MDAC 2.8, Jet 4.0, etc? Any light anyonecan shine on this subject would be greatly appreciated.

View 1 Replies View Related

SQL Server 2000 Standard On Windows 2000 Advanced Server

Jul 20, 2005

What is the limitation of memory that SQL Server 2000 Standard can usewhen running on a Windows 2000 Advanced Server platform?

View 1 Replies View Related

Upgrate Sql Server 2000 Standard To Sql Server 2000 Enterprise

Sep 15, 2006

Hi, I need to know if somebody knows like making the update of Standard SQL 2000 to Enterprise. Greetings.

View 3 Replies View Related

DB File Size Limit With SQLServer 2000 In Small Business Server 2000

Mar 15, 2006

Thanks in advance. What is maximum SQL Server database (*.mdf) file size with SQL Server 2000 as part of Microsoft Small Business Server 2000? (Database files were limited to 10 GB in SBS 4.5 with SQLServer 7.0... has this changed?).

View 1 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved