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  14084 
New MSSQL DBA (bo*******@gmail.com) writes: 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.
I have not tried it, but my naive guess is that you would do as for
any other database. That is first DBCC SHRINKFILE with EMPTYFILE, and
then ALTER DATABASE.
--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se
Books Online for SQL Server 2005 at http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at http://www.microsoft.com/sql/prodinf...ons/books.mspx
I've tried this:
use tempdb
go
dbcc shrinkfile(tempdev4, emptyfile)
go
alter database tempdb
remove file tempdev4
go
then it gives something like this:
Server: Msg 5042, Level 16, State 1, Line 1
The file 'tempdev4' cannot be removed because it is not empty.
File 'E:\Microsoft SQL Server\MSSQL\data\tempdev4.ndf' modified in
sysaltfiles. Delete old file after restarting SQL Server.
Don't know what does this mean. Does it mean that the file will be
dropped after I restarted the server?
New MSSQL DBA (bo*******@gmail.com) writes: I've tried this:
use tempdb
go
dbcc shrinkfile(tempdev4, emptyfile)
go
alter database tempdb
remove file tempdev4
go
then it gives something like this:
Server: Msg 5042, Level 16, State 1, Line 1
The file 'tempdev4' cannot be removed because it is not empty.
File 'E:\Microsoft SQL Server\MSSQL\data\tempdev4.ndf' modified in
sysaltfiles. Delete old file after restarting SQL Server.
Don't know what does this mean. Does it mean that the file will be
dropped after I restarted the server?
I don't know, but why not give it a try?
If this is a production machine, you may want to first play with a
test server, to see the effects of the commands.
--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se
Books Online for SQL Server 2005 at http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at http://www.microsoft.com/sql/prodinf...ons/books.mspx This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics | |
by: M A Srinivas |
last post by:
Hello,
I would like to know is it possible to disable drop database for sa
or sysadmin. If saor sysadmin needs to drop the database , he/she may
have to change status in one of the system...
|
by: Sean Lambert |
last post by:
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...
|
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...
|
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...
|
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...
| | |
by: Ryan |
last post by:
Just a quicky about temporarary tables. If using QA, when you create a
temporary table, it gets dropped if you close the query. Otherwise you
need to state 'DROP TABLE myTable' so that you can...
|
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...
|
by: SQL Server |
last post by:
Hi,
The tempdb file on one of our servers grew very large and used all
available disk space. This is SQL Server 2000 SP4. I have installed
hotfix version 8.00.2187. I opened a profiler trace but...
|
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...
|
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,...
|
by: Hystou |
last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can...
| | |
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...
|
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,...
|
by: isladogs |
last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM).
In this session, we are pleased to welcome a new...
|
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...
|
by: 6302768590 |
last post by:
Hai team
i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated ...
|
by: muto222 |
last post by:
How can i add a mobile payment intergratation into php mysql website.
| | |
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...
| |
