More problems with activation

I have two databases on the same instance.

One is Basket_ODS and the other is Intelligence_ODS. I am using service broker activation on a queue to move data from the Basket_ODS table to the Intelligence_ODS database. Previously I was able to move from table to table in Basket_ODS, however now that I am moving it to another database on the same instance it is no longer working.

If I set my active connection in SQL Management Studio to this user(BrokerUser) and execute the "move" procedure it works. When activated by Service Broker however, it does not. Here is the error message:

2006-05-09 14:47:52.940 spid86s The activated proc [ODS].[ProcessOrderQueue] running on queue Basket_ODS.ODS.Order Process Queue output the following: 'The server principal "BrokerUser" is not able to access the database "Intelligence_ODS" under the current security context.'

I'm sure I missed something becasue it works fine in the same database. BrokerUser has datareader and datawriter in both databases.

Thanks for any help on this matter.

Gary

Activated task run under impersonated security context, similar to using the EXECUTE AS clause. Let me cross-post a reply from Remus:

<remus>
The explanation is detailed in the 'Extending Database Impersonation by Using EXECUTE AS' chapter in BOL (http://msdn2.microsoft.com/en-us/library/ms188304(en-us,VS.90).aspx)

A short explanation is this: when executing under an EXECUTE AS context (as activated procedures always are), the trust is given by the dbo of the database. Therefore, the procedure is trusted only at the level of the database, not at the level of the server. Server level views require server level trust, and you execution context is lacking it. You execution context behaves as if you logged in with [Public] in the server. By marking the database as trustworthy, the dbo of the database is trusted at the server level and you execution context inherits all the permissions you expect at the server level.

Marking the database trustworthy is quite a powerfull step. The dbo of that database can elevate itself to sysadmin, there's no way of preventing it. A more refined approach is to sign the activated procedure with a server level certificate that has proper rights (ADD SIGNATURE).
</remus>

While the above could solve your problem, it may be worthwhile trying to ask yourself, why is the service located in Basket_ODS instead of Intelligence_ODS.

Hope that helps,
Rushi

|||

I actually have a sample showing how to do this: http://blogs.msdn.com/remusrusanu/archive/2006/03/07/545508.aspx

HTH,
~ Remus

|||

Thanks Rushi,

As usual you do a great job with follow up. The activated stored procedure copies data from the Basket_ODS database to the Intelligence_ODS database. If I move the proc to the Basket_ODS database, I ran into the same issue when it tried to copy over.

I did read the information on BOL and your earlier post on this. I guess I'll have to work my way through signing the stored procedure. The trust database option really won't be a player for me I think. I did set up trust tonight and it worked though. I have to do another run in the morning but it was incredibly slower than the copy to the same database.

I'll try to worth through the signing tomorrow. That seems the way to go for me. We are trying to operate in least-trust mode. The DBAs start getting white hair when I ask for things like database trust.

Thank you so much for your help and your contribution to the community.

Gary

|||

Thank's Remus. Wow that's a great sample. I wish I found that earlier today. Thanks again for the great information.

Gary

|||

I created the certificates as noted in your scripts. The user associated with the certificate in my system is BrokerUser2. I do have a question though.

why can't I do this?

ALTER QUEUE ODS.[Order Process Queue] WITH

ACTIVATION (

STATUS = ON,

PROCEDURE_NAME = ODS.ProcessOrderQueue,

MAX_QUEUE_READERS = 4,

EXECUTE AS 'BrokerUser2'

)

when I try this it says BrokerUser2 does not exist or I do not have access to it. I can do an sp_helpuser 'BrokerUser2' and see his rights though.

It did appear to work if I did this:

ALTER QUEUE ODS.[Order Process Queue] WITH

ACTIVATION (

STATUS = ON,

PROCEDURE_NAME = ODS.ProcessOrderQueue,

MAX_QUEUE_READERS = 4, EXECUTE AS OWNER)

I did create the ODS.ProcessOrderQueue stored procedure [With Execute As 'BrokerUser2'].

|||

The user executing the ALTER QUEUE statement must have IMPERSONATE permission over 'BrokerUser2'.

HTH,
~ Remus

More on restoring database in user instance - exclusive use not obtained

Hi all. I have read and implemented the very helpful threads on backing up and restoring with user instance posted with MFriedlander. Thank you. However, during the rs.SqlRestore(srv) command I am getting the following error "Restore failed for Server '\\.\pipe\4A1F91FF-F6FE-45\tsql\query'. "
"Exclusive access could not be obtained because the database is in use."

I have implemented the changedatabase method as described in that thread.

SqlConnection.ChangeDatabase("master")

right before the line

rs.SqlRestore(srv)

I do not use the default instance of sql express, but I do use an instance called 'test' for my app. Should my changedatabase method also refer to my sql instance?

I am running from VS 2005 debug (F5) when it fails and cannot think of anything that would be locking it. Any help would be appreciated. Thank you. Below is the full code of the restore.

Robert

Dim sqlconnection As SqlConnection = New SqlConnection(My.Settings.dbTestConnectionString)

sqlconnection.Open()

MsgBox(sqlconnection.Database.ToString())

Dim ServerConnection As ServerConnection = New ServerConnection(sqlconnection)

Dim srv As Server = New Server(ServerConnection)

'Declare a BackupDeviceItem by supplying the backup device file name in the constructor, and the type of device is a file.

Dim bdi As BackupDeviceItem

bdi = New BackupDeviceItem(txtRestoreFile.Text, DeviceType.File)

''Define a Restore object variable.

Dim rs As New Restore

'Set the NoRecovery property to true, so the transactions are not recovered.

rs.NoRecovery = True

rs.ReplaceDatabase = True

'Add the device that contains the full database backup to the Restore object.

rs.Devices.Add(bdi)

'don't know why the below norecovery is changed to false

rs.NoRecovery = False

'Specify the database name.

rs.Database = sqlconnection.Database.ToString()

sqlconnection.ChangeDatabase("master")

'Restore the full database backup with no recovery.

rs.SqlRestore(srv)

Hi Robert,

I believe the problem is at

Code Snippet

rs.NoRecovery=True

It should be False, but if you read the comments about NoRecovery in BOL you'll see that True leaves the database in the recovering state, which makes it appear that it is in use the next time you try to run your Restore code. I see that you have it set in your code twice, once each way, and I'm not sure of the impact of doing that is. (It seems it would just reset it.) In any case, I'd clean the code so you're only setting NoRecovery once, and you're setting it to False.

I have a clean version of the code I wrote based on the thread you mention available in the blog, find it here. The code is acutally C#, but the SMO part is identical between the two languages once you've defined the objects.

Mike

|||

Hi Mike-

unfortunately changing the norecovery to false did not stop the error. I got it to work by changing the sqlconnection from referencing the

My.Settings.testConnectionString and replaced it with the connection string spelled out. I toggle between the 2 lines of code and the my.settings consistently doesn't work while the fully spelled out connection string works. I don't get it, I got lucky, but it works and I thank you very much for your support. The only time the restore doesn't work is immediately after running the backup code. Restarting the application fixes this and I can find an elegant way to fix this or work around for it, unless you have any ideas. Thanks again. Robert

|||

Hi Robert,

I think there may be a problem with your code...I have successfully implemented SMO.Restore in my app, and I noticed a critical difference between my restore code and yours:

My restore code (partial):

Code Snippet

Dim dbName As String

Dim SqlCon As SqlClient.SqlConnection = New SqlClient.SqlConnection(My.Settings.UserDBConStr)

SqlCon.Open()

dbName = SqlCon.Database.ToString()

SqlCon.ChangeDatabase("master")

Dim SC As New ServerConnection(SqlCon)

Dim srv As New Smo.Server(SC)

'Verify User Instance connection

Console.WriteLine("User Instance Server: " & srv.InstanceName.ToString)

Please notice that I changed the SqlConnection database, SqlCon.ChangeDatabase("master"), before making a connection to the server using SqlCon. I believe that in your code, the SMO.Server is still using the database you are trying to restore because you made the server connection using the restore database.

Here is the rest of my restore code:

Code Snippet

Dim bdi As New Smo.BackupDeviceItem(RestoreDir, Smo.DeviceType.File)

'***Set backup details

Dim resDB As New Smo.Restore

resDB.Devices.Add(bdi)

resDB.NoRecovery = False

resDB.ReplaceDatabase = True

resDB.Database = dbName

'Run SqlBackup to perform the full database backup on the instance of SQL Server.

resDB.SqlRestore(srv)

My code is based on the guidance from Mike Waschal's blog, here

Hope that helps you.

Andre

More on restoring database in user instance - exclusive use not obtained

Hi all. I have read and implemented the very helpful threads on backing up and restoring with user instance posted with MFriedlander. Thank you. However, during the rs.SqlRestore(srv) command I am getting the following error "Restore failed for Server '\\.\pipe\4A1F91FF-F6FE-45\tsql\query'. "
"Exclusive access could not be obtained because the database is in use."

I have implemented the changedatabase method as described in that thread.

SqlConnection.ChangeDatabase("master")

right before the line

rs.SqlRestore(srv)

I do not use the default instance of sql express, but I do use an instance called 'test' for my app. Should my changedatabase method also refer to my sql instance?

I am running from VS 2005 debug (F5) when it fails and cannot think of anything that would be locking it. Any help would be appreciated. Thank you. Below is the full code of the restore.

Robert

Dim sqlconnection As SqlConnection = New SqlConnection(My.Settings.dbTestConnectionString)

sqlconnection.Open()

MsgBox(sqlconnection.Database.ToString())

Dim ServerConnection As ServerConnection = New ServerConnection(sqlconnection)

Dim srv As Server = New Server(ServerConnection)

'Declare a BackupDeviceItem by supplying the backup device file name in the constructor, and the type of device is a file.

Dim bdi As BackupDeviceItem

bdi = New BackupDeviceItem(txtRestoreFile.Text, DeviceType.File)

''Define a Restore object variable.

Dim rs As New Restore

'Set the NoRecovery property to true, so the transactions are not recovered.

rs.NoRecovery = True

rs.ReplaceDatabase = True

'Add the device that contains the full database backup to the Restore object.

rs.Devices.Add(bdi)

'don't know why the below norecovery is changed to false

rs.NoRecovery = False

'Specify the database name.

rs.Database = sqlconnection.Database.ToString()

sqlconnection.ChangeDatabase("master")

'Restore the full database backup with no recovery.

rs.SqlRestore(srv)

Hi Robert,

I believe the problem is at

Code Snippet

rs.NoRecovery=True

It should be False, but if you read the comments about NoRecovery in BOL you'll see that True leaves the database in the recovering state, which makes it appear that it is in use the next time you try to run your Restore code. I see that you have it set in your code twice, once each way, and I'm not sure of the impact of doing that is. (It seems it would just reset it.) In any case, I'd clean the code so you're only setting NoRecovery once, and you're setting it to False.

I have a clean version of the code I wrote based on the thread you mention available in the blog, find it here. The code is acutally C#, but the SMO part is identical between the two languages once you've defined the objects.

Mike

|||

Hi Mike-

unfortunately changing the norecovery to false did not stop the error. I got it to work by changing the sqlconnection from referencing the

My.Settings.testConnectionString and replaced it with the connection string spelled out. I toggle between the 2 lines of code and the my.settings consistently doesn't work while the fully spelled out connection string works. I don't get it, I got lucky, but it works and I thank you very much for your support. The only time the restore doesn't work is immediately after running the backup code. Restarting the application fixes this and I can find an elegant way to fix this or work around for it, unless you have any ideas. Thanks again. Robert

|||

Hi Robert,

I think there may be a problem with your code...I have successfully implemented SMO.Restore in my app, and I noticed a critical difference between my restore code and yours:

My restore code (partial):

Code Snippet

Dim dbName As String

Dim SqlCon As SqlClient.SqlConnection = New SqlClient.SqlConnection(My.Settings.UserDBConStr)

SqlCon.Open()

dbName = SqlCon.Database.ToString()

SqlCon.ChangeDatabase("master")

Dim SC As New ServerConnection(SqlCon)

Dim srv As New Smo.Server(SC)

'Verify User Instance connection

Console.WriteLine("User Instance Server: " & srv.InstanceName.ToString)

Please notice that I changed the SqlConnection database, SqlCon.ChangeDatabase("master"), before making a connection to the server using SqlCon. I believe that in your code, the SMO.Server is still using the database you are trying to restore because you made the server connection using the restore database.

Here is the rest of my restore code:

Code Snippet

Dim bdi As New Smo.BackupDeviceItem(RestoreDir, Smo.DeviceType.File)

'***Set backup details

Dim resDB As New Smo.Restore

resDB.Devices.Add(bdi)

resDB.NoRecovery = False

resDB.ReplaceDatabase = True

resDB.Database = dbName

'Run SqlBackup to perform the full database backup on the instance of SQL Server.

resDB.SqlRestore(srv)

My code is based on the guidance from Mike Waschal's blog, here

Hope that helps you.

Andre

More Connect Issues

I have an instance of SQL Server 2005 on two different development machines.
At first we could not connect to Machine A until we read about how it comes
locked down. We went to SQL Server Config Mgr and enabled all necessary
protocols and can connect just fine to it. When we did the same to machine
B, nothing happened. Our client apps just hang. The server is ping-able
but sql test connections just fail. Any thoughts what or where I could look
at next?
Regards.
Xref: TK2MSFTNGP08.phx.gbl microsoft.public.sqlserver.connect:47973
I'm sorry this is the error message I'm receiving:
TITLE: New Server Registration
Testing the registered server failed. Verify the server name, login
credentials, and database, and then click Test again.
ADDITIONAL INFORMATION:
An error has occurred while establishing a connection to the server. When
connecting to SQL Server 2005, this failure may be caused by the fact that
under the default settings SQL Server does not allow remote connections.
(provider: SQL Network Interfaces, error: 26 - Error Locating
Server/Instance Specified) (Microsoft SQL Server, Error: -1)
For help, click:
http://go.microsoft.com/fwlink?ProdN...1&LinkId=20476
BUTTONS:
OK
"Trex" <randytrexler@.hotmail.com> wrote in message
news:1Xdvf.3139$%W1.71@.newsread2.news.atl.earthlin k.net...
>I have an instance of SQL Server 2005 on two different development
>machines. At first we could not connect to Machine A until we read about
>how it comes locked down. We went to SQL Server Config Mgr and enabled all
>necessary protocols and can connect just fine to it. When we did the same
>to machine B, nothing happened. Our client apps just hang. The server is
>ping-able but sql test connections just fail. Any thoughts what or where I
>could look at next?
> Regards.
>
|||Did you enable the "Allow Remote Connections" on that server?
Andrew J. Kelly SQL MVP
"Trex" <randytrexler@.hotmail.com> wrote in message
news:KZdvf.3140$%W1.115@.newsread2.news.atl.earthli nk.net...
> I'm sorry this is the error message I'm receiving:
> TITLE: New Server Registration
> --
> Testing the registered server failed. Verify the server name, login
> credentials, and database, and then click Test again.
> --
> ADDITIONAL INFORMATION:
> An error has occurred while establishing a connection to the server. When
> connecting to SQL Server 2005, this failure may be caused by the fact that
> under the default settings SQL Server does not allow remote connections.
> (provider: SQL Network Interfaces, error: 26 - Error Locating
> Server/Instance Specified) (Microsoft SQL Server, Error: -1)
> For help, click:
> http://go.microsoft.com/fwlink?ProdN...1&LinkId=20476
> --
> BUTTONS:
> OK
> --
>
> "Trex" <randytrexler@.hotmail.com> wrote in message
> news:1Xdvf.3139$%W1.71@.newsread2.news.atl.earthlin k.net...
>

More Connect Issues

I have an instance of SQL Server 2005 on two different development machines.
At first we could not connect to Machine A until we read about how it comes
locked down. We went to SQL Server Config Mgr and enabled all necessary
protocols and can connect just fine to it. When we did the same to machine
B, nothing happened. Our client apps just hang. The server is ping-able
but sql test connections just fail. Any thoughts what or where I could look
at next?
Regards.Xref: TK2MSFTNGP08.phx.gbl microsoft.public.sqlserver.connect:47973
I'm sorry this is the error message I'm receiving:
TITLE: New Server Registration
--
Testing the registered server failed. Verify the server name, login
credentials, and database, and then click Test again.
ADDITIONAL INFORMATION:
An error has occurred while establishing a connection to the server. When
connecting to SQL Server 2005, this failure may be caused by the fact that
under the default settings SQL Server does not allow remote connections.
(provider: SQL Network Interfaces, error: 26 - Error Locating
Server/Instance Specified) (Microsoft SQL Server, Error: -1)
For help, click:
http://go.microsoft.com/fwlink?Prod...-1&LinkId=20476
BUTTONS:
OK
--
"Trex" <randytrexler@.hotmail.com> wrote in message
news:1Xdvf.3139$%W1.71@.newsread2.news.atl.earthlink.net...
>I have an instance of SQL Server 2005 on two different development
>machines. At first we could not connect to Machine A until we read about
>how it comes locked down. We went to SQL Server Config Mgr and enabled all
>necessary protocols and can connect just fine to it. When we did the same
>to machine B, nothing happened. Our client apps just hang. The server is
>ping-able but sql test connections just fail. Any thoughts what or where I
>could look at next?
> Regards.
>|||Did you enable the "Allow Remote Connections" on that server?
Andrew J. Kelly SQL MVP
"Trex" <randytrexler@.hotmail.com> wrote in message
news:KZdvf.3140$%W1.115@.newsread2.news.atl.earthlink.net...
> I'm sorry this is the error message I'm receiving:
> TITLE: New Server Registration
> --
> Testing the registered server failed. Verify the server name, login
> credentials, and database, and then click Test again.
> --
> ADDITIONAL INFORMATION:
> An error has occurred while establishing a connection to the server. When
> connecting to SQL Server 2005, this failure may be caused by the fact that
> under the default settings SQL Server does not allow remote connections.
> (provider: SQL Network Interfaces, error: 26 - Error Locating
> Server/Instance Specified) (Microsoft SQL Server, Error: -1)
> For help, click:
> http://go.microsoft.com/fwlink?Prod...-1&LinkId=20476
> --
> BUTTONS:
> OK
> --
>
> "Trex" <randytrexler@.hotmail.com> wrote in message
> news:1Xdvf.3139$%W1.71@.newsread2.news.atl.earthlink.net...
>

monitoring sql server box

I would like to find out if there are any free tools that could be used to
monitor a sql instance and list the
a. most frequently run stored procedures and how long each takes
b. most frequently run views and how long each takes
netiq used to have app manager - is that still available ?
also, which free tool can I use to monitor CPU, Memory and Netwok
Utilization for a windows box running SQL Server 2000 ?
Thanks"John Smith" <bukusu@.gmail.com> wrote in message
news:epVak7yuGHA.4756@.TK2MSFTNGP04.phx.gbl...
> I would like to find out if there are any free tools that could be used to
> monitor a sql instance and list the
> a. most frequently run stored procedures and how long each takes
> b. most frequently run views and how long each takes
> netiq used to have app manager - is that still available ?
> also, which free tool can I use to monitor CPU, Memory and Netwok
> Utilization for a windows box running SQL Server 2000 ?
> Thanks
>
Not sure about a. & b. but Windows Performance monitor (under Administrative
Tools) can monitor the rest, plus a lot of SQL specific items, too. You can
even monitor remotely.|||Hi John
You could use SQL profiler for a but I would not continually sample. You can
not "run" a view so b is not possible. You may want to look at
http://www.sqldbatips.com/showcode.asp?ID=7.
John
"John Smith" wrote:
> I would like to find out if there are any free tools that could be used to
> monitor a sql instance and list the
> a. most frequently run stored procedures and how long each takes
> b. most frequently run views and how long each takes
> netiq used to have app manager - is that still available ?
> also, which free tool can I use to monitor CPU, Memory and Netwok
> Utilization for a windows box running SQL Server 2000 ?
> Thanks
>
>|||John Smith wrote:
> I would like to find out if there are any free tools that could be used to
> monitor a sql instance and list the
> a. most frequently run stored procedures and how long each takes
> b. most frequently run views and how long each takes
> netiq used to have app manager - is that still available ?
> also, which free tool can I use to monitor CPU, Memory and Netwok
> Utilization for a windows box running SQL Server 2000 ?
> Thanks
>
You can capture stored procedure activity using Profiler, and then
analyze the resulting logs. There is a tool available for download here
(http://www.cleardata.biz/cleartrace/instructions.aspx) that will neatly
summarize the log activity, or you can do the same thing yourself, as we do.
Perfmon is your best bet for monitoring CPU, memory, and network
utilization.
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||NetIQ sold th app manager for SQL off to Idera and it is now their SQLdm
product. I should say that the Idera SQMdm product started as the NetIQ App
Manager for SQL. They have spent some time and money upgrading and
updating it since purchasing the software.
www.idera.com if you want to check it out.
--
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP
"John Smith" <bukusu@.gmail.com> wrote in message
news:epVak7yuGHA.4756@.TK2MSFTNGP04.phx.gbl...
>I would like to find out if there are any free tools that could be used to
>monitor a sql instance and list the
> a. most frequently run stored procedures and how long each takes
> b. most frequently run views and how long each takes
> netiq used to have app manager - is that still available ?
> also, which free tool can I use to monitor CPU, Memory and Netwok
> Utilization for a windows box running SQL Server 2000 ?
> Thanks
>

monitoring sql server box

I would like to find out if there are any free tools that could be used to
monitor a sql instance and list the
a. most frequently run stored procedures and how long each takes
b. most frequently run views and how long each takes
netiq used to have app manager - is that still available ?
also, which free tool can I use to monitor CPU, Memory and Netwok
Utilization for a windows box running SQL Server 2000 ?
Thanks"John Smith" <bukusu@.gmail.com> wrote in message
news:epVak7yuGHA.4756@.TK2MSFTNGP04.phx.gbl...
> I would like to find out if there are any free tools that could be used to
> monitor a sql instance and list the
> a. most frequently run stored procedures and how long each takes
> b. most frequently run views and how long each takes
> netiq used to have app manager - is that still available ?
> also, which free tool can I use to monitor CPU, Memory and Netwok
> Utilization for a windows box running SQL Server 2000 ?
> Thanks
>
Not sure about a. & b. but Windows Performance monitor (under Administrative
Tools) can monitor the rest, plus a lot of SQL specific items, too. You can
even monitor remotely.|||Hi John
You could use SQL profiler for a but I would not continually sample. You can
not "run" a view so b is not possible. You may want to look at
http://www.sqldbatips.com/showcode.asp?ID=7.
John
"John Smith" wrote:

> I would like to find out if there are any free tools that could be used to
> monitor a sql instance and list the
> a. most frequently run stored procedures and how long each takes
> b. most frequently run views and how long each takes
> netiq used to have app manager - is that still available ?
> also, which free tool can I use to monitor CPU, Memory and Netwok
> Utilization for a windows box running SQL Server 2000 ?
> Thanks
>
>|||John Smith wrote:
> I would like to find out if there are any free tools that could be used to
> monitor a sql instance and list the
> a. most frequently run stored procedures and how long each takes
> b. most frequently run views and how long each takes
> netiq used to have app manager - is that still available ?
> also, which free tool can I use to monitor CPU, Memory and Netwok
> Utilization for a windows box running SQL Server 2000 ?
> Thanks
>
You can capture stored procedure activity using Profiler, and then
analyze the resulting logs. There is a tool available for download here
(http://www.cleardata.biz/cleartrace/instructions.aspx) that will neatly
summarize the log activity, or you can do the same thing yourself, as we do.
Perfmon is your best bet for monitoring CPU, memory, and network
utilization.
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||NetIQ sold th app manager for SQL off to Idera and it is now their SQLdm
product. I should say that the Idera SQMdm product started as the NetIQ App
Manager for SQL. They have spent some time and money upgrading and
updating it since purchasing the software.
www.idera.com if you want to check it out.
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP
"John Smith" <bukusu@.gmail.com> wrote in message
news:epVak7yuGHA.4756@.TK2MSFTNGP04.phx.gbl...
>I would like to find out if there are any free tools that could be used to
>monitor a sql instance and list the
> a. most frequently run stored procedures and how long each takes
> b. most frequently run views and how long each takes
> netiq used to have app manager - is that still available ?
> also, which free tool can I use to monitor CPU, Memory and Netwok
> Utilization for a windows box running SQL Server 2000 ?
> Thanks
>

Monitoring SQL Compact Edition Performance

Does SQL Compact Edition expose performance counters to tools like Perfmon as SQL Server does? For instance, can you view lock wait times, cache hit ratio, etc.?

Currently, SQL Compact does not expose any performance counters. It is a in-process database engine, not a service/server process like SQL Server. You can analyze query execution with the query analyzer in SQL Server Management Studio (Express SP2).

Monitoring changes in any table in an instance

Is there a way to monitor changes in any tables of an instance? I have a
database driven application that I want to reverse engineer. I want to find
out if a function would perform changes to which tables in the instance. Is
there a realistic way to do this? Thank you.
Lito Kusnadi
Lito
CREATE TABLE AuditDDLEvents
(
LSN INT NOT NULL IDENTITY,
posttime DATETIME NOT NULL,
eventtype SYSNAME NOT NULL,
loginname SYSNAME NOT NULL,
schemaname SYSNAME NOT NULL,
objectname SYSNAME NOT NULL,
targetobjectname SYSNAME NOT NULL,
eventdata XML NOT NULL,
CONSTRAINT PK_AuditDDLEvents PRIMARY KEY(LSN)
)
GO
CREATE TRIGGER trg_audit_ddl_events ON DATABASE FOR
DDL_DATABASE_LEVEL_EVENTS
AS
DECLARE @.eventdata AS XML
SET @.eventdata = eventdata()
INSERT INTO dbo.AuditDDLEvents(
posttime, eventtype, loginname, schemaname,
objectname, targetobjectname, eventdata)
VALUES(
CAST(@.eventdata.query('data(//PostTime)') AS VARCHAR(23)),
CAST(@.eventdata.query('data(//EventType)') AS SYSNAME),
CAST(@.eventdata.query('data(//LoginName)') AS SYSNAME),
CAST(@.eventdata.query('data(//SchemaName)') AS SYSNAME),
CAST(@.eventdata.query('data(//ObjectName)') AS SYSNAME),
CAST(@.eventdata.query('data(//TargetObjectName)') AS SYSNAME),
@.eventdata)
GO
The trigger simply extracts all event attributes
of interest from the eventdata() function using XQuery,
and inserts those into the AuditDDLEvents table. To test the trigger,
submit a few DDL statements and query the audit table:
CREATE TABLE T1(col1 INT NOT NULL PRIMARY KEY)
ALTER TABLE T1 ADD col2 INT NULL
ALTER TABLE T1 ALTER COLUMN col2 INT NOT NULL
CREATE NONCLUSTERED INDEX idx1 ON T1(col2)
SELECT * FROM AuditDDLEvents
SELECT posttime, eventtype, loginname,
CAST(eventdata.query('data(//TSQLCommand)') AS NVARCHAR(2000))
AS tsqlcommand
FROM dbo.AuditDDLEvents
WHERE schemaname = N'dbo' AND N'T1' IN(objectname, targetobjectname)
ORDER BY posttime
"Lito Kusnadi" <LitoKusnadi@.discussions.microsoft.com> wrote in message
news:E78250B3-C634-4ADA-9262-24C85C4EFC92@.microsoft.com...
> Is there a way to monitor changes in any tables of an instance? I have a
> database driven application that I want to reverse engineer. I want to
> find
> out if a function would perform changes to which tables in the instance.
> Is
> there a realistic way to do this? Thank you.
> --
> Lito Kusnadi
>
|||In addition to Uri's recommendation you may want to take a look at the
information available in SQL Server 2005 default trace.
Take a look at this report in Management Studio, Reports - Standard Reports
- Schema Changes History.
Also see the path of the trace file by running
select * from sys.traces
where the default trace is usually id 1.
Hope this helps,
Ben Nevarez
"Uri Dimant" wrote:

> Lito
> CREATE TABLE AuditDDLEvents
> (
> LSN INT NOT NULL IDENTITY,
> posttime DATETIME NOT NULL,
> eventtype SYSNAME NOT NULL,
> loginname SYSNAME NOT NULL,
> schemaname SYSNAME NOT NULL,
> objectname SYSNAME NOT NULL,
> targetobjectname SYSNAME NOT NULL,
> eventdata XML NOT NULL,
> CONSTRAINT PK_AuditDDLEvents PRIMARY KEY(LSN)
> )
> GO
> CREATE TRIGGER trg_audit_ddl_events ON DATABASE FOR
> DDL_DATABASE_LEVEL_EVENTS
> AS
> DECLARE @.eventdata AS XML
> SET @.eventdata = eventdata()
> INSERT INTO dbo.AuditDDLEvents(
> posttime, eventtype, loginname, schemaname,
> objectname, targetobjectname, eventdata)
> VALUES(
> CAST(@.eventdata.query('data(//PostTime)') AS VARCHAR(23)),
> CAST(@.eventdata.query('data(//EventType)') AS SYSNAME),
> CAST(@.eventdata.query('data(//LoginName)') AS SYSNAME),
> CAST(@.eventdata.query('data(//SchemaName)') AS SYSNAME),
> CAST(@.eventdata.query('data(//ObjectName)') AS SYSNAME),
> CAST(@.eventdata.query('data(//TargetObjectName)') AS SYSNAME),
> @.eventdata)
> GO
> The trigger simply extracts all event attributes
> of interest from the eventdata() function using XQuery,
> and inserts those into the AuditDDLEvents table. To test the trigger,
> submit a few DDL statements and query the audit table:
>
> CREATE TABLE T1(col1 INT NOT NULL PRIMARY KEY)
> ALTER TABLE T1 ADD col2 INT NULL
> ALTER TABLE T1 ALTER COLUMN col2 INT NOT NULL
> CREATE NONCLUSTERED INDEX idx1 ON T1(col2)
> SELECT * FROM AuditDDLEvents
> SELECT posttime, eventtype, loginname,
> CAST(eventdata.query('data(//TSQLCommand)') AS NVARCHAR(2000))
> AS tsqlcommand
> FROM dbo.AuditDDLEvents
> WHERE schemaname = N'dbo' AND N'T1' IN(objectname, targetobjectname)
> ORDER BY posttime
>
> "Lito Kusnadi" <LitoKusnadi@.discussions.microsoft.com> wrote in message
> news:E78250B3-C634-4ADA-9262-24C85C4EFC92@.microsoft.com...
>
>

Monitoring changes in any table in an instance

Is there a way to monitor changes in any tables of an instance? I have a
database driven application that I want to reverse engineer. I want to find
out if a function would perform changes to which tables in the instance. Is
there a realistic way to do this? Thank you.
--
Lito KusnadiLito
CREATE TABLE AuditDDLEvents
(
LSN INT NOT NULL IDENTITY,
posttime DATETIME NOT NULL,
eventtype SYSNAME NOT NULL,
loginname SYSNAME NOT NULL,
schemaname SYSNAME NOT NULL,
objectname SYSNAME NOT NULL,
targetobjectname SYSNAME NOT NULL,
eventdata XML NOT NULL,
CONSTRAINT PK_AuditDDLEvents PRIMARY KEY(LSN)
)
GO
CREATE TRIGGER trg_audit_ddl_events ON DATABASE FOR
DDL_DATABASE_LEVEL_EVENTS
AS
DECLARE @.eventdata AS XML
SET @.eventdata = eventdata()
INSERT INTO dbo.AuditDDLEvents(
posttime, eventtype, loginname, schemaname,
objectname, targetobjectname, eventdata)
VALUES(
CAST(@.eventdata.query('data(//PostTime)') AS VARCHAR(23)),
CAST(@.eventdata.query('data(//EventType)') AS SYSNAME),
CAST(@.eventdata.query('data(//LoginName)') AS SYSNAME),
CAST(@.eventdata.query('data(//SchemaName)') AS SYSNAME),
CAST(@.eventdata.query('data(//ObjectName)') AS SYSNAME),
CAST(@.eventdata.query('data(//TargetObjectName)') AS SYSNAME),
@.eventdata)
GO
The trigger simply extracts all event attributes
of interest from the eventdata() function using XQuery,
and inserts those into the AuditDDLEvents table. To test the trigger,
submit a few DDL statements and query the audit table:
CREATE TABLE T1(col1 INT NOT NULL PRIMARY KEY)
ALTER TABLE T1 ADD col2 INT NULL
ALTER TABLE T1 ALTER COLUMN col2 INT NOT NULL
CREATE NONCLUSTERED INDEX idx1 ON T1(col2)
SELECT * FROM AuditDDLEvents
SELECT posttime, eventtype, loginname,
CAST(eventdata.query('data(//TSQLCommand)') AS NVARCHAR(2000))
AS tsqlcommand
FROM dbo.AuditDDLEvents
WHERE schemaname = N'dbo' AND N'T1' IN(objectname, targetobjectname)
ORDER BY posttime
"Lito Kusnadi" <LitoKusnadi@.discussions.microsoft.com> wrote in message
news:E78250B3-C634-4ADA-9262-24C85C4EFC92@.microsoft.com...
> Is there a way to monitor changes in any tables of an instance? I have a
> database driven application that I want to reverse engineer. I want to
> find
> out if a function would perform changes to which tables in the instance.
> Is
> there a realistic way to do this? Thank you.
> --
> Lito Kusnadi
>|||In addition to Uri's recommendation you may want to take a look at the
information available in SQL Server 2005 default trace.
Take a look at this report in Management Studio, Reports - Standard Reports
- Schema Changes History.
Also see the path of the trace file by running
select * from sys.traces
where the default trace is usually id 1.
Hope this helps,
Ben Nevarez
"Uri Dimant" wrote:
> Lito
> CREATE TABLE AuditDDLEvents
> (
> LSN INT NOT NULL IDENTITY,
> posttime DATETIME NOT NULL,
> eventtype SYSNAME NOT NULL,
> loginname SYSNAME NOT NULL,
> schemaname SYSNAME NOT NULL,
> objectname SYSNAME NOT NULL,
> targetobjectname SYSNAME NOT NULL,
> eventdata XML NOT NULL,
> CONSTRAINT PK_AuditDDLEvents PRIMARY KEY(LSN)
> )
> GO
> CREATE TRIGGER trg_audit_ddl_events ON DATABASE FOR
> DDL_DATABASE_LEVEL_EVENTS
> AS
> DECLARE @.eventdata AS XML
> SET @.eventdata = eventdata()
> INSERT INTO dbo.AuditDDLEvents(
> posttime, eventtype, loginname, schemaname,
> objectname, targetobjectname, eventdata)
> VALUES(
> CAST(@.eventdata.query('data(//PostTime)') AS VARCHAR(23)),
> CAST(@.eventdata.query('data(//EventType)') AS SYSNAME),
> CAST(@.eventdata.query('data(//LoginName)') AS SYSNAME),
> CAST(@.eventdata.query('data(//SchemaName)') AS SYSNAME),
> CAST(@.eventdata.query('data(//ObjectName)') AS SYSNAME),
> CAST(@.eventdata.query('data(//TargetObjectName)') AS SYSNAME),
> @.eventdata)
> GO
> The trigger simply extracts all event attributes
> of interest from the eventdata() function using XQuery,
> and inserts those into the AuditDDLEvents table. To test the trigger,
> submit a few DDL statements and query the audit table:
>
> CREATE TABLE T1(col1 INT NOT NULL PRIMARY KEY)
> ALTER TABLE T1 ADD col2 INT NULL
> ALTER TABLE T1 ALTER COLUMN col2 INT NOT NULL
> CREATE NONCLUSTERED INDEX idx1 ON T1(col2)
> SELECT * FROM AuditDDLEvents
> SELECT posttime, eventtype, loginname,
> CAST(eventdata.query('data(//TSQLCommand)') AS NVARCHAR(2000))
> AS tsqlcommand
> FROM dbo.AuditDDLEvents
> WHERE schemaname = N'dbo' AND N'T1' IN(objectname, targetobjectname)
> ORDER BY posttime
>
> "Lito Kusnadi" <LitoKusnadi@.discussions.microsoft.com> wrote in message
> news:E78250B3-C634-4ADA-9262-24C85C4EFC92@.microsoft.com...
> > Is there a way to monitor changes in any tables of an instance? I have a
> > database driven application that I want to reverse engineer. I want to
> > find
> > out if a function would perform changes to which tables in the instance.
> > Is
> > there a realistic way to do this? Thank you.
> >
> > --
> > Lito Kusnadi
> >
>
>

monitoring application performence

hi all
I'm running a sql 2000 standard edition server on win 2000 server.
one instance on a 3 gb memory, 2 cpus ,machine.
there are about 20 databases on the server; however I'm encounering a
problem with a spacific 500 mb db.
the problem is: 7 client stations located at a different city communicating
(only with)the problematic db on the server threw a single application via
iis. for the last few months the respons time is queit slow. tables are
reletivly small (the biggest is 60000 rows but the rest a only a few hunderd
each)
now... I'm trying yo isolate the problem.
my first question: is it true to say that since the rest of the applications
that are running against the other dbs are running smoothly the problem is
not at the server lelvel or at the network level?
also, opening the task mamager on the server machine I see that the cpu isnt
realy busy and that there is no paging.
I captured the a application queries with the profiler and I saw that the
cpu/ read/writes times are low.
there is no fragmentation on the indexes.
I also run the index tunning wizard with the profiler file. it indicated a
44% improvment and suggested 5 new indexes; but I still belive the problem
lays somewhere else.
and, the system guy, who is monitoring the network traffic, tells me it's
not the network as the bendwidth is 50% free.
so... where should I aim at next?
thanks,
alon.If the rest of the databases are doing fine except this one, then yes, the
problem is at the database level not at the server level.
You said, Index Tuning Wizard suggested new indexes, and the predicted
performance gain is substantial. I think you should go for it, after
testing.
To start with identify which stored procedures are running slow. Use
Profiler to identify the long running stored procedures (look at the
duration column, time is in milliseconds). once you identified the stored
procedures, try running them individually from Query Analyzer, with
'graphical execution plan' turned on. If you see index or table scans, then
the query is not written well, or there are no supporting indexes for that
query. Then try applying the indexes suggested by Index Tuning Wizard and
see if your query plan and response times improve.
Again, simply keep adding indexes is not really good for an OLTP system. if
you are trying to run report-like queries on an OLTP system, then you might
want to replicate the data to a different database, and run your queries
there. You could use transactional replication for this purpose. With
transactional replication the subscriber is only a few seconds behind the
publisher, in general.
Using Profiler, if you do find that the stored procedures and queries are
indeed running fine, the bottle neck is either the network or the
application.
Also check that there's no blockig going on in your database. You could use
sp_who or sp_who2 to see if any of the spids are getting blocked. Blocked
spid have a non-zero value in the 'blocked' column.
--
HTH,
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"alon" <alon@.discussions.microsoft.com> wrote in message
news:6467A9F4-A2C8-4EB8-9C2D-60286D564E6C@.microsoft.com...
> hi all
> I'm running a sql 2000 standard edition server on win 2000 server.
> one instance on a 3 gb memory, 2 cpus ,machine.
> there are about 20 databases on the server; however I'm encounering a
> problem with a spacific 500 mb db.
> the problem is: 7 client stations located at a different city
> communicating
> (only with)the problematic db on the server threw a single application
> via
> iis. for the last few months the respons time is queit slow. tables are
> reletivly small (the biggest is 60000 rows but the rest a only a few
> hunderd
> each)
> now... I'm trying yo isolate the problem.
> my first question: is it true to say that since the rest of the
> applications
> that are running against the other dbs are running smoothly the problem is
> not at the server lelvel or at the network level?
> also, opening the task mamager on the server machine I see that the cpu
> isnt
> realy busy and that there is no paging.
> I captured the a application queries with the profiler and I saw that the
> cpu/ read/writes times are low.
> there is no fragmentation on the indexes.
> I also run the index tunning wizard with the profiler file. it indicated a
> 44% improvment and suggested 5 new indexes; but I still belive the problem
> lays somewhere else.
> and, the system guy, who is monitoring the network traffic, tells me it's
> not the network as the bendwidth is 50% free.
> so... where should I aim at next?
> thanks,
> alon.
>

monitoring application performence

hi all
I'm running a sql 2000 standard edition server on win 2000 server.
one instance on a 3 gb memory, 2 cpus ,machine.
there are about 20 databases on the server; however I'm encounering a
problem with a spacific 500 mb db.
the problem is: 7 client stations located at a different city communicating
(only with)the problematic db on the server threw a single application via
iis. for the last few months the respons time is queit slow. tables are
reletivly small (the biggest is 60000 rows but the rest a only a few hunderd
each)
now... I'm trying yo isolate the problem.
my first question: is it true to say that since the rest of the applications
that are running against the other dbs are running smoothly the problem is
not at the server lelvel or at the network level?
also, opening the task mamager on the server machine I see that the cpu isnt
realy busy and that there is no paging.
I captured the a application queries with the profiler and I saw that the
cpu/ read/writes times are low.
there is no fragmentation on the indexes.
I also run the index tunning wizard with the profiler file. it indicated a
44% improvment and suggested 5 new indexes; but I still belive the problem
lays somewhere else.
and, the system guy, who is monitoring the network traffic, tells me it's
not the network as the bendwidth is 50% free.
so... where should I aim at next?
thanks,
alon.If the rest of the databases are doing fine except this one, then yes, the
problem is at the database level not at the server level.
You said, Index Tuning Wizard suggested new indexes, and the predicted
performance gain is substantial. I think you should go for it, after
testing.
To start with identify which stored procedures are running slow. Use
Profiler to identify the long running stored procedures (look at the
duration column, time is in milliseconds). once you identified the stored
procedures, try running them individually from Query Analyzer, with
'graphical execution plan' turned on. If you see index or table scans, then
the query is not written well, or there are no supporting indexes for that
query. Then try applying the indexes suggested by Index Tuning Wizard and
see if your query plan and response times improve.
Again, simply keep adding indexes is not really good for an OLTP system. if
you are trying to run report-like queries on an OLTP system, then you might
want to replicate the data to a different database, and run your queries
there. You could use transactional replication for this purpose. With
transactional replication the subscriber is only a few seconds behind the
publisher, in general.
Using Profiler, if you do find that the stored procedures and queries are
indeed running fine, the bottle neck is either the network or the
application.
Also check that there's no blockig going on in your database. You could use
sp_who or sp_who2 to see if any of the spids are getting blocked. Blocked
spid have a non-zero value in the 'blocked' column.
--
HTH,
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"alon" <alon@.discussions.microsoft.com> wrote in message
news:6467A9F4-A2C8-4EB8-9C2D-60286D564E6C@.microsoft.com...
> hi all
> I'm running a sql 2000 standard edition server on win 2000 server.
> one instance on a 3 gb memory, 2 cpus ,machine.
> there are about 20 databases on the server; however I'm encounering a
> problem with a spacific 500 mb db.
> the problem is: 7 client stations located at a different city
> communicating
> (only with)the problematic db on the server threw a single application
> via
> iis. for the last few months the respons time is queit slow. tables are
> reletivly small (the biggest is 60000 rows but the rest a only a few
> hunderd
> each)
> now... I'm trying yo isolate the problem.
> my first question: is it true to say that since the rest of the
> applications
> that are running against the other dbs are running smoothly the problem is
> not at the server lelvel or at the network level?
> also, opening the task mamager on the server machine I see that the cpu
> isnt
> realy busy and that there is no paging.
> I captured the a application queries with the profiler and I saw that the
> cpu/ read/writes times are low.
> there is no fragmentation on the indexes.
> I also run the index tunning wizard with the profiler file. it indicated a
> 44% improvment and suggested 5 new indexes; but I still belive the problem
> lays somewhere else.
> and, the system guy, who is monitoring the network traffic, tells me it's
> not the network as the bendwidth is 50% free.
> so... where should I aim at next?
> thanks,
> alon.
>

monitoring application performence

hi all
I'm running a sql 2000 standard edition server on win 2000 server.
one instance on a 3 gb memory, 2 cpus ,machine.
there are about 20 databases on the server; however I'm encounering a
problem with a spacific 500 mb db.
the problem is: 7 client stations located at a different city communicating
(only with)the problematic db on the server threw a single application via
iis. for the last few months the respons time is queit slow. tables are
reletivly small (the biggest is 60000 rows but the rest a only a few hunderd
each)
now... I'm trying yo isolate the problem.
my first question: is it true to say that since the rest of the applications
that are running against the other dbs are running smoothly the problem is
not at the server lelvel or at the network level?
also, opening the task mamager on the server machine I see that the cpu isnt
realy busy and that there is no paging.
I captured the a application queries with the profiler and I saw that the
cpu/ read/writes times are low.
there is no fragmentation on the indexes.
I also run the index tunning wizard with the profiler file. it indicated a
44% improvment and suggested 5 new indexes; but I still belive the problem
lays somewhere else.
and, the system guy, who is monitoring the network traffic, tells me it's
not the network as the bendwidth is 50% free.
so... where should I aim at next?
thanks,
alon.
If the rest of the databases are doing fine except this one, then yes, the
problem is at the database level not at the server level.
You said, Index Tuning Wizard suggested new indexes, and the predicted
performance gain is substantial. I think you should go for it, after
testing.
To start with identify which stored procedures are running slow. Use
Profiler to identify the long running stored procedures (look at the
duration column, time is in milliseconds). once you identified the stored
procedures, try running them individually from Query Analyzer, with
'graphical execution plan' turned on. If you see index or table scans, then
the query is not written well, or there are no supporting indexes for that
query. Then try applying the indexes suggested by Index Tuning Wizard and
see if your query plan and response times improve.
Again, simply keep adding indexes is not really good for an OLTP system. if
you are trying to run report-like queries on an OLTP system, then you might
want to replicate the data to a different database, and run your queries
there. You could use transactional replication for this purpose. With
transactional replication the subscriber is only a few seconds behind the
publisher, in general.
Using Profiler, if you do find that the stored procedures and queries are
indeed running fine, the bottle neck is either the network or the
application.
Also check that there's no blockig going on in your database. You could use
sp_who or sp_who2 to see if any of the spids are getting blocked. Blocked
spid have a non-zero value in the 'blocked' column.
HTH,
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"alon" <alon@.discussions.microsoft.com> wrote in message
news:6467A9F4-A2C8-4EB8-9C2D-60286D564E6C@.microsoft.com...
> hi all
> I'm running a sql 2000 standard edition server on win 2000 server.
> one instance on a 3 gb memory, 2 cpus ,machine.
> there are about 20 databases on the server; however I'm encounering a
> problem with a spacific 500 mb db.
> the problem is: 7 client stations located at a different city
> communicating
> (only with)the problematic db on the server threw a single application
> via
> iis. for the last few months the respons time is queit slow. tables are
> reletivly small (the biggest is 60000 rows but the rest a only a few
> hunderd
> each)
> now... I'm trying yo isolate the problem.
> my first question: is it true to say that since the rest of the
> applications
> that are running against the other dbs are running smoothly the problem is
> not at the server lelvel or at the network level?
> also, opening the task mamager on the server machine I see that the cpu
> isnt
> realy busy and that there is no paging.
> I captured the a application queries with the profiler and I saw that the
> cpu/ read/writes times are low.
> there is no fragmentation on the indexes.
> I also run the index tunning wizard with the profiler file. it indicated a
> 44% improvment and suggested 5 new indexes; but I still belive the problem
> lays somewhere else.
> and, the system guy, who is monitoring the network traffic, tells me it's
> not the network as the bendwidth is 50% free.
> so... where should I aim at next?
> thanks,
> alon.
>

Monitor network resources used by single database

Hi,

Is there any methord to monitor network utilization per database on single instance of SQLServer2005 or SQLServer2000.

Please help

Regards

Mohd Sufian

The tools are Profiler, accessable from the Tools menu in SSMS or Start/Programs/SQL Server/Performance Tools or perfmon accessable from Start/Programs/Administrative Tools/Performance.

The tool to use depends on the meaning of "network utilization". Profiler will allow you to run a trace you can use to aggregate reads, writes and duration by database. Perfmon has many counters that can be used by database.

See SQL Server 2005 Books Online topics:

SQL Server, Databases Object

http://msdn2.microsoft.com/en-US/library/ms189883.aspx

Monitoring Resource Usage (System Monitor)

http://msdn2.microsoft.com/en-us/library/ms191246.aspx

Monitor Data Access Speeds & Time-Outs

I've been asked to produce charts that show data access & time-out
information for an instance of SQL Server.
I've very little experience with SQL Server.
My background is MI - so only know Analysis Services part of SQL
Server.
It would seem the previous person manually went through error logs
generated & rekeyed appropriate data into Excel to produce charts.
How can I produce the following charts automatically?
1. Data Access - ave. access time per process by day
2. SQL Errors - number of by day
3. Time-outs - number by hour
Or anyway of achieving effectively the same thing without any manual
involvement.
Many thanks.
We probably need more information on what you are trying to collect but here
is a start.
Setup a SQL trace. It will need to run 24hours a day writing to a file(s).
Have it stop and write out every so often 5 mins, 4 hours (depends on system
load and requirements). Start the next trace. Read the trace into a table.
Delete old trace files.
1. Data Access - Assuming you mean how long did each query take to be
serviced. Add RPC:Completed and SQL:Batch Completeed to trace events.
Select the avg(duration) of these events over the time period you need.
2. SQL Errors - Add the specific SQL errors you are looking for in the
trace. Select the cout of them over the time period.
3. Timeouts. Not sure where you are getting timeout information now.
There's lock timeout, remote query time, or connection timeout in SQL
Agent...Need more info.
<duvinrouge@.servihoo.com> wrote in message
news:1129629233.565048.215040@.g44g2000cwa.googlegr oups.com...
> I've been asked to produce charts that show data access & time-out
> information for an instance of SQL Server.
> I've very little experience with SQL Server.
> My background is MI - so only know Analysis Services part of SQL
> Server.
> It would seem the previous person manually went through error logs
> generated & rekeyed appropriate data into Excel to produce charts.
> How can I produce the following charts automatically?
> 1. Data Access - ave. access time per process by day
> 2. SQL Errors - number of by day
> 3. Time-outs - number by hour
> Or anyway of achieving effectively the same thing without any manual
> involvement.
> Many thanks.
>

Monitor Data Access Speeds & Time-Outs

I've been asked to produce charts that show data access & time-out
information for an instance of SQL Server.
I've very little experience with SQL Server.
My background is MI - so only know Analysis Services part of SQL
Server.
It would seem the previous person manually went through error logs
generated & rekeyed appropriate data into Excel to produce charts.
How can I produce the following charts automatically?
1. Data Access - ave. access time per process by day
2. SQL Errors - number of by day
3. Time-outs - number by hour
Or anyway of achieving effectively the same thing without any manual
involvement.
Many thanks.We probably need more information on what you are trying to collect but here
is a start.
Setup a SQL trace. It will need to run 24hours a day writing to a file(s).
Have it stop and write out every so often 5 mins, 4 hours (depends on system
load and requirements). Start the next trace. Read the trace into a table.
Delete old trace files.
1. Data Access - Assuming you mean how long did each query take to be
serviced. Add RPC:Completed and SQL:Batch Completeed to trace events.
Select the avg(duration) of these events over the time period you need.
2. SQL Errors - Add the specific SQL errors you are looking for in the
trace. Select the cout of them over the time period.
3. Timeouts. Not sure where you are getting timeout information now.
There's lock timeout, remote query time, or connection timeout in SQL
Agent...Need more info.
<duvinrouge@.servihoo.com> wrote in message
news:1129629233.565048.215040@.g44g2000cwa.googlegroups.com...
> I've been asked to produce charts that show data access & time-out
> information for an instance of SQL Server.
> I've very little experience with SQL Server.
> My background is MI - so only know Analysis Services part of SQL
> Server.
> It would seem the previous person manually went through error logs
> generated & rekeyed appropriate data into Excel to produce charts.
> How can I produce the following charts automatically?
> 1. Data Access - ave. access time per process by day
> 2. SQL Errors - number of by day
> 3. Time-outs - number by hour
> Or anyway of achieving effectively the same thing without any manual
> involvement.
> Many thanks.
>

Monitor Data Access Speeds & Time-Outs

I've been asked to produce charts that show data access & time-out
information for an instance of SQL Server.
I've very little experience with SQL Server.
My background is MI - so only know Analysis Services part of SQL
Server.
It would seem the previous person manually went through error logs
generated & rekeyed appropriate data into Excel to produce charts.
How can I produce the following charts automatically?
1. Data Access - ave. access time per process by day
2. SQL Errors - number of by day
3. Time-outs - number by hour
Or anyway of achieving effectively the same thing without any manual
involvement.
Many thanks.We probably need more information on what you are trying to collect but here
is a start.
Setup a SQL trace. It will need to run 24hours a day writing to a file(s).
Have it stop and write out every so often 5 mins, 4 hours (depends on system
load and requirements). Start the next trace. Read the trace into a table.
Delete old trace files.
1. Data Access - Assuming you mean how long did each query take to be
serviced. Add RPC:Completed and SQL:Batch Completeed to trace events.
Select the avg(duration) of these events over the time period you need.
2. SQL Errors - Add the specific SQL errors you are looking for in the
trace. Select the cout of them over the time period.
3. Timeouts. Not sure where you are getting timeout information now.
There's lock timeout, remote query time, or connection timeout in SQL
Agent...Need more info.
<duvinrouge@.servihoo.com> wrote in message
news:1129629233.565048.215040@.g44g2000cwa.googlegroups.com...
> I've been asked to produce charts that show data access & time-out
> information for an instance of SQL Server.
> I've very little experience with SQL Server.
> My background is MI - so only know Analysis Services part of SQL
> Server.
> It would seem the previous person manually went through error logs
> generated & rekeyed appropriate data into Excel to produce charts.
> How can I produce the following charts automatically?
> 1. Data Access - ave. access time per process by day
> 2. SQL Errors - number of by day
> 3. Time-outs - number by hour
> Or anyway of achieving effectively the same thing without any manual
> involvement.
> Many thanks.
>