August 26, 2011 at 11:15 am
GilaMonster (8/26/2011)
Can anyone possibly see how an update WHERE <identity column> = <constant value> could possibly update the entire table?http://www.sqlservercentral.com/Forums/Topic1166215-1292-1.aspx
Without clicking the link (I should know better!) one way is if all the identity values were the same. No doubt there's a unique index or something to prevent that, but there's my not-so-useful suggestion 🙂
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
August 26, 2011 at 11:18 am
SQLkiwi (8/26/2011)
GilaMonster (8/26/2011)
Can anyone possibly see how an update WHERE <identity column> = <constant value> could possibly update the entire table?http://www.sqlservercentral.com/Forums/Topic1166215-1292-1.aspx
Without clicking the link (I should know better!) one way is if all the identity values were the same. No doubt there's a unique index or something to prevent that, but there's my not-so-useful suggestion 🙂
That did occur to me, and that table does not (I believe) have a primary key (unless someone made it nonclustered), his comments seemed to suggest that the rows were unique, and I'd made enough of a point (with a demo) that it doesn't just happen.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
August 26, 2011 at 11:22 am
GilaMonster (8/26/2011)
SQLkiwi (8/26/2011)
GilaMonster (8/26/2011)
Can anyone possibly see how an update WHERE <identity column> = <constant value> could possibly update the entire table?http://www.sqlservercentral.com/Forums/Topic1166215-1292-1.aspx
Without clicking the link (I should know better!) one way is if all the identity values were the same. No doubt there's a unique index or something to prevent that, but there's my not-so-useful suggestion 🙂
That did occur to me, and that table does not (I believe) have a primary key (unless someone made it nonclustered), his comments seemed to suggest that the rows were unique, and I'd made enough of a point (with a demo) that it doesn't just happen.
The only way I can see it happening is the first thing you said in response.
--------------------------------------
When you encounter a problem, if the solution isn't readily evident go back to the start and check your assumptions.
--------------------------------------
It’s unpleasantly like being drunk.
What’s so unpleasant about being drunk?
You ask a glass of water. -- Douglas Adams
August 26, 2011 at 11:23 am
I just want to say that I love it here.
Work is having me post at another forum, nameless, where I'm the lone voice suggesting that, oh, maybe setting all your databases to simple recovery is not the way to "fix" database log problems.
That sound you're hearing on the East Coast is neither another earthquake or the surf from hurricane Irene. It's my teeth grinding.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
August 26, 2011 at 11:24 am
GilaMonster (8/26/2011)
That did occur to me, and that table does not (I believe) have a primary key (unless someone made it nonclustered), his comments seemed to suggest that the rows were unique, and I'd made enough of a point (with a demo) that it doesn't just happen.
Read that thread now. It would be funny if someone else had just run a concurrent update (without a where clause) that happened to set all values in that column to the one value he was about to update - that would explain it, but it would be impossible to prove. I do like his final comment about it not coming off well - I can just imagine!
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
August 26, 2011 at 11:26 am
Grant Fritchey (8/26/2011)
Work is having me post at another forum, nameless, where I'm the lone voice suggesting that, oh, maybe setting all your databases to simple recovery is not the way to "fix" database log problems.
Let me guess, a development forum?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
August 26, 2011 at 11:27 am
Since the topic is Back up, I thought I would ask a stupid question. If you do a back up to local disk, you will see that there is a pretty significant pagefile usage. If you are doing it over the network, you dont see it. Does anybody know why that is?
-Roy
August 26, 2011 at 11:27 am
SQLkiwi (8/26/2011)
GilaMonster (8/26/2011)
That did occur to me, and that table does not (I believe) have a primary key (unless someone made it nonclustered), his comments seemed to suggest that the rows were unique, and I'd made enough of a point (with a demo) that it doesn't just happen.Read that thread now. It would be funny if someone else had just run a concurrent update (without a where clause) that happened to set all values in that column to the one value he was about to update - that would explain it, but it would be impossible to prove.
However identities can't be updated, so if that happened, the column's not an identity in the first place.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
August 26, 2011 at 11:30 am
GilaMonster (8/26/2011)
Grant Fritchey (8/26/2011)
Work is having me post at another forum, nameless, where I'm the lone voice suggesting that, oh, maybe setting all your databases to simple recovery is not the way to "fix" database log problems.Let me guess, a development forum?
It's actually primarily systems people, but yeah, a bunch of developers too.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
August 26, 2011 at 11:32 am
GilaMonster (8/26/2011)
However identities can't be updated, so if that happened, the column's not an identity in the first place.
Gah.
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
August 26, 2011 at 11:52 am
Grant Fritchey (8/26/2011)
Need the advice of the Thread.Putting together an article on the most common backup errors (apart from not having one) and how to avoid them. I have four so far:
Backing up files instead of creating backups (.mdf, ldf, you know)
Running out of space on disk
Log Filling up
Only having differential backups available.
Are there any glaring ones I'm missing here?
Having the wrong backup strategy to begin with (e.g. allowable data loss = 30 min but hourly log backup, point in time recovery available for the last 24 hrs, but two day full backup kept and all log backups deleted right after the daily full backup).
August 26, 2011 at 11:54 am
Stefan Krzywicki (8/26/2011)
Here's something everyone in IT should be aware ofYour decision making ability deteriorates as you make more decisions
Thanks for this Stefan, shared it around a bit myself this morning.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
August 26, 2011 at 12:46 pm
Grant Fritchey (8/26/2011)
SQLRNNR (8/26/2011)
Grant Fritchey (8/26/2011)
Need the advice of the Thread.Putting together an article on the most common backup errors (apart from not having one) and how to avoid them. I have four so far:
Backing up files instead of creating backups (.mdf, ldf, you know)
Running out of space on disk
Log Filling up
Only having differential backups available.
Are there any glaring ones I'm missing here?
Backing up across network.
I guess Log Filling up takes care of Full Recovery mode w/out tran log backups
Oh, right, across the network. Gotta ask though, do we really think that's as big a deal as we used to? I only ask because I've been running backups for years to "local" drives that were actually SAN drives that required a network hop to get there. Further, it's never been an issue. Why would simply backing up to a network share be so much worse (and I'm asking that with the full knowledge that I've seen it behave badly, but it was a long time ago). Are we perpetuating old advice on that one? If not, it's a good one.
I don't know how common it still is, but I'm fighting with this issue routinely right now. Network backup of a 100GB db normally takes 2 hrs 5 min. Except when it takes 6, or 7, or 8 hours. Or I get wonderful "lost connection to the..." messages and the backups fail. And that with 2GB of connectivity (teamed 1GB NICs).
It shouldn't take anywhere near that long for something of that size.
We don't have the physical space in the SQL server to put more storage (yes, I want another controller and separate drives) in for me to use for the backups - my server admin just double-checked on that for me. So...
Yes, cross network backups are still an issue in some situations. (I think mine is actually that the server we're backing up to is low on memory, so we're going to test with a different destination, but I'd lose a lot less sleep if those backups were local or SAN based.)
-Ki
August 26, 2011 at 12:51 pm
Grant Fritchey (8/26/2011)
SQLRNNR (8/26/2011)
Grant Fritchey (8/26/2011)
Need the advice of the Thread.Putting together an article on the most common backup errors (apart from not having one) and how to avoid them. I have four so far:
Backing up files instead of creating backups (.mdf, ldf, you know)
Running out of space on disk
Log Filling up
Only having differential backups available.
Are there any glaring ones I'm missing here?
Backing up across network.
I guess Log Filling up takes care of Full Recovery mode w/out tran log backups
Oh, right, across the network. Gotta ask though, do we really think that's as big a deal as we used to? I only ask because I've been running backups for years to "local" drives that were actually SAN drives that required a network hop to get there. Further, it's never been an issue. Why would simply backing up to a network share be so much worse (and I'm asking that with the full knowledge that I've seen it behave badly, but it was a long time ago). Are we perpetuating old advice on that one? If not, it's a good one
I've done it, but with occasional issues on big backups +2GB, I know that isn't big for some of you but it was for me. It was also SQL 7 & 2000, I haven't seen issues with 2005+. I know Brent Ozar recommends doing backups across the network, http://www.brentozar.com/archive/2008/09/back-up-your-database-to-the-network-not-local-disk/. He's a pretty smart guy.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
August 26, 2011 at 12:54 pm
Kiara (8/26/2011)
Grant Fritchey (8/26/2011)
SQLRNNR (8/26/2011)
Grant Fritchey (8/26/2011)
Need the advice of the Thread.Putting together an article on the most common backup errors (apart from not having one) and how to avoid them. I have four so far:
Backing up files instead of creating backups (.mdf, ldf, you know)
Running out of space on disk
Log Filling up
Only having differential backups available.
Are there any glaring ones I'm missing here?
Backing up across network.
I guess Log Filling up takes care of Full Recovery mode w/out tran log backups
Oh, right, across the network. Gotta ask though, do we really think that's as big a deal as we used to? I only ask because I've been running backups for years to "local" drives that were actually SAN drives that required a network hop to get there. Further, it's never been an issue. Why would simply backing up to a network share be so much worse (and I'm asking that with the full knowledge that I've seen it behave badly, but it was a long time ago). Are we perpetuating old advice on that one? If not, it's a good one.
I don't know how common it still is, but I'm fighting with this issue routinely right now. Network backup of a 100GB db normally takes 2 hrs 5 min. Except when it takes 6, or 7, or 8 hours. Or I get wonderful "lost connection to the..." messages and the backups fail. And that with 2GB of connectivity (teamed 1GB NICs).
It shouldn't take anywhere near that long for something of that size.
We don't have the physical space in the SQL server to put more storage (yes, I want another controller and separate drives) in for me to use for the backups - my server admin just double-checked on that for me. So...
Yes, cross network backups are still an issue in some situations. (I think mine is actually that the server we're backing up to is low on memory, so we're going to test with a different destination, but I'd lose a lot less sleep if those backups were local or SAN based.)
I don't know your free space on the SQL box, but I would backup local, then run a job to move the backup immediately after it is done. I have had to do this on SQL boxes that were space limited. This reduced backup times from 4hrs down to <=30min for the full backup plus move off to network.
The backups we found were also more reliable after that too.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
Viewing 15 posts - 29,371 through 29,385 (of 66,712 total)
You must be logged in to reply to this topic. Login to reply