473,856 Members | 1,750 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

Deleting extra tempdb log and data files

We had someone create an extra data file and log file for tempdb. So
we currently have two data files and two log files. Is it possible to
delete the newly created data and log files? If I just delete the
physical files, I assume they'll get created as soon as SQL Server
gets started back up. Any help would be great, since a single data
and log file for tempdb is my goal.

Thanks much.
sean
Jul 20 '05 #1
3 11878

"Sean Lambert" <sl*********@ya hoo.com> wrote in message
news:27******** *************** ***@posting.goo gle.com...
We had someone create an extra data file and log file for tempdb. So
we currently have two data files and two log files. Is it possible to
delete the newly created data and log files? If I just delete the
physical files, I assume they'll get created as soon as SQL Server
gets started back up. Any help would be great, since a single data
and log file for tempdb is my goal.
You should be able to simply shut down SQL Server, delete them (back them up
just in case :-) and start it back.

(assuming SQL 2000).

If not, I'd probably try starting SQL Server in single user mode and
removing them there.

But, again, back up everything etc.


Thanks much.
sean

Jul 20 '05 #2
sl*********@yah oo.com (Sean Lambert) wrote in message news:<27******* *************** ****@posting.go ogle.com>...
We had someone create an extra data file and log file for tempdb. So
we currently have two data files and two log files. Is it possible to
delete the newly created data and log files? If I just delete the
physical files, I assume they'll get created as soon as SQL Server
gets started back up. Any help would be great, since a single data
and log file for tempdb is my goal.

Thanks much.
sean


Assuming you have SQL2000, then this covers it:

http://support.microsoft.com/?kbid=814576

In summary, use DBCC SHRINKFILE with EMPTYFILE to remove any data on
the files, then ALTER DATABASE ... REMOVE FILE.

Simon
Jul 20 '05 #3
I'm using SQL7. Sorry...I should have mentioned that initially. Thanks!

sean

sq*@hayes.ch (Simon Hayes) wrote in message news:<60******* *************** ****@posting.go ogle.com>...
sl*********@yah oo.com (Sean Lambert) wrote in message news:<27******* *************** ****@posting.go ogle.com>...
We had someone create an extra data file and log file for tempdb. So
we currently have two data files and two log files. Is it possible to
delete the newly created data and log files? If I just delete the
physical files, I assume they'll get created as soon as SQL Server
gets started back up. Any help would be great, since a single data
and log file for tempdb is my goal.

Thanks much.
sean


Assuming you have SQL2000, then this covers it:

http://support.microsoft.com/?kbid=814576

In summary, use DBCC SHRINKFILE with EMPTYFILE to remove any data on
the files, then ALTER DATABASE ... REMOVE FILE.

Simon

Jul 20 '05 #4

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

10
13284
by: Jay Chan | last post by:
I keep getting the following error message when I run a serie of SQL commands: Server: Msg 9002, Level 17, State 6, Line 15 The log file for database 'tempdb' is full. Back up the transaction log for the database to free up some log space. I have tried "dump transaction tempdb with no_log" right before I run the SQL command. But that doesn't help.
3
8299
by: tom horner | last post by:
Something strange is happening to our SQL Server DB (2000). The tempdb transaction log file continues to grow (quite slowly) for no apparent reason. We have it in simple mode, and I have tried a manual checkpoint command and manual shrink (of the log file only). There are no unusual SQL's (large or small) going on. A "heavy hitter" would make it grow fast, not 10 MB every 30 minutes or so. This server has been in production for over a...
2
3740
by: Warren Wright | last post by:
Hi All, First, where can I get some questions of this sort answered? Preferably, are there good books or online guides that I can consult for these types of answers when necessary? 1. How do I know, when executing a query from Query Analyzer or otherwise, how many temporary tables will be needed, and how big they will be? 2. Where will those temporary tables be created? Always in tempdb?
2
3904
by: Tom | last post by:
I received an error that the log in tempdb was full, but the log and data segments are set to automatically grow with no limit AND there is plenty of available space on the disk. So I don't understand why this error occurred. Does anyone have any ideas? Thanks, Tom
0
1907
by: Rob | last post by:
Hello, I've a couple of questions on setting up my lovely new server... I've decided on R1 (2*36GB) for the OS, R1 (2*73GB) primarily for the Log Files and R5 (5*73GB) for the Data files. I was wondering if I could increase performance by partitioning off/ creating a new volume on my log array and placing on there the tempdb and possibly another volume for the indexes.ndf. Any ideas on whether its OK to have other volumes use the same...
8
12787
by: arijitchatterjee123 | last post by:
Hi Group, I am facing a problem regarding locking. I have created a Stored Procedure in my Database. In this Stored Procedure Temprary Tables get created and after that values are inserted in these tables.But this Stored Procedure is called from Java Portal. When then Stored Procedure execute from Java end new transaction begins. So all these temporary tables are created in tempDB and locking the entire Database. So at the same time if...
3
14106
by: New MSSQL DBA | last post by:
Hi all, I have a tempdb that consists of 8 datafiles, tempdb_data_1 to tempdb_data_8, each is 8GB. Now how can I drop 7 of them and leave only tempdb_data_1? Can this be done? Thanks a lot.
3
7110
by: Kurt | last post by:
Hello I have questions about how works transaction log et the database tempdb in SQL Server and I hop you could help me - Is it possible to reduce the size of the transaction log fil during an execution ? Indeed, I have a script inserting a very large quantity of data (many Go) and during that process my transaction log file use all the space avaible on my hard drive. Is there any way to solve that problem ? - Is it possible to limit...
0
1769
by: VIPS | last post by:
On Apr 3, 7:29 am, "Krisnamourt via SQLMonster.com" <u21487@uwe> wrote: REGARDING TEMP DB SIZE: -For a small db server that does about 10-20 GB of logging per day, I would recommend having a size that does not go through AUTO_GROW. i.e ensure that the size of tempdb is big enough that it will hold the whole days work.
0
9904
marktang
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look ! Part I. Meaning of...
0
10693
jinu1996
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth. The Art of Business Website Design Your website is...
0
10378
tracyyun
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
0
9527
agi2029
by: agi2029 | last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own.... Now, this would greatly impact the work of software developers. The idea...
0
7087
by: conductexam | last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert into image. Globals.ThisAddIn.Application.ActiveDocument.Select();...
0
5756
by: TSSRALBI | last post by:
Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
0
5956
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
2
4170
muto222
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.
3
3196
bsmnconsultancy
by: bsmnconsultancy | last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.