Hi guys !
Any body knows that how can i have a log of all SQL Server transactions ?
Thank's
SQL Server is writing every transaction in its own transaction log. There
are couple of 3rd party log readers around. Also, you can use Profiler, a
tool that ships with SQL Server, to catch all T-SQL commands.
Dejan Sarka, SQL Server MVP
Mentor
www.SolidQualityLearning.com
"SQLBIz" <SQLBIz@.discussions.microsoft.com> wrote in message
news:CF84C3CE-FD2F-4746-BB53-B7F275C60E18@.microsoft.com...
> Hi guys !
> Any body knows that how can i have a log of all SQL Server transactions ?
> Thank's
|||Hi
You do have a LOG . Lookup Transaction Log file in the BOL for more details
"SQLBIz" <SQLBIz@.discussions.microsoft.com> wrote in message
news:CF84C3CE-FD2F-4746-BB53-B7F275C60E18@.microsoft.com...
> Hi guys !
> Any body knows that how can i have a log of all SQL Server transactions ?
> Thank's
Showing posts with label transactions. Show all posts
Showing posts with label transactions. Show all posts
Wednesday, March 7, 2012
How can I have a log of all SQL Server transactions ?
Hi guys !
Any body knows that how can i have a log of all SQL Server transactions '
Thank'sSQL Server is writing every transaction in its own transaction log. There
are couple of 3rd party log readers around. Also, you can use Profiler, a
tool that ships with SQL Server, to catch all T-SQL commands.
Dejan Sarka, SQL Server MVP
Mentor
www.SolidQualityLearning.com
"SQLBIz" <SQLBIz@.discussions.microsoft.com> wrote in message
news:CF84C3CE-FD2F-4746-BB53-B7F275C60E18@.microsoft.com...
> Hi guys !
> Any body knows that how can i have a log of all SQL Server transactions '
> Thank's|||Hi
You do have a LOG . Lookup Transaction Log file in the BOL for more details
"SQLBIz" <SQLBIz@.discussions.microsoft.com> wrote in message
news:CF84C3CE-FD2F-4746-BB53-B7F275C60E18@.microsoft.com...
> Hi guys !
> Any body knows that how can i have a log of all SQL Server transactions '
> Thank's
Any body knows that how can i have a log of all SQL Server transactions '
Thank'sSQL Server is writing every transaction in its own transaction log. There
are couple of 3rd party log readers around. Also, you can use Profiler, a
tool that ships with SQL Server, to catch all T-SQL commands.
Dejan Sarka, SQL Server MVP
Mentor
www.SolidQualityLearning.com
"SQLBIz" <SQLBIz@.discussions.microsoft.com> wrote in message
news:CF84C3CE-FD2F-4746-BB53-B7F275C60E18@.microsoft.com...
> Hi guys !
> Any body knows that how can i have a log of all SQL Server transactions '
> Thank's|||Hi
You do have a LOG . Lookup Transaction Log file in the BOL for more details
"SQLBIz" <SQLBIz@.discussions.microsoft.com> wrote in message
news:CF84C3CE-FD2F-4746-BB53-B7F275C60E18@.microsoft.com...
> Hi guys !
> Any body knows that how can i have a log of all SQL Server transactions '
> Thank's
How can I have a log of all SQL Server transactions ?
Hi guys !
Any body knows that how can i have a log of all SQL Server transactions '
Thank'sSQL Server is writing every transaction in its own transaction log. There
are couple of 3rd party log readers around. Also, you can use Profiler, a
tool that ships with SQL Server, to catch all T-SQL commands.
--
Dejan Sarka, SQL Server MVP
Mentor
www.SolidQualityLearning.com
"SQLBIz" <SQLBIz@.discussions.microsoft.com> wrote in message
news:CF84C3CE-FD2F-4746-BB53-B7F275C60E18@.microsoft.com...
> Hi guys !
> Any body knows that how can i have a log of all SQL Server transactions '
> Thank's|||Hi
You do have a LOG . Lookup Transaction Log file in the BOL for more details
"SQLBIz" <SQLBIz@.discussions.microsoft.com> wrote in message
news:CF84C3CE-FD2F-4746-BB53-B7F275C60E18@.microsoft.com...
> Hi guys !
> Any body knows that how can i have a log of all SQL Server transactions '
> Thank's
Any body knows that how can i have a log of all SQL Server transactions '
Thank'sSQL Server is writing every transaction in its own transaction log. There
are couple of 3rd party log readers around. Also, you can use Profiler, a
tool that ships with SQL Server, to catch all T-SQL commands.
--
Dejan Sarka, SQL Server MVP
Mentor
www.SolidQualityLearning.com
"SQLBIz" <SQLBIz@.discussions.microsoft.com> wrote in message
news:CF84C3CE-FD2F-4746-BB53-B7F275C60E18@.microsoft.com...
> Hi guys !
> Any body knows that how can i have a log of all SQL Server transactions '
> Thank's|||Hi
You do have a LOG . Lookup Transaction Log file in the BOL for more details
"SQLBIz" <SQLBIz@.discussions.microsoft.com> wrote in message
news:CF84C3CE-FD2F-4746-BB53-B7F275C60E18@.microsoft.com...
> Hi guys !
> Any body knows that how can i have a log of all SQL Server transactions '
> Thank's
Sunday, February 19, 2012
How can I get a (count) of how many transactions the log reader is wating on?
I need to know if replication is running behind. I have already queried
with distribution db for this subscriber , but it's telling me there are
no transactions waiting.
the agent history on the log reader shows it moving across data to the
distributor normally, but when i try to shrink the translog its telling
me that it can't because there are transactions marked for rpelication
that have not been pushed over yet.
here's what I ran on the distrbutor to get the trasnactions waiting
Select * from dbo.MSpublisher_databases where publisher_db = 'cpk01'
(to get publisher id)
then
Select count(1) from dbo.MSrepl_transactions where
publisher_database_id = 13
(to get transaction count)
tia
-comb
Comb,
querying this view (distribution database) is what you need:
MSdistribution_status.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||In article <Ochx0lzBGHA.140@.TK2MSFTNGP12.phx.gbl>,
Paul.Ibison@.Pygmalion.Com says...
> Comb,
> querying this view (distribution database) is what you need:
> MSdistribution_status.
> Cheers,
> Paul Ibison SQL Server MVP, www.replicationanswers.com
> (recommended sql server 2000 replication book:
> http://www.nwsu.com/0974973602p.html)
>
>
Thanks Paul.
-comb
with distribution db for this subscriber , but it's telling me there are
no transactions waiting.
the agent history on the log reader shows it moving across data to the
distributor normally, but when i try to shrink the translog its telling
me that it can't because there are transactions marked for rpelication
that have not been pushed over yet.
here's what I ran on the distrbutor to get the trasnactions waiting
Select * from dbo.MSpublisher_databases where publisher_db = 'cpk01'
(to get publisher id)
then
Select count(1) from dbo.MSrepl_transactions where
publisher_database_id = 13
(to get transaction count)
tia
-comb
Comb,
querying this view (distribution database) is what you need:
MSdistribution_status.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||In article <Ochx0lzBGHA.140@.TK2MSFTNGP12.phx.gbl>,
Paul.Ibison@.Pygmalion.Com says...
> Comb,
> querying this view (distribution database) is what you need:
> MSdistribution_status.
> Cheers,
> Paul Ibison SQL Server MVP, www.replicationanswers.com
> (recommended sql server 2000 replication book:
> http://www.nwsu.com/0974973602p.html)
>
>
Thanks Paul.
-comb
How can I force some data maintenance statements whether the transactions success or
How can I force some data maintenance statements whether the transactions
success or failure?
The case is as:
When the insert statement to do, the insert trigger will execute, when the
triggers has errors, then the triggers should write the custom messages to
the another tables and then rollback the insert statements. The client
sides will access the another tables gather the messages. How to write this
case?Refer these error handlings
http://www.sommarskog.se/error-handling-I.html
http://www.sommarskog.se/error-handling-II.html
Madhivanan|||Hi, ABC
If you want something logged in a table even when an error appears
(because of a constraint violation, for example), you cannot do this.
If you only want something logged according to a certain condition
which is verified by the trigger, you have two solutions:
a) if it's OK to just ignore the incorrect value, use an "INSTEAD OF"
trigger which updates only the correct values and returns (maybe with a
RAISERROR, but with no ROLLBACK)
b) use a normal trigger, but after RAISERROR and ROLLBACK, insert the
message in the error log table, with something like this:
USE tempdb
SET NOCOUNT ON
GO
CREATE TABLE Employees (
EmployeeID int IDENTITY PRIMARY KEY,
EmployeeName nvarchar(50) NOT NULL UNIQUE,
Salary money NOT NULL CHECK (Salary>0)
)
INSERT INTO Employees VALUES ('Adam', 50000)
INSERT INTO Employees VALUES ('Bob', 70000)
GO
CREATE TABLE ErrorLog (
MessageID int IDENTITY PRIMARY KEY,
EventDate datetime NOT NULL DEFAULT GETDATE(),
UserName varchar(30) NOT NULL DEFAULT SYSTEM_USER,
HostName varchar(30) NOT NULL DEFAULT HOST_NAME(),
Message nvarchar(1000) NOT NULL
)
GO
CREATE TRIGGER Employees_U_SmallRaisesOnly ON Employees
FOR UPDATE
AS
IF @.@.ROWCOUNT>0 AND UPDATE(Salary) BEGIN
SET NOCOUNT ON
IF EXISTS (
SELECT * FROM inserted i
INNER JOIN deleted d ON i.EmployeeID=d.EmployeeID
WHERE i.Salary>d.Salary*2
) BEGIN
DECLARE @.Messages TABLE (Message nvarchar(1000))
INSERT INTO @.Messages (Message)
SELECT 'Someone tried to change the salary for '+i.EmployeeName
+' from '+CONVERT(varchar(23),d.Salary,1)
+' to '+CONVERT(varchar(23),i.Salary,1)+' !'
FROM inserted i
INNER JOIN deleted d ON i.EmployeeID=d.EmployeeID
WHERE i.Salary>d.Salary*2
RAISERROR ('You cannot raise a salary to more than double !',16,1)
ROLLBACK
INSERT INTO ErrorLog (Message)
SELECT Message FROM @.Messages
END
END
GO
UPDATE Employees SET Salary=60000 WHERE EmployeeName='Adam'
GO
SELECT * FROM Employees
SELECT * FROM ErrorLog
GO
UPDATE Employees SET Salary=200000 WHERE EmployeeName='Bob'
GO
SELECT * FROM Employees
SELECT * FROM ErrorLog
GO
DROP TABLE Employees, ErrorLog
Razvan
PS. I don't know if this behaviour is documented and/or supported.|||On Thu, 24 Nov 2005 14:19:17 +0800, ABC wrote:
>How can I force some data maintenance statements whether the transactions
>success or failure?
>The case is as:
>When the insert statement to do, the insert trigger will execute, when the
>triggers has errors, then the triggers should write the custom messages to
>the another tables and then rollback the insert statements. The client
>sides will access the another tables gather the messages. How to write thi
s
>case?
>
Hi ABC,
I know of only two ways to persist something over a ROLLBACK.
If you want to log a single error, best is to use variables:
SET @.ErrorMsg = 'Whatever the error code should be'
SET @.ErrorSrc = 'Something to identify where the error was made'
SET @.ErrorWhen = CURRENT_TIMESTAMP
ROLLBACK TRANSACTION
INSERT INTO Logging (ErrorMsg, ErrorSrc, ErrorWhen)
VALUES (@.ErrorMsg, @.ErrorSrc, @.ErrorWhen)
In this example, the variables are not actually needed since I use
hard-coded strings. But if the error message or error source has to be
taken from inserted or updated rows that will be rolled back, then use
this method.
If you have to log an unknown number of error messages (e.g. a multi-row
update fails and you want to log all rows that vioalte the business
constraint), then use a table variable:
DECLARE @.Errors TABLE(...)
INSERT INTO @.Errors (..., ..., ...)
SELECT ..., ..., ...
FROM inserted
WHERE business rule is violated
ROLLBACK TRANSACTION
INSERT INTO Logging (..., ..., ...)
SELECT ..., ..., ...
FROM @.Errors
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)
success or failure?
The case is as:
When the insert statement to do, the insert trigger will execute, when the
triggers has errors, then the triggers should write the custom messages to
the another tables and then rollback the insert statements. The client
sides will access the another tables gather the messages. How to write this
case?Refer these error handlings
http://www.sommarskog.se/error-handling-I.html
http://www.sommarskog.se/error-handling-II.html
Madhivanan|||Hi, ABC
If you want something logged in a table even when an error appears
(because of a constraint violation, for example), you cannot do this.
If you only want something logged according to a certain condition
which is verified by the trigger, you have two solutions:
a) if it's OK to just ignore the incorrect value, use an "INSTEAD OF"
trigger which updates only the correct values and returns (maybe with a
RAISERROR, but with no ROLLBACK)
b) use a normal trigger, but after RAISERROR and ROLLBACK, insert the
message in the error log table, with something like this:
USE tempdb
SET NOCOUNT ON
GO
CREATE TABLE Employees (
EmployeeID int IDENTITY PRIMARY KEY,
EmployeeName nvarchar(50) NOT NULL UNIQUE,
Salary money NOT NULL CHECK (Salary>0)
)
INSERT INTO Employees VALUES ('Adam', 50000)
INSERT INTO Employees VALUES ('Bob', 70000)
GO
CREATE TABLE ErrorLog (
MessageID int IDENTITY PRIMARY KEY,
EventDate datetime NOT NULL DEFAULT GETDATE(),
UserName varchar(30) NOT NULL DEFAULT SYSTEM_USER,
HostName varchar(30) NOT NULL DEFAULT HOST_NAME(),
Message nvarchar(1000) NOT NULL
)
GO
CREATE TRIGGER Employees_U_SmallRaisesOnly ON Employees
FOR UPDATE
AS
IF @.@.ROWCOUNT>0 AND UPDATE(Salary) BEGIN
SET NOCOUNT ON
IF EXISTS (
SELECT * FROM inserted i
INNER JOIN deleted d ON i.EmployeeID=d.EmployeeID
WHERE i.Salary>d.Salary*2
) BEGIN
DECLARE @.Messages TABLE (Message nvarchar(1000))
INSERT INTO @.Messages (Message)
SELECT 'Someone tried to change the salary for '+i.EmployeeName
+' from '+CONVERT(varchar(23),d.Salary,1)
+' to '+CONVERT(varchar(23),i.Salary,1)+' !'
FROM inserted i
INNER JOIN deleted d ON i.EmployeeID=d.EmployeeID
WHERE i.Salary>d.Salary*2
RAISERROR ('You cannot raise a salary to more than double !',16,1)
ROLLBACK
INSERT INTO ErrorLog (Message)
SELECT Message FROM @.Messages
END
END
GO
UPDATE Employees SET Salary=60000 WHERE EmployeeName='Adam'
GO
SELECT * FROM Employees
SELECT * FROM ErrorLog
GO
UPDATE Employees SET Salary=200000 WHERE EmployeeName='Bob'
GO
SELECT * FROM Employees
SELECT * FROM ErrorLog
GO
DROP TABLE Employees, ErrorLog
Razvan
PS. I don't know if this behaviour is documented and/or supported.|||On Thu, 24 Nov 2005 14:19:17 +0800, ABC wrote:
>How can I force some data maintenance statements whether the transactions
>success or failure?
>The case is as:
>When the insert statement to do, the insert trigger will execute, when the
>triggers has errors, then the triggers should write the custom messages to
>the another tables and then rollback the insert statements. The client
>sides will access the another tables gather the messages. How to write thi
s
>case?
>
Hi ABC,
I know of only two ways to persist something over a ROLLBACK.
If you want to log a single error, best is to use variables:
SET @.ErrorMsg = 'Whatever the error code should be'
SET @.ErrorSrc = 'Something to identify where the error was made'
SET @.ErrorWhen = CURRENT_TIMESTAMP
ROLLBACK TRANSACTION
INSERT INTO Logging (ErrorMsg, ErrorSrc, ErrorWhen)
VALUES (@.ErrorMsg, @.ErrorSrc, @.ErrorWhen)
In this example, the variables are not actually needed since I use
hard-coded strings. But if the error message or error source has to be
taken from inserted or updated rows that will be rolled back, then use
this method.
If you have to log an unknown number of error messages (e.g. a multi-row
update fails and you want to log all rows that vioalte the business
constraint), then use a table variable:
DECLARE @.Errors TABLE(...)
INSERT INTO @.Errors (..., ..., ...)
SELECT ..., ..., ...
FROM inserted
WHERE business rule is violated
ROLLBACK TRANSACTION
INSERT INTO Logging (..., ..., ...)
SELECT ..., ..., ...
FROM @.Errors
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)
Subscribe to:
Posts (Atom)