Can Not Drop Serverice or Queue

  • 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

  • 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

  • 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