Is there any way to find the time when the last DDL was happened in a table?
For example: The time when the new column(s) were added into a table or changed the datatype.
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.
Ok, please pardon my complete ignorance; but I am extremely SQL challenged. I manage the server right now and I am the closest thing to a DBA at the moment with this server.
Here is the problem. I have a job that runs every hour and until last week it was running without any problems. Now, once a day it hangs, and I end up having to restart sql to get it to be functional again. I guess I could just kill the spid, but regardless, it's fails.
It doesn't happen at the same time each day. I thought it was from the backups doing a SQL backup job, but I eliminated that possibility. I can't find what is causing the problem, because the SQL logs just stop at the time that this happens. It's like some other job/query is running and taking all the sql server resources. But I can never catch it when it's happening and I have no event logs or SQL logs to go on.
Is there any query that I can run to find what happened around the specific time the job failed to run? I just need to find what process did what when this job stopped working.
Again, I apologize if my lack of SQL knowledge is making me ask dumb questions, but I am really lost.
How to find last login date/time for user DML acitivity on databases on Instance?
Is there any way we can find our the last login date/time for databases?
Note: 1. We can find if the SQL Trace is running and store.This is not good solution 2. Audit logins off/on is also not good solution. 3. Using DMV's also not good option, if reboot sql server instance then historical values can not see.
Finding Time Zone: Hi, 1. I have a patient record created in the database with a CreateDate 2. I need to find the time zone based on a state or city or zip in the record 3. And find corresponding EST time of CreateDate
I have a start_datetime column and an end_datetime column that records when a process we run starts and stops. I am trying to write a query that can calculate the difference between the start time and the end time and display it as hh:mm:ss but cannot seem to come up with a solution. Any ideas?
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?
TimeStart - Which is the time and date that the user logs in
TimeEnd - Which is the time and date that the user logs out
Using SQL, how do you find who has logged in using the same account within the same time period. In other words, Two people are using the same username and are logged in at the same time.
What is the easiest way to write a query statemet to find two or more instances that occur in a 15 minute period? Any help would be greatly appreciated. Thanks.
Hello,I have an application that will be logging to a SQL Server 2000database user user activity from several Windows 2003 terminalservers. This information will be retrieved by monitoring theSecurity logs of these servers (this part I know how to accomplishalready).A table in the database, tblLogEntries, will contain the followingfields:- ID = autoincrementing int- LogTime = Date/Time the user activity was recorded in the securitylog- Username = User's login ID that the activity was recorded with- Type = int, referencing a lookup table with the values of Logon,Logoff, and possible other future items- Server = The name of the server the activity was recorded on.The only question I have is, can you offer a way to process the totaluser login time during a given range using T-SQL.For Example...Given the table data:ID LogTime Username Type Server1 10-10-2003 8:30:00 Tom Logon SERVER-A2 10-10-2003 8:45:00 Sarah Logon SERVER-A3 10-10-2003 16:45:00 Tom Logoff SERVER-A4 10-10-2003 17:00:00 Sarah Logoff SERVER-A5 10-11-2003 8:30:00 Tom Logon SERVER-A6 10-11-2003 8:45:00 Sarah Logon SERVER-A7 10-11-2003 16:30:00 Sarah Logoff SERVER-A8 10-11-2003 17:15:00 Tom Logoff SERVER-AHow would you receive the output:User Logon Total Time for SERVER-ATom 17.0 hrsSarah 16.0 hrsI know I can handle this type of processing on my ASP.NET front-end,but I'm curious as to how easily it can be done by the database,itself.Thanks in advance for your assistance.
I have a SP SPone. i have optimized that and kept it as SPone_Optimized. i would like to test the both SP's execution time to find out how best the optimized one fares.
i planned to test it as follows
declare @starttime datetime,@endtime datetime declare @count int=0 select @starttime=getdate() while(@i<10000) begin execute SPone_optimized @param='value1' end select @endtime=getdate() select datediff(ms,@stattime,@endtime) 'total_exec_time'
----- for the SP that is before optimize
declare @starttime datetime,@endtime datetime declare @count int=0 select @starttime=getdate() while(@i<10000) begin execute SPone @param='value1' end select @endtime=getdate() select datediff(ms,@stattime,@endtime) 'total_exec_time'
Hi guys,This might be simplest thing, but I am newbie to databases.I need to find out only rows modified within certain time period from adatabase. As I undertand a way out could be adding an where clause forthe time period might be an option, I might be wrong here again.But, wanted to know is there any other option. Can triggers or anyother things help me in this matter.Regards,Abhijeet
Working on a "social networking site" and would like to have something that records a member's interactions within the site.So for instance if a member uploads a photo, I would like to record it and then display it as "[Member] added photo" with a link to the photo. Also, if a friend is accepted as a friend "[Member1] is now friends with [Member2]" and also if a member posts a comment on the forums "[Member] posted message in [ForumName]" with link to post.This is very similar to Facebook's News Feeds and MySpace Friend Updates.The only way I can think of having this is having an Activity table with a field for each different Id I want to record and an associated list of tracked Actions:Activity:idmemberIdactionTypePhotoIdMember2IdForumIdThen there would be the ActionType tableactionIdNameThe biggest problem I see with this solution is that I would need to add a new field in the Activity table for each different type of activity I would like to track. This could get pretty big as I begin to track more and more activity items.Is there some way I can generalize this. - Andy
We have a web-based third-party application that has both background processes and user activity requests running in the same database (SQL Server 2005 SP2). The problem is that a background process will start a long-running transaction and hold an exclusive lock on a few rows in a given table (a small table, <100 rows). The web clients need to scan this same table, but when their "select *" statements get to those locked row(s), the web client queries stall waiting for that exclusive lock to be released. This effectively brings the entire web front end to a halt because all clients must hit this table for each user action. I realize that this is the classic lock condition that multiversioning databases like Oracle, PostgreSQL, SQL Server Compact Edition, and other databases do not suffer because they don't use shared read locks like SQL Server. But since we're on SQL Server for this app, what is the way to get around this problem? Modifying the clients to use WITH (NOLOCK) is not an option... there will be major consistency issues unless the clients run in Read Committed or higher. Any ideas? We could tweak this app if needed. Does SQL Server 2008 introduce multiversioning or at least some mechanism to get around this problem? I did not see it mentioned on the Microsoft site, but maybe I missed it. Thanks in advance.
Hi, I have been struggling trying to design a query that will alow be to select the most recent date in a table and I'm obviously not having much luck
This is basically the table layout, note each employee can have multiple rows with different dates
Employee_ID
Last_Name
First_Name
Evaluation_Date
Evaluation_Score
1
Jones
Tom
01/04/07
40
1
Jones
Tom
01/.12/07
50
1
Jones
Tom
04/01/08
60
2
Smith
Ed
02/14/05
70
2
Smith
Ed
03/18/06
80
3
Brown
John
06/23/04
80
3
Brown
John
12/23/04
79
3
Brown
John
01/07/06
50
3
Brown
John
10/22/08
69
What I'd like to do would be to write some thing that would return the following, just the last date of the evaluation & whatever relevant data is in the table
Employee_ID
Last_Name
First_Name
Evaluation_Date
Evaluation_Score
1
Jones
Tom
04/01/08
60
2
Smith
Ed
03/18/06
80
3
Brown
John
10/22/08
69
I've looked at select distinct and the date operatives with out any success.
I want to use time series algorithm to mine data from my case table and nested table. Case table is Date table, while nested table is the fact table. E.g, I want to predict the monthly sales amount for different region (I have region table related to the fact table), how can I achieve this?
Thanks a lot and I hope it is clear for your help and I am looking forward to hearing from you shortly.
I am confused on key column of case table and key time column of nested table by using Time Series algorithm.
In my case, the case table structure is as below:
Territory key text (the ID is actually dimrisk_key, in this case, I use the name column binding to combine the Territory column of case table Dimrisks),
While the nested table structure is as below:
Cal_month key time (in this case, actually the ID is dimdate_key, again, I used name column bining property to bind the Cal_month to the ID)
So my question is, as the key column of case table has been set to be Territory, as a result, does the model training still cover all the cases (rows) based on the ID of the table?
Also, in the nested table, as the key time column has been set to Cal_month rather than Dimdate_key of the nested table, as a result, would the single series based on the cal_month?
Hope it is clear for your advices and help.
And I am looking forward to hearing from you shortly.
I need to take a temporary table that has various times stored in a text field (4:30 pm, 11:00 am, 5:30 pm, etc.), convert it to miltary time then cast it as an integer with an update statement kind of like:
Update myTable set MovieTime = REPLACE(CONVERT(CHAR(5),GETDATE(),108), ':', '')
how this can be done while my temp table is in session?
I have a script that creates these spin off tables (used for lightning fast access) on a near daily basis. But it is possible the table does not exist for that day - in which the script need to check one day back - so on and so forth until finding the most recently created table.
The script I have written fails with overflow error. I was hoping someone could tell me the correct syntax to accomplish this and also why i recieve this overflow error...
Code:
Dim str_SQL_Asset_1 daysBack = 1 tblFound = FALSE Dim xbl_Holdings_date Dim xMonth Dim xDay xbl_Holdings_date = date() If Len(Month(xbl_Holdings_date))=1 Then xMonth = "0" & Month(xbl_Holdings_date) Else xMonth = Month(xbl_Holdings_date) End If If Len(Day(xbl_Holdings_date))=1 Then xDay = "0" & Day(xbl_Holdings_date) Else xDay = Day(xbl_Holdings_date) End If
IF MM_checkCmd.EOF Then xbl_Holdings_date = (xbl_Holdings_date - daysBack) daysBack = daysBack + 1 tblFound = FALSE ELSE tblFound = TRUE MM_checkCmd.CLOSE() SET MM_checkCmd = Nothing END IF loop
The goal of this snippet is simply to identify and set the value for the existing table name. An example table name would be "xbl_Holdings_2006_12_10".
I could have posted this question in the VB forum but I would like to push as much of the work onto SQL as possible and also - this sort of operation is more likely to be required of a dba imo.
I'm trying to find the primary key on a given table in SQL Server 2000 using SQL. I'm querying the sysobjects table to find a given table, and then querying the sysindexes table. I've ALMOST found what I'm looking for. I see the indexes and columns etc. on the tables in the database, I just don't see the field that indicates that the index is the primary key. Can anyone help? Thanks, Alex
I'm fairly new to sql.I've got a database that all I want to do is view some records fromsome tables. But the thing is, I do not know the table names ortheir relationships.I've got access to the database thru query analyzer. Can someoneassist me in gettign the correct statements that will allow me to findout what tables exist in the database.
When I open a table and need to make a change within a particular record...
How do I find the particular row that I am looking for? I seem to have to scroll through 600 records to find the one I need. I've found a search and replace function but it doesn't seem to work.
Need some suggestions on what to check or look for.My stored proc is not finding the table in the sql db. This is the error...Invalid object name 'tblServRec'I use the same function to pass in an arraylist and the sp name for another sp and that one works ok.The sp is the same as another one except for the table name and that one works ok.The sp works fine on my local machine. It finds the table ok. But when I try it on the server, it doesn't work.I have checked permissions, and they are the same for all tables, even the one that the other sp works ok on the server.Here is the sp if that will help.....CREATE PROCEDURE dbo.deleteServRec @fldNum INT ASBEGIN DECLARE @errCode INT DELETE FROM tblServRec WHERE fldNum = @fldNum SET @errCode = 0 RETURN @errCode HANDLE_APPERR: SET @errCode = 1 RETURN @errCodeENDGOThanks all,Zath
Is there a way to find out when the last modification or change to data in a table occured ? How about the last change of a view ?
We'd like to be able to extract data to another table based on a view. Then, at a later date, check to see if the first table or view has changed since the last extract. If not, then we don't need to re-extract the data, we know it hasn't changed.