Indexes, MDF, Reclaiming indexed space
-
I am working on cleaning up old and unused indexes in a database (that I did not create). One table in particular that I choose to look at has about 1 million records with a primary key and several varchar fields. Two of the fields have nonclustered indexes placed on them which are not necessary. I deleted the indexes (via EM) and saved the table.
I was assuming that the mdf size would shrink due to the index being deleted and how large the table was. It actually stayed the same.
I know that reindexing will grow a file size (especially if it hasn't been done in a long time) but what I need to know is how to reclaim the space that the useless indexes are taking up after I remove them.....
Thanks,
Chris
-
use dbcc shrinkdatabase('dbname') out of business hours
-
like andrewkane17 specified, shrinkfile or shrinkdatabase will solve your problem.
You can also perform an alter database set AUTO_SHRINK on, but that is not recommended for production databases !
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
-
Why do you want to shrink the file? Such a file needs to grow again when necessary. And that is a somewhat expensive operation which leads to file fragmentation and might degrade performance. I would leave the file size as it is.
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url] -
This is more of an exercise for me to see how much impact unneccessary indexing has on the larger tables....not so much what I will be practicing in the production environment. I have to learn every inch of this before I fully take control because this particular project is about a thousand times larger than what currently exists.
Not to get too far off the subject, but do you have any good links on best practices for login authentication to SQL Server?
-
I bet you'll find something useful here in the article section. I would look for articles by Brian Kelley. In addition, see if you find http://www.sqlsecurity.com useful in security in general. As for authentication I can offer you these links:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/adminsql/ad_security_47u6.asp
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnnetsec/html/SecNetch13.asp
http://support.microsoft.com/default.aspx?scid=kb;EN-US;842861
May I say that it's better for such follow-up questions to start a new thread?
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply