Is There An Easy Way To Monitor (audit) Who Logs Onto A Database ??
Jul 20, 2005Is there an easy way to monitor (audit) who logs onto a database ??
Thanks for any and all help that is provided.
Art
ADVERTISEMENT
SQL Activity Monitor Logs
Oct 5, 2007Hi All,
I want to analyze server (SQL 2005) activity at a specific time in the past. Unfortunately, there doesn't seem to be a log file reflecting the information in Activity Monitor. Knowing that the sysprocesses and syslocks tables feed Activity Monitor, I thought about doing a log analysis on the master transaction log, but I'm not sure that's possible.
Is there a (different) way to achieve this?
Or is there a better approach to identifying processes that cause performance bottleneck/deadlocks?
Any input would be appreciated.
Ken
Probably Easy Question On Transaction Logs
Feb 27, 2004Hi,
I have a transaction log that is 1311 Mb in size. I'd like to reduce it. From what I have read, I should just backup the entire database (or simply the transaction log) and it should reset the transaction log.
Either I can't read, I forget things (it's been a while since I read on that subject) or there's something wrong because a backup didn't empty my transaction log at all. It's still the exact same size!!!
The only way I found to successfully delete every bit of data in a transaction log was to detach the database, delete the log file and re-attach the database.
There must be an easier way!?!?!?!
Thanks,
Skip.
Database Audit Specification To Audit Select On Certain User And Table
Nov 1, 2014I have made a server security audit and specify from database audit specification to audit "select" on a certain user and on a certain table. I logged in by this user and made the select statement..when i run this query
"select * from sys.fn_get_audit_file('d:Auditaudit1*',null,null)"
It return a value at which time the query has done
after 15 minutes i repeated the same action, i run the audit query and the same result is showed off on the panel.is it suppose to return a list of values by how many times this user has made the select statement on that table ? for example at 5:00 pm then 6:00 pm and so on
Error/Failure Audit In Event Logs (SQL 2005 Express) [Advanced Services]
Aug 9, 2007Q1: Any way to control which db loads when, and perhaps stop this error from happening? Again- when all is said and done, the report server functions, and I am able to access just fine.
Q2: What kind of recovery is being referred to? Given the message type- I assume it's nothing of concern- probably more to do with a system that doesn't run all the time.
Okay- so I get these two event messages each time I boot.
Below are two error message I see daily with each system boot- the services mentioned do "actually" function, and are running- as not 10 seconds after these messages are logged, the successfully loaded messages appear. It's almost as if report services tries to connect before the databases are up and running.
Basically the sequence (today) was:
1. 05:49:01 Failure Audit (MSSQL$SQLEXPRESS)
2. 05:49:01 Error (Report Server Windows Service (SQLEXPRESS)
3. 05:49:02 Recovery is complete. (EventID 3408 MSSQL$SQLEXPRESS)
4. 05:49:06 Starting up database 'ReportServer'
5. 05:49:09 Server Resumed execution (MSSQL$SQLEXPRESS)
I'm assuming based on the events that Report Server Service attempts to connect to the ReportServer database before it's actually started. Doesn't really explain the failure audit-
1. Failure Audit:
Event Type: Failure Audit
Event Source: MSSQL$SQLEXPRESS
Event Category: (4)
Event ID: 18456
Date: 8/9/2007
Time: 05:49:01
User: <MACHINENAME><LOCALUSER>
Computer: <MACHINENAME>
Description:
Login failed for user '<MACHINENAME><LOCALUSER>'.
[CLIENT: <local machine>]
This is almost immediately followed by this (which I consider a no-brainer, considering the above):
2. Error:
Event Type: Error
Event Source: Report Server Windows Service (SQLEXPRESS)
Event Category: Management
Event ID: 107
Date: 8/9/2007
Time: 05:49:01
User: N/A
Computer: <MACHINENAME>
Description:
Report Server Windows Service (SQLEXPRESS) cannot connect to the report server database.
SQL Security :: How To Create Database Specifications On Newly Created Database Automatically For Audit
Jul 15, 2015I am setting up SQL audit on sql servers in my environment based on requirement. I want to create database specifications ASAP database created. I tried DDL trigger but Audit doesn't support triggers. So I created audit specifications on model database. the only problem with this is every specification created on new database with same name.database specification name includes newly created database name or other methods to create database specifications on newly created databases.
View 6 Replies View RelatedSetup And Upgrade :: Activity Monitor Shuts Down If Connect To Instance Its Monitoring With Performance Monitor
Aug 19, 2015If I'm on a remote machine, meaning a computer not in the WSFC cluster, and I open SSMS 2014, point it to a SQL Instance, and open activity monitor:
1. I get all the panes and charts except % Processor Time.
2. Then, if I authenticate to the cluster's domain by mapping a drive with valid domain credentials, I'm free to put performance counters in the Perfmon - - - but SQL Activity Monitor shuts down with“The Activity Monitor is unable to execute queries against server SQL-V01INSTANCE1..Activity monitor for this instance will be placed into a paused state.Use the context menu in the overview pane to resume the activity monitor.
Additional information: Access is denied. (Exception from HRESULT: 0x80070005 (E_ACCESSDENIED))(Mscorlib)”
3. Of course, the Activity monitor can't be resumed via the context menu. Removing counters and closing the perfmon do not work. I dropped the mapped drive and rebooted the machine. That brought back 95% of the information in the Activity monitor.
4. Further experimentation showed that any mapping of drive shares present on the SQL Server to the computer running SSMS cut off functionality of the 'overview' pane in the remote machine's SQL Activity monitor -- the monitor that had been trying to watch the server offering the shares.
Audit Logon / Audit Logoff Problem With SQL 2K
Jan 18, 2006I need help...here is the problem.Last weekend, the servers in our datacenter where moved around. After thismove, and maybe coincidental, 1 server is performing very poor. Afterrunning a trace with SQL Profiler, I saw the problem which was laterconfirmed with another tool for SQL server performance monitoring. It seemsthat all connections to the SQL server (between 200 - 400) are doing a login/ logout for each command that they process. For example, the user'sconnection will login, perform a SELECT, and then logout. This is not a..NET application. The client software was not changed, it is still thesame. The vendor has said that it is not supposed to do that, it issupposed to use 1 connection that log's on in the morning and logs off atthe end of the day or whenever the user exits. 1 user may have severalconnections to the database.At times, the server is processing over 250 login / logouts (avgeraged for30 second period). Has anyone seen this problem? I have the server inAUDIT FAILUREs only. The server has become very unresponsive, things thattook 3 seconds now take over 15 seconds.Any ideas???
View 6 Replies View RelatedDatabase Audit
May 2, 2006Does MSSQL 2000 and above have an auditing feature? We have a requirement for tracking activity and history on a particular table. I realize this can be accomplished by a simple trigger but I was wondering if MSSQL had a feature that would accomplish this.
joe
Database Audit
Jul 5, 2007Hi,
We are currently porting our POINT OF SALE system to SQL Server 2005. In our existing software we log database changes (inserts, amendments and deletions) to a flat acsii files so that these files can be FTP'd to remote sites (largest number of sites 100+) and processed on to replicate the data.
I have looked at REPLICATION in MSSQL 2005 but we need two way replication and additional processing. Therefore I dont think we will be able to use this feature.
Therefore what I wanted to do was to try and setup an automated way of capturing database changes to all tables within the database and log these changes to XML to be shipped out to remote sites. Unfortunatly I can only find TABLE TRIGGERS which would require creating 100's of triggers as we have 100's of tables.
Is there anyway of setting up MSSQL server to automatically do something like this... I was looking to see if there was a DATABASE TRIGGER which could perform this action but I cant see anything...
Can anyone advise or is there a simpler way of doing this ??
Ray
Database Mirroring Monitor Not Working For One Database
Apr 2, 2008I set up a new mirror server. Everything is good except that the Database Mirroring Monitor is not working for one of the databases. In the monitor the principal data is showing up as blank
If I look at dbm_monitor_data on the principal most of the data columns (e.g. Send_queue_size) are null where as they have data for the other databases.
Both servers are SQL Server 9.0.2047 Enterprise Edition.
Any idea what might be going on here?
Thanks in advance.
How To Monitor Through MOM For A Database
Feb 7, 2008Hi experts,
I never used "monitoring through MOM for a sql databse". so can any one explain, how to monitor through MOM.
-Thanks N Regards,
Kanthi.
Database Audit Trail Question....
Jan 2, 2001Hi All....
First a bit about me. I'm a developer in Atlanta. My background is mostly in Unix, but am working in NT at this job. I am gearing my skills towards enabling products on the web for people. I have experience in PHP, HTML, Javscript, C/C++, MySQL, a little Oracle and now getting fimilar with SQL Server.
What i'm trying to do....
Track all changes to data in the DB.
Why i'm doing it....
Banking application, any account changes need to be logged with which employee made the change.
How I want to do it....
Currently the app is a web app that accesses VB for the backend. There is a single SQL Server user accessing the DB. I want to use triggers on INSERT, UPDATE, and DELETE to copy the new row to any audit table. This will be identical but with user, action and time.
Problem....
Single user connection will hide acutal app userid. I want to have the VB app get the "SQL Server Session ID" (if one exists) and store the app_userid in a table with the session ID for cross reference by the trigger.
Question:
Does SQL Server have a "session id" for multiple connections for a single user? Where is it located? Can VB access this information?
Thanks,
Brian
Looking For SQL To Monitor Database Size
Jan 21, 2002Hello all!
I am looking for a way to monitor the space remaining inside of a database and to receive an e-mail alert when that space gets below a certain threshold.
What I want to do is schedule a job to run every hour or so and check to see if available space in my databases is below about 300 megs. If it is I would then like to be sent an e-mail to my cell phone. I have jobs like this already running on my Oracle databases but am not sure of which tables to query for this in the SQL Server environment.
Does anyone know which table(s) would need to be queried to retrieve this info or, better yet, have a script which already written?
Thanks in Advance!
Trea Johnson
Database Administrator
Vulcan Materials
Sql Scripts To Monitor Another SQL Database.
Jul 20, 2005Greetings All, I was hoping that some of you might be aware of anyfree scripts that were written explicitly for the purpose ofmonitoring a SQL Server database. I want to be able to determineup/down events, Error Log updates, Long running Transactions,Deadlocks, etc. My plan is to use a central SQL Server database as arepository that will query (via t-sql) the target SQL Server boxes forthe events listed above. If one of the events falls outside of adefined range I will use xp_sendmail to alert me via my pager. I havebeen looking at third party tools like BMC Patrol, Quest Foglight, andEmbarcadero Performance Center and although they are very nice theyare also very EXPENSIVE. I am looing for a cheap solution. Any helpwould be greatly appreciated.Louis
View 1 Replies View RelatedDatabase Mirroring Monitor
Jun 15, 2006Hi there!
I've some trouble with monitoring a database mirroring session. I am testing a lot configuration possiblities, and i use the database mirroring monitor to view the mirroring states.
But in some (most) of my configurations, i only get following informations in the database mirroring monitor:
Oldest unsend transaction, Unrestored Log, Current Restore rate and the Witness Adress. I am working in High safety mode with automatic failover.
Question 1: What are the reasons that all the other indicators (Unsend log, Current send rate, Mirror commit overhead) are not shown?
Question 2: In the moment, i am using certificates for the mirroring communication. I think, my client (who the database mirroring monitor is running) connects with the usual windows authentication to the server and only selects from sys.database_mirroring or something else. So there is no need to create inbound certificates on the partners for the client who is connecting with the mirroring monitor?
Torsten
DB Engine :: How To Take Backup Of Database Audit Specification
Nov 10, 2015How can I take backup of Database Audit Specification ?
View 3 Replies View RelatedProducts To Monitor Database Availability
Nov 4, 2003Are there any other products out there to monitor database availability
in SQL server 2000 beside Tivoli by IBM?
Thanks;
Lava
Database Mirroring Monitor Job Error
Apr 13, 2007We have recently set up two SQL Server 2005 Standard Edition serverswith database mirroring. The mirrors function and fail over without aproblem, but the Database Mirroring Monitor Job fails every time withthe following error message:Incorrect syntax near '.'. [SQLSTATE 42000] (Error 102) Incorrectsyntax near the keyword 'with'. If this statement is a common tableexpression or an xmlnamespaces clause, the previous statement must beterminated with a semicolon. [SQLSTATE 42000] (Error 319). The stepfailed.Both SQL Servers are running SP2 with the latest patches.Can anyone help with the resolutionto this this issue?Thank you!Bosko
View 5 Replies View RelatedEasy Problem, But Need Help With Setting Up Database Keys.
Jan 29, 2008I need help setting up my primary keys for a database table. Suppose I have two tables, as listed below. The first one isa table of students and the course they are enrolled in. The second table is a table of test scores. Never mind that this could be designed better. Please just using my design as an example, I need help.
First table - Student Table
Student Id Student Course Building1 Joe Math101 Primus Hall2 Jake Engl103 Indigo Hall3 Mary Art107 Money Hall
So my question is the following. On my second table below, can I just haveone Key as the Primary key? Just like in the first table. I will have a primary key named "TestId"
If so, how do I set up my table so that the values in the "TestId"table will be automatically generated upon inserting into the Testscore table.
Second table - Tests Score Table:
TestId StudentId TestScore Date 1 1 99 9/1/07 2 1 100 9/15/07 3 1 88 9/16/07 4 2 99 9/1/07 5 2 100 9/15/07 6 2 88 9/16/07 7 3 79 9/1/07 8 3 100 9/15/07 9 3 100 9/16/07
DB Engine :: Write Logs To Windows Event Logs?
Aug 6, 2015OS: Windows 2012 Enterprise
SQL Server: 2012 Enterprise
I was wondering if there is any way all SQL Server error log entries could be automatically written to Windows Event Log.
Transact SQL :: Database Audit Specification On Specific Users
Mar 30, 2015Currently I am using SQL server 2012 and would like to implement database audit specification on specific users in my database. These are the users in my database name Payroll :-
PayrollAndy.Bred - db_owner
PayrollArpit.Shah - db_owner
Payrollwebapp - db_datareader, db_datawriter, EXECUTE
web_payroll - db_datareader, db_datawriter, EXECUTE
In my database audit specification settings, I would like to capture any SELECT,UPDATE,DELETE and EXECUTE command for users PayrollAndy.Bred & PayrollArpit.Shah only since they owned db_owner access. However, I am unable to capture any single command from both users. I do not want to put 'Principal' as public since I just want to capture both users activity.
Is it I miss out anything? Is it because of windows login account?
SQL Server 2008 :: How To Monitor Database Growth
May 5, 2015I need to monitor my database growth, as few of databases are growing rapidly. My client wants the growth list of my databases. have report of database growth of specific databases, at least of one month.
View 3 Replies View RelatedMissing Feature In SP2 Database Mirror Monitor
Mar 29, 2007Hello,
I've been running SQL 2005 Enterprise SP1 for some time. After installing SP2, I miss the "Database Mirror Monitor"-Feature. Where is it? Did MS kill the feature?
regards
alejandroo
Launch Database Mirroring Monitor Is Not An Option
May 16, 2006Good morning all...
I would like use the "Database Mirroring Monitor" to track what's going on.
All three of my servers have SQL Server 2005 x64 edition installed with SP1.
Server A is mirroring to Server C
Server B is mirroring to Server C
Following the instructions at http://msdn2.microsoft.com/en-us/library/ms365786(d=ide).aspx:
On Server A and C, "Launch Database Mirroring Monitor" appears.
On Server B, "Launch Database Mirroring Monitor" does NOT appear.
Is there something I have to do to get this tool installed on the machine, or it is an automatic thing?
Thanks!
Forch
How To FAST And EASY ENCRYPTION ALL Stored Procedure In My MS-SQL Database ?
Jun 16, 2006My MS-SQL 2000 Database have 50 more Stored Procedure .How to FAST and EASY ENCRYPTION ALL Stored Procedure in my MS-SQLDatabase?
View 1 Replies View RelatedDB Engine :: Trace Database Audit Specification Enable And Disable
Nov 10, 2015How can i Trace Database Audit specification Enable and Disable. i want to maintain log for enable or disable database audit specification.
View 4 Replies View RelatedDB Engine :: Is There Any Possibility To Override Database Audit Specification File
Nov 10, 2015Is there any possibility to override database audit specification file. suppose i want to change some logs forcefully . is it possible ?
View 5 Replies View RelatedMaintain Audit Trail Of Access To SQL Server 2000 Database
Sep 25, 2006Is there any way to maintain audit trail of access to my SQL server 2000 database by any user ?? I need to log the timestamp of any insert/update/delete to any record in a table within the database by the user.
Alex
SQL 2012 :: Efficient Way To Monitor User Access To Database
Apr 22, 2014Currently there are various teams accessing the database. For costing reasons, we need to track usage.Is there an efficient way to monitor User access to the database.Can we track which user has executed which query(SELECT,insert etc),the login time and such parammeters?
View 5 Replies View RelatedRestoring Logs To A Database
Jul 20, 2005Hi Gurus,i am having problems with restoring a ms sql database.i have restored the database using veritas to a different location('g:datafiles') in no recover mode.when i view the database through the Enterprise Manager, it shows thedatabase as silver icon (loading).i go to sql analyzer, and put in the restore commandrestore log myDatabasefrom 'mylog'with recoverythis produces an errorRESTORE FILELISTONLYFROM jobsServer: Msg 3206, Level 16, State 1, Line 1No entry in sysdevices for backup device 'mylog'. Update sysdevicesand rerunstatement.i look in master..sysdevices - no entry for mylog, but then no entriesfor the log files for any of the other perfectly working databaseseither.i do have a copy of the log files ('c:logfiles') in another locationi would like the following help if possible:* a way to update sysdevices with the log file i wish to apply to myrestored database so it will let my restore go through properly.* a way to specify to use apply the logs in 'c:logfiles' withouthaving to give the restore statement a logical name for the log files(which naturally won't be in sysdevices!)pls supply transact sqlmy thanksEdwina63(if wish to email please remove h from edwinah@)p.s sp_add_log_file_recover_suspect_db will not work in a partiallyrestored database
View 6 Replies View RelatedWhich Needs The Better Performance: Logs Or Database?
Feb 6, 2008 I have a set of disks allocated for a high performance SQL implementation that will entail lots of large queries. My question is do I allocate more IOPS to the logs or to the database?
For example, if I have a 10 disk RAID 1/0 and a 4 disk RAID 1/0 available, which do I allocate to the logs and which do I allocate to the database? Which will require the most IOPS?
Thanks!
SSIS Logs Vs Custom Logs Components
Feb 12, 2007Hi,
In my case I have to log the errors raised by any task in a package to either windows event log, text file or SQL server. Also I need to send an email notifications to a group of people telling them about the error.
Now can I use SSIS package logging for logging the errors into the required destinations. I mean right clicking on the package and selecting Logging, then adding the required log providers and enabling the events for logging into those. I think I have to upfront select the log providers to log the error, I will not have the liberty to log the error to the destination, the name of which is passed as a variable to the package. This is okay with me though.
Now what will a custom log provider help me to do in this case. Also can I somehow configure my package to call the send mail task everytime an error is raised.
Also, one more option can be developing a package that only does the error handling. It will take in the paramters or the error codes and descriptions, the destination to write to and a flag to send mail or not for that particular type of error.
What do you think? Kindly advise.
Thanks in advance for your help and time.
Regards,
$wapnil