Very new to SQL server, and the database world in general. We're an old Oracle shop, and our Oracle dba's are pretty set in their ways and aren't too excited about supporting SQL server. Understandable, change is tough sometimes. Being the new kid in town, I have made it my goal to wrangle our SQL 2000/2005 environment and carve out my own little niche.
My first order of business is to do some general cleanup and documentation. So, my question is this... is there a way to determine when the last few user connection to a database were? I have a host of databases that I suspect are no longer in use, but no one can seem to identify them. Before I delete them, I definitely want to make sure they aren't being used by some off-the-beaten-path app that I am unaware of. I did a search here and a general Google search that was fruitless. I'm not the beast Googler though. :)
Any advice here will be greatly appreciated!
-Alan-
edit: My idea of a solution would be a query to show, say, the last 10 connections to a particular database. Username, date, time, etc. would be spectacular.
We have a great big database (90gb) which has been populated (monopolised) by our finance team, and its full of tables that probably aren't being used at all. But know knows whats being used and what isn't or they don't have the time to go through it with me.
So I have decided to implement a procedure that logs table activity on this database, and if for example a table isn't used for a month then it will be archived off and zipped up.
I have a few ideas in my head how I can acheive this, but I am looking for some opinions and ideas from you guys?
I use following trigger to stop user "smith" if he try to connect through SSMS to My Server:
create TRIGGER [trg_connection_MyServer] ON ALL SERVER WITH EXECUTE AS 'Smith' FOR LOGON AS BEGIN IF ORIGINAL_LOGIN()= 'Smith' begin if exists (SELECT 1 FROM sys.dm_exec_sessions WHERE (program_name like 'Microsoft SQL Server%' and original_login_name = 'Smith') ) ROLLBACK; end
I want to log this information or send emal incase, this user try to connect through SSMS, so that I can catch it. How can I do this, if I use insert command it rollsback everything and I can't do any activity.
I am developing a package on my local workstation. I have defined two logging service providers. One is for SQL Server and the other is for the Windows Event Log. I am using the Dts.Log method in a script task to write log entries.
Logging is working properly with the SQL Server provider and rows are being inserted into the sysdtslog90 table. However, the only events that are being logged in the Windows Event Log are the package start and end events which I believe SSIS is doing automatically anyway.
Is there something I need to do to enable WIndows Event Log logging other than defining a log provider and making sure it is checked active? Won't SSIS write to two different logs with one Dts.Log call? Any ideas on what might be going wrong with my approach?
Hi, I decided to use the SQL Server log provider to store logging data of all my Integration Services packages. I also created some reports about this data for operating purposes. I have a problem occurs the name of the executing package is not always written to the log,but the name of the single task which failed. But that is not very useful information for operating, because I do not see any chance to get the name of the package by the information which is logged in the sysdtslog90 table in the database which I defined for SSIS Logging.
How do I configure the package to always log the package information into the table, too?
I recently read the project real ETL design best practices whitepaper. I too, want to do custom logging as I do today, and also use SSIS logging. The paper recommended using the variable system::PackageExecutionId to tie the 2 logging methods together.
Hi there, Here we have got a asp.net application that was developed when database was sitting on SQL server 6.5. Now client has moved all of their databases to SQL server 2000. When the database was on 6.5 the previous development team has used oledb connections all over. As the databases have been moved to SQL server 2000 now i am in process of changing the database connection part. As part of the process i have a login authorization code. Private Function Authenticate(ByVal username As String, ByVal password As String, ByRef results As NorisSetupLib.AuthorizationResult) As Boolean Dim conn As IDbConnection = GetConnection() Try Dim cmd As IDbCommand = conn.CreateCommand() Dim sql As String = "EDSConfirmUpdate" '"EDSConfirmUpdate""PswdConfirmation" 'Dim cmd As SqlCommand = New SqlCommand("sql", conn)
cmd.CommandText = sql cmd.CommandType = CommandType.StoredProcedure NorisHelpers.DBHelpers.AddParam(cmd, "@logon", username) NorisHelpers.DBHelpers.AddParam(cmd, "@password", password) conn.Open() 'Get string for return values Dim ReturnValue As String = cmd.ExecuteScalar.ToString 'Split string into array Dim Values() As String = ReturnValue.Split(";~".ToCharArray) 'If the return code is CONTINUE, all is well. Otherwise, collect the 'reason why the result failed and let the user know If Values(0) = "CONTINUE" Then Return True Else results.Result = Values(0) 'Make sure there is a message being returned If Values.Length > 1 Then results.Message = Values(2) End If Return False End If Catch ex As Exception Throw ex Finally If (Not conn Is Nothing AndAlso conn.State = ConnectionState.Open) Then conn.Close() End If End Try End Function ''' ----------------------------------------------------------------------------- ''' <summary> ''' Getting the Connection from the config file ''' </summary> ''' <returns>A connection object</returns> ''' <remarks> ''' This is the same for all of the data classes. ''' Reads a specific connection string from the web.config file for the service, creates a connection object and returns it as an IDbConnection. ''' </remarks> ''' ----------------------------------------------------------------------------- Private Function GetConnection() As IDbConnection 'Dim conn As IDbConnection = New System.Data.OleDb.OleDbConnection Dim conn As IDbConnection = New System.Data.SqlClient.SqlConnection conn.ConnectionString = NorisHelpers.DBHelpers.GetConnectionString(NorisHelpers.DBHelpers.COMMON) Return conn End Function in the above GetConnection() method i have commented out the .net dataprovider for oledb and changed it to .net dataprovider for SQLconnection. this function works fine. But in the authenticate method above at the line Dim ReturnValue As String = cmd.ExecuteScalar.ToString
for some reason its throwing the below error. Run-time exception thrown : System.Data.SqlClient.SqlException - @password is not a parameter for procedure EDSConfirmUpdate. If i comment out the Dim conn As IDbConnection = New System.Data.SqlClient.SqlConnection and uncomment the .net oledb provider, Dim conn As IDbConnection = New System.Data.OleDb.OleDbConnection then it works fine. I also have changed the webconfig file as below. <!--<add key="Common" value='User ID=**secret**;pwd=**secret**;Data Source="ESMALLDB2K";Initial Catalog=cj_common;Auto Translate=True;Persist Security Info=False;Provider="SQLOLEDB.1";' />--> <add key="Common" value='User ID=**secret**;pwd=**secret**;Data Source="ESMALLDB2K";Initial Catalog=cj_common;' />
What is the best to produce user activity log ? Using triggers (insert, delete, update) is rather heavy (?) operation. Has anyone used SQL Profiler for that ? In log should be: who, what, when, workstation, table, changed fields, some data key values.
I have registered a server on to my client machine successfully,but I am not finding the current activity item in the management folder on EM.How do I see the current activity on the server.Do I need to be SA for that server?My machine has NT workstation with SQL server 7.0 on it.Any one to help?
If I run sp_who2, the following message is returned. (it also is returned randomly while using function in EM such as Backup/Restore.)
Msg 268, Level 16, State 1 You cannot run SELECT INTO in this database. The DBO would have to run sp_dboption to enable this option.
What is it??? It's buggin me......
Dano
p.s. I remember a similar problem in the past, it had to do with turning truncate on checkpoint in one of the system databases msdb/master/tempdb or something like that. I turned off all Trunc. and Select/Bulk options on these...
I have a requirment to create a activity log to log all changes that take place in my tables as they happen. All my Inserts,updates & Deletes take place inside stored procs. A windows .NET application calls the stored procs and passes the data set to the stored procs to execute them on a Sql server 2000 database.
When an update statement is run inside my stored proc, the dataset only passes me the new values. but,i will need the old value from the database before the update takes place to report in my activity log. What is the best way to do it ?
My activity log shold report a description like this
"Name Changed from 'Robert Johnson'(old value) to 'Bob Johnson'(New Value)"
Though i can do a select from the tables to get the old values and match the new values to get the differences before running the update statements, it is too much work as i have well over 100 stored procs in my application and i have to check for every single field value in a table. If only one column changed in a table containg 20 columns, i would still have to check for 20 columns before determining which fields changed.
Can any one suggest me a better solution to report the old values and new values for all the updates that take place in a database through stored Procedures.
Hi experts, I just want to know how can i kill all the processes of a database if the database have more than 100 connections.This is for the purpose of restoring a database.
I usually run this command in SQL 2000. Which tells me what is running with all the sql code statements.
Is there an alternative command to run in SQL 2005...rather than using the Reports - i find it easier to execute sp i called this sp_now.
set nocount on declare @handle binary(20), @spid smallint, @rowcnt smallint, @output varchar(500)
declare ActiveSpids CURSOR FOR select sql_handle, spid from sysprocesses where sql_handle <> 0x0000000000000000000000000000000000000000 --and spid <> @@SPID order by cpu desc
OPEN ActiveSpids FETCH NEXT FROM ActiveSpids INTO @handle, @spid
I am new at this site and new at using SQL also.... I was wondering if anyone could help me out with my prob...
I have set up an SQL 2000 server and made some accounts which my friends are using to log onto my server.. Is there a way I could keep a check on who is doin what or maintain a user log....
I have a SSIS which copies data from a table to a flat file. The connection string of this file is variable and the file is reused if not exists and is created if exists.
When I run the SSIS manually from my microsoft visual studio it works properly. However, when I run this SSIS from the Job Activity Monitor, I get the following error:
Message Executed as user: REDCAMadminsql2k5. Microsoft (R) SQL Server Execute Package Utility Version 9.00.3042.00 for 64-bit Copyright (C) Microsoft Corp 1984-2005. All rights reserved. Started: 8:41:10 AM Error: 2007-09-10 08:48:04.99 Code: 0xC020200E Source: Crear Historico Historico [1] Description: Cannot open the datafile "\srvnfileHISTORICOSCAJEROSOFI3210C01OFI3210C01_2007-7X.txt". End Error Error: 2007-09-10 08:48:04.99 Code: 0xC004701A Source: Crear Historico DTS.Pipeline Description: component "Historico" (1) failed the pre-execute phase and returned error code 0xC020200E. End Error DTExec: The package execution returned DTSER_FAILURE (1). Started: 8:41:10 AM Finished: 8:48:53 AM Elapsed: 462.234 seconds. The package execution failed. The step failed.
A developer is running sql transactions from the application and needs to know how it is executed from database, I know I have to run Profiler Trace, but not exactly which traces , Can you please guide me?
Does anybody know how to trace user activity after they log off from the SQL Server 6.5? I know SQL Trace is able to trace the user activity in real time.
Trying to view current activity in EM however get error 1222 Lock request time out period exceeded, this also occurs when trying to view tempdb database.
Is this a EM Gui error or locking issue and hwo do you resolve?
On sql server 6.5 running on a win/nt platform my Current Activity window comes up blank when I click on it. Everything else seems to be working fine. This is a new problem, it just stopped reporting any info when I click on it. Any suggestions would be greatly appreciated.
Hi there! in the ms-enterprise manager , the current activity.. under server menu does not display anything in one of the server in EM but in other servers it is okay.
I'm an Oracle DBA that has inherited some SQL Server 2000 databases. Can you audit a particular user in Sql Server 2000. We need to know exactly what a particular user is doing, (i.e. creating/dropping objects, and what data he is accessing)
I'm trying to produce a user activity table out of a table that contains rows on when users log in or out. However, I can't seem to get the query to 'pick' the right date time in the join.
Here is what I came up with, but it doesn't seem to be working right :eek:
select distinct (userin.changeid), userin.userid, userin.createdate as LoggedInDate, userout.createdate as LoggedOutDate, DateDiff(mm,userin.createdate,userout.createdate) as Duration from userChange userin inner join ( select * from userChange where loggedin = 0 ) userout on userin.userid = userout.userid where and userin.loggedin = 1 and userin.createdate < userout.createdate order by userin.createdate desc
We have several SQL 2000 databases on one server. One of the applications I'm responsible for has batch jobs that run for an hour; all activity is on the database. During this hour, other applications that use other databases on the same server experience time-outs. One of my coworkers did a count(*) on an empty table and it took 11 seconds.
We pay people to keep our servers up and running. Is this something they might solve by reconfiguring the server? It seems strange to me that a single database is allowed to hog all server resources. We are meeting with them later this week, and I'd like to have some knowledge about this; we don't want to BS'ed into buying a new server.
I have a strange situation. Performance monitor shows that SQLServer:Transactions Transactions value is 125, but SQL Server Profiler does not show any activity.
I ran sp_who2 and I have a bunch of processes with SUSPENDED status. Would those be counted in Performance monitor?
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?
I have created an integration services package with a script-source reading data from Active directory. Pretty much data is read and written into a sql2005 database.
I have notised that when I run the package via right-clicking the package (Under stored packagesMSDB) and selecting "Run Package" it takes about 45 minutes for it to complete successfully. The same goes if I run the package from inside Visual Studio. However, when I create a job and put this package as one of it's tasks the task takes about 1h 40min. This is more then double the time! In neither case the server is occupied with soething else. And I have tried it several times so it wasn't just an "accident".
What to check on NT or Sql server it self if 1. NT performance shows that sql serve.exe -CPU 93% memory 49000 2. Sql server current activity only sa connected to master. Thanks
