asked on

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

Deepak Chauhan

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ão

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.

compdigit44

ASKER

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

Vitor Montalvão

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?

compdigit44

ASKER

I am doing a reorg not a rebuild of indexes nighltly

Deepak Chauhan

If you are doing index mentinance then unallocated space would not create any issue.

Deepak Chauhan

This is depend on fragmentation level. Generelly rebuild the index if fragmentation level >=30 and reorg if < 30

compdigit44

ASKER

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ão

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.

compdigit44

ASKER

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 Chauhan

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.

compdigit44

ASKER

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 Chauhan

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.

compdigit44

ASKER

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 Chauhan

Can you share your query and what difference you found between result.

compdigit44

ASKER

See the attached file...which I found online
IndexFragmentation.sql

Deepak Chauhan

@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.

compdigit44

ASKER

since I am not getting any results except rolls affected = 1140 maybe my fragmentation is less than 30.. which I find surprising

Vitor Montalvão

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.

compdigit44

ASKER

I am doing a daily reorg but not a reindex... this is probably why...

ASKER CERTIFIED SOLUTION

Vitor Montalvão

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial