SQL - Want to split MDF/LDF

[lonewolf6465]

Hi. I have 8 databases (each contains an LDF/MDF) and they are starting to grow rather large. I understand there is a way in SQL to take one MDF and turn it into chunks.

Ex:

database.mdf (orginal) want to turn into

database.part1
database.part2 etc.

Does the LDF become a part as well?

I do not want to seperate them off of a the same drive, there is no raid setup. The system is 8 databases sitting on a drive, they're just becoming too large (I'm talking 40+ gig for some) so I am hoping to turn into smaller parts, say four 10 GIG parts for one database. I've been looking around google with no such luck, I imagine I'm using the wrong key words to search. I can only imagine the alter database command is used, I'm on SQL 2000 PE, but have envirorments of SQL2000/2005 standard to work with.

Thanks.

 

[KB]

You are referring to Files within FileGroups.
http://msdn.microsoft.com/en-us/library/ms179316.aspx

Research this strategy as much as possible before you start using it as it will increase backup complexity and will affect performance. Unless you put the new FileGroup on a new disk set you aren't going to see an improvement in performance, but you could see a small decrease if the filegroup is on the same diskset as the Primary filegroup.

IMO 40 GB isn't large for a database. Why do you want smaller files? Are you trying to improve backup performance? What you might do is instead of a full backup each night, do a full backup once a week, differential each night and transaction logs every hour.

http://www.microsoft.com/techn...lbackuprest.mspx#EDHAG

 

[lonewolf6465]

Thanks for the links, KB. We're trying to improve performance across the board to be honest. Not only in backup time but in our applications accessing of DBs.