Viewing 15 posts - 20,866 through 20,880 (of 49,552 total)
SPID 30? Sure about that, because that's a system SPID, not a user one.
To commit a transaction you just run COMMIT TRANSACTION from the same connection that you started it....
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
January 20, 2012 at 12:17 pm
UPDATE <someTable>
SET <whatever>
FROM <SomeTable> INNER JOIN (<subquery containing the row number>) ON <join condition>
If you want real code, post your table definitions in a usable state, I'd rather not try...
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
January 20, 2012 at 11:20 am
SELECT SSN, Effective_Date, Row_Number() OVER (Partition by SSN ORDER BY Effective_Date) AS ContractNumber
FROM <some table>
You can use that as the basis of an update
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
January 20, 2012 at 11:03 am
TimeToShine (1/20/2012)
Please, please, please read up on nomalisation. What you've got there is a nightmare waiting to happen. Think about how you'd go about adding or removing an attribute from...
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
January 20, 2012 at 9:35 am
Where did you get database mirroring from?
p.s. In case anyone missed it, the OP's on SQL 2005 and the log is in auto-truncate mode (because it's been backed up WITH...
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
January 20, 2012 at 8:49 am
Check the windows event log for any disk or IO-related errors.
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
January 20, 2012 at 8:37 am
Simha24 (1/20/2012)
BACKUP LOG ... WITH TRUNCATE ONLY.
Will It Truncate Active Log by...
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
January 20, 2012 at 8:34 am
patrickmcginnis59 (1/20/2012)
Thanks! I didn't even consider deletes. I'll have to buy some popcorn and watch the movie!
Not a movie. One of the MCM videos. That particular one is about 40...
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
January 20, 2012 at 7:41 am
Grant Fritchey (1/20/2012)
GilaMonster (1/20/2012)
Grant Fritchey (1/20/2012)
When it finds that, it identifies the least costly query by it's estimated cost and chooses that as the victim and initiates a rollback.
Just...
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
January 20, 2012 at 7:36 am
deepkt (1/20/2012)
1. Transaction logs are not recorded for the table variables. Hence, they are out of scope of the transaction mechanism
Half true. They are out of transaction scope, but that...
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
January 20, 2012 at 7:32 am
Please read through this - Managing Transaction Logs[/url] and http://www.sqlservercentral.com/articles/Transaction+Log/72488/
If you don't need point-in-time recovery (the ability to restore to a point-in-time or point-of-failure), switch the database to simple recovery...
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
January 20, 2012 at 7:07 am
patrickmcginnis59 (1/20/2012)
Whats the downside of having duplicate rows in nonleaf nodes? Aren't they still going to point to the correct child nodes that they're the parent of?
Yes, they will, however...
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
January 20, 2012 at 7:03 am
Ninja's_RGR'us (1/20/2012)
I live far enough. Not worth to waste 4 days on this, but you on the other hand live much closer to her ;-).
Much closer? Maybe for certain...
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
January 20, 2012 at 6:25 am
Grant Fritchey (1/20/2012)
When it finds that, it identifies the least costly query by it's estimated cost and chooses that as the victim and initiates a rollback.
Just one minor clarification,...
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
January 20, 2012 at 6:21 am
Why?
In simple recovery the log is automatically reused, in full recovery log reuse requires a log backup. Seems counter-intuitive to switch to full recovery model unless you need point-in-time recovery...
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
January 20, 2012 at 5:26 am
Viewing 15 posts - 20,866 through 20,880 (of 49,552 total)