Investigating Deleted Records

I am investigating an issue where a bunch (6000) records got deleted
from a table. I am using SQL 2000 Standard and have my data recovery
model set to Full. I am using Red-Gate SQL log rescue to look at the
log and to recover the records.
My real question is: Is there a way to find out what SQL statement
created a given log entry? Is there some way to match a log entry to a
trace in the SQL Profiler?
--
Japheth Nolt
Microsoft SBF Specialist
Landis Computer
www.landiscomputer.com
3/27/2007 5:51:50 PMTransaction logging only log the effect of the commands submitted. So, any log mining tool cannot
get the actual command from the transaction log. However, you might be able to correlated the log
records with a profiler trace based on things like spid, date and time. I would assume that a log
mining tool could do this if provided with a profiler trace, but the tool would of course need to
have such feature.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Japheth Nolt" <japheth.remove@.landiscomputer.com> wrote in message
news:xn0f47mo0ktq9v001@.msnews.microsoft.com...
>I am investigating an issue where a bunch (6000) records got deleted
> from a table. I am using SQL 2000 Standard and have my data recovery
> model set to Full. I am using Red-Gate SQL log rescue to look at the
> log and to recover the records.
> My real question is: Is there a way to find out what SQL statement
> created a given log entry? Is there some way to match a log entry to a
> trace in the SQL Profiler?
> --
> Japheth Nolt
> Microsoft SBF Specialist
> Landis Computer
> www.landiscomputer.com
> 3/27/2007 5:51:50 PMsql

Investigating Deleted Records

I am investigating an issue where a bunch (6000) records got deleted
from a table. I am using SQL 2000 Standard and have my data recovery
model set to Full. I am using Red-Gate SQL log rescue to look at the
log and to recover the records.
My real question is: Is there a way to find out what SQL statement
created a given log entry? Is there some way to match a log entry to a
trace in the SQL Profiler?
--
Japheth Nolt
Microsoft SBF Specialist
Landis Computer
www.landiscomputer.com
3/27/2007 5:51:50 PMTransaction logging only log the effect of the commands submitted. So, any l
og mining tool cannot
get the actual command from the transaction log. However, you might be able
to correlated the log
records with a profiler trace based on things like spid, date and time. I wo
uld assume that a log
mining tool could do this if provided with a profiler trace, but the tool wo
uld of course need to
have such feature.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Japheth Nolt" <japheth.remove@.landiscomputer.com> wrote in message
news:xn0f47mo0ktq9v001@.msnews.microsoft.com...
>I am investigating an issue where a bunch (6000) records got deleted
> from a table. I am using SQL 2000 Standard and have my data recovery
> model set to Full. I am using Red-Gate SQL log rescue to look at the
> log and to recover the records.
> My real question is: Is there a way to find out what SQL statement
> created a given log entry? Is there some way to match a log entry to a
> trace in the SQL Profiler?
> --
> Japheth Nolt
> Microsoft SBF Specialist
> Landis Computer
> www.landiscomputer.com
> 3/27/2007 5:51:50 PM

Investigating Deleted Records

I am investigating an issue where a bunch (6000) records got deleted
from a table. I am using SQL 2000 Standard and have my data recovery
model set to Full. I am using Red-Gate SQL log rescue to look at the
log and to recover the records.
My real question is: Is there a way to find out what SQL statement
created a given log entry? Is there some way to match a log entry to a
trace in the SQL Profiler?
Japheth Nolt
Microsoft SBF Specialist
Landis Computer
www.landiscomputer.com
3/27/2007 5:51:50 PM
Transaction logging only log the effect of the commands submitted. So, any log mining tool cannot
get the actual command from the transaction log. However, you might be able to correlated the log
records with a profiler trace based on things like spid, date and time. I would assume that a log
mining tool could do this if provided with a profiler trace, but the tool would of course need to
have such feature.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Japheth Nolt" <japheth.remove@.landiscomputer.com> wrote in message
news:xn0f47mo0ktq9v001@.msnews.microsoft.com...
>I am investigating an issue where a bunch (6000) records got deleted
> from a table. I am using SQL 2000 Standard and have my data recovery
> model set to Full. I am using Red-Gate SQL log rescue to look at the
> log and to recover the records.
> My real question is: Is there a way to find out what SQL statement
> created a given log entry? Is there some way to match a log entry to a
> trace in the SQL Profiler?
> --
> Japheth Nolt
> Microsoft SBF Specialist
> Landis Computer
> www.landiscomputer.com
> 3/27/2007 5:51:50 PM

Inverse -- Reverse

Hi,
I am not able to slove my problem so guys i need ur help.
i had (thousands) of records in my database in this manner.
Name Id
DFDDABBF-7079-4135-AAD8-1BDA58DB0CEB
Now i need the Nameid like below
BFABDDDF-7970-3541-AAD8-1BDA58DB0CEB
I need changes till 18 character started from left handside.
Thanx for ur help.
from
Dollerdoller
It is completely . Do you need to change/reverse only the left
side of the data?
It is not exactly what you wanted but if you explain what are you trying
to achive so we may suggets better solution
Anyway , I tried to give an idea.
CREATE TABLE #Test (col VARCHAR(50) NOT NULL)
INSERT INTO #Test VALUES ('DFDDABBF-7079-4135-AAD8-1BDA58DB0CEB')
SELECT REVERSE(col) FROM #Test
SELECT STUFF(col, 1, CHARINDEX('-',col), 'BFABDDDF-')
FROM #Test
"doller" <sufianarif@.gmail.com> wrote in message
news:1142831035.525419.135860@.t31g2000cwb.googlegroups.com...
> Hi,
> I am not able to slove my problem so guys i need ur help.
> i had (thousands) of records in my database in this manner.
> Name Id
> DFDDABBF-7079-4135-AAD8-1BDA58DB0CEB
>
> Now i need the Nameid like below
> BFABDDDF-7970-3541-AAD8-1BDA58DB0CEB
> I need changes till 18 character started from left handside.
> Thanx for ur help.
> from
> Doller
>|||SOrry if u got confuse
Given Data
DFDDABBF-7079-4135-AAD8-1BDA58DB0CEB (given Data)
Needs like below
BFABDDDF-7970-3541-AAD8-1BDA58DB0CEB
I dont know how to do this (not sure use reverse or inverse).
from
Doller|||Well, you will be better of doing such things on the client side
"doller" <sufianarif@.gmail.com> wrote in message
news:1142833372.212324.309310@.i39g2000cwa.googlegroups.com...
> SOrry if u got confuse
> Given Data
> DFDDABBF-7079-4135-AAD8-1BDA58DB0CEB (given Data)
> Needs like below
> BFABDDDF-7970-3541-AAD8-1BDA58DB0CEB
> I dont know how to do this (not sure use reverse or inverse).
> from
> Doller
>|||Hi Umi,'
Thanx for ur help . !ha|||Was it sarcasm?
"doller" <sufianarif@.gmail.com> wrote in message
news:1142833924.710613.158210@.v46g2000cwv.googlegroups.com...
> Hi Umi,'
> Thanx for ur help . !ha
>|||"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:e3kfDH%23SGHA.5172@.TK2MSFTNGP12.phx.gbl...
> Well, you will be better of doing such things on the client side
I don't see why it can't be done on the server. It looks like a maintenance
job to fix a bug in the app. A simple update statement could fix it pretty
quick.

>
> "doller" <sufianarif@.gmail.com> wrote in message
> news:1142833372.212324.309310@.i39g2000cwa.googlegroups.com...
>|||Hi umi,
Why u think like this man .Ha actually mean yes.
If u think i do then i am sorry.
from
Doller|||doller wrote on 19 Mar 2006 21:03:55 -0800:

> Hi,
> I am not able to slove my problem so guys i need ur help.
> i had (thousands) of records in my database in this manner.
> Name Id
> DFDDABBF-7079-4135-AAD8-1BDA58DB0CEB
> Now i need the Nameid like below
> BFABDDDF-7970-3541-AAD8-1BDA58DB0CEB
> I need changes till 18 character started from left handside.
> Thanx for ur help.
Assuming the strings are in Col1 of your table Table1
SELECT REVERSE(LEFT(Col1,8)) + '-' + REVERSE(SUBSTRING(Col1,10,4) + '-' +
REVERSE(SUBSTRING(Col2,16,4) + SUBSTRING(Col1,20,17)
FROM Table1
Dan|||I think you want something like this:
DROP TABLE #tmp
CREATE TABLE #tmp ( nameid VARCHAR(50) PRIMARY KEY )
INSERT INTO #tmp VALUES( 'DFDDABBF-7079-4135-AAD8-1BDA58DB0CEB' )
-- INSERT INTO #tmp VALUES( 'BFABDDDF-7970-3541-AAD8-1BDA58DB0CEB' )
SELECT nameid,
SUBSTRING( nameid, 7, 2 ) +
SUBSTRING( nameid, 5, 2 ) +
SUBSTRING( nameid, 3, 2 ) +
SUBSTRING( nameid, 1, 2 ) +
SUBSTRING( nameid, 9, 1 ) +
SUBSTRING( nameid, 12, 2 ) +
SUBSTRING( nameid, 10, 2 ) +
SUBSTRING( nameid, 14, 1 ) +
SUBSTRING( nameid, 17, 2 ) +
SUBSTRING( nameid, 15, 2 ) +
SUBSTRING( nameid, 19, 18 )
FROM #tmp
Lookup using SUBSTRING, LEFT, RIGHT, REPLACE and STUFF in the SQL Help.
Let me know how you get on.
Damien
"doller" wrote:

> Hi,
> I am not able to slove my problem so guys i need ur help.
> i had (thousands) of records in my database in this manner.
> Name Id
> DFDDABBF-7079-4135-AAD8-1BDA58DB0CEB
>
> Now i need the Nameid like below
> BFABDDDF-7970-3541-AAD8-1BDA58DB0CEB
> I need changes till 18 character started from left handside.
> Thanx for ur help.
> from
> Doller
>

INvariant part inside SELECT

All,
Can we do this trick and if yes then how? Just schematically: the SP should
return the number of records if the parameter @.Count=1, if not, then the
records themselves. The problem is that there is some complicated JOIN and
the whole set of WHERE clauses that I wouldn't like to repeat in two
different queries looking almost identically excluding the main SELECT part.
The idea described below doesn't work.
--Parameter
Declare @.Count bit
SET @.Count = 1
SELECT
CASE
WHEN @.Count = 1
THEN pe.*, pn.*
ELSE COUNT(*)
END
...
FROM ...
INNER JOIN ... ON ...
WHERE ...
Any ideas?
Just D.Just D (no@.spam.please) writes:
> Can we do this trick and if yes then how? Just schematically: the SP
> should return the number of records if the parameter @.Count=1, if not,
> then the records themselves. The problem is that there is some
> complicated JOIN and the whole set of WHERE clauses that I wouldn't like
> to repeat in two different queries looking almost identically excluding
> the main SELECT part. The idea described below doesn't work.
The best is probably to put the whole JOIN-WHERE business in an
inline table-valued function. Then the procedure can read:
IF @.count = 1
SELECT COUNT(*) FROM tblfunc(@.par1, @.par2, ...)
ELSE
SELECT col1, col2, ...
FROM tblfunc (@.par1, @.par2, ...)
You could also bounce the data over a temp tble, but that would be more
expensive in terms of performance, not the least for the COUNT. (Since for
the COUNT(*) SQL Server may find a quicker query plan when it does not have
to read all data pages.)
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||Hi
Using SELECT * in production code is not a good idea. The only way to do
what you are doing without writing the query twice would be to use dynamic
SQL.
John
"Just D" wrote:

> All,
> Can we do this trick and if yes then how? Just schematically: the SP shoul
d
> return the number of records if the parameter @.Count=1, if not, then the
> records themselves. The problem is that there is some complicated JOIN and
> the whole set of WHERE clauses that I wouldn't like to repeat in two
> different queries looking almost identically excluding the main SELECT par
t.
> The idea described below doesn't work.
> --Parameter
> Declare @.Count bit
> SET @.Count = 1
>
> SELECT
> CASE
> WHEN @.Count = 1
> THEN pe.*, pn.*
> ELSE COUNT(*)
> END
> ...
> FROM ...
> INNER JOIN ... ON ...
> WHERE ...
> Any ideas?
> Just D.
>
>|||Erland,
Correct me if I am wrong. I don't see any performance benifit by using the
table valued function over using the actual query, except for the fact that
the stored procedure looks better :)
The execution plan is not stored for the TVF but is stored in the calling
SP. And the plan will be recomplied everytime the condition changes. I would
say it would be better performance wise, if we have two stored procedures on
e
for returning the row count and one for returning the result set and call
these two SPs from the main SP based on the condition.So that only the main
SP will get recompiled and will not be much of an overhead.
If its a query with a simple execution plan, then what you suggest will be
fine.
-Omnibuzz (The SQL GC)
http://omnibuzz-sql.blogspot.com/
"Erland Sommarskog" wrote:

> Just D (no@.spam.please) writes:
> The best is probably to put the whole JOIN-WHERE business in an
> inline table-valued function. Then the procedure can read:
> IF @.count = 1
> SELECT COUNT(*) FROM tblfunc(@.par1, @.par2, ...)
> ELSE
> SELECT col1, col2, ...
> FROM tblfunc (@.par1, @.par2, ...)
> You could also bounce the data over a temp tble, but that would be more
> expensive in terms of performance, not the least for the COUNT. (Since for
> the COUNT(*) SQL Server may find a quicker query plan when it does not hav
e
> to read all data pages.)
> --
> Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
> Books Online for SQL Server 2005 at
> http://www.microsoft.com/technet/pr...oads/books.mspx
> Books Online for SQL Server 2000 at
> http://www.microsoft.com/sql/prodin...ions/books.mspx
>|||>> Just schematically: the SP should return the number of records [sic] if the parame
ter @.Count=1, if not, then the records [sic] themselves. <<
Did you ever have a software engineering course? Remember cohesion?
The idea that a properly designed code module will perform one
well-defined task. Good programmers do not write things that return
the square root of a number or translate Flemish depending on a
parameter.
Why did you make your low-level BIT flag a reserved word? Why are you
thinking in terms of assembly language style flags and variant records
instead of rows?
DECLARE @.Count bit
SET @.Count = 1
SELECT
CASE
WHEN @.Count = 1
THEN pe.*, pn.*
ELSE COUNT(*)
END
..
FROM ...
INNER JOIN ... ON ...
WHERE ... ; <<
CASE is an expression and not a control flow device. You can use an
IF-THEN-ELSE construct in T-SQL to mimic procedural coding with variant
records instead of using declarative coding.
Did you also notice that you want to return one column and then want to
return two columns? Arow in a relational table always has a fixed
number of columns, unlike records in a file. Basically, you are still
writing COBOL or some other procedural file-oriented language, but you
are doing it in SQL.
This is a simple matter of cut & paste, not the end of the world.
However, if you are just looking for a newsgroup kludge instead of a
real answer in one query, try:
SELECT
CASE WHEN @.assembly_language_flag = 1
THEN 'violated cohesion'
ELSE COUNT(*) END AS foobar,
CASE WHEN @.assembly_language_flag = 1
THEN PA.x
ELSE 'violated cohesion' END AS x,
etc.
FROM ..
Boy that is awful, isn't it?|||You're so kind as usual writing that in this style. :) Let me guess, you're
from the Western Ukraine, aren't you?
"--CELKO--" <jcelko212@.earthlink.net> wrote in message

> Did you ever have a software engineering course? Remember cohesion?
> The idea that a properly designed code module will perform one
> well-defined task. Good programmers do not write things that return
Tell that to the MS coders (mostly contractors from India:)) who were
usually adding 20 and more parameters like NULL (reserved) to the method
parameter list.overriding one method tons of times. That was always MS
style.

> the square root of a number or translate Flemish depending on a
> parameter.
Yea-yea, pretty close.
The flame is closed.|||Omnibuzz (Omnibuzz@.discussions.microsoft.com) writes:
> Correct me if I am wrong. I don't see any performance benifit by using
> the table valued function over using the actual query, except for the
> fact that the stored procedure looks better :)
Correct, but the presumption was that Just D wanted to the procedure
to look better. That is, he did not want repeat the conditions. And I can
think of four ways to achieve this aim:
1) view/inlined table function.
2) bounce over temp table.
3) dynamic SQL.
4) pre-processor.
In my post I only discussed the first two options, and of these the
TVF gives better performance than the temp table.
In my opinion, using dynamic SQL introduces another level of complexity
which is not worth the pain in this case.
And preprocessor? Well, we have one in our environment, but most
people doesn't.

> The execution plan is not stored for the TVF but is stored in the calling
> SP. And the plan will be recomplied everytime the condition changes.
As I understood it, the JOIN and WHERE conditions of the query are
stable. As for the condition on whether to return COUNT or result set,
that should lead to any recompilation.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||Erland, thanks for your answer.
Yes, the main idea was to make the SP more flexible and maintainable. The
conditions are complex enough to repeat them more than one time, and that's
especially bad if we need to improve/modify them in future, we can easily
make a simple mistake doing that in two places, OR we will have to
copy/paste each time we need to change something. That's why this idea
appeared. But from another side any change like that should not seriously
affect the speed of the code or the whole complexity because having this
divantage we're getting into another trouble which is not acceptable.
That's why I asked this newsgroup for a new, better idea. To implement the
function - then we'll need to maintain this function and provide the
required set of tables and parameters that should be cached in a different
way I guess if we call the function inside our SP. Temporary table - it's
even the worst scenario. Many different ways are able to change the whole
idea and to do one thing crashing all around.
Just D.
"Erland Sommarskog" <esquel@.sommarskog.se> wrote in message
news:Xns97E0F409CDF8DYazorman@.127.0.0.1...
> Omnibuzz (Omnibuzz@.discussions.microsoft.com) writes:
> Correct, but the presumption was that Just D wanted to the procedure
> to look better. That is, he did not want repeat the conditions. And I can
> think of four ways to achieve this aim:
> 1) view/inlined table function.
> 2) bounce over temp table.
> 3) dynamic SQL.
> 4) pre-processor.
> In my post I only discussed the first two options, and of these the
> TVF gives better performance than the temp table.
> In my opinion, using dynamic SQL introduces another level of complexity
> which is not worth the pain in this case.
> And preprocessor? Well, we have one in our environment, but most
> people doesn't.
>
> As I understood it, the JOIN and WHERE conditions of the query are
> stable. As for the condition on whether to return COUNT or result set,
> that should lead to any recompilation.
>
>
> --
> Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
> Books Online for SQL Server 2005 at
> http://www.microsoft.com/technet/pr...oads/books.mspx
> Books Online for SQL Server 2000 at
> http://www.microsoft.com/sql/prodin...ions/books.mspx|||Just D (no@.spam.please) writes:
> That's why I asked this newsgroup for a new, better idea. To implement the
> function - then we'll need to maintain this function and provide the
> required set of tables and parameters that should be cached in a different
> way I guess if we call the function inside our SP.
Not really sure what you mean here. An inline-table function does not have
any query plan of its own. An inline table function is really a macro that
the optimizer pastes in before building the query plan. (Note that this
does not apply to multi-statement functions nor to scalar functions.)
As for the maintenance, you would move that to the function. The procedure
would just be a wrapper on the function.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx

'Invalid cursor state' error when saving table changes

I've created a small DB (one table so far, 0 records as yet). When I try to
change the table structure via Enterprise Manager, I get the following
error:
/*
05 August 2004 16:02:36
User:
Server: HYPERION
Database: TADB
Application: MS SQLEM - Data Tools
*/
'TANumbers' table
- Unable to rename column from 'AdviceNoteID' to 'AdviceNote'.
ODBC error: [Microsoft][ODBC SQL Server Driver]Invalid cursor state
The actual change I was trying to do worked, or at least it appears to have
done.
BOL suggest that this error might be due to the fact that the DB or the Tlog
is full. With no data an no transactions so far, that is unlikely. Plus EM
reports that there is 8.43 Mb free out of 10Mb!
Any ideas anyone?
Thanks
ChrisHi CJM,
From your descriptions, I understood you could not rename your column name
by SQL Server Enterprise Manager. Have I understood you? If there is
anything I misunderstood, please feel free to let me know.
Admittedly, it is a known issue for us when using SQL Server Enterprise
Manager. You will have to use sp_rename in Query Analyzer instead. I am
sorry for the inconvenience you may encounter.
More information of how to use sp_rename could be found in BooksOnline or
MSDN Online
sp_rename
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_
sp_ra-rz_3ns5.asp
Thank you for your patience and cooperation. If you have any questions or
concerns, don't hesitate to let me know. We are here to be of assistance!
Sincerely yours,
Mingqing Cheng
Microsoft Developer Community Support
---
Introduction to Yukon! - http://www.microsoft.com/sql/yukon
This posting is provided "as is" with no warranties and confers no rights.
Please reply to newsgroups only, many thanks!|||Hi,
I'm just curious as to why *this* table on *this* DB suffers from this
problem?
I've never had a problem on this server before, so what is it about this DB
that is so different?
Chris
""Mingqing Cheng [MSFT]"" <v-mingqc@.online.microsoft.com> wrote in message
news:v5%23isS4eEHA.1060@.cpmsftngxa06.phx.gbl...
> Hi CJM,
> From your descriptions, I understood you could not rename your column name
> by SQL Server Enterprise Manager. Have I understood you? If there is
> anything I misunderstood, please feel free to let me know.
> Admittedly, it is a known issue for us when using SQL Server Enterprise
> Manager. You will have to use sp_rename in Query Analyzer instead. I am
> sorry for the inconvenience you may encounter.
> More information of how to use sp_rename could be found in BooksOnline or
> MSDN Online
> sp_rename
>
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_
> sp_ra-rz_3ns5.asp
>|||Hi CJM,
This issue was reported in SP3 and higher version, it was fixed in SQL
Server 8.00.902
It is very possible that changes of columns are made successfully before
you upgrade to SQL Server SP3 .
Thank you for your patience and cooperation. If you have any questions or
concerns, don't hesitate to let me know. We are here to be of assistance!
Sincerely yours,
Mingqing Cheng
Microsoft Developer Community Support
---
Introduction to Yukon! - http://www.microsoft.com/sql/yukon
This posting is provided "as is" with no warranties and confers no rights.
Please reply to newsgroups only, many thanks!|||""Mingqing Cheng [MSFT]"" <v-mingqc@.online.microsoft.com> wrote in message
news:k37x%23bDfEHA.3212@.cpmsftngxa06.phx.gbl...
> Hi CJM,
> This issue was reported in SP3 and higher version, it was fixed in SQL
> Server 8.00.902
> It is very possible that changes of columns are made successfully before
> you upgrade to SQL Server SP3 .
>
I'm a bit confused...
How do I get hold of v8.00.902? I thought I was up to date with SP3a...
Chris|||Hi Chris,
Thanks for your prompt updates!
For 8.00.0902 or higher, a supported fix is now available from Microsoft,
but it is only intended to correct the problem that is described in this
article. Apply it only to systems that are experiencing this specific
problem.
To resolve this problem, please contact Microsoft Product Support Services
to obtain the fix. For a complete list of Microsoft Product Support
Services phone numbers and information about support costs, visit the
following Microsoft Web site:
http://support.microsoft.com/default.aspx?scid=fh;EN-US;CNTACTMS
Asking hotfix will be a FREE case. If PSS staff asking a KB for this issue,
please show me the link and KB No below
FIX: Profiler RPC events truncate parameters that have a text data type to
16 characters
http://support.microsoft.com/?id=839688
You will receive Build 8.00.0927 instead from PSS, based on my testing,
this hotfix could resolved the problm as you described. BTW, I cannot find
excatly Build 8.00.0902 and its relative KB internal:(
If you want PSS Staff be notified of this newsgroup thread, show him the
following information
Tomcat IssueID: 24064701
Thank you for your patience and cooperation. If you have any questions or
concerns, don't hesitate to let me know. We are here to be of assistance!
Sincerely yours,
Mingqing Cheng
Microsoft Developer Community Support
---
Introduction to Yukon! - http://www.microsoft.com/sql/yukon
This posting is provided "as is" with no warranties and confers no rights.
Please reply to newsgroups only, many thanks!|||Mingqing,
I've downloaded, installed & tested the patch and everything works fine now.
Thanks
Chris
""Mingqing Cheng [MSFT]"" <v-mingqc@.online.microsoft.com> wrote in message
news:c6mfgnofEHA.1200@.cpmsftngxa06.phx.gbl...
> Hi Chris,
> Thanks for your prompt updates!
> For 8.00.0902 or higher, a supported fix is now available from Microsoft,
> but it is only intended to correct the problem that is described in this
> article. Apply it only to systems that are experiencing this specific
> problem.
>|||Hi Chris,
It's great to hear that you have resolved it! Thanks for your kindest reply
letting me know the status of your issue.
If you encounter any questions or difficulties using SQL Server, please
feel free to post here. We are always here to be of assistance!
Thanks again for using MSDN Managed Newsgroup!
Sincerely yours,
Mingqing Cheng
Microsoft Developer Community Support
---
Introduction to Yukon! - http://www.microsoft.com/sql/yukon
This posting is provided "as is" with no warranties and confers no rights.
Please reply to newsgroups only, many thanks!

'Invalid cursor state' error when saving table changes

I've created a small DB (one table so far, 0 records as yet). When I try to
change the table structure via Enterprise Manager, I get the following
error:
/*
05 August 2004 16:02:36
User:
Server: HYPERION
Database: TADB
Application: MS SQLEM - Data Tools
*/
'TANumbers' table
- Unable to rename column from 'AdviceNoteID' to 'AdviceNote'.
ODBC error: [Microsoft][ODBC SQL Server Driver]Invalid cursor state
The actual change I was trying to do worked, or at least it appears to have
done.
BOL suggest that this error might be due to the fact that the DB or the Tlog
is full. With no data an no transactions so far, that is unlikely. Plus EM
reports that there is 8.43 Mb free out of 10Mb!
Any ideas anyone?
Thanks
ChrisHi CJM,
From your descriptions, I understood you could not rename your column name
by SQL Server Enterprise Manager. Have I understood you? If there is
anything I misunderstood, please feel free to let me know.
Admittedly, it is a known issue for us when using SQL Server Enterprise
Manager. You will have to use sp_rename in Query Analyzer instead. I am
sorry for the inconvenience you may encounter.
More information of how to use sp_rename could be found in BooksOnline or
MSDN Online
sp_rename
http://msdn.microsoft.com/library/d...-us/tsqlref/ts_
sp_ra-rz_3ns5.asp
Thank you for your patience and cooperation. If you have any questions or
concerns, don't hesitate to let me know. We are here to be of assistance!
Sincerely yours,
Mingqing Cheng
Microsoft Developer Community Support
---
Introduction to Yukon! - http://www.microsoft.com/sql/yukon
This posting is provided "as is" with no warranties and confers no rights.
Please reply to newsgroups only, many thanks!|||Hi,
I'm just curious as to why *this* table on *this* DB suffers from this
problem?
I've never had a problem on this server before, so what is it about this DB
that is so different?
Chris
""Mingqing Cheng [MSFT]"" <v-mingqc@.online.microsoft.com> wrote in messa
ge
news:v5%23isS4eEHA.1060@.cpmsftngxa06.phx.gbl...
> Hi CJM,
> From your descriptions, I understood you could not rename your column name
> by SQL Server Enterprise Manager. Have I understood you? If there is
> anything I misunderstood, please feel free to let me know.
> Admittedly, it is a known issue for us when using SQL Server Enterprise
> Manager. You will have to use sp_rename in Query Analyzer instead. I am
> sorry for the inconvenience you may encounter.
> More information of how to use sp_rename could be found in BooksOnline or
> MSDN Online
> sp_rename
>
http://msdn.microsoft.com/library/d...-us/tsqlref/ts_
> sp_ra-rz_3ns5.asp
>|||Hi CJM,
This issue was reported in SP3 and higher version, it was fixed in SQL
Server 8.00.902
It is very possible that changes of columns are made successfully before
you upgrade to SQL Server SP3 .
Thank you for your patience and cooperation. If you have any questions or
concerns, don't hesitate to let me know. We are here to be of assistance!
Sincerely yours,
Mingqing Cheng
Microsoft Developer Community Support
---
Introduction to Yukon! - http://www.microsoft.com/sql/yukon
This posting is provided "as is" with no warranties and confers no rights.
Please reply to newsgroups only, many thanks!|||""Mingqing Cheng [MSFT]"" <v-mingqc@.online.microsoft.com> wrote in messa
ge
news:k37x%23bDfEHA.3212@.cpmsftngxa06.phx.gbl...
> Hi CJM,
> This issue was reported in SP3 and higher version, it was fixed in SQL
> Server 8.00.902
> It is very possible that changes of columns are made successfully before
> you upgrade to SQL Server SP3 .
>
I'm a bit confused...
How do I get hold of v8.00.902? I thought I was up to date with SP3a...
Chris|||Hi Chris,
Thanks for your prompt updates!
For 8.00.0902 or higher, a supported fix is now available from Microsoft,
but it is only intended to correct the problem that is described in this
article. Apply it only to systems that are experiencing this specific
problem.
To resolve this problem, please contact Microsoft Product Support Services
to obtain the fix. For a complete list of Microsoft Product Support
Services phone numbers and information about support costs, visit the
following Microsoft Web site:
http://support.microsoft.com/defaul...;EN-US;CNTACTMS
Asking hotfix will be a FREE case. If PSS staff asking a KB for this issue,
please show me the link and KB No below
FIX: Profiler RPC events truncate parameters that have a text data type to
16 characters
http://support.microsoft.com/?id=839688
You will receive Build 8.00.0927 instead from PSS, based on my testing,
this hotfix could resolved the problm as you described. BTW, I cannot find
excatly Build 8.00.0902 and its relative KB internal
If you want PSS Staff be notified of this newsgroup thread, show him the
following information
Tomcat IssueID: 24064701
Thank you for your patience and cooperation. If you have any questions or
concerns, don't hesitate to let me know. We are here to be of assistance!
Sincerely yours,
Mingqing Cheng
Microsoft Developer Community Support
---
Introduction to Yukon! - http://www.microsoft.com/sql/yukon
This posting is provided "as is" with no warranties and confers no rights.
Please reply to newsgroups only, many thanks!|||Mingqing,
I've downloaded, installed & tested the patch and everything works fine now.
Thanks
Chris
""Mingqing Cheng [MSFT]"" <v-mingqc@.online.microsoft.com> wrote in messa
ge
news:c6mfgnofEHA.1200@.cpmsftngxa06.phx.gbl...
> Hi Chris,
> Thanks for your prompt updates!
> For 8.00.0902 or higher, a supported fix is now available from Microsoft,
> but it is only intended to correct the problem that is described in this
> article. Apply it only to systems that are experiencing this specific
> problem.
>|||Hi Chris,
It's great to hear that you have resolved it! Thanks for your kindest reply
letting me know the status of your issue.
If you encounter any questions or difficulties using SQL Server, please
feel free to post here. We are always here to be of assistance!
Thanks again for using MSDN Managed Newsgroup!
Sincerely yours,
Mingqing Cheng
Microsoft Developer Community Support
---
Introduction to Yukon! - http://www.microsoft.com/sql/yukon
This posting is provided "as is" with no warranties and confers no rights.
Please reply to newsgroups only, many thanks!

'Invalid cursor state' error when saving table changes

I've created a small DB (one table so far, 0 records as yet). When I try to
change the table structure via Enterprise Manager, I get the following
error:
/*
05 August 2004 16:02:36
User:
Server: HYPERION
Database: TADB
Application: MS SQLEM - Data Tools
*/
'TANumbers' table
- Unable to rename column from 'AdviceNoteID' to 'AdviceNote'.
ODBC error: [Microsoft][ODBC SQL Server Driver]Invalid cursor state
The actual change I was trying to do worked, or at least it appears to have
done.
BOL suggest that this error might be due to the fact that the DB or the Tlog
is full. With no data an no transactions so far, that is unlikely. Plus EM
reports that there is 8.43 Mb free out of 10Mb!
Any ideas anyone?
Thanks
Chris
Hi CJM,
From your descriptions, I understood you could not rename your column name
by SQL Server Enterprise Manager. Have I understood you? If there is
anything I misunderstood, please feel free to let me know.
Admittedly, it is a known issue for us when using SQL Server Enterprise
Manager. You will have to use sp_rename in Query Analyzer instead. I am
sorry for the inconvenience you may encounter.
More information of how to use sp_rename could be found in BooksOnline or
MSDN Online
sp_rename
http://msdn.microsoft.com/library/de...us/tsqlref/ts_
sp_ra-rz_3ns5.asp
Thank you for your patience and cooperation. If you have any questions or
concerns, don't hesitate to let me know. We are here to be of assistance!
Sincerely yours,
Mingqing Cheng
Microsoft Developer Community Support
Introduction to Yukon! - http://www.microsoft.com/sql/yukon
This posting is provided "as is" with no warranties and confers no rights.
Please reply to newsgroups only, many thanks!
|||Hi,
I'm just curious as to why *this* table on *this* DB suffers from this
problem?
I've never had a problem on this server before, so what is it about this DB
that is so different?
Chris
""Mingqing Cheng [MSFT]"" <v-mingqc@.online.microsoft.com> wrote in message
news:v5%23isS4eEHA.1060@.cpmsftngxa06.phx.gbl...
> Hi CJM,
> From your descriptions, I understood you could not rename your column name
> by SQL Server Enterprise Manager. Have I understood you? If there is
> anything I misunderstood, please feel free to let me know.
> Admittedly, it is a known issue for us when using SQL Server Enterprise
> Manager. You will have to use sp_rename in Query Analyzer instead. I am
> sorry for the inconvenience you may encounter.
> More information of how to use sp_rename could be found in BooksOnline or
> MSDN Online
> sp_rename
>
http://msdn.microsoft.com/library/de...us/tsqlref/ts_
> sp_ra-rz_3ns5.asp
>
|||Hi CJM,
This issue was reported in SP3 and higher version, it was fixed in SQL
Server 8.00.902
It is very possible that changes of columns are made successfully before
you upgrade to SQL Server SP3 .
Thank you for your patience and cooperation. If you have any questions or
concerns, don't hesitate to let me know. We are here to be of assistance!
Sincerely yours,
Mingqing Cheng
Microsoft Developer Community Support
Introduction to Yukon! - http://www.microsoft.com/sql/yukon
This posting is provided "as is" with no warranties and confers no rights.
Please reply to newsgroups only, many thanks!
|||""Mingqing Cheng [MSFT]"" <v-mingqc@.online.microsoft.com> wrote in message
news:k37x%23bDfEHA.3212@.cpmsftngxa06.phx.gbl...
> Hi CJM,
> This issue was reported in SP3 and higher version, it was fixed in SQL
> Server 8.00.902
> It is very possible that changes of columns are made successfully before
> you upgrade to SQL Server SP3 .
>
I'm a bit confused...
How do I get hold of v8.00.902? I thought I was up to date with SP3a...
Chris
|||Hi Chris,
Thanks for your prompt updates!
For 8.00.0902 or higher, a supported fix is now available from Microsoft,
but it is only intended to correct the problem that is described in this
article. Apply it only to systems that are experiencing this specific
problem.
To resolve this problem, please contact Microsoft Product Support Services
to obtain the fix. For a complete list of Microsoft Product Support
Services phone numbers and information about support costs, visit the
following Microsoft Web site:
http://support.microsoft.com/default...EN-US;CNTACTMS
Asking hotfix will be a FREE case. If PSS staff asking a KB for this issue,
please show me the link and KB No below
FIX: Profiler RPC events truncate parameters that have a text data type to
16 characters
http://support.microsoft.com/?id=839688
You will receive Build 8.00.0927 instead from PSS, based on my testing,
this hotfix could resolved the problm as you described. BTW, I cannot find
excatly Build 8.00.0902 and its relative KB internal
If you want PSS Staff be notified of this newsgroup thread, show him the
following information
Tomcat IssueID: 24064701
Thank you for your patience and cooperation. If you have any questions or
concerns, don't hesitate to let me know. We are here to be of assistance!
Sincerely yours,
Mingqing Cheng
Microsoft Developer Community Support
Introduction to Yukon! - http://www.microsoft.com/sql/yukon
This posting is provided "as is" with no warranties and confers no rights.
Please reply to newsgroups only, many thanks!
|||Mingqing,
I've downloaded, installed & tested the patch and everything works fine now.
Thanks
Chris
""Mingqing Cheng [MSFT]"" <v-mingqc@.online.microsoft.com> wrote in message
news:c6mfgnofEHA.1200@.cpmsftngxa06.phx.gbl...
> Hi Chris,
> Thanks for your prompt updates!
> For 8.00.0902 or higher, a supported fix is now available from Microsoft,
> but it is only intended to correct the problem that is described in this
> article. Apply it only to systems that are experiencing this specific
> problem.
>
|||Hi Chris,
It's great to hear that you have resolved it! Thanks for your kindest reply
letting me know the status of your issue.
If you encounter any questions or difficulties using SQL Server, please
feel free to post here. We are always here to be of assistance!
Thanks again for using MSDN Managed Newsgroup!
Sincerely yours,
Mingqing Cheng
Microsoft Developer Community Support
Introduction to Yukon! - http://www.microsoft.com/sql/yukon
This posting is provided "as is" with no warranties and confers no rights.
Please reply to newsgroups only, many thanks!

Invalid attempt to read when no data is present

I am getting this error message when I go to run my report. I have another
report that is almost identical and when there is no records to return it
doesn't generate an error message. Anyone have any ideas?
Thanks in advance.I found my problem, I had an extra if statement in my stored procedure. Not
sure how this stored procedure executed though but my report is working fine
now.
"GORAMS" wrote:
> I am getting this error message when I go to run my report. I have another
> report that is almost identical and when there is no records to return it
> doesn't generate an error message. Anyone have any ideas?
> Thanks in advance.

Interrupt DBCC SHRINKDATABASE

BACKGROUND:
We have a large (330 GB) OLTP database. After archiving a
lot of records, we still have the same size of db and full
backups (BackupExec). The database has 2 filegroups, 24
data files, a log file over 4 logical drives. Log is
small, gets backup every hour and its file is no issue.
The data files range from 50 GB down to 70 MB since drives
and files were added as needed. Files auto grow, but do
not auto shrink. All maintenance is done manually via
Query Analyzer during scheduled times. A replicated
database with a subset of the information is used for OLTP
during these times.
Our goal was to shrink the database to recover OS space
and minimize backup time. This would be done with NO
LOAD. We scheduled 5 to 24 hours (based on some rough
estimates).
The SHRINKDATABASE has been running for 26 hours when load
was shifted back. It has now been running 31 hours. We
may need to cancel (using stop on Query Analyzer. However,
we hesitate to do so and risk corruption.
I found one reference in a PPT about shrink saying "may be
stopped without losing work". However, this is not a
mainstream URL:
http://www.microsoft.com/uk/partner/strategy/server_busines
s_agility/customers/business-
intelligence/downloads/RDBMSFeaturesandDataWarehousing.ppt
I would like an official, "Yes, if you started the DBCC
SHRINKDATABASE in the Query Analyzer, you can stop it by
using the Cancel Query. There will be now rollback time
or risk of corruption".
The documentation for these long running commands should
have a section on Cancellation.
Even an unofficial response is welcome.
Thanks,
SMI had what sounds like the same problem on SQL 2000. I calculated that the
database would take a week to come up. Not acceptable.
I had to use this solution 3 times: It worked just fine each time. I did
not get fired
It comes down to running this command;
UPDATE SYSDATABASES SET STATUS=-32768 WHERE NAME='DBNAME'
then restart the server.
It set the database in emergency mode, which bypasses all recovery stuff
http://support.microsoft.com/default.aspx?scid=kb;EN-US;165918
"Spencer Morley" <spencermatsynergisticsoftwaredotcom@.no-spam.net> wrote in
message news:023001c34f1d$32c14a40$a101280a@.phx.gbl...
> BACKGROUND:
> We have a large (330 GB) OLTP database. After archiving a
> lot of records, we still have the same size of db and full
> backups (BackupExec). The database has 2 filegroups, 24
> data files, a log file over 4 logical drives. Log is
> small, gets backup every hour and its file is no issue.
> The data files range from 50 GB down to 70 MB since drives
> and files were added as needed. Files auto grow, but do
> not auto shrink. All maintenance is done manually via
> Query Analyzer during scheduled times. A replicated
> database with a subset of the information is used for OLTP
> during these times.
> Our goal was to shrink the database to recover OS space
> and minimize backup time. This would be done with NO
> LOAD. We scheduled 5 to 24 hours (based on some rough
> estimates).
> The SHRINKDATABASE has been running for 26 hours when load
> was shifted back. It has now been running 31 hours. We
> may need to cancel (using stop on Query Analyzer. However,
> we hesitate to do so and risk corruption.
> I found one reference in a PPT about shrink saying "may be
> stopped without losing work". However, this is not a
> mainstream URL:
> http://www.microsoft.com/uk/partner/strategy/server_busines
> s_agility/customers/business-
> intelligence/downloads/RDBMSFeaturesandDataWarehousing.ppt
> I would like an official, "Yes, if you started the DBCC
> SHRINKDATABASE in the Query Analyzer, you can stop it by
> using the Cancel Query. There will be now rollback time
> or risk of corruption".
> The documentation for these long running commands should
> have a section on Cancellation.
> Even an unofficial response is welcome.
> Thanks,
> SM
>|||You absolutely can stop shrink at any time with no rollback cost and no risk
of corruption.
--
Paul Randal
DBCC Technical Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"Spencer Morley" <spencermatsynergisticsoftwaredotcom@.no-spam.net> wrote in
message news:023001c34f1d$32c14a40$a101280a@.phx.gbl...
> BACKGROUND:
> We have a large (330 GB) OLTP database. After archiving a
> lot of records, we still have the same size of db and full
> backups (BackupExec). The database has 2 filegroups, 24
> data files, a log file over 4 logical drives. Log is
> small, gets backup every hour and its file is no issue.
> The data files range from 50 GB down to 70 MB since drives
> and files were added as needed. Files auto grow, but do
> not auto shrink. All maintenance is done manually via
> Query Analyzer during scheduled times. A replicated
> database with a subset of the information is used for OLTP
> during these times.
> Our goal was to shrink the database to recover OS space
> and minimize backup time. This would be done with NO
> LOAD. We scheduled 5 to 24 hours (based on some rough
> estimates).
> The SHRINKDATABASE has been running for 26 hours when load
> was shifted back. It has now been running 31 hours. We
> may need to cancel (using stop on Query Analyzer. However,
> we hesitate to do so and risk corruption.
> I found one reference in a PPT about shrink saying "may be
> stopped without losing work". However, this is not a
> mainstream URL:
> http://www.microsoft.com/uk/partner/strategy/server_busines
> s_agility/customers/business-
> intelligence/downloads/RDBMSFeaturesandDataWarehousing.ppt
> I would like an official, "Yes, if you started the DBCC
> SHRINKDATABASE in the Query Analyzer, you can stop it by
> using the Cancel Query. There will be now rollback time
> or risk of corruption".
> The documentation for these long running commands should
> have a section on Cancellation.
> Even an unofficial response is welcome.
> Thanks,
> SM
>|||Hi Spencer,
Firstly, you don't say how big the database is or what other load is running
on it. Shrink will wait for locks it cannot immediately acquire so there may
be something blocking it. This is especially likely with long-running,
pervasive update queries.
Shrink will always run quicker without a concurrent workload, so quiescing
the database (using single-user mode, for example) should decrease the
runtime.
The wizard option you mention is the same underlying code as shrinkdatabase,
and so can be run online and can also be safely interrupted with no risk of
corruption or losing already completed work.
Regards,
Paul.
--
Paul Randal
DBCC Technical Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"Spencer Morely" <spencermatsynergisticsoftwaredotcom@.no-spam.net> wrote in
message news:022601c35155$f11b15e0$a401280a@.phx.gbl...
> While, we have been running the SHRINKDATABASE for 98
> hours now! Production does not seem to be affected.
> Assume this is because we have ample CPU, memory, and I/O?
> Should I expect this kind of run time on a database of
> this size?
> The process has shunk 6 files so far and we have gained
> about 2 GB per file. These are the files on the filegroup
> that is more static, so we expect better result on the
> other files. Also, these are the files on the RAID 5.
> Once the work on the current file is complete, we will
> move to those on the RAID 10, thus speedier?
> I plan to let this process run to completion, but we have
> a number of db's of simular size that will need to be
> shrunk as archiving is done.
> My question:
> Is there a way to force priority to a shrink (single user
> mode, etc) that will can be used while db is out of
> production that would be quicker?
> Also, if I use the "Database Maintenance Plan
> Wizzard", "Update Data Optimization Information"
> page, "Remove unused space from database files" option to
> shrink will it faster? Is the lower level process
> essentially the same as a SHRINKDATABSE? Can it be run
> while database is in use?
>|||yah but this database is in recovery mode, no?
"Paul S Randal [MS]" <prandal@.online.microsoft.com> wrote in message
news:%23Mi22eiUDHA.2184@.TK2MSFTNGP10.phx.gbl...
> Hi Spencer,
> Firstly, you don't say how big the database is or what other load is
running
> on it. Shrink will wait for locks it cannot immediately acquire so there
may
> be something blocking it. This is especially likely with long-running,
> pervasive update queries.
> Shrink will always run quicker without a concurrent workload, so quiescing
> the database (using single-user mode, for example) should decrease the
> runtime.
> The wizard option you mention is the same underlying code as
shrinkdatabase,
> and so can be run online and can also be safely interrupted with no risk
of
> corruption or losing already completed work.
> Regards,
> Paul.
> --
> Paul Randal
> DBCC Technical Lead, Microsoft SQL Server Storage Engine
> This posting is provided "AS IS" with no warranties, and confers no
rights.
> "Spencer Morely" <spencermatsynergisticsoftwaredotcom@.no-spam.net> wrote
in
> message news:022601c35155$f11b15e0$a401280a@.phx.gbl...
> > While, we have been running the SHRINKDATABASE for 98
> > hours now! Production does not seem to be affected.
> > Assume this is because we have ample CPU, memory, and I/O?
> >
> > Should I expect this kind of run time on a database of
> > this size?
> >
> > The process has shunk 6 files so far and we have gained
> > about 2 GB per file. These are the files on the filegroup
> > that is more static, so we expect better result on the
> > other files. Also, these are the files on the RAID 5.
> > Once the work on the current file is complete, we will
> > move to those on the RAID 10, thus speedier?
> >
> > I plan to let this process run to completion, but we have
> > a number of db's of simular size that will need to be
> > shrunk as archiving is done.
> >
> > My question:
> > Is there a way to force priority to a shrink (single user
> > mode, etc) that will can be used while db is out of
> > production that would be quicker?
> >
> > Also, if I use the "Database Maintenance Plan
> > Wizzard", "Update Data Optimization Information"
> > page, "Remove unused space from database files" option to
> > shrink will it faster? Is the lower level process
> > essentially the same as a SHRINKDATABSE? Can it be run
> > while database is in use?
> >
> >
>|||I don't see where recovery mode is mentioned in the original post..
--
Paul Randal
DBCC Technical Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"Roy in (H.E.Double Toothpicks)" <Roy@.DoubleToothpicks.com> wrote in message
news:O0Pn1vnUDHA.1928@.TK2MSFTNGP12.phx.gbl...
> yah but this database is in recovery mode, no?
> "Paul S Randal [MS]" <prandal@.online.microsoft.com> wrote in message
> news:%23Mi22eiUDHA.2184@.TK2MSFTNGP10.phx.gbl...
> > Hi Spencer,
> >
> > Firstly, you don't say how big the database is or what other load is
> running
> > on it. Shrink will wait for locks it cannot immediately acquire so there
> may
> > be something blocking it. This is especially likely with long-running,
> > pervasive update queries.
> >
> > Shrink will always run quicker without a concurrent workload, so
quiescing
> > the database (using single-user mode, for example) should decrease the
> > runtime.
> >
> > The wizard option you mention is the same underlying code as
> shrinkdatabase,
> > and so can be run online and can also be safely interrupted with no risk
> of
> > corruption or losing already completed work.
> >
> > Regards,
> >
> > Paul.
> >
> > --
> > Paul Randal
> > DBCC Technical Lead, Microsoft SQL Server Storage Engine
> >
> > This posting is provided "AS IS" with no warranties, and confers no
> rights.
> > "Spencer Morely" <spencermatsynergisticsoftwaredotcom@.no-spam.net> wrote
> in
> > message news:022601c35155$f11b15e0$a401280a@.phx.gbl...
> > > While, we have been running the SHRINKDATABASE for 98
> > > hours now! Production does not seem to be affected.
> > > Assume this is because we have ample CPU, memory, and I/O?
> > >
> > > Should I expect this kind of run time on a database of
> > > this size?
> > >
> > > The process has shunk 6 files so far and we have gained
> > > about 2 GB per file. These are the files on the filegroup
> > > that is more static, so we expect better result on the
> > > other files. Also, these are the files on the RAID 5.
> > > Once the work on the current file is complete, we will
> > > move to those on the RAID 10, thus speedier?
> > >
> > > I plan to let this process run to completion, but we have
> > > a number of db's of simular size that will need to be
> > > shrunk as archiving is done.
> > >
> > > My question:
> > > Is there a way to force priority to a shrink (single user
> > > mode, etc) that will can be used while db is out of
> > > production that would be quicker?
> > >
> > > Also, if I use the "Database Maintenance Plan
> > > Wizzard", "Update Data Optimization Information"
> > > page, "Remove unused space from database files" option to
> > > shrink will it faster? Is the lower level process
> > > essentially the same as a SHRINKDATABSE? Can it be run
> > > while database is in use?
> > >
> > >
> >
> >
>|||true, i inferred that.
Still, I guess that it is in recovery mode, although the guy didn't notice
or say that.
"Paul S Randal [MS]" <prandal@.online.microsoft.com> wrote in message
news:Os8udJsUDHA.1004@.TK2MSFTNGP12.phx.gbl...
> I don't see where recovery mode is mentioned in the original post..
> --
> Paul Randal
> DBCC Technical Lead, Microsoft SQL Server Storage Engine
> This posting is provided "AS IS" with no warranties, and confers no
rights.
> "Roy in (H.E.Double Toothpicks)" <Roy@.DoubleToothpicks.com> wrote in
message
> news:O0Pn1vnUDHA.1928@.TK2MSFTNGP12.phx.gbl...
> > yah but this database is in recovery mode, no?
> >
> > "Paul S Randal [MS]" <prandal@.online.microsoft.com> wrote in message
> > news:%23Mi22eiUDHA.2184@.TK2MSFTNGP10.phx.gbl...
> > > Hi Spencer,
> > >
> > > Firstly, you don't say how big the database is or what other load is
> > running
> > > on it. Shrink will wait for locks it cannot immediately acquire so
there
> > may
> > > be something blocking it. This is especially likely with long-running,
> > > pervasive update queries.
> > >
> > > Shrink will always run quicker without a concurrent workload, so
> quiescing
> > > the database (using single-user mode, for example) should decrease the
> > > runtime.
> > >
> > > The wizard option you mention is the same underlying code as
> > shrinkdatabase,
> > > and so can be run online and can also be safely interrupted with no
risk
> > of
> > > corruption or losing already completed work.
> > >
> > > Regards,
> > >
> > > Paul.
> > >
> > > --
> > > Paul Randal
> > > DBCC Technical Lead, Microsoft SQL Server Storage Engine
> > >
> > > This posting is provided "AS IS" with no warranties, and confers no
> > rights.
> > > "Spencer Morely" <spencermatsynergisticsoftwaredotcom@.no-spam.net>
wrote
> > in
> > > message news:022601c35155$f11b15e0$a401280a@.phx.gbl...
> > > > While, we have been running the SHRINKDATABASE for 98
> > > > hours now! Production does not seem to be affected.
> > > > Assume this is because we have ample CPU, memory, and I/O?
> > > >
> > > > Should I expect this kind of run time on a database of
> > > > this size?
> > > >
> > > > The process has shunk 6 files so far and we have gained
> > > > about 2 GB per file. These are the files on the filegroup
> > > > that is more static, so we expect better result on the
> > > > other files. Also, these are the files on the RAID 5.
> > > > Once the work on the current file is complete, we will
> > > > move to those on the RAID 10, thus speedier?
> > > >
> > > > I plan to let this process run to completion, but we have
> > > > a number of db's of simular size that will need to be
> > > > shrunk as archiving is done.
> > > >
> > > > My question:
> > > > Is there a way to force priority to a shrink (single user
> > > > mode, etc) that will can be used while db is out of
> > > > production that would be quicker?
> > > >
> > > > Also, if I use the "Database Maintenance Plan
> > > > Wizzard", "Update Data Optimization Information"
> > > > page, "Remove unused space from database files" option to
> > > > shrink will it faster? Is the lower level process
> > > > essentially the same as a SHRINKDATABSE? Can it be run
> > > > while database is in use?
> > > >
> > > >
> > >
> > >
> >
> >
>|||Why would the db be in recovery mode?
--
Paul Randal
DBCC Technical Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"Roy in (H.E.Double Toothpicks)" <Roy@.DoubleToothpicks.com> wrote in message
news:#29aFlvUDHA.2312@.TK2MSFTNGP12.phx.gbl...
> true, i inferred that.
> Still, I guess that it is in recovery mode, although the guy didn't
notice
> or say that.
>
> "Paul S Randal [MS]" <prandal@.online.microsoft.com> wrote in message
> news:Os8udJsUDHA.1004@.TK2MSFTNGP12.phx.gbl...
> > I don't see where recovery mode is mentioned in the original post..
> >
> > --
> > Paul Randal
> > DBCC Technical Lead, Microsoft SQL Server Storage Engine
> >
> > This posting is provided "AS IS" with no warranties, and confers no
> rights.
> >
> > "Roy in (H.E.Double Toothpicks)" <Roy@.DoubleToothpicks.com> wrote in
> message
> > news:O0Pn1vnUDHA.1928@.TK2MSFTNGP12.phx.gbl...
> > > yah but this database is in recovery mode, no?
> > >
> > > "Paul S Randal [MS]" <prandal@.online.microsoft.com> wrote in message
> > > news:%23Mi22eiUDHA.2184@.TK2MSFTNGP10.phx.gbl...
> > > > Hi Spencer,
> > > >
> > > > Firstly, you don't say how big the database is or what other load is
> > > running
> > > > on it. Shrink will wait for locks it cannot immediately acquire so
> there
> > > may
> > > > be something blocking it. This is especially likely with
long-running,
> > > > pervasive update queries.
> > > >
> > > > Shrink will always run quicker without a concurrent workload, so
> > quiescing
> > > > the database (using single-user mode, for example) should decrease
the
> > > > runtime.
> > > >
> > > > The wizard option you mention is the same underlying code as
> > > shrinkdatabase,
> > > > and so can be run online and can also be safely interrupted with no
> risk
> > > of
> > > > corruption or losing already completed work.
> > > >
> > > > Regards,
> > > >
> > > > Paul.
> > > >
> > > > --
> > > > Paul Randal
> > > > DBCC Technical Lead, Microsoft SQL Server Storage Engine
> > > >
> > > > This posting is provided "AS IS" with no warranties, and confers no
> > > rights.
> > > > "Spencer Morely" <spencermatsynergisticsoftwaredotcom@.no-spam.net>
> wrote
> > > in
> > > > message news:022601c35155$f11b15e0$a401280a@.phx.gbl...
> > > > > While, we have been running the SHRINKDATABASE for 98
> > > > > hours now! Production does not seem to be affected.
> > > > > Assume this is because we have ample CPU, memory, and I/O?
> > > > >
> > > > > Should I expect this kind of run time on a database of
> > > > > this size?
> > > > >
> > > > > The process has shunk 6 files so far and we have gained
> > > > > about 2 GB per file. These are the files on the filegroup
> > > > > that is more static, so we expect better result on the
> > > > > other files. Also, these are the files on the RAID 5.
> > > > > Once the work on the current file is complete, we will
> > > > > move to those on the RAID 10, thus speedier?
> > > > >
> > > > > I plan to let this process run to completion, but we have
> > > > > a number of db's of simular size that will need to be
> > > > > shrunk as archiving is done.
> > > > >
> > > > > My question:
> > > > > Is there a way to force priority to a shrink (single user
> > > > > mode, etc) that will can be used while db is out of
> > > > > production that would be quicker?
> > > > >
> > > > > Also, if I use the "Database Maintenance Plan
> > > > > Wizzard", "Update Data Optimization Information"
> > > > > page, "Remove unused space from database files" option to
> > > > > shrink will it faster? Is the lower level process
> > > > > essentially the same as a SHRINKDATABSE? Can it be run
> > > > > while database is in use?
> > > > >
> > > > >
> > > >
> > > >
> > >
> > >
> >
> >
>

Interpreting the percentage in decision-tree model

Hi,

I used a decision-tree mining-model to describe and predict fraud. The table contains 1039 records with 775 distinct value of A-number (the calling party). I used 9 columns in the model. SQL Server reports that only 3 columns are significant in predicting the fraud

- BPN_is_too_short (called party-number is too short)
- Duration_is_zero
- Invalid_area_code

The key-column in A-number, and the predicted column is Is_Fraud with the range of values are only 0 and 1. There's no record with NULL (missing-value) in the column Is_Fraud.

Mining Legend shows in the first split
[-] 625 cases of fraud
[-] 150 cases of non-fraud
[-] 0 cases of missing

In addition to that, Mining Legend shows
[-] 79.69% of fraud
[-] 19.64% of non-fraud
[-] 0.67% Missing

Now when I compare those values, they don't match.
(A) 625/775 is 80.645%, not 79.69%
(B) 150/775 is 19.355%, not 19.64%
(C) 0 cases of NULL (missing value) should imply 0% of missing, not 0.67% of missing

Furthermore in one node (with the split on duration_is_zero), there are 541 cases of fraud and 0 cases of non-fraud. This implies the node is leaf-node. However, Mining Legend shows

514 cases of fraud, 99.35%

0 cases of non-fraud, 0.33%

[F] 0 cases of missing, 0.33%

My questions
(1) Why the values don't match like in cases A through C ?
(2) Why the values don't match even in cases D through F when we have no subtree at all ?

I've searched explanation by reading the mathematical reasoning, entropy, Gini index; but it does not answer the discrepancies of those values and percentages in the Mining Legend.

Regards,

BernaridhoOur DT algorithm uses a baysian prior in all calculations. This means that it assumes that all possible states have equal probability at the beginning. This prior gets distributed throughout the tree. Therefore, what you are seeing are the prior-adjusted probabilities and the actual support.|||

Hi..

I need this information more details for doing my thesis which also use DT.

Would you like to give examples how to calculate this percentage ? I have tried to calculate using baysian prior, but the result isn't match which the percentage.

Thank you for your answer..

Interpreting the percentage in decision-tree model

Hi,

I used a decision-tree mining-model to describe and predict fraud. The table contains 1039 records with 775 distinct value of A-number (the calling party). I used 9 columns in the model. SQL Server reports that only 3 columns are significant in predicting the fraud

- BPN_is_too_short (called party-number is too short)
- Duration_is_zero
- Invalid_area_code

The key-column in A-number, and the predicted column is Is_Fraud with the range of values are only 0 and 1. There's no record with NULL (missing-value) in the column Is_Fraud.

Mining Legend shows in the first split
[-] 625 cases of fraud
[-] 150 cases of non-fraud
[-] 0 cases of missing

In addition to that, Mining Legend shows
[-] 79.69% of fraud
[-] 19.64% of non-fraud
[-] 0.67% Missing

Now when I compare those values, they don't match.
(A) 625/775 is 80.645%, not 79.69%
(B) 150/775 is 19.355%, not 19.64%
(C) 0 cases of NULL (missing value) should imply 0% of missing, not 0.67% of missing

Furthermore in one node (with the split on duration_is_zero), there are 541 cases of fraud and 0 cases of non-fraud. This implies the node is leaf-node. However, Mining Legend shows

514 cases of fraud, 99.35%
0 cases of non-fraud, 0.33%
[F] 0 cases of missing, 0.33%
My questions
(1) Why the values don't match like in cases A through C ?
(2) Why the values don't match even in cases D through F when we have no subtree at all ?

I've searched explanation by reading the mathematical reasoning, entropy, Gini index; but it does not answer the discrepancies of those values and percentages in the Mining Legend.

Regards,

Bernaridho
Our DT algorithm uses a baysian prior in all calculations. This means that it assumes that all possible states have equal probability at the beginning. This prior gets distributed throughout the tree. Therefore, what you are seeing are the prior-adjusted probabilities and the actual support.|||

Hi..

I need this information more details for doing my thesis which also use DT.

Would you like to give examples how to calculate this percentage ? I have tried to calculate using baysian prior, but the result isn't match which the percentage.

Thank you for your answer..

Interpreting the percentage in decision-tree model

Hi,

I used a decision-tree mining-model to describe and predict fraud. The table contains 1039 records with 775 distinct value of A-number (the calling party). I used 9 columns in the model. SQL Server reports that only 3 columns are significant in predicting the fraud

- BPN_is_too_short (called party-number is too short)
- Duration_is_zero
- Invalid_area_code

The key-column in A-number, and the predicted column is Is_Fraud with the range of values are only 0 and 1. There's no record with NULL (missing-value) in the column Is_Fraud.

Mining Legend shows in the first split
[-] 625 cases of fraud
[-] 150 cases of non-fraud
[-] 0 cases of missing

In addition to that, Mining Legend shows
[-] 79.69% of fraud
[-] 19.64% of non-fraud
[-] 0.67% Missing

Now when I compare those values, they don't match.
(A) 625/775 is 80.645%, not 79.69%
(B) 150/775 is 19.355%, not 19.64%
(C) 0 cases of NULL (missing value) should imply 0% of missing, not 0.67% of missing

Furthermore in one node (with the split on duration_is_zero), there are 541 cases of fraud and 0 cases of non-fraud. This implies the node is leaf-node. However, Mining Legend shows

514 cases of fraud, 99.35%
0 cases of non-fraud, 0.33%
[F] 0 cases of missing, 0.33%
My questions
(1) Why the values don't match like in cases A through C ?
(2) Why the values don't match even in cases D through F when we have no subtree at all ?

I've searched explanation by reading the mathematical reasoning, entropy, Gini index; but it does not answer the discrepancies of those values and percentages in the Mining Legend.

Regards,

Bernaridho
Our DT algorithm uses a baysian prior in all calculations. This means that it assumes that all possible states have equal probability at the beginning. This prior gets distributed throughout the tree. Therefore, what you are seeing are the prior-adjusted probabilities and the actual support.|||

Hi..

I need this information more details for doing my thesis which also use DT.

Would you like to give examples how to calculate this percentage ? I have tried to calculate using baysian prior, but the result isn't match which the percentage.

Thank you for your answer..