Determ if SQL 2008 is fragmented and needs to be backed up


I would like to know if there is any sql statement that could be checked to determ if the sql 2008 database has not been backup for a while (not fragmented) and tell the user that its needed to do that to increase the logs and to be safe.

Who is Participating?

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

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.


Take a look at this link for information on finding the fragmentation of indexes.

Assuming you want to notify the user of some other program using this database, you can set some sort of threshold and warn them if the fragmentation levels come back too high when you run the check.  Just an idea.

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
Éric MoreauSenior .Net ConsultantCommented:
fragmentation and backup are 2 different beast.

you should always take backup for your security in case the server crashes on (at least) a daily basis.

defragmentation should be part of your daily/weekly/monthly maintenance plan depending on the level of activity of your database
cynkanAuthor Commented:
Hi emoreauu,

Of course they are and its exactly a weekly maintenance I want the user to do with the defrag. You are so right ;)

Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

cynkanAuthor Commented:
Hi jmcmunn,

Many thanks for the url, it gave me exactly the info I needed. Do you have any idea of the limit avg_fragmentation_in_percent can come up to before I inform the user that they need to defrag (in our software its the same as take a backup)?

Some of our users have small databases (5Mb), other big (200Mb) but not many take daily backups even if the backup guide show up automaticly after the daily report. Thats why I need to force a backup and defrag so that they have a backup and faster queries.

Well, if you are seeing too much fragmentation it might indicate another issue as well.  Have you profiled the DB to see if SQL would recommend new or different indexes?

I think we chose to check for fragmentation over 90 on roughly 10% of our tables.  It's totally up to you though.  I would make it something where you start seeing performance issues when it gets noticeably worse.  That will be different for each DB.
Anthony PerkinsCommented:
The percentages are highly subjective, however see here for MS recommendations:
Reorganizing and Rebuilding Indexes

Ola Hallengren uses MS recommened percentages by default in the very useful script to defrag indexes:
Anthony PerkinsCommented:
One thing that should be pointed out, is that depending on your Fill Factor, small tables will always show as fragmented and you should skip these when defraging.
cynkanAuthor Commented:

Your information has been absolute great, especially Ola Hallgrens article ;)

Many thanks for your assistance!

Rgds Cynkan
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.