A
few weeks ago I published the blog Honey I Shrunk the Database, and I mentioned
a way of shrinking your database.
The
method to be discussed during this blog was inspired by - PaulRandal – “Why You Should NotShrink Your Data Files”
Let’s
begin and call it steps by which you can shrink your data files:
1. Create a new filegroup
a. Right click on the database you want to add the
new file group to.
b. In the select page option, select the option
files.
c. Click the add button and a new file group will be
added.
d. Click on the file group textbox that will give
you two options, select the option: <new filegroup>
After
completion of these steps your display should look like this….
2. 2. Give your new file group a Logical name and also
select which file group it needs to be a part of.
3. Then hit the select button.
The
easy part is done, now the process of moving everything from the primary to the
new filegroup can begin.
For
the table moving script:
Moving
all the tables and their dependencies to the new file group, I consulted a
developers best friend “Google” to find a piece of code that I can use to move
everything in the tables and Google led me to the following blog:
SQL
Server: Move Tables between Filegroups
All
the credit to the developer who created this script and thank you. Click on the
link, read the comments and all information about the script.
To
make the script work for me, I changed it up a bit, adding in extra code so
that it can loop through my database and move everything. With all the
extra code I have added it failed when I executed it, so I am not going to post
my broken script, I would rather let you use the link above and play around
with the script by yourself.
The
creator of the script also gives you a table of platforms on which the script works.
4. After you run the above script or piece of code
or any code that you want to use. If you have a better way or alternative way
of doing it please share it with us!
Drop the old filegroup that you were going to
shrink anyway (or shrink it way down if it’s the primary filegroup)
No comments:
Post a Comment