Creating a database report card

I'm creating a report card for the systems I'm responsible for and have
setup most of the data structures and collection infrastructure I need.
However, there is still the question of exactly what I need to collect.
I will be storing these values for an extended period of time (not sure how
long just yet, probably monthly averages over 5'ish years).
I'm defiantly tracking:
Overall drive space used/free
Database size by .MDF, LDF & .NDF (yes, I'm getting the filenames out of
the engine, not assuming extensions)
Log file usage
Data file usage.
However, I'm also looking at the SQL Server system variables: (I assume
these are for SQL Server only and don't include anything happening in
Windows).
Primary interest:
@.@.CPU_BUSY
@.@.IDLE
@.@.IO_BUSY
Secondary interest:
@.@.TOTAL_READ
@.@.TOTAL_WRITE
@.@.TOTAL_ERRORS
@.@.CONNECTIONS
Tertiary interest:
@.@.PACK_RECEIVED
@.@.PACK_SENT
@.@.PACKET_ERRORS
I know they get reset when SQL Server restarts (and I suppose they rollover
if the server has been up so long it can max the counter - like 5 years, or
something), however, that would produce an easily identifiable situation.
Opinions please.
Thanks,
JayI don't think it's that useful to track these system variables. What
questions are you going to answer with a history of these system variable
values?
You are better off tracking relevant perfmon counters and SQL Server
internal stats such as wait stats and I/O file stats.
Linchi
"Jay" wrote:
> I'm creating a report card for the systems I'm responsible for and have
> setup most of the data structures and collection infrastructure I need.
> However, there is still the question of exactly what I need to collect.
> I will be storing these values for an extended period of time (not sure how
> long just yet, probably monthly averages over 5'ish years).
> I'm defiantly tracking:
> Overall drive space used/free
> Database size by .MDF, LDF & .NDF (yes, I'm getting the filenames out of
> the engine, not assuming extensions)
> Log file usage
> Data file usage.
> However, I'm also looking at the SQL Server system variables: (I assume
> these are for SQL Server only and don't include anything happening in
> Windows).
> Primary interest:
> @.@.CPU_BUSY
> @.@.IDLE
> @.@.IO_BUSY
> Secondary interest:
> @.@.TOTAL_READ
> @.@.TOTAL_WRITE
> @.@.TOTAL_ERRORS
> @.@.CONNECTIONS
> Tertiary interest:
> @.@.PACK_RECEIVED
> @.@.PACK_SENT
> @.@.PACKET_ERRORS
>
> I know they get reset when SQL Server restarts (and I suppose they rollover
> if the server has been up so long it can max the counter - like 5 years, or
> something), however, that would produce an easily identifiable situation.
> Opinions please.
> Thanks,
> Jay
>
>|||Hi Linchi,
Thank you for your response.
>I don't think it's that useful to track these system variables. What
> questions are you going to answer with a history of these system variable
> values?
Average and peak CPU would measure when we we would need more processor
power, I/O would measure the efficiency of the drive subsystem.
> You are better off tracking relevant perfmon counters and SQL Server
> internal stats such as wait stats and I/O file stats.
Ya, that would be more traditional, however, they are more diffucult to get
into the database for long term storage. The @.@. system variables would be a
lot easier. Which is why I'm looking at them.
Jay
> Linchi
> "Jay" wrote:
>> I'm creating a report card for the systems I'm responsible for and have
>> setup most of the data structures and collection infrastructure I need.
>> However, there is still the question of exactly what I need to collect.
>> I will be storing these values for an extended period of time (not sure
>> how
>> long just yet, probably monthly averages over 5'ish years).
>> I'm defiantly tracking:
>> Overall drive space used/free
>> Database size by .MDF, LDF & .NDF (yes, I'm getting the filenames out
>> of
>> the engine, not assuming extensions)
>> Log file usage
>> Data file usage.
>> However, I'm also looking at the SQL Server system variables: (I assume
>> these are for SQL Server only and don't include anything happening in
>> Windows).
>> Primary interest:
>> @.@.CPU_BUSY
>> @.@.IDLE
>> @.@.IO_BUSY
>> Secondary interest:
>> @.@.TOTAL_READ
>> @.@.TOTAL_WRITE
>> @.@.TOTAL_ERRORS
>> @.@.CONNECTIONS
>> Tertiary interest:
>> @.@.PACK_RECEIVED
>> @.@.PACK_SENT
>> @.@.PACKET_ERRORS
>>
>> I know they get reset when SQL Server restarts (and I suppose they
>> rollover
>> if the server has been up so long it can max the counter - like 5 years,
>> or
>> something), however, that would produce an easily identifiable situation.
>> Opinions please.
>> Thanks,
>> Jay
>>

create XML Schema Collection Using UTF-8 in SQL Server 2005

How can I create XML Schema Collection using UTF-8 encoding?

I have an xml schema using UTF-8 encoding. When I using it in the "CREATE XML SCHEMA COLLECTION" statement, I got the following error message:

.Net SqlClient Data Provider: Msg 9402, Level 16, State 1, Line 1

XML parsing: line 1, character 38, unable to switch the encoding

I understand the SQL Server 2005 us UTF-16 for xml data. Does it mean I need to convert the schema to use UTF-16 manually?

The XML schema is being treated as UTF-16, hence the mismatch in the encoding.

You can do one of the following:

1) Send the XML schema as a System.Data.SqlTypes.SqlXml type from the .NET client to the server

2) Send the XML schema as a binary type from the .NET client to the server.

The server's XML parser will use UTF-8 encoding while parsing the XML schema.

A third alternative is to remove the XML declaration, convert the rest of the XML schema into UTF-16 encoding at the client, and send the resulting string to the server.

Hope this helps.

Thank you,

Shankar
Program Manager
Microsoft SQL Server|||

I am having the same issue. Unfortunately I am a DBA and am not familiar with the .NET client. Can you put this solution in a format that I can implement with the SQL Server 2005 tools?

Thanks,

Eileen Torrens

|||

Assuming you have a stored proc parameter @.xsc of type varbinary(max) or type xml, you just say:

create xml schema collection sc as @.xsc

how are you planning on passing the values to the database then?

Best regards

Michael

|||

Hi, there.

I don't know whether this has been resolved or not, but for completeness, here is an answer:

CREATE XML SCHEMA COLLECTION [AnXmlSchemaName] AS
N'<doc />'

Is it right?
ym

create XML Schema Collection Using UTF-8 in SQL Server 2005

How can I create XML Schema Collection using UTF-8 encoding?

I have an xml schema using UTF-8 encoding. When I using it in the "CREATE XML SCHEMA COLLECTION" statement, I got the following error message:

.Net SqlClient Data Provider: Msg 9402, Level 16, State 1, Line 1

XML parsing: line 1, character 38, unable to switch the encoding

I understand the SQL Server 2005 us UTF-16 for xml data. Does it mean I need to convert the schema to use UTF-16 manually?

The XML schema is being treated as UTF-16, hence the mismatch in the encoding.

You can do one of the following:

1) Send the XML schema as a System.Data.SqlTypes.SqlXml type from the .NET client to the server

2) Send the XML schema as a binary type from the .NET client to the server.

The server's XML parser will use UTF-8 encoding while parsing the XML schema.

A third alternative is to remove the XML declaration, convert the rest of the XML schema into UTF-16 encoding at the client, and send the resulting string to the server.

Hope this helps.

Thank you,

Shankar
Program Manager
Microsoft SQL Server|||

I am having the same issue. Unfortunately I am a DBA and am not familiar with the .NET client. Can you put this solution in a format that I can implement with the SQL Server 2005 tools?

Thanks,

Eileen Torrens

|||

Assuming you have a stored proc parameter @.xsc of type varbinary(max) or type xml, you just say:

create xml schema collection sc as @.xsc

how are you planning on passing the values to the database then?

Best regards

Michael

|||

Hi, there.

I don't know whether this has been resolved or not, but for completeness, here is an answer:

CREATE XML SCHEMA COLLECTION [AnXmlSchemaName] AS
N'<doc />'

Is it right?
ym

create XML Schema Collection Using UTF-8 in SQL Server 2005

How can I create XML Schema Collection using UTF-8 encoding?

I have an xml schema using UTF-8 encoding. When I using it in the "CREATE XML SCHEMA COLLECTION" statement, I got the following error message:

.Net SqlClient Data Provider: Msg 9402, Level 16, State 1, Line 1

XML parsing: line 1, character 38, unable to switch the encoding

I understand the SQL Server 2005 us UTF-16 for xml data. Does it mean I need to convert the schema to use UTF-16 manually?

The XML schema is being treated as UTF-16, hence the mismatch in the encoding.

You can do one of the following:

1) Send the XML schema as a System.Data.SqlTypes.SqlXml type from the .NET client to the server

2) Send the XML schema as a binary type from the .NET client to the server.

The server's XML parser will use UTF-8 encoding while parsing the XML schema.

A third alternative is to remove the XML declaration, convert the rest of the XML schema into UTF-16 encoding at the client, and send the resulting string to the server.

Hope this helps.

Thank you,

Shankar
Program Manager
Microsoft SQL Server|||

I am having the same issue. Unfortunately I am a DBA and am not familiar with the .NET client. Can you put this solution in a format that I can implement with the SQL Server 2005 tools?

Thanks,

Eileen Torrens

|||

Assuming you have a stored proc parameter @.xsc of type varbinary(max) or type xml, you just say:

create xml schema collection sc as @.xsc

how are you planning on passing the values to the database then?

Best regards

Michael

|||

Hi, there.

I don't know whether this has been resolved or not, but for completeness, here is an answer:

CREATE XML SCHEMA COLLECTION [AnXmlSchemaName] AS
N'<doc />'

Is it right?
ym

create XML Schema Collection Using UTF-8 in SQL Server 2005

How can I create XML Schema Collection using UTF-8 encoding?

I have an xml schema using UTF-8 encoding. When I using it in the "CREATE XML SCHEMA COLLECTION" statement, I got the following error message:

.Net SqlClient Data Provider: Msg 9402, Level 16, State 1, Line 1

XML parsing: line 1, character 38, unable to switch the encoding

I understand the SQL Server 2005 us UTF-16 for xml data. Does it mean I need to convert the schema to use UTF-16 manually?

The XML schema is being treated as UTF-16, hence the mismatch in the encoding.

You can do one of the following:

1) Send the XML schema as a System.Data.SqlTypes.SqlXml type from the .NET client to the server

2) Send the XML schema as a binary type from the .NET client to the server.

The server's XML parser will use UTF-8 encoding while parsing the XML schema.

A third alternative is to remove the XML declaration, convert the rest of the XML schema into UTF-16 encoding at the client, and send the resulting string to the server.

Hope this helps.

Thank you,

Shankar
Program Manager
Microsoft SQL Server|||

I am having the same issue. Unfortunately I am a DBA and am not familiar with the .NET client. Can you put this solution in a format that I can implement with the SQL Server 2005 tools?

Thanks,

Eileen Torrens

|||

Assuming you have a stored proc parameter @.xsc of type varbinary(max) or type xml, you just say:

create xml schema collection sc as @.xsc

how are you planning on passing the values to the database then?

Best regards

Michael

|||

Hi, there.

I don't know whether this has been resolved or not, but for completeness, here is an answer:

CREATE XML SCHEMA COLLECTION [AnXmlSchemaName] AS
N'<doc />'

Is it right?
ym

CREATE XML SCHEMA COLLECTION ddl statement?

what's the purpose of defining a "relational schema name" in the CREATE XML SCHEMA COLLECTION ddl statement? i don't understand why it's an option.

All objects in SQL Server 2005 database distributed among schemas.

Default schema is dbo. So when you create table "MyTable", its full name dbo.MyTable or Sales.MyTable etc. For more examples, please, see database AdventureWorks.

XML SCHEMA COLLECTION also included into one of database schemas.

|||

Konstantin Kosinsky wrote:

All objects in SQL Server 2005 database distributed among schemas.

Thanks for your reply, but I already knew this. What I previously didn't understand was the purpose of schemas. Anyway, I found the following in the BOL:

"The roles of schemas are enhanced in SQL Server databases. All objects in a database are now located in schemas and are not owned by individual users. Each schema can be owned by roles, allowing multiple users to administer database objects. In earlier versions of SQL Server, users could not be dropped from a database without having to reassign the ownership of each individual object that they owned. Now ownership only has to be adjusted for the schema, not each object." http://msdn2.microsoft.com/en-us/library/ms170800.aspx

Aha! Now it all makes sense to me.