Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

remove added files to SQL temdb database

Posted on 2011-03-24
5
Medium Priority
?
497 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
  • 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

New feature and membership benefit!

New feature! Upgrade and increase expert visibility of your issues with Priority Questions.

Question has a verified solution.

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

After restoring a Microsoft SQL Server database (.bak) from backup or attaching .mdf file, you may run into "Error '15023' User or role already exists in the current database" when you use the "User Mapping" SQL Management Studio functionality to al…
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…
this video summaries big data hadoop online training demo (http://onlineitguru.com/big-data-hadoop-online-training-placement.html) , and covers basics in big data hadoop .
Are you ready to place your question in front of subject-matter experts for more timely responses? With the release of Priority Question, Premium Members, Team Accounts and Qualified Experts can now identify the emergent level of their issue, signal…

963 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