DEFRAG FAILURE - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

Data Corruption Issues

DEFRAG FAILURE

Author

Topic

kbk
Starting Member

34 Posts

Posted - 2009-02-12 : 03:00:24

WHAT ARE THE IMPACTS OF A DEFRAG FAILURE ON DATABASE???

bk

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-02-12 : 03:05:08

Which defrag?
Do you mean SHRINK file, or physically defrag database file with an external tool?

E 12°55'05.63"
N 56°04'39.26"

kbk
Starting Member

34 Posts

Posted - 2009-02-12 : 03:34:37

We created a job that runs this query on all the tables of the database, for defragmentation

ALTER INDEX ALL ON TableName
REORGANIZE
..
I want to know the worst impacts if the defrag activity fails.

bk

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts

Posted - 2009-02-12 : 05:26:18

Your indexes stay fragmented.

--
Gail Shaw
SQL Server MVP

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2009-02-12 : 07:25:36

quote:
Originally posted by kbk

We created a job that runs this query on all the tables of the database, for defragmentation

ALTER INDEX ALL ON TableName
REORGANIZE
..
I want to know the worst impacts if the defrag activity fails.

bk

It can take longer time than rebuild index for highly fragmented huge tables.You should check fragmentation level and determine whether you need rebuild or Reorganize.

kbk
Starting Member

34 Posts

Posted - 2009-02-12 : 07:53:39

But i want to know the impacts in the case of failure of the defrag job.Any impacts on size or any other factors????

bk

paulrandal
Yak with Vast SQL Skills

899 Posts

Posted - 2009-02-12 : 17:03:12

No - it just doesn't defrag the indexes. Maybe you're getting confused with using ALTER INDEX ... REBUILD, which may cause data file growth if there isn't enough free space in the database.

But either of these failing will not make the indexes any worse.

Paul S. Randal, Managing Director, SQLskills.com (www.SQLskills.com/blogs/paul)
SQL Server MVP, Contributing Editor of TechNet Magazine
Author of SQL 2005 DBCC CHECKDB/repair code
Author & Instructor of Microsoft Certified Master - Database course

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources