Hi everybody
I have a process in a SQL Server 2000 database that is blocking itself, is
that normal? The sp_who output looks like this:
Process id: 80
Hostname: XXXXX
user: XXX
..
..
Blkby: 80
I would appreciate any advice.
HaroldIt depends. It can happen and it may or may not be
problematic. Check the following article - this may be what
you are seeing:
The blocked column in the sysprocesses table is populated
for latch waits after you install SQL Server 2000 SP4
http://support.microsoft.com/?id=906344
-Sue
On Mon, 9 Oct 2006 10:19:01 -0700, haRold
<haRold@.discussions.microsoft.com> wrote:
>Hi everybody
>I have a process in a SQL Server 2000 database that is blocking itself, is
>that normal? The sp_who output looks like this:
>Process id: 80
>Hostname: XXXXX
>user: XXX
>..
>..
>Blkby: 80
>I would appreciate any advice.
>Harold
Showing posts with label everybody. Show all posts
Showing posts with label everybody. Show all posts
Wednesday, March 7, 2012
Thursday, February 16, 2012
Blank IE screen
Hi everybody,
I have a problem with our new setup of RS 2005.
I can access de report manager an show reports fine.
I have the Bruce-workaround t.i. a "dummy"-report that refreshes itself
every few minutes to keep connections alive.
Still somtimes when I open up a report I get a blank IE window and have to
wait a full minute for "report is being generated" to appear. Although I ran
a report e few minutes earlier (certainly les than 20 minutes AND my dummy
report is running).
I ran a trace, but I can't really see the problem, could anybody please help.
I added the trace here below starting from the point I reqeusted the report
to the time that the "report is being generated" appeared. I can't add the
complete trace here (to long) I will put the rest in a next post.
Trace Start 2007-05-17 14:06:46.403
ExistingConnection -- network protocol: LPC
set quoted_identifier off
set arithabort off
set numeric_roundabort off
set ansi_warnings on
set ansi_padding on
set ansi_nulls on
set concat_null_yields_null on
set cursor_close_on_commit off
set implicit_transactions off
set language us_english
set dateformat mdy
set datefirst 7
set transaction isolation level read committed
SQLAgent - Generic Refresher adminsql KLINA\adminsql 3232 51 2007-05-04
12:47:40.653
ExistingConnection -- network protocol: LPC
set quoted_identifier on
set arithabort off
set numeric_roundabort off
set ansi_warnings on
set ansi_padding on
set ansi_nulls on
set concat_null_yields_null on
set cursor_close_on_commit off
set implicit_transactions off
set language us_english
set dateformat mdy
set datefirst 7
set transaction isolation level read committed
Report Server adminsql KLINA\adminsql 2056 52 2007-05-17 12:54:29.373
ExistingConnection -- network protocol: TCP/IP
set quoted_identifier on
set arithabort on
set numeric_roundabort off
set ansi_warnings on
set ansi_padding on
set ansi_nulls on
set concat_null_yields_null on
set cursor_close_on_commit off
set implicit_transactions off
set language us_english
set dateformat mdy
set datefirst 7
set transaction isolation level read committed
Toad for SQL Server sa 1364 55 2007-05-17 11:31:23.827
ExistingConnection -- network protocol: TCP/IP
set quoted_identifier off
set arithabort off
set numeric_roundabort off
set ansi_warnings on
set ansi_padding on
set ansi_nulls on
set concat_null_yields_null on
set cursor_close_on_commit off
set implicit_transactions off
set language us_english
set dateformat mdy
set datefirst 7
set transaction isolation level read committed
SQLDMO_1 sa 1364 56 2007-05-17 11:31:25.890
ExistingConnection -- network protocol: LPC
set quoted_identifier off
set arithabort off
set numeric_roundabort off
set ansi_warnings on
set ansi_padding on
set ansi_nulls on
set concat_null_yields_null on
set cursor_close_on_commit off
set implicit_transactions off
set language us_english
set dateformat mdy
set datefirst 7
set transaction isolation level read committed
SQLAgent - Job invocation
engine adminsql KLINA\adminsql 3232 58 2007-05-05 00:10:00.810
ExistingConnection -- network protocol: TCP/IP
set quoted_identifier on
set arithabort off
set numeric_roundabort off
set ansi_warnings on
set ansi_padding on
set ansi_nulls on
set concat_null_yields_null on
set cursor_close_on_commit off
set implicit_transactions off
set language us_english
set dateformat mdy
set datefirst 7
set transaction isolation level read committed
Microsoft® Visual Studio®
2005 vansinan KLINA\vansinan 1920 62 2007-05-10 20:23:44.327
ExistingConnection -- network protocol: LPC
set quoted_identifier on
set arithabort off
set numeric_roundabort off
set ansi_warnings on
set ansi_padding on
set ansi_nulls on
set concat_null_yields_null on
set cursor_close_on_commit off
set implicit_transactions off
set language us_english
set dateformat mdy
set datefirst 7
set transaction isolation level read committed
Report Server adminsql KLINA\adminsql 2056 65 2007-05-17 14:04:29.090
ExistingConnection -- network protocol: LPC
set quoted_identifier on
set arithabort off
set numeric_roundabort off
set ansi_warnings on
set ansi_padding on
set ansi_nulls on
set concat_null_yields_null on
set cursor_close_on_commit off
set implicit_transactions off
set language us_english
set dateformat mdy
set datefirst 7
set transaction isolation level read committed
Report Server adminsql KLINA\adminsql 2056 103 2007-05-17 14:06:42.717
Audit Logout Report
Server adminsql KLINA\adminsql 0 45 0 10000 2056 103 2007-05-17
14:06:42.717 2007-05-17 14:06:52.717
RPC:Completed exec sp_reset_connection Report
Server adminsql KLINA\adminsql 0 0 0 0 2056 103 2007-05-17
14:06:52.717 2007-05-17
14:06:52.717 0X00000000000000002600730070005F00720065007300650074005F0063006F006E006E0065006300740069006F006E00
Audit Login -- network protocol: LPC
set quoted_identifier on
set arithabort off
set numeric_roundabort off
set ansi_warnings on
set ansi_padding on
set ansi_nulls on
set concat_null_yields_null on
set cursor_close_on_commit off
set implicit_transactions off
set language us_english
set dateformat mdy
set datefirst 7
set transaction isolation level read committed
Report Server adminsql KLINA\adminsql 2056 103 2007-05-17 14:06:52.717
SQL:BatchStarting
declare @.BatchID uniqueidentifier
set @.BatchID = NEWID()
UPDATE [Event] WITH (TABLOCKX)
SET [BatchID] = @.BatchID,
[ProcessStart] = GETUTCDATE(),
[ProcessHeartbeat] = GETUTCDATE()
FROM (
SELECT TOP 2 [EventID] FROM
[Event] WITH (TABLOCKX) WHERE [ProcessStart] is NULL ORDER BY [TimeEntered]
) AS t1
WHERE [Event].[EventID] = t1.[EventID]
select top 2
E.[EventID],
E.[EventType],
E.[EventData]
from
[Event] E WITH (TABLOCKX)
where
[BatchID] = @.BatchID
ORDER BY [TimeEntered]
Report
Server adminsql KLINA\adminsql 2056 103 2007-05-17 14:06:52.717
SQL:BatchCompleted
declare @.BatchID uniqueidentifier
set @.BatchID = NEWID()
UPDATE [Event] WITH (TABLOCKX)
SET [BatchID] = @.BatchID,
[ProcessStart] = GETUTCDATE(),
[ProcessHeartbeat] = GETUTCDATE()
FROM (
SELECT TOP 2 [EventID] FROM
[Event] WITH (TABLOCKX) WHERE [ProcessStart] is NULL ORDER BY [TimeEntered]
) AS t1
WHERE [Event].[EventID] = t1.[EventID]
select top 2
E.[EventID],
E.[EventType],
E.[EventData]
from
[Event] E WITH (TABLOCKX)
where
[BatchID] = @.BatchID
ORDER BY [TimeEntered]
Report
Server adminsql KLINA\adminsql 0 0 0 0 2056 103 2007-05-17
14:06:52.717 2007-05-17 14:06:52.717
SQL:BatchStarting
declare @.BatchID uniqueidentifier
set @.BatchID = newid()
UPDATE [Notifications] WITH (TABLOCKX)
SET [BatchID] = @.BatchID,
[ProcessStart] = GETUTCDATE(),
[ProcessHeartbeat] = GETUTCDATE()
FROM (
SELECT TOP 2 [NotificationID] FROM
[Notifications] WITH (TABLOCKX) WHERE ProcessStart is NULL and
(ProcessAfter is NULL or ProcessAfter <
GETUTCDATE()) ORDER BY [NotificationEntered]
) AS t1
WHERE [Notifications].[NotificationID] = t1.[NotificationID]
select top 2
-- Notification data
N.[NotificationID],
N.[SubscriptionID],
N.[ActivationID],
N.[ReportID],
N.[SnapShotDate],
N.[DeliveryExtension],
N.[ExtensionSettings],
N.[Locale],
N.[Parameters],
N.[SubscriptionLastRunTime],
N.[ProcessStart],
N.[NotificationEntered],
N.[Attempt],
N.[IsDataDriven],
SUSER_SNAME(Owner.[Sid]),
Owner.[UserName],
-- Report Data
O.[Path],
O.[Type],
SD.NtSecDescPrimary,
N.[Version],
Owner.[AuthType]
from
[Notifications] N with (TABLOCKX)
inner join [Catalog] O on O.[ItemID] = N.[ReportID]
inner join [Users] Owner on
N.SubscriptionOwnerID = Owner.UserID
left outer join [SecData] SD on
O.[PolicyID] = SD.[PolicyID] AND SD.AuthType = Owner.AuthType
where
N.[BatchID] = @.BatchID
ORDER BY [NotificationEntered]
Report
Server adminsql KLINA\adminsql 2056 103 2007-05-17 14:06:52.717
SQL:BatchCompleted
declare @.BatchID uniqueidentifier
set @.BatchID = newid()
UPDATE [Notifications] WITH (TABLOCKX)
SET [BatchID] = @.BatchID,
[ProcessStart] = GETUTCDATE(),
[ProcessHeartbeat] = GETUTCDATE()
FROM (
SELECT TOP 2 [NotificationID] FROM
[Notifications] WITH (TABLOCKX) WHERE ProcessStart is NULL and
(ProcessAfter is NULL or ProcessAfter <
GETUTCDATE()) ORDER BY [NotificationEntered]
) AS t1
WHERE [Notifications].[NotificationID] = t1.[NotificationID]
select top 2
-- Notification data
N.[NotificationID],
N.[SubscriptionID],
N.[ActivationID],
N.[ReportID],
N.[SnapShotDate],
N.[DeliveryExtension],
N.[ExtensionSettings],
N.[Locale],
N.[Parameters],
N.[SubscriptionLastRunTime],
N.[ProcessStart],
N.[NotificationEntered],
N.[Attempt],
N.[IsDataDriven],
SUSER_SNAME(Owner.[Sid]),
Owner.[UserName],
-- Report Data
O.[Path],
O.[Type],
SD.NtSecDescPrimary,
N.[Version],
Owner.[AuthType]
from
[Notifications] N with (TABLOCKX)
inner join [Catalog] O on O.[ItemID] = N.[ReportID]
inner join [Users] Owner on
N.SubscriptionOwnerID = Owner.UserID
left outer join [SecData] SD on
O.[PolicyID] = SD.[PolicyID] AND SD.AuthType = Owner.AuthType
where
N.[BatchID] = @.BatchID
ORDER BY [NotificationEntered]
Report
Server adminsql KLINA\adminsql 0 0 0 0 2056 103 2007-05-17
14:06:52.717 2007-05-17 14:06:52.717
Audit Logout Report
Server adminsql KLINA\adminsql 0 45 0 10250 2056 103 2007-05-17
14:06:52.717 2007-05-17 14:07:02.967
RPC:Completed exec sp_reset_connection Report
Server adminsql KLINA\adminsql 0 0 0 0 2056 103 2007-05-17
14:07:02.967 2007-05-17
14:07:02.967 0X00000000000000002600730070005F00720065007300650074005F0063006F006E006E0065006300740069006F006E00
Audit Login -- network protocol: LPC
set quoted_identifier on
set arithabort off
set numeric_roundabort off
set ansi_warnings on
set ansi_padding on
set ansi_nulls on
set concat_null_yields_null on
set cursor_close_on_commit off
set implicit_transactions off
set language us_english
set dateformat mdy
set datefirst 7
set transaction isolation level read committed
Report Server adminsql KLINA\adminsql 2056 103 2007-05-17 14:07:02.967
SQL:BatchStarting
declare @.BatchID uniqueidentifier
set @.BatchID = NEWID()
UPDATE [Event] WITH (TABLOCKX)
SET [BatchID] = @.BatchID,
[ProcessStart] = GETUTCDATE(),
[ProcessHeartbeat] = GETUTCDATE()
FROM (
SELECT TOP 2 [EventID] FROM
[Event] WITH (TABLOCKX) WHERE [ProcessStart] is NULL ORDER BY [TimeEntered]
) AS t1
WHERE [Event].[EventID] = t1.[EventID]
select top 2
E.[EventID],
E.[EventType],
E.[EventData]
from
[Event] E WITH (TABLOCKX)
where
[BatchID] = @.BatchID
ORDER BY [TimeEntered]
Report
Server adminsql KLINA\adminsql 2056 103 2007-05-17 14:07:02.967
SQL:BatchCompleted
declare @.BatchID uniqueidentifier
set @.BatchID = NEWID()
UPDATE [Event] WITH (TABLOCKX)
SET [BatchID] = @.BatchID,
[ProcessStart] = GETUTCDATE(),
[ProcessHeartbeat] = GETUTCDATE()
FROM (
SELECT TOP 2 [EventID] FROM
[Event] WITH (TABLOCKX) WHERE [ProcessStart] is NULL ORDER BY [TimeEntered]
) AS t1
WHERE [Event].[EventID] = t1.[EventID]
select top 2
E.[EventID],
E.[EventType],
E.[EventData]
from
[Event] E WITH (TABLOCKX)
where
[BatchID] = @.BatchID
ORDER BY [TimeEntered]
Report
Server adminsql KLINA\adminsql 0 0 0 0 2056 103 2007-05-17
14:07:02.967 2007-05-17 14:07:02.967
SQL:BatchStarting
declare @.BatchID uniqueidentifier
set @.BatchID = newid()
UPDATE [Notifications] WITH (TABLOCKX)
SET [BatchID] = @.BatchID,
[ProcessStart] = GETUTCDATE(),
[ProcessHeartbeat] = GETUTCDATE()
FROM (
SELECT TOP 2 [NotificationID] FROM
[Notifications] WITH (TABLOCKX) WHERE ProcessStart is NULL and
(ProcessAfter is NULL or ProcessAfter <
GETUTCDATE()) ORDER BY [NotificationEntered]
) AS t1
WHERE [Notifications].[NotificationID] = t1.[NotificationID]
select top 2
-- Notification data
N.[NotificationID],
N.[SubscriptionID],
N.[ActivationID],
N.[ReportID],
N.[SnapShotDate],
N.[DeliveryExtension],
N.[ExtensionSettings],
N.[Locale],
N.[Parameters],
N.[SubscriptionLastRunTime],
N.[ProcessStart],
N.[NotificationEntered],
N.[Attempt],
N.[IsDataDriven],
SUSER_SNAME(Owner.[Sid]),
Owner.[UserName],
-- Report Data
O.[Path],
O.[Type],
SD.NtSecDescPrimary,
N.[Version],
Owner.[AuthType]
from
[Notifications] N with (TABLOCKX)
inner join [Catalog] O on O.[ItemID] = N.[ReportID]
inner join [Users] Owner on
N.SubscriptionOwnerID = Owner.UserID
left outer join [SecData] SD on
O.[PolicyID] = SD.[PolicyID] AND SD.AuthType = Owner.AuthType
where
N.[BatchID] = @.BatchID
ORDER BY [NotificationEntered]
Report
Server adminsql KLINA\adminsql 2056 103 2007-05-17 14:07:02.967
SQL:BatchCompleted
declare @.BatchID uniqueidentifier
set @.BatchID = newid()
UPDATE [Notifications] WITH (TABLOCKX)
SET [BatchID] = @.BatchID,
[ProcessStart] = GETUTCDATE(),
[ProcessHeartbeat] = GETUTCDATE()
FROM (
SELECT TOP 2 [NotificationID] FROM
[Notifications] WITH (TABLOCKX) WHERE ProcessStart is NULL and
(ProcessAfter is NULL or ProcessAfter <
GETUTCDATE()) ORDER BY [NotificationEntered]
) AS t1
WHERE [Notifications].[NotificationID] = t1.[NotificationID]
select top 2
-- Notification data
N.[NotificationID],
N.[SubscriptionID],
N.[ActivationID],
N.[ReportID],
N.[SnapShotDate],
N.[DeliveryExtension],
N.[ExtensionSettings],
N.[Locale],
N.[Parameters],
N.[SubscriptionLastRunTime],
N.[ProcessStart],
N.[NotificationEntered],
N.[Attempt],
N.[IsDataDriven],
SUSER_SNAME(Owner.[Sid]),
Owner.[UserName],
-- Report Data
O.[Path],
O.[Type],
SD.NtSecDescPrimary,
N.[Version],
Owner.[AuthType]
from
[Notifications] N with (TABLOCKX)
inner join [Catalog] O on O.[ItemID] = N.[ReportID]
inner join [Users] Owner on
N.SubscriptionOwnerID = Owner.UserID
left outer join [SecData] SD on
O.[PolicyID] = SD.[PolicyID] AND SD.AuthType = Owner.AuthType
where
N.[BatchID] = @.BatchID
ORDER BY [NotificationEntered]
Report
Server adminsql KLINA\adminsql 0 0 0 0 2056 103 2007-05-17
14:07:02.967 2007-05-17 14:07:02.967Here's the rest of it:
Audit Logout Report
Server adminsql KLINA\adminsql 0 45 0 10000 2056 103 2007-05-17
14:07:02.967 2007-05-17 14:07:12.967
RPC:Completed exec sp_reset_connection Report
Server adminsql KLINA\adminsql 0 0 0 0 2056 103 2007-05-17
14:07:12.967 2007-05-17
14:07:12.967 0X00000000000000002600730070005F00720065007300650074005F0063006F006E006E0065006300740069006F006E00
Audit Login -- network protocol: LPC
set quoted_identifier on
set arithabort off
set numeric_roundabort off
set ansi_warnings on
set ansi_padding on
set ansi_nulls on
set concat_null_yields_null on
set cursor_close_on_commit off
set implicit_transactions off
set language us_english
set dateformat mdy
set datefirst 7
set transaction isolation level read committed
Report Server adminsql KLINA\adminsql 2056 103 2007-05-17 14:07:12.967
SQL:BatchStarting
declare @.BatchID uniqueidentifier
set @.BatchID = NEWID()
UPDATE [Event] WITH (TABLOCKX)
SET [BatchID] = @.BatchID,
[ProcessStart] = GETUTCDATE(),
[ProcessHeartbeat] =GETUTCDATE()
FROM (
SELECT TOP 2 [EventID] FROM
[Event] WITH (TABLOCKX) WHERE [ProcessStart] is NULL ORDER BY [TimeEntered]
) AS t1
WHERE [Event].[EventID] =t1.[EventID]
select top 2
E.[EventID],
E.[EventType],
E.[EventData]
from
[Event] E WITH (TABLOCKX)
where
[BatchID] = @.BatchID
ORDER BY [TimeEntered]
Report
Server adminsql KLINA\adminsql 2056 103 2007-05-17 14:07:12.967
SQL:BatchCompleted
declare @.BatchID uniqueidentifier
set @.BatchID = NEWID()
UPDATE [Event] WITH (TABLOCKX)
SET [BatchID] = @.BatchID,
[ProcessStart] = GETUTCDATE(),
[ProcessHeartbeat] =GETUTCDATE()
FROM (
SELECT TOP 2 [EventID] FROM
[Event] WITH (TABLOCKX) WHERE [ProcessStart] is NULL ORDER BY [TimeEntered]
) AS t1
WHERE [Event].[EventID] =t1.[EventID]
select top 2
E.[EventID],
E.[EventType],
E.[EventData]
from
[Event] E WITH (TABLOCKX)
where
[BatchID] = @.BatchID
ORDER BY [TimeEntered]
Report
Server adminsql KLINA\adminsql 0 0 0 0 2056 103 2007-05-17
14:07:12.967 2007-05-17 14:07:12.967
SQL:BatchStarting
declare @.BatchID uniqueidentifier
set @.BatchID = newid()
UPDATE [Notifications] WITH (TABLOCKX)
SET [BatchID] = @.BatchID,
[ProcessStart] = GETUTCDATE(),
[ProcessHeartbeat] = GETUTCDATE()
FROM (
SELECT TOP 2 [NotificationID] FROM
[Notifications] WITH (TABLOCKX) WHERE ProcessStart is NULL and
(ProcessAfter is NULL or ProcessAfter <
GETUTCDATE()) ORDER BY [NotificationEntered]
) AS t1
WHERE [Notifications].[NotificationID] =t1.[NotificationID]
select top 2
-- Notification data
N.[NotificationID],
N.[SubscriptionID],
N.[ActivationID],
N.[ReportID],
N.[SnapShotDate],
N.[DeliveryExtension],
N.[ExtensionSettings],
N.[Locale],
N.[Parameters],
N.[SubscriptionLastRunTime],
N.[ProcessStart],
N.[NotificationEntered],
N.[Attempt],
N.[IsDataDriven],
SUSER_SNAME(Owner.[Sid]),
Owner.[UserName],
-- Report Data
O.[Path],
O.[Type],
SD.NtSecDescPrimary,
N.[Version],
Owner.[AuthType]
from
[Notifications] N with (TABLOCKX)
inner join [Catalog] O on O.[ItemID] = N.[ReportID]
inner join [Users] Owner on
N.SubscriptionOwnerID = Owner.UserID
left outer join [SecData] SD on
O.[PolicyID] = SD.[PolicyID] AND SD.AuthType = Owner.AuthType
where
N.[BatchID] = @.BatchID
ORDER BY [NotificationEntered]
Report
Server adminsql KLINA\adminsql 2056 103 2007-05-17 14:07:12.967
SQL:BatchCompleted
declare @.BatchID uniqueidentifier
set @.BatchID = newid()
UPDATE [Notifications] WITH (TABLOCKX)
SET [BatchID] = @.BatchID,
[ProcessStart] = GETUTCDATE(),
[ProcessHeartbeat] = GETUTCDATE()
FROM (
SELECT TOP 2 [NotificationID] FROM
[Notifications] WITH (TABLOCKX) WHERE ProcessStart is NULL and
(ProcessAfter is NULL or ProcessAfter <
GETUTCDATE()) ORDER BY [NotificationEntered]
) AS t1
WHERE [Notifications].[NotificationID] =t1.[NotificationID]
select top 2
-- Notification data
N.[NotificationID],
N.[SubscriptionID],
N.[ActivationID],
N.[ReportID],
N.[SnapShotDate],
N.[DeliveryExtension],
N.[ExtensionSettings],
N.[Locale],
N.[Parameters],
N.[SubscriptionLastRunTime],
N.[ProcessStart],
N.[NotificationEntered],
N.[Attempt],
N.[IsDataDriven],
SUSER_SNAME(Owner.[Sid]),
Owner.[UserName],
-- Report Data
O.[Path],
O.[Type],
SD.NtSecDescPrimary,
N.[Version],
Owner.[AuthType]
from
[Notifications] N with (TABLOCKX)
inner join [Catalog] O on O.[ItemID] = N.[ReportID]
inner join [Users] Owner on
N.SubscriptionOwnerID = Owner.UserID
left outer join [SecData] SD on
O.[PolicyID] = SD.[PolicyID] AND SD.AuthType = Owner.AuthType
where
N.[BatchID] = @.BatchID
ORDER BY [NotificationEntered]
Report
Server adminsql KLINA\adminsql 0 0 0 0 2056 103 2007-05-17
14:07:12.967 2007-05-17 14:07:12.967
Audit Logout Report
Server adminsql KLINA\adminsql 0 9082 39 173876 2056 65 2007-05-17
14:04:29.090 2007-05-17 14:07:22.967
RPC:Completed exec sp_reset_connection Report
Server adminsql KLINA\adminsql 0 0 0 0 2056 65 2007-05-17
14:07:22.967 2007-05-17
14:07:22.967 0X00000000000000002600730070005F00720065007300650074005F0063006F006E006E0065006300740069006F006E00
Audit Login -- network protocol: LPC
set quoted_identifier on
set arithabort off
set numeric_roundabort off
set ansi_warnings on
set ansi_padding on
set ansi_nulls on
set concat_null_yields_null on
set cursor_close_on_commit off
set implicit_transactions off
set language us_english
set dateformat mdy
set datefirst 7
set transaction isolation level read committed
Report Server adminsql KLINA\adminsql 2056 65 2007-05-17 14:07:22.967
SQL:BatchStarting
declare @.BatchID uniqueidentifier
set @.BatchID = NEWID()
UPDATE [Event] WITH (TABLOCKX)
SET [BatchID] = @.BatchID,
[ProcessStart] = GETUTCDATE(),
[ProcessHeartbeat] =GETUTCDATE()
FROM (
SELECT TOP 2 [EventID] FROM
[Event] WITH (TABLOCKX) WHERE [ProcessStart] is NULL ORDER BY [TimeEntered]
) AS t1
WHERE [Event].[EventID] =t1.[EventID]
select top 2
E.[EventID],
E.[EventType],
E.[EventData]
from
[Event] E WITH (TABLOCKX)
where
[BatchID] = @.BatchID
ORDER BY [TimeEntered]
Report
Server adminsql KLINA\adminsql 2056 65 2007-05-17 14:07:22.967
SQL:BatchCompleted
declare @.BatchID uniqueidentifier
set @.BatchID = NEWID()
UPDATE [Event] WITH (TABLOCKX)
SET [BatchID] = @.BatchID,
[ProcessStart] = GETUTCDATE(),
[ProcessHeartbeat] =GETUTCDATE()
FROM (
SELECT TOP 2 [EventID] FROM
[Event] WITH (TABLOCKX) WHERE [ProcessStart] is NULL ORDER BY [TimeEntered]
) AS t1
WHERE [Event].[EventID] =t1.[EventID]
select top 2
E.[EventID],
E.[EventType],
E.[EventData]
from
[Event] E WITH (TABLOCKX)
where
[BatchID] = @.BatchID
ORDER BY [TimeEntered]
Report
Server adminsql KLINA\adminsql 0 0 0 0 2056 65 2007-05-17
14:07:22.967 2007-05-17 14:07:22.967
SQL:BatchStarting
declare @.BatchID uniqueidentifier
set @.BatchID = newid()
UPDATE [Notifications] WITH (TABLOCKX)
SET [BatchID] = @.BatchID,
[ProcessStart] = GETUTCDATE(),
[ProcessHeartbeat] = GETUTCDATE()
FROM (
SELECT TOP 2 [NotificationID] FROM
[Notifications] WITH (TABLOCKX) WHERE ProcessStart is NULL and
(ProcessAfter is NULL or ProcessAfter <
GETUTCDATE()) ORDER BY [NotificationEntered]
) AS t1
WHERE [Notifications].[NotificationID] =t1.[NotificationID]
select top 2
-- Notification data
N.[NotificationID],
N.[SubscriptionID],
N.[ActivationID],
N.[ReportID],
N.[SnapShotDate],
N.[DeliveryExtension],
N.[ExtensionSettings],
N.[Locale],
N.[Parameters],
N.[SubscriptionLastRunTime],
N.[ProcessStart],
N.[NotificationEntered],
N.[Attempt],
N.[IsDataDriven],
SUSER_SNAME(Owner.[Sid]),
Owner.[UserName],
-- Report Data
O.[Path],
O.[Type],
SD.NtSecDescPrimary,
N.[Version],
Owner.[AuthType]
from
[Notifications] N with (TABLOCKX)
inner join [Catalog] O on O.[ItemID] = N.[ReportID]
inner join [Users] Owner on
N.SubscriptionOwnerID = Owner.UserID
left outer join [SecData] SD on
O.[PolicyID] = SD.[PolicyID] AND SD.AuthType = Owner.AuthType
where
N.[BatchID] = @.BatchID
ORDER BY [NotificationEntered]
Report
Server adminsql KLINA\adminsql 2056 65 2007-05-17 14:07:22.967
SQL:BatchCompleted
declare @.BatchID uniqueidentifier
set @.BatchID = newid()
UPDATE [Notifications] WITH (TABLOCKX)
SET [BatchID] = @.BatchID,
[ProcessStart] = GETUTCDATE(),
[ProcessHeartbeat] = GETUTCDATE()
FROM (
SELECT TOP 2 [NotificationID] FROM
[Notifications] WITH (TABLOCKX) WHERE ProcessStart is NULL and
(ProcessAfter is NULL or ProcessAfter <
GETUTCDATE()) ORDER BY [NotificationEntered]
) AS t1
WHERE [Notifications].[NotificationID] =t1.[NotificationID]
select top 2
-- Notification data
N.[NotificationID],
N.[SubscriptionID],
N.[ActivationID],
N.[ReportID],
N.[SnapShotDate],
N.[DeliveryExtension],
N.[ExtensionSettings],
N.[Locale],
N.[Parameters],
N.[SubscriptionLastRunTime],
N.[ProcessStart],
N.[NotificationEntered],
N.[Attempt],
N.[IsDataDriven],
SUSER_SNAME(Owner.[Sid]),
Owner.[UserName],
-- Report Data
O.[Path],
O.[Type],
SD.NtSecDescPrimary,
N.[Version],
Owner.[AuthType]
from
[Notifications] N with (TABLOCKX)
inner join [Catalog] O on O.[ItemID] = N.[ReportID]
inner join [Users] Owner on
N.SubscriptionOwnerID = Owner.UserID
left outer join [SecData] SD on
O.[PolicyID] = SD.[PolicyID] AND SD.AuthType = Owner.AuthType
where
N.[BatchID] = @.BatchID
ORDER BY [NotificationEntered]
Report
Server adminsql KLINA\adminsql 0 0 0 0 2056 65 2007-05-17
14:07:22.967 2007-05-17 14:07:22.967
Audit Logout Report
Server adminsql KLINA\adminsql 0 9082 39 4970 2056 65 2007-05-17
14:07:22.967 2007-05-17 14:07:27.937
RPC:Completed exec sp_reset_connection Report
Server adminsql KLINA\adminsql 0 0 0 0 2056 65 2007-05-17
14:07:27.937 2007-05-17
14:07:27.937 0X00000000000000002600730070005F00720065007300650074005F0063006F006E006E0065006300740069006F006E00
Audit Login -- network protocol: LPC
set quoted_identifier on
set arithabort off
set numeric_roundabort off
set ansi_warnings on
set ansi_padding on
set ansi_nulls on
set concat_null_yields_null on
set cursor_close_on_commit off
set implicit_transactions off
set language us_english
set dateformat mdy
set datefirst 7
set transaction isolation level read committed
Report Server adminsql KLINA\adminsql 2056 65 2007-05-17 14:07:27.937
RPC:Completed exec GetMyRunningJobs
@.ComputerName=N'REPORTSRV',@.JobType=1 Report
Server adminsql KLINA\adminsql 0 2 0 0 2056 65 2007-05-17
14:07:27.937 2007-05-17
14:07:27.937 0X000000000300000020004700650074004D007900520075006E006E0069006E0067004A006F00620073005200000082001600E7306E0076006100720063006800610072002800390029001A00400043006F006D00700075007400650072004E0061006D006500120000005200450050004F00520054005300520056002E00000002001000341073006D0061006C006C0069006E007400100040004A006F006200540079007000650001001400000003000600380469006E00740000000000
Audit Login -- network protocol: LPC
set quoted_identifier on
set arithabort off
set numeric_roundabort off
set ansi_warnings on
set ansi_padding on
set ansi_nulls on
set concat_null_yields_null on
set cursor_close_on_commit off
set implicit_transactions off
set language us_english
set dateformat mdy
set datefirst 7
set transaction isolation level read committed
Report Server NETWORK SERVICE NT AUTHORITY\NETWORK
SERVICE 5324 53 2007-05-17 14:07:28.140
RPC:Completed exec GetOneConfigurationInfo
@.Name=N'SharePointIntegrated' Report Server NETWORK SERVICE NT
AUTHORITY\NETWORK SERVICE 0 2 0 0 5324 53 2007-05-17 14:07:28.140 2007-05-17
14:07:28.140 0X00000000020000002E004700650074004F006E00650043006F006E00660069006700750072006100740069006F006E0049006E0066006F005A00000082001800E7306E00760061007200630068006100720028003200300029000A0040004E0061006D006500280000005300680061007200650050006F0069006E00740049006E00740065006700720061007400650064001400000003000600380469006E00740000000000
Audit Logout Report Server NETWORK SERVICE NT AUTHORITY\NETWORK
SERVICE 0 2 0 76 5324 53 2007-05-17 14:07:28.140 2007-05-17 14:07:28.217
RPC:Completed exec sp_reset_connection Report Server NETWORK SERVICE NT
AUTHORITY\NETWORK SERVICE 0 0 0 0 5324 53 2007-05-17 14:07:28.217 2007-05-17
14:07:28.217 0X00000000000000002600730070005F00720065007300650074005F0063006F006E006E0065006300740069006F006E00
Audit Login -- network protocol: LPC
set quoted_identifier on
set arithabort off
set numeric_roundabort off
set ansi_warnings on
set ansi_padding on
set ansi_nulls on
set concat_null_yields_null on
set cursor_close_on_commit off
set implicit_transactions off
set language us_english
set dateformat mdy
set datefirst 7
set transaction isolation level read committed
Report Server NETWORK SERVICE NT AUTHORITY\NETWORK
SERVICE 5324 53 2007-05-17 14:07:28.217
SQL:BatchStarting SELECT SERVERPROPERTY('Edition') Report Server NETWORK
SERVICE NT AUTHORITY\NETWORK SERVICE 5324 53 2007-05-17 14:07:28.217
SQL:BatchCompleted SELECT SERVERPROPERTY('Edition') Report Server NETWORK
SERVICE NT AUTHORITY\NETWORK SERVICE 0 0 0 0 5324 53 2007-05-17
14:07:28.217 2007-05-17 14:07:28.217
RPC:Completed exec GetOneConfigurationInfo @.Name=N'EnableMyReports' Report
Server NETWORK SERVICE NT AUTHORITY\NETWORK
SERVICE 0 2 0 0 5324 53 2007-05-17 14:07:28.233 2007-05-17
14:07:28.233 0X00000000020000002E004700650074004F006E00650043006F006E00660069006700750072006100740069006F006E0049006E0066006F005000000082001800E7306E00760061007200630068006100720028003100350029000A0040004E0061006D0065001E00000045006E00610062006C0065004D0079005200650070006F007200740073001400000003000600380469006E00740000000000
Audit Logout Report Server NETWORK SERVICE NT AUTHORITY\NETWORK
SERVICE 0 4 0 16 5324 53 2007-05-17 14:07:28.217 2007-05-17 14:07:28.233
RPC:Completed exec sp_reset_connection Report Server NETWORK SERVICE NT
AUTHORITY\NETWORK SERVICE 0 0 0 0 5324 53 2007-05-17 14:07:28.233 2007-05-17
14:07:28.233 0X00000000000000002600730070005F00720065007300650074005F0063006F006E006E0065006300740069006F006E00
Audit Login -- network protocol: LPC
set quoted_identifier on
set arithabort off
set numeric_roundabort off
set ansi_warnings on
set ansi_padding on
set ansi_nulls on
set concat_null_yields_null on
set cursor_close_on_commit off
set implicit_transactions off
set language us_english
set dateformat mdy
set datefirst 7
set transaction isolation level read committed
Report Server NETWORK SERVICE NT AUTHORITY\NETWORK
SERVICE 5324 53 2007-05-17 14:07:28.233
I have a problem with our new setup of RS 2005.
I can access de report manager an show reports fine.
I have the Bruce-workaround t.i. a "dummy"-report that refreshes itself
every few minutes to keep connections alive.
Still somtimes when I open up a report I get a blank IE window and have to
wait a full minute for "report is being generated" to appear. Although I ran
a report e few minutes earlier (certainly les than 20 minutes AND my dummy
report is running).
I ran a trace, but I can't really see the problem, could anybody please help.
I added the trace here below starting from the point I reqeusted the report
to the time that the "report is being generated" appeared. I can't add the
complete trace here (to long) I will put the rest in a next post.
Trace Start 2007-05-17 14:06:46.403
ExistingConnection -- network protocol: LPC
set quoted_identifier off
set arithabort off
set numeric_roundabort off
set ansi_warnings on
set ansi_padding on
set ansi_nulls on
set concat_null_yields_null on
set cursor_close_on_commit off
set implicit_transactions off
set language us_english
set dateformat mdy
set datefirst 7
set transaction isolation level read committed
SQLAgent - Generic Refresher adminsql KLINA\adminsql 3232 51 2007-05-04
12:47:40.653
ExistingConnection -- network protocol: LPC
set quoted_identifier on
set arithabort off
set numeric_roundabort off
set ansi_warnings on
set ansi_padding on
set ansi_nulls on
set concat_null_yields_null on
set cursor_close_on_commit off
set implicit_transactions off
set language us_english
set dateformat mdy
set datefirst 7
set transaction isolation level read committed
Report Server adminsql KLINA\adminsql 2056 52 2007-05-17 12:54:29.373
ExistingConnection -- network protocol: TCP/IP
set quoted_identifier on
set arithabort on
set numeric_roundabort off
set ansi_warnings on
set ansi_padding on
set ansi_nulls on
set concat_null_yields_null on
set cursor_close_on_commit off
set implicit_transactions off
set language us_english
set dateformat mdy
set datefirst 7
set transaction isolation level read committed
Toad for SQL Server sa 1364 55 2007-05-17 11:31:23.827
ExistingConnection -- network protocol: TCP/IP
set quoted_identifier off
set arithabort off
set numeric_roundabort off
set ansi_warnings on
set ansi_padding on
set ansi_nulls on
set concat_null_yields_null on
set cursor_close_on_commit off
set implicit_transactions off
set language us_english
set dateformat mdy
set datefirst 7
set transaction isolation level read committed
SQLDMO_1 sa 1364 56 2007-05-17 11:31:25.890
ExistingConnection -- network protocol: LPC
set quoted_identifier off
set arithabort off
set numeric_roundabort off
set ansi_warnings on
set ansi_padding on
set ansi_nulls on
set concat_null_yields_null on
set cursor_close_on_commit off
set implicit_transactions off
set language us_english
set dateformat mdy
set datefirst 7
set transaction isolation level read committed
SQLAgent - Job invocation
engine adminsql KLINA\adminsql 3232 58 2007-05-05 00:10:00.810
ExistingConnection -- network protocol: TCP/IP
set quoted_identifier on
set arithabort off
set numeric_roundabort off
set ansi_warnings on
set ansi_padding on
set ansi_nulls on
set concat_null_yields_null on
set cursor_close_on_commit off
set implicit_transactions off
set language us_english
set dateformat mdy
set datefirst 7
set transaction isolation level read committed
Microsoft® Visual Studio®
2005 vansinan KLINA\vansinan 1920 62 2007-05-10 20:23:44.327
ExistingConnection -- network protocol: LPC
set quoted_identifier on
set arithabort off
set numeric_roundabort off
set ansi_warnings on
set ansi_padding on
set ansi_nulls on
set concat_null_yields_null on
set cursor_close_on_commit off
set implicit_transactions off
set language us_english
set dateformat mdy
set datefirst 7
set transaction isolation level read committed
Report Server adminsql KLINA\adminsql 2056 65 2007-05-17 14:04:29.090
ExistingConnection -- network protocol: LPC
set quoted_identifier on
set arithabort off
set numeric_roundabort off
set ansi_warnings on
set ansi_padding on
set ansi_nulls on
set concat_null_yields_null on
set cursor_close_on_commit off
set implicit_transactions off
set language us_english
set dateformat mdy
set datefirst 7
set transaction isolation level read committed
Report Server adminsql KLINA\adminsql 2056 103 2007-05-17 14:06:42.717
Audit Logout Report
Server adminsql KLINA\adminsql 0 45 0 10000 2056 103 2007-05-17
14:06:42.717 2007-05-17 14:06:52.717
RPC:Completed exec sp_reset_connection Report
Server adminsql KLINA\adminsql 0 0 0 0 2056 103 2007-05-17
14:06:52.717 2007-05-17
14:06:52.717 0X00000000000000002600730070005F00720065007300650074005F0063006F006E006E0065006300740069006F006E00
Audit Login -- network protocol: LPC
set quoted_identifier on
set arithabort off
set numeric_roundabort off
set ansi_warnings on
set ansi_padding on
set ansi_nulls on
set concat_null_yields_null on
set cursor_close_on_commit off
set implicit_transactions off
set language us_english
set dateformat mdy
set datefirst 7
set transaction isolation level read committed
Report Server adminsql KLINA\adminsql 2056 103 2007-05-17 14:06:52.717
SQL:BatchStarting
declare @.BatchID uniqueidentifier
set @.BatchID = NEWID()
UPDATE [Event] WITH (TABLOCKX)
SET [BatchID] = @.BatchID,
[ProcessStart] = GETUTCDATE(),
[ProcessHeartbeat] = GETUTCDATE()
FROM (
SELECT TOP 2 [EventID] FROM
[Event] WITH (TABLOCKX) WHERE [ProcessStart] is NULL ORDER BY [TimeEntered]
) AS t1
WHERE [Event].[EventID] = t1.[EventID]
select top 2
E.[EventID],
E.[EventType],
E.[EventData]
from
[Event] E WITH (TABLOCKX)
where
[BatchID] = @.BatchID
ORDER BY [TimeEntered]
Report
Server adminsql KLINA\adminsql 2056 103 2007-05-17 14:06:52.717
SQL:BatchCompleted
declare @.BatchID uniqueidentifier
set @.BatchID = NEWID()
UPDATE [Event] WITH (TABLOCKX)
SET [BatchID] = @.BatchID,
[ProcessStart] = GETUTCDATE(),
[ProcessHeartbeat] = GETUTCDATE()
FROM (
SELECT TOP 2 [EventID] FROM
[Event] WITH (TABLOCKX) WHERE [ProcessStart] is NULL ORDER BY [TimeEntered]
) AS t1
WHERE [Event].[EventID] = t1.[EventID]
select top 2
E.[EventID],
E.[EventType],
E.[EventData]
from
[Event] E WITH (TABLOCKX)
where
[BatchID] = @.BatchID
ORDER BY [TimeEntered]
Report
Server adminsql KLINA\adminsql 0 0 0 0 2056 103 2007-05-17
14:06:52.717 2007-05-17 14:06:52.717
SQL:BatchStarting
declare @.BatchID uniqueidentifier
set @.BatchID = newid()
UPDATE [Notifications] WITH (TABLOCKX)
SET [BatchID] = @.BatchID,
[ProcessStart] = GETUTCDATE(),
[ProcessHeartbeat] = GETUTCDATE()
FROM (
SELECT TOP 2 [NotificationID] FROM
[Notifications] WITH (TABLOCKX) WHERE ProcessStart is NULL and
(ProcessAfter is NULL or ProcessAfter <
GETUTCDATE()) ORDER BY [NotificationEntered]
) AS t1
WHERE [Notifications].[NotificationID] = t1.[NotificationID]
select top 2
-- Notification data
N.[NotificationID],
N.[SubscriptionID],
N.[ActivationID],
N.[ReportID],
N.[SnapShotDate],
N.[DeliveryExtension],
N.[ExtensionSettings],
N.[Locale],
N.[Parameters],
N.[SubscriptionLastRunTime],
N.[ProcessStart],
N.[NotificationEntered],
N.[Attempt],
N.[IsDataDriven],
SUSER_SNAME(Owner.[Sid]),
Owner.[UserName],
-- Report Data
O.[Path],
O.[Type],
SD.NtSecDescPrimary,
N.[Version],
Owner.[AuthType]
from
[Notifications] N with (TABLOCKX)
inner join [Catalog] O on O.[ItemID] = N.[ReportID]
inner join [Users] Owner on
N.SubscriptionOwnerID = Owner.UserID
left outer join [SecData] SD on
O.[PolicyID] = SD.[PolicyID] AND SD.AuthType = Owner.AuthType
where
N.[BatchID] = @.BatchID
ORDER BY [NotificationEntered]
Report
Server adminsql KLINA\adminsql 2056 103 2007-05-17 14:06:52.717
SQL:BatchCompleted
declare @.BatchID uniqueidentifier
set @.BatchID = newid()
UPDATE [Notifications] WITH (TABLOCKX)
SET [BatchID] = @.BatchID,
[ProcessStart] = GETUTCDATE(),
[ProcessHeartbeat] = GETUTCDATE()
FROM (
SELECT TOP 2 [NotificationID] FROM
[Notifications] WITH (TABLOCKX) WHERE ProcessStart is NULL and
(ProcessAfter is NULL or ProcessAfter <
GETUTCDATE()) ORDER BY [NotificationEntered]
) AS t1
WHERE [Notifications].[NotificationID] = t1.[NotificationID]
select top 2
-- Notification data
N.[NotificationID],
N.[SubscriptionID],
N.[ActivationID],
N.[ReportID],
N.[SnapShotDate],
N.[DeliveryExtension],
N.[ExtensionSettings],
N.[Locale],
N.[Parameters],
N.[SubscriptionLastRunTime],
N.[ProcessStart],
N.[NotificationEntered],
N.[Attempt],
N.[IsDataDriven],
SUSER_SNAME(Owner.[Sid]),
Owner.[UserName],
-- Report Data
O.[Path],
O.[Type],
SD.NtSecDescPrimary,
N.[Version],
Owner.[AuthType]
from
[Notifications] N with (TABLOCKX)
inner join [Catalog] O on O.[ItemID] = N.[ReportID]
inner join [Users] Owner on
N.SubscriptionOwnerID = Owner.UserID
left outer join [SecData] SD on
O.[PolicyID] = SD.[PolicyID] AND SD.AuthType = Owner.AuthType
where
N.[BatchID] = @.BatchID
ORDER BY [NotificationEntered]
Report
Server adminsql KLINA\adminsql 0 0 0 0 2056 103 2007-05-17
14:06:52.717 2007-05-17 14:06:52.717
Audit Logout Report
Server adminsql KLINA\adminsql 0 45 0 10250 2056 103 2007-05-17
14:06:52.717 2007-05-17 14:07:02.967
RPC:Completed exec sp_reset_connection Report
Server adminsql KLINA\adminsql 0 0 0 0 2056 103 2007-05-17
14:07:02.967 2007-05-17
14:07:02.967 0X00000000000000002600730070005F00720065007300650074005F0063006F006E006E0065006300740069006F006E00
Audit Login -- network protocol: LPC
set quoted_identifier on
set arithabort off
set numeric_roundabort off
set ansi_warnings on
set ansi_padding on
set ansi_nulls on
set concat_null_yields_null on
set cursor_close_on_commit off
set implicit_transactions off
set language us_english
set dateformat mdy
set datefirst 7
set transaction isolation level read committed
Report Server adminsql KLINA\adminsql 2056 103 2007-05-17 14:07:02.967
SQL:BatchStarting
declare @.BatchID uniqueidentifier
set @.BatchID = NEWID()
UPDATE [Event] WITH (TABLOCKX)
SET [BatchID] = @.BatchID,
[ProcessStart] = GETUTCDATE(),
[ProcessHeartbeat] = GETUTCDATE()
FROM (
SELECT TOP 2 [EventID] FROM
[Event] WITH (TABLOCKX) WHERE [ProcessStart] is NULL ORDER BY [TimeEntered]
) AS t1
WHERE [Event].[EventID] = t1.[EventID]
select top 2
E.[EventID],
E.[EventType],
E.[EventData]
from
[Event] E WITH (TABLOCKX)
where
[BatchID] = @.BatchID
ORDER BY [TimeEntered]
Report
Server adminsql KLINA\adminsql 2056 103 2007-05-17 14:07:02.967
SQL:BatchCompleted
declare @.BatchID uniqueidentifier
set @.BatchID = NEWID()
UPDATE [Event] WITH (TABLOCKX)
SET [BatchID] = @.BatchID,
[ProcessStart] = GETUTCDATE(),
[ProcessHeartbeat] = GETUTCDATE()
FROM (
SELECT TOP 2 [EventID] FROM
[Event] WITH (TABLOCKX) WHERE [ProcessStart] is NULL ORDER BY [TimeEntered]
) AS t1
WHERE [Event].[EventID] = t1.[EventID]
select top 2
E.[EventID],
E.[EventType],
E.[EventData]
from
[Event] E WITH (TABLOCKX)
where
[BatchID] = @.BatchID
ORDER BY [TimeEntered]
Report
Server adminsql KLINA\adminsql 0 0 0 0 2056 103 2007-05-17
14:07:02.967 2007-05-17 14:07:02.967
SQL:BatchStarting
declare @.BatchID uniqueidentifier
set @.BatchID = newid()
UPDATE [Notifications] WITH (TABLOCKX)
SET [BatchID] = @.BatchID,
[ProcessStart] = GETUTCDATE(),
[ProcessHeartbeat] = GETUTCDATE()
FROM (
SELECT TOP 2 [NotificationID] FROM
[Notifications] WITH (TABLOCKX) WHERE ProcessStart is NULL and
(ProcessAfter is NULL or ProcessAfter <
GETUTCDATE()) ORDER BY [NotificationEntered]
) AS t1
WHERE [Notifications].[NotificationID] = t1.[NotificationID]
select top 2
-- Notification data
N.[NotificationID],
N.[SubscriptionID],
N.[ActivationID],
N.[ReportID],
N.[SnapShotDate],
N.[DeliveryExtension],
N.[ExtensionSettings],
N.[Locale],
N.[Parameters],
N.[SubscriptionLastRunTime],
N.[ProcessStart],
N.[NotificationEntered],
N.[Attempt],
N.[IsDataDriven],
SUSER_SNAME(Owner.[Sid]),
Owner.[UserName],
-- Report Data
O.[Path],
O.[Type],
SD.NtSecDescPrimary,
N.[Version],
Owner.[AuthType]
from
[Notifications] N with (TABLOCKX)
inner join [Catalog] O on O.[ItemID] = N.[ReportID]
inner join [Users] Owner on
N.SubscriptionOwnerID = Owner.UserID
left outer join [SecData] SD on
O.[PolicyID] = SD.[PolicyID] AND SD.AuthType = Owner.AuthType
where
N.[BatchID] = @.BatchID
ORDER BY [NotificationEntered]
Report
Server adminsql KLINA\adminsql 2056 103 2007-05-17 14:07:02.967
SQL:BatchCompleted
declare @.BatchID uniqueidentifier
set @.BatchID = newid()
UPDATE [Notifications] WITH (TABLOCKX)
SET [BatchID] = @.BatchID,
[ProcessStart] = GETUTCDATE(),
[ProcessHeartbeat] = GETUTCDATE()
FROM (
SELECT TOP 2 [NotificationID] FROM
[Notifications] WITH (TABLOCKX) WHERE ProcessStart is NULL and
(ProcessAfter is NULL or ProcessAfter <
GETUTCDATE()) ORDER BY [NotificationEntered]
) AS t1
WHERE [Notifications].[NotificationID] = t1.[NotificationID]
select top 2
-- Notification data
N.[NotificationID],
N.[SubscriptionID],
N.[ActivationID],
N.[ReportID],
N.[SnapShotDate],
N.[DeliveryExtension],
N.[ExtensionSettings],
N.[Locale],
N.[Parameters],
N.[SubscriptionLastRunTime],
N.[ProcessStart],
N.[NotificationEntered],
N.[Attempt],
N.[IsDataDriven],
SUSER_SNAME(Owner.[Sid]),
Owner.[UserName],
-- Report Data
O.[Path],
O.[Type],
SD.NtSecDescPrimary,
N.[Version],
Owner.[AuthType]
from
[Notifications] N with (TABLOCKX)
inner join [Catalog] O on O.[ItemID] = N.[ReportID]
inner join [Users] Owner on
N.SubscriptionOwnerID = Owner.UserID
left outer join [SecData] SD on
O.[PolicyID] = SD.[PolicyID] AND SD.AuthType = Owner.AuthType
where
N.[BatchID] = @.BatchID
ORDER BY [NotificationEntered]
Report
Server adminsql KLINA\adminsql 0 0 0 0 2056 103 2007-05-17
14:07:02.967 2007-05-17 14:07:02.967Here's the rest of it:
Audit Logout Report
Server adminsql KLINA\adminsql 0 45 0 10000 2056 103 2007-05-17
14:07:02.967 2007-05-17 14:07:12.967
RPC:Completed exec sp_reset_connection Report
Server adminsql KLINA\adminsql 0 0 0 0 2056 103 2007-05-17
14:07:12.967 2007-05-17
14:07:12.967 0X00000000000000002600730070005F00720065007300650074005F0063006F006E006E0065006300740069006F006E00
Audit Login -- network protocol: LPC
set quoted_identifier on
set arithabort off
set numeric_roundabort off
set ansi_warnings on
set ansi_padding on
set ansi_nulls on
set concat_null_yields_null on
set cursor_close_on_commit off
set implicit_transactions off
set language us_english
set dateformat mdy
set datefirst 7
set transaction isolation level read committed
Report Server adminsql KLINA\adminsql 2056 103 2007-05-17 14:07:12.967
SQL:BatchStarting
declare @.BatchID uniqueidentifier
set @.BatchID = NEWID()
UPDATE [Event] WITH (TABLOCKX)
SET [BatchID] = @.BatchID,
[ProcessStart] = GETUTCDATE(),
[ProcessHeartbeat] =GETUTCDATE()
FROM (
SELECT TOP 2 [EventID] FROM
[Event] WITH (TABLOCKX) WHERE [ProcessStart] is NULL ORDER BY [TimeEntered]
) AS t1
WHERE [Event].[EventID] =t1.[EventID]
select top 2
E.[EventID],
E.[EventType],
E.[EventData]
from
[Event] E WITH (TABLOCKX)
where
[BatchID] = @.BatchID
ORDER BY [TimeEntered]
Report
Server adminsql KLINA\adminsql 2056 103 2007-05-17 14:07:12.967
SQL:BatchCompleted
declare @.BatchID uniqueidentifier
set @.BatchID = NEWID()
UPDATE [Event] WITH (TABLOCKX)
SET [BatchID] = @.BatchID,
[ProcessStart] = GETUTCDATE(),
[ProcessHeartbeat] =GETUTCDATE()
FROM (
SELECT TOP 2 [EventID] FROM
[Event] WITH (TABLOCKX) WHERE [ProcessStart] is NULL ORDER BY [TimeEntered]
) AS t1
WHERE [Event].[EventID] =t1.[EventID]
select top 2
E.[EventID],
E.[EventType],
E.[EventData]
from
[Event] E WITH (TABLOCKX)
where
[BatchID] = @.BatchID
ORDER BY [TimeEntered]
Report
Server adminsql KLINA\adminsql 0 0 0 0 2056 103 2007-05-17
14:07:12.967 2007-05-17 14:07:12.967
SQL:BatchStarting
declare @.BatchID uniqueidentifier
set @.BatchID = newid()
UPDATE [Notifications] WITH (TABLOCKX)
SET [BatchID] = @.BatchID,
[ProcessStart] = GETUTCDATE(),
[ProcessHeartbeat] = GETUTCDATE()
FROM (
SELECT TOP 2 [NotificationID] FROM
[Notifications] WITH (TABLOCKX) WHERE ProcessStart is NULL and
(ProcessAfter is NULL or ProcessAfter <
GETUTCDATE()) ORDER BY [NotificationEntered]
) AS t1
WHERE [Notifications].[NotificationID] =t1.[NotificationID]
select top 2
-- Notification data
N.[NotificationID],
N.[SubscriptionID],
N.[ActivationID],
N.[ReportID],
N.[SnapShotDate],
N.[DeliveryExtension],
N.[ExtensionSettings],
N.[Locale],
N.[Parameters],
N.[SubscriptionLastRunTime],
N.[ProcessStart],
N.[NotificationEntered],
N.[Attempt],
N.[IsDataDriven],
SUSER_SNAME(Owner.[Sid]),
Owner.[UserName],
-- Report Data
O.[Path],
O.[Type],
SD.NtSecDescPrimary,
N.[Version],
Owner.[AuthType]
from
[Notifications] N with (TABLOCKX)
inner join [Catalog] O on O.[ItemID] = N.[ReportID]
inner join [Users] Owner on
N.SubscriptionOwnerID = Owner.UserID
left outer join [SecData] SD on
O.[PolicyID] = SD.[PolicyID] AND SD.AuthType = Owner.AuthType
where
N.[BatchID] = @.BatchID
ORDER BY [NotificationEntered]
Report
Server adminsql KLINA\adminsql 2056 103 2007-05-17 14:07:12.967
SQL:BatchCompleted
declare @.BatchID uniqueidentifier
set @.BatchID = newid()
UPDATE [Notifications] WITH (TABLOCKX)
SET [BatchID] = @.BatchID,
[ProcessStart] = GETUTCDATE(),
[ProcessHeartbeat] = GETUTCDATE()
FROM (
SELECT TOP 2 [NotificationID] FROM
[Notifications] WITH (TABLOCKX) WHERE ProcessStart is NULL and
(ProcessAfter is NULL or ProcessAfter <
GETUTCDATE()) ORDER BY [NotificationEntered]
) AS t1
WHERE [Notifications].[NotificationID] =t1.[NotificationID]
select top 2
-- Notification data
N.[NotificationID],
N.[SubscriptionID],
N.[ActivationID],
N.[ReportID],
N.[SnapShotDate],
N.[DeliveryExtension],
N.[ExtensionSettings],
N.[Locale],
N.[Parameters],
N.[SubscriptionLastRunTime],
N.[ProcessStart],
N.[NotificationEntered],
N.[Attempt],
N.[IsDataDriven],
SUSER_SNAME(Owner.[Sid]),
Owner.[UserName],
-- Report Data
O.[Path],
O.[Type],
SD.NtSecDescPrimary,
N.[Version],
Owner.[AuthType]
from
[Notifications] N with (TABLOCKX)
inner join [Catalog] O on O.[ItemID] = N.[ReportID]
inner join [Users] Owner on
N.SubscriptionOwnerID = Owner.UserID
left outer join [SecData] SD on
O.[PolicyID] = SD.[PolicyID] AND SD.AuthType = Owner.AuthType
where
N.[BatchID] = @.BatchID
ORDER BY [NotificationEntered]
Report
Server adminsql KLINA\adminsql 0 0 0 0 2056 103 2007-05-17
14:07:12.967 2007-05-17 14:07:12.967
Audit Logout Report
Server adminsql KLINA\adminsql 0 9082 39 173876 2056 65 2007-05-17
14:04:29.090 2007-05-17 14:07:22.967
RPC:Completed exec sp_reset_connection Report
Server adminsql KLINA\adminsql 0 0 0 0 2056 65 2007-05-17
14:07:22.967 2007-05-17
14:07:22.967 0X00000000000000002600730070005F00720065007300650074005F0063006F006E006E0065006300740069006F006E00
Audit Login -- network protocol: LPC
set quoted_identifier on
set arithabort off
set numeric_roundabort off
set ansi_warnings on
set ansi_padding on
set ansi_nulls on
set concat_null_yields_null on
set cursor_close_on_commit off
set implicit_transactions off
set language us_english
set dateformat mdy
set datefirst 7
set transaction isolation level read committed
Report Server adminsql KLINA\adminsql 2056 65 2007-05-17 14:07:22.967
SQL:BatchStarting
declare @.BatchID uniqueidentifier
set @.BatchID = NEWID()
UPDATE [Event] WITH (TABLOCKX)
SET [BatchID] = @.BatchID,
[ProcessStart] = GETUTCDATE(),
[ProcessHeartbeat] =GETUTCDATE()
FROM (
SELECT TOP 2 [EventID] FROM
[Event] WITH (TABLOCKX) WHERE [ProcessStart] is NULL ORDER BY [TimeEntered]
) AS t1
WHERE [Event].[EventID] =t1.[EventID]
select top 2
E.[EventID],
E.[EventType],
E.[EventData]
from
[Event] E WITH (TABLOCKX)
where
[BatchID] = @.BatchID
ORDER BY [TimeEntered]
Report
Server adminsql KLINA\adminsql 2056 65 2007-05-17 14:07:22.967
SQL:BatchCompleted
declare @.BatchID uniqueidentifier
set @.BatchID = NEWID()
UPDATE [Event] WITH (TABLOCKX)
SET [BatchID] = @.BatchID,
[ProcessStart] = GETUTCDATE(),
[ProcessHeartbeat] =GETUTCDATE()
FROM (
SELECT TOP 2 [EventID] FROM
[Event] WITH (TABLOCKX) WHERE [ProcessStart] is NULL ORDER BY [TimeEntered]
) AS t1
WHERE [Event].[EventID] =t1.[EventID]
select top 2
E.[EventID],
E.[EventType],
E.[EventData]
from
[Event] E WITH (TABLOCKX)
where
[BatchID] = @.BatchID
ORDER BY [TimeEntered]
Report
Server adminsql KLINA\adminsql 0 0 0 0 2056 65 2007-05-17
14:07:22.967 2007-05-17 14:07:22.967
SQL:BatchStarting
declare @.BatchID uniqueidentifier
set @.BatchID = newid()
UPDATE [Notifications] WITH (TABLOCKX)
SET [BatchID] = @.BatchID,
[ProcessStart] = GETUTCDATE(),
[ProcessHeartbeat] = GETUTCDATE()
FROM (
SELECT TOP 2 [NotificationID] FROM
[Notifications] WITH (TABLOCKX) WHERE ProcessStart is NULL and
(ProcessAfter is NULL or ProcessAfter <
GETUTCDATE()) ORDER BY [NotificationEntered]
) AS t1
WHERE [Notifications].[NotificationID] =t1.[NotificationID]
select top 2
-- Notification data
N.[NotificationID],
N.[SubscriptionID],
N.[ActivationID],
N.[ReportID],
N.[SnapShotDate],
N.[DeliveryExtension],
N.[ExtensionSettings],
N.[Locale],
N.[Parameters],
N.[SubscriptionLastRunTime],
N.[ProcessStart],
N.[NotificationEntered],
N.[Attempt],
N.[IsDataDriven],
SUSER_SNAME(Owner.[Sid]),
Owner.[UserName],
-- Report Data
O.[Path],
O.[Type],
SD.NtSecDescPrimary,
N.[Version],
Owner.[AuthType]
from
[Notifications] N with (TABLOCKX)
inner join [Catalog] O on O.[ItemID] = N.[ReportID]
inner join [Users] Owner on
N.SubscriptionOwnerID = Owner.UserID
left outer join [SecData] SD on
O.[PolicyID] = SD.[PolicyID] AND SD.AuthType = Owner.AuthType
where
N.[BatchID] = @.BatchID
ORDER BY [NotificationEntered]
Report
Server adminsql KLINA\adminsql 2056 65 2007-05-17 14:07:22.967
SQL:BatchCompleted
declare @.BatchID uniqueidentifier
set @.BatchID = newid()
UPDATE [Notifications] WITH (TABLOCKX)
SET [BatchID] = @.BatchID,
[ProcessStart] = GETUTCDATE(),
[ProcessHeartbeat] = GETUTCDATE()
FROM (
SELECT TOP 2 [NotificationID] FROM
[Notifications] WITH (TABLOCKX) WHERE ProcessStart is NULL and
(ProcessAfter is NULL or ProcessAfter <
GETUTCDATE()) ORDER BY [NotificationEntered]
) AS t1
WHERE [Notifications].[NotificationID] =t1.[NotificationID]
select top 2
-- Notification data
N.[NotificationID],
N.[SubscriptionID],
N.[ActivationID],
N.[ReportID],
N.[SnapShotDate],
N.[DeliveryExtension],
N.[ExtensionSettings],
N.[Locale],
N.[Parameters],
N.[SubscriptionLastRunTime],
N.[ProcessStart],
N.[NotificationEntered],
N.[Attempt],
N.[IsDataDriven],
SUSER_SNAME(Owner.[Sid]),
Owner.[UserName],
-- Report Data
O.[Path],
O.[Type],
SD.NtSecDescPrimary,
N.[Version],
Owner.[AuthType]
from
[Notifications] N with (TABLOCKX)
inner join [Catalog] O on O.[ItemID] = N.[ReportID]
inner join [Users] Owner on
N.SubscriptionOwnerID = Owner.UserID
left outer join [SecData] SD on
O.[PolicyID] = SD.[PolicyID] AND SD.AuthType = Owner.AuthType
where
N.[BatchID] = @.BatchID
ORDER BY [NotificationEntered]
Report
Server adminsql KLINA\adminsql 0 0 0 0 2056 65 2007-05-17
14:07:22.967 2007-05-17 14:07:22.967
Audit Logout Report
Server adminsql KLINA\adminsql 0 9082 39 4970 2056 65 2007-05-17
14:07:22.967 2007-05-17 14:07:27.937
RPC:Completed exec sp_reset_connection Report
Server adminsql KLINA\adminsql 0 0 0 0 2056 65 2007-05-17
14:07:27.937 2007-05-17
14:07:27.937 0X00000000000000002600730070005F00720065007300650074005F0063006F006E006E0065006300740069006F006E00
Audit Login -- network protocol: LPC
set quoted_identifier on
set arithabort off
set numeric_roundabort off
set ansi_warnings on
set ansi_padding on
set ansi_nulls on
set concat_null_yields_null on
set cursor_close_on_commit off
set implicit_transactions off
set language us_english
set dateformat mdy
set datefirst 7
set transaction isolation level read committed
Report Server adminsql KLINA\adminsql 2056 65 2007-05-17 14:07:27.937
RPC:Completed exec GetMyRunningJobs
@.ComputerName=N'REPORTSRV',@.JobType=1 Report
Server adminsql KLINA\adminsql 0 2 0 0 2056 65 2007-05-17
14:07:27.937 2007-05-17
14:07:27.937 0X000000000300000020004700650074004D007900520075006E006E0069006E0067004A006F00620073005200000082001600E7306E0076006100720063006800610072002800390029001A00400043006F006D00700075007400650072004E0061006D006500120000005200450050004F00520054005300520056002E00000002001000341073006D0061006C006C0069006E007400100040004A006F006200540079007000650001001400000003000600380469006E00740000000000
Audit Login -- network protocol: LPC
set quoted_identifier on
set arithabort off
set numeric_roundabort off
set ansi_warnings on
set ansi_padding on
set ansi_nulls on
set concat_null_yields_null on
set cursor_close_on_commit off
set implicit_transactions off
set language us_english
set dateformat mdy
set datefirst 7
set transaction isolation level read committed
Report Server NETWORK SERVICE NT AUTHORITY\NETWORK
SERVICE 5324 53 2007-05-17 14:07:28.140
RPC:Completed exec GetOneConfigurationInfo
@.Name=N'SharePointIntegrated' Report Server NETWORK SERVICE NT
AUTHORITY\NETWORK SERVICE 0 2 0 0 5324 53 2007-05-17 14:07:28.140 2007-05-17
14:07:28.140 0X00000000020000002E004700650074004F006E00650043006F006E00660069006700750072006100740069006F006E0049006E0066006F005A00000082001800E7306E00760061007200630068006100720028003200300029000A0040004E0061006D006500280000005300680061007200650050006F0069006E00740049006E00740065006700720061007400650064001400000003000600380469006E00740000000000
Audit Logout Report Server NETWORK SERVICE NT AUTHORITY\NETWORK
SERVICE 0 2 0 76 5324 53 2007-05-17 14:07:28.140 2007-05-17 14:07:28.217
RPC:Completed exec sp_reset_connection Report Server NETWORK SERVICE NT
AUTHORITY\NETWORK SERVICE 0 0 0 0 5324 53 2007-05-17 14:07:28.217 2007-05-17
14:07:28.217 0X00000000000000002600730070005F00720065007300650074005F0063006F006E006E0065006300740069006F006E00
Audit Login -- network protocol: LPC
set quoted_identifier on
set arithabort off
set numeric_roundabort off
set ansi_warnings on
set ansi_padding on
set ansi_nulls on
set concat_null_yields_null on
set cursor_close_on_commit off
set implicit_transactions off
set language us_english
set dateformat mdy
set datefirst 7
set transaction isolation level read committed
Report Server NETWORK SERVICE NT AUTHORITY\NETWORK
SERVICE 5324 53 2007-05-17 14:07:28.217
SQL:BatchStarting SELECT SERVERPROPERTY('Edition') Report Server NETWORK
SERVICE NT AUTHORITY\NETWORK SERVICE 5324 53 2007-05-17 14:07:28.217
SQL:BatchCompleted SELECT SERVERPROPERTY('Edition') Report Server NETWORK
SERVICE NT AUTHORITY\NETWORK SERVICE 0 0 0 0 5324 53 2007-05-17
14:07:28.217 2007-05-17 14:07:28.217
RPC:Completed exec GetOneConfigurationInfo @.Name=N'EnableMyReports' Report
Server NETWORK SERVICE NT AUTHORITY\NETWORK
SERVICE 0 2 0 0 5324 53 2007-05-17 14:07:28.233 2007-05-17
14:07:28.233 0X00000000020000002E004700650074004F006E00650043006F006E00660069006700750072006100740069006F006E0049006E0066006F005000000082001800E7306E00760061007200630068006100720028003100350029000A0040004E0061006D0065001E00000045006E00610062006C0065004D0079005200650070006F007200740073001400000003000600380469006E00740000000000
Audit Logout Report Server NETWORK SERVICE NT AUTHORITY\NETWORK
SERVICE 0 4 0 16 5324 53 2007-05-17 14:07:28.217 2007-05-17 14:07:28.233
RPC:Completed exec sp_reset_connection Report Server NETWORK SERVICE NT
AUTHORITY\NETWORK SERVICE 0 0 0 0 5324 53 2007-05-17 14:07:28.233 2007-05-17
14:07:28.233 0X00000000000000002600730070005F00720065007300650074005F0063006F006E006E0065006300740069006F006E00
Audit Login -- network protocol: LPC
set quoted_identifier on
set arithabort off
set numeric_roundabort off
set ansi_warnings on
set ansi_padding on
set ansi_nulls on
set concat_null_yields_null on
set cursor_close_on_commit off
set implicit_transactions off
set language us_english
set dateformat mdy
set datefirst 7
set transaction isolation level read committed
Report Server NETWORK SERVICE NT AUTHORITY\NETWORK
SERVICE 5324 53 2007-05-17 14:07:28.233
Tuesday, February 14, 2012
Bkp - question recovery model
Hello everybody,
I could get any problem if i set recovery model to simple?
My database uses only full backup?
I dont need to do differential or incremental backup.
I dont have problems with backup size.
I have problems to keep the log file short.
Thanks
I would like more explanation about that.
Leandro L S
Vitoria-ES
BRAZIL
Log files are log files .. they grow!
If you have limited space, simply restrict their size.
"Leandro Loureiro dos Santos" <naofalo@.com.vc> wrote in message
news:OLJ8XfdGEHA.3096@.TK2MSFTNGP11.phx.gbl...
> Hello everybody,
> I could get any problem if i set recovery model to simple?
> My database uses only full backup?
> I dont need to do differential or incremental backup.
> I dont have problems with backup size.
> I have problems to keep the log file short.
> Thanks
> I would like more explanation about that.
> Leandro L S
> Vitoria-ES
> BRAZIL
>
|||Hi,
Log files grow based on your type of transactions, if you have bulk
transaction worth 1 GB, automatically transaction log file will grow to a
higher size and the log file will be cleared after the commit (Since u are
using SIPLE recovery). But the physical file size wont reduce.
How to control the growth
1. Insted of doing the bulk transaction do it in batches, so there will be
commit inbetween , this will ensure that file will not grow bigger.
How to reduce the file size
Execute the below commands in Query Analyzer;
1. Backup log <database_name> with no_log
2. use <dbname>
go
dbcc shrinkfile('logical_logfilename','truncateonly')
Thanks
Hari
MCDBA
"Leandro Loureiro dos Santos" <naofalo@.com.vc> wrote in message
news:OLJ8XfdGEHA.3096@.TK2MSFTNGP11.phx.gbl...
> Hello everybody,
> I could get any problem if i set recovery model to simple?
> My database uses only full backup?
> I dont need to do differential or incremental backup.
> I dont have problems with backup size.
> I have problems to keep the log file short.
> Thanks
> I would like more explanation about that.
> Leandro L S
> Vitoria-ES
> BRAZIL
>
|||In the SIMPLE recovery model, your log file needs to be large enough to
accommodate your largest single transaction. Some transactions, such as
DBCC DBREINDEX on a large table, can perform a lot of logging and your log
needs to be sized accordingly.
Hope this helps.
Dan Guzman
SQL Server MVP
"Leandro Loureiro dos Santos" <naofalo@.com.vc> wrote in message
news:OLJ8XfdGEHA.3096@.TK2MSFTNGP11.phx.gbl...
> Hello everybody,
> I could get any problem if i set recovery model to simple?
> My database uses only full backup?
> I dont need to do differential or incremental backup.
> I dont have problems with backup size.
> I have problems to keep the log file short.
> Thanks
> I would like more explanation about that.
> Leandro L S
> Vitoria-ES
> BRAZIL
>
I could get any problem if i set recovery model to simple?
My database uses only full backup?
I dont need to do differential or incremental backup.
I dont have problems with backup size.
I have problems to keep the log file short.
Thanks
I would like more explanation about that.
Leandro L S
Vitoria-ES
BRAZIL
Log files are log files .. they grow!
If you have limited space, simply restrict their size.
"Leandro Loureiro dos Santos" <naofalo@.com.vc> wrote in message
news:OLJ8XfdGEHA.3096@.TK2MSFTNGP11.phx.gbl...
> Hello everybody,
> I could get any problem if i set recovery model to simple?
> My database uses only full backup?
> I dont need to do differential or incremental backup.
> I dont have problems with backup size.
> I have problems to keep the log file short.
> Thanks
> I would like more explanation about that.
> Leandro L S
> Vitoria-ES
> BRAZIL
>
|||Hi,
Log files grow based on your type of transactions, if you have bulk
transaction worth 1 GB, automatically transaction log file will grow to a
higher size and the log file will be cleared after the commit (Since u are
using SIPLE recovery). But the physical file size wont reduce.
How to control the growth
1. Insted of doing the bulk transaction do it in batches, so there will be
commit inbetween , this will ensure that file will not grow bigger.
How to reduce the file size
Execute the below commands in Query Analyzer;
1. Backup log <database_name> with no_log
2. use <dbname>
go
dbcc shrinkfile('logical_logfilename','truncateonly')
Thanks
Hari
MCDBA
"Leandro Loureiro dos Santos" <naofalo@.com.vc> wrote in message
news:OLJ8XfdGEHA.3096@.TK2MSFTNGP11.phx.gbl...
> Hello everybody,
> I could get any problem if i set recovery model to simple?
> My database uses only full backup?
> I dont need to do differential or incremental backup.
> I dont have problems with backup size.
> I have problems to keep the log file short.
> Thanks
> I would like more explanation about that.
> Leandro L S
> Vitoria-ES
> BRAZIL
>
|||In the SIMPLE recovery model, your log file needs to be large enough to
accommodate your largest single transaction. Some transactions, such as
DBCC DBREINDEX on a large table, can perform a lot of logging and your log
needs to be sized accordingly.
Hope this helps.
Dan Guzman
SQL Server MVP
"Leandro Loureiro dos Santos" <naofalo@.com.vc> wrote in message
news:OLJ8XfdGEHA.3096@.TK2MSFTNGP11.phx.gbl...
> Hello everybody,
> I could get any problem if i set recovery model to simple?
> My database uses only full backup?
> I dont need to do differential or incremental backup.
> I dont have problems with backup size.
> I have problems to keep the log file short.
> Thanks
> I would like more explanation about that.
> Leandro L S
> Vitoria-ES
> BRAZIL
>
Bkp - question recovery model
Hello everybody,
I could get any problem if i set recovery model to simple?
My database uses only full backup?
I dont need to do differential or incremental backup.
I dont have problems with backup size.
I have problems to keep the log file short.
Thanks
I would like more explanation about that.
Leandro L S
Vitoria-ES
BRAZILLog files are log files .. they grow!
If you have limited space, simply restrict their size.
"Leandro Loureiro dos Santos" <naofalo@.com.vc> wrote in message
news:OLJ8XfdGEHA.3096@.TK2MSFTNGP11.phx.gbl...
> Hello everybody,
> I could get any problem if i set recovery model to simple?
> My database uses only full backup?
> I dont need to do differential or incremental backup.
> I dont have problems with backup size.
> I have problems to keep the log file short.
> Thanks
> I would like more explanation about that.
> Leandro L S
> Vitoria-ES
> BRAZIL
>|||Hi,
Log files grow based on your type of transactions, if you have bulk
transaction worth 1 GB, automatically transaction log file will grow to a
higher size and the log file will be cleared after the commit (Since u are
using SIPLE recovery). But the physical file size wont reduce.
How to control the growth
1. Insted of doing the bulk transaction do it in batches, so there will be
commit inbetween , this will ensure that file will not grow bigger.
How to reduce the file size
Execute the below commands in Query Analyzer;
1. Backup log <database_name> with no_log
2. use <dbname>
go
dbcc shrinkfile('logical_logfilename','trunca
teonly')
Thanks
Hari
MCDBA
"Leandro Loureiro dos Santos" <naofalo@.com.vc> wrote in message
news:OLJ8XfdGEHA.3096@.TK2MSFTNGP11.phx.gbl...
> Hello everybody,
> I could get any problem if i set recovery model to simple?
> My database uses only full backup?
> I dont need to do differential or incremental backup.
> I dont have problems with backup size.
> I have problems to keep the log file short.
> Thanks
> I would like more explanation about that.
> Leandro L S
> Vitoria-ES
> BRAZIL
>|||In the SIMPLE recovery model, your log file needs to be large enough to
accommodate your largest single transaction. Some transactions, such as
DBCC DBREINDEX on a large table, can perform a lot of logging and your log
needs to be sized accordingly.
Hope this helps.
Dan Guzman
SQL Server MVP
"Leandro Loureiro dos Santos" <naofalo@.com.vc> wrote in message
news:OLJ8XfdGEHA.3096@.TK2MSFTNGP11.phx.gbl...
> Hello everybody,
> I could get any problem if i set recovery model to simple?
> My database uses only full backup?
> I dont need to do differential or incremental backup.
> I dont have problems with backup size.
> I have problems to keep the log file short.
> Thanks
> I would like more explanation about that.
> Leandro L S
> Vitoria-ES
> BRAZIL
>
I could get any problem if i set recovery model to simple?
My database uses only full backup?
I dont need to do differential or incremental backup.
I dont have problems with backup size.
I have problems to keep the log file short.
Thanks
I would like more explanation about that.
Leandro L S
Vitoria-ES
BRAZILLog files are log files .. they grow!
If you have limited space, simply restrict their size.
"Leandro Loureiro dos Santos" <naofalo@.com.vc> wrote in message
news:OLJ8XfdGEHA.3096@.TK2MSFTNGP11.phx.gbl...
> Hello everybody,
> I could get any problem if i set recovery model to simple?
> My database uses only full backup?
> I dont need to do differential or incremental backup.
> I dont have problems with backup size.
> I have problems to keep the log file short.
> Thanks
> I would like more explanation about that.
> Leandro L S
> Vitoria-ES
> BRAZIL
>|||Hi,
Log files grow based on your type of transactions, if you have bulk
transaction worth 1 GB, automatically transaction log file will grow to a
higher size and the log file will be cleared after the commit (Since u are
using SIPLE recovery). But the physical file size wont reduce.
How to control the growth
1. Insted of doing the bulk transaction do it in batches, so there will be
commit inbetween , this will ensure that file will not grow bigger.
How to reduce the file size
Execute the below commands in Query Analyzer;
1. Backup log <database_name> with no_log
2. use <dbname>
go
dbcc shrinkfile('logical_logfilename','trunca
teonly')
Thanks
Hari
MCDBA
"Leandro Loureiro dos Santos" <naofalo@.com.vc> wrote in message
news:OLJ8XfdGEHA.3096@.TK2MSFTNGP11.phx.gbl...
> Hello everybody,
> I could get any problem if i set recovery model to simple?
> My database uses only full backup?
> I dont need to do differential or incremental backup.
> I dont have problems with backup size.
> I have problems to keep the log file short.
> Thanks
> I would like more explanation about that.
> Leandro L S
> Vitoria-ES
> BRAZIL
>|||In the SIMPLE recovery model, your log file needs to be large enough to
accommodate your largest single transaction. Some transactions, such as
DBCC DBREINDEX on a large table, can perform a lot of logging and your log
needs to be sized accordingly.
Hope this helps.
Dan Guzman
SQL Server MVP
"Leandro Loureiro dos Santos" <naofalo@.com.vc> wrote in message
news:OLJ8XfdGEHA.3096@.TK2MSFTNGP11.phx.gbl...
> Hello everybody,
> I could get any problem if i set recovery model to simple?
> My database uses only full backup?
> I dont need to do differential or incremental backup.
> I dont have problems with backup size.
> I have problems to keep the log file short.
> Thanks
> I would like more explanation about that.
> Leandro L S
> Vitoria-ES
> BRAZIL
>
Subscribe to:
Posts (Atom)