How to know "At what time the command is executed"

Question

How to know "At what time the command is executed"

Santhoshkumar KB

SSCertifiable

Points: 5066
More actions
December 9, 2008 at 7:24 am

#127413

By mistake I executed a command
"TRUNCATE TABLE table_name" in production
At that time, some SQL batches were scheduled to run which used the truncated table.
I know the timings of the SQL batches executed.
I need to know the time of the TRUNCATE command I issued so that I can know whether it had impacted further Batches.
Where should I see the log of commands that I have executed?
Thanks
Thanks,
Santhosh
Human Knowledge Belongs To The World !!

Viewing 10 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply

Roy Ernest SSC-Dedicated Points: 38780 More actions · Answer 1

That is going to be tough. Truncate table does not make any entry in the Log file if I am not mistaken. Do you have traces running that you have set up? If so, maybe you can find it there.

-Roy

Steve Jones - SSC Editor SSC Guru Points: 736254 More actions · Answer 2

http://www.sqlservercentral.com/articles/SQL+Server+2005/64547/

Santhoshkumar KB SSCertifiable Points: 5066 More actions · Answer 3

Thanks jones,

But

I am not the member of sysadmin and I think there is no access for me to see all these things.

As said in articel,

"to check the default trace to see if it is enabled,"

I tried, but got below error message:

Msg 208, Level 16, State 1, Line 1

Invalid object name 'sys.configurations'.

Is there any other way to check "At what time the command got executed"?

Thanks

Thanks,
Santhosh

Human Knowledge Belongs To The World !!

Jack Corbett SSC Guru Points: 184394 More actions · Answer 4

Unless you have a log reader then there probably isn't a way to see it. I don't think that would be included in the default trace that is running, but you can look there. Here is some code to query the default trace:

Select

E.*,

TF.*

From

sys.traces T Cross Apply

::fn_trace_gettable(T.path, default) TF Left Join

sys.trace_events E On

TF.eventclass = E.trace_event_id

Where

T.id = 1

It doesn't look like it logs truncate. You can see statistics being updated/created because of the truncate so you MIGHT be able to get an idea.

Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question

Santhoshkumar KB SSCertifiable Points: 5066 More actions · Answer 5

Roy Ernest (12/9/2008)
That is going to be tough. Truncate table does not make any entry in the Log file if I am not mistaken. Do you have traces running that you have set up? If so, maybe you can find it there.

I am not the member of sysadmin.

I donot know whether the traces are running are not and I donot know how to check them.

Any more help like,

1. where will these logs stored in database and how to see them by querying

2. which are the commands that doesn't leave a trace in log

3. any other related to this

Thanks

Thanks,
Santhosh

Human Knowledge Belongs To The World !!

Jack Corbett SSC Guru Points: 184394 More actions · Answer 6

Roy Ernest (12/9/2008)
That is going to be tough. Truncate table does not make any entry in the Log file if I am not mistaken. Do you have traces running that you have set up? If so, maybe you can find it there.

Truncate is logged.

Basically you need to find a 3rd party log reader like Lumigent's LogExplorer[/url]

In reality you will probably need to restore the database to before the truncate and then re-run all the batch jobs.

Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question

Santhoshkumar KB SSCertifiable Points: 5066 More actions · Answer 7

Truncate is logged.
Basically you need to find a 3rd party log reader like Lumigent's LogExplorer[/url]
In reality you will probably need to restore the database to before the truncate and then re-run all the batch jobs.

You mean to say that there is no way to find the "Time executed" as of now...

Ok fine...Thanks for the information...

I will be more cautious from now before doing anything on production database...

Thanks,
Santhosh

Human Knowledge Belongs To The World !!

Roy Ernest SSC-Dedicated Points: 38780 More actions · Answer 8

Jack Corbett (12/9/2008)
Roy Ernest (12/9/2008)
That is going to be tough. Truncate table does not make any entry in the Log file if I am not mistaken. Do you have traces running that you have set up? If so, maybe you can find it there.
Truncate is logged.
Basically you need to find a 3rd party log reader like Lumigent's LogExplorer[/url]
In reality you will probably need to restore the database to before the truncate and then re-run all the batch jobs.

Thanks for the Info Jack. I did not know that..:-)

-Roy

Jack Corbett SSC Guru Points: 184394 More actions · Answer 9

Santhosh (12/9/2008)
Truncate is logged.
Basically you need to find a 3rd party log reader like Lumigent's LogExplorer[/url]
In reality you will probably need to restore the database to before the truncate and then re-run all the batch jobs.
You mean to say that there is no way to find the "Time executed" as of now...

Not without having some kind of trace running or having a 3rd party log reader.

Ok fine...Thanks for the information...
I will be more cautious from now before doing anything on production database...

Probably a good idea. I normally would not run a truncate against a production database anyway, there is usually a reason the data is in the table.

Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question