January 24, 2011 at 1:20 am
Hi
In sql2005 or Sql2008, is there any possibility to find since last month, what r the manipution have done in sql (i.e insert ,update,delete, creation,query running..etc in database makuse of any log file). That i need to track this manually. Can you please guide .
January 24, 2011 at 2:08 am
If u are talking about tracking DML changes been made on the table then i would do the following:
I would have introduced columns named created_date, modified_date in the table. First time insert then created_date and modified_date both would be getdate()
Whenever an existing record is modified then modified_date would be getdate(), created_date remains as it is.
You also have something named EVENT NOTIFICATION which you can use in SQL Server 2005 to accomplish this task.
In SQL Server 2008 R2 you have something named CDC(Change Data Capture) which keeps a track of all the DML operations happened on a table.
Satnam
January 24, 2011 at 2:36 am
Unless you had a trace or trigger present when the changes were made, you cannot go back and get details of what happened.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
January 24, 2011 at 3:24 am
Just i need to know what r the queries ,that we running since last month. I need to track this result
January 24, 2011 at 3:40 am
Unless you've had a server-side trace running for the last month, you cannot get this information. SQL does not keep a log of all queries unless you set one up.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
January 24, 2011 at 3:47 am
Hi,
Actaully i have tried below one, but i got only last one day results only. but i need to past 25 days records.
SELECT deqs.last_execution_time AS [Time], dest.TEXT AS [Query]
FROM sys.dm_exec_query_stats AS deqs
CROSS APPLY sys.dm_exec_sql_text(deqs.sql_handle) AS dest
ORDER BY deqs.last_execution_time DESC
can you please tel how achieve this one
January 24, 2011 at 4:04 am
if you just looking that what were the modification at schema level, like alter/delete or create then in SSMS 2008 you can use the inbuilt report named as 'schema change history' report.
----------
Ashish
January 24, 2011 at 4:06 am
CAn you please give me, bit information about this.
January 24, 2011 at 4:12 am
KMPSSS (1/24/2011)
Hi,Actaully i have tried below one, but i got only last one day results only. but i need to past 25 days records.
That just shows you queries that are currently in the plan cache. There are many reasons why queries won't show up in there at all, or will be cleared, including restarting the SQL instance.
Again, if you need every single query run against the system, you need to have been running a trace over the period you're interested in. SQL does not log that information anywhere by default.
What Ashish is talking about is the default trace. It's present in SQL 2005 and 2008. It's a lightweight trace that keeps track of DDL (data definition) changes and a few other events. It's limited to 5 files of no more than 20 MB each, a restart of SQL will create a new file. There's no guarantee it'll go back a month, depends how often you restart the instance and how busy it is.
For details see http://www.sqlservercentral.com/articles/SQL+Server+2005/64547/
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
January 24, 2011 at 4:13 am
CAn you please give me, bit information about this.
open SSMS -- right click on your database-- reports-- standard reports -- schema change history
----------
Ashish
January 24, 2011 at 4:56 am
Hello crazy4sql, its very helpful to me. Nice one.
I have small doubt in this,here i can only view one month data in that report. Is there any constraints in this one.Can you please give info about this
January 24, 2011 at 5:56 am
KMPSSS (1/24/2011)
I have small doubt in this,here i can only view one month data in that report. Is there any constraints in this one.Can you please give info about this
Are you reading my posts?
What Ashish is talking about is the default trace. It's present in SQL 2005 and 2008. It's a lightweight trace that keeps track of DDL (data definition) changes and a few other events. It's limited to 5 files of no more than 20 MB each, a restart of SQL will create a new file. There's no guarantee it'll go back a month, depends how often you restart the instance and how busy it is.
For details see http://www.sqlservercentral.com/articles/SQL+Server+2005/64547/
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
January 24, 2011 at 8:17 am
I'm guessing not, since you aren't telling him what he wants to hear. Give a tool and a fair amount of time and ALL the transaction log dumps, he MIGHT be able to, but out of the box he's already done.
CEWII
January 24, 2011 at 10:14 am
KMPSSS (1/24/2011)
Hello crazy4sql, its very helpful to me. Nice one.I have small doubt in this,here i can only view one month data in that report. Is there any constraints in this one.Can you please give info about this
The default trace files get overwritten when they are full, so your history only goes back until that point.
For some expert posts on this topic, see this thread :Whistling:
http://www.sqlservercentral.com/Forums/Topic1052214-338-1.aspx
January 24, 2011 at 10:35 am
Elliott Whitlow (1/24/2011)
Give a tool and a fair amount of time and ALL the transaction log dumps, he MIGHT be able to, but out of the box he's already done.
Except for the selects...
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 15 posts - 1 through 15 (of 18 total)
You must be logged in to reply to this topic. Login to reply