?
Solved

remove added files to SQL temdb database

Posted on 2011-03-24
5
Medium Priority
?
490 Views
Last Modified: 2012-05-11
I just noticed that someone added files to the tempdb on a different disk. Problem is the disk the files were added to was only meant as a temporary disk and should not be there. How do i remove the secondary database file and the secondary log file from the database without screwing things up. I have attached a screen shot from management studio. Basically i need to get rid of the ndf and ldf on the G: drive.
Capture.JPG
0
Comment
Question by:netstaff
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
5 Comments
 
LVL 40

Expert Comment

by:lcohan
ID: 35210725
Remember tempdb is rebuilt at each SQL server service restart so it's not a big problem if you don't run a clustered server and SQL system dbs were installed localy - that would be bad..
Assuming all these you could simply remove those files but you must make sure there is sufficient disk space where the other permanent files are by running ALTER DATABASE commands.
The system objects will be updated and after a restart tempdb will have the new desired configuration.
0
 
LVL 1

Author Comment

by:netstaff
ID: 35210954
I am less than a novice when it comes to sql so could you please explain the alter databse commands you referenced. Also sql is clustered but the system db's are on shared disks and not local.
0
 
LVL 4

Accepted Solution

by:
RGBDart earned 1000 total points
ID: 35214321
To move these files from G: to S: you may use following script

alter database tempdb 
MODIFY FILE (Name=tempdev_2, FILENAME='S:\MSSQL10.MSSQLSERVER\MSSQL\DATA\tempdev_2.mdf')

alter database tempdb 
MODIFY FILE (Name=templog_2, FILENAME='S:\MSSQL10.MSSQLSERVER\MSSQL\DATA\templog_2.mdf')

Open in new window


If you want to remove these files, use following scripts

alter database tempdb REMOVE FILE tempdev_2
alter database tempdb REMOVE FILE templog_2

Open in new window

0
 
LVL 40

Assisted Solution

by:lcohan
lcohan earned 1000 total points
ID: 35217812
http://msdn.microsoft.com/en-us/library/bb522469.aspx
     "The file cannot be removed unless it is empty." therefor before you can remove the files you must empty them but let me ask you why do you want to remove them?

BTW - is G:\Files attached to the SQL Cluster or is a local to the active node?

If G: is local to a node but the SQL sysdbs are indeed on a cluster drive then I suggest you move them first by running alter database comands below(caution the ones posted by RBGDart above may have incorect file extension), and restart SQL Service on the active node from Cluster Manager when you can afford the downtime. Careful do not do a failover as your other node G:\files won't have the tempdb files and SQL may not restart.

alter database tempdb
MODIFY FILE (Name=tempdev_2, FILENAME='S:\MSSQL10.MSSQLSERVER\MSSQL\DATA\tempdev_2.ndf')
alter database tempdb
MODIFY FILE (Name=templog_2, FILENAME='S:\MSSQL10.MSSQLSERVER\MSSQL\DATA\templog_2.ldf')

After you are back on an node with tempdb files location changed to the cluster maped drive S:\ then you can decide if you still want to drop them or not as Microsoft recommends 1 tempdb data file per socket but not more than four as far as I'm aware. Please check details below:

http://msdn.microsoft.com/en-us/library/ms175527.aspx

"Create as many files as needed to maximize disk bandwidth. Using multiple files reduces tempdb storage contention and yields significantly better scalability. However, do not create too many files because this can reduce performance and increase management overhead. As a general guideline, create one data file for each CPU on the server (accounting for any affinity mask settings) and then adjust the number of files up or down as necessary. Note that a dual-core CPU is considered to be two CPUs."

http://technet.microsoft.com/en-us/library/cc966545.aspx
0
 
LVL 40

Expert Comment

by:lcohan
ID: 35217820
Having said that - if you decide to remove something than I suggest you only remove the additional Log file
0

Featured Post

Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this article I will describe the Detach & Attach method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
Video by: ITPro.TV
In this episode Don builds upon the troubleshooting techniques by demonstrating how to properly monitor a vSphere deployment to detect problems before they occur. He begins the show using tools found within the vSphere suite as ends the show demonst…
In this video, Percona Director of Solution Engineering Jon Tobin discusses the function and features of Percona Server for MongoDB. How Percona can help Percona can help you determine if Percona Server for MongoDB is the right solution for …

718 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question