Determine Date Database Was Last Used ?
Jun 15, 2006
Hi There
I am trying to establish the date a database was last used.
At first i checked out sp_helpfile and sysfiles to see if there was a date last modified, i also tried using xp_cmdshell to check the date on the actual .mdf or .ldf on the o/s but if the file has not grown the date will be the create date.
I need to establish the date a database was last used, either the last time someone logged into it or the last time any sort of command was run against the database, i cannot use sysprocesses as there are no connections to the database, so i need to determine the last time there actually were any processes run against this database.
And i need to use tsql, checking audit logs etc is not viable, can anyone help ?
Thanx
View 6 Replies
ADVERTISEMENT
Feb 1, 2007
Hi All,I have to a few backups restored from a set of backup files. Thebackup files have been removed from the drive because of some storageconstrains...For some data comparison reasons, I need to figure out the actual dateof these backup files that is used to restore these databases. Isthere any system table I can query to figure the actual backup timesof these databases...Any help will be much appreciated.. Thanks in advance.- Aravin
View 1 Replies
View Related
Nov 5, 2015
I have a table that contains the SKU and a period of time the SKU was available to purchase (StartDtKey and EndDtKey). I am trying to write a query determine which skus were available during a given date range. The DtKeys correspond to a date in our calendar table and are in order. So DtKey 5 is 4 days after DtKey 1.
Code:
CREATE TABLE #SKU(
[SKU] varchar(10) NULL,
[StartDtKey] int NULL,
[EndDtKey] int NULL);
INSERT INTO #SKU VALUES ('Sku1',1,9)
,('Sku2',2,6)
,('Sku3',10,20)
,('Sku4',5,9)
,('Sku5',25,30)
Example:
Which SKUs were available on at least one day between DtKey 7 and DtKey 9?
Answer:
Sku1, Sku4
View 1 Replies
View Related
Jun 20, 2014
This is the logic I need to incorporate in to sql
if the getdate() < term start date then R
if the getdate() > term date date and getdate() < term end date then C
if the getdate() > term end date then H
I have come up with the following type of case statement that will allow me to determine the first two values (to a degree), however , I need to evaluate the end date as well.
Is there a way to look at multiple columns in a case statement?
CASE
WHEN DATEDIFF(DD,GETDATE(),TRM_BEGIN_DTE) >0 THEN 'R' else 'C' end
View 5 Replies
View Related
Oct 2, 2006
I need to determine the actual date/time that a message was placed on the queue. In my "activated" procedure I want to log this information and pass it along to further processing routines. From what I can tell, the Queue table itself does not have this information captured.
View 4 Replies
View Related
Nov 7, 2012
Is it possible to determine an index create date in SQL Server 2008 R2? The index in question is not a primary key.
View 8 Replies
View Related
Jul 20, 2005
DECLARE @varDate datetimeDECLARE @varMonthDate datetimeDECLARE @varYear datetimeDECLARE @varFOM varchar(10)DECLARE @NumDaysInMonth as intselect @varDate = '2/1/2004'select @varMonthDate = MONTH(@varDate)select @varYear = YEAR(@varDate)select @varFOM = CAST(CAST(@varMonthDate as int) as varchar) + '/1/' +CAST(CAST(@varYear as int) as varchar)select @NumDaysInMonth = DATEDIFF (day,@varFOM,DATEADD(mm,1,@varFOM))select @NumDaysInMonth as DaysInMonth
View 1 Replies
View Related
May 12, 2015
Given the data below, I have a couple needs:
1) Query to determine if any date ranges overlap (regardless of category, e.g., row ids 6 & 7 below)
2) Query to determine if any date ranges of the same category overlap
declare @t1 table (id int primary key, category int, start_date datetime, end_date datetime)
insert @t1 select 1, 1, '1/1/2015 12:00:00 AM', '1/15/2015 12:59:59 PM'
insert @t1 select 2, 1, '1/16/2015 12:00:00 AM', '1/31/2015 12:59:59 PM'
insert @t1 select 3, 1, '2/1/2015 12:00:00 AM', '2/15/2015 12:59:59 PM'
insert @t1 select 4, 1, '2/16/2015 12:00:00 AM', '2/28/2015 12:59:59 PM'
insert @t1 select 5, 1, '3/1/2015 12:00:00 AM', '3/15/2015 12:59:59 PM'
[code]....
View 7 Replies
View Related
Feb 13, 2008
What is the most reliable way to determine the last LSN of a database? I've looked in sys.database_files to no avail. I've also looked in msdb.dbo.backupset which is accurate but only based on backups already performed not the current state of the database.
-joe
View 5 Replies
View Related
Feb 21, 2001
Problem: I can add 4 items to a SQL 7.0 database but I need to redirect the user if all 4 items exist in the same Record elsewhere in the Database.
Clearly I need some kind of statement like "if category1 = var1 AND category2 = var 2 AND..." but don't know where to put it.
Code:
strSQL = "SELECT * FROM tblUserQuarter"
Set objRS = Server.CreateObject("ADODB.Recordset")
objRS.Open strSQL, objConn,adOpenStatic , adLockOptimistic
'Insert choices from report.asp into tblUserQuarter
objRS.AddNew
objRS("cboFY") = Request.Form("cboFY")
objRS("Quarter") = Request.Form("Quarter")
objRS("ID") = Request.Form("ID")
objRS("Report") = Request.Form("Report")
objRS.Update
objRS.Close
Set objRS = Nothing
objConn.Close
set objConn = Nothing
View 1 Replies
View Related
Feb 25, 2015
I ran a query to see how big my database on my SQL Server is and do not understand the metrics. I want to see how much of my 10GB database is left.
database_namedatabase_sizeunallocated space
ANTHEMQ 6521.06 MB243.55 MB
reserveddata index_sizeunused
1332304 KB1328168 KB1800 KB 2336 KB
View 3 Replies
View Related
Mar 8, 2007
Simple question...
When I use the full version of SQL Management Studio, I can run a report to view the size. How can I do this with SQL Mgmnt Express?
View 1 Replies
View Related
Dec 18, 2007
Does anybody know of a way to determine the last date/time a table has been accessed (query/update)? I've done enough research to know that this isn't easy. However, perhaps somebody has figured out a way through some of the stats that SQL Server keeps to determine the last access of a table. I have recently been put on a team that had no DBA and has a number of databases out there. They would like to determine which databases are inactive and get rid of them. I am a developer and haven't had much SQL Server Administration experience. Any info will help greatly! Thanks!
View 2 Replies
View Related
Sep 17, 2015
I am working on Sql Server 2012. and I have multiple databases there. Out of those, i want to move one of my databases to other SQL server 2012, For that i was trying to get approximate size of my database on current server. As i don't have the admin rights, so i can't get that. Can i get the approximate size by right clicking on database and by using the size property Under Database category to get the size idea?
View 4 Replies
View Related
Dec 31, 2007
I have a SQL database that gets populated by another program. Is there a way to determine when a new record is added to that database?
Thanks
View 4 Replies
View Related
Aug 23, 2007
When executing a stored procedure that is defined in another database, as:
USE db1;
EXEC db2.dbo.sproc;
Is there a way in the stored procedure "sproc" to determine that the caller made the call from db1?
View 1 Replies
View Related
May 14, 2015
Is it possible to determine the data flow within a database? e.g. I start by adding/inserting a record in MainTable1, then through a trigger SubTable1, SubTable2 have records inserted into, SubTable3 has a record updated into, then a StoredProcedure1 is run which updates first SubTable4, then a StoredProcedure2 is run which adds a record in SubTable5. So by simply adding a record in MainTable1, I affect 5 other tables.
Imagine a more complex process than this one now (which I'm currently facing and I want to win time and precision; I will still investigate it myself (if I will find an answer first - I will post it here)) Is there a way I could "extract" this flow by using T-SQL?
View 7 Replies
View Related
Jun 14, 2007
My compatibility settings are fine. Anyone know how to to this?
View 4 Replies
View Related
Jul 31, 2015
I was asked to determine the last time 2 databases were accessed.
Are the .trc files an accurate way to determine the database access history?
View 5 Replies
View Related
May 28, 2015
I use a number of certificates on my database server. I'm trying to verify the correct certificate is being used on the correct database.
View 3 Replies
View Related
Nov 15, 2007
Hi there,
Does anyone know a way (a query perhaps?) to determine which logins have write permissions to a specific database on SQL Server 2005? Ive thought about joining sys.syslogins & sys.sysusers but looking at the columns, not sure which one would render me that info.
Any help is appreciated. Many thanks!
View 1 Replies
View Related
Jun 25, 2007
Hi, all experts here,
Thank you very much for your kind attention.
Just found that my tempdb is always full whenever I run a query against a large database. Could please any experts here give me any advices on what is tempdb database used for and how to determine what files can be deleted from it?
I am looking forward to hearing from you and thanks a lot in advance.
With best regards,
Yours sincerely,
View 3 Replies
View Related
Apr 22, 2015
I was wondering if there was another method to determine when a database was last backed up without using the backup history tables in msdb? whether using DBCC DBINFO, DBCC PAGE on a specific database page...etc.
Also, when restoring a database, is there a trace flag you can use to force restore details to be written to the error log?
View 4 Replies
View Related
Oct 6, 2015
How do I determine the read/write frequency on a database table? I am trying to do this on a 2012 and 2008 R2 servers.
View 2 Replies
View Related
Mar 25, 2008
I need to determine the following about the current authenticated Windows domain user who is trying to access a SQL Server via a trusted connection.
1 Has the current user been granted login access to the trusted SQL Server?
2 Has the current user been granted access to a specific database?
3 Is the current user a member of a specific database role such as (DB_ROLE_ADMINISTRATORS)?
Thanks,
Sean
View 6 Replies
View Related
Jan 30, 2007
I asked this question below, but the answer was that the conversion will take place automatically, but I can't get that to happen. I have a flat file with an 8 position field that I identify as string (and I also tried date) that is yyyymmdd and it needs to go into the database field that is datetime format. IS there something I am doing wrong with the definition of it, or do I need to add some kind of conversion, and if so, what and how would that be done. I'm a dts Sql2000 expert, but the SSIS thing is driving me crazy. I have a ton of dts' to convert and the migration tool doesn't work because there are a lot of active X scripts in them. thanks for your help. Boston Rose
View 1 Replies
View Related
Jun 18, 2007
Brand new to this, so please bear with me.I'm using the following code fragment to update a datetime field on a SQL Server 2005 database table:cmd.CommandText = "Update Projects Set EntryDate = " & Convert.ToDateTime(txtEntryDate.Text)cmd.ExecuteNonQuery()The result of the update operation is the the database field contains the value "1900-01-01 00:00:00:000". This probably means that I passed nulls to SQL; however, I see a valid date in the txtEntryDate field on my web form (i.e., "06/18/2007"). I also did a "Response.write" to display the txtEntryDate and it looks Okay.Can someone tell me what I'm doing wrong?Thanks!Using Visual Web Developer 2005 Express.
View 1 Replies
View Related
Jan 31, 2007
would someone be able to shed some light as to why the date returned to my windows application does not match the date that is actually stored in the database. the products_soldDate field is assigned 1/1/1900 at the product records creation time and is updated when it's sold. when the windows application received that data and I check for it I get the following value 12/31/1899 11:00:00 PManyone has a clue as to what is happening here. It worked fine when I hosted it on a MS SQL 2000 database I just recently moved the data over to a SQL 2005 database
View 2 Replies
View Related
May 6, 2008
hi,
I am getting a data in a dbf file and they have a StartDate and end Date for where the Statments are valid for.. How can i incorporate them into the database..
right now We are doing with PeriodId.. like while the user imports the data they select which period they want to import the data into.. But now if i have to get those details from the Database how do i store it in the Database..and i need to store them in multiple tables..
Thanks
Karen
View 6 Replies
View Related
Mar 24, 2014
The dB dates are store in db as 20140303 when I bring this to my application to pick a date, the apps shows the date as 20140303 how can I change this to show in the format 03/03/2014 in my application?
View 3 Replies
View Related
May 22, 2008
Hi,
Please give the T-SQL script for this ? Thanks
Shanth
View 4 Replies
View Related
Jan 22, 2008
Hi all,
How to determine the value for the bar in Neural Network Viewer? I know the value is displaying in the tooltips when we pointing to the bar in the table but i don't know how to get them. So where can i get those of calculation or data of score, probability of value1 and 2, and lift for value1 and 2 ? Is it get from the Microsoft Neural Network Content Viewer? which column and how to calculate? If not, please advise.
Hope my question is clear.
I am looking forward to hearing from you shortly and thanks a lot in advance.
Thanks!
With best regards,
Yours sincerely,
xuenly
View 8 Replies
View Related
Sep 21, 2006
HiI am using SQL 2005, VB 2005I am trying to insert a record using parameters using the following code as per MotLey suggestion and it works finestring insertSQL; insertSQL = "INSERT INTO Issue(ProjectID, TypeofEntryID, PriorityID ,Title, Area) VALUES (@ProjectID, @TypeofEntryID, @PriorityID ,@Title, @Area)"; cmdInsert SqlCommand; cmdInsert=new SqlCommand(insertSQL,conn); cmdInsert.Parameters.Add("@ProjectID",SqlDbType.Varchar).Value=ProjectID.Text; My query is how to detail with dates my previous code wasinsertSQL += "convert(datetime,'" + DateTime.Now.ToString("dd/MM/yy") + "',3), '";I tried the code below but the record doesn't save?string date = DateTime.Now.ToString("dd/MM/yy"); insertSQL = "INSERT INTO WorkFlow(IssueID, TaskID, TaskDone, Date ,StaffID) VALUES (@IDIssue, @IDTask, @TaskDone, convert(DateTime,@Date,3),@IDStaff)"; cmdInsert.Parameters.Add("IDIssue", SqlDbType.Int).Value = IDIssue.ToString();cmdInsert.Parameters.Add("IDTask",SqlDbType.Int).Value = IDTask.Text;cmdInsert.Parameters.Add("TaskDone",SqlDbType.VarChar).Value = TaskDoneTxtbox.Text;cmdInsert.Parameters.Add("Date",SqlDbType.DateTime).Value = date;cmdInsert.Parameters.Add("IDStaff",SqlDbType.Int).Value = IDStaff.Text;Could someone point to me in the right direction?Thanks in advance
View 3 Replies
View Related
