IS that code killing queries that are blocking and running over 2 hours?

I agree but in this case its with an adhoc dev enviroment which its ok. If someone could explain to me how to go about it I would appreciate it

I figured it out. In my code there is a wait command that occasionally is called. I changed the timeout setting in the subscription to 1200 and it ran fine.

How can a correct that?

Found it!

USE MyDB

GO

SET ANSI_WARNINGS OFF;

SET NOCOUNT ON;

GO

WITH agg AS

(

SELECT

[object_id],

last_user_seek,

...

This is for an adhoc enviroment for report development.

But Im needing to do it on a weekly basis

Yes Sean thats exactly what m trying to do. Trying to find out which table can be dropped.

I appreciate your help. The load of the temp table and creation of the index is working but I'm getting errors with the last select statement.

Sorry, heres a create script for the table

CREATE TABLE [dbo].[SQLPLEX_AUDITLOG_ALL](

[SQLPlexAuditLogID] [bigint] IDENTITY(1,1) NOT NULL,

[SQLPlexAuditLogID_Local] [bigint] NULL,

[event_time] [datetime2](7) NOT NULL,

[sequence_number] [int] NOT NULL,

[database_type] [varchar](13) NOT NULL,

[server_instance_name] [nvarchar](128) NULL,

[server_machine_name] [sql_variant] NULL,

[server_ip_address] [varchar](15)...

I ended up using the Transfer SQL Server Objects task in ssis to copy the tables over without the data which is exactly what I needed.

Nice! I will see if we can use 3rd party

I figured it out:

exec sp_MSforeachtable '

IF EXISTS(select * from information_schema.tables where TABLE_SCHEMA = ''s016PASP'' AND TABLE_NAME = parsename ("?", 1)

)

BEGIN

ALTER SCHEMA dbo TRANSFER ?

END'

Actually went in this direction. I created the missinig schemas on my destination db, ran the obj transfer, then ran the following to move all tables into the corrrect schema

exec...

The reason I need to be ablee to work it without the filegroup is that I'm using a Transfer SQL Server Objects Task to copy about 200 tables over and...