Can I move all system DBs to other drive?

Posted on 2011-05-09
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?
Question by:Balack
    LVL 9

    Expert Comment

    You can use alter database to move datafiles to another location

    This article has all the details

    Author Comment

    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?
    LVL 9

    Expert Comment

    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.

    Author Comment

    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?
    LVL 9

    Accepted Solution

    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
    LVL 35

    Expert Comment

    by:David Todd

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


    Author Closing Comment


    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Threat Intelligence Starter Resources

    Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

    Suggested Solutions

    Title # Comments Views Activity
    Getting the 3 middle digits 4 25
    SQL Server 2014 network-related error 6 21
    Stored procedure 4 21
    What does this mean? 6 24
    This is basically a blog post I wrote recently. I've found that SARGability is poorly understood, and since many people don't read blogs, I figured I'd post it here as an article. SARGable is an adjective in SQL that means that an item can be fou…
    Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
    Need more eyes on your posted question? Go ahead and follow the quick steps in this video to learn how to Request Attention to your question. *Log into your Experts Exchange account *Find the question you want to Request Attention for *Go to the e…
    Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

    758 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

    11 Experts available now in Live!

    Get 1:1 Help Now