Is there any way to see what jobs and or changes have been
made to a database. Not the data itself, but things like
indexes, statistics, DB settings, or outside/automated
jobs? I know I can run a trace to see real-time actions,
but I'm interested to see what may have happened overnight
or yesterday. Any help would be greatly appreciated.
Thank you,
Heidi
Some stuff are logged in the errolog file. You can also configure each SQL Server message to say
whether it will go to the errorlog file. However, DBCC DBREINDEX, for instance, doesn't return any
messages. You can check the Agent log, but that will not catch of someone does it from outside
Agent. Depending on your situation, your only option can be Profiler, or some audit tool reading the
transaction loge (see my links page about log reader tools).
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"hdsjunk" <anonymous@.discussions.microsoft.com> wrote in message
news:0d3801c49c0d$a14666b0$a301280a@.phx.gbl...
> Is there any way to see what jobs and or changes have been
> made to a database. Not the data itself, but things like
> indexes, statistics, DB settings, or outside/automated
> jobs? I know I can run a trace to see real-time actions,
> but I'm interested to see what may have happened overnight
> or yesterday. Any help would be greatly appreciated.
> Thank you,
> Heidi
Showing posts with label statistics. Show all posts
Showing posts with label statistics. Show all posts
Tuesday, March 27, 2012
Database Change Log
Is there any way to see what jobs and or changes have been
made to a database. Not the data itself, but things like
indexes, statistics, DB settings, or outside/automated
jobs? I know I can run a trace to see real-time actions,
but I'm interested to see what may have happened overnight
or yesterday. Any help would be greatly appreciated.
Thank you,
Heidi
Some stuff are logged in the errolog file. You can also configure each SQL Server message to say
whether it will go to the errorlog file. However, DBCC DBREINDEX, for instance, doesn't return any
messages. You can check the Agent log, but that will not catch of someone does it from outside
Agent. Depending on your situation, your only option can be Profiler, or some audit tool reading the
transaction loge (see my links page about log reader tools).
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"hdsjunk" <anonymous@.discussions.microsoft.com> wrote in message
news:0d3801c49c0d$a14666b0$a301280a@.phx.gbl...
> Is there any way to see what jobs and or changes have been
> made to a database. Not the data itself, but things like
> indexes, statistics, DB settings, or outside/automated
> jobs? I know I can run a trace to see real-time actions,
> but I'm interested to see what may have happened overnight
> or yesterday. Any help would be greatly appreciated.
> Thank you,
> Heidi
sql
made to a database. Not the data itself, but things like
indexes, statistics, DB settings, or outside/automated
jobs? I know I can run a trace to see real-time actions,
but I'm interested to see what may have happened overnight
or yesterday. Any help would be greatly appreciated.
Thank you,
Heidi
Some stuff are logged in the errolog file. You can also configure each SQL Server message to say
whether it will go to the errorlog file. However, DBCC DBREINDEX, for instance, doesn't return any
messages. You can check the Agent log, but that will not catch of someone does it from outside
Agent. Depending on your situation, your only option can be Profiler, or some audit tool reading the
transaction loge (see my links page about log reader tools).
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"hdsjunk" <anonymous@.discussions.microsoft.com> wrote in message
news:0d3801c49c0d$a14666b0$a301280a@.phx.gbl...
> Is there any way to see what jobs and or changes have been
> made to a database. Not the data itself, but things like
> indexes, statistics, DB settings, or outside/automated
> jobs? I know I can run a trace to see real-time actions,
> but I'm interested to see what may have happened overnight
> or yesterday. Any help would be greatly appreciated.
> Thank you,
> Heidi
sql
Database Change Log
Is there any way to see what jobs and or changes have been
made to a database. Not the data itself, but things like
indexes, statistics, DB settings, or outside/automated
jobs? I know I can run a trace to see real-time actions,
but I'm interested to see what may have happened overnight
or yesterday. Any help would be greatly appreciated.
Thank you,
HeidiSome stuff are logged in the errolog file. You can also configure each SQL Server message to say
whether it will go to the errorlog file. However, DBCC DBREINDEX, for instance, doesn't return any
messages. You can check the Agent log, but that will not catch of someone does it from outside
Agent. Depending on your situation, your only option can be Profiler, or some audit tool reading the
transaction loge (see my links page about log reader tools).
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"hdsjunk" <anonymous@.discussions.microsoft.com> wrote in message
news:0d3801c49c0d$a14666b0$a301280a@.phx.gbl...
> Is there any way to see what jobs and or changes have been
> made to a database. Not the data itself, but things like
> indexes, statistics, DB settings, or outside/automated
> jobs? I know I can run a trace to see real-time actions,
> but I'm interested to see what may have happened overnight
> or yesterday. Any help would be greatly appreciated.
> Thank you,
> Heidi
made to a database. Not the data itself, but things like
indexes, statistics, DB settings, or outside/automated
jobs? I know I can run a trace to see real-time actions,
but I'm interested to see what may have happened overnight
or yesterday. Any help would be greatly appreciated.
Thank you,
HeidiSome stuff are logged in the errolog file. You can also configure each SQL Server message to say
whether it will go to the errorlog file. However, DBCC DBREINDEX, for instance, doesn't return any
messages. You can check the Agent log, but that will not catch of someone does it from outside
Agent. Depending on your situation, your only option can be Profiler, or some audit tool reading the
transaction loge (see my links page about log reader tools).
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"hdsjunk" <anonymous@.discussions.microsoft.com> wrote in message
news:0d3801c49c0d$a14666b0$a301280a@.phx.gbl...
> Is there any way to see what jobs and or changes have been
> made to a database. Not the data itself, but things like
> indexes, statistics, DB settings, or outside/automated
> jobs? I know I can run a trace to see real-time actions,
> but I'm interested to see what may have happened overnight
> or yesterday. Any help would be greatly appreciated.
> Thank you,
> Heidi
Thursday, March 8, 2012
database access statistics?
Is there a simple way to see when the last time a
database was accessed?
Best,
Adria
a-carbo@.tamu.eduSQL Server can log event information for logon attempts and you can view it
by reviewing the errorlog. By turning on the auditing level of SQL Server.
follow these steps to enable auditing of all/successfull connections with
Enterprise Manager in SQL Server:
Expand a server group.
Right-click a server, and then click Properties.
On the Security tab, under Audit Level, click all/success etc(required
option).
You must stop and restart the server for this setting to take effect.
you can make use of profiler as well by running trace.
--
-Vishal|||go to the errorlog file which can be found at
$\Program Files\Microsoft SQL Server\MSSQL$<instance>\LOG
and open the errorlog file , the lastest errorlog file will not have any
extension. Every time SQL Server is started, the current error log is
renamed to errorlog.1; errorlog.1 becomes errorlog.2, errorlog.2 becomes
errorlog.3 and so on you will have to scroll through these errorlog files to
see which user has logged in at what time. This method will be useful if you
want to track "database connection" made by selected users who are specific
to the particular database only. System administrators like 'sa' can logon
to any of the database. Hence you can not exactly track database access on
the basis of database connections but, on the basis of "user connections"
made.
If you purely want to check the database connections you will have to make
use of SQL Profiler. Create a new trace with the events under "Security
audit" >> "audit login" . Under data columns include "database name". and
run the trace.
--
-Vishal
"Adria" <a-carbo@.tamu.edu> wrote in message
news:1c6f01c372f3$390c6980$a501280a@.phx.gbl...
> I just checked the Properties on the server and found
> that the Audit Level was already set to all/successes.
> How then do I view connections?
> FYI ... this is a database that I suspect has not been
> used for some time.
> Best,
> Adria
> >--Original Message--
> >SQL Server can log event information for logon attempts
> and you can view it
> >by reviewing the errorlog. By turning on the auditing
> level of SQL Server.
> >
> >follow these steps to enable auditing of all/successfull
> connections with
> >Enterprise Manager in SQL Server:
> >
> >Expand a server group.
> >Right-click a server, and then click Properties.
> >On the Security tab, under Audit Level, click
> all/success etc(required
> >option).
> >You must stop and restart the server for this setting to
> take effect.
> >
> >you can make use of profiler as well by running trace.
> >
> >--
> >-Vishal
> >
> >
> >.
> >|||Login attempts won't tell you if a database has been used or
not. It will only tell you about server logins, not database
access. You will need to monitor this yourself with profiler
going forward. I don't think there is any way to get
historical usage if you haven't been monitoring for such.
-Sue
On Thu, 4 Sep 2003 07:45:45 -0700, "Adria"
<a-carbo@.tamu.edu> wrote:
>I just checked the Properties on the server and found
>that the Audit Level was already set to all/successes.
>How then do I view connections?
>FYI ... this is a database that I suspect has not been
>used for some time.
>Best,
>Adria
>>--Original Message--
>>SQL Server can log event information for logon attempts
>and you can view it
>>by reviewing the errorlog. By turning on the auditing
>level of SQL Server.
>>follow these steps to enable auditing of all/successfull
>connections with
>>Enterprise Manager in SQL Server:
>>Expand a server group.
>>Right-click a server, and then click Properties.
>>On the Security tab, under Audit Level, click
>all/success etc(required
>>option).
>>You must stop and restart the server for this setting to
>take effect.
>>you can make use of profiler as well by running trace.
>>--
>>-Vishal
>>
>>.|||Vishal, Database Name is not one of the available columns
in the SQL Profiler, unfortunately. I'm trying to find
out the same thing on one of my servers and haven't found
a clean way to make the determination. (My latest RFI is
in subject "Determine database no longer in use - Redux"
If there was some time of condition that could be
monitored to determine if an application opens a
particular database it would help tremendously. Right now
I'm trying to capture "Active Transactions" because that
seems to be the closest I can find to 'real' usage. If
there's another one you can think of I'd be much obliged.
Allen
>--Original Message--
>go to the errorlog file which can be found at
>$\Program Files\Microsoft SQL Server\MSSQL$<instance>\LOG
>and open the errorlog file , the lastest errorlog file
will not have any
>extension. Every time SQL Server is started, the current
error log is
>renamed to errorlog.1; errorlog.1 becomes errorlog.2,
errorlog.2 becomes
>errorlog.3 and so on you will have to scroll through
these errorlog files to
>see which user has logged in at what time. This method
will be useful if you
>want to track "database connection" made by selected
users who are specific
>to the particular database only. System administrators
like 'sa' can logon
>to any of the database. Hence you can not exactly track
database access on
>the basis of database connections but, on the basis
of "user connections"
>made.
>If you purely want to check the database connections you
will have to make
>use of SQL Profiler. Create a new trace with the events
under "Security
>audit" >> "audit login" . Under data columns
include "database name". and
>run the trace.
>--
>-Vishal
>"Adria" <a-carbo@.tamu.edu> wrote in message
>news:1c6f01c372f3$390c6980$a501280a@.phx.gbl...
>> I just checked the Properties on the server and found
>> that the Audit Level was already set to all/successes.
>> How then do I view connections?
>> FYI ... this is a database that I suspect has not been
>> used for some time.
>> Best,
>> Adria
>> >--Original Message--
>> >SQL Server can log event information for logon attempts
>> and you can view it
>> >by reviewing the errorlog. By turning on the auditing
>> level of SQL Server.
>> >
>> >follow these steps to enable auditing of
all/successfull
>> connections with
>> >Enterprise Manager in SQL Server:
>> >
>> >Expand a server group.
>> >Right-click a server, and then click Properties.
>> >On the Security tab, under Audit Level, click
>> all/success etc(required
>> >option).
>> >You must stop and restart the server for this setting
to
>> take effect.
>> >
>> >you can make use of profiler as well by running trace.
>> >
>> >--
>> >-Vishal
>> >
>> >
>> >.
>> >
>
>.
>|||> Database Name is not one of the available columns
> in the SQL Profiler, unfortunately.
This column will not come by default.When you create a new trace go to the
3rd tab "data columns" and add the data column "database name" to the
selected data.
You can run the system stored procedure sp_who2 from query analyzer.
or query sysprocesses table to find users and processes
Ex:
select * from sysprocesses where db_name(dbid) = 'master' --specify
To get the active transaction on particular database you can run a dbcc
command.
dbcc opentran ('<database_name>')
--
-Vishal
"Allen White" <awhite@.advanstar.com> wrote in message
news:1e8101c372fa$8afa0f20$a601280a@.phx.gbl...
> Vishal, Database Name is not one of the available columns
> in the SQL Profiler, unfortunately. I'm trying to find
> out the same thing on one of my servers and haven't found
> a clean way to make the determination. (My latest RFI is
> in subject "Determine database no longer in use - Redux"
> If there was some time of condition that could be
> monitored to determine if an application opens a
> particular database it would help tremendously. Right now
> I'm trying to capture "Active Transactions" because that
> seems to be the closest I can find to 'real' usage. If
> there's another one you can think of I'd be much obliged.
> Allen
>
> >--Original Message--
> >go to the errorlog file which can be found at
> >
> >$\Program Files\Microsoft SQL Server\MSSQL$<instance>\LOG
> >
> >and open the errorlog file , the lastest errorlog file
> will not have any
> >extension. Every time SQL Server is started, the current
> error log is
> >renamed to errorlog.1; errorlog.1 becomes errorlog.2,
> errorlog.2 becomes
> >errorlog.3 and so on you will have to scroll through
> these errorlog files to
> >see which user has logged in at what time. This method
> will be useful if you
> >want to track "database connection" made by selected
> users who are specific
> >to the particular database only. System administrators
> like 'sa' can logon
> >to any of the database. Hence you can not exactly track
> database access on
> >the basis of database connections but, on the basis
> of "user connections"
> >made.
> >
> >If you purely want to check the database connections you
> will have to make
> >use of SQL Profiler. Create a new trace with the events
> under "Security
> >audit" >> "audit login" . Under data columns
> include "database name". and
> >run the trace.
> >
> >--
> >-Vishal
> >
> >"Adria" <a-carbo@.tamu.edu> wrote in message
> >news:1c6f01c372f3$390c6980$a501280a@.phx.gbl...
> >> I just checked the Properties on the server and found
> >> that the Audit Level was already set to all/successes.
> >>
> >> How then do I view connections?
> >>
> >> FYI ... this is a database that I suspect has not been
> >> used for some time.
> >>
> >> Best,
> >> Adria
> >>
> >> >--Original Message--
> >> >SQL Server can log event information for logon attempts
> >> and you can view it
> >> >by reviewing the errorlog. By turning on the auditing
> >> level of SQL Server.
> >> >
> >> >follow these steps to enable auditing of
> all/successfull
> >> connections with
> >> >Enterprise Manager in SQL Server:
> >> >
> >> >Expand a server group.
> >> >Right-click a server, and then click Properties.
> >> >On the Security tab, under Audit Level, click
> >> all/success etc(required
> >> >option).
> >> >You must stop and restart the server for this setting
> to
> >> take effect.
> >> >
> >> >you can make use of profiler as well by running trace.
> >> >
> >> >--
> >> >-Vishal
> >> >
> >> >
> >> >.
> >> >
> >
> >
> >.
> >
database was accessed?
Best,
Adria
a-carbo@.tamu.eduSQL Server can log event information for logon attempts and you can view it
by reviewing the errorlog. By turning on the auditing level of SQL Server.
follow these steps to enable auditing of all/successfull connections with
Enterprise Manager in SQL Server:
Expand a server group.
Right-click a server, and then click Properties.
On the Security tab, under Audit Level, click all/success etc(required
option).
You must stop and restart the server for this setting to take effect.
you can make use of profiler as well by running trace.
--
-Vishal|||go to the errorlog file which can be found at
$\Program Files\Microsoft SQL Server\MSSQL$<instance>\LOG
and open the errorlog file , the lastest errorlog file will not have any
extension. Every time SQL Server is started, the current error log is
renamed to errorlog.1; errorlog.1 becomes errorlog.2, errorlog.2 becomes
errorlog.3 and so on you will have to scroll through these errorlog files to
see which user has logged in at what time. This method will be useful if you
want to track "database connection" made by selected users who are specific
to the particular database only. System administrators like 'sa' can logon
to any of the database. Hence you can not exactly track database access on
the basis of database connections but, on the basis of "user connections"
made.
If you purely want to check the database connections you will have to make
use of SQL Profiler. Create a new trace with the events under "Security
audit" >> "audit login" . Under data columns include "database name". and
run the trace.
--
-Vishal
"Adria" <a-carbo@.tamu.edu> wrote in message
news:1c6f01c372f3$390c6980$a501280a@.phx.gbl...
> I just checked the Properties on the server and found
> that the Audit Level was already set to all/successes.
> How then do I view connections?
> FYI ... this is a database that I suspect has not been
> used for some time.
> Best,
> Adria
> >--Original Message--
> >SQL Server can log event information for logon attempts
> and you can view it
> >by reviewing the errorlog. By turning on the auditing
> level of SQL Server.
> >
> >follow these steps to enable auditing of all/successfull
> connections with
> >Enterprise Manager in SQL Server:
> >
> >Expand a server group.
> >Right-click a server, and then click Properties.
> >On the Security tab, under Audit Level, click
> all/success etc(required
> >option).
> >You must stop and restart the server for this setting to
> take effect.
> >
> >you can make use of profiler as well by running trace.
> >
> >--
> >-Vishal
> >
> >
> >.
> >|||Login attempts won't tell you if a database has been used or
not. It will only tell you about server logins, not database
access. You will need to monitor this yourself with profiler
going forward. I don't think there is any way to get
historical usage if you haven't been monitoring for such.
-Sue
On Thu, 4 Sep 2003 07:45:45 -0700, "Adria"
<a-carbo@.tamu.edu> wrote:
>I just checked the Properties on the server and found
>that the Audit Level was already set to all/successes.
>How then do I view connections?
>FYI ... this is a database that I suspect has not been
>used for some time.
>Best,
>Adria
>>--Original Message--
>>SQL Server can log event information for logon attempts
>and you can view it
>>by reviewing the errorlog. By turning on the auditing
>level of SQL Server.
>>follow these steps to enable auditing of all/successfull
>connections with
>>Enterprise Manager in SQL Server:
>>Expand a server group.
>>Right-click a server, and then click Properties.
>>On the Security tab, under Audit Level, click
>all/success etc(required
>>option).
>>You must stop and restart the server for this setting to
>take effect.
>>you can make use of profiler as well by running trace.
>>--
>>-Vishal
>>
>>.|||Vishal, Database Name is not one of the available columns
in the SQL Profiler, unfortunately. I'm trying to find
out the same thing on one of my servers and haven't found
a clean way to make the determination. (My latest RFI is
in subject "Determine database no longer in use - Redux"
If there was some time of condition that could be
monitored to determine if an application opens a
particular database it would help tremendously. Right now
I'm trying to capture "Active Transactions" because that
seems to be the closest I can find to 'real' usage. If
there's another one you can think of I'd be much obliged.
Allen
>--Original Message--
>go to the errorlog file which can be found at
>$\Program Files\Microsoft SQL Server\MSSQL$<instance>\LOG
>and open the errorlog file , the lastest errorlog file
will not have any
>extension. Every time SQL Server is started, the current
error log is
>renamed to errorlog.1; errorlog.1 becomes errorlog.2,
errorlog.2 becomes
>errorlog.3 and so on you will have to scroll through
these errorlog files to
>see which user has logged in at what time. This method
will be useful if you
>want to track "database connection" made by selected
users who are specific
>to the particular database only. System administrators
like 'sa' can logon
>to any of the database. Hence you can not exactly track
database access on
>the basis of database connections but, on the basis
of "user connections"
>made.
>If you purely want to check the database connections you
will have to make
>use of SQL Profiler. Create a new trace with the events
under "Security
>audit" >> "audit login" . Under data columns
include "database name". and
>run the trace.
>--
>-Vishal
>"Adria" <a-carbo@.tamu.edu> wrote in message
>news:1c6f01c372f3$390c6980$a501280a@.phx.gbl...
>> I just checked the Properties on the server and found
>> that the Audit Level was already set to all/successes.
>> How then do I view connections?
>> FYI ... this is a database that I suspect has not been
>> used for some time.
>> Best,
>> Adria
>> >--Original Message--
>> >SQL Server can log event information for logon attempts
>> and you can view it
>> >by reviewing the errorlog. By turning on the auditing
>> level of SQL Server.
>> >
>> >follow these steps to enable auditing of
all/successfull
>> connections with
>> >Enterprise Manager in SQL Server:
>> >
>> >Expand a server group.
>> >Right-click a server, and then click Properties.
>> >On the Security tab, under Audit Level, click
>> all/success etc(required
>> >option).
>> >You must stop and restart the server for this setting
to
>> take effect.
>> >
>> >you can make use of profiler as well by running trace.
>> >
>> >--
>> >-Vishal
>> >
>> >
>> >.
>> >
>
>.
>|||> Database Name is not one of the available columns
> in the SQL Profiler, unfortunately.
This column will not come by default.When you create a new trace go to the
3rd tab "data columns" and add the data column "database name" to the
selected data.
You can run the system stored procedure sp_who2 from query analyzer.
or query sysprocesses table to find users and processes
Ex:
select * from sysprocesses where db_name(dbid) = 'master' --specify
To get the active transaction on particular database you can run a dbcc
command.
dbcc opentran ('<database_name>')
--
-Vishal
"Allen White" <awhite@.advanstar.com> wrote in message
news:1e8101c372fa$8afa0f20$a601280a@.phx.gbl...
> Vishal, Database Name is not one of the available columns
> in the SQL Profiler, unfortunately. I'm trying to find
> out the same thing on one of my servers and haven't found
> a clean way to make the determination. (My latest RFI is
> in subject "Determine database no longer in use - Redux"
> If there was some time of condition that could be
> monitored to determine if an application opens a
> particular database it would help tremendously. Right now
> I'm trying to capture "Active Transactions" because that
> seems to be the closest I can find to 'real' usage. If
> there's another one you can think of I'd be much obliged.
> Allen
>
> >--Original Message--
> >go to the errorlog file which can be found at
> >
> >$\Program Files\Microsoft SQL Server\MSSQL$<instance>\LOG
> >
> >and open the errorlog file , the lastest errorlog file
> will not have any
> >extension. Every time SQL Server is started, the current
> error log is
> >renamed to errorlog.1; errorlog.1 becomes errorlog.2,
> errorlog.2 becomes
> >errorlog.3 and so on you will have to scroll through
> these errorlog files to
> >see which user has logged in at what time. This method
> will be useful if you
> >want to track "database connection" made by selected
> users who are specific
> >to the particular database only. System administrators
> like 'sa' can logon
> >to any of the database. Hence you can not exactly track
> database access on
> >the basis of database connections but, on the basis
> of "user connections"
> >made.
> >
> >If you purely want to check the database connections you
> will have to make
> >use of SQL Profiler. Create a new trace with the events
> under "Security
> >audit" >> "audit login" . Under data columns
> include "database name". and
> >run the trace.
> >
> >--
> >-Vishal
> >
> >"Adria" <a-carbo@.tamu.edu> wrote in message
> >news:1c6f01c372f3$390c6980$a501280a@.phx.gbl...
> >> I just checked the Properties on the server and found
> >> that the Audit Level was already set to all/successes.
> >>
> >> How then do I view connections?
> >>
> >> FYI ... this is a database that I suspect has not been
> >> used for some time.
> >>
> >> Best,
> >> Adria
> >>
> >> >--Original Message--
> >> >SQL Server can log event information for logon attempts
> >> and you can view it
> >> >by reviewing the errorlog. By turning on the auditing
> >> level of SQL Server.
> >> >
> >> >follow these steps to enable auditing of
> all/successfull
> >> connections with
> >> >Enterprise Manager in SQL Server:
> >> >
> >> >Expand a server group.
> >> >Right-click a server, and then click Properties.
> >> >On the Security tab, under Audit Level, click
> >> all/success etc(required
> >> >option).
> >> >You must stop and restart the server for this setting
> to
> >> take effect.
> >> >
> >> >you can make use of profiler as well by running trace.
> >> >
> >> >--
> >> >-Vishal
> >> >
> >> >
> >> >.
> >> >
> >
> >
> >.
> >
Subscribe to:
Posts (Atom)