TempDb - Moving / Adding Files And Resizing

Oct 23, 2015

Have 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.

View 4 Replies


ADVERTISEMENT

DB Design :: Resizing LDF And MDF Files Of Server Databases

Jul 9, 2015

I want to control the size of ldf files and mdf files of several databases on SQL Server 2008 in my organization (manual increase), but i have a question:

What would be the best practices (best methods) for provisioning a ldf file and mdf file?  Exists any generic formula?

With this i want to avoid the shrink operation and the autogrow of sql server databases...

View 6 Replies View Related

SQL Server Admin 2014 :: Separate Data Files / Log Files / TempDB / Backups

Jan 9, 2015

I 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 Related

Moving Tempdb

Mar 20, 2002

Can someone tell me how I can move the temp database. I know it get's recreated on startup so there must be somewhere that it's referencing.. Is it the model database it uses?

Thanks!

View 2 Replies View Related

Moving Tempdb

Jun 18, 2002

Hi All,
I need to move the tempdb onto a different drive. Can you give me the sql statement to do so.
Thanks
Paul

View 1 Replies View Related

Moving Tempdb

Oct 17, 2001

Can someone tell me how to move my tempdb to a different drive?

Thanks,
Dianne

View 4 Replies View Related

Moving The Location Of Tempdb

Feb 20, 2002

How do I move the location of the tempdb. I wish to place it on a separated
disk to to increase performance.
Parg

View 3 Replies View Related

Moving Tempdb Database .

Aug 14, 2002

Hello ,

I want to move my tempdb database to another drive by moving the mdf and ldf files . Is it possible to shift the tempdb once it is created ?
I am shifting the database since there is no space on the current drive and the database might grow in the near furure .

Any ideas ???

Thanks .

View 1 Replies View Related

Moving TEMPDB Issues

Aug 22, 2002

I want to move my TEMPDB from C:MSSQL7DATA to another (less critical) drive in the event it grows too much/fast. I've reviewed the process from MS using ALTER DATABASE, etc., but wanted some feedback from the real world before I commit. Are there any issues I should be aware of? I know it must reside on a local drive on the server. Do I need to make any other adjustments in SQL after the move? Your help is much appreciated. - Rob

View 1 Replies View Related

Moving Second Tempdb Device?

Apr 24, 2000

Hello:

We are supporting an mssql 6.5, sp 4 application. My associate recently
increased tempdb from 3 gb to 15 gb by adding a 12gb device on a second drive.

The original tempdb device was 3 gb on an h drive and the 12gb addition was added to the f drive limiting the disk space for the data dat file to be expanded. So now I would like to move that second tempdb device to a different drive to free up disk space on the f drive.

1) Can I move the second tempdb device?

2) Or should I try to shrink it and then expand it on a differetn drive?

3) Any other suggestions?


Any information you can provide will be greatly appreciated.

THanks.

David

View 1 Replies View Related

Moving TEMPDB To RAM - Problems

Nov 10, 1999

Hi,

My tempdb size on the hard disk at the moment is 1GB.

We have recently put in an additional 1GB of RAM to add to our existing 1GB RAM on the SQL server m/c, making it 2GB now.

SQL server is configured to use 928MB of memory. So, I put the TEMPDB (1GB) on the spare (1GB) memory in RAM.

The problem is, it doesn't allow me this saying not enough memory available.

I have also set the WORK SET SIZE parm to 1, to reserve memory for SQL. Still, it will not allow me. The maximum value it allows me for TEMPDB in RAM is 384MB, beyond which the server doesn't start.

What could be the reason for not getting enough memory, though the 1GB of RAM was put in additionally just for this.

Please advise.

Thanks
Satish

View 1 Replies View Related

Tempdb And Templog - Moving

Jul 23, 2005

Right now both of these reside on my C:, but templog is over 3 1/2 GB.1) What do I need to do to move both of these to another drive on thesame system?2) Is there an easy way to purge or compact templog, or just set a sizelimit for it?Thanks,Scott

View 1 Replies View Related

Moving Model/Tempdb/MSDB

Mar 13, 2001

I have a situation where I would like to move the physical location of the Model, tempdb and MSDB database files. Does anyone know of a way to do this?

View 2 Replies View Related

Moving Tempdb Datafile & Logfile

Jul 16, 2001

Is it possible to move (detach and attach) the physical data and log file (.mdf &.ldf) to another location?

View 1 Replies View Related

Moving Tempdb Onto A Faster Drive

Feb 27, 2006

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

View 3 Replies View Related

Moving Tempdb To Free Up Disk Space

Nov 29, 2000

does anyone know if tempdb can be physically moved to a different partition on a disk drive on SQL Server 7.0? Since it can't be backed up I'm hesitant to use the sp_detach/sp_attach procedure because I don't want to crash it. If nothing else is available, I can attempt moving it this way at the end of the day and then just reboot to get tempdb back up again if the server fails, but I'd really appreciate a suggestion from someone who has more know-how than I do about system table operations. Thanks again

View 1 Replies View Related

Benefit Of Moving Master && TempDB To Diff HD

Jul 20, 2005

I am interested to hear if people think it would be a good idea to movethe Master & TempDB to a different HD.Here is my DB Server's set up:1. Processor: (1) AMD XP 28002. 1st HD (IDE 0) is the system & boot drive3. (3) SCSI HD make up a hardware RAID level 0 (striped withoutparity)solution - these striped drives are just for my working DBs4. (1) SCSI HD that's not doing anything.I want to put the Master & TempDB on the SCSI HD that's not doinganything. Would that be the best place for it for maximum performance orshould I put in the striped array. I am leaning more towards putting onthe SCSI HD that's not doing anything. What do you all think?Ed*** Sent via Developersdex http://www.developersdex.com ***Don't just participate in USENET...get rewarded for it!

View 1 Replies View Related

SQL 2012 :: Moving TempDB To Local Non-clustered Drive

Sep 11, 2014

We 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.

View 4 Replies View Related

Adding Space To Tempdb

Oct 18, 1999

Hi,

I'm trying to add space to tempdb. I created a new device for 100megs and then I double clicked on tempdb db and then clicked on 'Expand'. I selected this new device in the data device - pull down and then did a 'Expand now'. After it successfully completed I went back and did a recalculate to find that the log space on tempdb has been increased and not the data space. I tried this twice with the same results.

Could anyone please tell me what's happening and how I can increase the data space on tempdb.

Thanks in advance,
Haranath

View 1 Replies View Related

TempDB Log Files Are Filling Up.

Dec 28, 2006

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.

View 2 Replies View Related

TempDB Files Gone Missing

Nov 25, 2013

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.

View 2 Replies View Related

How To Drop One Of The Tempdb Files

Nov 23, 2005

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 Related

SQL 2012 :: TempDB Data And Log Files On One SSD?

Mar 3, 2015

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...

View 4 Replies View Related

TempDB Files On Root Of Drive

Sep 15, 2015

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 Replies View Related

How To Start SQL Server When Tempdb Files Were Relocated?

Nov 22, 2005

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

View 1 Replies View Related

SQL 2012 :: Splitting TempDB With Multiple Files?

May 21, 2015

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.

View 9 Replies View Related

Deleting Extra Tempdb Log And Data Files

Jul 20, 2005

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 Related

Setup And Upgrade :: Number Of TempDB Files

Aug 24, 2015

My server has two 8 core processor. Should I use 8 or 16 data files for Tempdb?

View 4 Replies View Related

Moving .MDF Files

Mar 4, 2008

Hi,
I'm using SQL 2005 express edition and my database files are in the MSSQL.1MSSQLData folder. When I'm trying to copy them I get "Files already in Use" error. How do I stop the server from running (services or command line). Or is there a better why to export
them to a diffrent location.

Thanks

View 3 Replies View Related

Moving Log Files

Jul 23, 2005

If I use attach/detach to move my log file to a different drive, willit break any of my permissions for the sql logins. If so, how can I fixthis.This all stemmed from needing to setup log shipping to another serverfor redundancy. Step 1 says to create a share where the log filesreside. Well the sql server was installed by a previous employee andthe log files are in the same directory as the data files. I would feelmore comfortable (security wise) if I only shared out the directorywhere the log files lived.If anyone thinks that I am traveling down the wrong path or has anysuggestions, please let me know.Thanks

View 3 Replies View Related

Moving Files

Apr 1, 2008

How can i move files from one folder to other using script task

pls specify if its clear n can i ask this query in this forum

View 3 Replies View Related

Moving Mdf Files To A SAN

May 12, 2008



Hi, I'm fairly new to SQL, so I hope someone can help.

I'm trying to move our SQl data files to a new drive on a SAN. The MDF files are currently located in the E;mssql directory.

I want to keep the same drive letter, so I am attempting to do the following

1: stop SQL
2: xcopy data and ntfs permissions from E: to G:
3: rename e: to h:
4: rename g: to e:
5: reboot Server

when I do this, I get the following errors and SQl fails to start

udopen: Operating system error 3(The system cannot find the path specified.) during the creation/opening of physical device e:datamssqlDATAmodel.mdf.

FCB:pen failed: Could not open device e:datamssqlDATAmodel.mdf for virtual device number (VDN) 1.

Device activation error. The physical file name 'e:datamssqlDATAmodel.mdf' may be incorrect.

is anyone able to tell me what I'm doing wrong?

thanks!

View 7 Replies View Related

Moving Files

May 11, 2007

Hi,



I need to move backup files from the production server to another server. This would be regularly scheduled file move only for security reasons. The target server does not have SQL Server installed. Which is the best way to do this?



Thanks

View 1 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved