Drill down slow: update

Showing posts with label update. Show all posts

Wednesday, March 21, 2012

DownDate ? from SP4 to SP3

Sorry SUBJECT was backwards...
Can it be done' How can I UNDO my SQL2000 SP4 update and go back to SP3'
Does anybody know if that can be done'Unfortunately looks like there is no easy way to do that. See
3.8 Uninstall Database Components SP4
on
http://download.microsoft.com/download/1/B/D/1BDF5B78-584E-4DE0-B36F-C44E06B0D2A3/ReadmeSql2k32sp4.htm
Ben Nevarez, MCDBA, OCP
Database Administrator
"msnews.microsoft.com" wrote:
> Sorry SUBJECT was backwards...
> Can it be done' How can I UNDO my SQL2000 SP4 update and go back to SP3'
> Does anybody know if that can be done'
>
>

DownDate ? from SP3 to SP4

Can it be done' How can I UNDO my SQL2000 SP4 update and go back to SP3'
Does anybody know if that can be done'Check out:
HOW TO: Remove a SQL Server Service Pack
http://support.microsoft.com/kb/314823/en-us
HTH
Jerry
"msnews.microsoft.com" <breichenbach@.istate.com> wrote in message
news:OqkGe7EgGHA.4080@.TK2MSFTNGP03.phx.gbl...
> Can it be done' How can I UNDO my SQL2000 SP4 update and go back to
> SP3'
> Does anybody know if that can be done'
>|||Be aware that support for SP3a ends in July 2006
(http://support.microsoft.com/gp/lifesupsps).
Perhaps there is a hotfix avialable to address the issue you are having.
Check out http://support.microsoft.com/kb/894905/
Hope this helps.
Dan Guzman
SQL Server MVP
"msnews.microsoft.com" <breichenbach@.istate.com> wrote in message
news:OqkGe7EgGHA.4080@.TK2MSFTNGP03.phx.gbl...
> Can it be done' How can I UNDO my SQL2000 SP4 update and go back to
> SP3'
> Does anybody know if that can be done'
>

DownDate ? from SP3 to SP4

Monday, March 19, 2012

Doubt aboult loop in trigger?

Hi all,
I have one doubt, if anyone can helpe-me.
I have one table that have one trigger (UPDATE), this trigger have one loop
with 12 interactions, for each interaction I have one INSERT in other table.
I did read that is not good put loop in trigger, this is true, if yes how I
should do this.
ThanksHi,
Anything done inside/called from the trigger will be in a transaction so can
cause blocking which can really quickly degrade the performance and
experience the user gets.
What loopnig are you trying to do, please post your trigger code.
Tony.
Tony Rogerson
SQL Server MVP
http://sqlserverfaq.com - free video tutorials
"ReTF" <re.tf@.newsgroup.nospam> wrote in message
news:ukG%23lhh3FHA.1416@.TK2MSFTNGP09.phx.gbl...
> Hi all,
> I have one doubt, if anyone can helpe-me.
> I have one table that have one trigger (UPDATE), this trigger have one
> loop with 12 interactions, for each interaction I have one INSERT in other
> table.
> I did read that is not good put loop in trigger, this is true, if yes how
> I should do this.
> Thanks
>|||Generally, processing one row at a time inside a loop is not efficient,
compared to processing all rows using a single statement. In many cases,
loops and cursors can be avoided by using SET based queries.
Can you show us what is it that you are trying to do, and may be someone can
suggest an alternative for you.
--
HTH,
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"ReTF" <re.tf@.newsgroup.nospam> wrote in message
news:ukG%23lhh3FHA.1416@.TK2MSFTNGP09.phx.gbl...
Hi all,
I have one doubt, if anyone can helpe-me.
I have one table that have one trigger (UPDATE), this trigger have one loop
with 12 interactions, for each interaction I have one INSERT in other table.
I did read that is not good put loop in trigger, this is true, if yes how I
should do this.
Thanks|||Hi,
I have one table that receive, request(purchase) of clients, these request
can be divided (for sample one purchase of 300,00 in 3x100,00), when this
occur I need schedule the payments (1th in cash , 2th in 30 days, 3th in 60
days), then I use LOOP to INSERT these in TABLE schedule.
I can have 1 to 12 INSERTS in this loop.
You have any other idea to do this, how? Thank you very much.
Thanks for help.
If you don't understand let-me know, because English is not my native
language, I can try explain in other way.
Retf
"Narayana Vyas Kondreddi" <answer_me@.hotmail.com> escreveu na mensagem
news:ePof$nh3FHA.3900@.TK2MSFTNGP12.phx.gbl...
> Generally, processing one row at a time inside a loop is not efficient,
> compared to processing all rows using a single statement. In many cases,
> loops and cursors can be avoided by using SET based queries.
> Can you show us what is it that you are trying to do, and may be someone
> can
> suggest an alternative for you.
> --
> HTH,
> Vyas, MVP (SQL Server)
> SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
>
> "ReTF" <re.tf@.newsgroup.nospam> wrote in message
> news:ukG%23lhh3FHA.1416@.TK2MSFTNGP09.phx.gbl...
> Hi all,
> I have one doubt, if anyone can helpe-me.
> I have one table that have one trigger (UPDATE), this trigger have one
> loop
> with 12 interactions, for each interaction I have one INSERT in other
> table.
> I did read that is not good put loop in trigger, this is true, if yes how
> I
> should do this.
> Thanks
>
>|||Here's a small example, that uses a numbers table, to replace the loop with
a singel INSERT statement inside your trigger.
CREATE TABLE Trn (TranID int, TotalSum money, TotalInstallments tinyint)
CREATE TABLE Pmts (TranID int, InstallmentNo tinyint, InstallmentAmt money)
CREATE TABLE Numbers (Number tinyint)
GO
SET NOCOUNT ON
GO
DECLARE @.i int
SET @.i = 1
WHILE @.i <= 12
BEGIN
INSERT INTO Numbers (Number)
SELECT @.i
SET @.i = @.i + 1
END
GO
CREATE TRIGGER TrnInsTrg
ON Trn
FOR INSERT
AS
BEGIN
INSERT Pmts (TranID, InstallmentNo, InstallmentAmt)
SELECT i.TranID, n.Number, i.TotalSum/i.TotalInstallments
FROM inserted AS i
INNER JOIN
Numbers AS n
ON n.Number <= i.TotalInstallments
END
GO
INSERT Trn (TranID, TotalSum, TotalInstallments) VALUES (1, 30000, 3)
INSERT Trn (TranID, TotalSum, TotalInstallments) VALUES (1, 120000, 12)
GO
SELECT * FROM Trn
SELECT * FROM Pmts
HTH,
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"ReTF" <re.tf@.newsgroup.nospam> wrote in message
news:ucBkH1i3FHA.4076@.TK2MSFTNGP15.phx.gbl...
Hi,
I have one table that receive, request(purchase) of clients, these request
can be divided (for sample one purchase of 300,00 in 3x100,00), when this
occur I need schedule the payments (1th in cash , 2th in 30 days, 3th in 60
days), then I use LOOP to INSERT these in TABLE schedule.
I can have 1 to 12 INSERTS in this loop.
You have any other idea to do this, how? Thank you very much.
Thanks for help.
If you don't understand let-me know, because English is not my native
language, I can try explain in other way.
Retf
"Narayana Vyas Kondreddi" <answer_me@.hotmail.com> escreveu na mensagem
news:ePof$nh3FHA.3900@.TK2MSFTNGP12.phx.gbl...
> Generally, processing one row at a time inside a loop is not efficient,
> compared to processing all rows using a single statement. In many cases,
> loops and cursors can be avoided by using SET based queries.
> Can you show us what is it that you are trying to do, and may be someone
> can
> suggest an alternative for you.
> --
> HTH,
> Vyas, MVP (SQL Server)
> SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
>
> "ReTF" <re.tf@.newsgroup.nospam> wrote in message
> news:ukG%23lhh3FHA.1416@.TK2MSFTNGP09.phx.gbl...
> Hi all,
> I have one doubt, if anyone can helpe-me.
> I have one table that have one trigger (UPDATE), this trigger have one
> loop
> with 12 interactions, for each interaction I have one INSERT in other
> table.
> I did read that is not good put loop in trigger, this is true, if yes how
> I
> should do this.
> Thanks
>
>

Wednesday, March 7, 2012

Don't update Files rtn, SQL secundary with warnings 208

I have 3 sql Server 2000 with log shipping, but the last files .rtn is
191207, don't update. In SQL Primary don't have events, in SQL Secundary
there are many warnings with the next descripcion:
Source: SQLserveragent, category: Job Engine, Type: Warning, Event ID:208
SQL server Sheduled Job 'Log Shipping Restore for
(local).BPP_logshipping'(0x6DA834715FFBFD4C8DF2F9226CA343F)
Status: Failed - Invoked on 2008-01-21 09:29:00 - Messae: The job failed.
The job was invoked bye schedule (Schedule 1). The lst step to run was step
1 (Log Shipping Restore).
In SQL enterpise, jobs, there is a Log shipping restore for local... in red.
the files rtn in SQL Secundary is the same in SQL Primary.
I don't find solution.Hi
I am not a log shipping expert, but it sounds like the backup may be
corrupt. I would stop the log shipping jobs, take a new backup of the
database and restore it on the secondary server and then restart the log
shipping jobs.
John
"Kerly" wrote:
> I have 3 sql Server 2000 with log shipping, but the last files .rtn is
> 191207, don't update. In SQL Primary don't have events, in SQL Secundary
> there are many warnings with the next descripcion:
> Source: SQLserveragent, category: Job Engine, Type: Warning, Event ID:208
> SQL server Sheduled Job 'Log Shipping Restore for
> (local).BPP_logshipping'(0x6DA834715FFBFD4C8DF2F9226CA343F)
> Status: Failed - Invoked on 2008-01-21 09:29:00 - Messae: The job failed.
> The job was invoked bye schedule (Schedule 1). The lst step to run was step
> 1 (Log Shipping Restore).
> In SQL enterpise, jobs, there is a Log shipping restore for local... in red.
> the files rtn in SQL Secundary is the same in SQL Primary.
> I don't find solution.
>

Dont understand error...

This is failing:

// Trying to update DynamicPageContent.Html where DynamicPageContent.PageID='121'
//With
// Select Html from DynamicPageHistory where HistoryID='831'
//

Update DynamicPageContent
SET Html=(SELECT Html FROM DynamicPageHistory WHERE HistoryID='831')
WHERE PageID='121'

With the following error:

Server: Msg 279, Level 16, State 3, Line 1
The text, ntext, and image data types are invalid in this subquery or aggregate expression.

DynamicPageContent.Html is Ntext size 16 and DynamicPageHistory.Html is Ntext size 16. PageID is and int and HistoryID is an int. It fails with single quotes around 831 and 121 and it fails without single quotes. The error message is the same both ways.

Ideas?

Thanks,

Rex

Look at the error message, it indicates the HTML column (whose data type is NTEXT) can not be used in subquery. To manipulate BLOB data, you should use some system functions. You can start from here:

http://msdn.microsoft.com/library/en-us/acdata/ac_8_con_11_7zox.asp?frame=true

|||I'm not a DBA at all and the SQL syntax is weird for me in terms of ordering the operations how would I modify the above SQL to use a declared variable and insert that value? Sorry to be so simple minded on this I'm learning as I go...|||

Nevermind:) Well, you can use something like this:

DECLARE @.val_His varbinary(16)
SELECT @.val_His = textptr(Html) FROM DynamicPageContent
WHERE HistoryID='831'

DECLARE @.val_Content varbinary(16)
SELECT @.val_Content = textptr(Html) FROM DynamicPageContent
WHERE PageID='121'

WRITETEXT DynamicPageContent.Html @.val_His @.val_Content

|||After posting to you I just plain wrote the SQL Select in ASP stuffed it into a string passed it as a param to the Update and let ASP do all the thinking.

Snap-Snap-Done. :)

Thank you for the help though.

Sunday, February 26, 2012

Don

Hi every one,
I learn a lot reading your comments,
Does runnning "UPDATE STATISTICS MYBIGTABLE" will affect
performance or create locks ?
I mean : While UPDATE STATISTICS is runnning, will it
affect performance ?
Thanks !
DonYes it will have an impact. Whether this will be noticeable to users are
not is another question...
Regarding locking, SQL Server takes two types of locks:
"Sch-S: Schema Stability Lock
--
This lock ensures that a schema element, such as a table or index, will
not be dropped while any session holds a schema stability lock on the
schema element.
Sch-M-UPD-STATS: Schema Modification Lock
---
This is a non-blocking lock that is used by the system to ensure that
only one automatic UPDATE STATISTICS process is run against a table at
any given point in time. The sp_lock stored procedure will report this
lock has having a type = TAB, resouce = UPD-STATS and mode = SCH-M."
Above information found here:
INF: How SQL Server 7.0 and SQL Server 2000 Autostats Work
http://support.microsoft.com/kb/q195565/
They should not block usual DML queries (insert, update, delete), but
may block DDL queries (drop table, alter table, alter database, etc).
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602m.html
PERFORMANCE DURING UPDATING STATS wrote:
> Hi every one,
> I learn a lot reading your comments,
> Does runnning "UPDATE STATISTICS MYBIGTABLE" will affect
> performance or create locks ?
> I mean : While UPDATE STATISTICS is runnning, will it
> affect performance ?
> Thanks !
> Don|||"PERFORMANCE DURING UPDATING STATS" <anonymous@.discussions.microsoft.com>
wrote in message news:1ecd01c4b5d7$297b9ea0$a401280a@.phx.gbl...
> Does runnning "UPDATE STATISTICS MYBIGTABLE" will affect
> performance or create locks ?
> I mean : While UPDATE STATISTICS is runnning, will it
> affect performance ?
It will have an effect on performance, in that it uses system resources.
How much of an effect is dependant on your server's horsepower, the size of
the table you're updating statistics for, etc... It will, like any other
read operation, create shared locks, but I have not seen it create exclusive
locks. So blocking should not be an issue.|||"Mark Allison" <marka@.no.tinned.meat.mvps.org> wrote in message
news:OtavF8dtEHA.2072@.tk2msftngp13.phx.gbl...
> any given point in time. The sp_lock stored procedure will report this
> lock has having a type = TAB, resouce = UPD-STATS and mode = SCH-M."
Mark,
I've never seen the SCH-M lock; I always get only SCH-S when I use
UPDATE STATISTICS on my systems. Is there an option that controls which
lock types are taken?|||Adam,
No, not that I'm aware of.
Adam Machanic wrote:
> I've never seen the SCH-M lock; I always get only SCH-S when I use
> UPDATE STATISTICS on my systems. Is there an option that controls which
> lock types are taken?
>|||UPDATE STATISTICS doesn't use locks, but it will affect performance, because
data pages are randomly accessed from the table to create the statistics.
Most likely not all of these pages are in memory and have to be read from
disk.
Jacco Schalkwijk
SQL Server MVP
"PERFORMANCE DURING UPDATING STATS" <anonymous@.discussions.microsoft.com>
wrote in message news:1ecd01c4b5d7$297b9ea0$a401280a@.phx.gbl...
> Hi every one,
> I learn a lot reading your comments,
> Does runnning "UPDATE STATISTICS MYBIGTABLE" will affect
> performance or create locks ?
> I mean : While UPDATE STATISTICS is runnning, will it
> affect performance ?
> Thanks !
> Don

Don

Hi every one,
I learn a lot reading your comments,
Does runnning "UPDATE STATISTICS MYBIGTABLE" will affect
performance or create locks ?
I mean : While UPDATE STATISTICS is runnning, will it
affect performance ?
Thanks !
Don
Yes it will have an impact. Whether this will be noticeable to users are
not is another question...
Regarding locking, SQL Server takes two types of locks:
"Sch-S: Schema Stability Lock
This lock ensures that a schema element, such as a table or index, will
not be dropped while any session holds a schema stability lock on the
schema element.
Sch-M-UPD-STATS: Schema Modification Lock
This is a non-blocking lock that is used by the system to ensure that
only one automatic UPDATE STATISTICS process is run against a table at
any given point in time. The sp_lock stored procedure will report this
lock has having a type = TAB, resouce = UPD-STATS and mode = SCH-M."
Above information found here:
INF: How SQL Server 7.0 and SQL Server 2000 Autostats Work
http://support.microsoft.com/kb/q195565/
They should not block usual DML queries (insert, update, delete), but
may block DDL queries (drop table, alter table, alter database, etc).
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602m.html
PERFORMANCE DURING UPDATING STATS wrote:
> Hi every one,
> I learn a lot reading your comments,
> Does runnning "UPDATE STATISTICS MYBIGTABLE" will affect
> performance or create locks ?
> I mean : While UPDATE STATISTICS is runnning, will it
> affect performance ?
> Thanks !
> Don
|||"PERFORMANCE DURING UPDATING STATS" <anonymous@.discussions.microsoft.com>
wrote in message news:1ecd01c4b5d7$297b9ea0$a401280a@.phx.gbl...
> Does runnning "UPDATE STATISTICS MYBIGTABLE" will affect
> performance or create locks ?
> I mean : While UPDATE STATISTICS is runnning, will it
> affect performance ?
It will have an effect on performance, in that it uses system resources.
How much of an effect is dependant on your server's horsepower, the size of
the table you're updating statistics for, etc... It will, like any other
read operation, create shared locks, but I have not seen it create exclusive
locks. So blocking should not be an issue.
|||"Mark Allison" <marka@.no.tinned.meat.mvps.org> wrote in message
news:OtavF8dtEHA.2072@.tk2msftngp13.phx.gbl...
> any given point in time. The sp_lock stored procedure will report this
> lock has having a type = TAB, resouce = UPD-STATS and mode = SCH-M."
Mark,
I've never seen the SCH-M lock; I always get only SCH-S when I use
UPDATE STATISTICS on my systems. Is there an option that controls which
lock types are taken?
|||Adam,
No, not that I'm aware of.
Adam Machanic wrote:
> I've never seen the SCH-M lock; I always get only SCH-S when I use
> UPDATE STATISTICS on my systems. Is there an option that controls which
> lock types are taken?
>
|||UPDATE STATISTICS doesn't use locks, but it will affect performance, because
data pages are randomly accessed from the table to create the statistics.
Most likely not all of these pages are in memory and have to be read from
disk.
Jacco Schalkwijk
SQL Server MVP
"PERFORMANCE DURING UPDATING STATS" <anonymous@.discussions.microsoft.com>
wrote in message news:1ecd01c4b5d7$297b9ea0$a401280a@.phx.gbl...
> Hi every one,
> I learn a lot reading your comments,
> Does runnning "UPDATE STATISTICS MYBIGTABLE" will affect
> performance or create locks ?
> I mean : While UPDATE STATISTICS is runnning, will it
> affect performance ?
> Thanks !
> Don

Don

Hi every one,
I learn a lot reading your comments,
Does runnning "UPDATE STATISTICS MYBIGTABLE" will affect
performance or create locks ?
I mean : While UPDATE STATISTICS is runnning, will it
affect performance ?
Thanks !
DonYes it will have an impact. Whether this will be noticeable to users are
not is another question...
Regarding locking, SQL Server takes two types of locks:
"Sch-S: Schema Stability Lock
--
This lock ensures that a schema element, such as a table or index, will
not be dropped while any session holds a schema stability lock on the
schema element.
Sch-M-UPD-STATS: Schema Modification Lock
---
This is a non-blocking lock that is used by the system to ensure that
only one automatic UPDATE STATISTICS process is run against a table at
any given point in time. The sp_lock stored procedure will report this
lock has having a type = TAB, resouce = UPD-STATS and mode = SCH-M."
Above information found here:
INF: How SQL Server 7.0 and SQL Server 2000 Autostats Work
http://support.microsoft.com/kb/q195565/
They should not block usual DML queries (insert, update, delete), but
may block DDL queries (drop table, alter table, alter database, etc).
--
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602m.html
PERFORMANCE DURING UPDATING STATS wrote:
> Hi every one,
> I learn a lot reading your comments,
> Does runnning "UPDATE STATISTICS MYBIGTABLE" will affect
> performance or create locks ?
> I mean : While UPDATE STATISTICS is runnning, will it
> affect performance ?
> Thanks !
> Don|||"PERFORMANCE DURING UPDATING STATS" <anonymous@.discussions.microsoft.com>
wrote in message news:1ecd01c4b5d7$297b9ea0$a401280a@.phx.gbl...
> Does runnning "UPDATE STATISTICS MYBIGTABLE" will affect
> performance or create locks ?
> I mean : While UPDATE STATISTICS is runnning, will it
> affect performance ?
It will have an effect on performance, in that it uses system resources.
How much of an effect is dependant on your server's horsepower, the size of
the table you're updating statistics for, etc... It will, like any other
read operation, create shared locks, but I have not seen it create exclusive
locks. So blocking should not be an issue.|||"Mark Allison" <marka@.no.tinned.meat.mvps.org> wrote in message
news:OtavF8dtEHA.2072@.tk2msftngp13.phx.gbl...
> any given point in time. The sp_lock stored procedure will report this
> lock has having a type = TAB, resouce = UPD-STATS and mode = SCH-M."
Mark,
I've never seen the SCH-M lock; I always get only SCH-S when I use
UPDATE STATISTICS on my systems. Is there an option that controls which
lock types are taken?|||Adam,
No, not that I'm aware of.
Adam Machanic wrote:
> I've never seen the SCH-M lock; I always get only SCH-S when I use
> UPDATE STATISTICS on my systems. Is there an option that controls which
> lock types are taken?
>|||UPDATE STATISTICS doesn't use locks, but it will affect performance, because
data pages are randomly accessed from the table to create the statistics.
Most likely not all of these pages are in memory and have to be read from
disk.
--
Jacco Schalkwijk
SQL Server MVP
"PERFORMANCE DURING UPDATING STATS" <anonymous@.discussions.microsoft.com>
wrote in message news:1ecd01c4b5d7$297b9ea0$a401280a@.phx.gbl...
> Hi every one,
> I learn a lot reading your comments,
> Does runnning "UPDATE STATISTICS MYBIGTABLE" will affect
> performance or create locks ?
> I mean : While UPDATE STATISTICS is runnning, will it
> affect performance ?
> Thanks !
> Don

Tuesday, February 14, 2012

Does Update stats cause all stored procs to recompile

Do all stored procs in a database get recompiled once sp_updatestats is run
against a database ? Using SQL 2000According to the BOL, the procs should be recompiled (i.e. new plans should
be generated). See the following excerpt from the BOL:
Recompiling Execution Plans
... The conditions that cause a plan to be invalidated include:
a.. New distribution statistics generated either explicitly from a
statement such as UPDATE STATISTICS or automatically.
...
However, my test didn't seem to confirm this to be always the case. When I
ran UPDATE STATISTICS on pubs..authors, I did see the following simple proc
got recompiled:
create proc pr_authors
as
select au_id from authors where au_id = '172-32-1176'
More specifically, I didn't see SP:Recompile event class in SQL Trace, which
you would see if you do a sp_recompile authors.
--
Linchi Shea
linchi_shea@.NOSPAMml.com
"Hassan" <fatima_ja@.hotmail.com> wrote in message
news:u8Kvj9vgDHA.3144@.tk2msftngp13.phx.gbl...
> Do all stored procs in a database get recompiled once sp_updatestats is
run
> against a database ? Using SQL 2000
>|||If the structure of the table(s) that a stored procedure uses is
changed, or when the statistics of the table(s) is changed, then the
stored procedure is marked for compilation. The next time the stored
procedure is used it will be (re)compiled.
Gert-Jan
Hassan wrote:
> Do all stored procs in a database get recompiled once sp_updatestats is run
> against a database ? Using SQL 2000

Does Tlog actually contain data ?

Say I update a column affecting a few thousand rows but do not issue a
commit .. I want to know if all these new values for the column, are they in
the Tlog or they actually get checkpointed to the data files as well ?
If they do not go to the data files, would it be true that only committed
data makes it to the data files while uncommitted data gets written to the
Transaction Log files ?
Thank you..Hassan
Yes , they are. In sql server 2000 a virtual tables "inserted" and
"deleted" (within a triggers) are reading the data from LOG file
"Hassan" <Hassan@.hotmail.com> wrote in message
news:uYcMghyIHHA.1252@.TK2MSFTNGP02.phx.gbl...
> Say I update a column affecting a few thousand rows but do not issue a
> commit .. I want to know if all these new values for the column, are they
> in the Tlog or they actually get checkpointed to the data files as well ?
> If they do not go to the data files, would it be true that only committed
> data makes it to the data files while uncommitted data gets written to the
> Transaction Log files ?
> Thank you..
>|||Yes
Only committed data will be written to the data files.
Regards
Amish Shah
http://shahamishm.tripod.com
Uri Dimant wrote:
> Hassan
> Yes , they are. In sql server 2000 a virtual tables "inserted" and
> "deleted" (within a triggers) are reading the data from LOG file
>
> "Hassan" <Hassan@.hotmail.com> wrote in message
> news:uYcMghyIHHA.1252@.TK2MSFTNGP02.phx.gbl...
> > Say I update a column affecting a few thousand rows but do not issue a
> > commit .. I want to know if all these new values for the column, are they
> > in the Tlog or they actually get checkpointed to the data files as well ?
> >
> > If they do not go to the data files, would it be true that only committed
> > data makes it to the data files while uncommitted data gets written to the
> > Transaction Log files ?
> >
> > Thank you..
> >|||> Only committed data will be written to the data files.
That is not correct, I'm afraid. SQL Server can write pages that are dirty and not committed to the
database data files. This can happen by both the lazywriter and the checkpoint process. SQL Server
makes sure that all log records (which can be cached) up to that point are written first, however.
This is called the Write Ahead protocol (WAL). See
http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/sqlIObasics.mspx for details.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"amish" <shahamishm@.gmail.com> wrote in message
news:1166514127.349431.258400@.48g2000cwx.googlegroups.com...
> Yes
> Only committed data will be written to the data files.
> Regards
> Amish Shah
> http://shahamishm.tripod.com
>
> Uri Dimant wrote:
>> Hassan
>> Yes , they are. In sql server 2000 a virtual tables "inserted" and
>> "deleted" (within a triggers) are reading the data from LOG file
>>
>> "Hassan" <Hassan@.hotmail.com> wrote in message
>> news:uYcMghyIHHA.1252@.TK2MSFTNGP02.phx.gbl...
>> > Say I update a column affecting a few thousand rows but do not issue a
>> > commit .. I want to know if all these new values for the column, are they
>> > in the Tlog or they actually get checkpointed to the data files as well ?
>> >
>> > If they do not go to the data files, would it be true that only committed
>> > data makes it to the data files while uncommitted data gets written to the
>> > Transaction Log files ?
>> >
>> > Thank you..
>> >
>|||Ok got it,
When checkpoint / lazywriter runs it writes dirty pages to disk and
lock that data to prevent committed view of it. User can view that data
only after data is committed. Nice to know...
Thanks Tibor.
Regards
Amish shah
Tibor Karaszi wrote:
> > Only committed data will be written to the data files.
> That is not correct, I'm afraid. SQL Server can write pages that are dirty and not committed to the
> database data files. This can happen by both the lazywriter and the checkpoint process. SQL Server
> makes sure that all log records (which can be cached) up to that point are written first, however.
> This is called the Write Ahead protocol (WAL). See
> http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/sqlIObasics.mspx for details.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "amish" <shahamishm@.gmail.com> wrote in message
> news:1166514127.349431.258400@.48g2000cwx.googlegroups.com...
> > Yes
> > Only committed data will be written to the data files.
> >
> > Regards
> > Amish Shah
> > http://shahamishm.tripod.com
> >
> >
> > Uri Dimant wrote:
> >> Hassan
> >> Yes , they are. In sql server 2000 a virtual tables "inserted" and
> >> "deleted" (within a triggers) are reading the data from LOG file
> >>
> >>
> >> "Hassan" <Hassan@.hotmail.com> wrote in message
> >> news:uYcMghyIHHA.1252@.TK2MSFTNGP02.phx.gbl...
> >> > Say I update a column affecting a few thousand rows but do not issue a
> >> > commit .. I want to know if all these new values for the column, are they
> >> > in the Tlog or they actually get checkpointed to the data files as well ?
> >> >
> >> > If they do not go to the data files, would it be true that only committed
> >> > data makes it to the data files while uncommitted data gets written to the
> >> > Transaction Log files ?
> >> >
> >> > Thank you..
> >> >
> >|||Data is always viewed and modified in cache. Checkpoint has nothing to do with locking. As soon as
you do, say, a modification, the rows are locked, and the lock is held until end of transaction.
Since other users also read the data from cache (never directly from disk), they will be blocked,
regardless of whether the page is dirty and regardless of the checkpoint process.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"amish" <shahamishm@.gmail.com> wrote in message
news:1166538947.256500.58740@.48g2000cwx.googlegroups.com...
> Ok got it,
> When checkpoint / lazywriter runs it writes dirty pages to disk and
> lock that data to prevent committed view of it. User can view that data
> only after data is committed. Nice to know...
> Thanks Tibor.
> Regards
> Amish shah
>
> Tibor Karaszi wrote:
>> > Only committed data will be written to the data files.
>> That is not correct, I'm afraid. SQL Server can write pages that are dirty and not committed to
>> the
>> database data files. This can happen by both the lazywriter and the checkpoint process. SQL
>> Server
>> makes sure that all log records (which can be cached) up to that point are written first,
>> however.
>> This is called the Write Ahead protocol (WAL). See
>> http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/sqlIObasics.mspx for details.
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://www.solidqualitylearning.com/
>>
>> "amish" <shahamishm@.gmail.com> wrote in message
>> news:1166514127.349431.258400@.48g2000cwx.googlegroups.com...
>> > Yes
>> > Only committed data will be written to the data files.
>> >
>> > Regards
>> > Amish Shah
>> > http://shahamishm.tripod.com
>> >
>> >
>> > Uri Dimant wrote:
>> >> Hassan
>> >> Yes , they are. In sql server 2000 a virtual tables "inserted" and
>> >> "deleted" (within a triggers) are reading the data from LOG file
>> >>
>> >>
>> >> "Hassan" <Hassan@.hotmail.com> wrote in message
>> >> news:uYcMghyIHHA.1252@.TK2MSFTNGP02.phx.gbl...
>> >> > Say I update a column affecting a few thousand rows but do not issue a
>> >> > commit .. I want to know if all these new values for the column, are they
>> >> > in the Tlog or they actually get checkpointed to the data files as well ?
>> >> >
>> >> > If they do not go to the data files, would it be true that only committed
>> >> > data makes it to the data files while uncommitted data gets written to the
>> >> > Transaction Log files ?
>> >> >
>> >> > Thank you..
>> >> >
>> >
>|||Tibor,
So the dirty pages that are not committed can be written to both the data
and log files. Does that mean that if its written to the data files, it
would get cleared from the log files even though its still not committed ?
Thanks
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:%2350BLy3IHHA.4376@.TK2MSFTNGP03.phx.gbl...
> Data is always viewed and modified in cache. Checkpoint has nothing to do
> with locking. As soon as you do, say, a modification, the rows are locked,
> and the lock is held until end of transaction. Since other users also read
> the data from cache (never directly from disk), they will be blocked,
> regardless of whether the page is dirty and regardless of the checkpoint
> process.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "amish" <shahamishm@.gmail.com> wrote in message
> news:1166538947.256500.58740@.48g2000cwx.googlegroups.com...
>> Ok got it,
>> When checkpoint / lazywriter runs it writes dirty pages to disk and
>> lock that data to prevent committed view of it. User can view that data
>> only after data is committed. Nice to know...
>> Thanks Tibor.
>> Regards
>> Amish shah
>>
>> Tibor Karaszi wrote:
>> > Only committed data will be written to the data files.
>> That is not correct, I'm afraid. SQL Server can write pages that are
>> dirty and not committed to the
>> database data files. This can happen by both the lazywriter and the
>> checkpoint process. SQL Server
>> makes sure that all log records (which can be cached) up to that point
>> are written first, however.
>> This is called the Write Ahead protocol (WAL). See
>> http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/sqlIObasics.mspx
>> for details.
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://www.solidqualitylearning.com/
>>
>> "amish" <shahamishm@.gmail.com> wrote in message
>> news:1166514127.349431.258400@.48g2000cwx.googlegroups.com...
>> > Yes
>> > Only committed data will be written to the data files.
>> >
>> > Regards
>> > Amish Shah
>> > http://shahamishm.tripod.com
>> >
>> >
>> > Uri Dimant wrote:
>> >> Hassan
>> >> Yes , they are. In sql server 2000 a virtual tables "inserted" and
>> >> "deleted" (within a triggers) are reading the data from LOG file
>> >>
>> >>
>> >> "Hassan" <Hassan@.hotmail.com> wrote in message
>> >> news:uYcMghyIHHA.1252@.TK2MSFTNGP02.phx.gbl...
>> >> > Say I update a column affecting a few thousand rows but do not
>> >> > issue a
>> >> > commit .. I want to know if all these new values for the column,
>> >> > are they
>> >> > in the Tlog or they actually get checkpointed to the data files as
>> >> > well ?
>> >> >
>> >> > If they do not go to the data files, would it be true that only
>> >> > committed
>> >> > data makes it to the data files while uncommitted data gets written
>> >> > to the
>> >> > Transaction Log files ?
>> >> >
>> >> > Thank you..
>> >> >
>> >
>|||> So the dirty pages that are not committed can be written to both the data and log files.
Correct.
> Does that mean that if its written to the data files, it would get cleared from the log files even
> though its still not committed ?
By cleared, you mean like when you do a log backup and log records are removed from the ldf file?
the log can only be truncated to the oldest open transaction, so log records for uncommitted
transactions will not be removed "prematurely". If that was the case, then SQL Server would be in a
situation where it can't perform a rollback, which isn't acceptable.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Hassan" <Hassan@.hotmail.com> wrote in message news:eWb9Xu4IHHA.3676@.TK2MSFTNGP03.phx.gbl...
> Tibor,
> So the dirty pages that are not committed can be written to both the data and log files. Does that
> mean that if its written to the data files, it would get cleared from the log files even though
> its still not committed ?
> Thanks
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in message
> news:%2350BLy3IHHA.4376@.TK2MSFTNGP03.phx.gbl...
>> Data is always viewed and modified in cache. Checkpoint has nothing to do with locking. As soon
>> as you do, say, a modification, the rows are locked, and the lock is held until end of
>> transaction. Since other users also read the data from cache (never directly from disk), they
>> will be blocked, regardless of whether the page is dirty and regardless of the checkpoint
>> process.
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://www.solidqualitylearning.com/
>>
>> "amish" <shahamishm@.gmail.com> wrote in message
>> news:1166538947.256500.58740@.48g2000cwx.googlegroups.com...
>> Ok got it,
>> When checkpoint / lazywriter runs it writes dirty pages to disk and
>> lock that data to prevent committed view of it. User can view that data
>> only after data is committed. Nice to know...
>> Thanks Tibor.
>> Regards
>> Amish shah
>>
>> Tibor Karaszi wrote:
>> > Only committed data will be written to the data files.
>> That is not correct, I'm afraid. SQL Server can write pages that are dirty and not committed to
>> the
>> database data files. This can happen by both the lazywriter and the checkpoint process. SQL
>> Server
>> makes sure that all log records (which can be cached) up to that point are written first,
>> however.
>> This is called the Write Ahead protocol (WAL). See
>> http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/sqlIObasics.mspx for details.
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://www.solidqualitylearning.com/
>>
>> "amish" <shahamishm@.gmail.com> wrote in message
>> news:1166514127.349431.258400@.48g2000cwx.googlegroups.com...
>> > Yes
>> > Only committed data will be written to the data files.
>> >
>> > Regards
>> > Amish Shah
>> > http://shahamishm.tripod.com
>> >
>> >
>> > Uri Dimant wrote:
>> >> Hassan
>> >> Yes , they are. In sql server 2000 a virtual tables "inserted" and
>> >> "deleted" (within a triggers) are reading the data from LOG file
>> >>
>> >>
>> >> "Hassan" <Hassan@.hotmail.com> wrote in message
>> >> news:uYcMghyIHHA.1252@.TK2MSFTNGP02.phx.gbl...
>> >> > Say I update a column affecting a few thousand rows but do not issue a
>> >> > commit .. I want to know if all these new values for the column, are they
>> >> > in the Tlog or they actually get checkpointed to the data files as well ?
>> >> >
>> >> > If they do not go to the data files, would it be true that only committed
>> >> > data makes it to the data files while uncommitted data gets written to the
>> >> > Transaction Log files ?
>> >> >
>> >> > Thank you..
>> >> >
>> >
>>
>

Does Tlog actually contain data ?

Does there have any undo or rollback function in SQL server?

When we update or delete data... it's very serious and seem to no choice to rollback. How does everyone to control this part of situation?

thanks for the opinion.As you have posted a question in the articles section it is being moved to SQL Server Forum.|||

Quote:

Originally Posted by debasisdas

As you have posted a question in the articles section it is being moved to SQL Server Forum.

sorry.. and thank you.|||

Quote:

Originally Posted by Gison

When we update or delete data... it's very serious and seem to no choice to rollback. How does everyone to control this part of situation?

thanks for the opinion.

im my case i have a backup routine which runs every night, can you not restore from a backup?|||

Quote:

Originally Posted by Gison

When we update or delete data... it's very serious and seem to no choice to rollback. How does everyone to control this part of situation?

thanks for the opinion.

yes. you can use transaction to handle this situation.
some commands are there

BEGIN TRANSACTION
<sql code>
COMMIT / ROLLBACK TRANSACTION

for more use the msdn help.|||You can always rollback the transaction before commiting it.

does the XML Task update XML files?

I'm using package configurations to store my server/database name in an XML File. I need to be able to dynamically change the database at runtime when I execute the package. Can I use the XML Task in another package to make a change to that xml file? I'm not familiar at all with XML, so I don't really know the syntax for XPath or XSLT or anything. Basically I'm just looking for an example of how to this with XML, but I haven't found anything on the web to explain this to me. BOL isn't very helpful with the XML Task.
I'm using the June CTP, but also have access to the Sept CTP.
Thanks,
-Andy

If you save the results as a file, yes.
K|||but can I overwrite the source file? Do you have any examples of this or point me to one? I don't see on in BOL or in the Samples that come with the CTP's.
Thanks,
-Andy|||

badandy:

did you ever get over this problem? i have come across the same problem and not sure how to do it. If you did please let me know

Thanks

hegde

does the XML Task update XML files?

badandy:

did you ever get over this problem? i have come across the same problem and not sure how to do it. If you did please let me know

Thanks

hegde

Does the UPDATE trigger fire when a record is updated or only when it is deleted?

I've gotten conflicting info about this in the past so I thought I'd try to get clarification.

When a record is deleted, I'm sure it fires the delete trigger. Does it also fire the update trigger?

ThanksThe update trigger doesnot fire on a delete.

however:

when an update occurs and the update trigger is fired, there are 2 virtual recordset that you can inspect to see what changed

inserted and deleted

the deleted recordset contains the old values
the inserted recordset contains the new values|||dpends on when you set the trigger to fire..could be update and/or delete and/or insert..


CREATE TRIGGER <trigger_name>
ON <table_name>
FOR DELETE, INSERT, UPDATE
AS ...

hth|||Thanks mbanavige. That's what I needed to know.

Does the rsreportserver.config file update in realtime?

for example, if i was to change my smtp server within the rsreportserver.config file, does it automatically update in realtime the second i save it or do i need to refresh something?

any insight on this would be grateful before i do anything.

thanks!

No. When you save the config file the server will automatically restarts itself.

|||

Teo Lachev wrote:

No. When you save the config file the server will automatically restarts itself.

so just to clarify before i start editing the xml file, if i change any value and save the xml file, the reporting server will restart itself?

so basically after saving, i just need to wait a couple of minutes for the changes to take effect once the server restarts?|||No need to wait. As you would see the first request after the config change will take much longer because the server needs to initialize itself.

Wednesday, March 21, 2012

DownDate ? from SP4 to SP3

DownDate ? from SP3 to SP4

DownDate ? from SP3 to SP4

Monday, March 19, 2012

Doubt aboult loop in trigger?

Wednesday, March 7, 2012

Don't update Files rtn, SQL secundary with warnings 208

Dont understand error...

Sunday, February 26, 2012

Don

Don

Don

Tuesday, February 14, 2012

Does Update stats cause all stored procs to recompile

Does Tlog actually contain data ?

Does Tlog actually contain data ?

Does Tlog actually contain data ?

Does there have any undo or rollback function in SQL server?

does the XML Task update XML files?

does the XML Task update XML files?

Does the UPDATE trigger fire when a record is updated or only when it is deleted?

Does the rsreportserver.config file update in realtime?

Drill down slow

Blog Archive

About Me