SQL 2008 DB and 85% UnAllocated Space

I am not a DBA and really only know enough to be dangerous. I have a SQL 2008 DB that is 160GB in size yet when I go to shrink database it list 85% unallocated space. Mind you I did not shrink the DB.

Now I know there a hundreds of items that affect DB performance but can to much unallocated space cause poor DB performance. I know when you shrink a DB is causing fragmentation and us need to reindex the DB...

thoughts
LVL 21
compdigit44Asked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Deepak ChauhanSQL Server DBACommented:
Yes it correct, we should not shrink the database and files it leads to index fragmentation badly. You should avoid this until it is necessary or any disk space issue. It will grow again whenever require and so far i know no impact on database performance if free space there.
Vitor MontalvãoMSSQL Senior EngineerCommented:
If the disk space isn't an issue for you then let the data file as is. If you really need to get some extra disk space then add the shrink step just before the scheduled reindex job and don't forget to remove it after the job is completed.
compdigit44Author Commented:
Space is not an issue.... I am more concerned if the excess white space can add to performace problem.. IN my mind it would make sense that it does... more area of the DB to scan during a query... then again I am not a DBA
Acronis True Image 2019 just released!

Create a reliable backup. Make sure you always have dependable copies of your data so you can restore your entire system or individual files.

Vitor MontalvãoMSSQL Senior EngineerCommented:
I am more concerned if the excess white space can add to performace problem
Not really if you have regular reindex job so all datapages will be sequentially organized.
See the datafile as a virtual disk. Would you have performance issues in a disk if it has plenty of empty space?
compdigit44Author Commented:
I am doing a reorg not a rebuild of indexes nighltly
Deepak ChauhanSQL Server DBACommented:
If you are doing index mentinance then unallocated space would not create any issue.
Deepak ChauhanSQL Server DBACommented:
This is depend on fragmentation level. Generelly rebuild the index if fragmentation level >=30 and reorg if < 30
compdigit44Author Commented:
Very interesting so you can have a DB with 85% whitespace (free space) and have it not affect performance correct it all comes down to the fragmentation level correct

can a reindex and reorg be done online on a SQL 2008 server?
Vitor MontalvãoMSSQL Senior EngineerCommented:
can a reindex and reorg be done online on a SQL 2008 server?
If it's an Enterprise Edition, yes you can. Express and Standard Edition only allows offline operations.
compdigit44Author Commented:
I have tried a couple of different query online to line the fragmentation level of the DB an it comes back after a couple of times with number of rows affect that's it. When I try the same query against another DB is works perfectly..

My plan would be to shrink the DB. to reclaim 85% of the whitepace then to a index rebuild..

Target DB if SQL 2008 Standard/ build 11.0.3128
Deepak ChauhanSQL Server DBACommented:
Ok. You can reclaim the space but please do it in non- Business hours because you have SQL server Standard edition and it does not support online reindexing. Rebuild index will make this table inaccessible to users during rebuild.
compdigit44Author Commented:
So I would have to place it in single user or recovery mode then run the maintenance task..

Why can I not run a query to see the current fragmentation level..
Deepak ChauhanSQL Server DBACommented:
You can check and you should check before rebuild or reorganizing.

No need to place it in Single user mode and no need to change the recovery mode.
This will be a normal operation but in off working hours are preferable. Your actual database size is ~25GB excluding unallocated space. Database will be accessible to users.
compdigit44Author Commented:
thanks any idea why I cannot lsit the fragmentation level of the tables.. when I run the same query on other servers it works correctly but not on this one
Deepak ChauhanSQL Server DBACommented:
Can you share your query and what difference you found between result.
compdigit44Author Commented:
See the attached file...which I found online
IndexFragmentation.sql
Deepak ChauhanSQL Server DBACommented:
@when I run the same query on other servers it works correctly but not on this one

So what is the wearied behaviour of this query? You have given the value >30 it means if avg. fragmentation level is > 30 row will display otherwise not.
compdigit44Author Commented:
since I am not getting any results except rolls affected = 1140 maybe my fragmentation is less than 30.. which I find surprising
Vitor MontalvãoMSSQL Senior EngineerCommented:
since I am not getting any results except rolls affected = 1140 maybe my fragmentation is less than 30.. which I find surprising
If there's a regular reindex job scheduled then is not surprise at all. Or if the database doesn't have any index either.
Change the value of 30 to 5 and check how many rows returns.
compdigit44Author Commented:
I am doing a daily reorg but not a reindex... this is probably why...
Vitor MontalvãoMSSQL Senior EngineerCommented:
If you don't have any index fragmentation level > 30% then a reorg should be enough.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2008

From novice to tech pro — start learning today.