Any workaround for row size > 8060?

Hi All,
We're converting Oracle databases to SQL Server. In Oracle there's no such limit for row size and SQL has.
We've Centura as front-end (Gupta SQL formerly).
While testing is going on......we are getting row size exceeds max limit of 8060 bytes...and we don't know what exactly we need to do.
In the problem tables, I have one TEXT column and various varchar 4000 and varchar 2000 columns.
The application will insert more than 8060 characters for sure..
Can anybody suggest any workaround for this? or Any pointers?
The table structure is something like this:
CREATE TABLE [Table1] (
[Table_ID] [varchar] (10) NOT NULL ,
[T_NOTES1] [TEXT] NULL ,
[T_NOTES2] [varchar] (4000) NULL ,
[C_NOTES] [varchar] (4000) NULL ,
[P_NOTES] [varchar] (4000) NULL ,
[G_NOTES] [varchar] (256) NULL ,
[F_NOTES] [varchar] (256) NULL ,
[L_NOTES] [varchar] (256) NULL ,
[P_NEEDS] [varchar] (256) NULL ,
[F_NEEDS] [varchar] (256) NULL ,
[O_NOTE] [varchar] (250) NULL ,
[V_SOURCES] [varchar] (2000) NULL ,
[T_PROOFS] [varchar] (250) NULL ,
[V_SKILLS] [varchar] (2000) NULL ,
[R_CREDS] [varchar] (2000) NULL ,
[ROWID] [uniqueidentifier] NOT NULL CONSTRAINT
CONSTRAINT [FK_RO_TES_PROID] FOREIGN KEY
(
[PRO_ID]
) REFERENCES [Table_INFO1] (
[PRO_ID]
) ON DELETE CASCADE
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
If we split the table, will it help?
Can we have more than one column as TEXT data type?
-----
One more point: I appreciate if somebody can clarify this:
TEXT data type can store lot of data...right?
Then if we're inserting > 8060 (row size limit) into the TEXT column why would it error?
If this is so, what is the point in using TEXT column?
I'm little bit confused........I appreciate if somebody can clarify this..
Thanks much,
John..
John,
You can split the table vertically (and of course carry the PK to each table).
As for TEXT (and NTEXT and IMAGE), yes, in these you can have up to 2GB per row. They are, however, more
involved to program against. At the TSQL level, you use stuff like READTEXT, UPDATETEXT, TEXTPRT etc (think of
reading and writing in a loop, using a pointer). The API/dev tool you are using might abstract this from you,
though.
Yes, you can have several columns of the "BLOB" datatypes.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
"John" <johnfull_2000@.yahoo.com> wrote in message news:C8579948-714C-4CA5-9244-9AC486144518@.microsoft.com...
> Hi All,
> We're converting Oracle databases to SQL Server. In Oracle there's no such limit for row size and SQL has.
> We've Centura as front-end (Gupta SQL formerly).
> While testing is going on......we are getting row size exceeds max limit of 8060 bytes...and we don't know
what exactly we need to do.
> In the problem tables, I have one TEXT column and various varchar 4000 and varchar 2000 columns.
> The application will insert more than 8060 characters for sure..
> Can anybody suggest any workaround for this? or Any pointers?
> The table structure is something like this:
> CREATE TABLE [Table1] (
> [Table_ID] [varchar] (10) NOT NULL ,
> [T_NOTES1] [TEXT] NULL ,
> [T_NOTES2] [varchar] (4000) NULL ,
> [C_NOTES] [varchar] (4000) NULL ,
> [P_NOTES] [varchar] (4000) NULL ,
> [G_NOTES] [varchar] (256) NULL ,
> [F_NOTES] [varchar] (256) NULL ,
> [L_NOTES] [varchar] (256) NULL ,
> [P_NEEDS] [varchar] (256) NULL ,
> [F_NEEDS] [varchar] (256) NULL ,
> [O_NOTE] [varchar] (250) NULL ,
> [V_SOURCES] [varchar] (2000) NULL ,
> [T_PROOFS] [varchar] (250) NULL ,
> [V_SKILLS] [varchar] (2000) NULL ,
> [R_CREDS] [varchar] (2000) NULL ,
> [ROWID] [uniqueidentifier] NOT NULL CONSTRAINT
> CONSTRAINT [FK_RO_TES_PROID] FOREIGN KEY
> (
> [PRO_ID]
> ) REFERENCES [Table_INFO1] (
> [PRO_ID]
> ) ON DELETE CASCADE
> ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
> GO
> If we split the table, will it help?
> Can we have more than one column as TEXT data type?
> -----
> One more point: I appreciate if somebody can clarify this:
> TEXT data type can store lot of data...right?
> Then if we're inserting > 8060 (row size limit) into the TEXT column why would it error?
> If this is so, what is the point in using TEXT column?
> I'm little bit confused........I appreciate if somebody can clarify this..
> Thanks much,
> John..
|||John,
Does Centura support SQL Server? Perhaps an engineer from Centura would be
required to do this migration work. The problem you are having is that you
will need to use the text data type if you need to store more than 8060
bytes per row. Application changes may need to be made to accomodate this
data type, either at the stored procedure layer or the data access layer in
dynamic SQL in the application itself.
There are many gotchas with the text datatype and they cannot be treated in
the same way as a varchar due to the nature in the way they are stored. i.e.
if the text cannot be stored in row, as in its greater than 8K, then a
pointer is stored in the row to the data pages where the text data can be
found. There is a lot of information on this data type in Books Online, I
recommend you read it.
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
"John" <johnfull_2000@.yahoo.com> wrote in message
news:C8579948-714C-4CA5-9244-9AC486144518@.microsoft.com...
> Hi All,
> We're converting Oracle databases to SQL Server. In Oracle there's no such
limit for row size and SQL has.
> We've Centura as front-end (Gupta SQL formerly).
> While testing is going on......we are getting row size exceeds max limit
of 8060 bytes...and we don't know what exactly we need to do.
> In the problem tables, I have one TEXT column and various varchar 4000 and
varchar 2000 columns.
> The application will insert more than 8060 characters for sure..
> Can anybody suggest any workaround for this? or Any pointers?
> The table structure is something like this:
> CREATE TABLE [Table1] (
> [Table_ID] [varchar] (10) NOT NULL ,
> [T_NOTES1] [TEXT] NULL ,
> [T_NOTES2] [varchar] (4000) NULL ,
> [C_NOTES] [varchar] (4000) NULL ,
> [P_NOTES] [varchar] (4000) NULL ,
> [G_NOTES] [varchar] (256) NULL ,
> [F_NOTES] [varchar] (256) NULL ,
> [L_NOTES] [varchar] (256) NULL ,
> [P_NEEDS] [varchar] (256) NULL ,
> [F_NEEDS] [varchar] (256) NULL ,
> [O_NOTE] [varchar] (250) NULL ,
> [V_SOURCES] [varchar] (2000) NULL ,
> [T_PROOFS] [varchar] (250) NULL ,
> [V_SKILLS] [varchar] (2000) NULL ,
> [R_CREDS] [varchar] (2000) NULL ,
> [ROWID] [uniqueidentifier] NOT NULL CONSTRAINT
> CONSTRAINT [FK_RO_TES_PROID] FOREIGN KEY
> (
> [PRO_ID]
> ) REFERENCES [Table_INFO1] (
> [PRO_ID]
> ) ON DELETE CASCADE
> ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
> GO
> If we split the table, will it help?
> Can we have more than one column as TEXT data type?
> ----
--
> One more point: I appreciate if somebody can clarify this:
> TEXT data type can store lot of data...right?
> Then if we're inserting > 8060 (row size limit) into the TEXT column why
would it error?
> If this is so, what is the point in using TEXT column?
> I'm little bit confused........I appreciate if somebody can clarify
this..
> Thanks much,
> John..
|||For your question, Mark:
Yes. Centura supports SQL Server.
I want to get clear about one more thing:
You mean to say, If I have a TEXT column, it can accommodate more than 8060 bytes..right?
That means excluding the TEXT column, the total row size shouldn't be greater than 8060....
That means, the error is coming bcoz of the total exceeds 8060....
Please clarify..
For you, Tibor:
I changed couple of columns (3 to 4 columns) to TEXT and I started receiving STACK DUMP and EXCEPTION HANDLING ERRORS etc....
and nobody could connect to the server.
Then I had to stop the server and restart the server.
Then I had to change the columns back to varchar(4000)......but the error 'row size cannot be greater than > 8060 is coming'..
Any inputs, please let me know.
Thanks,
John..
|||> You mean to say, If I have a TEXT column, it can accommodate more than 8060 bytes..right?
Correct.

> That means excluding the TEXT column, the total row size shouldn't be greater than 8060....
Correct.

> That means, the error is coming bcoz of the total exceeds 8060....
Correct.

> I changed couple of columns (3 to 4 columns) to TEXT and I started receiving STACK DUMP and EXCEPTION
HANDLING ERRORS etc....
> and nobody could connect to the server.
Then you had the unfortunate to (most likely) hit some bug in SQL Server. You can make sure you are updated on
service packs, search KB for resolution or possibly open a case with MS on this.
Important to understand it that it takes special programming to work with these datatypes. It is not only a
matter of changing the table structure. As I mentioned in my earlier post, the dev tool/API might abstract you
from that, but that is up to each dev tool/API vendor.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
"John" <johnfull_2000@.yahoo.com> wrote in message news:A06EB4C0-2FA1-48F8-B0E3-692EA32246B3@.microsoft.com...
> For your question, Mark:
> Yes. Centura supports SQL Server.
> I want to get clear about one more thing:
> You mean to say, If I have a TEXT column, it can accommodate more than 8060 bytes..right?
> That means excluding the TEXT column, the total row size shouldn't be greater than 8060....
> That means, the error is coming bcoz of the total exceeds 8060....
> Please clarify..
> For you, Tibor:
> I changed couple of columns (3 to 4 columns) to TEXT and I started receiving STACK DUMP and EXCEPTION
HANDLING ERRORS etc....
> and nobody could connect to the server.
> Then I had to stop the server and restart the server.
> Then I had to change the columns back to varchar(4000)......but the error 'row size cannot be greater than
> 8060 is coming'..
> Any inputs, please let me know.
> Thanks,
> John..
>
|||THANK YOU.....for the info
John.
sql

Any workaround for row size > 8060?

Hi All,
We're converting Oracle databases to SQL Server. In Oracle there's no such l
imit for row size and SQL has.
We've Centura as front-end (Gupta SQL formerly).
While testing is going on......we are getting row size exceeds max limit o
f 8060 bytes...and we don't know what exactly we need to do.
In the problem tables, I have one TEXT column and various varchar 4000 and v
archar 2000 columns.
The application will insert more than 8060 characters for sure..
Can anybody suggest any workaround for this? or Any pointers?
The table structure is something like this:
---
CREATE TABLE [Table1] (
[Table_ID] [varchar] (10) NOT NULL ,
[T_NOTES1] [TEXT] NULL ,
[T_NOTES2] [varchar] (4000) NULL ,
[C_NOTES] [varchar] (4000) NULL ,
[P_NOTES] [varchar] (4000) NULL ,
[G_NOTES] [varchar] (256) NULL ,
[F_NOTES] [varchar] (256) NULL ,
[L_NOTES] [varchar] (256) NULL ,
[P_NEEDS] [varchar] (256) NULL ,
[F_NEEDS] [varchar] (256) NULL ,
[O_NOTE] [varchar] (250) NULL ,
[V_SOURCES] [varchar] (2000) NULL ,
[T_PROOFS] [varchar] (250) NULL ,
[V_SKILLS] [varchar] (2000) NULL ,
[R_CREDS] [varchar] (2000) NULL ,
[ROWID] [uniqueidentifier] NOT NULL CONSTRAINT
CONSTRAINT [FK_RO_TES_PROID] FOREIGN KEY
(
[PRO_ID]
) REFERENCES [Table_INFO1] (
[PRO_ID]
) ON DELETE CASCADE
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
If we split the table, will it help?
Can we have more than one column as TEXT data type?
----
--
One more point: I appreciate if somebody can clarify this:
TEXT data type can store lot of data...right?
Then if we're inserting > 8060 (row size limit) into the TEXT column why wou
ld it error?
If this is so, what is the point in using TEXT column?
I'm little bit confused........I appreciate if somebody can clarify this..
Thanks much,
John..John,
You can split the table vertically (and of course carry the PK to each table
).
As for TEXT (and NTEXT and IMAGE), yes, in these you can have up to 2GB per
row. They are, however, more
involved to program against. At the TSQL level, you use stuff like READTEXT,
UPDATETEXT, TEXTPRT etc (think of
reading and writing in a loop, using a pointer). The API/dev tool you are us
ing might abstract this from you,
though.
Yes, you can have several columns of the "BLOB" datatypes.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
"John" <johnfull_2000@.yahoo.com> wrote in message news:C8579948-714C-4CA5-9244-9AC486144518@.
microsoft.com...
> Hi All,
> We're converting Oracle databases to SQL Server. In Oracle there's no such
limit for row size and SQL has.
> We've Centura as front-end (Gupta SQL formerly).
> While testing is going on......we are getting row size exceeds max limit of 8060
bytes...and we don't know
what exactly we need to do.
> In the problem tables, I have one TEXT column and various varchar 4000 and
varchar 2000 columns.
> The application will insert more than 8060 characters for sure..
> Can anybody suggest any workaround for this? or Any pointers?
> The table structure is something like this:
> ---
> CREATE TABLE [Table1] (
> [Table_ID] [varchar] (10) NOT NULL ,
> [T_NOTES1] [TEXT] NULL ,
> [T_NOTES2] [varchar] (4000) NULL ,
> [C_NOTES] [varchar] (4000) NULL ,
> [P_NOTES] [varchar] (4000) NULL ,
> [G_NOTES] [varchar] (256) NULL ,
> [F_NOTES] [varchar] (256) NULL ,
> [L_NOTES] [varchar] (256) NULL ,
> [P_NEEDS] [varchar] (256) NULL ,
> [F_NEEDS] [varchar] (256) NULL ,
> [O_NOTE] [varchar] (250) NULL ,
> [V_SOURCES] [varchar] (2000) NULL ,
> [T_PROOFS] [varchar] (250) NULL ,
> [V_SKILLS] [varchar] (2000) NULL ,
> [R_CREDS] [varchar] (2000) NULL ,
> [ROWID] [uniqueidentifier] NOT NULL CONSTRAINT
> CONSTRAINT [FK_RO_TES_PROID] FOREIGN KEY
> (
> [PRO_ID]
> ) REFERENCES [Table_INFO1] (
> [PRO_ID]
> ) ON DELETE CASCADE
> ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
> GO
> If we split the table, will it help?
> Can we have more than one column as TEXT data type?
> ----
--
> One more point: I appreciate if somebody can clarify this:
> TEXT data type can store lot of data...right?
> Then if we're inserting > 8060 (row size limit) into the TEXT column why w
ould it error?
> If this is so, what is the point in using TEXT column?
> I'm little bit confused........I appreciate if somebody can clarify this
.
> Thanks much,
> John..|||John,
Does Centura support SQL Server? Perhaps an engineer from Centura would be
required to do this migration work. The problem you are having is that you
will need to use the text data type if you need to store more than 8060
bytes per row. Application changes may need to be made to accomodate this
data type, either at the stored procedure layer or the data access layer in
dynamic SQL in the application itself.
There are many gotchas with the text datatype and they cannot be treated in
the same way as a varchar due to the nature in the way they are stored. i.e.
if the text cannot be stored in row, as in its greater than 8K, then a
pointer is stored in the row to the data pages where the text data can be
found. There is a lot of information on this data type in Books Online, I
recommend you read it.
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
"John" <johnfull_2000@.yahoo.com> wrote in message
news:C8579948-714C-4CA5-9244-9AC486144518@.microsoft.com...
> Hi All,
> We're converting Oracle databases to SQL Server. In Oracle there's no such
limit for row size and SQL has.
> We've Centura as front-end (Gupta SQL formerly).
> While testing is going on......we are getting row size exceeds max limit
of 8060 bytes...and we don't know what exactly we need to do.
> In the problem tables, I have one TEXT column and various varchar 4000 and
varchar 2000 columns.
> The application will insert more than 8060 characters for sure..
> Can anybody suggest any workaround for this? or Any pointers?
> The table structure is something like this:
> ---
> CREATE TABLE [Table1] (
> [Table_ID] [varchar] (10) NOT NULL ,
> [T_NOTES1] [TEXT] NULL ,
> [T_NOTES2] [varchar] (4000) NULL ,
> [C_NOTES] [varchar] (4000) NULL ,
> [P_NOTES] [varchar] (4000) NULL ,
> [G_NOTES] [varchar] (256) NULL ,
> [F_NOTES] [varchar] (256) NULL ,
> [L_NOTES] [varchar] (256) NULL ,
> [P_NEEDS] [varchar] (256) NULL ,
> [F_NEEDS] [varchar] (256) NULL ,
> [O_NOTE] [varchar] (250) NULL ,
> [V_SOURCES] [varchar] (2000) NULL ,
> [T_PROOFS] [varchar] (250) NULL ,
> [V_SKILLS] [varchar] (2000) NULL ,
> [R_CREDS] [varchar] (2000) NULL ,
> [ROWID] [uniqueidentifier] NOT NULL CONSTRAINT
> CONSTRAINT [FK_RO_TES_PROID] FOREIGN KEY
> (
> [PRO_ID]
> ) REFERENCES [Table_INFO1] (
> [PRO_ID]
> ) ON DELETE CASCADE
> ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
> GO
> If we split the table, will it help?
> Can we have more than one column as TEXT data type?
> ----
--
> One more point: I appreciate if somebody can clarify this:
> TEXT data type can store lot of data...right?
> Then if we're inserting > 8060 (row size limit) into the TEXT column why
would it error?
> If this is so, what is the point in using TEXT column?
> I'm little bit confused........I appreciate if somebody can clarify
this..
> Thanks much,
> John..|||For your question, Mark:
Yes. Centura supports SQL Server.
I want to get clear about one more thing:
You mean to say, If I have a TEXT column, it can accommodate more than 8060
bytes..right?
That means excluding the TEXT column, the total row size shouldn't be greate
r than 8060....
That means, the error is coming bcoz of the total exceeds 8060....
Please clarify..
For you, Tibor:
I changed couple of columns (3 to 4 columns) to TEXT and I started receiving
STACK DUMP and EXCEPTION HANDLING ERRORS etc....
and nobody could connect to the server.
Then I had to stop the server and restart the server.
Then I had to change the columns back to varchar(4000)......but the error '
row size cannot be greater than > 8060 is coming'..
Any inputs, please let me know.
Thanks,
John..|||> You mean to say, If I have a TEXT column, it can accommodate more than 806
0 bytes..right?
Correct.

> That means excluding the TEXT column, the total row size shouldn't be greater than
8060....
Correct.

> That means, the error is coming bcoz of the total exceeds 8060....
Correct.

> I changed couple of columns (3 to 4 columns) to TEXT and I started receiving STACK
DUMP and EXCEPTION
HANDLING ERRORS etc....
> and nobody could connect to the server.
Then you had the unfortunate to (most likely) hit some bug in SQL Server. Yo
u can make sure you are updated on
service packs, search KB for resolution or possibly open a case with MS on t
his.
Important to understand it that it takes special programming to work with th
ese datatypes. It is not only a
matter of changing the table structure. As I mentioned in my earlier post, t
he dev tool/API might abstract you
from that, but that is up to each dev tool/API vendor.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
"John" <johnfull_2000@.yahoo.com> wrote in message news:A06EB4C0-2FA1-48F8-B0E3-692EA32246B3@.
microsoft.com...
> For your question, Mark:
> Yes. Centura supports SQL Server.
> I want to get clear about one more thing:
> You mean to say, If I have a TEXT column, it can accommodate more than 806
0 bytes..right?
> That means excluding the TEXT column, the total row size shouldn't be grea
ter than 8060....
> That means, the error is coming bcoz of the total exceeds 8060....
> Please clarify..
> For you, Tibor:
> I changed couple of columns (3 to 4 columns) to TEXT and I started receiving STACK
DUMP and EXCEPTION
HANDLING ERRORS etc....
> and nobody could connect to the server.
> Then I had to stop the server and restart the server.
> Then I had to change the columns back to varchar(4000)......but the error
'row size cannot be greater than
> 8060 is coming'..
> Any inputs, please let me know.
> Thanks,
> John..
>|||THANK YOU.....for the info
John.

Any workaround for row size > 8060?

Hi All
We're converting Oracle databases to SQL Server. In Oracle there's no such limit for row size and SQL has
We've Centura as front-end (Gupta SQL formerly).
While testing is going on......we are getting row size exceeds max limit of 8060 bytes...and we don't know what exactly we need to do
In the problem tables, I have one TEXT column and various varchar 4000 and varchar 2000 columns
The application will insert more than 8060 characters for sure.
Can anybody suggest any workaround for this? or Any pointers
The table structure is something like this
----
CREATE TABLE [Table1]
[Table_ID] [varchar] (10) NOT NULL
[T_NOTES1] [TEXT] NULL
[T_NOTES2] [varchar] (4000) NULL
[C_NOTES] [varchar] (4000) NULL
[P_NOTES] [varchar] (4000) NULL
[G_NOTES] [varchar] (256) NULL
[F_NOTES] [varchar] (256) NULL
[L_NOTES] [varchar] (256) NULL
[P_NEEDS] [varchar] (256) NULL
[F_NEEDS] [varchar] (256) NULL
[O_NOTE] [varchar] (250) NULL
[V_SOURCES] [varchar] (2000) NULL
[T_PROOFS] [varchar] (250) NULL
[V_SKILLS] [varchar] (2000) NULL
[R_CREDS] [varchar] (2000) NULL
[ROWID] [uniqueidentifier] NOT NULL CONSTRAINT
CONSTRAINT [FK_RO_TES_PROID] FOREIGN KEY
[PRO_ID
) REFERENCES [Table_INFO1]
[PRO_ID
) ON DELETE CASCADE
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY
G
If we split the table, will it help
Can we have more than one column as TEXT data type
----
One more point: I appreciate if somebody can clarify this
TEXT data type can store lot of data...right
Then if we're inserting > 8060 (row size limit) into the TEXT column why would it error
If this is so, what is the point in using TEXT column
I'm little bit confused........I appreciate if somebody can clarify this.
Thanks much
John..John,
You can split the table vertically (and of course carry the PK to each table).
As for TEXT (and NTEXT and IMAGE), yes, in these you can have up to 2GB per row. They are, however, more
involved to program against. At the TSQL level, you use stuff like READTEXT, UPDATETEXT, TEXTPRT etc (think of
reading and writing in a loop, using a pointer). The API/dev tool you are using might abstract this from you,
though.
Yes, you can have several columns of the "BLOB" datatypes.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
"John" <johnfull_2000@.yahoo.com> wrote in message news:C8579948-714C-4CA5-9244-9AC486144518@.microsoft.com...
> Hi All,
> We're converting Oracle databases to SQL Server. In Oracle there's no such limit for row size and SQL has.
> We've Centura as front-end (Gupta SQL formerly).
> While testing is going on......we are getting row size exceeds max limit of 8060 bytes...and we don't know
what exactly we need to do.
> In the problem tables, I have one TEXT column and various varchar 4000 and varchar 2000 columns.
> The application will insert more than 8060 characters for sure..
> Can anybody suggest any workaround for this? or Any pointers?
> The table structure is something like this:
> ---
> CREATE TABLE [Table1] (
> [Table_ID] [varchar] (10) NOT NULL ,
> [T_NOTES1] [TEXT] NULL ,
> [T_NOTES2] [varchar] (4000) NULL ,
> [C_NOTES] [varchar] (4000) NULL ,
> [P_NOTES] [varchar] (4000) NULL ,
> [G_NOTES] [varchar] (256) NULL ,
> [F_NOTES] [varchar] (256) NULL ,
> [L_NOTES] [varchar] (256) NULL ,
> [P_NEEDS] [varchar] (256) NULL ,
> [F_NEEDS] [varchar] (256) NULL ,
> [O_NOTE] [varchar] (250) NULL ,
> [V_SOURCES] [varchar] (2000) NULL ,
> [T_PROOFS] [varchar] (250) NULL ,
> [V_SKILLS] [varchar] (2000) NULL ,
> [R_CREDS] [varchar] (2000) NULL ,
> [ROWID] [uniqueidentifier] NOT NULL CONSTRAINT
> CONSTRAINT [FK_RO_TES_PROID] FOREIGN KEY
> (
> [PRO_ID]
> ) REFERENCES [Table_INFO1] (
> [PRO_ID]
> ) ON DELETE CASCADE
> ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
> GO
> If we split the table, will it help?
> Can we have more than one column as TEXT data type?
> -----
> One more point: I appreciate if somebody can clarify this:
> TEXT data type can store lot of data...right?
> Then if we're inserting > 8060 (row size limit) into the TEXT column why would it error?
> If this is so, what is the point in using TEXT column?
> I'm little bit confused........I appreciate if somebody can clarify this..
> Thanks much,
> John..|||John,
Does Centura support SQL Server? Perhaps an engineer from Centura would be
required to do this migration work. The problem you are having is that you
will need to use the text data type if you need to store more than 8060
bytes per row. Application changes may need to be made to accomodate this
data type, either at the stored procedure layer or the data access layer in
dynamic SQL in the application itself.
There are many gotchas with the text datatype and they cannot be treated in
the same way as a varchar due to the nature in the way they are stored. i.e.
if the text cannot be stored in row, as in its greater than 8K, then a
pointer is stored in the row to the data pages where the text data can be
found. There is a lot of information on this data type in Books Online, I
recommend you read it.
--
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
"John" <johnfull_2000@.yahoo.com> wrote in message
news:C8579948-714C-4CA5-9244-9AC486144518@.microsoft.com...
> Hi All,
> We're converting Oracle databases to SQL Server. In Oracle there's no such
limit for row size and SQL has.
> We've Centura as front-end (Gupta SQL formerly).
> While testing is going on......we are getting row size exceeds max limit
of 8060 bytes...and we don't know what exactly we need to do.
> In the problem tables, I have one TEXT column and various varchar 4000 and
varchar 2000 columns.
> The application will insert more than 8060 characters for sure..
> Can anybody suggest any workaround for this? or Any pointers?
> The table structure is something like this:
> ---
> CREATE TABLE [Table1] (
> [Table_ID] [varchar] (10) NOT NULL ,
> [T_NOTES1] [TEXT] NULL ,
> [T_NOTES2] [varchar] (4000) NULL ,
> [C_NOTES] [varchar] (4000) NULL ,
> [P_NOTES] [varchar] (4000) NULL ,
> [G_NOTES] [varchar] (256) NULL ,
> [F_NOTES] [varchar] (256) NULL ,
> [L_NOTES] [varchar] (256) NULL ,
> [P_NEEDS] [varchar] (256) NULL ,
> [F_NEEDS] [varchar] (256) NULL ,
> [O_NOTE] [varchar] (250) NULL ,
> [V_SOURCES] [varchar] (2000) NULL ,
> [T_PROOFS] [varchar] (250) NULL ,
> [V_SKILLS] [varchar] (2000) NULL ,
> [R_CREDS] [varchar] (2000) NULL ,
> [ROWID] [uniqueidentifier] NOT NULL CONSTRAINT
> CONSTRAINT [FK_RO_TES_PROID] FOREIGN KEY
> (
> [PRO_ID]
> ) REFERENCES [Table_INFO1] (
> [PRO_ID]
> ) ON DELETE CASCADE
> ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
> GO
> If we split the table, will it help?
> Can we have more than one column as TEXT data type?
> ----
--
> One more point: I appreciate if somebody can clarify this:
> TEXT data type can store lot of data...right?
> Then if we're inserting > 8060 (row size limit) into the TEXT column why
would it error?
> If this is so, what is the point in using TEXT column?
> I'm little bit confused........I appreciate if somebody can clarify
this..
> Thanks much,
> John..|||For your question, Mark
Yes. Centura supports SQL Server
I want to get clear about one more thing
You mean to say, If I have a TEXT column, it can accommodate more than 8060 bytes..right
That means excluding the TEXT column, the total row size shouldn't be greater than 8060...
That means, the error is coming bcoz of the total exceeds 8060...
Please clarify.
For you, Tibor
I changed couple of columns (3 to 4 columns) to TEXT and I started receiving STACK DUMP and EXCEPTION HANDLING ERRORS etc...
and nobody could connect to the server
Then I had to stop the server and restart the server
Then I had to change the columns back to varchar(4000)......but the error 'row size cannot be greater than > 8060 is coming'.
Any inputs, please let me know
Thanks
John.|||> You mean to say, If I have a TEXT column, it can accommodate more than 8060 bytes..right?
Correct.
> That means excluding the TEXT column, the total row size shouldn't be greater than 8060....
Correct.
> That means, the error is coming bcoz of the total exceeds 8060....
Correct.
> I changed couple of columns (3 to 4 columns) to TEXT and I started receiving STACK DUMP and EXCEPTION
HANDLING ERRORS etc....
> and nobody could connect to the server.
Then you had the unfortunate to (most likely) hit some bug in SQL Server. You can make sure you are updated on
service packs, search KB for resolution or possibly open a case with MS on this.
Important to understand it that it takes special programming to work with these datatypes. It is not only a
matter of changing the table structure. As I mentioned in my earlier post, the dev tool/API might abstract you
from that, but that is up to each dev tool/API vendor.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
"John" <johnfull_2000@.yahoo.com> wrote in message news:A06EB4C0-2FA1-48F8-B0E3-692EA32246B3@.microsoft.com...
> For your question, Mark:
> Yes. Centura supports SQL Server.
> I want to get clear about one more thing:
> You mean to say, If I have a TEXT column, it can accommodate more than 8060 bytes..right?
> That means excluding the TEXT column, the total row size shouldn't be greater than 8060....
> That means, the error is coming bcoz of the total exceeds 8060....
> Please clarify..
> For you, Tibor:
> I changed couple of columns (3 to 4 columns) to TEXT and I started receiving STACK DUMP and EXCEPTION
HANDLING ERRORS etc....
> and nobody could connect to the server.
> Then I had to stop the server and restart the server.
> Then I had to change the columns back to varchar(4000)......but the error 'row size cannot be greater than
> 8060 is coming'..
> Any inputs, please let me know.
> Thanks,
> John..
>|||THANK YOU.....for the inf
John.

Any way to recover schema (thats it) from a mdf file

Ok, with sqlserver 2005, I've noticed my databases being marked as
suspect far more often than sql 2000. Anyway... after futzing around
with it for quite some time... I'd made no progress.
Now I am at the point where the database wasnt cleanly shutdown, and I
dont have a log file.
I am curious if theres anyway to grab schema from the mdf. I am
relatively sure that the mdf file is in fact in tact and not corrupt,
but I dont know where to go from here. sp_attach_single_file_db looks
like it'd work if it was cleanly shutdown.
Thanks in advance
WestonBefore I say anything else: this is worth a call to PSS, especially if this
is critical data. That being said...
You can start the database in EMERGENCY mode, which will start it without
attempting recovery. Given that all of the original data files are in place,
you can expert data out (although you aren't guaranteed as to transactional
consistency).
I would do that and then try the the sp_attach_single_file_db - at least you
can pump out info from the database first.
"Weston Weems" <wweemsNO_SPAM.PLEASE@.gmail.com> wrote in message
news:u2i2ZGdoGHA.4776@.TK2MSFTNGP03.phx.gbl...
> Ok, with sqlserver 2005, I've noticed my databases being marked as suspect
> far more often than sql 2000. Anyway... after futzing around with it for
> quite some time... I'd made no progress.
> Now I am at the point where the database wasnt cleanly shutdown, and I
> dont have a log file.
> I am curious if theres anyway to grab schema from the mdf. I am relatively
> sure that the mdf file is in fact in tact and not corrupt, but I dont know
> where to go from here. sp_attach_single_file_db looks like it'd work if it
> was cleanly shutdown.
>
> Thanks in advance
> Weston|||If you've got the MDF , do a reattach . Are you saying your log file has
disappeared?
--
Jack Vamvas
___________________________________
Receive free SQL tips - www.ciquery.com/sqlserver.htm
___________________________________
"Weston Weems" <wweemsNO_SPAM.PLEASE@.gmail.com> wrote in message
news:u2i2ZGdoGHA.4776@.TK2MSFTNGP03.phx.gbl...
> Ok, with sqlserver 2005, I've noticed my databases being marked as
> suspect far more often than sql 2000. Anyway... after futzing around
> with it for quite some time... I'd made no progress.
> Now I am at the point where the database wasnt cleanly shutdown, and I
> dont have a log file.
> I am curious if theres anyway to grab schema from the mdf. I am
> relatively sure that the mdf file is in fact in tact and not corrupt,
> but I dont know where to go from here. sp_attach_single_file_db looks
> like it'd work if it was cleanly shutdown.
>
> Thanks in advance
> Weston|||On Fri, 07 Jul 2006 07:28:20 -0700, Weston Weems wrote:

>Ok, with sqlserver 2005, I've noticed my databases being marked as
>suspect far more often than sql 2000.
Hi Weston,
Having databases marked as suspect should not happen on a regular basis,
unless you're running on wacky hardware or you're doing wacky things.

>Now I am at the point where the database wasnt cleanly shutdown, and I
>dont have a log file.
Any idea what caused yoou to lose the log file? The cause of that might
be related to yoour high frequency of suspect databases.
Hugo Kornelis, SQL Server MVP

Any way to recover schema (thats it) from a mdf file

Ok, with sqlserver 2005, I've noticed my databases being marked as
suspect far more often than sql 2000. Anyway... after futzing around
with it for quite some time... I'd made no progress.
Now I am at the point where the database wasnt cleanly shutdown, and I
dont have a log file.
I am curious if theres anyway to grab schema from the mdf. I am
relatively sure that the mdf file is in fact in tact and not corrupt,
but I dont know where to go from here. sp_attach_single_file_db looks
like it'd work if it was cleanly shutdown.
Thanks in advance
WestonBefore I say anything else: this is worth a call to PSS, especially if this
is critical data. That being said...
You can start the database in EMERGENCY mode, which will start it without
attempting recovery. Given that all of the original data files are in place,
you can expert data out (although you aren't guaranteed as to transactional
consistency).
I would do that and then try the the sp_attach_single_file_db - at least you
can pump out info from the database first.
"Weston Weems" <wweemsNO_SPAM.PLEASE@.gmail.com> wrote in message
news:u2i2ZGdoGHA.4776@.TK2MSFTNGP03.phx.gbl...
> Ok, with sqlserver 2005, I've noticed my databases being marked as suspect
> far more often than sql 2000. Anyway... after futzing around with it for
> quite some time... I'd made no progress.
> Now I am at the point where the database wasnt cleanly shutdown, and I
> dont have a log file.
> I am curious if theres anyway to grab schema from the mdf. I am relatively
> sure that the mdf file is in fact in tact and not corrupt, but I dont know
> where to go from here. sp_attach_single_file_db looks like it'd work if it
> was cleanly shutdown.
>
> Thanks in advance
> Weston|||If you've got the MDF , do a reattach . Are you saying your log file has
disappeared?
--
--
Jack Vamvas
___________________________________
Receive free SQL tips - www.ciquery.com/sqlserver.htm
___________________________________
"Weston Weems" <wweemsNO_SPAM.PLEASE@.gmail.com> wrote in message
news:u2i2ZGdoGHA.4776@.TK2MSFTNGP03.phx.gbl...
> Ok, with sqlserver 2005, I've noticed my databases being marked as
> suspect far more often than sql 2000. Anyway... after futzing around
> with it for quite some time... I'd made no progress.
> Now I am at the point where the database wasnt cleanly shutdown, and I
> dont have a log file.
> I am curious if theres anyway to grab schema from the mdf. I am
> relatively sure that the mdf file is in fact in tact and not corrupt,
> but I dont know where to go from here. sp_attach_single_file_db looks
> like it'd work if it was cleanly shutdown.
>
> Thanks in advance
> Weston|||On Fri, 07 Jul 2006 07:28:20 -0700, Weston Weems wrote:
>Ok, with sqlserver 2005, I've noticed my databases being marked as
>suspect far more often than sql 2000.
Hi Weston,
Having databases marked as suspect should not happen on a regular basis,
unless you're running on wacky hardware or you're doing wacky things.
>Now I am at the point where the database wasnt cleanly shutdown, and I
>dont have a log file.
Any idea what caused yoou to lose the log file? The cause of that might
be related to yoour high frequency of suspect databases.
--
Hugo Kornelis, SQL Server MVP

Any way to be notified if database grows?

Our databases are configured for automatic growth by varying increments of MB.
Is there a way (an alert?) to find out when the growth has actualy happened?
Our customers are complaining of "random" poor performance - I would like to
rule out database growth.
TYIA
You can catch it using a Profiler trace. And probably also using an event notification (see CREATE
EVENT NOTIFICATION), if you are on 2005.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"RobinMC" <RobinMC@.discussions.microsoft.com> wrote in message
news:EA3B1E1E-9A31-4C7D-B3F0-89B32CAD431F@.microsoft.com...
> Our databases are configured for automatic growth by varying increments of MB.
> Is there a way (an alert?) to find out when the growth has actualy happened?
> Our customers are complaining of "random" poor performance - I would like to
> rule out database growth.
> TYIA
|||Thank you for your response.
Unfortunately, we are sill on 2000.
Are there any alerts SQL that would capture this?
Any way to write to the Event logs?
I don't know when it will happen and can't have a trace running 24 x 7.
Thank you .
"Tibor Karaszi" wrote:

> You can catch it using a Profiler trace. And probably also using an event notification (see CREATE
> EVENT NOTIFICATION), if you are on 2005.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://sqlblog.com/blogs/tibor_karaszi
>
> "RobinMC" <RobinMC@.discussions.microsoft.com> wrote in message
> news:EA3B1E1E-9A31-4C7D-B3F0-89B32CAD431F@.microsoft.com...
>
|||No and no, I'm afraid. It isn't a big deal to have a *server side* profiler trace running
continually. Of you can poll for the file size as frequently as you need and based on that log when
you notice a change in size.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"RobinMC" <RobinMC@.discussions.microsoft.com> wrote in message
news:3AC5067C-9E8D-4564-BDD9-1F365BB8661D@.microsoft.com...[vbcol=seagreen]
> Thank you for your response.
> Unfortunately, we are sill on 2000.
> Are there any alerts SQL that would capture this?
> Any way to write to the Event logs?
> I don't know when it will happen and can't have a trace running 24 x 7.
> Thank you .
> "Tibor Karaszi" wrote:
|||On Aug 15, 9:45 pm, "Tibor Karaszi"
<tibor_please.no.email_kara...@.hotmail.nomail.com> wrote:
> No and no, I'm afraid. It isn't a big deal to have a *server side* profiler trace running
> continually. Of you can poll for the file size as frequently as you need and based on that log when
> you notice a change in size.
> --
> Tibor Karaszi, SQL Server MVPhttp://www.karaszi.com/sqlserver/default.asphttp://sqlblog.com/blogs/tibor_karaszi
> "RobinMC" <Robi...@.discussions.microsoft.com> wrote in message
> news:3AC5067C-9E8D-4564-BDD9-1F365BB8661D@.microsoft.com...
>
>
>
>
> - Show quoted text -
Instead of a profiler trace, start a perfmon (administrative tools-
>performance) trace on the server, tracing the following counters:
Performance Object: MsSQL:Databases
Counters: Data File Size KB, Log File Size KB
Instances: Select the appropriate database(s) that you'd like to
collect.
A relatively low sampling interval (once per every minute or few)
should be enough to give you the data that you're looking for.
Whenever any of these counters increase, you'll know that a file
growth occured and the amount that the file was expanded.
MS
|||On Wed, 15 Aug 2007 13:10:01 -0700, RobinMC
<RobinMC@.discussions.microsoft.com> wrote:

>Our databases are configured for automatic growth by varying increments of MB.
>Is there a way (an alert?) to find out when the growth has actualy happened?
>Our customers are complaining of "random" poor performance - I would like to
>rule out database growth.
You could write a short app that checks the .mdf filesize every five
seconds or so.
J.

Any way to be notified if database grows?

Our databases are configured for automatic growth by varying increments of MB.
Is there a way (an alert?) to find out when the growth has actualy happened?
Our customers are complaining of "random" poor performance - I would like to
rule out database growth.
TYIAYou can catch it using a Profiler trace. And probably also using an event notification (see CREATE
EVENT NOTIFICATION), if you are on 2005.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"RobinMC" <RobinMC@.discussions.microsoft.com> wrote in message
news:EA3B1E1E-9A31-4C7D-B3F0-89B32CAD431F@.microsoft.com...
> Our databases are configured for automatic growth by varying increments of MB.
> Is there a way (an alert?) to find out when the growth has actualy happened?
> Our customers are complaining of "random" poor performance - I would like to
> rule out database growth.
> TYIA|||Thank you for your response.
Unfortunately, we are sill on 2000.
Are there any alerts SQL that would capture this?
Any way to write to the Event logs?
I don't know when it will happen and can't have a trace running 24 x 7.
Thank you .
"Tibor Karaszi" wrote:
> You can catch it using a Profiler trace. And probably also using an event notification (see CREATE
> EVENT NOTIFICATION), if you are on 2005.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://sqlblog.com/blogs/tibor_karaszi
>
> "RobinMC" <RobinMC@.discussions.microsoft.com> wrote in message
> news:EA3B1E1E-9A31-4C7D-B3F0-89B32CAD431F@.microsoft.com...
> > Our databases are configured for automatic growth by varying increments of MB.
> > Is there a way (an alert?) to find out when the growth has actualy happened?
> > Our customers are complaining of "random" poor performance - I would like to
> > rule out database growth.
> >
> > TYIA
>|||No and no, I'm afraid. It isn't a big deal to have a *server side* profiler trace running
continually. Of you can poll for the file size as frequently as you need and based on that log when
you notice a change in size.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"RobinMC" <RobinMC@.discussions.microsoft.com> wrote in message
news:3AC5067C-9E8D-4564-BDD9-1F365BB8661D@.microsoft.com...
> Thank you for your response.
> Unfortunately, we are sill on 2000.
> Are there any alerts SQL that would capture this?
> Any way to write to the Event logs?
> I don't know when it will happen and can't have a trace running 24 x 7.
> Thank you .
> "Tibor Karaszi" wrote:
>> You can catch it using a Profiler trace. And probably also using an event notification (see
>> CREATE
>> EVENT NOTIFICATION), if you are on 2005.
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://sqlblog.com/blogs/tibor_karaszi
>>
>> "RobinMC" <RobinMC@.discussions.microsoft.com> wrote in message
>> news:EA3B1E1E-9A31-4C7D-B3F0-89B32CAD431F@.microsoft.com...
>> > Our databases are configured for automatic growth by varying increments of MB.
>> > Is there a way (an alert?) to find out when the growth has actualy happened?
>> > Our customers are complaining of "random" poor performance - I would like to
>> > rule out database growth.
>> >
>> > TYIA
>>|||On Aug 15, 9:45 pm, "Tibor Karaszi"
<tibor_please.no.email_kara...@.hotmail.nomail.com> wrote:
> No and no, I'm afraid. It isn't a big deal to have a *server side* profiler trace running
> continually. Of you can poll for the file size as frequently as you need and based on that log when
> you notice a change in size.
> --
> Tibor Karaszi, SQL Server MVPhttp://www.karaszi.com/sqlserver/default.asphttp://sqlblog.com/blogs/tibor_karaszi
> "RobinMC" <Robi...@.discussions.microsoft.com> wrote in message
> news:3AC5067C-9E8D-4564-BDD9-1F365BB8661D@.microsoft.com...
>
> > Thank you for your response.
> > Unfortunately, we are sill on 2000.
> > Are there any alerts SQL that would capture this?
> > Any way to write to the Event logs?
> > I don't know when it will happen and can't have a trace running 24 x 7.
> > Thank you .
> > "Tibor Karaszi" wrote:
> >> You can catch it using a Profiler trace. And probably also using an event notification (see
> >> CREATE
> >> EVENT NOTIFICATION), if you are on 2005.
> >> --
> >> Tibor Karaszi, SQL Server MVP
> >>http://www.karaszi.com/sqlserver/default.asp
> >>http://sqlblog.com/blogs/tibor_karaszi
> >> "RobinMC" <Robi...@.discussions.microsoft.com> wrote in message
> >>news:EA3B1E1E-9A31-4C7D-B3F0-89B32CAD431F@.microsoft.com...
> >> > Our databases are configured for automatic growth by varying increments of MB.
> >> > Is there a way (an alert?) to find out when the growth has actualy happened?
> >> > Our customers are complaining of "random" poor performance - I would like to
> >> > rule out database growth.
> >> > TYIA- Hide quoted text -
> - Show quoted text -
Instead of a profiler trace, start a perfmon (administrative tools-
>performance) trace on the server, tracing the following counters:
Performance Object: MsSQL:Databases
Counters: Data File Size KB, Log File Size KB
Instances: Select the appropriate database(s) that you'd like to
collect.
A relatively low sampling interval (once per every minute or few)
should be enough to give you the data that you're looking for.
Whenever any of these counters increase, you'll know that a file
growth occured and the amount that the file was expanded.
MS|||On Wed, 15 Aug 2007 13:10:01 -0700, RobinMC
<RobinMC@.discussions.microsoft.com> wrote:
>Our databases are configured for automatic growth by varying increments of MB.
>Is there a way (an alert?) to find out when the growth has actualy happened?
>Our customers are complaining of "random" poor performance - I would like to
>rule out database growth.
You could write a short app that checks the .mdf filesize every five
seconds or so.
J.

Any way to be notified if database grows?

Our databases are configured for automatic growth by varying increments of M
B.
Is there a way (an alert?) to find out when the growth has actualy happened?
Our customers are complaining of "random" poor performance - I would like to
rule out database growth.
TYIAYou can catch it using a Profiler trace. And probably also using an event no
tification (see CREATE
EVENT NOTIFICATION), if you are on 2005.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"RobinMC" <RobinMC@.discussions.microsoft.com> wrote in message
news:EA3B1E1E-9A31-4C7D-B3F0-89B32CAD431F@.microsoft.com...
> Our databases are configured for automatic growth by varying increments of
MB.
> Is there a way (an alert?) to find out when the growth has actualy happene
d?
> Our customers are complaining of "random" poor performance - I would like
to
> rule out database growth.
> TYIA|||Thank you for your response.
Unfortunately, we are sill on 2000.
Are there any alerts SQL that would capture this?
Any way to write to the Event logs?
I don't know when it will happen and can't have a trace running 24 x 7.
Thank you .
"Tibor Karaszi" wrote:

> You can catch it using a Profiler trace. And probably also using an event
notification (see CREATE
> EVENT NOTIFICATION), if you are on 2005.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://sqlblog.com/blogs/tibor_karaszi
>
> "RobinMC" <RobinMC@.discussions.microsoft.com> wrote in message
> news:EA3B1E1E-9A31-4C7D-B3F0-89B32CAD431F@.microsoft.com...
>|||No and no, I'm afraid. It isn't a big deal to have a *server side* profiler
trace running
continually. Of you can poll for the file size as frequently as you need and
based on that log when
you notice a change in size.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"RobinMC" <RobinMC@.discussions.microsoft.com> wrote in message
news:3AC5067C-9E8D-4564-BDD9-1F365BB8661D@.microsoft.com...[vbcol=seagreen]
> Thank you for your response.
> Unfortunately, we are sill on 2000.
> Are there any alerts SQL that would capture this?
> Any way to write to the Event logs?
> I don't know when it will happen and can't have a trace running 24 x 7.
> Thank you .
> "Tibor Karaszi" wrote:
>|||On Aug 15, 9:45 pm, "Tibor Karaszi"
<tibor_please.no.email_kara...@.hotmail.nomail.com> wrote:
> No and no, I'm afraid. It isn't a big deal to have a *server side* profile
r trace running
> continually. Of you can poll for the file size as frequently as you need a
nd based on that log when
> you notice a change in size.
> --
> Tibor Karaszi, SQL Server MVPhttp://www.karaszi.com/sqlserver/default.asph
ttp://sqlblog.com/blogs/tibor_karaszi
> "RobinMC" <Robi...@.discussions.microsoft.com> wrote in message
> news:3AC5067C-9E8D-4564-BDD9-1F365BB8661D@.microsoft.com...
>
>
>
>
>
>
>
> - Show quoted text -
Instead of a profiler trace, start a perfmon (administrative tools-
>performance) trace on the server, tracing the following counters:
Performance Object: MsSQL:Databases
Counters: Data File Size KB, Log File Size KB
Instances: Select the appropriate database(s) that you'd like to
collect.
A relatively low sampling interval (once per every minute or few)
should be enough to give you the data that you're looking for.
Whenever any of these counters increase, you'll know that a file
growth occured and the amount that the file was expanded.
MS|||On Wed, 15 Aug 2007 13:10:01 -0700, RobinMC
<RobinMC@.discussions.microsoft.com> wrote:

>Our databases are configured for automatic growth by varying increments of
MB.
>Is there a way (an alert?) to find out when the growth has actualy happened
?
>Our customers are complaining of "random" poor performance - I would like t
o
>rule out database growth.
You could write a short app that checks the .mdf filesize every five
seconds or so.
J.

any stored procedure to report all the databases' information

Hello everyone,
We're going to migrate our SQL 2000 server from one domain to another. I
need to get a better idea of how our current databases are structured and
utilized. What I want is a report showing each database's:
name
data size
log size
users
if backed up or not
Before I dive into it in my own way, just wondering if there is any stored
procedure that can help collect the above information? I'd appreciate any
help.
Thanks,
Bing
sp_helpdb does most what I wanted.
"bing" wrote:

> Hello everyone,
> We're going to migrate our SQL 2000 server from one domain to another. I
> need to get a better idea of how our current databases are structured and
> utilized. What I want is a report showing each database's:
> name
> data size
> log size
> users
> if backed up or not
> Before I dive into it in my own way, just wondering if there is any stored
> procedure that can help collect the above information? I'd appreciate any
> help.
> Thanks,
> Bing

any stored procedure to report all the databases' information

Hello everyone,
We're going to migrate our SQL 2000 server from one domain to another. I
need to get a better idea of how our current databases are structured and
utilized. What I want is a report showing each database's:
name
data size
log size
users
if backed up or not
Before I dive into it in my own way, just wondering if there is any stored
procedure that can help collect the above information? I'd appreciate any
help.
Thanks,
Bingsp_helpdb does most what I wanted.
"bing" wrote:
> Hello everyone,
> We're going to migrate our SQL 2000 server from one domain to another. I
> need to get a better idea of how our current databases are structured and
> utilized. What I want is a report showing each database's:
> name
> data size
> log size
> users
> if backed up or not
> Before I dive into it in my own way, just wondering if there is any stored
> procedure that can help collect the above information? I'd appreciate any
> help.
> Thanks,
> Bing

any stored procedure to report all the databases' information

Hello everyone,
We're going to migrate our SQL 2000 server from one domain to another. I
need to get a better idea of how our current databases are structured and
utilized. What I want is a report showing each database's:
name
data size
log size
users
if backed up or not
Before I dive into it in my own way, just wondering if there is any stored
procedure that can help collect the above information? I'd appreciate any
help.
Thanks,
Bingsp_helpdb does most what I wanted.
"bing" wrote:

> Hello everyone,
> We're going to migrate our SQL 2000 server from one domain to another. I
> need to get a better idea of how our current databases are structured and
> utilized. What I want is a report showing each database's:
> name
> data size
> log size
> users
> if backed up or not
> Before I dive into it in my own way, just wondering if there is any stored
> procedure that can help collect the above information? I'd appreciate any
> help.
> Thanks,
> Bing

Any recommendation for a good DBA tool.

Hi All
I looking for a good DBA tool to view the REAL TIME performance of my
databases especially the stored procedures. Anyone with recommendations
please help? Thank you in advance.Hi, Maybe Spotlight from Quest Software
"MittyKom" wrote:
> Hi All
> I looking for a good DBA tool to view the REAL TIME performance of my
> databases especially the stored procedures. Anyone with recommendations
> please help? Thank you in advance.
>|||I'm currently evaluating Quest Spotlight for SQL Server. I've evaluated a
few different offerings and I like this one the best. Additional
performance tools can be purchased as a suite (Quest Central) or as
indiviual components.
HTH
Jerry
"MittyKom" <MittyKom@.discussions.microsoft.com> wrote in message
news:302F5C4A-ED69-4175-A26C-C4E84BDF64A4@.microsoft.com...
> Hi All
> I looking for a good DBA tool to view the REAL TIME performance of my
> databases especially the stored procedures. Anyone with recommendations
> please help? Thank you in advance.
>|||What you want to get from the performance. I use Profiler to mornitor any
SP or ad hoc query that run more than 1 minues. If I found one, then I will
try to optimize the SQL statement. Why am I doing this? because I am a new
DBA, and most of our database design were done by a consultant firm. And I
found that a lot of SP were done terriblely.
Perayu
"MittyKom" <MittyKom@.discussions.microsoft.com> wrote in message
news:302F5C4A-ED69-4175-A26C-C4E84BDF64A4@.microsoft.com...
> Hi All
> I looking for a good DBA tool to view the REAL TIME performance of my
> databases especially the stored procedures. Anyone with recommendations
> please help? Thank you in advance.
>

any link that talks about different DR options

We want to set up Disaster Recovery for our SQL databases and want to
evaluate the different technologies out there that can do so..( log
shipping, mirroring, stretch clustering, SAN replication, 3rd party,etc.)
and wanted to get a quick overview of the pros and cons of each approach
that may be listed on a link already.
Can someone let me know if there is one out there and send me the path ?
ThanksHi Hassan,
Check these links:
http://technet.microsoft.com/en-us/...r/bb331801.aspx
http://support.microsoft.com/kb/822400
Jonathan
Hassan wrote:
> We want to set up Disaster Recovery for our SQL databases and want to
> evaluate the different technologies out there that can do so..( log
> shipping, mirroring, stretch clustering, SAN replication, 3rd party,etc.)
> and wanted to get a quick overview of the pros and cons of each approach
> that may be listed on a link already.
> Can someone let me know if there is one out there and send me the path ?
> Thanks
>

any link that talks about different DR options

We want to set up Disaster Recovery for our SQL databases and want to
evaluate the different technologies out there that can do so..( log
shipping, mirroring, stretch clustering, SAN replication, 3rd party,etc.)
and wanted to get a quick overview of the pros and cons of each approach
that may be listed on a link already.
Can someone let me know if there is one out there and send me the path ?
ThanksHi Hassan,
Check these links:
http://technet.microsoft.com/en-us/sqlserver/bb331801.aspx
http://support.microsoft.com/kb/822400
Jonathan
Hassan wrote:
> We want to set up Disaster Recovery for our SQL databases and want to
> evaluate the different technologies out there that can do so..( log
> shipping, mirroring, stretch clustering, SAN replication, 3rd party,etc.)
> and wanted to get a quick overview of the pros and cons of each approach
> that may be listed on a link already.
> Can someone let me know if there is one out there and send me the path ?
> Thanks
>

Any good whitepapers on security/deployment for entire SQL Server BI solutions?

At my current employer we are struggling with the best way to manage security and deployment of a project that contains databases, SSIS, SSAS and SSRS components, using configurations.

Environment (Dev):

3 SQL Server databases, all using mixed-mode security, using SQL Server security credentials.

12 SSIS packages; one master package, eleven child packages, 3 shared data sources

1 SSAS database; one cube, 15 dimensions, three referenced data sources from the SSIS project (in same solution)

6 SSRS reports, one data source to cube (not shared- doesn't appear SSRS can share datasources among other projects in the solution? Why?)

Everything runs fine in development. Now comes the tricky part.

Deploying SSIS and SSAS into production environments:

-Packages use XML config files for connection strings to three relational data sources.

-Deploy to SQL Server storage. Deploy wizard copies package dependencies (including XML config files) to default location set in INI file. When I do this, no config file shows up in remote server (remote server not set up identical to local, so directory does not exist. Need UNC path?) So, being a developer with no "special" permissions on the PROD server, what security permissions is allowing the deployment wizard from copying files to this location on a production server?

-Using a deploy script using dtutil doesn't copy the SSIS dependencies. Is this matter of using COPY or XCOPY to copy the configuration files to the dependency location? Again, in real-world practice, do developers typically change this location in the INI file to another location, or stick with the default. In either case, how does security work that allows files to get copied to the remote folder? (i.e. manual, or SQL Server manages this file folder permission through some other magic)

When using SSMS and running the package after being deployed on the remote server, if the config path is the default (e.g. C:\program files\Microsoft SQL Server\90\DTS\Packages\...) it appears to be read from the local machines directory rather than the remote machines directory path (do I need to use UNC paths? The wizard doesn't give this option it seems)

-When scheduling the job from SQL Agent, does the proxy account need permissions to the folder the config files sit in?

-What about the roles security on the packages themselves? Where does the server roles come into play (dtsltuser, dtsadmin)

-Because the SSAS project uses connection references to the SSIS project in BIDS, and SSIS project uses configurations, will SSAS pick up on these connections?

-What about impersonation levels for SSAS? Leave all data sources set to default, and set the database impersonation level to "UseServiceAccount"? What if the developer is not the same as the OLAP administrator on the production server? In this case, Use Service Account isn't an option, and neither is the current users credentials.

-SSAS database also has security for Full Control, but still doesn't prevent security at the data source level within the database (talking about impersonation level, not source db credentials)

-How can SSRS connections leverage other shared connections?

As you can see, there are a ton of security considerations, none of which are intuitive and can be configured multiple ways and actually work (and a ton of ways that won't work).

I need a simple cheat-sheet about each step to take to configure this so multiple developers can work without interruption, hot-deploying SSIS, SSAS, and SSRS changes into different environments (QA, PROD).

-Kory

It is still on my list to read so I'm not sure it contains the information you need but check http://www.microsoft.com/technet/prodtechnol/sql/2005/sql2005secbestpract.mspx

WesleyB

Visit my SQL Server weblog @. http://dis4ea.blogspot.com

|||

I've already read this- not much on the BI tools side, mostly for the RDBMS.

-Kory

Any experience with really large, high transaction databases?

Most of my experience is with what I consider small, low transaction rate
databases. For example, the largest table in one DB has fewer than 100,000
rows (other tables have far fewer rows) with anywhere from 1 to 15 people
posting queries and updates at any given time. SQL Server handles this just
fine.
We are beginning to design a database for a new project. We haven't
determined how big the tables will be but at peak usage will have perhaps
100 people logged on at a time. There is pressure to use Oracle because of a
belief that SQL Server will not be up to the demands.
Solid, unbiased comparisons between Oracle and SQL Server are hard to come
by. Does anyone here have experience with large systems that would show what
SQL Server is really capable of?
Much obliged.
I can't give out many proprietary details but we are a SQL Server shop and
we handle databases > 1 TB with over 1000 simultaneous connections.
IMHO, your bottleneck is going to be the architecture and design of the
database and application, and more importantly hardware. You will not be
bound by the vendor choice.
http://www.aspfaq.com/
(Reverse address to reply.)
"Geoff Pennington" <Geoffrey.Pennington@.tma.osd.mil.nospam> wrote in message
news:eJtzZ#dZEHA.3092@.tk2msftngp13.phx.gbl...
> Most of my experience is with what I consider small, low transaction rate
> databases. For example, the largest table in one DB has fewer than 100,000
> rows (other tables have far fewer rows) with anywhere from 1 to 15 people
> posting queries and updates at any given time. SQL Server handles this
just
> fine.
> We are beginning to design a database for a new project. We haven't
> determined how big the tables will be but at peak usage will have perhaps
> 100 people logged on at a time. There is pressure to use Oracle because of
a
> belief that SQL Server will not be up to the demands.
> Solid, unbiased comparisons between Oracle and SQL Server are hard to come
> by. Does anyone here have experience with large systems that would show
what
> SQL Server is really capable of?
> Much obliged.
>
|||100 concurrent users is piddly these days for SQL Server. Three years ago I
was doing over 100 Million Inserts / Updates or Deletes against a db with
over 1 Billion rows on what would be considered an obsolete box these days
with no problem what so ever. Here are some links but these are for VLDB's.
We are talking thousands of trans per second and up with Terabyte db's.
http://www.microsoft.com/sql/techinf...calability.asp
Andrew J. Kelly SQL MVP
"Geoff Pennington" <Geoffrey.Pennington@.tma.osd.mil.nospam> wrote in message
news:eJtzZ%23dZEHA.3092@.tk2msftngp13.phx.gbl...
> Most of my experience is with what I consider small, low transaction rate
> databases. For example, the largest table in one DB has fewer than 100,000
> rows (other tables have far fewer rows) with anywhere from 1 to 15 people
> posting queries and updates at any given time. SQL Server handles this
just
> fine.
> We are beginning to design a database for a new project. We haven't
> determined how big the tables will be but at peak usage will have perhaps
> 100 people logged on at a time. There is pressure to use Oracle because of
a
> belief that SQL Server will not be up to the demands.
> Solid, unbiased comparisons between Oracle and SQL Server are hard to come
> by. Does anyone here have experience with large systems that would show
what
> SQL Server is really capable of?
> Much obliged.
>
|||Geoff,
The best example I can think of is Monster.com. They use SQL Server for
their entire jobs database, and get a lot more traffic than 100 people
logged on at a time!
One of the reasons, IMO, Oracle is often considered to be "more scalable"
than SQL Server is that companies tend to buy cheaper servers to run SQL
Server than to run Oracle. For instance, a recent employer of mine based
their entire product on SQL Server and our servers were dual Xeons with 2 gb
of RAM. The servers ran without too many problems, but management would
never spend a cent to upgrade them when we did have issues.
A consultant was hired for a totally new project and convinced management to
take it forward with Oracle. A pair of servers was purchased (QA and
Production), each with 8 Itanium processors and 64 gb of RAM. This, for an
unproven product that was making no money.
Why? Because for some reason, due to the way SQL Server is marketed vs. the
way Oracle is marketed, it's assumed that Oracle "should" sit on a huge
server whereas SQL Server will do better on a glorified desktop box. I'm
not sure how to best convince people of the truth... Good luck!
"Geoff Pennington" <Geoffrey.Pennington@.tma.osd.mil.nospam> wrote in message
news:eJtzZ%23dZEHA.3092@.tk2msftngp13.phx.gbl...
> Most of my experience is with what I consider small, low transaction rate
> databases. For example, the largest table in one DB has fewer than 100,000
> rows (other tables have far fewer rows) with anywhere from 1 to 15 people
> posting queries and updates at any given time. SQL Server handles this
just
> fine.
> We are beginning to design a database for a new project. We haven't
> determined how big the tables will be but at peak usage will have perhaps
> 100 people logged on at a time. There is pressure to use Oracle because of
a
> belief that SQL Server will not be up to the demands.
> Solid, unbiased comparisons between Oracle and SQL Server are hard to come
> by. Does anyone here have experience with large systems that would show
what
> SQL Server is really capable of?
> Much obliged.
>
|||I agree with Aaron here, the architecture and design are key. We have over
1TB of data here and tables with over 1 billion records (horizontally
partitioned.) SQL Server can handle the workload if the project is done
correctly. Make sure to note if you don't have the enterprise version of
SQL you can't use more than 2 gigs of memory.
-John Oakes
"Aaron [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:ui$E6CeZEHA.3304@.TK2MSFTNGP09.phx.gbl...
> I can't give out many proprietary details but we are a SQL Server shop and
> we handle databases > 1 TB with over 1000 simultaneous connections.
> IMHO, your bottleneck is going to be the architecture and design of the
> database and application, and more importantly hardware. You will not be
> bound by the vendor choice.
> --
> http://www.aspfaq.com/
> (Reverse address to reply.)
>
>
> "Geoff Pennington" <Geoffrey.Pennington@.tma.osd.mil.nospam> wrote in
message[vbcol=seagreen]
> news:eJtzZ#dZEHA.3092@.tk2msftngp13.phx.gbl...
rate[vbcol=seagreen]
100,000[vbcol=seagreen]
people[vbcol=seagreen]
> just
perhaps[vbcol=seagreen]
of[vbcol=seagreen]
> a
come
> what
>
|||Thanks Adam - Monster.com is a good example. Could you tell me how you know
they use SQL Server?
I wonder if the reason everyone thinks of SQL Server as being lower end is
two-fold: (1) it is cheaper to get a SQL license, and (2) Oracle has always
been associated with large servers, while MS is associated with the desktop.
"Adam Machanic" <amachanic@.hotmail._removetoemail_.com> wrote in message
news:eQZjlOeZEHA.2776@.TK2MSFTNGP10.phx.gbl...
> Geoff,
> The best example I can think of is Monster.com. They use SQL Server for
> their entire jobs database, and get a lot more traffic than 100 people
> logged on at a time!
> One of the reasons, IMO, Oracle is often considered to be "more scalable"
> than SQL Server is that companies tend to buy cheaper servers to run SQL
> Server than to run Oracle. For instance, a recent employer of mine based
> their entire product on SQL Server and our servers were dual Xeons with 2
gb
> of RAM. The servers ran without too many problems, but management would
> never spend a cent to upgrade them when we did have issues.
> A consultant was hired for a totally new project and convinced management
to
> take it forward with Oracle. A pair of servers was purchased (QA and
> Production), each with 8 Itanium processors and 64 gb of RAM. This, for
an
> unproven product that was making no money.
> Why? Because for some reason, due to the way SQL Server is marketed vs.
the
> way Oracle is marketed, it's assumed that Oracle "should" sit on a huge
> server whereas SQL Server will do better on a glorified desktop box. I'm
> not sure how to best convince people of the truth... Good luck!
>
> "Geoff Pennington" <Geoffrey.Pennington@.tma.osd.mil.nospam> wrote in
message[vbcol=seagreen]
> news:eJtzZ%23dZEHA.3092@.tk2msftngp13.phx.gbl...
rate[vbcol=seagreen]
100,000[vbcol=seagreen]
people[vbcol=seagreen]
> just
perhaps[vbcol=seagreen]
of[vbcol=seagreen]
> a
come
> what
>
|||"Geoff Pennington" <Geoffrey.Pennington@.tma.osd.mil.nospam> wrote in message
news:%23RVJG9eZEHA.912@.TK2MSFTNGP10.phx.gbl...
> Thanks Adam - Monster.com is a good example. Could you tell me how you
know
> they use SQL Server?
I interviewed there last year for a data architect position.
|||There are many larger sites with lots of users that use SQL Server.
Dell.com, CareerBuilder.com, Barnes & Nobles etc...
Andrew J. Kelly SQL MVP
"Geoff Pennington" <Geoffrey.Pennington@.tma.osd.mil.nospam> wrote in message
news:%23RVJG9eZEHA.912@.TK2MSFTNGP10.phx.gbl...
> Thanks Adam - Monster.com is a good example. Could you tell me how you
know
> they use SQL Server?
> I wonder if the reason everyone thinks of SQL Server as being lower end is
> two-fold: (1) it is cheaper to get a SQL license, and (2) Oracle has
always
> been associated with large servers, while MS is associated with the
desktop.[vbcol=seagreen]
>
> "Adam Machanic" <amachanic@.hotmail._removetoemail_.com> wrote in message
> news:eQZjlOeZEHA.2776@.TK2MSFTNGP10.phx.gbl...
scalable"[vbcol=seagreen]
based[vbcol=seagreen]
2[vbcol=seagreen]
> gb
management[vbcol=seagreen]
> to
> an
> the
I'm[vbcol=seagreen]
> message
> rate
> 100,000
> people
> perhaps
because[vbcol=seagreen]
> of
> come
show
>
|||SQL Server is not a punk anymore.
many of the ORACLE Folks still think of SQL Server as SQL Server 6.5.
if you want a Non-Biased opinion check out the TPC site.
www.tpc.org
The results speak for themselves (SQL Server can DEFINATELY Hold it's own AS
Can ORACLE and DB2).
Greg Jackson
PDX, Oregon
|||We HAVE had performance issues. HOWEVER every one of them were
"Architectural" in nature.
Cheers
Greg Jackson
PDX, Oregon

Any experience with really large, high transaction databases?

Most of my experience is with what I consider small, low transaction rate
databases. For example, the largest table in one DB has fewer than 100,000
rows (other tables have far fewer rows) with anywhere from 1 to 15 people
posting queries and updates at any given time. SQL Server handles this just
fine.
We are beginning to design a database for a new project. We haven't
determined how big the tables will be but at peak usage will have perhaps
100 people logged on at a time. There is pressure to use Oracle because of a
belief that SQL Server will not be up to the demands.
Solid, unbiased comparisons between Oracle and SQL Server are hard to come
by. Does anyone here have experience with large systems that would show what
SQL Server is really capable of?
Much obliged.I can't give out many proprietary details but we are a SQL Server shop and
we handle databases > 1 TB with over 1000 simultaneous connections.
IMHO, your bottleneck is going to be the architecture and design of the
database and application, and more importantly hardware. You will not be
bound by the vendor choice.
http://www.aspfaq.com/
(Reverse address to reply.)
"Geoff Pennington" <Geoffrey.Pennington@.tma.osd.mil.nospam> wrote in message
news:eJtzZ#dZEHA.3092@.tk2msftngp13.phx.gbl...
> Most of my experience is with what I consider small, low transaction rate
> databases. For example, the largest table in one DB has fewer than 100,000
> rows (other tables have far fewer rows) with anywhere from 1 to 15 people
> posting queries and updates at any given time. SQL Server handles this
just
> fine.
> We are beginning to design a database for a new project. We haven't
> determined how big the tables will be but at peak usage will have perhaps
> 100 people logged on at a time. There is pressure to use Oracle because of
a
> belief that SQL Server will not be up to the demands.
> Solid, unbiased comparisons between Oracle and SQL Server are hard to come
> by. Does anyone here have experience with large systems that would show
what
> SQL Server is really capable of?
> Much obliged.
>|||100 concurrent users is piddly these days for SQL Server. Three years ago I
was doing over 100 Million Inserts / Updates or Deletes against a db with
over 1 Billion rows on what would be considered an obsolete box these days
with no problem what so ever. Here are some links but these are for VLDB's.
We are talking thousands of trans per second and up with Terabyte db's.
http://www.microsoft.com/sql/techin...scalability.asp
Andrew J. Kelly SQL MVP
"Geoff Pennington" <Geoffrey.Pennington@.tma.osd.mil.nospam> wrote in message
news:eJtzZ%23dZEHA.3092@.tk2msftngp13.phx.gbl...
> Most of my experience is with what I consider small, low transaction rate
> databases. For example, the largest table in one DB has fewer than 100,000
> rows (other tables have far fewer rows) with anywhere from 1 to 15 people
> posting queries and updates at any given time. SQL Server handles this
just
> fine.
> We are beginning to design a database for a new project. We haven't
> determined how big the tables will be but at peak usage will have perhaps
> 100 people logged on at a time. There is pressure to use Oracle because of
a
> belief that SQL Server will not be up to the demands.
> Solid, unbiased comparisons between Oracle and SQL Server are hard to come
> by. Does anyone here have experience with large systems that would show
what
> SQL Server is really capable of?
> Much obliged.
>|||Geoff,
The best example I can think of is Monster.com. They use SQL Server for
their entire jobs database, and get a lot more traffic than 100 people
logged on at a time!
One of the reasons, IMO, Oracle is often considered to be "more scalable"
than SQL Server is that companies tend to buy cheaper servers to run SQL
Server than to run Oracle. For instance, a recent employer of mine based
their entire product on SQL Server and our servers were dual Xeons with 2 gb
of RAM. The servers ran without too many problems, but management would
never spend a cent to upgrade them when we did have issues.
A consultant was hired for a totally new project and convinced management to
take it forward with Oracle. A pair of servers was purchased (QA and
Production), each with 8 Itanium processors and 64 gb of RAM. This, for an
unproven product that was making no money.
Why? Because for some reason, due to the way SQL Server is marketed vs. the
way Oracle is marketed, it's assumed that Oracle "should" sit on a huge
server whereas SQL Server will do better on a glorified desktop box. I'm
not sure how to best convince people of the truth... Good luck!
"Geoff Pennington" <Geoffrey.Pennington@.tma.osd.mil.nospam> wrote in message
news:eJtzZ%23dZEHA.3092@.tk2msftngp13.phx.gbl...
> Most of my experience is with what I consider small, low transaction rate
> databases. For example, the largest table in one DB has fewer than 100,000
> rows (other tables have far fewer rows) with anywhere from 1 to 15 people
> posting queries and updates at any given time. SQL Server handles this
just
> fine.
> We are beginning to design a database for a new project. We haven't
> determined how big the tables will be but at peak usage will have perhaps
> 100 people logged on at a time. There is pressure to use Oracle because of
a
> belief that SQL Server will not be up to the demands.
> Solid, unbiased comparisons between Oracle and SQL Server are hard to come
> by. Does anyone here have experience with large systems that would show
what
> SQL Server is really capable of?
> Much obliged.
>|||I agree with Aaron here, the architecture and design are key. We have over
1TB of data here and tables with over 1 billion records (horizontally
partitioned.) SQL Server can handle the workload if the project is done
correctly. Make sure to note if you don't have the enterprise version of
SQL you can't use more than 2 gigs of memory.
-John Oakes
"Aaron [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:ui$E6CeZEHA.3304@.TK2MSFTNGP09.phx.gbl...
> I can't give out many proprietary details but we are a SQL Server shop and
> we handle databases > 1 TB with over 1000 simultaneous connections.
> IMHO, your bottleneck is going to be the architecture and design of the
> database and application, and more importantly hardware. You will not be
> bound by the vendor choice.
> --
> http://www.aspfaq.com/
> (Reverse address to reply.)
>
>
> "Geoff Pennington" <Geoffrey.Pennington@.tma.osd.mil.nospam> wrote in
message
> news:eJtzZ#dZEHA.3092@.tk2msftngp13.phx.gbl...
rate[vbcol=seagreen]
100,000[vbcol=seagreen]
people[vbcol=seagreen]
> just
perhaps[vbcol=seagreen]
of[vbcol=seagreen]
> a
come[vbcol=seagreen]
> what
>|||Thanks Adam - Monster.com is a good example. Could you tell me how you know
they use SQL Server?
I wonder if the reason everyone thinks of SQL Server as being lower end is
two-fold: (1) it is cheaper to get a SQL license, and (2) Oracle has always
been associated with large servers, while MS is associated with the desktop.
"Adam Machanic" <amachanic@.hotmail._removetoemail_.com> wrote in message
news:eQZjlOeZEHA.2776@.TK2MSFTNGP10.phx.gbl...
> Geoff,
> The best example I can think of is Monster.com. They use SQL Server for
> their entire jobs database, and get a lot more traffic than 100 people
> logged on at a time!
> One of the reasons, IMO, Oracle is often considered to be "more scalable"
> than SQL Server is that companies tend to buy cheaper servers to run SQL
> Server than to run Oracle. For instance, a recent employer of mine based
> their entire product on SQL Server and our servers were dual Xeons with 2
gb
> of RAM. The servers ran without too many problems, but management would
> never spend a cent to upgrade them when we did have issues.
> A consultant was hired for a totally new project and convinced management
to
> take it forward with Oracle. A pair of servers was purchased (QA and
> Production), each with 8 Itanium processors and 64 gb of RAM. This, for
an
> unproven product that was making no money.
> Why? Because for some reason, due to the way SQL Server is marketed vs.
the
> way Oracle is marketed, it's assumed that Oracle "should" sit on a huge
> server whereas SQL Server will do better on a glorified desktop box. I'm
> not sure how to best convince people of the truth... Good luck!
>
> "Geoff Pennington" <Geoffrey.Pennington@.tma.osd.mil.nospam> wrote in
message
> news:eJtzZ%23dZEHA.3092@.tk2msftngp13.phx.gbl...
rate[vbcol=seagreen]
100,000[vbcol=seagreen]
people[vbcol=seagreen]
> just
perhaps[vbcol=seagreen]
of[vbcol=seagreen]
> a
come[vbcol=seagreen]
> what
>|||"Geoff Pennington" <Geoffrey.Pennington@.tma.osd.mil.nospam> wrote in message
news:%23RVJG9eZEHA.912@.TK2MSFTNGP10.phx.gbl...
> Thanks Adam - Monster.com is a good example. Could you tell me how you
know
> they use SQL Server?
I interviewed there last year for a data architect position.|||There are many larger sites with lots of users that use SQL Server.
Dell.com, CareerBuilder.com, Barnes & Nobles etc...
Andrew J. Kelly SQL MVP
"Geoff Pennington" <Geoffrey.Pennington@.tma.osd.mil.nospam> wrote in message
news:%23RVJG9eZEHA.912@.TK2MSFTNGP10.phx.gbl...
> Thanks Adam - Monster.com is a good example. Could you tell me how you
know
> they use SQL Server?
> I wonder if the reason everyone thinks of SQL Server as being lower end is
> two-fold: (1) it is cheaper to get a SQL license, and (2) Oracle has
always
> been associated with large servers, while MS is associated with the
desktop.
>
> "Adam Machanic" <amachanic@.hotmail._removetoemail_.com> wrote in message
> news:eQZjlOeZEHA.2776@.TK2MSFTNGP10.phx.gbl...
scalable"[vbcol=seagreen]
based[vbcol=seagreen]
2[vbcol=seagreen]
> gb
management[vbcol=seagreen]
> to
> an
> the
I'm[vbcol=seagreen]
> message
> rate
> 100,000
> people
> perhaps
because[vbcol=seagreen]
> of
> come
show[vbcol=seagreen]
>|||SQL Server is not a punk anymore.
many of the ORACLE Folks still think of SQL Server as SQL Server 6.5.
if you want a Non-Biased opinion check out the TPC site.
www.tpc.org
The results speak for themselves (SQL Server can DEFINATELY Hold it's own AS
Can ORACLE and DB2).
Greg Jackson
PDX, Oregon|||We HAVE had performance issues. HOWEVER every one of them were
"Architectural" in nature.
Cheers
Greg Jackson
PDX, Oregon

Any experience with really large, high transaction databases?

Most of my experience is with what I consider small, low transaction rate
databases. For example, the largest table in one DB has fewer than 100,000
rows (other tables have far fewer rows) with anywhere from 1 to 15 people
posting queries and updates at any given time. SQL Server handles this just
fine.
We are beginning to design a database for a new project. We haven't
determined how big the tables will be but at peak usage will have perhaps
100 people logged on at a time. There is pressure to use Oracle because of a
belief that SQL Server will not be up to the demands.
Solid, unbiased comparisons between Oracle and SQL Server are hard to come
by. Does anyone here have experience with large systems that would show what
SQL Server is really capable of?
Much obliged.I can't give out many proprietary details but we are a SQL Server shop and
we handle databases > 1 TB with over 1000 simultaneous connections.
IMHO, your bottleneck is going to be the architecture and design of the
database and application, and more importantly hardware. You will not be
bound by the vendor choice.
--
http://www.aspfaq.com/
(Reverse address to reply.)
"Geoff Pennington" <Geoffrey.Pennington@.tma.osd.mil.nospam> wrote in message
news:eJtzZ#dZEHA.3092@.tk2msftngp13.phx.gbl...
> Most of my experience is with what I consider small, low transaction rate
> databases. For example, the largest table in one DB has fewer than 100,000
> rows (other tables have far fewer rows) with anywhere from 1 to 15 people
> posting queries and updates at any given time. SQL Server handles this
just
> fine.
> We are beginning to design a database for a new project. We haven't
> determined how big the tables will be but at peak usage will have perhaps
> 100 people logged on at a time. There is pressure to use Oracle because of
a
> belief that SQL Server will not be up to the demands.
> Solid, unbiased comparisons between Oracle and SQL Server are hard to come
> by. Does anyone here have experience with large systems that would show
what
> SQL Server is really capable of?
> Much obliged.
>|||100 concurrent users is piddly these days for SQL Server. Three years ago I
was doing over 100 Million Inserts / Updates or Deletes against a db with
over 1 Billion rows on what would be considered an obsolete box these days
with no problem what so ever. Here are some links but these are for VLDB's.
We are talking thousands of trans per second and up with Terabyte db's.
http://www.microsoft.com/sql/techinfo/administration/2000/scalability.asp
--
Andrew J. Kelly SQL MVP
"Geoff Pennington" <Geoffrey.Pennington@.tma.osd.mil.nospam> wrote in message
news:eJtzZ%23dZEHA.3092@.tk2msftngp13.phx.gbl...
> Most of my experience is with what I consider small, low transaction rate
> databases. For example, the largest table in one DB has fewer than 100,000
> rows (other tables have far fewer rows) with anywhere from 1 to 15 people
> posting queries and updates at any given time. SQL Server handles this
just
> fine.
> We are beginning to design a database for a new project. We haven't
> determined how big the tables will be but at peak usage will have perhaps
> 100 people logged on at a time. There is pressure to use Oracle because of
a
> belief that SQL Server will not be up to the demands.
> Solid, unbiased comparisons between Oracle and SQL Server are hard to come
> by. Does anyone here have experience with large systems that would show
what
> SQL Server is really capable of?
> Much obliged.
>|||Geoff,
The best example I can think of is Monster.com. They use SQL Server for
their entire jobs database, and get a lot more traffic than 100 people
logged on at a time!
One of the reasons, IMO, Oracle is often considered to be "more scalable"
than SQL Server is that companies tend to buy cheaper servers to run SQL
Server than to run Oracle. For instance, a recent employer of mine based
their entire product on SQL Server and our servers were dual Xeons with 2 gb
of RAM. The servers ran without too many problems, but management would
never spend a cent to upgrade them when we did have issues.
A consultant was hired for a totally new project and convinced management to
take it forward with Oracle. A pair of servers was purchased (QA and
Production), each with 8 Itanium processors and 64 gb of RAM. This, for an
unproven product that was making no money.
Why? Because for some reason, due to the way SQL Server is marketed vs. the
way Oracle is marketed, it's assumed that Oracle "should" sit on a huge
server whereas SQL Server will do better on a glorified desktop box. I'm
not sure how to best convince people of the truth... Good luck!
"Geoff Pennington" <Geoffrey.Pennington@.tma.osd.mil.nospam> wrote in message
news:eJtzZ%23dZEHA.3092@.tk2msftngp13.phx.gbl...
> Most of my experience is with what I consider small, low transaction rate
> databases. For example, the largest table in one DB has fewer than 100,000
> rows (other tables have far fewer rows) with anywhere from 1 to 15 people
> posting queries and updates at any given time. SQL Server handles this
just
> fine.
> We are beginning to design a database for a new project. We haven't
> determined how big the tables will be but at peak usage will have perhaps
> 100 people logged on at a time. There is pressure to use Oracle because of
a
> belief that SQL Server will not be up to the demands.
> Solid, unbiased comparisons between Oracle and SQL Server are hard to come
> by. Does anyone here have experience with large systems that would show
what
> SQL Server is really capable of?
> Much obliged.
>|||I agree with Aaron here, the architecture and design are key. We have over
1TB of data here and tables with over 1 billion records (horizontally
partitioned.) SQL Server can handle the workload if the project is done
correctly. Make sure to note if you don't have the enterprise version of
SQL you can't use more than 2 gigs of memory.
-John Oakes
"Aaron [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:ui$E6CeZEHA.3304@.TK2MSFTNGP09.phx.gbl...
> I can't give out many proprietary details but we are a SQL Server shop and
> we handle databases > 1 TB with over 1000 simultaneous connections.
> IMHO, your bottleneck is going to be the architecture and design of the
> database and application, and more importantly hardware. You will not be
> bound by the vendor choice.
> --
> http://www.aspfaq.com/
> (Reverse address to reply.)
>
>
> "Geoff Pennington" <Geoffrey.Pennington@.tma.osd.mil.nospam> wrote in
message
> news:eJtzZ#dZEHA.3092@.tk2msftngp13.phx.gbl...
> > Most of my experience is with what I consider small, low transaction
rate
> > databases. For example, the largest table in one DB has fewer than
100,000
> > rows (other tables have far fewer rows) with anywhere from 1 to 15
people
> > posting queries and updates at any given time. SQL Server handles this
> just
> > fine.
> >
> > We are beginning to design a database for a new project. We haven't
> > determined how big the tables will be but at peak usage will have
perhaps
> > 100 people logged on at a time. There is pressure to use Oracle because
of
> a
> > belief that SQL Server will not be up to the demands.
> >
> > Solid, unbiased comparisons between Oracle and SQL Server are hard to
come
> > by. Does anyone here have experience with large systems that would show
> what
> > SQL Server is really capable of?
> >
> > Much obliged.
> >
> >
>|||Thanks Adam - Monster.com is a good example. Could you tell me how you know
they use SQL Server?
I wonder if the reason everyone thinks of SQL Server as being lower end is
two-fold: (1) it is cheaper to get a SQL license, and (2) Oracle has always
been associated with large servers, while MS is associated with the desktop.
"Adam Machanic" <amachanic@.hotmail._removetoemail_.com> wrote in message
news:eQZjlOeZEHA.2776@.TK2MSFTNGP10.phx.gbl...
> Geoff,
> The best example I can think of is Monster.com. They use SQL Server for
> their entire jobs database, and get a lot more traffic than 100 people
> logged on at a time!
> One of the reasons, IMO, Oracle is often considered to be "more scalable"
> than SQL Server is that companies tend to buy cheaper servers to run SQL
> Server than to run Oracle. For instance, a recent employer of mine based
> their entire product on SQL Server and our servers were dual Xeons with 2
gb
> of RAM. The servers ran without too many problems, but management would
> never spend a cent to upgrade them when we did have issues.
> A consultant was hired for a totally new project and convinced management
to
> take it forward with Oracle. A pair of servers was purchased (QA and
> Production), each with 8 Itanium processors and 64 gb of RAM. This, for
an
> unproven product that was making no money.
> Why? Because for some reason, due to the way SQL Server is marketed vs.
the
> way Oracle is marketed, it's assumed that Oracle "should" sit on a huge
> server whereas SQL Server will do better on a glorified desktop box. I'm
> not sure how to best convince people of the truth... Good luck!
>
> "Geoff Pennington" <Geoffrey.Pennington@.tma.osd.mil.nospam> wrote in
message
> news:eJtzZ%23dZEHA.3092@.tk2msftngp13.phx.gbl...
> > Most of my experience is with what I consider small, low transaction
rate
> > databases. For example, the largest table in one DB has fewer than
100,000
> > rows (other tables have far fewer rows) with anywhere from 1 to 15
people
> > posting queries and updates at any given time. SQL Server handles this
> just
> > fine.
> >
> > We are beginning to design a database for a new project. We haven't
> > determined how big the tables will be but at peak usage will have
perhaps
> > 100 people logged on at a time. There is pressure to use Oracle because
of
> a
> > belief that SQL Server will not be up to the demands.
> >
> > Solid, unbiased comparisons between Oracle and SQL Server are hard to
come
> > by. Does anyone here have experience with large systems that would show
> what
> > SQL Server is really capable of?
> >
> > Much obliged.
> >
> >
>|||"Geoff Pennington" <Geoffrey.Pennington@.tma.osd.mil.nospam> wrote in message
news:%23RVJG9eZEHA.912@.TK2MSFTNGP10.phx.gbl...
> Thanks Adam - Monster.com is a good example. Could you tell me how you
know
> they use SQL Server?
I interviewed there last year for a data architect position.|||There are many larger sites with lots of users that use SQL Server.
Dell.com, CareerBuilder.com, Barnes & Nobles etc...
Andrew J. Kelly SQL MVP
"Geoff Pennington" <Geoffrey.Pennington@.tma.osd.mil.nospam> wrote in message
news:%23RVJG9eZEHA.912@.TK2MSFTNGP10.phx.gbl...
> Thanks Adam - Monster.com is a good example. Could you tell me how you
know
> they use SQL Server?
> I wonder if the reason everyone thinks of SQL Server as being lower end is
> two-fold: (1) it is cheaper to get a SQL license, and (2) Oracle has
always
> been associated with large servers, while MS is associated with the
desktop.
>
> "Adam Machanic" <amachanic@.hotmail._removetoemail_.com> wrote in message
> news:eQZjlOeZEHA.2776@.TK2MSFTNGP10.phx.gbl...
> > Geoff,
> >
> > The best example I can think of is Monster.com. They use SQL Server for
> > their entire jobs database, and get a lot more traffic than 100 people
> > logged on at a time!
> >
> > One of the reasons, IMO, Oracle is often considered to be "more
scalable"
> > than SQL Server is that companies tend to buy cheaper servers to run SQL
> > Server than to run Oracle. For instance, a recent employer of mine
based
> > their entire product on SQL Server and our servers were dual Xeons with
2
> gb
> > of RAM. The servers ran without too many problems, but management would
> > never spend a cent to upgrade them when we did have issues.
> >
> > A consultant was hired for a totally new project and convinced
management
> to
> > take it forward with Oracle. A pair of servers was purchased (QA and
> > Production), each with 8 Itanium processors and 64 gb of RAM. This, for
> an
> > unproven product that was making no money.
> >
> > Why? Because for some reason, due to the way SQL Server is marketed vs.
> the
> > way Oracle is marketed, it's assumed that Oracle "should" sit on a huge
> > server whereas SQL Server will do better on a glorified desktop box.
I'm
> > not sure how to best convince people of the truth... Good luck!
> >
> >
> > "Geoff Pennington" <Geoffrey.Pennington@.tma.osd.mil.nospam> wrote in
> message
> > news:eJtzZ%23dZEHA.3092@.tk2msftngp13.phx.gbl...
> > > Most of my experience is with what I consider small, low transaction
> rate
> > > databases. For example, the largest table in one DB has fewer than
> 100,000
> > > rows (other tables have far fewer rows) with anywhere from 1 to 15
> people
> > > posting queries and updates at any given time. SQL Server handles this
> > just
> > > fine.
> > >
> > > We are beginning to design a database for a new project. We haven't
> > > determined how big the tables will be but at peak usage will have
> perhaps
> > > 100 people logged on at a time. There is pressure to use Oracle
because
> of
> > a
> > > belief that SQL Server will not be up to the demands.
> > >
> > > Solid, unbiased comparisons between Oracle and SQL Server are hard to
> come
> > > by. Does anyone here have experience with large systems that would
show
> > what
> > > SQL Server is really capable of?
> > >
> > > Much obliged.
> > >
> > >
> >
> >
>|||SQL Server is not a punk anymore.
many of the ORACLE Folks still think of SQL Server as SQL Server 6.5.
if you want a Non-Biased opinion check out the TPC site.
www.tpc.org
The results speak for themselves (SQL Server can DEFINATELY Hold it's own AS
Can ORACLE and DB2).
Greg Jackson
PDX, Oregon|||We HAVE had performance issues. HOWEVER every one of them were
"Architectural" in nature.
Cheers
Greg Jackson
PDX, Oregon|||Thanks in particular for the TPC link, the references to companies that are
using SQL Server for large apps, and to everyone for pointing out the
importance of design. I was also able to find some case-study "success
stories" of companies using SQL Server. Now we'll see how the argument
proceeeds on my project...
"Geoff Pennington" <Geoffrey.Pennington@.tma.osd.mil.nospam> wrote in message
news:eJtzZ%23dZEHA.3092@.tk2msftngp13.phx.gbl...
> Most of my experience is with what I consider small, low transaction rate
> databases. For example, the largest table in one DB has fewer than 100,000
> rows (other tables have far fewer rows) with anywhere from 1 to 15 people
> posting queries and updates at any given time. SQL Server handles this
just
> fine.
> We are beginning to design a database for a new project. We haven't
> determined how big the tables will be but at peak usage will have perhaps
> 100 people logged on at a time. There is pressure to use Oracle because of
a
> belief that SQL Server will not be up to the demands.
> Solid, unbiased comparisons between Oracle and SQL Server are hard to come
> by. Does anyone here have experience with large systems that would show
what
> SQL Server is really capable of?
> Much obliged.
>