Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
chris_wood99
Yak Posting Veteran
70 Posts |
 Posted - 2005-07-05 : 07:57:13
|
I have recently inherited a system and currently working through all the areas that look to be in a mess one at a time and with the help of many people on these forums, i'm starting to make some progress so many thanks to all contributors!  I am currently looking at a Database that is approx 18gb in size, but it has 5 datafiles (all with the .mdf extension for some reason) which all belong to different filegroups. Unfortunately they are all sitting on the same e: drive. not only that, they are accompanied by the log file, tempdb and the backups! The server is running on 2003 advanced server, Raid 5 on the E: 70gb (30gb free) drive and a smaller F: drive 10gb (7gb free), raid 5.I have suggested a new drive be added ASAP to store the local backups on (we have others to tape) and also the transaction log & tempdb files and set this drive to be RAID 1 as the logs and tempdb should be taking alot of writes to disk. Is this a decent or best solution??I was also wondering if there was any real benefit of having 5 datafiles & filegroups for the database if they all sit on the same physical disk? |
|
|
Thrasymachus
Constraint Violating Yak Guru
483 Posts |
Posted - 2005-07-05 : 12:53:08
|
| I would keep your transaction logs and backups on seperate drives. Could the seperate filegroups be in place for backup purposes?The file extensions of data files really have no meaning but it is good practice for your secondary data files to have the ndf extension.Sean RoussyPlease backup all of your databases including master, msdb and model on a regular basis. I am tired of telling people they are screwed. The job you save may be your own.I am available for consulting work. Just email me though the forum. |
 |
|
|
chris_wood99
Yak Posting Veteran
70 Posts |
Posted - 2005-07-06 : 04:42:30
|
| a full standard nightly backup of the whole db is completed so i'm pretty sure that the filegroup structure is not setup for this consideration. The only thing i can imagine is that the 3rd Party software vendor has specified this filegroup setup, i will check with them.If they have not specified this setup. What Is the preffered method for relocating Datafiles to another physical drive? |
|
|
|
Bex
Aged Yak Warrior
580 Posts |
Posted - 2005-07-06 : 07:10:16
|
| Hi ChrisI hope you don't mind me piggy-backing this post, but you are asking a similar question to me. I am very new to DB Administration (which is probably evident by my posts) and we have an issue of room (or lack of) on our database server. As with you Chris, everything is located on the same drive partition; the D: drive. (our server is housing both SQL Server and MySql - not the best solution!)Another partition seems to have ample room, so I want to move some of the files to this location. What do I have to do and what repercussions do I need to consider?? A link to any information on this would be much appreciated! Thanks (and sorry again Chris for interjecting-(is that even a word?!?!))Hearty head pats |
|
|
|
chris_wood99
Yak Posting Veteran
70 Posts |
Posted - 2005-07-06 : 11:40:26
|
| I'd have to get some new hardware to store the local backups on another disk but i've suggested it. On the immeadiate front i've discovered that they are not really bothered about the transaction log cos the db is in simple recovery model (I KNOW I WILL HAVE TO HAVE A CHAT PRONTO ABOUT WHY THIS IS!) but Its a good idea to be moving the tempdb though right? if i move it from the raid 5 to a new raid 1 drive will i see significant performance improvements, performance is their main complaint?my tempdb is also at 1.5 gb and has 1.4gb spare, i'm aware of the implications of having too small tempdb or increment but what about a large one? i've set a scheduled job to shrink it down to leave 15% but was just wondering if large tempdbs would cause any probs |
|
|
|
|
|
|
|
|
Sensible Location of datafiles & use of Filegroups