Temp DB Ocupied

Jul 22, 1998

The tempdb in a lot of OLTP MS SQL 6.5 servers of our customer site are experiencing continuous descrease in the available space. The more transactions the server has done, then more space is ocupied.

We have to restart SQL server service once a week. This has caused some inconvenience.

By the way, we are not tunning DBCC stuff to maintain the database regularly.

Any idea to avoid or decelerate the problem?

View 1 Replies


ADVERTISEMENT

A Curious Error Message, Local Temp Vs. Global Temp Tables?!?!?

Nov 17, 2004

Hi all,

Looking at BOL for temp tables help, I discover that a local temp table (I want to only have life within my stored proc) SHOULD be visible to all (child) stored procs called by the papa stored proc.

However, the following code works just peachy when I use a GLOBAL temp table (i.e., ##MyTempTbl) but fails when I use a local temp table (i.e., #MyTempTable). Through trial and error, and careful weeding efforts, I know that the error I get on the local version is coming from the xp_sendmail call. The error I get is: ODBC error 208 (42S02) Invalid object name '#MyTempTbl'.

Here is the code that works:SET NOCOUNT ON

CREATE TABLE ##MyTempTbl (SeqNo int identity, MyWords varchar(1000))
INSERT ##MyTempTbl values ('Put your long message here.')
INSERT ##MyTempTbl values ('Put your second long message here.')
INSERT ##MyTempTbl values ('put your really, really LONG message (yeah, every guy says his message is the longest...whatever!')
DECLARE @cmd varchar(256)
DECLARE @LargestEventSize int
DECLARE @Width int, @Msg varchar(128)
SELECT @LargestEventSize = Max(Len(MyWords))
FROM ##MyTempTbl

SET @cmd = 'SELECT Cast(MyWords AS varchar(' +
CONVERT(varchar(5), @LargestEventSize) +
')) FROM ##MyTempTbl order by SeqNo'
SET @Width = @LargestEventSize + 1
SET @Msg = 'Here is the junk you asked about' + CHAR(13) + '----------------------------'
EXECUTE Master.dbo.xp_sendmail
'YoMama@WhoKnows.com',
@query = @cmd,
@no_header= 'TRUE',
@width = @Width,
@dbuse = 'MyDB',
@subject='none of your darn business',
@message= @Msg
DROP TABLE ##MyTempTbl

The only thing I change to make it fail is the table name, change it from ##MyTempTbl to #MyTempTbl, and it dashes the email hopes of the stored procedure upon the jagged rocks of electronic despair.

Any insight anyone? Or is BOL just full of...well..."stuff"?

View 2 Replies View Related

Unable To Extend Temp Segment By 64 In Tablespace TEMP (SSIS Error While Copying Data From Oracle)

Oct 22, 2007

I am transferring data from oracle and getting below error message.

I using 4 data flow tasks with in a single control flow and all the 4 tasks quueries same table but populates data in to different sql tables based on the where contidion

[OLE DB Source 1 [853]] Error: An OLE DB error has occurred. Error code: 0x80004005. An OLE DB record is available. Source: "Microsoft OLE DB Provider for Oracle" Hresult: 0x80004005 Description: "ORA-01652: unable to extend temp segment by 64 in tablespace TEMP ".

View 4 Replies View Related

Temp Tables Vs Temp Variables

Jul 20, 2005

I have an application that I am working on that uses some small temptables. I am considering moving them to Table Variables - Would thisbe a performance enhancement?Some background information: The system I am working on has numeroustables but for this exercise there are only three that really matter.Claim, Transaction and Parties.A Claim can have 0 or more transactions.A Claim can have 1 or more parties.A Transaction can have 1 or more parties.A party can have 1 or more claim.A party can have 1 or more transactions. Parties are really many tomany back to Claim and transaction tables.I have three stored procsinsertClaiminsertTransactioninsertPartiesFrom an xml point of view the data looks like this<claim><parties><info />insertClaim takes 3 sets of paramters - All the claim levelinformation (as individual parameters), All the parties on a claim (asone xml parameter), All the transactions on a claim(As one xmlparameter with Parties as part of the xml)insertClaim calls insertParties and passes in the parties xml -insertParties returns a recordset of the newly inserted records.insertClaim then uses that table to join the claim to the parties. Itthen calls insertTransaction and passes the transaction xml into thatsproc.insertTransaciton then inserts the transactions in the xml, and alsocalls insertParties, passing in the XML snippet

View 2 Replies View Related

T-SQL (SS2K8) :: Moving Values From Temp Table To Another Temp Table?

Apr 9, 2014

Below are my temp tables

--DROP TABLE #Base_Resource, #Resource, #Resource_Trans;
SELECT data.*
INTO #Base_Resource
FROM (
SELECT '11A','Samsung' UNION ALL

[Code] ....

I want to loop through the data from #Base_Resource and do the follwing logic.

1. get the Resourcekey from #Base_Resource and insert into #Resource table

2. Get the SCOPE_IDENTITY(),value and insert into to

#Resource_Trans table's column(StringId,value)

I am able to do this using while loop. Is there any way to avoid the while loop to make this work?

View 2 Replies View Related

Temp Table Vs Global Temp Table

Jun 24, 1999

I think this is a very simple question, however, I don't know the
answer. What is the difference between a regular Temp table
and a Global Temp table? I need to create a temp table within
an sp that all users will use. I want the table recreated each
time someone accesses the sp, though, because some of the
same info may need to be inserted and I don't want any PK errors.

thanks!!
Toni Eibner

View 2 Replies View Related

Temp DB

Jan 26, 2001

What conuter can you check on NT to see what TEMP DB is doing.
Also, How can you check how hard Temp DB is working.

Thank you,
John

View 1 Replies View Related

Temp Db

Apr 2, 2008

Hi all ,
this is chetan .SQl server DBa.
i want to know that what should be done when tempdb is full.

Thanks in advance...

Thanks in advance.
-- Chetan

View 4 Replies View Related

Temp Db

Jul 20, 2005

what is the best practice for sizing the temp db in sql 2000?what the best way to free up temp db space without shutdown the sql?how to monitor temp db parameters?

View 1 Replies View Related

TEMP TABLE

May 31, 2008

HI ALL,iam creating a temporary table using following Stored procedure but when i complile the Stored procedure iam getting the following errorServer: Msg 2714, Level 16, State 1, Procedure SP!, Line 15There is already an object named '#TEMP2' in the database.the stored procedure is  ALTER PROC SP1@SELECT VARCHAR(15)=NULL  AS  BEGIN  IF @SELECT ='FOLDER'BEGIN     SELECT            DISTINCT(HIERARCHY_ID),HIERARCHY_NAME,HIERARCHY_DESCRIPTION,HIERARCHY_PARENT_ID                      INTO #TEMP2      FROM                 BM_HIERARCHY_MASTER        ENDELSE IF @SELECT='PAGE' BEGIN        SELECT            DISTINCT(HIERARCHY_ID),HIERARCHY_NAME,HIERARCHY_DESCRIPTION,HIERARCHY_PARENT_ID                            INTO #TEMP2      FROM                 BM_HIERARCHY_MASTEREND  

View 5 Replies View Related

UDF Vs Temp Table

Apr 4, 2006

Which one is better to use User Defined function or Temp Table?

I am working on SQL Server 2005, and I have a user defined function that returns a table. I need to run queries on this table.

I am not sure if I should run the function once and store the results in a temp table and run queries on the temp table or call the UDF multiple times. I am concerned about performance.

Thanks

View 3 Replies View Related

DTS - SP And Temp Tables

Nov 3, 2000

I am attempting to execute a stored procedure as the sql query for a data transformation from sql into an excel file. The stored procedure I am calling uses temp tables (#tempT1, #tempT2, etc.) to gather results from various calculations. When I try to execute this sp, I get
'Error Source: Microsoft OLE DB Provider for SQL Server
Error Description: Invalid Object name "#tempT1"'

Is there a way to make a DTS package call a stored procedure that uses temp tables?

Thanks.

View 2 Replies View Related

Temp Table

Dec 12, 2000

When do you use temp table?.

View 2 Replies View Related

Temp Database

Jan 24, 2001

I'm running SQL Server 7.0 and in the morning received the following message
and immidiately after the server went for a reboot.

ERROR :
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..

However,I fail to understand why I got the above error even though the temp database is configured to grow automatically by 10% and also why did the SQL
Server went for a reboot? Can anyone help me on this
Thanks!

View 1 Replies View Related

Temp DB Files

Jan 2, 2000

Using SQL Server 7 I have the following files hanging out and I'm not sure what they are for or if I can delete them.

My data and logs are on the same RAID 5 drives. They are located in the E:DATA and E:LOGS directories.
I have the following files on the root of my E: drive

tempdbData.ndf
tempdbLog.ndf

tempdbData1.ndf
tempdbLog1.ndf

this goes on up to tempdbData4.ndf. The data files are large up to 2 GBytes. The dates are not current except for the last 2 sequential files.

Anybody know what they are there for and my I delete any of these files?

Thanks and Thanks again......

View 1 Replies View Related

Temp Table

Sep 29, 2000

Can I use identity in the temp table? e.g.

CREATE TABLE #order
(orid int identity(1,1),
userid varchar(12), firstname varchar(20),
middleinit varchar(10), lastname varchar(20), emailaddr varchar(254))

INSERT INTO ...
SELECT ...
FROM ...
WHERE ...
.
.
.

When I create procedure as above, I got error message which I list below:
++++++++++++++++++++++++++++++++++++++++++++++++++ +++++++++++++
Server: Msg 207, Level 16, State 3, Procedure pro_order, Line 40
Invalid column name 'orid'.
++++++++++++++++++++++++++++++++++++++++++++++++++ +++++++++++++

Is this mean that I can not use identity in the #table?

Thanks in advance.

View 1 Replies View Related

Temp Tables

Jun 12, 2002

Hi,

I want to check to see if a temporary table exists before I try creating one but I can't seem to find which sys table or schema collection I check. Any ideas?

Seoras

View 2 Replies View Related

##Temp Tables

Jun 16, 2004

I have a stored proc that creates a temporary table, then calls several other stored procs to insert data.


CREATE PROCEDURE usp_CreateTakeoff
@iEstimate int,
AS

CREATE TABLE ##Temp_Takeoff
(
Field1 ......
Field2 ......
)

-- Add Structural data
usp_AddStructural @iEstimateID, 1, 'Structural'
usp_AddForming @iEstimateID, 2, 'Forming'
...
...
...
GO


Now, a couple of problems, after the table is created and populated, I cannot find it in my list of tables, even after "refreshing".

I checked to ensure that it exists using the query analyzer and it does so I know the table is being created.

Also, I cannot see the table using crystal reports, connecting etc...... Can I not access a temporary table from 3rd party applications? I have crystal reports 7.0 professional.

Any ideas?

Mike B

View 4 Replies View Related

Help With Temp Table!

Jul 22, 2004

Need a little help ...i am trying to create a temp table with a result set from a record query that yields, a two column multiple row resultset.

I would like to obtain the second column from the first result set and make that the first row in the first column of my temp table, then the first column from the second result set and make that the first row in the second column of my temp table, etc.

I.E

resultset:

color l number
green l 0
blue l 1
red l 2
black l 3

TO #temptable

A l B
0 l blue
1 l red
2 l black

I have tried to do this

but yielded :

A l B
0 l NULL
1 l NULL
2 l NULL
NULL l blue
NULL l red
NULL l black

Not exactly the results I need :( I would GREATLY appreciate any help!!!

THANK U!!

View 2 Replies View Related

Temp Tables And UDF's

Dec 14, 2004

Hey,

I am in the process of modifying some stored procedures that currently do not use temp tables. For this modification I am required to make the stored procedures use temp tables. There are several UDF's within this stored procedure that will need to use the temp tables, and this is where in lies the problem. Does anyone know of a work around that would allow UDF's to use temp tables, or does anyone know of alternate methods instead of temp tables that wouldn't involve too much change?

Thanks

View 1 Replies View Related

Temp Table?

Feb 22, 2005

I have 2 sql tables. One containing sales agents and sub-agents (Up to 5 or 6 levels deep), the other containing customers. Each customer has an agent associated with them. Agents Table - AgentId - ParentId Customers Table - CustomerId - AgentId What I need to do is when you select an agent, I need to return all of that agents customers AND all of that agents sub-agents customers, and down the tree like that. I think the way to do this is to use a Stored Procedure to create a TEMP table and then select theh top level agent, then continue a loop to get their subagents, then each of their subagents, etc.. and put the results all in this temp table. I then can call the temp table and retrieve the customers where the agent id matches any of the agents in the temp table. Am I thinking the correct way on this? I have never even built a stored procedure before, so can anyone help me with any tutorials or syntax for doing this? I still haven't been able to google anything yet. Thanks!

View 5 Replies View Related

#temp Query

Dec 2, 2005

I have a very long stored procedure running with a temp table in it. While the stored procedure is running (and the temp table hasn't been dropped) How can I query the temp table.

I've tried something like the following with no success:

Select * from tempdb..#tempTable

View 6 Replies View Related

Temp Table

Apr 4, 2008

1 how can we create a temp table using a stored procedure.
2. how can we eliminate duplicate rows from a table.

View 1 Replies View Related

Temp Tables

Apr 7, 2006

Hi,

I have a called stored procedure which creates a bunch of temporary tables, inserts data into them, indexes the tables and then returns to the main calling SP. In the main stored procedure I then do SELECTs from the temporary tables. My problem is I keep getting
invalid object errors on the temporary tables:
Invalid object name '#temp_table1'

The stored procedure is in a test environment. In the SELECT I tried a prefix of database owner (my logon) as well as "dbo." but still get the error. Any suggestions as to what I am doing wrong would be much appreciated.

Thanks,
Jeff

View 6 Replies View Related

Temp Table

Oct 4, 2006

sai writes "Hi,

I have a query like this

select sum(total) as app from f where f.file_id = g.file_id
and f.wire = 1 and f.desc = 'app'

like above query i have to select sum(total) for different descriptions.I want to keep all these in temp table and use those fields in main table.How do i do that?Iam new to SQL
can anybody help me.

Thanks In Advance"

View 2 Replies View Related

Temp DB Size

Feb 6, 2007

I could not figure out, if this is a feature.
I set TempDB to autogrow, it accepts it but when I go back to it it is showing as fixed growth or max. size as 2GB.
Has anybody encountered that ?

View 11 Replies View Related

Temp Table

May 14, 2007

Hi All,

Is there anything like

CREATE TABLE
temptable
AS
SELECT column1,column2... FROM table1 ...

I know there

SELECT column1,... INTO temptable FROM table1....

How would I go about this when I have

SELECT columns1,....FROM table1...
UNION
SELECT columns1,...FROM tableA...
UNOIN
SELECT columns1...FROM tableN..
UNOIN
.........


Into a TempTable.



Necessity is the mother of all inventions!

View 15 Replies View Related

Temp Table

May 18, 2007

I have a developer that used temp table to store data using
the select * into #tbl. Apparently the data got delete, I was just wondering how hong does the temp #tbl hold the data, and when does it delete the data?

thanks

View 5 Replies View Related

Need Help: Temp Table

Aug 27, 2007

How can I get data from temp table into vb recordset ?
I created a sp called sp_test, and hope it will return recordset

create proc sp_test
as
create table #list( code char(30), name char(200))
insert into #list ( code, name )
select Item, [Item Name] from tbItem
select * from #list
return

It works fine in Query Analyzer, now I try in VB 6.0

Dim cn as new ADODB.connection
Dim rs as new ADODB.recordset
cn.open ....
rs.open "select * from sp_test",cn
tdbGrid1.datasource = rs

vb cannot return recordset as I expected,
whats wrong with my vb program?
thanks

View 4 Replies View Related

Temp Tables

Sep 4, 2007

hi All,
I am using a temp table creating it as

create table #process
(
tons of coomuns in here
)
then
insert into #process(collumns)
select from peon
where etc....

Can i use the same temp table definition , but insert into another tempTable.Does alias help me accomplish this task.
Thanks for your input

View 3 Replies View Related

Help With These Temp Tables

Dec 11, 2007

In these two tables im just to bring the data back where the two DesignID's dont match. Im gettin an error

Server: Msg 107, Level 16, State 3, Line 1
The column prefix '#ttTopSellers' does not match with a table name or alias name used in the query.


Declare @CustomerID as VARCHAR(25)
Set @CustomerID = 'DELCOZ01-10'

/*Figure the designs that stores carry*/
Select Design.Description, Item.DesignID,
CustomerClassificationID, CustomerID, Region.[ID] as RegionID, Region.Name
Into #ttDesign
From Mas.dbo.Item Item
Inner Join MAS.dbo.Style Style
on Item.StyleID = Style.[ID]
Inner Join MAS.dbo.Line Line
on Style.LineID = Line.[ID]
Inner Join MAS.dbo.Design Design
on Item.DesignID = Design.[ID]
Inner Join Mas.dbo.DesignRegionIndex DRI
on Design.[ID] = DRI.DesignID
Inner Join MAS.dbo.Region Region
on DRI.RegionID = Region.[ID]
Inner Join MAS.dbo.CustomerClassificationRegionIndex CRI
on Region.[ID] = CRI.RegionID
Inner Join MAS.dbo.CustomerClassification CC
on CRI.CustomerClassificationID = CC.[ID]

Where @CustomerID = CustomerID
Group By Design.Description, Item.DesignID,
CustomerClassificationID, CustomerID, Region.[ID], Region.Name


/*This finds the top retail sales globally*/
Select Top 10 Sum(Sales) as Sales, DesignID, Design.[Description]
Into #ttTopSellers
From Reporting.dbo.RetailSales_ByStore_ByCustomer_ByDay_ByItem DI
Inner Join Mas.dbo.Item Item
on DI.ItemNumber = Item.ItemNumber
Inner Join MAS.dbo.Style Style
on Item.StyleID = Style.[ID]
Inner Join MAS.dbo.Line Line
on Style.LineID = Line.[ID]
Inner Join MAS.dbo.Design Design
on Item.DesignID = Design.[ID]
Where [Date] >= Month(getdate())-12
and DesignID <> 0
Group By DesignID, Design.[Description]
Order by Sum(Sales) Desc


Select *
From #ttDesign
Where #ttDesign.DesignID <> #ttTopSellers.DesignID


--Drop Table #ttDesign
--Drop Table #ttTopSellers

View 2 Replies View Related

Temp Table Name

Jul 23, 2005

I created a temp table #tBut in tempdb..sysobjects the name is stored as#t________________________________________________ __________________________________________________ ________________000000000018Any reason for extra length?Madhivanan

View 1 Replies View Related

#Temp Tables

Jul 20, 2005

Why cant I use the same temptable name i a stored procedure after i have droped it?I use the Pubs database for the test case.CREATE PROCEDURE spFulltUttrekk ASSELECT *INTO #tempFROM JobsSELECT *FROM #tempDROP TABLE #tempSELECT *INTO #tempFROM EmployeeSELECT *FROM #temp

View 1 Replies View Related







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