Sunday, May 6, 2007

Can we look into LOG file?

In SQL Server 2005 with fn_dblog function you can get more info about what's happening in SQL Server. MS has added a new data like 'partitionid' column for example.

Let us do some testing

USE Demo
--create a table for testing
CREATE TABLE dbo.Test1 (c INT)
--populate the table with data

WITH numbers AS
(SELECT 1 as Num
UNION ALL
SELECT Num + 1 FROM numbers WHERE Num <10)
INSERT INTO dbo.Test1 SELECT Num FROM numbers OPTION(Maxrecursion 10)

--delete the data
DELETE FROM dbo.Test1

In order to identify how many 'DELETE' operation and when does it start? Run the followinq query.

SELECT [Transaction Name],[begin time FROM ::fn_dblog( NULL,NULL)WHERE [Transaction Name] ='DELETE'

Ok, the next step I'd like to know how long does DELETION take? So we have [end time column] for this purpose. We need to write the below query which gets little complicated.

WITH cte_delete
AS
(
SELECT [Transaction Name],[begin time],[end time]
FROM ::fn_dblog( NULL,NULL)d
WHERE [begin time] IS NOT NULL OR [end time]IS NOT NULL
) ,cte_d
AS
(
SELECT [Transaction Name],[begin time],(SELECT MIN([end time]) FROM cte_delete C
WHERE cte_delete.[begin time]<=C.[end time])AS [end time]
FROM cte_delete WHERE [Transaction Name] ='DELETE' OR
[Transaction Name] IS NULL
)
SELECT * FROM cte_d
WHERE [begin time] IS NOT NULL OR [end time]IS NOT NULL

Also take a look at 'Operation' column.

SELECT Operation,AllocUnitName,[Transaction Name],[begin time],[end time]
FROM ::fn_dblog( NULL,NULL)
WHERE Operation ='LOP_DELETE_ROWS' OR [Transaction Name]='DELETE'

To identify which tables are being hit by DELETE operation.

SELECT AllocUnitName, COUNT([Current LSN])
FROM ::fn_dblog(NULL,NULL )
WHERE Operation = N'LOP_DELETE_ROWS'
GROUP BY AllocUnitName

6 comments:

Anonymous said...

Once I played habbo, I did not know how to get strong, someone told me that you must have habbo credits. He gave me some habbo gold, he said that I could buy habbo gold, but I did not have money, then I played it all my spare time. From then on, I got some habbo coins, if I did not continue to play it, I can sell cheap habbo credits to anyone who want.


Once I played hero, I did not know how to get strong, someone told me that you must have hero gold. He gave me some hero online gold, he said that I could buy hero gold, but I did not have money, then I played it all my spare time. From then on, I got some hero online money, if I did not continue to play it, I can sell hero money to anyone who want.

Anonymous said...

Although the game is free to play, we have to cost some shaiya money to buy our favorite equipment. shaiya gold is the important one in the Shaiya Game, when I begin to come into contact with the wonderful Online Game. Every one also likes playing this Shaiya game with some shaiya online gold. Their primary goal of cheap shaiya gold is not damage infliction, but rather keeping foes away from other party members. So I have decided to buy shaiya gold to try playing this game first.
Play rappelz and feel the power and might of rappelz money. See you in game! If yes, then the first step to buy rupees when you play Rappelz is for you. rappelz rupees of Rappelz Online Game has more functions and this Rappelz Game is a very good free game. This rappelz gold of score could go down A LOT if they choose to mess up the classes even more. I would have rated it much cheap rappelz rupees better when it was epic3.

Anonymous said...

Replica B.R.M Watches
Replica Baume & Mercier Watches
Replica Bell & Ross Watches
Replica bmw Watches
Replica breguet Watches
Replica breitling Watches
Replica bvlgari Watches
Replica cartier Watches
Replica chanel Watches
Replica dior Watches
Replica concord Watches
Replica corum Watches
Replica D&G Watches
Replica De Witt Watches
Replica ebel Watches
Replica Emporio Armani Watches
Replica Fendi Watches
Replica ferrari Watches
Replica Fortis Watches

Anonymous said...

Christian Louboutin Pumps
Christian Louboutin Sandals
christian louboutin heels
Christian Louboutin Short Boots
Christian Louboutin Spring Collection
Christian Louboutin Tall Boots
discount Christian Louboutin Boots
discount Christian Louboutin Pumps
discount Christian Louboutin Sandals
discount christian louboutin heels
discount Christian Louboutin Short Boots
discount Christian Louboutin Tall Boots
Christian Louboutin Boots Sale
Christian Louboutin Pumps sale
Christian Louboutin Sandals sale

aiya said...
This comment has been removed by a blog administrator.
nedox said...
This comment has been removed by a blog administrator.