Disk fragmentation after backup

I got heavily disk fragmented after a full database backup. The disks
configured in RAID 10 and used to store just 1 full backup file daily. I
used the option "with init" on the backup statement. If you have experience
this or know the solution to this issue, please help. Thanks!
Relax. Is it really affecting performance? How do you know?
"KTN" <KTN@.discussions.microsoft.com> wrote in message
news:6A5A83B1-1BE6-418E-956F-1E25608E9B62@.microsoft.com...
>I got heavily disk fragmented after a full database backup. The disks
> configured in RAID 10 and used to store just 1 full backup file daily. I
> used the option "with init" on the backup statement. If you have
> experience
> this or know the solution to this issue, please help. Thanks!
>
|||Try deleting the file once just before you do the full backup. If the disk
does not have other files on it then the new file should be contiguous.
Andrew J. Kelly SQL MVP
"KTN" <KTN@.discussions.microsoft.com> wrote in message
news:6A5A83B1-1BE6-418E-956F-1E25608E9B62@.microsoft.com...
>I got heavily disk fragmented after a full database backup. The disks
> configured in RAID 10 and used to store just 1 full backup file daily. I
> used the option "with init" on the backup statement. If you have
> experience
> this or know the solution to this issue, please help. Thanks!
>

Disk fragmentation after backup

I got heavily disk fragmented after a full database backup. The disks
configured in RAID 10 and used to store just 1 full backup file daily. I
used the option "with init" on the backup statement. If you have experience
this or know the solution to this issue, please help. Thanks!Relax. Is it really affecting performance? How do you know?
"KTN" <KTN@.discussions.microsoft.com> wrote in message
news:6A5A83B1-1BE6-418E-956F-1E25608E9B62@.microsoft.com...
>I got heavily disk fragmented after a full database backup. The disks
> configured in RAID 10 and used to store just 1 full backup file daily. I
> used the option "with init" on the backup statement. If you have
> experience
> this or know the solution to this issue, please help. Thanks!
>|||Try deleting the file once just before you do the full backup. If the disk
does not have other files on it then the new file should be contiguous.
--
Andrew J. Kelly SQL MVP
"KTN" <KTN@.discussions.microsoft.com> wrote in message
news:6A5A83B1-1BE6-418E-956F-1E25608E9B62@.microsoft.com...
>I got heavily disk fragmented after a full database backup. The disks
> configured in RAID 10 and used to store just 1 full backup file daily. I
> used the option "with init" on the backup statement. If you have
> experience
> this or know the solution to this issue, please help. Thanks!
>

Disk fragmentation after backup

I got heavily disk fragmented after a full database backup. The disks
configured in RAID 10 and used to store just 1 full backup file daily. I
used the option "with init" on the backup statement. If you have experience
this or know the solution to this issue, please help. Thanks!Relax. Is it really affecting performance? How do you know?
"KTN" <KTN@.discussions.microsoft.com> wrote in message
news:6A5A83B1-1BE6-418E-956F-1E25608E9B62@.microsoft.com...
>I got heavily disk fragmented after a full database backup. The disks
> configured in RAID 10 and used to store just 1 full backup file daily. I
> used the option "with init" on the backup statement. If you have
> experience
> this or know the solution to this issue, please help. Thanks!
>|||Try deleting the file once just before you do the full backup. If the disk
does not have other files on it then the new file should be contiguous.
Andrew J. Kelly SQL MVP
"KTN" <KTN@.discussions.microsoft.com> wrote in message
news:6A5A83B1-1BE6-418E-956F-1E25608E9B62@.microsoft.com...
>I got heavily disk fragmented after a full database backup. The disks
> configured in RAID 10 and used to store just 1 full backup file daily. I
> used the option "with init" on the backup statement. If you have
> experience
> this or know the solution to this issue, please help. Thanks!
>

disk fragmentation

I have a Dell PowerEdge 2850 with Windows 2003 server. 2 disk drives with RAID. My databases are fragmented on the drive and I need to defrag the drive at least once a week. My database files are fragmented. Defragger cleans up the files but as soon as I start running processes again, the files fragment and system response time suffers.

What can I do to keep the files from fragmenting?

calculate the database growth

and set the file increment in the db option

bigger than that. i meann just a little bigger

|||

Question : does file fragmentation really impact the performance of a DB ?

I mean : by construction, the data in files are fragmented, and access to data in DB always require a lot of disk seek. Sequential access through files should be marginal. So, fragmentation of the whole file should not imply a big performance impact.

Am I correct ?

|||

How are you measuring the fragmentation?

There are two types of fragmentation here:

1) The normal filesystem fragmentation of the database files. This should have minimal to nil impact unless you do a lot of table-scans. On the other hand, you shouldn't be getting this sort of fragmentation unless you are constantly growing your database. Far better to size it for growth initially than to allow it to grow incrementally.

2) Internal fragmentation of the information within the database files (as measured by commands like DBCC SHOWCONTIG). THis has nothing to do with the physical files being fragmented on disk, and is resolved by using SQL techniques such as DBCC INDEXDEFRAG or ALTER INDEX REBUILD.

Disk defragmentation on SQLserver

SQLserver 200 SP3 running on Win2000
I check disk D: where all the databases reside and found that it is heavily
fragmented. The list of most fragmented files include some important
database (MDF) files.
I would like to defragment the disk but also concerned about possible
problem with the databases in question since they are up and running during
the defragmentation process.
What's a safe procedure to perform defragmentation on the the disk and how
to reduce fragment in the furture?
Any suggestion is greatly appreciated.
BillFirst off always make sure you have FULL and tested backups before
attempting something like that. While some vendors state they can defrag
the sql files while running I personally would not trust my data to it. If
you want to prevent fragmentation in the first place you should always start
with a clean disk and each time you add a new database you should do the
following:
One at a time:
1. Create the database with the files at say 10MB in size. (this is so you
can always shrink it down later if need be)
2. Immediately after creating the database, alter the database and grow
each file to the size you think you will need for the next year or two
allowing plenty of room for free space in each file.
If you do that for each database as you create them the files will be
contiguous on disk and there will be no need to defrag them in the future
unless you srink or grow them again.
--
Andrew J. Kelly SQL MVP
"Bill Nguyen" <billn_nospam_please@.jaco.com> wrote in message
news:%2368scSPcEHA.2520@.TK2MSFTNGP12.phx.gbl...
> SQLserver 200 SP3 running on Win2000
> I check disk D: where all the databases reside and found that it is
heavily
> fragmented. The list of most fragmented files include some important
> database (MDF) files.
> I would like to defragment the disk but also concerned about possible
> problem with the databases in question since they are up and running
during
> the defragmentation process.
> What's a safe procedure to perform defragmentation on the the disk and how
> to reduce fragment in the furture?
> Any suggestion is greatly appreciated.
> Bill
>