I recently moved my backups from the wizard generated maintenance plans to a
simple backup script. My 180GB database is now taking 7 hours to backup with
instead of the normal 4 hours. The step details show 7.359 MB/sec when the
backup completes in 7 hours and 12.453 MB/sec when the backup completes in 4
hours.
Below is my script. This is actually step 2. Step 1 deletes the previous
backup.
BACKUP DATABASE MyDB
TO DISK = '\\MyNAS\sql\server\MyDB\MyDB.bak'
WITH INIT
During the backup, pinging the NAS device from the SQL Server takes 0ms.
I have used Perfmon and the counters seem usual from what I have researched
(e.g., http://www.sql-server-performance.co...ore_tuning.asp).
Average Device Throughput bytes/sec: 5570346
Average % Disk Time: 3.854
Average Disk Queue Length: .0193
Average IO Write Bytes/sec: 5897638
Average Split IO/sec: 0
As for sp_who2, the only thing peculiar is a DISKIO of 875659 for SQLAgent -
Alert Engine.
No compression is taking place on either machine.
Does anyone have any suggestions as what is causing the backup time to
almost double?
Thanks,
ray
SS2K
Hi,
Was your Wizard generated backup maintenance plan overwriting the same
backup file each time or writing to a new file each time? The only
thing I can think of is that if the backup file already exists and you
are re-initializing it takes less time. From an OS/Network standpoint
that should not hinder performance. Perhaps you can look at the wizard
generated backup plan to see if there are some option enabled by SQL
Server which may increase performance.
Shahryar
raybouk wrote:
>I recently moved my backups from the wizard generated maintenance plans to a
>simple backup script. My 180GB database is now taking 7 hours to backup with
>instead of the normal 4 hours. The step details show 7.359 MB/sec when the
>backup completes in 7 hours and 12.453 MB/sec when the backup completes in 4
>hours.
>Below is my script. This is actually step 2. Step 1 deletes the previous
>backup.
>BACKUP DATABASE MyDB
>TO DISK = '\\MyNAS\sql\server\MyDB\MyDB.bak'
>WITH INIT
>During the backup, pinging the NAS device from the SQL Server takes 0ms.
>I have used Perfmon and the counters seem usual from what I have researched
>(e.g., http://www.sql-server-performance.co...ore_tuning.asp).
>Average Device Throughput bytes/sec: 5570346
>Average % Disk Time: 3.854
>Average Disk Queue Length: .0193
>Average IO Write Bytes/sec: 5897638
>Average Split IO/sec: 0
>As for sp_who2, the only thing peculiar is a DISKIO of 875659 for SQLAgent -
>Alert Engine.
>No compression is taking place on either machine.
>Does anyone have any suggestions as what is causing the backup time to
>almost double?
>Thanks,
>ray
>SS2K
>
Shahryar G. Hashemi | Sr. DBA Consultant
InfoSpace, Inc.
601 108th Ave NE | Suite 1200 | Bellevue, WA 98004 USA
Mobile +1 206.459.6203 | Office +1 425.201.8853 | Fax +1 425.201.6150
shashem@.infospace.com | www.infospaceinc.com
This e-mail and any attachments may contain confidential information that is legally privileged. The information is solely for the use of the intended recipient(s); any disclosure, copying, distribution, or other use of this information is strictly prohi
bited. If you have received this e-mail in error, please notify the sender by return e-mail and delete this message. Thank you.
|||Delete the old backup file prior to performing the backup. Dont init the new
backup device. I found this to work the best.
On a sidenote, ping rates do not necessarily reflect thoughput performance.
"raybouk" wrote:
> I recently moved my backups from the wizard generated maintenance plans to a
> simple backup script. My 180GB database is now taking 7 hours to backup with
> instead of the normal 4 hours. The step details show 7.359 MB/sec when the
> backup completes in 7 hours and 12.453 MB/sec when the backup completes in 4
> hours.
> Below is my script. This is actually step 2. Step 1 deletes the previous
> backup.
> BACKUP DATABASE MyDB
> TO DISK = '\\MyNAS\sql\server\MyDB\MyDB.bak'
> WITH INIT
> During the backup, pinging the NAS device from the SQL Server takes 0ms.
> I have used Perfmon and the counters seem usual from what I have researched
> (e.g., http://www.sql-server-performance.co...ore_tuning.asp).
> Average Device Throughput bytes/sec: 5570346
> Average % Disk Time: 3.854
> Average Disk Queue Length: .0193
> Average IO Write Bytes/sec: 5897638
> Average Split IO/sec: 0
> As for sp_who2, the only thing peculiar is a DISKIO of 875659 for SQLAgent -
> Alert Engine.
> No compression is taking place on either machine.
> Does anyone have any suggestions as what is causing the backup time to
> almost double?
> Thanks,
> ray
> SS2K
|||What was the destination of the Wizard generated backups?
There is going to be a world of difference between writing to a local disk
vs going to a NAS device.
You should be able to go into the Management Studio and look at the script
that it generates.
See what the differences are in the backup statement.
"Raoul Laoyan" <RaoulLaoyan@.discussions.microsoft.com> wrote in message
news:0C4A91B3-D06B-40DA-87EE-1D94A94A707F@.microsoft.com...[vbcol=seagreen]
> Delete the old backup file prior to performing the backup. Dont init the
> new
> backup device. I found this to work the best.
> On a sidenote, ping rates do not necessarily reflect thoughput
> performance.
> "raybouk" wrote:
Showing posts with label moved. Show all posts
Showing posts with label moved. Show all posts
Monday, March 26, 2012
long backup duration
I recently moved my backups from the wizard generated maintenance plans to a
simple backup script. My 180GB database is now taking 7 hours to backup with
instead of the normal 4 hours. The step details show 7.359 MB/sec when the
backup completes in 7 hours and 12.453 MB/sec when the backup completes in 4
hours.
Below is my script. This is actually step 2. Step 1 deletes the previous
backup.
BACKUP DATABASE MyDB
TO DISK = '\\MyNAS\sql\server\MyDB\MyDB.bak'
WITH INIT
During the backup, pinging the NAS device from the SQL Server takes 0ms.
I have used Perfmon and the counters seem usual from what I have researched
(e.g., http://www.sql-server-performance.c...tore_tuning.asp).
Average Device Throughput bytes/sec: 5570346
Average % Disk Time: 3.854
Average Disk Queue Length: .0193
Average IO Write Bytes/sec: 5897638
Average Split IO/sec: 0
As for sp_who2, the only thing peculiar is a DISKIO of 875659 for SQLAgent -
Alert Engine.
No compression is taking place on either machine.
Does anyone have any suggestions as what is causing the backup time to
almost double?
Thanks,
ray
SS2KHi,
Was your Wizard generated backup maintenance plan overwriting the same
backup file each time or writing to a new file each time? The only
thing I can think of is that if the backup file already exists and you
are re-initializing it takes less time. From an OS/Network standpoint
that should not hinder performance. Perhaps you can look at the wizard
generated backup plan to see if there are some option enabled by SQL
Server which may increase performance.
Shahryar
raybouk wrote:
>I recently moved my backups from the wizard generated maintenance plans to
a
>simple backup script. My 180GB database is now taking 7 hours to backup wit
h
>instead of the normal 4 hours. The step details show 7.359 MB/sec when the
>backup completes in 7 hours and 12.453 MB/sec when the backup completes in
4
>hours.
>Below is my script. This is actually step 2. Step 1 deletes the previous
>backup.
>BACKUP DATABASE MyDB
>TO DISK = '\\MyNAS\sql\server\MyDB\MyDB.bak'
>WITH INIT
>During the backup, pinging the NAS device from the SQL Server takes 0ms.
>I have used Perfmon and the counters seem usual from what I have researched
>(e.g., http://www.sql-server-performance.c...tore_tuning.asp).
>Average Device Throughput bytes/sec: 5570346
>Average % Disk Time: 3.854
>Average Disk Queue Length: .0193
>Average IO Write Bytes/sec: 5897638
>Average Split IO/sec: 0
>As for sp_who2, the only thing peculiar is a DISKIO of 875659 for SQLAgent
-
>Alert Engine.
>No compression is taking place on either machine.
>Does anyone have any suggestions as what is causing the backup time to
>almost double?
>Thanks,
>ray
>SS2K
>
Shahryar G. Hashemi | Sr. DBA Consultant
InfoSpace, Inc.
601 108th Ave NE | Suite 1200 | Bellevue, WA 98004 USA
Mobile +1 206.459.6203 | Office +1 425.201.8853 | Fax +1 425.201.6150
shashem@.infospace.com | www.infospaceinc.com
This e-mail and any attachments may contain confidential information that is
legally privileged. The information is solely for the use of the intended
recipient(s); any disclosure, copying, distribution, or other use of this in
formation is strictly prohi
bited. If you have received this e-mail in error, please notify the sender
by return e-mail and delete this message. Thank you.|||Delete the old backup file prior to performing the backup. Dont init the ne
w
backup device. I found this to work the best.
On a sidenote, ping rates do not necessarily reflect thoughput performance.
"raybouk" wrote:
> I recently moved my backups from the wizard generated maintenance plans to
a
> simple backup script. My 180GB database is now taking 7 hours to backup wi
th
> instead of the normal 4 hours. The step details show 7.359 MB/sec when the
> backup completes in 7 hours and 12.453 MB/sec when the backup completes in
4
> hours.
> Below is my script. This is actually step 2. Step 1 deletes the previous
> backup.
> BACKUP DATABASE MyDB
> TO DISK = '\\MyNAS\sql\server\MyDB\MyDB.bak'
> WITH INIT
> During the backup, pinging the NAS device from the SQL Server takes 0ms.
> I have used Perfmon and the counters seem usual from what I have researche
d
> (e.g., http://www.sql-server-performance.c...tore_tuning.asp).
> Average Device Throughput bytes/sec: 5570346
> Average % Disk Time: 3.854
> Average Disk Queue Length: .0193
> Average IO Write Bytes/sec: 5897638
> Average Split IO/sec: 0
> As for sp_who2, the only thing peculiar is a DISKIO of 875659 for SQLAgent
-
> Alert Engine.
> No compression is taking place on either machine.
> Does anyone have any suggestions as what is causing the backup time to
> almost double?
> Thanks,
> ray
> SS2K|||What was the destination of the Wizard generated backups?
There is going to be a world of difference between writing to a local disk
vs going to a NAS device.
You should be able to go into the Management Studio and look at the script
that it generates.
See what the differences are in the backup statement.
"Raoul Laoyan" <RaoulLaoyan@.discussions.microsoft.com> wrote in message
news:0C4A91B3-D06B-40DA-87EE-1D94A94A707F@.microsoft.com...[vbcol=seagreen]
> Delete the old backup file prior to performing the backup. Dont init the
> new
> backup device. I found this to work the best.
> On a sidenote, ping rates do not necessarily reflect thoughput
> performance.
> "raybouk" wrote:
>
simple backup script. My 180GB database is now taking 7 hours to backup with
instead of the normal 4 hours. The step details show 7.359 MB/sec when the
backup completes in 7 hours and 12.453 MB/sec when the backup completes in 4
hours.
Below is my script. This is actually step 2. Step 1 deletes the previous
backup.
BACKUP DATABASE MyDB
TO DISK = '\\MyNAS\sql\server\MyDB\MyDB.bak'
WITH INIT
During the backup, pinging the NAS device from the SQL Server takes 0ms.
I have used Perfmon and the counters seem usual from what I have researched
(e.g., http://www.sql-server-performance.c...tore_tuning.asp).
Average Device Throughput bytes/sec: 5570346
Average % Disk Time: 3.854
Average Disk Queue Length: .0193
Average IO Write Bytes/sec: 5897638
Average Split IO/sec: 0
As for sp_who2, the only thing peculiar is a DISKIO of 875659 for SQLAgent -
Alert Engine.
No compression is taking place on either machine.
Does anyone have any suggestions as what is causing the backup time to
almost double?
Thanks,
ray
SS2KHi,
Was your Wizard generated backup maintenance plan overwriting the same
backup file each time or writing to a new file each time? The only
thing I can think of is that if the backup file already exists and you
are re-initializing it takes less time. From an OS/Network standpoint
that should not hinder performance. Perhaps you can look at the wizard
generated backup plan to see if there are some option enabled by SQL
Server which may increase performance.
Shahryar
raybouk wrote:
>I recently moved my backups from the wizard generated maintenance plans to
a
>simple backup script. My 180GB database is now taking 7 hours to backup wit
h
>instead of the normal 4 hours. The step details show 7.359 MB/sec when the
>backup completes in 7 hours and 12.453 MB/sec when the backup completes in
4
>hours.
>Below is my script. This is actually step 2. Step 1 deletes the previous
>backup.
>BACKUP DATABASE MyDB
>TO DISK = '\\MyNAS\sql\server\MyDB\MyDB.bak'
>WITH INIT
>During the backup, pinging the NAS device from the SQL Server takes 0ms.
>I have used Perfmon and the counters seem usual from what I have researched
>(e.g., http://www.sql-server-performance.c...tore_tuning.asp).
>Average Device Throughput bytes/sec: 5570346
>Average % Disk Time: 3.854
>Average Disk Queue Length: .0193
>Average IO Write Bytes/sec: 5897638
>Average Split IO/sec: 0
>As for sp_who2, the only thing peculiar is a DISKIO of 875659 for SQLAgent
-
>Alert Engine.
>No compression is taking place on either machine.
>Does anyone have any suggestions as what is causing the backup time to
>almost double?
>Thanks,
>ray
>SS2K
>
Shahryar G. Hashemi | Sr. DBA Consultant
InfoSpace, Inc.
601 108th Ave NE | Suite 1200 | Bellevue, WA 98004 USA
Mobile +1 206.459.6203 | Office +1 425.201.8853 | Fax +1 425.201.6150
shashem@.infospace.com | www.infospaceinc.com
This e-mail and any attachments may contain confidential information that is
legally privileged. The information is solely for the use of the intended
recipient(s); any disclosure, copying, distribution, or other use of this in
formation is strictly prohi
bited. If you have received this e-mail in error, please notify the sender
by return e-mail and delete this message. Thank you.|||Delete the old backup file prior to performing the backup. Dont init the ne
w
backup device. I found this to work the best.
On a sidenote, ping rates do not necessarily reflect thoughput performance.
"raybouk" wrote:
> I recently moved my backups from the wizard generated maintenance plans to
a
> simple backup script. My 180GB database is now taking 7 hours to backup wi
th
> instead of the normal 4 hours. The step details show 7.359 MB/sec when the
> backup completes in 7 hours and 12.453 MB/sec when the backup completes in
4
> hours.
> Below is my script. This is actually step 2. Step 1 deletes the previous
> backup.
> BACKUP DATABASE MyDB
> TO DISK = '\\MyNAS\sql\server\MyDB\MyDB.bak'
> WITH INIT
> During the backup, pinging the NAS device from the SQL Server takes 0ms.
> I have used Perfmon and the counters seem usual from what I have researche
d
> (e.g., http://www.sql-server-performance.c...tore_tuning.asp).
> Average Device Throughput bytes/sec: 5570346
> Average % Disk Time: 3.854
> Average Disk Queue Length: .0193
> Average IO Write Bytes/sec: 5897638
> Average Split IO/sec: 0
> As for sp_who2, the only thing peculiar is a DISKIO of 875659 for SQLAgent
-
> Alert Engine.
> No compression is taking place on either machine.
> Does anyone have any suggestions as what is causing the backup time to
> almost double?
> Thanks,
> ray
> SS2K|||What was the destination of the Wizard generated backups?
There is going to be a world of difference between writing to a local disk
vs going to a NAS device.
You should be able to go into the Management Studio and look at the script
that it generates.
See what the differences are in the backup statement.
"Raoul Laoyan" <RaoulLaoyan@.discussions.microsoft.com> wrote in message
news:0C4A91B3-D06B-40DA-87EE-1D94A94A707F@.microsoft.com...[vbcol=seagreen]
> Delete the old backup file prior to performing the backup. Dont init the
> new
> backup device. I found this to work the best.
> On a sidenote, ping rates do not necessarily reflect thoughput
> performance.
> "raybouk" wrote:
>
long backup duration
I recently moved my backups from the wizard generated maintenance plans to a
simple backup script. My 180GB database is now taking 7 hours to backup with
instead of the normal 4 hours. The step details show 7.359 MB/sec when the
backup completes in 7 hours and 12.453 MB/sec when the backup completes in 4
hours.
Below is my script. This is actually step 2. Step 1 deletes the previous
backup.
BACKUP DATABASE MyDB
TO DISK = '\\MyNAS\sql\server\MyDB\MyDB.bak'
WITH INIT
During the backup, pinging the NAS device from the SQL Server takes 0ms.
I have used Perfmon and the counters seem usual from what I have researched
(e.g., http://www.sql-server-performance.com/backup_restore_tuning.asp).
Average Device Throughput bytes/sec: 5570346
Average % Disk Time: 3.854
Average Disk Queue Length: .0193
Average IO Write Bytes/sec: 5897638
Average Split IO/sec: 0
As for sp_who2, the only thing peculiar is a DISKIO of 875659 for SQLAgent -
Alert Engine.
No compression is taking place on either machine.
Does anyone have any suggestions as what is causing the backup time to
almost double?
Thanks,
ray
SS2KHi,
Was your Wizard generated backup maintenance plan overwriting the same
backup file each time or writing to a new file each time? The only
thing I can think of is that if the backup file already exists and you
are re-initializing it takes less time. From an OS/Network standpoint
that should not hinder performance. Perhaps you can look at the wizard
generated backup plan to see if there are some option enabled by SQL
Server which may increase performance.
Shahryar
raybouk wrote:
>I recently moved my backups from the wizard generated maintenance plans to a
>simple backup script. My 180GB database is now taking 7 hours to backup with
>instead of the normal 4 hours. The step details show 7.359 MB/sec when the
>backup completes in 7 hours and 12.453 MB/sec when the backup completes in 4
>hours.
>Below is my script. This is actually step 2. Step 1 deletes the previous
>backup.
>BACKUP DATABASE MyDB
>TO DISK = '\\MyNAS\sql\server\MyDB\MyDB.bak'
>WITH INIT
>During the backup, pinging the NAS device from the SQL Server takes 0ms.
>I have used Perfmon and the counters seem usual from what I have researched
>(e.g., http://www.sql-server-performance.com/backup_restore_tuning.asp).
>Average Device Throughput bytes/sec: 5570346
>Average % Disk Time: 3.854
>Average Disk Queue Length: .0193
>Average IO Write Bytes/sec: 5897638
>Average Split IO/sec: 0
>As for sp_who2, the only thing peculiar is a DISKIO of 875659 for SQLAgent -
>Alert Engine.
>No compression is taking place on either machine.
>Does anyone have any suggestions as what is causing the backup time to
>almost double?
>Thanks,
>ray
>SS2K
>
Shahryar G. Hashemi | Sr. DBA Consultant
InfoSpace, Inc.
601 108th Ave NE | Suite 1200 | Bellevue, WA 98004 USA
Mobile +1 206.459.6203 | Office +1 425.201.8853 | Fax +1 425.201.6150
shashem@.infospace.com | www.infospaceinc.com
This e-mail and any attachments may contain confidential information that is legally privileged. The information is solely for the use of the intended recipient(s); any disclosure, copying, distribution, or other use of this information is strictly prohibited. If you have received this e-mail in error, please notify the sender by return e-mail and delete this message. Thank you.|||Delete the old backup file prior to performing the backup. Dont init the new
backup device. I found this to work the best.
On a sidenote, ping rates do not necessarily reflect thoughput performance.
"raybouk" wrote:
> I recently moved my backups from the wizard generated maintenance plans to a
> simple backup script. My 180GB database is now taking 7 hours to backup with
> instead of the normal 4 hours. The step details show 7.359 MB/sec when the
> backup completes in 7 hours and 12.453 MB/sec when the backup completes in 4
> hours.
> Below is my script. This is actually step 2. Step 1 deletes the previous
> backup.
> BACKUP DATABASE MyDB
> TO DISK = '\\MyNAS\sql\server\MyDB\MyDB.bak'
> WITH INIT
> During the backup, pinging the NAS device from the SQL Server takes 0ms.
> I have used Perfmon and the counters seem usual from what I have researched
> (e.g., http://www.sql-server-performance.com/backup_restore_tuning.asp).
> Average Device Throughput bytes/sec: 5570346
> Average % Disk Time: 3.854
> Average Disk Queue Length: .0193
> Average IO Write Bytes/sec: 5897638
> Average Split IO/sec: 0
> As for sp_who2, the only thing peculiar is a DISKIO of 875659 for SQLAgent -
> Alert Engine.
> No compression is taking place on either machine.
> Does anyone have any suggestions as what is causing the backup time to
> almost double?
> Thanks,
> ray
> SS2K|||What was the destination of the Wizard generated backups?
There is going to be a world of difference between writing to a local disk
vs going to a NAS device.
You should be able to go into the Management Studio and look at the script
that it generates.
See what the differences are in the backup statement.
"Raoul Laoyan" <RaoulLaoyan@.discussions.microsoft.com> wrote in message
news:0C4A91B3-D06B-40DA-87EE-1D94A94A707F@.microsoft.com...
> Delete the old backup file prior to performing the backup. Dont init the
> new
> backup device. I found this to work the best.
> On a sidenote, ping rates do not necessarily reflect thoughput
> performance.
> "raybouk" wrote:
>> I recently moved my backups from the wizard generated maintenance plans
>> to a
>> simple backup script. My 180GB database is now taking 7 hours to backup
>> with
>> instead of the normal 4 hours. The step details show 7.359 MB/sec when
>> the
>> backup completes in 7 hours and 12.453 MB/sec when the backup completes
>> in 4
>> hours.
>> Below is my script. This is actually step 2. Step 1 deletes the previous
>> backup.
>> BACKUP DATABASE MyDB
>> TO DISK = '\\MyNAS\sql\server\MyDB\MyDB.bak'
>> WITH INIT
>> During the backup, pinging the NAS device from the SQL Server takes 0ms.
>> I have used Perfmon and the counters seem usual from what I have
>> researched
>> (e.g., http://www.sql-server-performance.com/backup_restore_tuning.asp).
>> Average Device Throughput bytes/sec: 5570346
>> Average % Disk Time: 3.854
>> Average Disk Queue Length: .0193
>> Average IO Write Bytes/sec: 5897638
>> Average Split IO/sec: 0
>> As for sp_who2, the only thing peculiar is a DISKIO of 875659 for
>> SQLAgent -
>> Alert Engine.
>> No compression is taking place on either machine.
>> Does anyone have any suggestions as what is causing the backup time to
>> almost double?
>> Thanks,
>> ray
>> SS2K
simple backup script. My 180GB database is now taking 7 hours to backup with
instead of the normal 4 hours. The step details show 7.359 MB/sec when the
backup completes in 7 hours and 12.453 MB/sec when the backup completes in 4
hours.
Below is my script. This is actually step 2. Step 1 deletes the previous
backup.
BACKUP DATABASE MyDB
TO DISK = '\\MyNAS\sql\server\MyDB\MyDB.bak'
WITH INIT
During the backup, pinging the NAS device from the SQL Server takes 0ms.
I have used Perfmon and the counters seem usual from what I have researched
(e.g., http://www.sql-server-performance.com/backup_restore_tuning.asp).
Average Device Throughput bytes/sec: 5570346
Average % Disk Time: 3.854
Average Disk Queue Length: .0193
Average IO Write Bytes/sec: 5897638
Average Split IO/sec: 0
As for sp_who2, the only thing peculiar is a DISKIO of 875659 for SQLAgent -
Alert Engine.
No compression is taking place on either machine.
Does anyone have any suggestions as what is causing the backup time to
almost double?
Thanks,
ray
SS2KHi,
Was your Wizard generated backup maintenance plan overwriting the same
backup file each time or writing to a new file each time? The only
thing I can think of is that if the backup file already exists and you
are re-initializing it takes less time. From an OS/Network standpoint
that should not hinder performance. Perhaps you can look at the wizard
generated backup plan to see if there are some option enabled by SQL
Server which may increase performance.
Shahryar
raybouk wrote:
>I recently moved my backups from the wizard generated maintenance plans to a
>simple backup script. My 180GB database is now taking 7 hours to backup with
>instead of the normal 4 hours. The step details show 7.359 MB/sec when the
>backup completes in 7 hours and 12.453 MB/sec when the backup completes in 4
>hours.
>Below is my script. This is actually step 2. Step 1 deletes the previous
>backup.
>BACKUP DATABASE MyDB
>TO DISK = '\\MyNAS\sql\server\MyDB\MyDB.bak'
>WITH INIT
>During the backup, pinging the NAS device from the SQL Server takes 0ms.
>I have used Perfmon and the counters seem usual from what I have researched
>(e.g., http://www.sql-server-performance.com/backup_restore_tuning.asp).
>Average Device Throughput bytes/sec: 5570346
>Average % Disk Time: 3.854
>Average Disk Queue Length: .0193
>Average IO Write Bytes/sec: 5897638
>Average Split IO/sec: 0
>As for sp_who2, the only thing peculiar is a DISKIO of 875659 for SQLAgent -
>Alert Engine.
>No compression is taking place on either machine.
>Does anyone have any suggestions as what is causing the backup time to
>almost double?
>Thanks,
>ray
>SS2K
>
Shahryar G. Hashemi | Sr. DBA Consultant
InfoSpace, Inc.
601 108th Ave NE | Suite 1200 | Bellevue, WA 98004 USA
Mobile +1 206.459.6203 | Office +1 425.201.8853 | Fax +1 425.201.6150
shashem@.infospace.com | www.infospaceinc.com
This e-mail and any attachments may contain confidential information that is legally privileged. The information is solely for the use of the intended recipient(s); any disclosure, copying, distribution, or other use of this information is strictly prohibited. If you have received this e-mail in error, please notify the sender by return e-mail and delete this message. Thank you.|||Delete the old backup file prior to performing the backup. Dont init the new
backup device. I found this to work the best.
On a sidenote, ping rates do not necessarily reflect thoughput performance.
"raybouk" wrote:
> I recently moved my backups from the wizard generated maintenance plans to a
> simple backup script. My 180GB database is now taking 7 hours to backup with
> instead of the normal 4 hours. The step details show 7.359 MB/sec when the
> backup completes in 7 hours and 12.453 MB/sec when the backup completes in 4
> hours.
> Below is my script. This is actually step 2. Step 1 deletes the previous
> backup.
> BACKUP DATABASE MyDB
> TO DISK = '\\MyNAS\sql\server\MyDB\MyDB.bak'
> WITH INIT
> During the backup, pinging the NAS device from the SQL Server takes 0ms.
> I have used Perfmon and the counters seem usual from what I have researched
> (e.g., http://www.sql-server-performance.com/backup_restore_tuning.asp).
> Average Device Throughput bytes/sec: 5570346
> Average % Disk Time: 3.854
> Average Disk Queue Length: .0193
> Average IO Write Bytes/sec: 5897638
> Average Split IO/sec: 0
> As for sp_who2, the only thing peculiar is a DISKIO of 875659 for SQLAgent -
> Alert Engine.
> No compression is taking place on either machine.
> Does anyone have any suggestions as what is causing the backup time to
> almost double?
> Thanks,
> ray
> SS2K|||What was the destination of the Wizard generated backups?
There is going to be a world of difference between writing to a local disk
vs going to a NAS device.
You should be able to go into the Management Studio and look at the script
that it generates.
See what the differences are in the backup statement.
"Raoul Laoyan" <RaoulLaoyan@.discussions.microsoft.com> wrote in message
news:0C4A91B3-D06B-40DA-87EE-1D94A94A707F@.microsoft.com...
> Delete the old backup file prior to performing the backup. Dont init the
> new
> backup device. I found this to work the best.
> On a sidenote, ping rates do not necessarily reflect thoughput
> performance.
> "raybouk" wrote:
>> I recently moved my backups from the wizard generated maintenance plans
>> to a
>> simple backup script. My 180GB database is now taking 7 hours to backup
>> with
>> instead of the normal 4 hours. The step details show 7.359 MB/sec when
>> the
>> backup completes in 7 hours and 12.453 MB/sec when the backup completes
>> in 4
>> hours.
>> Below is my script. This is actually step 2. Step 1 deletes the previous
>> backup.
>> BACKUP DATABASE MyDB
>> TO DISK = '\\MyNAS\sql\server\MyDB\MyDB.bak'
>> WITH INIT
>> During the backup, pinging the NAS device from the SQL Server takes 0ms.
>> I have used Perfmon and the counters seem usual from what I have
>> researched
>> (e.g., http://www.sql-server-performance.com/backup_restore_tuning.asp).
>> Average Device Throughput bytes/sec: 5570346
>> Average % Disk Time: 3.854
>> Average Disk Queue Length: .0193
>> Average IO Write Bytes/sec: 5897638
>> Average Split IO/sec: 0
>> As for sp_who2, the only thing peculiar is a DISKIO of 875659 for
>> SQLAgent -
>> Alert Engine.
>> No compression is taking place on either machine.
>> Does anyone have any suggestions as what is causing the backup time to
>> almost double?
>> Thanks,
>> ray
>> SS2K
Monday, February 20, 2012
Login permisions
Hi!
I've just moved a mdb to MSDE without problem. The problem comes when I try
to open the tables I have attached via ODBC. Tables are in MSDE and I call
them from Access.
I get an error telling me that the user is not allowed to read (error
18456). Following that message, I get a login screen. If I enter the sa user
password it connects and allows me to see the table contents.
I use MSDE Manager to manage the server, but any other option is welcomed if
I should have more control over it.
What I would like to do is that no password is asked when the users run the
Access database.
Thanks in advance.
Xavier Boneu
hi Xavier,
Xavier Boneu wrote:
> Hi!
> I've just moved a mdb to MSDE without problem. The problem comes when
> I try to open the tables I have attached via ODBC. Tables are in MSDE
> and I call them from Access.
> I get an error telling me that the user is not allowed to read (error
> 18456). Following that message, I get a login screen. If I enter the
> sa user password it connects and allows me to see the table contents.
> I use MSDE Manager to manage the server, but any other option is
> welcomed if I should have more control over it.
> What I would like to do is that no password is asked when the users
> run the Access database.
> Thanks in advance.
you have then to specify, in the ODBC management console, to use integrated
security... you have then to manage your Windows account to grant them
access to the MSDE instance, using sp_grantlogin system stored procedure (
http://msdn.microsoft.com/library/de...ga-gz_8dri.asp )
and grant each login, or WinNT group (if you can manage them at group level
instead of single account level you will gain some freedom) access to each
desired database via sp_grantdbaccess system stored procedure (
http://msdn.microsoft.com/library/de...ga-gz_290z.asp )
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
DbaMgr2k ver 0.11.1 - DbaMgr ver 0.57.0
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
-- remove DMO to reply
|||Hi!
Thanks for your info. I'll try it and post back the results.
Xavier Boneu
"Andrea Montanari" wrote:
> hi Xavier,
> Xavier Boneu wrote:
> you have then to specify, in the ODBC management console, to use integrated
> security... you have then to manage your Windows account to grant them
> access to the MSDE instance, using sp_grantlogin system stored procedure (
> http://msdn.microsoft.com/library/de...ga-gz_8dri.asp )
> and grant each login, or WinNT group (if you can manage them at group level
> instead of single account level you will gain some freedom) access to each
> desired database via sp_grantdbaccess system stored procedure (
> http://msdn.microsoft.com/library/de...ga-gz_290z.asp )
> --
> Andrea Montanari (Microsoft MVP - SQL Server)
> http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
> DbaMgr2k ver 0.11.1 - DbaMgr ver 0.57.0
> (my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
> interface)
> -- remove DMO to reply
>
>
|||Andrea,
I am trying what you suggest but it works only partialy.
I've issued the sp_grantlogin and sp_grantdbaccess but when I try to execute
a query from a client computer I get an error saying I have no permission to
SELECT the object 'mytable', database 'mydtabase' , owner 'dbo'. (#229)
What I wrote was:
EXEC sp_grantlogin 'companyname\username'
EXEC sp_grantdbaccess 'companyname\username'
but something is still missing...
Could you give me a hand with this error?
Thank you.
Xavier
Xavier Boneu
"Andrea Montanari" wrote:
> hi Xavier,
> Xavier Boneu wrote:
> you have then to specify, in the ODBC management console, to use integrated
> security... you have then to manage your Windows account to grant them
> access to the MSDE instance, using sp_grantlogin system stored procedure (
> http://msdn.microsoft.com/library/de...ga-gz_8dri.asp )
> and grant each login, or WinNT group (if you can manage them at group level
> instead of single account level you will gain some freedom) access to each
> desired database via sp_grantdbaccess system stored procedure (
> http://msdn.microsoft.com/library/de...ga-gz_290z.asp )
> --
> Andrea Montanari (Microsoft MVP - SQL Server)
> http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
> DbaMgr2k ver 0.11.1 - DbaMgr ver 0.57.0
> (my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
> interface)
> -- remove DMO to reply
>
>
|||Andrea,
I am trying what you suggest but it works only partialy.
I've issued the sp_grantlogin and sp_grantdbaccess but when I try to execute
a query from a client computer I get an error saying I have no permission to
SELECT the object 'mytable', database 'mydtabase' , owner 'dbo'. (#229)
What I wrote was:
EXEC sp_grantlogin 'companyname\username'
EXEC sp_grantdbaccess 'companyname\username'
but something is still missing...
Could you give me a hand with this error?
Thank you.
Xavier
Xavier Boneu
"Andrea Montanari" wrote:
> hi Xavier,
> Xavier Boneu wrote:
> you have then to specify, in the ODBC management console, to use integrated
> security... you have then to manage your Windows account to grant them
> access to the MSDE instance, using sp_grantlogin system stored procedure (
> http://msdn.microsoft.com/library/de...ga-gz_8dri.asp )
> and grant each login, or WinNT group (if you can manage them at group level
> instead of single account level you will gain some freedom) access to each
> desired database via sp_grantdbaccess system stored procedure (
> http://msdn.microsoft.com/library/de...ga-gz_290z.asp )
> --
> Andrea Montanari (Microsoft MVP - SQL Server)
> http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
> DbaMgr2k ver 0.11.1 - DbaMgr ver 0.57.0
> (my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
> interface)
> -- remove DMO to reply
>
>
|||hi Xavier,
Xavier Boneu wrote:
> Andrea,
> I am trying what you suggest but it works only partialy.
> I've issued the sp_grantlogin and sp_grantdbaccess but when I try to
> execute a query from a client computer I get an error saying I have
> no permission to SELECT the object 'mytable', database 'mydtabase' ,
> owner 'dbo'. (#229)
the security goes on :D
as you add a database user to your preferred database, he/she will only be
member of the Public database role, and usually that role has no kind of
access to object's data...
you can so grant the desired permission (SELECT/INSERT/UPDATE/DELETE) to the
Public database role (not recommended at all) or make him/her member of
specific builtin (or user defined) database role(s) [
http://msdn.microsoft.com/library/de..._addp_4boy.asp ,
http://msdn.microsoft.com/library/de..._addp_33s5.asp ]
...
that's to say you can make him/her member of the default db_datareader role
to permit only data browsing, eventually db_datawriter to allow data
modification, or grant him/her only specific privilegs on specific tables...
usually you make him/her member of custom db roles granting only EXEC
privileges to defined stored procedures for data retrevial/modification, and
/or SELECT/INSERT/UPDATE/DELETE privileges on user define views as it's not
a best practice to allow direct access to base table (please have a look at
http://msdn.microsoft.com/library/de...ga-gz_8odw.asp)
you can start reading at
http://www.microsoft.com/technet/pro...sp3sec00.mspx,
http://www.developer.com/tech/article.php/721441
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
DbaMgr2k ver 0.11.1 - DbaMgr ver 0.57.0
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
-- remove DMO to reply
|||Andrea,
Thanks for your help. By now, I have granted db_datareader and db_datawriter
permisions to all the users. This way they can work and now, with more time,
I will read the information you have suggested and 'study' to manage MSDE
better.
Thanks a lot!!!
Xavier Boneu
"Andrea Montanari" wrote:
> hi Xavier,
> Xavier Boneu wrote:
> the security goes on :D
> as you add a database user to your preferred database, he/she will only be
> member of the Public database role, and usually that role has no kind of
> access to object's data...
> you can so grant the desired permission (SELECT/INSERT/UPDATE/DELETE) to the
> Public database role (not recommended at all) or make him/her member of
> specific builtin (or user defined) database role(s) [
> http://msdn.microsoft.com/library/de..._addp_4boy.asp ,
> http://msdn.microsoft.com/library/de..._addp_33s5.asp ]
> ...
> that's to say you can make him/her member of the default db_datareader role
> to permit only data browsing, eventually db_datawriter to allow data
> modification, or grant him/her only specific privilegs on specific tables...
> usually you make him/her member of custom db roles granting only EXEC
> privileges to defined stored procedures for data retrevial/modification, and
> /or SELECT/INSERT/UPDATE/DELETE privileges on user define views as it's not
> a best practice to allow direct access to base table (please have a look at
> http://msdn.microsoft.com/library/de...ga-gz_8odw.asp)
> you can start reading at
> http://www.microsoft.com/technet/pro...sp3sec00.mspx,
> http://www.developer.com/tech/article.php/721441
> --
> Andrea Montanari (Microsoft MVP - SQL Server)
> http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
> DbaMgr2k ver 0.11.1 - DbaMgr ver 0.57.0
> (my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
> interface)
> -- remove DMO to reply
>
>
I've just moved a mdb to MSDE without problem. The problem comes when I try
to open the tables I have attached via ODBC. Tables are in MSDE and I call
them from Access.
I get an error telling me that the user is not allowed to read (error
18456). Following that message, I get a login screen. If I enter the sa user
password it connects and allows me to see the table contents.
I use MSDE Manager to manage the server, but any other option is welcomed if
I should have more control over it.
What I would like to do is that no password is asked when the users run the
Access database.
Thanks in advance.
Xavier Boneu
hi Xavier,
Xavier Boneu wrote:
> Hi!
> I've just moved a mdb to MSDE without problem. The problem comes when
> I try to open the tables I have attached via ODBC. Tables are in MSDE
> and I call them from Access.
> I get an error telling me that the user is not allowed to read (error
> 18456). Following that message, I get a login screen. If I enter the
> sa user password it connects and allows me to see the table contents.
> I use MSDE Manager to manage the server, but any other option is
> welcomed if I should have more control over it.
> What I would like to do is that no password is asked when the users
> run the Access database.
> Thanks in advance.
you have then to specify, in the ODBC management console, to use integrated
security... you have then to manage your Windows account to grant them
access to the MSDE instance, using sp_grantlogin system stored procedure (
http://msdn.microsoft.com/library/de...ga-gz_8dri.asp )
and grant each login, or WinNT group (if you can manage them at group level
instead of single account level you will gain some freedom) access to each
desired database via sp_grantdbaccess system stored procedure (
http://msdn.microsoft.com/library/de...ga-gz_290z.asp )
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
DbaMgr2k ver 0.11.1 - DbaMgr ver 0.57.0
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
-- remove DMO to reply
|||Hi!
Thanks for your info. I'll try it and post back the results.
Xavier Boneu
"Andrea Montanari" wrote:
> hi Xavier,
> Xavier Boneu wrote:
> you have then to specify, in the ODBC management console, to use integrated
> security... you have then to manage your Windows account to grant them
> access to the MSDE instance, using sp_grantlogin system stored procedure (
> http://msdn.microsoft.com/library/de...ga-gz_8dri.asp )
> and grant each login, or WinNT group (if you can manage them at group level
> instead of single account level you will gain some freedom) access to each
> desired database via sp_grantdbaccess system stored procedure (
> http://msdn.microsoft.com/library/de...ga-gz_290z.asp )
> --
> Andrea Montanari (Microsoft MVP - SQL Server)
> http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
> DbaMgr2k ver 0.11.1 - DbaMgr ver 0.57.0
> (my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
> interface)
> -- remove DMO to reply
>
>
|||Andrea,
I am trying what you suggest but it works only partialy.
I've issued the sp_grantlogin and sp_grantdbaccess but when I try to execute
a query from a client computer I get an error saying I have no permission to
SELECT the object 'mytable', database 'mydtabase' , owner 'dbo'. (#229)
What I wrote was:
EXEC sp_grantlogin 'companyname\username'
EXEC sp_grantdbaccess 'companyname\username'
but something is still missing...
Could you give me a hand with this error?
Thank you.
Xavier
Xavier Boneu
"Andrea Montanari" wrote:
> hi Xavier,
> Xavier Boneu wrote:
> you have then to specify, in the ODBC management console, to use integrated
> security... you have then to manage your Windows account to grant them
> access to the MSDE instance, using sp_grantlogin system stored procedure (
> http://msdn.microsoft.com/library/de...ga-gz_8dri.asp )
> and grant each login, or WinNT group (if you can manage them at group level
> instead of single account level you will gain some freedom) access to each
> desired database via sp_grantdbaccess system stored procedure (
> http://msdn.microsoft.com/library/de...ga-gz_290z.asp )
> --
> Andrea Montanari (Microsoft MVP - SQL Server)
> http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
> DbaMgr2k ver 0.11.1 - DbaMgr ver 0.57.0
> (my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
> interface)
> -- remove DMO to reply
>
>
|||Andrea,
I am trying what you suggest but it works only partialy.
I've issued the sp_grantlogin and sp_grantdbaccess but when I try to execute
a query from a client computer I get an error saying I have no permission to
SELECT the object 'mytable', database 'mydtabase' , owner 'dbo'. (#229)
What I wrote was:
EXEC sp_grantlogin 'companyname\username'
EXEC sp_grantdbaccess 'companyname\username'
but something is still missing...
Could you give me a hand with this error?
Thank you.
Xavier
Xavier Boneu
"Andrea Montanari" wrote:
> hi Xavier,
> Xavier Boneu wrote:
> you have then to specify, in the ODBC management console, to use integrated
> security... you have then to manage your Windows account to grant them
> access to the MSDE instance, using sp_grantlogin system stored procedure (
> http://msdn.microsoft.com/library/de...ga-gz_8dri.asp )
> and grant each login, or WinNT group (if you can manage them at group level
> instead of single account level you will gain some freedom) access to each
> desired database via sp_grantdbaccess system stored procedure (
> http://msdn.microsoft.com/library/de...ga-gz_290z.asp )
> --
> Andrea Montanari (Microsoft MVP - SQL Server)
> http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
> DbaMgr2k ver 0.11.1 - DbaMgr ver 0.57.0
> (my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
> interface)
> -- remove DMO to reply
>
>
|||hi Xavier,
Xavier Boneu wrote:
> Andrea,
> I am trying what you suggest but it works only partialy.
> I've issued the sp_grantlogin and sp_grantdbaccess but when I try to
> execute a query from a client computer I get an error saying I have
> no permission to SELECT the object 'mytable', database 'mydtabase' ,
> owner 'dbo'. (#229)
the security goes on :D
as you add a database user to your preferred database, he/she will only be
member of the Public database role, and usually that role has no kind of
access to object's data...
you can so grant the desired permission (SELECT/INSERT/UPDATE/DELETE) to the
Public database role (not recommended at all) or make him/her member of
specific builtin (or user defined) database role(s) [
http://msdn.microsoft.com/library/de..._addp_4boy.asp ,
http://msdn.microsoft.com/library/de..._addp_33s5.asp ]
...
that's to say you can make him/her member of the default db_datareader role
to permit only data browsing, eventually db_datawriter to allow data
modification, or grant him/her only specific privilegs on specific tables...
usually you make him/her member of custom db roles granting only EXEC
privileges to defined stored procedures for data retrevial/modification, and
/or SELECT/INSERT/UPDATE/DELETE privileges on user define views as it's not
a best practice to allow direct access to base table (please have a look at
http://msdn.microsoft.com/library/de...ga-gz_8odw.asp)
you can start reading at
http://www.microsoft.com/technet/pro...sp3sec00.mspx,
http://www.developer.com/tech/article.php/721441
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
DbaMgr2k ver 0.11.1 - DbaMgr ver 0.57.0
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
-- remove DMO to reply
|||Andrea,
Thanks for your help. By now, I have granted db_datareader and db_datawriter
permisions to all the users. This way they can work and now, with more time,
I will read the information you have suggested and 'study' to manage MSDE
better.
Thanks a lot!!!
Xavier Boneu
"Andrea Montanari" wrote:
> hi Xavier,
> Xavier Boneu wrote:
> the security goes on :D
> as you add a database user to your preferred database, he/she will only be
> member of the Public database role, and usually that role has no kind of
> access to object's data...
> you can so grant the desired permission (SELECT/INSERT/UPDATE/DELETE) to the
> Public database role (not recommended at all) or make him/her member of
> specific builtin (or user defined) database role(s) [
> http://msdn.microsoft.com/library/de..._addp_4boy.asp ,
> http://msdn.microsoft.com/library/de..._addp_33s5.asp ]
> ...
> that's to say you can make him/her member of the default db_datareader role
> to permit only data browsing, eventually db_datawriter to allow data
> modification, or grant him/her only specific privilegs on specific tables...
> usually you make him/her member of custom db roles granting only EXEC
> privileges to defined stored procedures for data retrevial/modification, and
> /or SELECT/INSERT/UPDATE/DELETE privileges on user define views as it's not
> a best practice to allow direct access to base table (please have a look at
> http://msdn.microsoft.com/library/de...ga-gz_8odw.asp)
> you can start reading at
> http://www.microsoft.com/technet/pro...sp3sec00.mspx,
> http://www.developer.com/tech/article.php/721441
> --
> Andrea Montanari (Microsoft MVP - SQL Server)
> http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
> DbaMgr2k ver 0.11.1 - DbaMgr ver 0.57.0
> (my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
> interface)
> -- remove DMO to reply
>
>
Subscribe to:
Posts (Atom)