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?
jmcmunnConnect With a Mentor Commented:

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.
É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 ;)

Improve Your Query Performance Tuning

In this FREE six-day email course, you'll learn from Janis Griffin, Database Performance Evangelist. She'll teach 12 steps that you can use to optimize your queries as much as possible and see measurable results in your work. Get started today!

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 PerkinsConnect With a Mentor Commented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.