Solved

remove added files to SQL temdb database

Posted on 2011-03-24
5
479 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 250 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 250 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

Free eBook: Backup on AWS

Everything you need to know about backup and disaster recovery with AWS, for FREE!

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
SQL Insert parts by customer 12 53
Separate 2 comma delimited columns into separate rows 2 46
sql query help 15 52
T-SQL: Need Group By to use "fuzzy logic"?? 3 18
There have been several questions about Large Transaction Log Files in SQL Server 2008, and how to get rid of them when disk space has become critical. This article will explain how to disable full recovery and implement simple recovery that carries…
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…
The Email Laundry PDF encryption service allows companies to send confidential encrypted  emails to anybody. The PDF document can also contain attachments that are embedded in the encrypted PDF. The password is randomly generated by The Email Laundr…
In a recent question (https://www.experts-exchange.com/questions/29004105/Run-AutoHotkey-script-directly-from-Notepad.html) here at Experts Exchange, a member asked how to run an AutoHotkey script (.AHK) directly from Notepad++ (aka NPP). This video…

756 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