May 8, 2009 at 6:22 am
My database server (Sql server2005)is having disk space problem and it contains some data which is not very important but it is huge in terms of size.The server have some extra space on D: drive .
To solve space issues, a job needs to be created that will move unused data from the data drives to D:
Please any body tell me how to move data using job to another drive (D: drive in my case )
May 8, 2009 at 6:40 am
Such operation will be noticeble for ongoing operations !
Can you afford downtime ?
If yes,
Can you move the whole db file, or do you want just to move some tables/indexes to a new filegroup on a new disk ?
If you can move the whole db file, it is best to go for the alter database path.
(This will create downtime at db level !)
Select *
from YourDatabase.sys.sysfiles
/* Move/Rename database files */
-- SQL2005
alter database YourDatabase
MODIFY FILE ( NAME = logicalfilename, FILENAME = 'x:\MSSQL.1\MSSQL\DATA\YourDatabase.MDF' ) -- specify new location
;
/* put the db offline -- start of downtime !! */
alter database YourDatabase set offline;
go
print 'NOW MOVE/RENAME THE ACTUAL FILES !!!!'
/*
-- after the move has been completed
alter database YourDatabase set online;
*/
The advantages of doing it this way, is that you don't use any auth, ... settings at sqlserver instance level.
(if you would use detach / attach, you might lose e.g. user login database,..)
If you only intend to move some objects to a new filegroup, just add the new filegroup and a file for that filegroup on the new location.
Then move your objects.
This will create downtime at object level !
e.g.
Create index x...
With ( DROP_EXISTING = ON )
on NewFileGroup
;
Check Bol topic "Create index" subtopic "DROP_EXISTING Clause"
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
May 8, 2009 at 6:59 am
Hi ALZDBA,
Thanks for your reply..Can you please tell me how to create job when I just
want to move few Tables/Views to another location...Not whole db...
Please help...
May 8, 2009 at 7:12 am
To move just tables in the same db, you need to create a new filegroup on that new drive. Then rebuild the clustered index on that filegroup, and the data will move.
May 8, 2009 at 7:36 am
Have a look at :
- "Moving Large Table to Different File Group" http://www.sqlservercentral.com/articles/Administration/64275/
- "Moving Tables and Indexes to Filegroups En Masse " http://www.sqlservercentral.com/articles/Files+and+Filegroups/65538/
- "Move a table between filegroups" http://www.sqlservercentral.com/scripts/Miscellaneous/31526/
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
May 13, 2009 at 6:12 am
Thanks
Viewing 6 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply