Solved

remove added files to SQL temdb database

Posted on 2011-03-24
5
468 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 39

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 39

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 39

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

Top 6 Sources for Identifying Threat Actor TTPs

Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

Join & Write a Comment

Suggested Solutions

I have written a PowerShell script to "walk" the security structure of each SQL instance to find:         Each Login (Windows or SQL)             * Its Server Roles             * Every database to which the login is mapped             * The associated "Database User" for this …
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.
In this seventh video of the Xpdf series, we discuss and demonstrate the PDFfonts utility, which lists all the fonts used in a PDF file. It does this via a command line interface, making it suitable for use in programs, scripts, batch files — any pl…
This demo shows you how to set up the containerized NetScaler CPX with NetScaler Management and Analytics System in a non-routable Mesos/Marathon environment for use with Micro-Services applications.

707 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

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now