read_committed_snapshot take forever to turn off

I have turned on the database option read_committed_snapshot
alter database mydb set read_committed_snapshot on
this only took 1 sec.
But now I'm trying to turn it off.
alter database mydb set read_committed_snapshot off.
It already ran about 2 hours and it is still running.
Please help.
Thanks.
PingxPerhaps it is a refresh problem? Or perhaps you have some open transaction which holds the
transition?
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Pingx" <Pingx@.discussions.microsoft.com> wrote in message
news:A9E47C54-10B8-495E-BC41-5FD00BBAB497@.microsoft.com...
>I have turned on the database option read_committed_snapshot
> alter database mydb set read_committed_snapshot on
> this only took 1 sec.
> But now I'm trying to turn it off.
> alter database mydb set read_committed_snapshot off.
> It already ran about 2 hours and it is still running.
> Please help.
> Thanks.
> Pingx
>
>|||Likely, it is an open transaction. The ALTER DATABASE topic in Books Online
says "In order to set READ_COMMITTED_SNAPSHOT ON or OFF, there must be no
active connections to the database except for the connection executing the
ALTER DATABASE command. ".
--
Gail Erickson [MS]
SQL Server Documentation Team
This posting is provided "AS IS" with no warranties, and confers no rights
Download the latest version of Books Online from
http://technet.microsoft.com/en-us/sqlserver/bb428874.aspx
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:823B6EB2-3417-4B32-8C1F-DD4F42A1BA58@.microsoft.com...
> Perhaps it is a refresh problem? Or perhaps you have some open transaction
> which holds the transition?
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://sqlblog.com/blogs/tibor_karaszi
>
> "Pingx" <Pingx@.discussions.microsoft.com> wrote in message
> news:A9E47C54-10B8-495E-BC41-5FD00BBAB497@.microsoft.com...
>>I have turned on the database option read_committed_snapshot
>> alter database mydb set read_committed_snapshot on
>> this only took 1 sec.
>> But now I'm trying to turn it off.
>> alter database mydb set read_committed_snapshot off.
>> It already ran about 2 hours and it is still running.
>> Please help.
>> Thanks.
>> Pingx
>>
>>
>|||You guys are right. I killed some sessions and the command finished.
Thanks
"Pingx" wrote:
> I have turned on the database option read_committed_snapshot
> alter database mydb set read_committed_snapshot on
> this only took 1 sec.
> But now I'm trying to turn it off.
> alter database mydb set read_committed_snapshot off.
> It already ran about 2 hours and it is still running.
> Please help.
> Thanks.
> Pingx
>
>

read_committed_snapshot on

set transaction isolation level read committed
alter database mydatabase set read_committed_snapshot on
From the Sql Server docs :
"Row versioning is used to present each statement within the transaction
with a transactionally consistent snapshot of the data as it existed at the
start of the statement. Locks are not used to protet the data from updates
by other transactions."
But given that read queries can have complex execution plans, how can the
server know the set of rows that need to be versioned without blocking all
other transactions and figuring it out ( which would be the same as using
shared locks ) '> But given that read queries can have complex execution plans, how can the
> server know the set of rows that need to be versioned without blocking all
> other transactions and figuring it out ( which would be the same as using
> shared locks ) '
When you set a database to READ_COMMITTED_SNAPSHOT, all row changes are
versioned independently of select queries. Select queries in the
READ_COMMITTED isolation level use the latest row versions available at the
time the SELECT statement started.
--
Hope this helps.
Dan Guzman
SQL Server MVP
http://weblogs.sqlteam.com/dang/
"John A Grandy" <johnagrandy@.gmail.com> wrote in message
news:eU1pJxfjIHA.5504@.TK2MSFTNGP05.phx.gbl...
> set transaction isolation level read committed
> alter database mydatabase set read_committed_snapshot on
> From the Sql Server docs :
> "Row versioning is used to present each statement within the transaction
> with a transactionally consistent snapshot of the data as it existed at
> the start of the statement. Locks are not used to protet the data from
> updates by other transactions."
>
> But given that read queries can have complex execution plans, how can the
> server know the set of rows that need to be versioned without blocking all
> other transactions and figuring it out ( which would be the same as using
> shared locks ) '
>
>

READ_COMMITTED_SNAPSHOT

ALTER DATABASE TestDb SET READ_COMMITTED_SNAPSHOT ON;

The above statement work fine in SQL Server 2005.

But my problem is that I have some old databases still using Sql Server 2000. I know the above statement is only valid in 2005.

Is there any workaround in Sql Server 2000 to do the same thing?

Thanks.

You can use READ_COMMITTEE isolation in SQL 2000 and this READ_COMMITTED_SNAPSHOT is introducted in SQL 2005.

http://www.informit.com/articles/article.asp?p=27020

http://www.informit.com/articles/article.asp?p=327394&seqNum=2&rl=1

http://www.microsoft.com/technet/prodtechnol/sql/2005/cncrrncy.mspx

|||What you want is non-blocking read-committed isolation level in SQL2000. No, it is not supported. Only choice you have is to use read-uncommitted but that will potentially require you to change your app. Also, your app will need to be changed to handle uncommitted data.|||

Hello Wilson,

Is it the "Row versioning" functionnality that you want?

This is something new on SQL Server 2005.

Regards.

Carl

READ_COMMITTED_SNAPSHOT

ALTER DATABASE TestDb SET READ_COMMITTED_SNAPSHOT ON;

The above statement work fine in SQL Server 2005.

But my problem is that I have some old databases still using Sql Server 2000. I know the above statement is only valid in 2005.

Is there any workaround in Sql Server 2000 to do the same thing?

Thanks.

You can use READ_COMMITTEE isolation in SQL 2000 and this READ_COMMITTED_SNAPSHOT is introducted in SQL 2005.

http://www.informit.com/articles/article.asp?p=27020

http://www.informit.com/articles/article.asp?p=327394&seqNum=2&rl=1

http://www.microsoft.com/technet/prodtechnol/sql/2005/cncrrncy.mspx

|||What you want is non-blocking read-committed isolation level in SQL2000. No, it is not supported. Only choice you have is to use read-uncommitted but that will potentially require you to change your app. Also, your app will need to be changed to handle uncommitted data.|||

Hello Wilson,

Is it the "Row versioning" functionnality that you want?

This is something new on SQL Server 2005.

Regards.

Carl

Read Only Cursor

I have a sproc that has been running for over 2 years. We then alter a tabl
e structure
increasince the size of 3 fields, and correspondingly alter an Insert statem
ent for this
table. Now an 'update tablename where current of mycur' much later in the c
ode, issues an error of
'The cursor is Read Only' and fails.
The cursor syntax were :
SELECT ld_employee_no,
adjusted_hours
FROM Labrdet
WHERE ld_employee_no = @.dIFf_cur_empno
AND ld_prod_id + ld_prod_category + ld_prod_activity <> '02263'
ORDER BY ld_employee_no, adjusted_hours desc
I changed last line to the following and it works.
SELECT ld_employee_no,
adjusted_hours
FROM Labrdet
WHERE ld_employee_no = @.dIFf_cur_empno
AND ld_prod_id + ld_prod_category + ld_prod_activity <> '02263'
FOR UPDATE OF adjusted_hours
Does anyone have any idea, or should I post more info? We'd really like to
know
why the DDL change and the Insert change would affect a cursor update.
TIA,
Marc MillerWithout seeing the full repro I'm guessing that you changed something
that caused an implicit conversion to a static cursor. Always specify
cursor options explicitly to avoid this.
Could you explain why you are using a cursor at all? Again just
guessing by your code fragment this looks very like a straight data mod
that ought to be possible in an UPDATE with no cursor at all. If this
is a 2 year code legacy then maybe now would be a good time to review
and replace it.
David Portas
SQL Server MVP
--|||David,
I have a table of salaried employee time entires. Reporting requires,
however, that I only
show 40 hours per employee, even though they report overtime hours. Their
time is reported
in quarter hours increments and I need to loop and decrement/increment the
line items by the amount of
the overtime until I can best adjust each line 'evenly' (sort of an
allocation type basis.) to a total of 40 hours
for each person.
I have no idea in the world how I would use an UPDATE to accomplish this.
Thanks,
Marc Miller
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:1128538402.789619.48450@.z14g2000cwz.googlegroups.com...
> Without seeing the full repro I'm guessing that you changed something
> that caused an implicit conversion to a static cursor. Always specify
> cursor options explicitly to avoid this.
> Could you explain why you are using a cursor at all? Again just
> guessing by your code fragment this looks very like a straight data mod
> that ought to be possible in an UPDATE with no cursor at all. If this
> is a 2 year code legacy then maybe now would be a good time to review
> and replace it.
> --
> David Portas
> SQL Server MVP
> --
>|||> Reporting requires,
> however, that I only
> show 40 hours per employee
If that's just a reporting requirement why do you need to update the
table? Wouldn't it suffice to put the calc in a SELECT statement?

> I have no idea in the world how I would use an UPDATE to accomplish this.
If you want help with that please post DDL, sample data and required
results as described here:
http://www.aspfaq.com/etiquette.asp?id=5006
David Portas
SQL Server MVP
--

rc:StyleSheet Parameter... PLEASE HELP

I am trying to utlize the rc:Stylesheet parameter to alter the default header
look/feel so that it matches the rest of my site and reports (My
understanding when I read the SP1 readme file and other posts was that I can
use the rc:StyleSheet parameter to change the 'HTML viewer' area of a report
which is the area where the parameters are located). However, I am totally
confused because when I alter the styles in the htmlviewer.css style sheet it
does nothing to my reports. Also, when I create a new style sheet and point
to that using the rc:Stylesheet parameter, nothing changes on my reports.
For now I have the following questions:
1) Can I use the rc:Stylesheet property to alter the look/feel of the
header/parameter section of my reports?
2) If so, how do I go about doing this? I installed SP1, created a style
sheet called 'test.css' with the styles I wanted then pointed my report to
that style sheet using the 'rc:Stylesheet=test' command. Is this right/wrong?
3) If everything above is correct, then perhaps I didn't install the SP1
corretly (?). The version I am now running is: Microsoft SQL Server
Reporting Services Version 8.00.878.00.
Any information is greatly appreciated.If you right click in the parameters area and select view source, do you see
your style sheet embedded in the html? If not, do you see an error message
in the report server log file?
--
This posting is provided "AS IS" with no warranties, and confers no rights
"David Whitfield" <David Whitfield@.discussions.microsoft.com> wrote in
message news:DBE923B5-A9A4-46D0-AF1E-73C8E4F97CD3@.microsoft.com...
> I am trying to utlize the rc:Stylesheet parameter to alter the default
header
> look/feel so that it matches the rest of my site and reports (My
> understanding when I read the SP1 readme file and other posts was that I
can
> use the rc:StyleSheet parameter to change the 'HTML viewer' area of a
report
> which is the area where the parameters are located). However, I am
totally
> confused because when I alter the styles in the htmlviewer.css style sheet
it
> does nothing to my reports. Also, when I create a new style sheet and
point
> to that using the rc:Stylesheet parameter, nothing changes on my reports.
> For now I have the following questions:
> 1) Can I use the rc:Stylesheet property to alter the look/feel of the
> header/parameter section of my reports?
> 2) If so, how do I go about doing this? I installed SP1, created a style
> sheet called 'test.css' with the styles I wanted then pointed my report to
> that style sheet using the 'rc:Stylesheet=test' command. Is this
right/wrong?
> 3) If everything above is correct, then perhaps I didn't install the SP1
> corretly (?). The version I am now running is: Microsoft SQL Server
> Reporting Services Version 8.00.878.00.
> Any information is greatly appreciated.|||The link to the htmlviewer.css file is a link to a stylesheet embedded in
the report server dlls. It the one used by default. The file placed in the
styles directory is identical to the embedded stylesheet and provided as
template for modifying things.
You can check the log file for errors by trying your URL and checking the
latest ReportServer*.log file in the \Program Files\Microsoft Sql
Server\MSSQL\Reporting Services\LogFiles.
Also, just to make sure, your URL is pointing at the report server, not the
Report Manager, right? It should be http://localhost/reportserver/... not
http://localhost/reports/...
--
This posting is provided "AS IS" with no warranties, and confers no rights
"David Whitfield" <DavidWhitfield@.discussions.microsoft.com> wrote in
message news:ED9BC075-5E82-4035-9E81-39DAC2DC068A@.microsoft.com...
> Correction to my last post and a better response to your questions...
> 1) No, I don't see a reference to the test.css file that I am trying to
> reference in the HTML.
> 2) I'm sorry, but I do not know how to check the log file. I will try to
> find out but any information you can give is appreciated.
> Thank you.
> "Brian Hartman [MSFT]" wrote:
> > If you right click in the parameters area and select view source, do you
see
> > your style sheet embedded in the html? If not, do you see an error
message
> > in the report server log file?
> >
> > --
> > This posting is provided "AS IS" with no warranties, and confers no
rights
> >
> > "David Whitfield" <David Whitfield@.discussions.microsoft.com> wrote in
> > message news:DBE923B5-A9A4-46D0-AF1E-73C8E4F97CD3@.microsoft.com...
> > > I am trying to utlize the rc:Stylesheet parameter to alter the default
> > header
> > > look/feel so that it matches the rest of my site and reports (My
> > > understanding when I read the SP1 readme file and other posts was that
I
> > can
> > > use the rc:StyleSheet parameter to change the 'HTML viewer' area of a
> > report
> > > which is the area where the parameters are located). However, I am
> > totally
> > > confused because when I alter the styles in the htmlviewer.css style
sheet
> > it
> > > does nothing to my reports. Also, when I create a new style sheet and
> > point
> > > to that using the rc:Stylesheet parameter, nothing changes on my
reports.
> > >
> > > For now I have the following questions:
> > > 1) Can I use the rc:Stylesheet property to alter the look/feel of the
> > > header/parameter section of my reports?
> > > 2) If so, how do I go about doing this? I installed SP1, created a
style
> > > sheet called 'test.css' with the styles I wanted then pointed my
report to
> > > that style sheet using the 'rc:Stylesheet=test' command. Is this
> > right/wrong?
> > > 3) If everything above is correct, then perhaps I didn't install the
SP1
> > > corretly (?). The version I am now running is: Microsoft SQL Server
> > > Reporting Services Version 8.00.878.00.
> > >
> > > Any information is greatly appreciated.
> >
> >
> >|||I do see a reference to the htmlviewer style sheet as follows:
<link href="http://links.10026.com/?link=?rs:Command=Get&rc:GetImage=8.00.878.00HtmlViewer.css"
type="text/css" rel="stylesheet" >
Does this mean the page is referencing the htmlviewer style sheet and if so,
why am I able to delete that style sheet without any effect on the page?
"Brian Hartman [MSFT]" wrote:
> If you right click in the parameters area and select view source, do you see
> your style sheet embedded in the html? If not, do you see an error message
> in the report server log file?
> --
> This posting is provided "AS IS" with no warranties, and confers no rights
> "David Whitfield" <David Whitfield@.discussions.microsoft.com> wrote in
> message news:DBE923B5-A9A4-46D0-AF1E-73C8E4F97CD3@.microsoft.com...
> > I am trying to utlize the rc:Stylesheet parameter to alter the default
> header
> > look/feel so that it matches the rest of my site and reports (My
> > understanding when I read the SP1 readme file and other posts was that I
> can
> > use the rc:StyleSheet parameter to change the 'HTML viewer' area of a
> report
> > which is the area where the parameters are located). However, I am
> totally
> > confused because when I alter the styles in the htmlviewer.css style sheet
> it
> > does nothing to my reports. Also, when I create a new style sheet and
> point
> > to that using the rc:Stylesheet parameter, nothing changes on my reports.
> >
> > For now I have the following questions:
> > 1) Can I use the rc:Stylesheet property to alter the look/feel of the
> > header/parameter section of my reports?
> > 2) If so, how do I go about doing this? I installed SP1, created a style
> > sheet called 'test.css' with the styles I wanted then pointed my report to
> > that style sheet using the 'rc:Stylesheet=test' command. Is this
> right/wrong?
> > 3) If everything above is correct, then perhaps I didn't install the SP1
> > corretly (?). The version I am now running is: Microsoft SQL Server
> > Reporting Services Version 8.00.878.00.
> >
> > Any information is greatly appreciated.
>
>|||Correction to my last post and a better response to your questions...
1) No, I don't see a reference to the test.css file that I am trying to
reference in the HTML.
2) I'm sorry, but I do not know how to check the log file. I will try to
find out but any information you can give is appreciated.
Thank you.
"Brian Hartman [MSFT]" wrote:
> If you right click in the parameters area and select view source, do you see
> your style sheet embedded in the html? If not, do you see an error message
> in the report server log file?
> --
> This posting is provided "AS IS" with no warranties, and confers no rights
> "David Whitfield" <David Whitfield@.discussions.microsoft.com> wrote in
> message news:DBE923B5-A9A4-46D0-AF1E-73C8E4F97CD3@.microsoft.com...
> > I am trying to utlize the rc:Stylesheet parameter to alter the default
> header
> > look/feel so that it matches the rest of my site and reports (My
> > understanding when I read the SP1 readme file and other posts was that I
> can
> > use the rc:StyleSheet parameter to change the 'HTML viewer' area of a
> report
> > which is the area where the parameters are located). However, I am
> totally
> > confused because when I alter the styles in the htmlviewer.css style sheet
> it
> > does nothing to my reports. Also, when I create a new style sheet and
> point
> > to that using the rc:Stylesheet parameter, nothing changes on my reports.
> >
> > For now I have the following questions:
> > 1) Can I use the rc:Stylesheet property to alter the look/feel of the
> > header/parameter section of my reports?
> > 2) If so, how do I go about doing this? I installed SP1, created a style
> > sheet called 'test.css' with the styles I wanted then pointed my report to
> > that style sheet using the 'rc:Stylesheet=test' command. Is this
> right/wrong?
> > 3) If everything above is correct, then perhaps I didn't install the SP1
> > corretly (?). The version I am now running is: Microsoft SQL Server
> > Reporting Services Version 8.00.878.00.
> >
> > Any information is greatly appreciated.
>
>|||That was the problem; I was not pointing to the report server.
Thank you!!
"Brian Hartman [MSFT]" wrote:
> The link to the htmlviewer.css file is a link to a stylesheet embedded in
> the report server dlls. It the one used by default. The file placed in the
> styles directory is identical to the embedded stylesheet and provided as
> template for modifying things.
> You can check the log file for errors by trying your URL and checking the
> latest ReportServer*.log file in the \Program Files\Microsoft Sql
> Server\MSSQL\Reporting Services\LogFiles.
> Also, just to make sure, your URL is pointing at the report server, not the
> Report Manager, right? It should be http://localhost/reportserver/... not
> http://localhost/reports/...
> --
> This posting is provided "AS IS" with no warranties, and confers no rights
> "David Whitfield" <DavidWhitfield@.discussions.microsoft.com> wrote in
> message news:ED9BC075-5E82-4035-9E81-39DAC2DC068A@.microsoft.com...
> > Correction to my last post and a better response to your questions...
> >
> > 1) No, I don't see a reference to the test.css file that I am trying to
> > reference in the HTML.
> > 2) I'm sorry, but I do not know how to check the log file. I will try to
> > find out but any information you can give is appreciated.
> >
> > Thank you.
> >
> > "Brian Hartman [MSFT]" wrote:
> >
> > > If you right click in the parameters area and select view source, do you
> see
> > > your style sheet embedded in the html? If not, do you see an error
> message
> > > in the report server log file?
> > >
> > > --
> > > This posting is provided "AS IS" with no warranties, and confers no
> rights
> > >
> > > "David Whitfield" <David Whitfield@.discussions.microsoft.com> wrote in
> > > message news:DBE923B5-A9A4-46D0-AF1E-73C8E4F97CD3@.microsoft.com...
> > > > I am trying to utlize the rc:Stylesheet parameter to alter the default
> > > header
> > > > look/feel so that it matches the rest of my site and reports (My
> > > > understanding when I read the SP1 readme file and other posts was that
> I
> > > can
> > > > use the rc:StyleSheet parameter to change the 'HTML viewer' area of a
> > > report
> > > > which is the area where the parameters are located). However, I am
> > > totally
> > > > confused because when I alter the styles in the htmlviewer.css style
> sheet
> > > it
> > > > does nothing to my reports. Also, when I create a new style sheet and
> > > point
> > > > to that using the rc:Stylesheet parameter, nothing changes on my
> reports.
> > > >
> > > > For now I have the following questions:
> > > > 1) Can I use the rc:Stylesheet property to alter the look/feel of the
> > > > header/parameter section of my reports?
> > > > 2) If so, how do I go about doing this? I installed SP1, created a
> style
> > > > sheet called 'test.css' with the styles I wanted then pointed my
> report to
> > > > that style sheet using the 'rc:Stylesheet=test' command. Is this
> > > right/wrong?
> > > > 3) If everything above is correct, then perhaps I didn't install the
> SP1
> > > > corretly (?). The version I am now running is: Microsoft SQL Server
> > > > Reporting Services Version 8.00.878.00.
> > > >
> > > > Any information is greatly appreciated.
> > >
> > >
> > >
>
>