[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Can I move all system DBs to other drive?

Posted on 2011-05-09
7
Medium Priority
?
237 Views
Last Modified: 2012-08-14
This is using MS Windows 2008 server. Want to setup MS SQL 2008 on the D:\Program files. One more requirement is, can I move all the system DBs - Master, and others to the F:\ drive? If so, how?
0
Comment
Question by:Balack
  • 3
  • 3
7 Comments
 
LVL 9

Expert Comment

by:kaminda
ID: 35718265
You can use alter database to move datafiles to another location


This article has all the details
http://msdn.microsoft.com/en-us/library/ms345408(v=SQL.100).aspx
0
 

Author Comment

by:Balack
ID: 35718608
Hi Kaminda,

btw, How much disk space should be allocated for this F: partition, if only it is used for storing sql system DBs only?
0
 
LVL 9

Expert Comment

by:kaminda
ID: 35718755
For databases other than tempdb you dont need much of a space. But for tempdb general best practise is moving it in to a different drive with sufficient space. It is depending on your database operations. Tempdb is mainly used to store worktables and tempory tables, if your database is having lot of transacitonal operations you should allocate considerable space to your disk drive.

Also keep in mind all the data in tempdb gets cleared when you restart your SQL Service.

These are the other operations which used tempdb

any sorting that requires more memory than has been allocated to SQL Server will be forced to do its work in tempdb;
if the sorting requires more space than you have allocated to tempdb, one of the above errors will occur;
DBCC CheckDB('any database') will perform its work in tempdb -- on larger databases, this can consume quite a bit of space;
DBCC DBREINDEX or similar DBCC commands with 'Sort in tempdb' option set will also potentially fill up tempdb;
large resultsets involving unions, order by / group by, cartesian joins, outer joins, cursors, temp tables, table variables, and hashing can often require help from tempdb;
any transactions left uncommitted and not rolled back can leave objects orphaned in tempdb;
use of an ODBC DSN with the option 'create temporary stored procedures' set can leave objects there for the life of the connection.
0
NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

 

Author Comment

by:Balack
ID: 35718806
Any guidelines on how much space is this tempdb use? For example, if the production db can grow to 1TB, how much space is consider enough for tempdb?
0
 
LVL 9

Accepted Solution

by:
kaminda earned 1500 total points
ID: 35718925
It is pretty much hard to say something like that coz tempdb is for entire sql server not just for a single db. Also it does not depends on the database size but the amount of transactions happens on the db. You can use the perfomance counters get an idea about the load on your tempdb described in this msdn article

http://msdn.microsoft.com/en-us/library/ms176029.aspx
0
 
LVL 35

Expert Comment

by:David Todd
ID: 35726480
Hi,

And remember that some of the nice new cool features such as row versioning hammer tempdb.

Regards
  David
0
 

Author Closing Comment

by:Balack
ID: 35829480
Cool
0

Featured Post

Vote for the Most Valuable Expert

It’s time to recognize experts that go above and beyond with helpful solutions and engagement on site. Choose from the top experts in the Hall of Fame or on the right rail of your favorite topic page. Look for the blue “Nominate” button on their profile to vote.

Question has a verified solution.

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

In this article I will describe the Copy Database Wizard 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.
How to leverage one TLS certificate to encrypt Microsoft SQL traffic and Remote Desktop Services, versus creating multiple tickets for the same server.
This Micro Tutorial will teach you how to add a cinematic look to any film or video out there. There are very few simple steps that you will follow to do so. This will be demonstrated using Adobe Premiere Pro CS6.
Screencast - Getting to Know the Pipeline
Suggested Courses

873 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