Documentation that supports the placement of Tempdb files on the root of a drive, i.e T: instead of T: empdb. I am positive this is not a best practice, but when challenged could not find any documentation that would support that view.
View 7 RepliesI have a 'need an opinion' question. I've always installed the system databases (master, msdb, model, tempdb) on the root drive (c:) with the sql application. Then I was thinking, the only things that really should be on the root drive is the sql app itself (and any other required apps). So...my question is, do y'all install the master, etc on a separate drive other than the root, ie d: or e:? The reason I ask is that if the tempdb database has alot of activity, and the database resides on the root drive, could it affect sql performance?
Just wondering. I just started working in a 'virgin' sql environment (setting up sql servers from scratch), and want to have sql installed in the best possible way for performance.
Thanks in advance for any info y'all can provide.
Server: SQL 2008 R2
1: TempDB keeps getting filled. Restart of the server has not fixed it. I shrink it, but the space gets filled again. Now I can't even shrink it anymore
2: TempDB is at the wrong location. Its current location is this :C:Program FilesMicrosoft SQL ServerMSSQL10_50.SQLPROD6MSSQLDATA empdb
How do I change its location?
C:Program FilesMicrosoft SQL ServerMSSQL10_50.SQLPROD6MSSQLDATA empdb
Correct location of TempDB should be: TempDB(T:) But its not there
hi
my application is applocation1 i kept a file in this folder i want to read it from application how can i get the path of that file in my application.
i want to read the file in my root folder excluding the bin path
Please any one know the answer let me know ..
thanks and regards
Ravishanker Maduri
Hello all ...
is there a standard procedure or document that explains how to:
Expand tempdb onto a faster drive ... making it larger
then ...
Remove the small portion of tempdb from my c: drive to reduce contention?
Thanks!
Doug
I'm having difficulties copying a production DB to a new computer using backup files. The production computer had tempdb on the D: drive, the new computer is much smaller and only has a C: drive. I've successfully restored the Master DB backup but now the database will only start with the (-F) parameter. I know how to Alter the DB to move the tempdb, but I cann't get the DB to start while Tempdb is pointed to the D: drive
View 5 Replies View RelatedI proposed on a new server that we separate Data Files, Log Files, tempDB, Backups, etc. onto separate LUNS on a SAN with High Speed Solid State Drives.I was told that with the new technology with solid state SAN's that it would decrease performance and that it did not work the same way as it did when you had RAID 5's etc.I thought that if things were cared out correctly by a SAN Administrator they would know how to configure for optimal performance.
View 2 Replies View RelatedWe are seeing very high Average Disk Queue Length numbers in one of our clusters (both nodes of the cluster are Virtual, but have their own dedicated virtual environments). Our main data drive also houses TempDB, which I would like to move.
Each node in the Active/Passive cluster are running Windows Server 2012 Standard 64bit and SQL Server 2012 Enterprise 64bit. There is a separate drive for Log files and data files.
The data files also have TempDB on them as previously mentioned. I am reading that you can set up a local disk on each node of the cluster, with the same drive letter and path and then move tempdb as you would with a stand alone SQL Server.
I just upgraded SQL6.5 standard(Installed 7.0 and ran Wizard to upgrade dbs) to SQL7.0 by running the exe file.
It turned out that the MSSQL7 folder is follen on Drive C instead of Drive D which is what I wanted. When I was running the upgrade exe file, there was no prompt to ask me the right path.
How could I have the MSSQL7 folder installed on D drive?
Thank you for help
All,
I'm writing to ask if anyone knows whether or not MS SQL server stores in any system tables the association between a database and the drive letter/directory path where its corresponding MDF/LDF files are located.
Thanks,
Isaac
Hi friends, i want to know something abt sql server installation and database properties as under.1) how much disk space reqd by multiuser sqlserver 2000 db to install ?2) is it possible to install sql 2K on Flash Drive ( gen connect at USB) ?3) When v create a database user Enterprise Manager , which files created by database ?4) If i Know datafiles name of a database e.g. pubs , and if v copied on cd and paste on other system which has sql 2K install , does those database works ?5) is it possible to create database on flash drive ? thanx in advance.
View 2 Replies View RelatedHowdy y'all! :)
I have been instructed to move a large database we have on one of our servers off the current drive (local RAID-5 driveset in the server) to a EMC "drive" (logical drive, off-server).
I know one option is to back up the database, delete the database, re-create the db using the new drive for data/log files, then restore the database.
However, I was wondering if it would be better to just detach the DB, move the data/log files, then reattach to them?
Is it half-doz of one, and 6 of the other?
How should I go about this dastardly deed?
Off to poke around in BOL, but thought I would post first in case it's an incredibly easy answer for y'all
Thanks!
We have a cluster with two nodes and two instances of SQL Server 2012 Standard Edition running on them. Volume W: is a Fusion-IO card.
On one of these nodes a lot of database names are showing up in the resource monitor as *.mdf files (W:0MSSQL1…).
How and why SQL Server is using these files? They only show up on one of the nodes having more load.
Volume I: is the volume where the transaction log is written so we can explain these files.
Hello guys and girls. I have installed SQL Server 2005 Standard Edition and I have specified that the databases should be created on the K: drive. This is okay but now I need to move all the transaction log files (.ldf) to the L: drive. I have already changed the default location for the log files to point to the L: drive and the new databases that were created after the installation have their transaction log file correctly in the L: drive but now I need to move transaction log files for the master, model, temp ... databases. How can this be done? And are there any gotchas?
View 11 Replies View RelatedI have a database [CarlosDB] that currently has it's .MDF on E: and I need to move the x2 .NDF data files off C: to E:data using a single T-SQL statement:
Code:
database_id file_id db_name disk_path status size read_only
----------- ----------- --------------------------------------------------------------------------------------------------------------------------------
7 1 CarlosDB E:dataCarlosDB.mdf ONLINE 384 0
7 2 CarlosDB_log L:logsCarlosDB_log.ldf ONLINE 128 0
7 3 CarlosDB_2 C:sqlCarlosDB_2.ndf ONLINE 128 0
7 4 CarlosDB_3 C:sqlCarlosDB_3.ndf ONLINE 128 0
(4 row(s) affected)
Looking at the file configuration above, what would be the most logical way as a DBA / SQL Server 2014 Std to move the NDF files to live w/ the MDF file using:
Code:
EXEC master.dbo.xp_cmdshell 'copy c:sqlCarlosDB_2.ndf e:dataCarlosDB_2/ndf'...
but cleanly using a single T-SQL statement? properly formatting a single T-SQL query to use the xp_cmdshell system stored procedure.
Hello,
I need to move all log files for my SQL 2005 databases to another drive. I don't wish to shrink the files, I need to move the logs to another drive spindle. I did find an article (Article ID: 224071) that describes moving both the database and logs using sp_detach and then sp_attach. What is the best way just to move the logs to another drive on the same server, and that keeps the databases in their original location?
Thanks.
I have two databases on a Production Server that I want to Log Ship to a Test Server. According to the sys.master files the physical File Location is on an E drive. Early attempts at Log Shipping these two files error'd out due to space issues on the E drive (one Log Shipped and then one err'd out). I was subsequently informed from the server group that they would prefer that I Log Ship these two database files over to the M Drive where more space is available. In fact, they modified the Server Properties / Database Settings / Database Default Locations (for Data and Log) to the larger M drive (I'm not really sure why they just don't increase the E drive space but there is proabably a good reason).
Okay, so now my problems have been solved. Easy enough. Now I deleted the successful Log Shipped database and started from scratch. However, as before, one db restored and one failed (due to space issues). Apparently, both db are pointing towards the E drive. How is that possible?
So here I am with one successful database and the normal sys databases pointing to the E drive. What is the best way of approaching this move to the larger and preferred M drive?
We have a package that is using a ForEach loop container to access files on a network drive. For some reason I am getting a message that the ForEach enumerator is empty and did not find any files that matched the pattern. For the pattern I left the default *.* for testing purposes. I have specified the file folder as \remoteserverfilesharesubfolder and also as \remoteserverc$filesharesubfolder and have gotten the same message. However when I map a network drive and set the file folder to the network drive it finds the files. Is this a permissions issue?
After I finish processing the file I want to move it to a new directory. Once this is deployed in production, the package will not be running under a domain account and probably won't have access to the network folder. Is there any way to specifiy in the connection manager itself that it should use a specific account to access the folder?
TIA,
Sabrina
I haven't found the definitive answer on how or if this can be done without removing replication. I'm thinking ALTER DATABASE modify_file is the way to go. Anybody know if this will work or a better way to go about it?
Thanks
Hi,
View 12 Replies View RelatedThe MDF and LDF files are placed in SSD drive and tempdb files are placed in HDD drive. Snapshot isolation is enabled on the database. When a script is executed to insert data with NULL value to a table which has NOT NULL column, the transaction fails and then a log undo happens which fails and takes the database to suspect mode.
But when the MDF and LDF files are placed in HDD drive all this do not happen. The transaction just fails.
I have a database which has log file size 300 GB. As the drive is filling up i need to clear the space on the drive, for that i have to shrink the log file.
Unfortunately i dont have option to take backup of the database.And i am not able to shrink the file now. Is there any way to shrink the log file with out taking backup of it ?
What is the best way to fix issues with your log files in TempDB when you start to see them causing error msgs?
Thansk for your time.
I was in the process of migrating a server from one physical box to another. They are identical drive setups, same OS (2003), same SQL install (2005). Our server team did a 'PlateSpin', which copies the drives from one server to another, as long as the files are not in use. I did not reinstall SQL on the new box, i let the 'PlateSpin' tool copy everything over for me. I then stopped the SQL services on the old server and new server and copied over all of the system database (.mdf & .ldf) files. As soon as i started up the services on the new server, it looked great with one exception. The TempDB was only showing one datafile. When i queried sys.master_files, it was showing me 8 TempDB files. I tried restarting the services, but i still saw the same, only 1 file. I then tried to re-add TempDB files with the same name, but it would error saying they already existed. In turn, i could add new files with different names and they showed up fine. However, on a restart, they would not show up in the properties of the TempDB.
When i queried, sys.master_files again, i now had 16 Temp db files listed in the results. I deleted all but the original single file that was recognized out of the sys.master_files table and re-added the additional 7 files with he original names, restarted the service and then they all appeared.
Hi all, I have a tempdb that consists of 8 datafiles, tempdb_data_1 totempdb_data_8, each is 8GB. Now how can I drop 7 of them and leaveonly tempdb_data_1? Can this be done? Thanks a lot.
View 3 Replies View RelatedHello.
One of our hard drives has crashed and as a result we have lost our master mdf/ldf & user db mdf/ldf files. It's not that a critical system by any means, but if the hard drive crashes and the master mdf/ldf files & user db mdf/ldf are lost, is there any way of restoring the system?
I'm thinking we probably need to re-install Enterprise Manager completely, and re-install the user db from a backup.
Any advice/suggestions would be much appreciated.
Thanks in advance,
Is there a way to get access to the files on the shared drive from either of the nodes when the SQL Cluster is shut down?
It doesn't look like there is, but hoping there is some trick. I see how it can be done with a Windows Cluster but not a SQL Cluster.
I'm having an argument with our infrastructure architect who has just gone and bought lots of SSD drives to use for our tempdb data and log files, sounds great doesn't it? There is a catch though, his plan is to add the disks to the two available slots in each blade in a RAID0+1 configuration, effectively giving you one usable drive, and adding both data and log files on to one disk.
I then pointed out that SQL Server best practice is to host tempdb data and log files on two separate drive to reduce contention. The architect then basically said that because this isn't spinning disk the issue of drive, r/w contention isn't an issue I don't agree with this and wanted to get some opinions from the community, I'm still advising that two separate disks should be used but someone just went and spent £80k ($150k) on SSDs and doesn't want to back down...
It's been a long time since I've tried this, but I have a SQL Server that needs to be restored (including master) to a server whose drives and corresponding folders match the source server, with the exception of tempdb. When SQL Server initially starts I believe it will fail since it cannot find tempdb. I just don't recall if it fails to startup or if it starts up reporting errors and recreates tempdb in the same location as master. Does anyone recall the steps needed to point SQL Server to the new location of tempdb?
Dave
So we have new servers that are going to be installed with SQL 2012 and I'm debating the wisdom of splitting tempdb with multiple files.
I know it's a myth that performance automatically improves if you split it into a number of files based on processors, but I'm debating the wisdom of putting a file on each of my data / log file drives.
For instance, I have a server with a C: drive (OS), D: drive (Data for system DBs and install of programs - 458 GB), an F: drive for user DB data files (767 GB), and a J: drive for log files (255 GB).
Obviously no files are going on C:. I'm debating on whether or not we should even leave system DBs on the D: drive given in our current 2k8 servers, we end up with Memory.dmp files over flowing the D: drives as well as .cabs and other install / update files that tend to collect on that drive over the years.
But if we leave the system DBs on D:, I'm wondering if adding a second tempdb file to F: and a third to J: will improve query performance or not.
We had someone create an extra data file and log file for tempdb. Sowe currently have two data files and two log files. Is it possible todelete the newly created data and log files? If I just delete thephysical files, I assume they'll get created as soon as SQL Servergets started back up. Any help would be great, since a single dataand log file for tempdb is my goal.Thanks much.sean
View 3 Replies View RelatedHave a SQL2008R2 instance on a VM where the single .mdf for the tempDb database is located on a high contention disk. I've managed to get another 60GB disk and thought it would be a good time to move the .mdf and also increase it's size and number of files.
The server has 12 cores and after a bit of reading I've decided that it would be best just to have four files for this database as the 1 file per core (-1) seems to be disputed.
-- Move the existing file to the new disk and rename it.
ALTER DATABASE tempdb MODIFY FILE (NAME='tempdev', FILENAME='E:SQLData empdb0.mdf');
-- Change the size to 1GB
ALTER DATABASE tempdb MODIFY FILE (NAME='tempdev', SIZE= 1048576KB, FILEGROWTH=5%);
-- Add three new files, all with the same size & growth
ALTER DATABASE [tempdb] ADD FILE ( NAME = N'tempdev1', FILENAME = N'E:SQLData empdb1.mdf' , SIZE = 1048576KB , FILEGROWTH = 5%)
ALTER DATABASE [tempdb] ADD FILE ( NAME = N'tempdev2', FILENAME = N'E:SQLData empdb2.mdf' , SIZE = 1048576KB , FILEGROWTH = 5%)
ALTER DATABASE [tempdb] ADD FILE ( NAME = N'tempdev3', FILENAME = N'E:SQLData empdb3.mdf' , SIZE = 1048576KB , FILEGROWTH = 5%)
-- Now restart the instance.
Also, what are peoples thoughts on percentage growth for tempDb? I've read that it's not recommend and yet it seems to be the norm.