Defragmenting Local Disk

Is there any performance gain from defragmenting the SQL
SERVER data and log file disk subsystem (or OS). I have
run the "Analyze" part of the windows disk defragmenter
and several of the data and log files have several
fragments within the file. Some of them have 20
fragments. Is there another tool that is preferred? Any
advice or past experienced is much appreciated.
Thanks,
Andy20 fragments is not much compared to the fragmentation you probably have int
ernally in the files. But sure,
stop the SQL Server service and do a defrag if you wish - no harm.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
"Andy" <anonymous@.discussions.microsoft.com> wrote in message news:12e6801c41674$4b7a4c20$a
601280a@.phx.gbl...
> Is there any performance gain from defragmenting the SQL
> SERVER data and log file disk subsystem (or OS). I have
> run the "Analyze" part of the windows disk defragmenter
> and several of the data and log files have several
> fragments within the file. Some of them have 20
> fragments. Is there another tool that is preferred? Any
> advice or past experienced is much appreciated.
> Thanks,
> Andy|||OS level defrag is a good thing. SQL Server might report 0% fragmentation
for internal and internal fragmentation within it's data structures. But
that's just for SQL.
SQL Server can't know anything about where the bits are stored at the OS.
Consider a situation where SQL reports no fragmentation, and you're doing a
table scan. Imagine that the file with the big table is scattered all over
disk. Yes, there's be a benefit to OS frag in this case.
However, one thing to consider... defragging at the OS level doesn't always
have as big an impact as some people might expect. SQL Server pre-allocates
space when you create a file. If you create a 100M file on a disk with no
fragmentation... and put 1M of data on it. The file still uses 100M at the
OS and it would be contiguous if the file was created when the disk was not
fragmented... if the disk later becomes fragmented... that 100M file will
still be contiguous.
OS level frag tends to help the most with db files that have grown a number
of times since they were created... or if you're creating DB files on a
disks that are already fragmented...
also... don't forget that you'll need to shut down SQL Server before doing
the defrag. Open files aren't defragged...
Brian Moran
Principal Mentor
Solid Quality Learning
SQL Server MVP
http://www.solidqualitylearning.com
"Andy" <anonymous@.discussions.microsoft.com> wrote in message
news:12e6801c41674$4b7a4c20$a601280a@.phx
.gbl...
> Is there any performance gain from defragmenting the SQL
> SERVER data and log file disk subsystem (or OS). I have
> run the "Analyze" part of the windows disk defragmenter
> and several of the data and log files have several
> fragments within the file. Some of them have 20
> fragments. Is there another tool that is preferred? Any
> advice or past experienced is much appreciated.
> Thanks,
> Andy|||Sure, just like any other file access, defragmenting your database files
will improve performance if they're badly fragmented. I'm not sure if 20
fragments is that bad, however. It really depends on the size of the files
(if the files are very small, 20 fragments may be bad; if they're quite
large, it's probably not affecting contiguous data access too much). Either
way, if you don't mind the downtime (you'll have to shut down the server in
order to defrag the files), it certainly won't hurt. As for defrag tools, I
don't like Windows' built in one. I personally have gotten very good
results from O&O Software Defrag.
"Andy" <anonymous@.discussions.microsoft.com> wrote in message
news:12e6801c41674$4b7a4c20$a601280a@.phx
.gbl...
> Is there any performance gain from defragmenting the SQL
> SERVER data and log file disk subsystem (or OS). I have
> run the "Analyze" part of the windows disk defragmenter
> and several of the data and log files have several
> fragments within the file. Some of them have 20
> fragments. Is there another tool that is preferred? Any
> advice or past experienced is much appreciated.
> Thanks,
> Andy

Defrag local drive in SQL Server 2005

Are there any considerations that must be taken before defragging the local
drives in SQL Server 2005? For RAID and non-RAID drives?A backup would be nice first but other than that not really.
--
Andrew J. Kelly SQL MVP
"morphius" <morphius@.discussions.microsoft.com> wrote in message
news:A0DD0845-0609-4094-9A23-3DAEF22922B3@.microsoft.com...
> Are there any considerations that must be taken before defragging the
> local
> drives in SQL Server 2005? For RAID and non-RAID drives?|||Thanks...
"Andrew J. Kelly" wrote:
> A backup would be nice first but other than that not really.
> --
> Andrew J. Kelly SQL MVP
> "morphius" <morphius@.discussions.microsoft.com> wrote in message
> news:A0DD0845-0609-4094-9A23-3DAEF22922B3@.microsoft.com...
> > Are there any considerations that must be taken before defragging the
> > local
> > drives in SQL Server 2005? For RAID and non-RAID drives?
>
>|||morphius wrote:
> Are there any considerations that must be taken before defragging the local
> drives in SQL Server 2005? For RAID and non-RAID drives?
Be sure to shut down SQL Server, otherwise the data files will be locked
and won't be accessible to defrag. DisKeeper claims to defrag database
files without shutting down SQL, but I wouldn't be comfortable doing that.
Tracy McKibben
MCDBA
http://www.realsqlguy.com

Defrag local drive in SQL Server 2005

Are there any considerations that must be taken before defragging the local
drives in SQL Server 2005? For RAID and non-RAID drives?
A backup would be nice first but other than that not really.
Andrew J. Kelly SQL MVP
"morphius" <morphius@.discussions.microsoft.com> wrote in message
news:A0DD0845-0609-4094-9A23-3DAEF22922B3@.microsoft.com...
> Are there any considerations that must be taken before defragging the
> local
> drives in SQL Server 2005? For RAID and non-RAID drives?
|||Thanks...
"Andrew J. Kelly" wrote:

> A backup would be nice first but other than that not really.
> --
> Andrew J. Kelly SQL MVP
> "morphius" <morphius@.discussions.microsoft.com> wrote in message
> news:A0DD0845-0609-4094-9A23-3DAEF22922B3@.microsoft.com...
>
>
|||morphius wrote:
> Are there any considerations that must be taken before defragging the local
> drives in SQL Server 2005? For RAID and non-RAID drives?
Be sure to shut down SQL Server, otherwise the data files will be locked
and won't be accessible to defrag. DisKeeper claims to defrag database
files without shutting down SQL, but I wouldn't be comfortable doing that.
Tracy McKibben
MCDBA
http://www.realsqlguy.com

Defrag local drive in SQL Server 2005

Are there any considerations that must be taken before defragging the local
drives in SQL Server 2005? For RAID and non-RAID drives?A backup would be nice first but other than that not really.
Andrew J. Kelly SQL MVP
"morphius" <morphius@.discussions.microsoft.com> wrote in message
news:A0DD0845-0609-4094-9A23-3DAEF22922B3@.microsoft.com...
> Are there any considerations that must be taken before defragging the
> local
> drives in SQL Server 2005? For RAID and non-RAID drives?|||Thanks...
"Andrew J. Kelly" wrote:

> A backup would be nice first but other than that not really.
> --
> Andrew J. Kelly SQL MVP
> "morphius" <morphius@.discussions.microsoft.com> wrote in message
> news:A0DD0845-0609-4094-9A23-3DAEF22922B3@.microsoft.com...
>
>|||morphius wrote:
> Are there any considerations that must be taken before defragging the loca
l
> drives in SQL Server 2005? For RAID and non-RAID drives?
Be sure to shut down SQL Server, otherwise the data files will be locked
and won't be accessible to defrag. DisKeeper claims to defrag database
files without shutting down SQL, but I wouldn't be comfortable doing that.
Tracy McKibben
MCDBA
http://www.realsqlguy.com

Define Job using SQL DMO

Hi Friends,
I have to define a new job, to take the backup automatically, at the SQL
Server 2000. on the Local Network if i login using the Machine name (Only
Default instance is installed), it works fine. But in the following scenario
s
it does not work -
-- If i login using IP Address of the machine on the local network.
-- If the Server is on other network on the internet.
I get the Folloing error-
[Microsoft][ODBC SQL Server Driver][SQL Server]The specified @.server_name
('192.168.100.19') does not exist.
With Thanks and Regards,
LalitHi Lalit,
Can you be more specific in what situation you see the failure? Is it when
creating the job? Can you share the script with us then (of course, strip
out any confidential info)?
Regards,
Ciprian Gerea
SDE, SqlServer
This posting is provided "AS IS" with no warranties, and confers no rights.
"Lalit" <Lalit@.discussions.microsoft.com> wrote in message
news:12AA128D-0919-457D-95E9-86FA861372CA@.microsoft.com...
> Hi Friends,
> I have to define a new job, to take the backup automatically, at the SQL
> Server 2000. on the Local Network if i login using the Machine name (Only
> Default instance is installed), it works fine. But in the following
> scenarios
> it does not work -
> -- If i login using IP Address of the machine on the local network.
> -- If the Server is on other network on the internet.
> I get the Folloing error-
> [Microsoft][ODBC SQL Server Driver][SQL Server]The specified @.server_name
> ('192.168.100.19') does not exist.
> With Thanks and Regards,
> Lalit
>|||Hi Ciprian,
Thanks for your reply.
I am doing the following
'Adding Schedule to Job
objJob.BeginAlter 'Begin changes to the job object
objJob.RemoveAllJobSchedules
objJob.RemoveAllJobSteps
'Setting JobSchedule Name
objJobSchedule.Name = "strJobName" & "_Schedule"
'Properties of Schedule
objJobSchedule.Schedule.FrequencyType = SQLDMOFreq_Daily
objJobSchedule.Schedule.FrequencyInterval = Val(txtDays.Text)
'Set Active Start date
strStartYear = DatePart("yyyy", Now)
strStartMonth = Format(DatePart("m", Now), "00")
strStartDay = Format(DatePart("d", Now), "00")
objJobSchedule.Schedule.ActiveStartDate = strStartYear & strStartMonth &
strStartDay
objJobSchedule.Schedule.ActiveStartTimeOfDay = intTime
objJobSchedule.Schedule.ActiveEndDate = SQLDMO_NOENDDATE
objJob.JobSchedules.Add objJobSchedule
objJob.JobSchedules.Refresh
objjobStep.Name = "Take_DataBackup"
'Setting Properties of JobStep
objjobStep.DatabaseName = cboDatabaseName.Text
objjobStep.Server = cboBackupServer.Text
objjobStep.Command = "DatabaseBackup"
objjobStep.OnSuccessAction = SQLDMOJobStepAction_QuitWithSuccess
objjobStep.OnFailAction = SQLDMOJobStepAction_QuitWithFailure
'Adding JobSteps to Job object
objJob.AddStepToJob objjobStep
'Commit changes to the job object
objJob.DoAlter
'Target the server to enable the job
objJob.ApplyToTargetServer "192.168.100.19"
objJob.Refresh
I Get the erro in the ApplyToTargetServer method when i am using IP address.
Where 192.168.100.19 is the IP address of the DB Server where i am connecte
d.
But the Error Message Says
[Microsoft][ODBC SQL Server Driver][SQL Server]The specified @.server_name
('192.168.100.19') does not exist
If i am providing the name of the machine then no error occurs.
With regadrs,
Lalit