Last Transact-SQL Statement Executed

Hello All,
Please how can I retrieve/save the last Transact-SQL
statements executed against a database? Can I use a
trigger for this?
Thanks.Triggers work on tables and will only fire for data
updates not selects.
If all your access is via SPs then you could log in the SP.
You could log in the client if you are in control of that.
Otherwise the easiest way is to use the profiler to log
statements to a table or text file.
>--Original Message--
>Hello All,
>Please how can I retrieve/save the last Transact-SQL
>statements executed against a database? Can I use a
>trigger for this?
>Thanks.
>
>.
>

Last SQL Statement run on SQL Server

Hi All,

Im just wondering if there is any way to get the last SQL Stmt or SP that was run on the server without using the SQL Profiler or third party software.

Thanks and have a nice day to all.No. There is really no easy way to determine this information. And do you want last SQL statement or SP executed by any connection? Or is it the last on the server? In some cases, you could use DBCC INPUTBUFFER with the currently executing or inactive SPIDs but this is not entirely accurate. And if the connections have disconnected from the server then there is really no record. You could look at the cached plan entries but there is no way to relate them in order of time and you will miss statements that were not cached for example. What are you trying to do with this information though? What do you want to determine from knowing the last command that was sent to the server?|||

Thanks for the reply.

Just only wondering if there is any alternate way using sql stmt on SQL Query Analyzer by not using the SQL profiler to get the SQL stmts.

Last Select Statement

Any ideas how I can find out when was the last update/select made in a database in SQL Server? Even if I can find any user login information, that would be useful too.

Regards,

Bala

With the SQL Server Profiler tool, you can monitor querying activity, including who's running the query. But you only get that information when it's running... I don't know of a general activity log SQL Server keeps that you could study.

-Ryan / Kardax

|||

You could try using Lumigent's Log Explorer or Red-Gate's Log Rescue, however these will not help with tracking SELECT statements but will help track modifications to the database's data or structure:

http://www.lumigent.com/products/le_sql.html

http://www.red-gate.com/products/SQL_Log_Rescue/index.htm

Note that the ability to obtain such information from a database's log file is dependent on both the database's recovery model and when the last log backup was taken (if appropriate).

Chris

Last record in 10,000,000 rows

Hey gang, I got a table that has about 10,000,000 row and I need a test of the last 10 rows. Isn't the statement: Select bottom 10 from table?Rows in a table don't have any order. You can order the result set any way you want. Take the TOP 10 with the order "upside down" and you are in business!

-PatP|||SELECT TOP 1 * from test order by id desc
OR
select * from TEST a
where 1>(select count(*) from TEST where ID>a.id)
-SS|||Do you have an ADD_ROW_TS or IDENTITY Column?|||Gentlemen, I dont beleive I've been clear on what I'm attempting to do. I have a table with 10,000,000 records. (which happend to reflect 6 months historical data) that I need to confirm it is there. I can do a 'select top 10 from table" to get the first 10 record of this new table. I need to see the last 10 to confirm the month,date,time etc... What I've come up with in the sql anaylser is
select * from dbo.INCOMING_TEMP
where RECORD_NUMBER > 10453700
according to my records the total number of rows at 10453747

It's running but taking all day to get there.|||I'm assuming RECORD_NUMBER is defined like

RECORD_NUMBER int IDENTITY(1,1) NOT NULL

Yes?

SELECT TOP 10 * FROM yourTable99 ORDER BY RECORD_NUMBER DESC

Will get you the official last 10 records...

If that's truly the case...

No?|||Thanks that works fine. I havent been my self since I got the damn cast on my foot. So bear with me. Not I get to compare radomn samples of the data before blowing the original table away.

Last Query Statement fired into the MSSQL Engine

Hi,

How can you get the Query Statement of the last executed SQL command.

I am not quite sure but I do remember coming across such a command ( maybe an undocumented one).

Thanks for your help and pointers.

My Best wishes for the new year to all the folks in the forum. Wishing you greater days ahead.

Warm Regards,
Ranjit S Hans.

---------------------
Everywhere is a walking distance if you have the time - Steven Wrightdbcc inputbuffer(@.spid)|||You can also use fn_get_sql, but it is a little more awkward.|||Hi Matt/ Jezemine,

Thanks so much for the help .. You have been wonderful all along.

I am looking forward to try out using fn_get_sql sometime soon.

Thanks once again !!

Warm Regards,
Ranjit S Hans
----------------------
Everywhere is a walking distance if you have the time - Steven Wright|||fn_get_sql has a number of benefits over dbcc inputbuffer actually. dbcc cuts off the query returned at 255 chars as I recall. Usually thats enough, unless you have apps out there making ad-hoc sql calls doing a 10-way join. :)

Large sql statement with 5000 insert and update commands

Hi
What can I do to speed up the processing of a large amount of insert
and update commands that are exchanged using a notepad document.
The document has, say 5000, individual sql statements that either
update or insert.
Is there something in the database that can be turned on or off to
speed up this processing.
thanks
NewishBy default, each INSERT is an implicit transaction and thus requires a
synchronous log i/o for each statement. If you specify an explicit BEGIN
TRANSACTION at the beginning of the script and a COMMIT at the end, you will
get improved performance because log i/o is reduced and only the log flush
for the COMMIT is synchronous.
Hope this helps.
Dan Guzman
SQL Server MVP
"Newish" <ahussain3@.gmail.com> wrote in message
news:1160325892.791516.228990@.i3g2000cwc.googlegroups.com...
> Hi
> What can I do to speed up the processing of a large amount of insert
> and update commands that are exchanged using a notepad document.
> The document has, say 5000, individual sql statements that either
> update or insert.
> Is there something in the database that can be turned on or off to
> speed up this processing.
> thanks
> Newish
>

Large sql statement with 5000 insert and update commands

Hi
What can I do to speed up the processing of a large amount of insert
and update commands that are exchanged using a notepad document.
The document has, say 5000, individual sql statements that either
update or insert.
Is there something in the database that can be turned on or off to
speed up this processing.
thanks
Newish
Hello Newish,
If you batch the inserts in a transaction you should get an improvement.
around each 2000 statements do a BEGIN TRANSACTION and a COMMIT TRANSACTION.
Simon Sabin
SQL Server MVP
http://sqlblogcasts.com/blogs/simons

> Hi
> What can I do to speed up the processing of a large amount of insert
> and update commands that are exchanged using a notepad document.
> The document has, say 5000, individual sql statements that either
> update or insert.
> Is there something in the database that can be turned on or off to
> speed up this processing.
> thanks
> Newish
>

Large sql statement with 5000 insert and update commands

Hi
What can I do to speed up the processing of a large amount of insert
and update commands that are exchanged using a notepad document.
The document has, say 5000, individual sql statements that either
update or insert.
Is there something in the database that can be turned on or off to
speed up this processing.
thanks
Newish
By default, each INSERT is an implicit transaction and thus requires a
synchronous log i/o for each statement. If you specify an explicit BEGIN
TRANSACTION at the beginning of the script and a COMMIT at the end, you will
get improved performance because log i/o is reduced and only the log flush
for the COMMIT is synchronous.
Hope this helps.
Dan Guzman
SQL Server MVP
"Newish" <ahussain3@.gmail.com> wrote in message
news:1160325892.791516.228990@.i3g2000cwc.googlegro ups.com...
> Hi
> What can I do to speed up the processing of a large amount of insert
> and update commands that are exchanged using a notepad document.
> The document has, say 5000, individual sql statements that either
> update or insert.
> Is there something in the database that can be turned on or off to
> speed up this processing.
> thanks
> Newish
>

Large sql statement with 5000 insert and update commands

Hi
What can I do to speed up the processing of a large amount of insert
and update commands that are exchanged using a notepad document.
The document has, say 5000, individual sql statements that either
update or insert.
Is there something in the database that can be turned on or off to
speed up this processing.
thanks
NewishHello Newish,
If you batch the inserts in a transaction you should get an improvement.
around each 2000 statements do a BEGIN TRANSACTION and a COMMIT TRANSACTION.
Simon Sabin
SQL Server MVP
http://sqlblogcasts.com/blogs/simons
> Hi
> What can I do to speed up the processing of a large amount of insert
> and update commands that are exchanged using a notepad document.
> The document has, say 5000, individual sql statements that either
> update or insert.
> Is there something in the database that can be turned on or off to
> speed up this processing.
> thanks
> Newish
>

Large sql statement with 5000 insert and update commands

Hi
What can I do to speed up the processing of a large amount of insert
and update commands that are exchanged using a notepad document.
The document has, say 5000, individual sql statements that either
update or insert.
Is there something in the database that can be turned on or off to
speed up this processing.
thanks
NewishBy default, each INSERT is an implicit transaction and thus requires a
synchronous log i/o for each statement. If you specify an explicit BEGIN
TRANSACTION at the beginning of the script and a COMMIT at the end, you will
get improved performance because log i/o is reduced and only the log flush
for the COMMIT is synchronous.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Newish" <ahussain3@.gmail.com> wrote in message
news:1160325892.791516.228990@.i3g2000cwc.googlegroups.com...
> Hi
> What can I do to speed up the processing of a large amount of insert
> and update commands that are exchanged using a notepad document.
> The document has, say 5000, individual sql statements that either
> update or insert.
> Is there something in the database that can be turned on or off to
> speed up this processing.
> thanks
> Newish
>

large INSERTS effects on TempDB?

We have a long running query that inserts a large dynamic sql statement with
lots of joins, into a table.
There appear to be some locking issues around this object. How do I
determine the effect, if any, on tempdb?
Would the Select portion of the Insert create some exclusive locks, or tie
up the tempdb in some way? If so, how do I monitor tempdb size?
Thanks,
Bill Thompson> Would the Select portion of the Insert create some exclusive locks, or tie
> up the tempdb in some way?
Could be that, depending on your query hints. Also, if you are inserting
into a table with a clustered index, there could be a lot of sorting and
data movement going on...
--
http://www.aspfaq.com/
(Reverse address to reply.)

large INSERTS effects on TempDB?

We have a long running query that inserts a large dynamic sql statement with
lots of joins, into a table.
There appear to be some locking issues around this object. How do I
determine the effect, if any, on tempdb?
Would the Select portion of the Insert create some exclusive locks, or tie
up the tempdb in some way? If so, how do I monitor tempdb size?
Thanks,
Bill Thompson
> Would the Select portion of the Insert create some exclusive locks, or tie
> up the tempdb in some way?
Could be that, depending on your query hints. Also, if you are inserting
into a table with a clustered index, there could be a lot of sorting and
data movement going on...
http://www.aspfaq.com/
(Reverse address to reply.)

large INSERTS effects on TempDB?

We have a long running query that inserts a large dynamic sql statement with
lots of joins, into a table.
There appear to be some locking issues around this object. How do I
determine the effect, if any, on tempdb?
Would the Select portion of the Insert create some exclusive locks, or tie
up the tempdb in some way? If so, how do I monitor tempdb size?
Thanks,
Bill Thompson> Would the Select portion of the Insert create some exclusive locks, or tie
> up the tempdb in some way?
Could be that, depending on your query hints. Also, if you are inserting
into a table with a clustered index, there could be a lot of sorting and
data movement going on...
http://www.aspfaq.com/
(Reverse address to reply.)