Hi,
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
I am using 6.5 .
Here is the error that i get
Think that tempdb is small how do i change that or what is this error about'
--------------------------
AIMSMan
---------------------------
Application-defined or object-defined error 40002
37000: [Microsoft][ODBC SQL Server Driver][SQL Server]Can't allocate space for object '##RevByNetSALIMJUMMA' in database 'tempdb' because the 'default' segment is full. If you ran out of space in Syslogs, dump the transaction log. Otherwise, use ALTER DATABASE or sp_extendsegment to increase the size of the segment.( 1105)
ODBC
I only have data and log device how do i increase the tempdb device
let me know
SEJ
I have a database in development in SQL Server 6.5 that needs to be occasionally deleted and rebuilt from a script when table structures are changed. I found that when very complex queries were performed, the 2 MB default size of tempdb filled up and returned errors, so I went to the Enterprise Manager to expand tempdb, learned that I had to first expand a device to expand tempdb into, and foolishly chose to expand tempdb into the same device space used by my application, instead of into one of the system databases. Now when I try to delete the device in preparation for its rebuild, the Enterprise Manager responds with an error message saying the device can't be deleted because it contains system tables. Is there any way to get the expanded portion of tempdb out of my application device so that the device can be deleted, without reinstalling SQL Server?
View 1 Replies View RelatedI would like to free up some space on a SQL 6.5 server. My tempdb is on a second device (other than master) that is 500M. I don't need tempdb to be this large anymore. What would happen if I shut down SQL, renamed this device so that SQL Server wouldn't use it, and restarted SQL Server. Could I then create a new, much smaller device for tempdb? Would this work?
TIA!
JJ
Hello:
We are supporting an mssql 6.5, sp 4 application. My associate recently
increased tempdb from 3 gb to 15 gb by adding a 12gb device on a second drive.
The original tempdb device was 3 gb on an h drive and the 12gb addition was added to the f drive limiting the disk space for the data dat file to be expanded. So now I would like to move that second tempdb device to a different drive to free up disk space on the f drive.
1) Can I move the second tempdb device?
2) Or should I try to shrink it and then expand it on a differetn drive?
3) Any other suggestions?
Any information you can provide will be greatly appreciated.
THanks.
David
I want to duplicate the environment of another existing server where tempdb
resides on it's own device (200mb in size). I can create the devices (data & log). What do
I have to do to physically move tempdb to the new devices I create? I've seen such
articles on Swynk.com, but today the links are not working. Thanks very much!
Mark Blackburn o `"._ _ M onterey
mark@mbari.org o / _ |||;._/ ) B ay
Science at its Best! ~ _/@ @ /// ( ~ A quarium
(831) 775-1880 ( (`__, ,`| R esearch
http://www.mbari.org/ '.\_/ |\_.' I nstitute
Database Administrator
MBARI Personal Web Page: http://www.mbari.org/~mark/
Hi -
In order to restore a DB on another system I need to know the exact expansion history of the source DB in order to create/expand the devices on the target system the same way...
Is there a way for me to see the history of expansions I did on a DB ??
Tanx,
Paul
I'm at a new installation where there's no DBA at all, so, as a Coldfusion programmer, I'm now the DBA, LOL.
The main SQL2000 DB we use is approximately 100MB with about 7MB of free space and is allocated to expand by 10%.
I am adding a new, large table, about 60 columns with lots of variable-length unicode fields, mostly nvarchar. It's being used to track non-USA user-form information. Even though the DB is set to expand, I'm concerned that due to the potential size/volume of records, that the auto-expand could cause performance issues.
The SQL2000 server has plenty of room, about 49GB, so I'm wondering if I should expand the size of the current DB, or if the auto-expand feature will be ok.
At this point I'm not sure what the volume of the user-form records will be in the new table. It won't be a million records certainly, but I'm guessing it could climb to maybe 10-20,000 records.
If I should expand the DB, can I do this while the DB is still online?
thanks...
I need to expand the size of one column which has been defined as varchar(32) to varchar(50).Is this possible?Already there are many old records in the table,in what way it will effect the old records?Any help is appreciated.
Thanks!!
In SQL 6.5 and SQL 7.0 when Enterprise Manager started it would show the server groups in expanded view.
SQL 2000 shows just the Microsoft SQL Servers level not expanded and none of the groups under it.
With the SQL 2000 client, is there a setting that will expand my tree automatically when starting Enterprise Manager. I have a bunch of server groups defined and do not want to have to expand each one every time I start EM so that I can view my servers.
Thanks for any help.
SQL Server 2008 R2
Issue: In the globalts I haveÂ
<Expansion>
<sub>Multi-lan</sub>
<sub>Multilan</sub>
</expansion>
my where clause.. is similar to.'where Contains(Word, ''formsof(thesaurus,"'+@curWord+'") OR "'+@curWord+'*"'')'When i pass in Multi-Lan, i get results for Multi-LAN and MultiLAN.. however.. when i pass in Multilan.. i dont get results for Multilan nor Multi-lan.I have double checked my formats, and i have checked to see if this word was used in a previous expansion set.. everything checked out. Â I also have many other expansions that function without issue. BT and Bluetooth are interchangeable.
I've created some Matrix reports which work just fine, I'm able to drill down and the expansion images(+/-) appear correctly.
However, after setting Role security on the folder containing the reports(this is an asp.net application), the reports still function but the images for the (+/-) are missing, just getting the missing image icon.
Any ideas?
Thanks, Burl
hi all,
i have a device file which i know for sure contains all the data for a database (not the log). is there any way to recover the data from this file?
thanx.
:-)
hemant
Hi,
We currently have a fairly new SQL server 2000 db (currently about 18mb is size) as a backend to an application (Navision). Performance seems to be below what it should be.
The db is increasing quite rapidly in size, with a lot of data scheduled to be loaded onto the db and also more and more shops and users coming onto the system with alot more transactions going onto the db.
The initial setup of the db has the database File properties set to "Automatically grow file" by "30%" and has an unrestricted file growth.
The server that the db sits on is high spec and very large disk space.
Because the database will be expanding alot and thus reaching its maximum space allocation and then performing a 30% increase in size (which I guess affects performance quite a bit??) quite regularly.
Is it best to set the intitial size of the db to a alot bigger size in the first place as we have large disk space availiable and also set the % increase bigger also.
any advice on best performance would be much appreicated.
Regards,
David
Basically I've been using Visual Studio 2005 for a few weeks now moving a Pocket PC project from 2003 to 2005. When I hit the Start Debugging Button every time until today the project would rebuild and deploy to my pocket PC allowing me to debug etc but now I get
The remote connection to the device has been lost.
Please verify the device conection and restart debugging.
I used to get this problem in VS2003 sometimes and just like the numerous posts on different sites that I've looked at the problem eventually goes away and I'm none the wiser. One guy said that he found that if he went to bed the problem was resolved when he came back!
My PDA running Windows 2003 2nd Edition is directly connected to my PC via a USB port. I've rebooted my PC and done a soft reset on the PDA but it didn't help. I'm using ActiveSync 4.1.
Does anyone know how to resolve this problem?
Hi to All
when i m trying to execute following code
backup database web
to disk = 'c:inetpubwwwrootackupmybakup.bak'
with format
I m Getting Error like :
Server: Msg 3201, Level 16, State 1, Line 1
Cannot open backup device 'c:inetpubwwwrootackupmybakup.bak'. Device error or device off-line. See the SQL Server error log for more details.
Server: Msg 3013, Level 16, State 1, Line 1
BACKUP DATABASE is terminating abnormally.
This error is Generated only when i m trying to access folders within "wwwroot" but not in any other folders , even command runs success fully for "wwwroot" folder . !!
but not for any subfolders of wwwroot.
Can Any One Help Me ??
i had to fromat the harddisk for some reason , but took a backup of the data device file . is it possible to recover tha data from them .
View 1 Replies View RelatedI want to update data on the device which has a sql server mobile database.
the main database is sql server.
I use web services to enable the mobile application to access the server.
Can anyone recommend a way of updating the data on the device without using replication? I basically want to read a dataset from the server via webservice and put this in to local db - but not row by row as too many records.
Regards
If a Select is done on a column whose data type is nvarchar(16) and contains only numerals (UPC numbers) the select does not return the record.
1. Query with numerals in nvarchar column works as long as multiple records are returned (LIKE '012%')
2. Numeric (INT only one tested) columns works as expected
3. String columns with alpha data works as expected
4. Problem only exist when running in Device Emulator and/or actual device.
5. Same test on desktop app runs as expected.
6. Windows Mobile 6, Vista Ultimate
7. Same results when when connection to device from SSMS
8. SQL Servers comes on
Previous thread discussion of this problem (I thought that Parameters corrected problem, but not in all cases???)
http://www.microsoft.com/communities/newsgroups/en-us/default.aspx?dg=microsoft.public.sqlserver.ce&mid=0cd9cd3a-f9b0-477f-b1e7-c27eb76158ae
Here is the complete code:
SqlCeConnection _conn = null;
_conn = new SqlCeConnection(@"Data Source=program FilesTestResultSetevsoft.sdf;");
_conn.Open();
// DOES NOT WORK *** This statement does not return the record (it exist)
string _sql = "SELECT * FROM Product where RegDescr='0123456' ";
// works correctly
string _sql = "SELECT * FROM PRODUCT where ProdNum = 6523 ";
// works correctly *** as long as multiple records are returned
string _sql = "SELECT * FROM PRODUCT where RegDescr LIKE '01%' ";
// works correctly
string _sql = "SELECT * FROM PRODUCT where RegDescr='BACARDI SILVER RAZZ'";
SqlCeCommand _cmd = _conn.CreateCommand();
SqlCeDataReader _rdr;
_cmd.CommandText = _sql;
_cmd.CommandType = CommandType.Text;
// Same results using ExecuteResultSet or ExecuteReader
//_rdr = _cmd.ExecuteResultSet(ResultSetOptions.Scrollable | ResultSetOptions.Updatable);
_rdr = _cmd.ExecuteReader();
listBox1.Items.Add("In the while loop");
while (_rdr.Read())
{
listBox1.Items.Add(_rdr.GetValue(1) + " / " + _rdr.GetValue(3));
}
listBox1.Items.Add("Done");
Presently, we have allocated 600MB to tempdb and when I run the sp_spaceused command for tempdb I notice that the unallocated space is a negative number. I have not received any errors in the error log with regards to expanding the segment because segment is full. Is this just a bug in SQL 6.5 or something else?
View 2 Replies View RelatedHello,
I have the following situation.
tempdb data file has 8Mb and log file has 1Mb - but I´m getting message that log is full.
Once tempdb is shrinked and expanded by the system (we even don´t see it at database folder!!), what can be done, (except reinstall from scrach and restore DBs) to make tempdb not vulnerable to very frequent expanding/shrinking (I guess this can be one of the root of the problem) ?
TIA
Massa/mig
Against my better judgement, we are using fixed allocations of tempdb on some of our servers. This is to deal with specific limitations of our applicaitons and hardware configuration that I'm not allowed to discuss in much detail.
The problem that I have is that the present plan is to configure the data file at around 18 Gb and the log file at around 2 Gb. This seems just plain wrong to me, but I haven't been able to find a formal recommendation that gives any relative sizing. I would expect to have about twice as much log as data space, especially for tempdb.
Does anyone know of a formal citation (preferably from Microsoft) that discusses this?
-PatP
This is a SQL 2005 production server.
I have to delete around 51 million rows from a table which has 149 million rows.
Can't use truncate option as the other rows in the table are still needed.
How can I delete the rows without filling up the tempdb ?
If the tempdb fills up I can't bounce the server.
It is possible that Data Collection can cause massive increasing MB/sec to tempdb ? I cannot find connection with tempdb and I set cash file, but on same disk.
Or it can be something different? Last two weeks what I checked was Read/Write MB/s to tempdb increasing progressively.
One time it was about 20MB/sec
After it was reseting and again 1MB/sec..
What I checked , External company which install SQL Server made one file for tempdb, next week or during breaktime(it will be possible), I would like make 8files next weekend work.
Now I saw that TempDB mdf was still increased, but using was just 8-10%
I'm having an argument with our infrastructure architect who has just gone and bought lots of SSD drives to use for our tempdb data and log files, sounds great doesn't it? There is a catch though, his plan is to add the disks to the two available slots in each blade in a RAID0+1 configuration, effectively giving you one usable drive, and adding both data and log files on to one disk.
I then pointed out that SQL Server best practice is to host tempdb data and log files on two separate drive to reduce contention. The architect then basically said that because this isn't spinning disk the issue of drive, r/w contention isn't an issue I don't agree with this and wanted to get some opinions from the community, I'm still advising that two separate disks should be used but someone just went and spent £80k ($150k) on SSDs and doesn't want to back down...
Hi,
I have some stored procedures that create table data types for temporary tables. From everything I've read about these, they shouldn't occupy space in tempdb. However we've been having problems with our tempdb log growing quite large, so I've done some profiling on the database and found that the temporary tables are being created in tempdb, and I don't understand why.
The Stored Procedure looks like:
-- WORKING TABLES
DECLARE @t_InsertParams TABLE (ParamKey1 VARCHAR(50) NOT NULL, ParamValue VARCHAR(1024), ParamOrder INT)
/*** POPULATE PARAMS TABLES ***/
INSERT @t_InsertParams
SELECT *
FROM dbo.GetParamsOffload(@p_ParamList, @p_TargetTable)
and GetParamsOffload is a user defined function that returns a table:
CREATE FUNCTION GetParamsOffload(@p_ParamsList AS VARCHAR(8000), @p_TargetTable AS VARCHAR(50))
RETURNS @t_Params TABLE (ParamKey1 VARCHAR(50) NOT NULL, ParamValue VARCHAR(1024), ParamOrder INT)
AS
<snip>
INSERT @t_Params
SELECT @_ParamKey
,@_ParamValue
,ISNULL(@_ParamOrder, 0)
<snip>
RETURN
During the profiling of the application, I see numerous records of EventType "Object:Created" for the tempdb DatabaseId and the ObjectName is both @t_InsertParams and @t_Params. I am also monitoring the "Objecteleted" EventType, but I never see these objects deleted.
My first question is why this code uses the tempdb in the first place. I've read that you can't use the table data type in a few instances (can't insert the values of a SP execute into the table data type; cannot do SELECT INTO or INSERT INTO statements). Is there an issue with inserting data into the table data type from a function? Is there something that can be changed in the way the table data type is defined/used that will not use tempdb?
If I am stuck with the above using tempdb, is there something that should be explicitly called to Delete the specified objects from tempdb? Do I need to Drop those tables in my SP?
This SP is called via a CSV file parser - gets called A LOT over and over parsing files and I see these objects getting created in tempdb. I need to figure out how this process can manage the space in tempdb better.
I'm using SQL Server 2000.
Thanks for any help.
Beth
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
We had someone create an extra data file and log file for tempdb. Sowe currently have two data files and two log files. Is it possible todelete the newly created data and log files? If I just delete thephysical files, I assume they'll get created as soon as SQL Servergets started back up. Any help would be great, since a single dataand log file for tempdb is my goal.Thanks much.sean
View 3 Replies View RelatedIn tweaking performance of tempdb by adding add'l data files I want to reset back to defaults and remove all add'l files I've created. Â I was not able to do it for most as they were in use, but by starting the server in single-user mode with all other sql services shut off, and using sqlcmd I was able to use the ALTER DATABASE tempdb REMOVE FILE <tempdev#> to remove the files... except for one.
I have also tried using the command:
ALTER DATABASE tempdb MODIFY FILE (NAME = tempdev#, FILENAME = '<new location');
Restarted SQL server, and tried the ALTER DATABASE ... REMOVE FILE again but am always denied with the message that the file can't be removed b/c it's still in use.
I also tried to shrink it with EMPTYFILE but that also fails with the message that a page is a work table page and can't be removed.
I really need to get tempdb back to just one data file but am unable to find a way to remove this last data file.
Is there an Admin script that yields the following directories?
Data
Data Root
Log
TempDB
Shared Feature
Backup
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?
As far as i know sql server iterators spill to tempdb when estimation number of rows less than actual number of rows ? for my example estimation > actual,but sort iterator still spill to temdb.
View 6 Replies View RelatedWe 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