March 16, 2010 at 8:02 am
To start off I am new to using services and queue...
A little background....
With the help of some articles I found in SQL Server Central, I developed a process to capture Login/Logout events and report them to a table within my DBA database. I needed to do this for a week so that I can audit all the logins and where they were coming from. I am in the process of tightening up the security on our systems and part of that is removing dead SQL Login/Users, consolidating redundant login/users and dumbing down the privleges.
Here are the procedures that I used...
ALTER DATABASE MyDBADB SET ENABLE_BROKER
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[DBA_AuditLoginEvents](
[LoggingID] [bigint] IDENTITY(1,1) NOT NULL,
[EventTime] [datetime] NULL,
[EventType] [varchar](100) NULL,
[LoginName] [varchar](100) NULL,
[HostName] [varchar](100) NULL,
[NTUserName] [varchar](100) NULL,
[NTDomainName] [varchar](100) NULL,
[SPID] [varchar](7) NULL,
[ApplicationName] [varchar](100) NULL,
[Success] [int] NULL,
[FullLog] [xml] NULL,
CONSTRAINT [PK_DBA_AuditLoginEvents] PRIMARY KEY NONCLUSTERED
(
[LoggingID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
/****** Object: Index [EventTimeIDX] Script Date: 03/08/2010 12:57:30 ******/
CREATE CLUSTERED INDEX [EventTimeIDX] ON [dbo].[DBA_AuditLoginEvents]
(
[EventTime] ASC,
[LoginName] ASC,
[EventType] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
--The queue
CREATE QUEUE DBA_AuditLoginQueue
GO
--The service
CREATE SERVICE DBA_AuditLoginService
ON QUEUE DBA_AuditLoginQueue
([http://schemas.microsoft.com/SQL/Notifications/PostEventNotification])
GO
--The route
CREATE ROUTE DBA_AuditLoginRoute
WITH SERVICE_NAME = 'DBA_AuditLoginService',
ADDRESS = 'LOCAL'
GO
CREATE EVENT NOTIFICATION DBA_AuditLogin_Event_Notification
ON SERVER
FOR AUDIT_LOGIN, AUDIT_LOGOUT, AUDIT_LOGIN_FAILED
TO SERVICE 'DBA_AuditLoginService', 'current database'
GO
--The proc to handle the events
-- Set options required for the XML data type.
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
create PROCEDURE DBA_AuditLogin
AS....
-- procedure basically read the events and inserted them into a table
-- ...
go
ALTER QUEUE DBA_AuditLoginQueue
WITH ACTIVATION (
STATUS = ON,
PROCEDURE_NAME = DBA_AuditLogin ,
MAX_QUEUE_READERS = 2, EXECUTE AS SELF )
GO
ALTER QUEUE DBA_AuditLoginQueue WITH STATUS = OFF ;
GO
I now have all the logs I need and I would like to back this out with the execpion of the captured results in the audit table.
I have successfully backed this out in one of our development servers and one of our prodcution environments but now I am having trouble backing this out of our BIG prod server. Here are the procedure I am using to back it out...
ALTER QUEUE DBA_AuditLoginQueue WITH STATUS = Off ;
go
drop EVENT NOTIFICATION DBA_AuditLogin_Event_Notification
ON SERVER
go
drop ROUTE DBA_AuditLoginRoute
go
drop SERVICE DBA_AuditLoginService
go
drop QUEUE DBA_AuditLoginQueue
go
When I try to drop the service I get the following message...
Msg 9002, Level 17, State 4, Line 1
The transaction log for database 'MYDBADB' is full. To find out why space in the log cannot be reused, see the log_reuse_wait_desc column in sys.databases
This db has a recovery model of SIMPLE
At point I have tried increasing the size of the TLOG to over 6 gig and I still can not drop the service.
To make matters worse, my msdb db seems to have grown to a critical level. It has settled down since I disabled the status of the queue.
I am kind of in a pickle here and I am in the process of open a critical case with MS...Maybe I can find an answer here faster.
Can someone give me a hint for how to drop the service and queue safely.
I am assuming once the queue is safely removed I can then shrink the msdb database.
If all I need to do is to increase the TLOG file space on the DBA database, I can do that but I am not sure how big to make it.
Any help would be great.
Thank You
March 16, 2010 at 2:27 pm
Ok...I am going to answer my own post
I worked with MS on this all day and this caused quite a mess.
First...
I am not quite sure how this mess started but I am sure it had to do with the order in which I tried to back out the Queue.
In the end I ended up with a ton of messages in my local queue and 17 gig worth of messages in the msdb..sysxmitqueue table.
To delete the Service, I had to increase the TLOG size to 20+ gig in the MYDBADB.
I am guessing that the TLOG on the MYDBADB needs to be larger than the size of the sysxmitqueue in the msdb database. Even thoug the destination queue resides in a user DB it still seems to have some roots in msdb :ermm: You can derive the size of the table by running the following in msdb:
SELECT TOP 100 OBJECT_NAME([object_id]), *
FROM sys.dm_db_partition_stats
WHERE index_id IN (0,1)
ORDER BY in_row_reserved_page_count DESC
Once I did this I was able to first disable the broker in the MYDBADB and then drop the service and queue.
Full procedure should have been...
use mydbadb
go
ALTER DATABASE MYDBADB SET DISABLE_BROKER
GO
ALTER QUEUE DBA_AuditLoginQueue WITH STATUS = Off ;
go
drop EVENT NOTIFICATION DBA_AuditLogin_Event_Notification
ON SERVER
go
drop ROUTE DBA_AuditLoginRoute
go
drop SERVICE DBA_AuditLoginService
go
drop QUEUE DBA_AuditLoginQueue
goGO
Now I am still left with a sysxmitqueue table in msdb that has 17+gig or 4+million orphan rows.
There is no easy way (that I am aware of) to remove these orphan rows without putting msdb into single user mode.
In my case, the msdb primary datafile grew out of proportion and we were forced to create a new datafile on a separate drive and disable autogrowth on the first file.
In my situation, our application is highly dependent on msdb so I will need to wait until a scheduled outage to clean this mess up.
The following procedures have not been tested but I believe this will clean up my orphan rows from the sysxmitqueue table.
1 - Shut down the SQL Server Agent
2 - Put msdb into single user mode
3 - run the following command to purge the table:
ALTER DATABASE [msdb] SET NEW_BROKER WITH ROLLBACK IMMEDIATE;
I will not be running this procedure until mid-April 2010. I will post the outcome.
In the meantime, please post if anyone can enlighten me as to what I did wrong originally.
I hope this comes of help to someone...it is a little cryptic
November 12, 2010 at 9:24 am
Awesome post. You just made my day. Thanks!:-)
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply