Hello:
A couple of times on this board, I posed the question as to why expired
backup files may not get deleted from a server folder automatically despite
the fact that the maintenance plan in SQL 2000 tells the server to delete
files older than x number of days.
I want to thank everyone for pointing me to that really good article by Bill
Hollinshead. The part of the article about reading the maintenance plan's
history report solved the problem for me.
You see, by reading the report at my client site, I found a third reason as
to why backup files do not get deleted. (The first two reasons are
Permissions and Sharing Violation.)
The client did not have enough space on his server's hard drive. Therefore,
the last few databases in the list of databases were not getting deleted.
So, here is what a SQL maintenance plan does in this case where there is not
enough space and the backups of those last few databases terminate abnormally
therefore.
The maintenace plan quits and does not bother deleting the expired backups.
basically, if the server has run out of space, the maintenance plan does not
try to delete expired backups!
In a way, this should be added as a third reason as to why backup files do
not get deleted!
Thanks, again!
childofthe1980s
On Feb 8, 10:15 pm, childofthe1980s
<childofthe19...@.discussions.microsoft.com> wrote:
> Hello:
> A couple of times on this board, I posed the question as to why expired
> backup files may not get deleted from a server folder automatically despite
> the fact that the maintenance plan in SQL 2000 tells the server to delete
> files older than x number of days.
> I want to thank everyone for pointing me to that really good article by Bill
> Hollinshead. The part of the article about reading the maintenance plan's
> history report solved the problem for me.
> You see, by reading the report at my client site, I found a third reason as
> to why backup files do not get deleted. (The first two reasons are
> Permissions and Sharing Violation.)
> The client did not have enough space on his server's hard drive. Therefore,
> the last few databases in the list of databases were not getting deleted.
> So, here is what a SQL maintenance plan does in this case where there is not
> enough space and the backups of those last few databases terminate abnormally
> therefore.
> The maintenace plan quits and does not bother deleting the expired backups.
> basically, if the server has run out of space, the maintenance plan does not
> try to delete expired backups!
> In a way, this should be added as a third reason as to why backup files do
> not get deleted!
> Thanks, again!
> childofthe1980s
This is one argument I have for NOT using the maintenance plans - you
have no control over the inner workings. I'd encourage everyone to
build their OWN maintenance routines. Write your own backup scripts,
your own reindex scripts, your own cleanup scripts. I have examples
of all three here:
http://realsqlguy.blogspot.com/2007/02/automating-database-backups.html
http://realsqlguy.blogspot.com/2007/02/cleaning-up-old-files.html
http://realsqlguy.blogspot.com/2007/02/smart-index-defragmentation.html
Showing posts with label backup. Show all posts
Showing posts with label backup. Show all posts
Monday, March 19, 2012
another reason why backup files do not get deleted
Hello:
A couple of times on this board, I posed the question as to why expired
backup files may not get deleted from a server folder automatically despite
the fact that the maintenance plan in SQL 2000 tells the server to delete
files older than x number of days.
I want to thank everyone for pointing me to that really good article by Bill
Hollinshead. The part of the article about reading the maintenance plan's
history report solved the problem for me.
You see, by reading the report at my client site, I found a third reason as
to why backup files do not get deleted. (The first two reasons are
Permissions and Sharing Violation.)
The client did not have enough space on his server's hard drive. Therefore,
the last few databases in the list of databases were not getting deleted.
So, here is what a SQL maintenance plan does in this case where there is not
enough space and the backups of those last few databases terminate abnormall
y
therefore.
The maintenace plan quits and does not bother deleting the expired backups.
basically, if the server has run out of space, the maintenance plan does not
try to delete expired backups!
In a way, this should be added as a third reason as to why backup files do
not get deleted!
Thanks, again!
childofthe1980sThanks for reporting back to us. This is essentially why I would like a "con
tinue on error" option.
Basically, this is the same as connect issue 125025. Re-reading this report,
I now see that they are
considering it for sp2, so it might be worth checking if they implemented su
ch an option...
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"childofthe1980s" <childofthe1980s@.discussions.microsoft.com> wrote in messa
ge
news:B8454FDE-F33B-4BAD-BF70-7C18261A4677@.microsoft.com...
> Hello:
> A couple of times on this board, I posed the question as to why expired
> backup files may not get deleted from a server folder automatically despit
e
> the fact that the maintenance plan in SQL 2000 tells the server to delete
> files older than x number of days.
> I want to thank everyone for pointing me to that really good article by Bi
ll
> Hollinshead. The part of the article about reading the maintenance plan's
> history report solved the problem for me.
> You see, by reading the report at my client site, I found a third reason a
s
> to why backup files do not get deleted. (The first two reasons are
> Permissions and Sharing Violation.)
> The client did not have enough space on his server's hard drive. Therefor
e,
> the last few databases in the list of databases were not getting deleted.
> So, here is what a SQL maintenance plan does in this case where there is n
ot
> enough space and the backups of those last few databases terminate abnorma
lly
> therefore.
> The maintenace plan quits and does not bother deleting the expired backups
.
> basically, if the server has run out of space, the maintenance plan does n
ot
> try to delete expired backups!
> In a way, this should be added as a third reason as to why backup files do
> not get deleted!
> Thanks, again!
> childofthe1980s|||On Feb 8, 10:15 pm, childofthe1980s
<childofthe19...@.discussions.microsoft.com> wrote:
> Hello:
> A couple of times on this board, I posed the question as to why expired
> backup files may not get deleted from a server folder automatically despit
e
> the fact that the maintenance plan in SQL 2000 tells the server to delete
> files older than x number of days.
> I want to thank everyone for pointing me to that really good article by Bi
ll
> Hollinshead. The part of the article about reading the maintenance plan's
> history report solved the problem for me.
> You see, by reading the report at my client site, I found a third reason a
s
> to why backup files do not get deleted. (The first two reasons are
> Permissions and Sharing Violation.)
> The client did not have enough space on his server's hard drive. Therefor
e,
> the last few databases in the list of databases were not getting deleted.
> So, here is what a SQL maintenance plan does in this case where there is n
ot
> enough space and the backups of those last few databases terminate abnorma
lly
> therefore.
> The maintenace plan quits and does not bother deleting the expired backups
.
> basically, if the server has run out of space, the maintenance plan does n
ot
> try to delete expired backups!
> In a way, this should be added as a third reason as to why backup files do
> not get deleted!
> Thanks, again!
> childofthe1980s
This is one argument I have for NOT using the maintenance plans - you
have no control over the inner workings. I'd encourage everyone to
build their OWN maintenance routines. Write your own backup scripts,
your own reindex scripts, your own cleanup scripts. I have examples
of all three here:
http://realsqlguy.blogspot.com/2007...se-backups.html
http://realsqlguy.blogspot.com/2007...-old-files.html
http://realsqlguy.blogspot.com/2007...gmentation.html
A couple of times on this board, I posed the question as to why expired
backup files may not get deleted from a server folder automatically despite
the fact that the maintenance plan in SQL 2000 tells the server to delete
files older than x number of days.
I want to thank everyone for pointing me to that really good article by Bill
Hollinshead. The part of the article about reading the maintenance plan's
history report solved the problem for me.
You see, by reading the report at my client site, I found a third reason as
to why backup files do not get deleted. (The first two reasons are
Permissions and Sharing Violation.)
The client did not have enough space on his server's hard drive. Therefore,
the last few databases in the list of databases were not getting deleted.
So, here is what a SQL maintenance plan does in this case where there is not
enough space and the backups of those last few databases terminate abnormall
y
therefore.
The maintenace plan quits and does not bother deleting the expired backups.
basically, if the server has run out of space, the maintenance plan does not
try to delete expired backups!
In a way, this should be added as a third reason as to why backup files do
not get deleted!
Thanks, again!
childofthe1980sThanks for reporting back to us. This is essentially why I would like a "con
tinue on error" option.
Basically, this is the same as connect issue 125025. Re-reading this report,
I now see that they are
considering it for sp2, so it might be worth checking if they implemented su
ch an option...
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"childofthe1980s" <childofthe1980s@.discussions.microsoft.com> wrote in messa
ge
news:B8454FDE-F33B-4BAD-BF70-7C18261A4677@.microsoft.com...
> Hello:
> A couple of times on this board, I posed the question as to why expired
> backup files may not get deleted from a server folder automatically despit
e
> the fact that the maintenance plan in SQL 2000 tells the server to delete
> files older than x number of days.
> I want to thank everyone for pointing me to that really good article by Bi
ll
> Hollinshead. The part of the article about reading the maintenance plan's
> history report solved the problem for me.
> You see, by reading the report at my client site, I found a third reason a
s
> to why backup files do not get deleted. (The first two reasons are
> Permissions and Sharing Violation.)
> The client did not have enough space on his server's hard drive. Therefor
e,
> the last few databases in the list of databases were not getting deleted.
> So, here is what a SQL maintenance plan does in this case where there is n
ot
> enough space and the backups of those last few databases terminate abnorma
lly
> therefore.
> The maintenace plan quits and does not bother deleting the expired backups
.
> basically, if the server has run out of space, the maintenance plan does n
ot
> try to delete expired backups!
> In a way, this should be added as a third reason as to why backup files do
> not get deleted!
> Thanks, again!
> childofthe1980s|||On Feb 8, 10:15 pm, childofthe1980s
<childofthe19...@.discussions.microsoft.com> wrote:
> Hello:
> A couple of times on this board, I posed the question as to why expired
> backup files may not get deleted from a server folder automatically despit
e
> the fact that the maintenance plan in SQL 2000 tells the server to delete
> files older than x number of days.
> I want to thank everyone for pointing me to that really good article by Bi
ll
> Hollinshead. The part of the article about reading the maintenance plan's
> history report solved the problem for me.
> You see, by reading the report at my client site, I found a third reason a
s
> to why backup files do not get deleted. (The first two reasons are
> Permissions and Sharing Violation.)
> The client did not have enough space on his server's hard drive. Therefor
e,
> the last few databases in the list of databases were not getting deleted.
> So, here is what a SQL maintenance plan does in this case where there is n
ot
> enough space and the backups of those last few databases terminate abnorma
lly
> therefore.
> The maintenace plan quits and does not bother deleting the expired backups
.
> basically, if the server has run out of space, the maintenance plan does n
ot
> try to delete expired backups!
> In a way, this should be added as a third reason as to why backup files do
> not get deleted!
> Thanks, again!
> childofthe1980s
This is one argument I have for NOT using the maintenance plans - you
have no control over the inner workings. I'd encourage everyone to
build their OWN maintenance routines. Write your own backup scripts,
your own reindex scripts, your own cleanup scripts. I have examples
of all three here:
http://realsqlguy.blogspot.com/2007...se-backups.html
http://realsqlguy.blogspot.com/2007...-old-files.html
http://realsqlguy.blogspot.com/2007...gmentation.html
another reason why backup files do not get deleted
Hello:
A couple of times on this board, I posed the question as to why expired
backup files may not get deleted from a server folder automatically despite
the fact that the maintenance plan in SQL 2000 tells the server to delete
files older than x number of days.
I want to thank everyone for pointing me to that really good article by Bill
Hollinshead. The part of the article about reading the maintenance plan's
history report solved the problem for me.
You see, by reading the report at my client site, I found a third reason as
to why backup files do not get deleted. (The first two reasons are
Permissions and Sharing Violation.)
The client did not have enough space on his server's hard drive. Therefore,
the last few databases in the list of databases were not getting deleted.
So, here is what a SQL maintenance plan does in this case where there is not
enough space and the backups of those last few databases terminate abnormally
therefore.
The maintenace plan quits and does not bother deleting the expired backups.
basically, if the server has run out of space, the maintenance plan does not
try to delete expired backups!
In a way, this should be added as a third reason as to why backup files do
not get deleted!
Thanks, again!
childofthe1980sThanks for reporting back to us. This is essentially why I would like a "continue on error" option.
Basically, this is the same as connect issue 125025. Re-reading this report, I now see that they are
considering it for sp2, so it might be worth checking if they implemented such an option...
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"childofthe1980s" <childofthe1980s@.discussions.microsoft.com> wrote in message
news:B8454FDE-F33B-4BAD-BF70-7C18261A4677@.microsoft.com...
> Hello:
> A couple of times on this board, I posed the question as to why expired
> backup files may not get deleted from a server folder automatically despite
> the fact that the maintenance plan in SQL 2000 tells the server to delete
> files older than x number of days.
> I want to thank everyone for pointing me to that really good article by Bill
> Hollinshead. The part of the article about reading the maintenance plan's
> history report solved the problem for me.
> You see, by reading the report at my client site, I found a third reason as
> to why backup files do not get deleted. (The first two reasons are
> Permissions and Sharing Violation.)
> The client did not have enough space on his server's hard drive. Therefore,
> the last few databases in the list of databases were not getting deleted.
> So, here is what a SQL maintenance plan does in this case where there is not
> enough space and the backups of those last few databases terminate abnormally
> therefore.
> The maintenace plan quits and does not bother deleting the expired backups.
> basically, if the server has run out of space, the maintenance plan does not
> try to delete expired backups!
> In a way, this should be added as a third reason as to why backup files do
> not get deleted!
> Thanks, again!
> childofthe1980s|||On Feb 8, 10:15 pm, childofthe1980s
<childofthe19...@.discussions.microsoft.com> wrote:
> Hello:
> A couple of times on this board, I posed the question as to why expired
> backup files may not get deleted from a server folder automatically despite
> the fact that the maintenance plan in SQL 2000 tells the server to delete
> files older than x number of days.
> I want to thank everyone for pointing me to that really good article by Bill
> Hollinshead. The part of the article about reading the maintenance plan's
> history report solved the problem for me.
> You see, by reading the report at my client site, I found a third reason as
> to why backup files do not get deleted. (The first two reasons are
> Permissions and Sharing Violation.)
> The client did not have enough space on his server's hard drive. Therefore,
> the last few databases in the list of databases were not getting deleted.
> So, here is what a SQL maintenance plan does in this case where there is not
> enough space and the backups of those last few databases terminate abnormally
> therefore.
> The maintenace plan quits and does not bother deleting the expired backups.
> basically, if the server has run out of space, the maintenance plan does not
> try to delete expired backups!
> In a way, this should be added as a third reason as to why backup files do
> not get deleted!
> Thanks, again!
> childofthe1980s
This is one argument I have for NOT using the maintenance plans - you
have no control over the inner workings. I'd encourage everyone to
build their OWN maintenance routines. Write your own backup scripts,
your own reindex scripts, your own cleanup scripts. I have examples
of all three here:
http://realsqlguy.blogspot.com/2007/02/automating-database-backups.html
http://realsqlguy.blogspot.com/2007/02/cleaning-up-old-files.html
http://realsqlguy.blogspot.com/2007/02/smart-index-defragmentation.html
A couple of times on this board, I posed the question as to why expired
backup files may not get deleted from a server folder automatically despite
the fact that the maintenance plan in SQL 2000 tells the server to delete
files older than x number of days.
I want to thank everyone for pointing me to that really good article by Bill
Hollinshead. The part of the article about reading the maintenance plan's
history report solved the problem for me.
You see, by reading the report at my client site, I found a third reason as
to why backup files do not get deleted. (The first two reasons are
Permissions and Sharing Violation.)
The client did not have enough space on his server's hard drive. Therefore,
the last few databases in the list of databases were not getting deleted.
So, here is what a SQL maintenance plan does in this case where there is not
enough space and the backups of those last few databases terminate abnormally
therefore.
The maintenace plan quits and does not bother deleting the expired backups.
basically, if the server has run out of space, the maintenance plan does not
try to delete expired backups!
In a way, this should be added as a third reason as to why backup files do
not get deleted!
Thanks, again!
childofthe1980sThanks for reporting back to us. This is essentially why I would like a "continue on error" option.
Basically, this is the same as connect issue 125025. Re-reading this report, I now see that they are
considering it for sp2, so it might be worth checking if they implemented such an option...
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"childofthe1980s" <childofthe1980s@.discussions.microsoft.com> wrote in message
news:B8454FDE-F33B-4BAD-BF70-7C18261A4677@.microsoft.com...
> Hello:
> A couple of times on this board, I posed the question as to why expired
> backup files may not get deleted from a server folder automatically despite
> the fact that the maintenance plan in SQL 2000 tells the server to delete
> files older than x number of days.
> I want to thank everyone for pointing me to that really good article by Bill
> Hollinshead. The part of the article about reading the maintenance plan's
> history report solved the problem for me.
> You see, by reading the report at my client site, I found a third reason as
> to why backup files do not get deleted. (The first two reasons are
> Permissions and Sharing Violation.)
> The client did not have enough space on his server's hard drive. Therefore,
> the last few databases in the list of databases were not getting deleted.
> So, here is what a SQL maintenance plan does in this case where there is not
> enough space and the backups of those last few databases terminate abnormally
> therefore.
> The maintenace plan quits and does not bother deleting the expired backups.
> basically, if the server has run out of space, the maintenance plan does not
> try to delete expired backups!
> In a way, this should be added as a third reason as to why backup files do
> not get deleted!
> Thanks, again!
> childofthe1980s|||On Feb 8, 10:15 pm, childofthe1980s
<childofthe19...@.discussions.microsoft.com> wrote:
> Hello:
> A couple of times on this board, I posed the question as to why expired
> backup files may not get deleted from a server folder automatically despite
> the fact that the maintenance plan in SQL 2000 tells the server to delete
> files older than x number of days.
> I want to thank everyone for pointing me to that really good article by Bill
> Hollinshead. The part of the article about reading the maintenance plan's
> history report solved the problem for me.
> You see, by reading the report at my client site, I found a third reason as
> to why backup files do not get deleted. (The first two reasons are
> Permissions and Sharing Violation.)
> The client did not have enough space on his server's hard drive. Therefore,
> the last few databases in the list of databases were not getting deleted.
> So, here is what a SQL maintenance plan does in this case where there is not
> enough space and the backups of those last few databases terminate abnormally
> therefore.
> The maintenace plan quits and does not bother deleting the expired backups.
> basically, if the server has run out of space, the maintenance plan does not
> try to delete expired backups!
> In a way, this should be added as a third reason as to why backup files do
> not get deleted!
> Thanks, again!
> childofthe1980s
This is one argument I have for NOT using the maintenance plans - you
have no control over the inner workings. I'd encourage everyone to
build their OWN maintenance routines. Write your own backup scripts,
your own reindex scripts, your own cleanup scripts. I have examples
of all three here:
http://realsqlguy.blogspot.com/2007/02/automating-database-backups.html
http://realsqlguy.blogspot.com/2007/02/cleaning-up-old-files.html
http://realsqlguy.blogspot.com/2007/02/smart-index-defragmentation.html
Thursday, March 8, 2012
Another Login after restoring database
Hello all,
I have created a backup of a database and Restored as a new database in another machine.
Primary login of the database was X , now after restoring as a new database I dont want X as the login name i want it to be Y ...
how to do that ?
I can create other login as y and Give necessary premissions...but the Problem is i have to manually issue all the permissions to the new user which i feel is a time taking process.
Please let me know about it..
Thanks and Regards
saiI am not sure but u can try this :
script the login and user for the database in question,after generating the script for the user X,delete the the user X from the database ,Replace the user/login name X from the script with Y and run the script on the database.
I think this should work after some enhancements in the script.|||Use roles!
Or sp_change_users_login SP
I have created a backup of a database and Restored as a new database in another machine.
Primary login of the database was X , now after restoring as a new database I dont want X as the login name i want it to be Y ...
how to do that ?
I can create other login as y and Give necessary premissions...but the Problem is i have to manually issue all the permissions to the new user which i feel is a time taking process.
Please let me know about it..
Thanks and Regards
saiI am not sure but u can try this :
script the login and user for the database in question,after generating the script for the user X,delete the the user X from the database ,Replace the user/login name X from the script with Y and run the script on the database.
I think this should work after some enhancements in the script.|||Use roles!
Or sp_change_users_login SP
Wednesday, March 7, 2012
another backup quick question
Hi
What does Backup set expiry do? Apart from mark the set as "Not being allowed to restore", does it delete the file?
cheers
ICW
If you're backing up to tape it sets the date the backup expires so the tape can be overwritten on the next cycle.
I personally recommend you back up to disk, then back up those disk files to tape along with your regular network backup.
Another Backup Question
I am using SQL Server 2000. I have a database that is 230GB that I backup
nightly using the Maintenance Plan. Up until last week it was working fine
and was completing in less than two hours. Last week I applied the monthly
critical updates and rebooted the server. Since then, it now takes 12 hours
to complete. I tried deleting and rebuilding the Maintenance Plan but it
still takes 12 hours. I can't figure what has changed. How long should a
backup of this size take? I thought I'd check here for some suggestions
before I'm forced to go back and uninstall the critical updates.
Have you tried eliminating anything with maintenance plans
by create a job with a T-SQL step that does just the backup
of the database?
Are there any errors or warnings in the windows event logs
when the backups are executing?
Have you run Perfmon/System Monitor or created a counter log
to run when the backups run to check for disk, memory, cpu
issues? You could also query the system tables in msdb to
see how long the backup actually took - I wouldn't rely on
what the maintenance plan reports.
-Sue
On Fri, 3 Nov 2006 17:32:27 -0500, "Scott" <someone at
si-intl.com> wrote:
>I am using SQL Server 2000. I have a database that is 230GB that I backup
>nightly using the Maintenance Plan. Up until last week it was working fine
>and was completing in less than two hours. Last week I applied the monthly
>critical updates and rebooted the server. Since then, it now takes 12 hours
>to complete. I tried deleting and rebuilding the Maintenance Plan but it
>still takes 12 hours. I can't figure what has changed. How long should a
>backup of this size take? I thought I'd check here for some suggestions
>before I'm forced to go back and uninstall the critical updates.
>
nightly using the Maintenance Plan. Up until last week it was working fine
and was completing in less than two hours. Last week I applied the monthly
critical updates and rebooted the server. Since then, it now takes 12 hours
to complete. I tried deleting and rebuilding the Maintenance Plan but it
still takes 12 hours. I can't figure what has changed. How long should a
backup of this size take? I thought I'd check here for some suggestions
before I'm forced to go back and uninstall the critical updates.
Have you tried eliminating anything with maintenance plans
by create a job with a T-SQL step that does just the backup
of the database?
Are there any errors or warnings in the windows event logs
when the backups are executing?
Have you run Perfmon/System Monitor or created a counter log
to run when the backups run to check for disk, memory, cpu
issues? You could also query the system tables in msdb to
see how long the backup actually took - I wouldn't rely on
what the maintenance plan reports.
-Sue
On Fri, 3 Nov 2006 17:32:27 -0500, "Scott" <someone at
si-intl.com> wrote:
>I am using SQL Server 2000. I have a database that is 230GB that I backup
>nightly using the Maintenance Plan. Up until last week it was working fine
>and was completing in less than two hours. Last week I applied the monthly
>critical updates and rebooted the server. Since then, it now takes 12 hours
>to complete. I tried deleting and rebuilding the Maintenance Plan but it
>still takes 12 hours. I can't figure what has changed. How long should a
>backup of this size take? I thought I'd check here for some suggestions
>before I'm forced to go back and uninstall the critical updates.
>
another backup question
why are 'transaction log' and 'file and file group' dimmed (not available)
on the general tab of the backup window? (right-click on database, all
tasks, backup database)
I am backing up to a file device and need to set a schedule of 1 daily full
backup and backup of the transaction logs every 1/2 hour.
any help is appreciated.
Because the database is in simple recovery mode.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"djc" <noone@.nowhere.com> wrote in message news:ucbsl59UEHA.3420@.TK2MSFTNGP12.phx.gbl...
> why are 'transaction log' and 'file and file group' dimmed (not available)
> on the general tab of the backup window? (right-click on database, all
> tasks, backup database)
> I am backing up to a file device and need to set a schedule of 1 daily full
> backup and backup of the transaction logs every 1/2 hour.
> any help is appreciated.
>
|||Thank you!
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:uWjcUB%23UEHA.1048@.tk2msftngp13.phx.gbl...
> Because the database is in simple recovery mode.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "djc" <noone@.nowhere.com> wrote in message
news:ucbsl59UEHA.3420@.TK2MSFTNGP12.phx.gbl...[vbcol=seagreen]
available)[vbcol=seagreen]
full
>
on the general tab of the backup window? (right-click on database, all
tasks, backup database)
I am backing up to a file device and need to set a schedule of 1 daily full
backup and backup of the transaction logs every 1/2 hour.
any help is appreciated.
Because the database is in simple recovery mode.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"djc" <noone@.nowhere.com> wrote in message news:ucbsl59UEHA.3420@.TK2MSFTNGP12.phx.gbl...
> why are 'transaction log' and 'file and file group' dimmed (not available)
> on the general tab of the backup window? (right-click on database, all
> tasks, backup database)
> I am backing up to a file device and need to set a schedule of 1 daily full
> backup and backup of the transaction logs every 1/2 hour.
> any help is appreciated.
>
|||Thank you!
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:uWjcUB%23UEHA.1048@.tk2msftngp13.phx.gbl...
> Because the database is in simple recovery mode.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "djc" <noone@.nowhere.com> wrote in message
news:ucbsl59UEHA.3420@.TK2MSFTNGP12.phx.gbl...[vbcol=seagreen]
available)[vbcol=seagreen]
full
>
Another Backup Question
I am using SQL Server 2000. I have a database that is 230GB that I backup
nightly using the Maintenance Plan. Up until last week it was working fine
and was completing in less than two hours. Last week I applied the monthly
critical updates and rebooted the server. Since then, it now takes 12 hours
to complete. I tried deleting and rebuilding the Maintenance Plan but it
still takes 12 hours. I can't figure what has changed. How long should a
backup of this size take? I thought I'd check here for some suggestions
before I'm forced to go back and uninstall the critical updates.Have you tried eliminating anything with maintenance plans
by create a job with a T-SQL step that does just the backup
of the database?
Are there any errors or warnings in the windows event logs
when the backups are executing?
Have you run Perfmon/System Monitor or created a counter log
to run when the backups run to check for disk, memory, cpu
issues? You could also query the system tables in msdb to
see how long the backup actually took - I wouldn't rely on
what the maintenance plan reports.
-Sue
On Fri, 3 Nov 2006 17:32:27 -0500, "Scott" <someone at
si-intl.com> wrote:
>I am using SQL Server 2000. I have a database that is 230GB that I backup
>nightly using the Maintenance Plan. Up until last week it was working fine
>and was completing in less than two hours. Last week I applied the monthly
>critical updates and rebooted the server. Since then, it now takes 12 hour
s
>to complete. I tried deleting and rebuilding the Maintenance Plan but it
>still takes 12 hours. I can't figure what has changed. How long should a
>backup of this size take? I thought I'd check here for some suggestions
>before I'm forced to go back and uninstall the critical updates.
>
nightly using the Maintenance Plan. Up until last week it was working fine
and was completing in less than two hours. Last week I applied the monthly
critical updates and rebooted the server. Since then, it now takes 12 hours
to complete. I tried deleting and rebuilding the Maintenance Plan but it
still takes 12 hours. I can't figure what has changed. How long should a
backup of this size take? I thought I'd check here for some suggestions
before I'm forced to go back and uninstall the critical updates.Have you tried eliminating anything with maintenance plans
by create a job with a T-SQL step that does just the backup
of the database?
Are there any errors or warnings in the windows event logs
when the backups are executing?
Have you run Perfmon/System Monitor or created a counter log
to run when the backups run to check for disk, memory, cpu
issues? You could also query the system tables in msdb to
see how long the backup actually took - I wouldn't rely on
what the maintenance plan reports.
-Sue
On Fri, 3 Nov 2006 17:32:27 -0500, "Scott" <someone at
si-intl.com> wrote:
>I am using SQL Server 2000. I have a database that is 230GB that I backup
>nightly using the Maintenance Plan. Up until last week it was working fine
>and was completing in less than two hours. Last week I applied the monthly
>critical updates and rebooted the server. Since then, it now takes 12 hour
s
>to complete. I tried deleting and rebuilding the Maintenance Plan but it
>still takes 12 hours. I can't figure what has changed. How long should a
>backup of this size take? I thought I'd check here for some suggestions
>before I'm forced to go back and uninstall the critical updates.
>
Another Backup Question
I am using SQL Server 2000. I have a database that is 230GB that I backup
nightly using the Maintenance Plan. Up until last week it was working fine
and was completing in less than two hours. Last week I applied the monthly
critical updates and rebooted the server. Since then, it now takes 12 hours
to complete. I tried deleting and rebuilding the Maintenance Plan but it
still takes 12 hours. I can't figure what has changed. How long should a
backup of this size take? I thought I'd check here for some suggestions
before I'm forced to go back and uninstall the critical updates.Have you tried eliminating anything with maintenance plans
by create a job with a T-SQL step that does just the backup
of the database?
Are there any errors or warnings in the windows event logs
when the backups are executing?
Have you run Perfmon/System Monitor or created a counter log
to run when the backups run to check for disk, memory, cpu
issues? You could also query the system tables in msdb to
see how long the backup actually took - I wouldn't rely on
what the maintenance plan reports.
-Sue
On Fri, 3 Nov 2006 17:32:27 -0500, "Scott" <someone at
si-intl.com> wrote:
>I am using SQL Server 2000. I have a database that is 230GB that I backup
>nightly using the Maintenance Plan. Up until last week it was working fine
>and was completing in less than two hours. Last week I applied the monthly
>critical updates and rebooted the server. Since then, it now takes 12 hours
>to complete. I tried deleting and rebuilding the Maintenance Plan but it
>still takes 12 hours. I can't figure what has changed. How long should a
>backup of this size take? I thought I'd check here for some suggestions
>before I'm forced to go back and uninstall the critical updates.
>
nightly using the Maintenance Plan. Up until last week it was working fine
and was completing in less than two hours. Last week I applied the monthly
critical updates and rebooted the server. Since then, it now takes 12 hours
to complete. I tried deleting and rebuilding the Maintenance Plan but it
still takes 12 hours. I can't figure what has changed. How long should a
backup of this size take? I thought I'd check here for some suggestions
before I'm forced to go back and uninstall the critical updates.Have you tried eliminating anything with maintenance plans
by create a job with a T-SQL step that does just the backup
of the database?
Are there any errors or warnings in the windows event logs
when the backups are executing?
Have you run Perfmon/System Monitor or created a counter log
to run when the backups run to check for disk, memory, cpu
issues? You could also query the system tables in msdb to
see how long the backup actually took - I wouldn't rely on
what the maintenance plan reports.
-Sue
On Fri, 3 Nov 2006 17:32:27 -0500, "Scott" <someone at
si-intl.com> wrote:
>I am using SQL Server 2000. I have a database that is 230GB that I backup
>nightly using the Maintenance Plan. Up until last week it was working fine
>and was completing in less than two hours. Last week I applied the monthly
>critical updates and rebooted the server. Since then, it now takes 12 hours
>to complete. I tried deleting and rebuilding the Maintenance Plan but it
>still takes 12 hours. I can't figure what has changed. How long should a
>backup of this size take? I thought I'd check here for some suggestions
>before I'm forced to go back and uninstall the critical updates.
>
another backup question
why are 'transaction log' and 'file and file group' dimmed (not available)
on the general tab of the backup window? (right-click on database, all
tasks, backup database)
I am backing up to a file device and need to set a schedule of 1 daily full
backup and backup of the transaction logs every 1/2 hour.
any help is appreciated.Because the database is in simple recovery mode.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"djc" <noone@.nowhere.com> wrote in message news:ucbsl59UEHA.3420@.TK2MSFTNGP12.phx.gbl...
> why are 'transaction log' and 'file and file group' dimmed (not available)
> on the general tab of the backup window? (right-click on database, all
> tasks, backup database)
> I am backing up to a file device and need to set a schedule of 1 daily full
> backup and backup of the transaction logs every 1/2 hour.
> any help is appreciated.
>|||Thank you!
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:uWjcUB%23UEHA.1048@.tk2msftngp13.phx.gbl...
> Because the database is in simple recovery mode.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "djc" <noone@.nowhere.com> wrote in message
news:ucbsl59UEHA.3420@.TK2MSFTNGP12.phx.gbl...
> > why are 'transaction log' and 'file and file group' dimmed (not
available)
> > on the general tab of the backup window? (right-click on database, all
> > tasks, backup database)
> >
> > I am backing up to a file device and need to set a schedule of 1 daily
full
> > backup and backup of the transaction logs every 1/2 hour.
> >
> > any help is appreciated.
> >
> >
>
on the general tab of the backup window? (right-click on database, all
tasks, backup database)
I am backing up to a file device and need to set a schedule of 1 daily full
backup and backup of the transaction logs every 1/2 hour.
any help is appreciated.Because the database is in simple recovery mode.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"djc" <noone@.nowhere.com> wrote in message news:ucbsl59UEHA.3420@.TK2MSFTNGP12.phx.gbl...
> why are 'transaction log' and 'file and file group' dimmed (not available)
> on the general tab of the backup window? (right-click on database, all
> tasks, backup database)
> I am backing up to a file device and need to set a schedule of 1 daily full
> backup and backup of the transaction logs every 1/2 hour.
> any help is appreciated.
>|||Thank you!
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:uWjcUB%23UEHA.1048@.tk2msftngp13.phx.gbl...
> Because the database is in simple recovery mode.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "djc" <noone@.nowhere.com> wrote in message
news:ucbsl59UEHA.3420@.TK2MSFTNGP12.phx.gbl...
> > why are 'transaction log' and 'file and file group' dimmed (not
available)
> > on the general tab of the backup window? (right-click on database, all
> > tasks, backup database)
> >
> > I am backing up to a file device and need to set a schedule of 1 daily
full
> > backup and backup of the transaction logs every 1/2 hour.
> >
> > any help is appreciated.
> >
> >
>
Sunday, February 19, 2012
Analyze Backup Server
I am trying to analyze/identify the bottleneck on our new backup
server. I was wondering if someone could recommend some performance
counters to watch.
Right now I am watching "% Write Time" I am averaging around
"500" for this measure while copying a 50GB database file. It is
taking almost twice as long to copy the file to a different server as
opposed to a different direct attached array. The % disk write counter
is similar on both servers. Am I wrong to assume that the physical
disks are the bottleneck (due to high performance counter ratings)?
Does anyone know some general guide lines on real world throughput on
the following? What kinds of things should I be looking at and/or
asking our IT department?
Gigabit dedicated network
Perc4 (I read on Dells site that it is 320MB/s which translates to
1.1TB / hour)
Raid 5 using 6 - 300MB disks (10,000 RPM)
Other bottleneck candidate?
Server Configuration
Windows Server 2003
Dell PowerEdge 2850
Perc4 - Raid 5
PowerVault 200s
Gigabit networkdaveg.01@.gmail.com wrote:
> I am trying to analyze/identify the bottleneck on our new backup
> server. I was wondering if someone could recommend some performance
> counters to watch.
> Right now I am watching "% Write Time" I am averaging around
> "500" for this measure while copying a 50GB database file. It is
> taking almost twice as long to copy the file to a different server as
> opposed to a different direct attached array. The % disk write counter
> is similar on both servers. Am I wrong to assume that the physical
> disks are the bottleneck (due to high performance counter ratings)?
>
> Does anyone know some general guide lines on real world throughput on
> the following? What kinds of things should I be looking at and/or
> asking our IT department?
> Gigabit dedicated network
> Perc4 (I read on Dells site that it is 320MB/s which translates to
> 1.1TB / hour)
> Raid 5 using 6 - 300MB disks (10,000 RPM)
> Other bottleneck candidate?
>
> Server Configuration
> Windows Server 2003
> Dell PowerEdge 2850
> Perc4 - Raid 5
> PowerVault 200s
> Gigabit network
>
5 of 6 disks at 60MB/sec is 300MB/sec for sustained transfer at
the disk level. You might verify that the disk transfer rate
is full Ultra320. A cabling or termination problem might
reduce the transfer rate.
The PERC4 listed here, has a 64 bit 66MHz interface, and as long
as it is fully utilizing the bus, the bus should not be a limit.
Sometimes a bus segment can be slowed, by the presence of a slower
card. So you may want to check the card configuration and bus
structure of your server.
http://www.jjwei.com/shop/item.asp?itemid=78
But your Gigabit Ethernet only does 125MB/sec theoretical in one
direction, so a single transaction with your server will be limited
by the network. A local transfer on the server itself could go faster.
Plenty of little things to check.
Paul|||Assuming everything is configured properly, how long would I expect it
to take to transfer a 50GB file to the backup server?
If the perfmon coutner "%disk write" goes above 100 should I assume
that the physical disks are the bottleneck?
Paul wrote:
> daveg.01@.gmail.com wrote:
> > I am trying to analyze/identify the bottleneck on our new backup
> > server. I was wondering if someone could recommend some performance
> > counters to watch.
> >
> > Right now I am watching "% Write Time" I am averaging around
> > "500" for this measure while copying a 50GB database file. It is
> > taking almost twice as long to copy the file to a different server as
> > opposed to a different direct attached array. The % disk write counter
> > is similar on both servers. Am I wrong to assume that the physical
> > disks are the bottleneck (due to high performance counter ratings)?
> >
> >
> > Does anyone know some general guide lines on real world throughput on
> > the following? What kinds of things should I be looking at and/or
> > asking our IT department?
> >
> > Gigabit dedicated network
> > Perc4 (I read on Dells site that it is 320MB/s which translates to
> > 1.1TB / hour)
> > Raid 5 using 6 - 300MB disks (10,000 RPM)
> > Other bottleneck candidate?
> >
> >
> > Server Configuration
> > Windows Server 2003
> > Dell PowerEdge 2850
> > Perc4 - Raid 5
> > PowerVault 200s
> > Gigabit network
> >
> 5 of 6 disks at 60MB/sec is 300MB/sec for sustained transfer at
> the disk level. You might verify that the disk transfer rate
> is full Ultra320. A cabling or termination problem might
> reduce the transfer rate.
> The PERC4 listed here, has a 64 bit 66MHz interface, and as long
> as it is fully utilizing the bus, the bus should not be a limit.
> Sometimes a bus segment can be slowed, by the presence of a slower
> card. So you may want to check the card configuration and bus
> structure of your server.
> http://www.jjwei.com/shop/item.asp?itemid=78
> But your Gigabit Ethernet only does 125MB/sec theoretical in one
> direction, so a single transaction with your server will be limited
> by the network. A local transfer on the server itself could go faster.
> Plenty of little things to check.
> Paul|||Dave wrote:
> Assuming everything is configured properly, how long would I expect it
> to take to transfer a 50GB file to the backup server?
> If the perfmon coutner "%disk write" goes above 100 should I assume
> that the physical disks are the bottleneck?
>
> Paul wrote:
> > daveg.01@.gmail.com wrote:
> > > I am trying to analyze/identify the bottleneck on our new backup
> > > server. I was wondering if someone could recommend some performance
> > > counters to watch.
> > >
> > > Right now I am watching "% Write Time" I am averaging around
> > > "500" for this measure while copying a 50GB database file. It is
> > > taking almost twice as long to copy the file to a different server as
> > > opposed to a different direct attached array. The % disk write counter
> > > is similar on both servers. Am I wrong to assume that the physical
> > > disks are the bottleneck (due to high performance counter ratings)?
> > >
> > >
> > > Does anyone know some general guide lines on real world throughput on
> > > the following? What kinds of things should I be looking at and/or
> > > asking our IT department?
> > >
> > > Gigabit dedicated network
> > > Perc4 (I read on Dells site that it is 320MB/s which translates to
> > > 1.1TB / hour)
> > > Raid 5 using 6 - 300MB disks (10,000 RPM)
> > > Other bottleneck candidate?
> > >
> > >
> > > Server Configuration
> > > Windows Server 2003
> > > Dell PowerEdge 2850
> > > Perc4 - Raid 5
> > > PowerVault 200s
> > > Gigabit network
> > >
> >
> > 5 of 6 disks at 60MB/sec is 300MB/sec for sustained transfer at
> > the disk level. You might verify that the disk transfer rate
> > is full Ultra320. A cabling or termination problem might
> > reduce the transfer rate.
> >
> > The PERC4 listed here, has a 64 bit 66MHz interface, and as long
> > as it is fully utilizing the bus, the bus should not be a limit.
> > Sometimes a bus segment can be slowed, by the presence of a slower
> > card. So you may want to check the card configuration and bus
> > structure of your server.
> >
> > http://www.jjwei.com/shop/item.asp?itemid=78
> >
> > But your Gigabit Ethernet only does 125MB/sec theoretical in one
> > direction, so a single transaction with your server will be limited
> > by the network. A local transfer on the server itself could go faster.
> > Plenty of little things to check.
> >
> > Paul|||I am trying to analyze/identify the bottleneck on our new backup
server. I was wondering if someone could recommend some performance
counters to watch.
Right now I am watching "% Write Time" I am averaging around
"500" for this measure while copying a 50GB database file. It is
taking almost twice as long to copy the file to a different server as
opposed to a different direct attached array. The % disk write counter
is similar on both servers. Am I wrong to assume that the physical
disks are the bottleneck (due to high performance counter ratings)?
Does anyone know some general guide lines on real world throughput on
the following? What kinds of things should I be looking at and/or
asking our IT department?
Gigabit dedicated network
Perc4 (I read on Dells site that it is 320MB/s which translates to
1.1TB / hour)
Raid 5 using 6 - 300MB disks (10,000 RPM)
Other bottleneck candidate?
Server Configuration
Windows Server 2003
Dell PowerEdge 2850
Perc4 - Raid 5
PowerVault 200s
Gigabit network|||Dave wrote:
> Assuming everything is configured properly, how long would I expect it
> to take to transfer a 50GB file to the backup server?
> If the perfmon coutner "%disk write" goes above 100 should I assume
> that the physical disks are the bottleneck?
If the transfer is over the network, the limit is 125MB/sec. To transfer
a 50GB file takes 400 seconds.
Paul
> Paul wrote:
>> daveg.01@.gmail.com wrote:
>> I am trying to analyze/identify the bottleneck on our new backup
>> server. I was wondering if someone could recommend some performance
>> counters to watch.
>> Right now I am watching "% Write Time" I am averaging around
>> "500" for this measure while copying a 50GB database file. It is
>> taking almost twice as long to copy the file to a different server as
>> opposed to a different direct attached array. The % disk write counter
>> is similar on both servers. Am I wrong to assume that the physical
>> disks are the bottleneck (due to high performance counter ratings)?
>>
>> Does anyone know some general guide lines on real world throughput on
>> the following? What kinds of things should I be looking at and/or
>> asking our IT department?
>> Gigabit dedicated network
>> Perc4 (I read on Dells site that it is 320MB/s which translates to
>> 1.1TB / hour)
>> Raid 5 using 6 - 300MB disks (10,000 RPM)
>> Other bottleneck candidate?
>>
>> Server Configuration
>> Windows Server 2003
>> Dell PowerEdge 2850
>> Perc4 - Raid 5
>> PowerVault 200s
>> Gigabit network
>> 5 of 6 disks at 60MB/sec is 300MB/sec for sustained transfer at
>> the disk level. You might verify that the disk transfer rate
>> is full Ultra320. A cabling or termination problem might
>> reduce the transfer rate.
>> The PERC4 listed here, has a 64 bit 66MHz interface, and as long
>> as it is fully utilizing the bus, the bus should not be a limit.
>> Sometimes a bus segment can be slowed, by the presence of a slower
>> card. So you may want to check the card configuration and bus
>> structure of your server.
>> http://www.jjwei.com/shop/item.asp?itemid=78
>> But your Gigabit Ethernet only does 125MB/sec theoretical in one
>> direction, so a single transaction with your server will be limited
>> by the network. A local transfer on the server itself could go faster.
>> Plenty of little things to check.
>> Paul
>|||On Sun, 14 Jan 2007 06:54:21 -0500, Paul <nospam@.needed.com>
wrote:
>Dave wrote:
>> Assuming everything is configured properly, how long would I expect it
>> to take to transfer a 50GB file to the backup server?
>> If the perfmon coutner "%disk write" goes above 100 should I assume
>> that the physical disks are the bottleneck?
>If the transfer is over the network, the limit is 125MB/sec. To transfer
>a 50GB file takes 400 seconds.
> Paul
>
I'd be surprised if a real transfer averages over 90MB/s,
especially if TCP/IP
server. I was wondering if someone could recommend some performance
counters to watch.
Right now I am watching "% Write Time" I am averaging around
"500" for this measure while copying a 50GB database file. It is
taking almost twice as long to copy the file to a different server as
opposed to a different direct attached array. The % disk write counter
is similar on both servers. Am I wrong to assume that the physical
disks are the bottleneck (due to high performance counter ratings)?
Does anyone know some general guide lines on real world throughput on
the following? What kinds of things should I be looking at and/or
asking our IT department?
Gigabit dedicated network
Perc4 (I read on Dells site that it is 320MB/s which translates to
1.1TB / hour)
Raid 5 using 6 - 300MB disks (10,000 RPM)
Other bottleneck candidate?
Server Configuration
Windows Server 2003
Dell PowerEdge 2850
Perc4 - Raid 5
PowerVault 200s
Gigabit networkdaveg.01@.gmail.com wrote:
> I am trying to analyze/identify the bottleneck on our new backup
> server. I was wondering if someone could recommend some performance
> counters to watch.
> Right now I am watching "% Write Time" I am averaging around
> "500" for this measure while copying a 50GB database file. It is
> taking almost twice as long to copy the file to a different server as
> opposed to a different direct attached array. The % disk write counter
> is similar on both servers. Am I wrong to assume that the physical
> disks are the bottleneck (due to high performance counter ratings)?
>
> Does anyone know some general guide lines on real world throughput on
> the following? What kinds of things should I be looking at and/or
> asking our IT department?
> Gigabit dedicated network
> Perc4 (I read on Dells site that it is 320MB/s which translates to
> 1.1TB / hour)
> Raid 5 using 6 - 300MB disks (10,000 RPM)
> Other bottleneck candidate?
>
> Server Configuration
> Windows Server 2003
> Dell PowerEdge 2850
> Perc4 - Raid 5
> PowerVault 200s
> Gigabit network
>
5 of 6 disks at 60MB/sec is 300MB/sec for sustained transfer at
the disk level. You might verify that the disk transfer rate
is full Ultra320. A cabling or termination problem might
reduce the transfer rate.
The PERC4 listed here, has a 64 bit 66MHz interface, and as long
as it is fully utilizing the bus, the bus should not be a limit.
Sometimes a bus segment can be slowed, by the presence of a slower
card. So you may want to check the card configuration and bus
structure of your server.
http://www.jjwei.com/shop/item.asp?itemid=78
But your Gigabit Ethernet only does 125MB/sec theoretical in one
direction, so a single transaction with your server will be limited
by the network. A local transfer on the server itself could go faster.
Plenty of little things to check.
Paul|||Assuming everything is configured properly, how long would I expect it
to take to transfer a 50GB file to the backup server?
If the perfmon coutner "%disk write" goes above 100 should I assume
that the physical disks are the bottleneck?
Paul wrote:
> daveg.01@.gmail.com wrote:
> > I am trying to analyze/identify the bottleneck on our new backup
> > server. I was wondering if someone could recommend some performance
> > counters to watch.
> >
> > Right now I am watching "% Write Time" I am averaging around
> > "500" for this measure while copying a 50GB database file. It is
> > taking almost twice as long to copy the file to a different server as
> > opposed to a different direct attached array. The % disk write counter
> > is similar on both servers. Am I wrong to assume that the physical
> > disks are the bottleneck (due to high performance counter ratings)?
> >
> >
> > Does anyone know some general guide lines on real world throughput on
> > the following? What kinds of things should I be looking at and/or
> > asking our IT department?
> >
> > Gigabit dedicated network
> > Perc4 (I read on Dells site that it is 320MB/s which translates to
> > 1.1TB / hour)
> > Raid 5 using 6 - 300MB disks (10,000 RPM)
> > Other bottleneck candidate?
> >
> >
> > Server Configuration
> > Windows Server 2003
> > Dell PowerEdge 2850
> > Perc4 - Raid 5
> > PowerVault 200s
> > Gigabit network
> >
> 5 of 6 disks at 60MB/sec is 300MB/sec for sustained transfer at
> the disk level. You might verify that the disk transfer rate
> is full Ultra320. A cabling or termination problem might
> reduce the transfer rate.
> The PERC4 listed here, has a 64 bit 66MHz interface, and as long
> as it is fully utilizing the bus, the bus should not be a limit.
> Sometimes a bus segment can be slowed, by the presence of a slower
> card. So you may want to check the card configuration and bus
> structure of your server.
> http://www.jjwei.com/shop/item.asp?itemid=78
> But your Gigabit Ethernet only does 125MB/sec theoretical in one
> direction, so a single transaction with your server will be limited
> by the network. A local transfer on the server itself could go faster.
> Plenty of little things to check.
> Paul|||Dave wrote:
> Assuming everything is configured properly, how long would I expect it
> to take to transfer a 50GB file to the backup server?
> If the perfmon coutner "%disk write" goes above 100 should I assume
> that the physical disks are the bottleneck?
>
> Paul wrote:
> > daveg.01@.gmail.com wrote:
> > > I am trying to analyze/identify the bottleneck on our new backup
> > > server. I was wondering if someone could recommend some performance
> > > counters to watch.
> > >
> > > Right now I am watching "% Write Time" I am averaging around
> > > "500" for this measure while copying a 50GB database file. It is
> > > taking almost twice as long to copy the file to a different server as
> > > opposed to a different direct attached array. The % disk write counter
> > > is similar on both servers. Am I wrong to assume that the physical
> > > disks are the bottleneck (due to high performance counter ratings)?
> > >
> > >
> > > Does anyone know some general guide lines on real world throughput on
> > > the following? What kinds of things should I be looking at and/or
> > > asking our IT department?
> > >
> > > Gigabit dedicated network
> > > Perc4 (I read on Dells site that it is 320MB/s which translates to
> > > 1.1TB / hour)
> > > Raid 5 using 6 - 300MB disks (10,000 RPM)
> > > Other bottleneck candidate?
> > >
> > >
> > > Server Configuration
> > > Windows Server 2003
> > > Dell PowerEdge 2850
> > > Perc4 - Raid 5
> > > PowerVault 200s
> > > Gigabit network
> > >
> >
> > 5 of 6 disks at 60MB/sec is 300MB/sec for sustained transfer at
> > the disk level. You might verify that the disk transfer rate
> > is full Ultra320. A cabling or termination problem might
> > reduce the transfer rate.
> >
> > The PERC4 listed here, has a 64 bit 66MHz interface, and as long
> > as it is fully utilizing the bus, the bus should not be a limit.
> > Sometimes a bus segment can be slowed, by the presence of a slower
> > card. So you may want to check the card configuration and bus
> > structure of your server.
> >
> > http://www.jjwei.com/shop/item.asp?itemid=78
> >
> > But your Gigabit Ethernet only does 125MB/sec theoretical in one
> > direction, so a single transaction with your server will be limited
> > by the network. A local transfer on the server itself could go faster.
> > Plenty of little things to check.
> >
> > Paul|||I am trying to analyze/identify the bottleneck on our new backup
server. I was wondering if someone could recommend some performance
counters to watch.
Right now I am watching "% Write Time" I am averaging around
"500" for this measure while copying a 50GB database file. It is
taking almost twice as long to copy the file to a different server as
opposed to a different direct attached array. The % disk write counter
is similar on both servers. Am I wrong to assume that the physical
disks are the bottleneck (due to high performance counter ratings)?
Does anyone know some general guide lines on real world throughput on
the following? What kinds of things should I be looking at and/or
asking our IT department?
Gigabit dedicated network
Perc4 (I read on Dells site that it is 320MB/s which translates to
1.1TB / hour)
Raid 5 using 6 - 300MB disks (10,000 RPM)
Other bottleneck candidate?
Server Configuration
Windows Server 2003
Dell PowerEdge 2850
Perc4 - Raid 5
PowerVault 200s
Gigabit network|||Dave wrote:
> Assuming everything is configured properly, how long would I expect it
> to take to transfer a 50GB file to the backup server?
> If the perfmon coutner "%disk write" goes above 100 should I assume
> that the physical disks are the bottleneck?
If the transfer is over the network, the limit is 125MB/sec. To transfer
a 50GB file takes 400 seconds.
Paul
> Paul wrote:
>> daveg.01@.gmail.com wrote:
>> I am trying to analyze/identify the bottleneck on our new backup
>> server. I was wondering if someone could recommend some performance
>> counters to watch.
>> Right now I am watching "% Write Time" I am averaging around
>> "500" for this measure while copying a 50GB database file. It is
>> taking almost twice as long to copy the file to a different server as
>> opposed to a different direct attached array. The % disk write counter
>> is similar on both servers. Am I wrong to assume that the physical
>> disks are the bottleneck (due to high performance counter ratings)?
>>
>> Does anyone know some general guide lines on real world throughput on
>> the following? What kinds of things should I be looking at and/or
>> asking our IT department?
>> Gigabit dedicated network
>> Perc4 (I read on Dells site that it is 320MB/s which translates to
>> 1.1TB / hour)
>> Raid 5 using 6 - 300MB disks (10,000 RPM)
>> Other bottleneck candidate?
>>
>> Server Configuration
>> Windows Server 2003
>> Dell PowerEdge 2850
>> Perc4 - Raid 5
>> PowerVault 200s
>> Gigabit network
>> 5 of 6 disks at 60MB/sec is 300MB/sec for sustained transfer at
>> the disk level. You might verify that the disk transfer rate
>> is full Ultra320. A cabling or termination problem might
>> reduce the transfer rate.
>> The PERC4 listed here, has a 64 bit 66MHz interface, and as long
>> as it is fully utilizing the bus, the bus should not be a limit.
>> Sometimes a bus segment can be slowed, by the presence of a slower
>> card. So you may want to check the card configuration and bus
>> structure of your server.
>> http://www.jjwei.com/shop/item.asp?itemid=78
>> But your Gigabit Ethernet only does 125MB/sec theoretical in one
>> direction, so a single transaction with your server will be limited
>> by the network. A local transfer on the server itself could go faster.
>> Plenty of little things to check.
>> Paul
>|||On Sun, 14 Jan 2007 06:54:21 -0500, Paul <nospam@.needed.com>
wrote:
>Dave wrote:
>> Assuming everything is configured properly, how long would I expect it
>> to take to transfer a 50GB file to the backup server?
>> If the perfmon coutner "%disk write" goes above 100 should I assume
>> that the physical disks are the bottleneck?
>If the transfer is over the network, the limit is 125MB/sec. To transfer
>a 50GB file takes 400 seconds.
> Paul
>
I'd be surprised if a real transfer averages over 90MB/s,
especially if TCP/IP
Sunday, February 12, 2012
Analysis services backup and restore problem.
I'm backing up an analysis services database to an abf file and then restoring it to another database, with a different name, on the same server. However, when I process the database that I originally backed up, the cubes in the restored database are deleted?
I can only think that the objects in the database have a unique identifier and these are the same in both databases, and when I process the original database it's deleting both sets of cubes, but only rebuilding the ones in the database I'm processing.
The only way that I can think of out of this is to restore the database to another server, which I don't want to do.
Any ideas would be much appreciated.
This sounds like a bug. There is a special code during restore which is supposed to change the unique ids you refer to in order for them to stay unique. Sounds like for whatever reason in your scenario this code doesn't work.
Subscribe to:
Posts (Atom)