DSO in SQL2005: job

Showing posts with label job. Show all posts

Tuesday, March 27, 2012

Dropping and Recreating Tables

We have a job that runs every 30 minutes dropping and recreating a table with
updated information. Besides the fact that I am trying to get rid of this
needless process, the question I have is:
When this job is running and attempting to drop and recreate the table will
it not be able to complete if someone has a connection to the database and/or
is running a report off the table that SQL Server is attempting to drop and
recreate? If there is a lock on this table from someone using it, is there a
way I can stop this from happening?Connections to the database itself will not matter however all locks
involving the table would need to be released before the DROP command
executes. In the event that someone is using the table, the drop command
will wait until the locks are released or your connection times out (which
ever comes first).
--
--Brian
(Please reply to the newsgroups only.)
"Mike Collins" <MikeCollins@.discussions.microsoft.com> wrote in message
news:37EF75DC-72E7-49C6-BBD1-D1C32E04EA73@.microsoft.com...
> We have a job that runs every 30 minutes dropping and recreating a table
> with
> updated information. Besides the fact that I am trying to get rid of this
> needless process, the question I have is:
> When this job is running and attempting to drop and recreate the table
> will
> it not be able to complete if someone has a connection to the database
> and/or
> is running a report off the table that SQL Server is attempting to drop
> and
> recreate? If there is a lock on this table from someone using it, is there
> a
> way I can stop this from happening?|||Thanks for the reply. That should help a lot. Another question to your reply
though. If the job is waiting for the locks to be released, will this cause
the database to become unresponsive through Enterprise Manager, and will this
slow database access (as a whole) through the website?
"Brian Lawton" wrote:
> Connections to the database itself will not matter however all locks
> involving the table would need to be released before the DROP command
> executes. In the event that someone is using the table, the drop command
> will wait until the locks are released or your connection times out (which
> ever comes first).
> --
> --Brian
> (Please reply to the newsgroups only.)
>
> "Mike Collins" <MikeCollins@.discussions.microsoft.com> wrote in message
> news:37EF75DC-72E7-49C6-BBD1-D1C32E04EA73@.microsoft.com...
> > We have a job that runs every 30 minutes dropping and recreating a table
> > with
> > updated information. Besides the fact that I am trying to get rid of this
> > needless process, the question I have is:
> >
> > When this job is running and attempting to drop and recreate the table
> > will
> > it not be able to complete if someone has a connection to the database
> > and/or
> > is running a report off the table that SQL Server is attempting to drop
> > and
> > recreate? If there is a lock on this table from someone using it, is there
> > a
> > way I can stop this from happening?
>
>|||While the DROP command is waiting, unless its connection is holding locks on
other resources, then no, it will not impact any performance of EM until it
actually executes. While executing, it may impact performance depending on
the table size involved and the time it takes to complete its work. This is
especially true if the web site is waiting for the "refreshed" table to be
repopulated.
All that said, if you are issuing the DROP command through EM, then yes, EM
will become "unresponsive" while it waits for the command to complete.
Unfortunately EM operates synchronously so any operation you perform using
EM may cause it to pause while the operation completes.
--
--Brian
(Please reply to the newsgroups only.)
"Mike Collins" <MikeCollins@.discussions.microsoft.com> wrote in message
news:2F58BF53-683A-4E5B-A664-0F418DD019EF@.microsoft.com...
> Thanks for the reply. That should help a lot. Another question to your
> reply
> though. If the job is waiting for the locks to be released, will this
> cause
> the database to become unresponsive through Enterprise Manager, and will
> this
> slow database access (as a whole) through the website?
> "Brian Lawton" wrote:
>> Connections to the database itself will not matter however all locks
>> involving the table would need to be released before the DROP command
>> executes. In the event that someone is using the table, the drop command
>> will wait until the locks are released or your connection times out
>> (which
>> ever comes first).
>> --
>> --Brian
>> (Please reply to the newsgroups only.)
>>
>> "Mike Collins" <MikeCollins@.discussions.microsoft.com> wrote in message
>> news:37EF75DC-72E7-49C6-BBD1-D1C32E04EA73@.microsoft.com...
>> > We have a job that runs every 30 minutes dropping and recreating a
>> > table
>> > with
>> > updated information. Besides the fact that I am trying to get rid of
>> > this
>> > needless process, the question I have is:
>> >
>> > When this job is running and attempting to drop and recreate the table
>> > will
>> > it not be able to complete if someone has a connection to the database
>> > and/or
>> > is running a report off the table that SQL Server is attempting to drop
>> > and
>> > recreate? If there is a lock on this table from someone using it, is
>> > there
>> > a
>> > way I can stop this from happening?
>>

Wednesday, March 21, 2012

Drop table statement didn't actually drop table...

I have a scheduled job that runs every minute to monitor for
long-running jobs in production databases. This job has been running
without issue for at least 8 months now. I have not done ANY work on
this server in at least 2 months and no one has rights to do anything
within the SQL Server on this box except me. A couple of days ago, I
started getting job failure messages sporradically and the reason was
that '##jobs already exists in the database'. This is a global temp
table that gets created, populated, and dropped in the stored procedure
that's run in my job.
I ran a trace to see what's going on and why the job thinks the temp
table still exists. I can see where the DROP TABLE statement for the
temp table executes and completes on a job run that succeeds, but if
the next job run fails on the statement that creates the temp table, I
can do a select for that table and it does exist and is still populated
with the records from the previous run. I also ran the trace with
errors/exceptions and none were picked up.
Has anyone ever seen where a DROP TABLE statement is issued and
completes, but the table doesn't actually drop? Below you will find
the stored procedure, the results of the trace (where the first job run
wraps up with the final statements and the next job run tries to
execute and fails), and the select from the temp table after the DROP
TABLE statement is executed. Sorry for the length.
Any ideas would be appreciated!
STORED PROCEDURE
--
CREATE PROCEDURE [dbo].[LongRunningJobs]
@.emailaddress varchar(100)
AS
-- Description: This stored procedure will detect long running jobs.
-- A long running job is defined as a job that has
-- been running longer than what is specified as the time
-- limit for the job in the LongRunningJobs_JobTimeLimits
-- table. If it detects a long running job an email is sent.
-- DECLARE variables
SET NOCOUNT ON
DECLARE @.jobcount int
DECLARE @.jobid uniqueidentifier
DECLARE @.jobname sysname
DECLARE @.spid smallint
DECLARE @.logintime datetime
DECLARE @.duration int
-- CREATE TABLE to hold job information
CREATE TABLE #enum_job (
Job_ID uniqueidentifier,
Last_Run_Date int,
Last_Run_Time int,
Next_Run_Date int,
Next_Run_Time int,
Next_Run_Schedule_ID int,
Requested_To_Run int,
Request_Source int,
Request_Source_ID varchar(100),
Running int,
Current_Step int,
Current_Retry_Attempt int,
State int
)
-- CREATE TABLE to hold job_id and the job_id in hex character format
CREATE TABLE ##jobs (
job_id uniqueidentifier ,
job_id_char varchar(100)
)
-- Get a list of jobs
INSERT INTO #enum_job
EXEC master.dbo.xp_sqlagent_enum_jobs 1, '' -- doesn't seem to
matter what you put here
-- calculate the #jobs TABLE with job_id's
-- and their hex character representation
INSERT INTO ##jobs
SELECT job_id, dbo.fn_hex_to_char(job_id,16) FROM #enum_job
-- get a COUNT of long running jobs
SELECT @.jobcount = COUNT(*)
FROM master.dbo.sysprocesses a
join ##jobs b
ON SUBSTRING(a.program_name,32,32)= b.job_id_char
join msdb.dbo.sysjobs c
ON b.job_id = c.job_id
join dbo.LongRunningJobs_JobTimeLimits d
ON c.job_id = d.job_id
WHERE login_time < DATEADD(mi,-(d.minutes),GETDATE()) -- check for jobs
that have been running longer than 1 minute.
-- record long running jobs in LongRunningJobs_Jobs
IF @.jobcount > 0
BEGIN
UPDATE LongRunningJobs_Jobs
SET still_running = 0
DECLARE jobs_cursor CURSOR
FOR
SELECT SUBSTRING(c.name,1,78), c.job_id, a.spid, a.login_time
FROM master.dbo.sysprocesses a
join ##jobs b
ON SUBSTRING(a.program_name,32,32)= b.job_id_char
join msdb.dbo.sysjobs c
ON b.job_id = c.job_id
join dbo.LongRunningJobs_JobTimeLimits d
ON c.job_id = d.job_id
WHERE login_time < DATEADD(mi,-(d.minutes),GETDATE())
OPEN jobs_cursor
FETCH NEXT FROM jobs_cursor INTO @.jobname, @.jobid, @.spid, @.logintime
WHILE @.@.FETCH_STATUS = 0
BEGIN
IF NOT EXISTS (SELECT job_id, login_time FROM
dbo.LongRunningJobs_Jobs
WHERE job_id = @.jobid AND login_time = @.logintime)
BEGIN
SET @.duration = (SELECT DATEDIFF(mi, @.logintime, getdate()))
EXEC LongRunningJobs_Notification @.jobid, @.logintime, @.duration,
@.emailaddress
INSERT INTO dbo.LongRunningJobs_Jobs (job_id, job_name, spid,
login_time, still_running)
VALUES (@.jobid, @.jobname, @.spid, @.logintime, 1)
FETCH NEXT FROM jobs_cursor INTO @.jobname, @.jobid, @.spid, @.logintime
END
ELSE
UPDATE LongRunningJobs_Jobs
SET still_running = 1
WHERE job_id = @.jobid AND login_time = @.logintime
FETCH NEXT FROM jobs_cursor INTO @.jobname, @.jobid, @.spid, @.logintime
END
CLOSE jobs_cursor
DEALLOCATE jobs_cursor
END
ELSE
UPDATE LongRunningJobs_Jobs
SET still_running = 0
DELETE FROM LongRunningJobs_Jobs
WHERE still_running = 0
DROP TABLE #enum_job
DROP TABLE ##jobs
GO
TRACE RESULTS
--
EventClass TextData
-- --
SP:StmtStarting -- LongRunningJobs
UPDATE LongRunningJobs_Jobs
SET still_running = 0
SP:StmtCompleted -- LongRunningJobs
UPDATE LongRunningJobs_Jobs
SET still_running = 0
SP:StmtStarting -- LongRunningJobs
DELETE FROM LongRunningJobs_Jobs
WHERE still_running = 0
SP:StmtCompleted -- LongRunningJobs
DELETE FROM LongRunningJobs_Jobs
WHERE still_running = 0
SP:StmtStarting -- LongRunningJobs
DROP TABLE #enum_job
SP:StmtCompleted -- LongRunningJobs
DROP TABLE #enum_job
SP:StmtStarting -- LongRunningJobs
DROP TABLE ##jobs
SP:StmtCompleted -- LongRunningJobs
DROP TABLE ##jobs
SP:Completed EXEC ISS_DBAData_Production.dbo.LongRunningJobs
'rfsupport@.kroger.com'
SQL:StmtCompleted EXEC ISS_DBAData_Production.dbo.LongRunningJobs
'rfsupport@.kroger.com'
SQL:BatchCompleted EXEC ISS_DBAData_Production.dbo.LongRunningJobs
'rfsupport@.kroger.com'
SQL:BatchStarting EXEC ISS_DBAData_Production.dbo.LongRunningJobs
'rfsupport@.kroger.com'
SQL:StmtStarting EXEC ISS_DBAData_Production.dbo.LongRunningJobs
'rfsupport@.kroger.com'
SP:Starting EXEC ISS_DBAData_Production.dbo.LongRunningJobs
'rfsupport@.kroger.com'
SP:StmtStarting -- LongRunningJobs
SET NOCOUNT ON
SP:StmtCompleted -- LongRunningJobs
SET NOCOUNT ON
SP:StmtStarting -- LongRunningJobs
CREATE TABLE #enum_job (
Job_ID uniqueidentifier,
Last_Run_Date int,
Last_Run_Time int,
Next_Run_Date int,
Next_Run_Time int,
Next_Run_Schedule_ID int,
Requested_To_Run int,
Request_Source int,
Request_Source_ID varch
SP:StmtCompleted -- LongRunningJobs
CREATE TABLE #enum_job (
Job_ID uniqueidentifier,
Last_Run_Date int,
Last_Run_Time int,
Next_Run_Date int,
Next_Run_Time int,
Next_Run_Schedule_ID int,
Requested_To_Run int,
Request_Source int,
Request_Source_ID varch
SP:StmtStarting -- LongRunningJobs
CREATE TABLE ##jobs (
job_id uniqueidentifier ,
job_id_char varchar(100)
)
-- Get a list of jobs
SQL:BatchCompleted EXEC ISS_DBAData_Production.dbo.LongRunningJobs
'rfsupport@.kroger.com'
SELECT OF ##jobs AFTER DROP STATEMENT HAS BEEN EXECUTED SHOWS TABLE
STILL EXISTS
---
job_id job_id_char
--
----
0887FA02-2785-415B-A1E7-0F70EA9B2BF9 02FA870885275B41A1E70F70EA9B2BF9
A22E5C05-9331-4D13-A2E0-C4470EF51BEE 055C2EA23193134DA2E0C4470EF51BEE
B45F740A-3847-42E4-B5F6-0A7AC400E5B8 0A745FB44738E442B5F60A7AC400E5B8
7EDEBA0D-730C-4C57-BD79-4ABAF8D2FB8F 0DBADE7E0C73574CBD794ABAF8D2FB8F
6152F60D-7CA7-4ADD-A117-43D1E6A9E07E 0DF65261A77CDD4AA11743D1E6A9E07E
F7810610-F203-4C52-BE0C-BBF9CF68C887 100681F703F2524CBE0CBBF9CF68C887
F482D610-0171-4E1F-B43F-1266AE7506A2 10D682F471011F4EB43F1266AE7506A2
9FB2FA11-B69F-41DF-AD25-49FF5C605F37 11FAB29F9FB6DF41AD2549FF5C605F37
6CBB1318-95D3-4B03-B5B5-E8C0B526087C 1813BB6CD395034BB5B5E8C0B526087CForgot to mention:
SQL Server 2000 Enterprise Edition with sp4.
This is an active/active cluster.
The job is set up on both nodes and is only failing on the first node.|||Apan,
Why not try making the table a local temporary table instead of global
temporary table?...proc won't see it as an existing table...since you're
dropping it at the end of the proc anyway.
HTH
Jerry
"apan" <anapatterson@.yahoo.com> wrote in message
news:1148661240.078643.204250@.y43g2000cwc.googlegroups.com...
> Forgot to mention:
> SQL Server 2000 Enterprise Edition with sp4.
> This is an active/active cluster.
> The job is set up on both nodes and is only failing on the first node.
>|||I had originally set it up as a global temp table because there were
going to be other sprocs querying it. I've since changed this (nothing
else in the database calls on this temp table), so I probably could
change it to a local temp table.
However, that really doesn't answer my question as to why it's
occasionally not being dropped even though the DROP TABLE command
executes. I'm not sure why it would matter if it's local or global if
the DROP is called.|||FYI - I've gone ahead and changed the global to a local to see if this
fixes the problem since I don't really need a global. But I'd still
like to see if anyone's got any ideas as to my previous question...
BTW, thanks for your input Jerry...forgot to say that before.|||It wouldn't...any blocking occuring that could prevent this? My solution
was an imediate "get you past this" solution.
HTH
Jerry
"apan" <anapatterson@.yahoo.com> wrote in message
news:1148663035.537739.42460@.y43g2000cwc.googlegroups.com...
>I had originally set it up as a global temp table because there were
> going to be other sprocs querying it. I've since changed this (nothing
> else in the database calls on this temp table), so I probably could
> change it to a local temp table.
> However, that really doesn't answer my question as to why it's
> occasionally not being dropped even though the DROP TABLE command
> executes. I'm not sure why it would matter if it's local or global if
> the DROP is called.
>|||Nope, no blocking. I also traced locks and errors/exceptions in my
profile trace when I ran it and none showed up. Also checked the
Locks/Process ID in Enterprise Manager for blocking when I could see
that the temp table hadn't been dropped and didn't see any blocking
there.|||Hmmm...if you query for the ## table in TEMPDB does it still exist?
"apan" <anapatterson@.yahoo.com> wrote in message
news:1148664132.029575.250840@.38g2000cwa.googlegroups.com...
> Nope, no blocking. I also traced locks and errors/exceptions in my
> profile trace when I ran it and none showed up. Also checked the
> Locks/Process ID in Enterprise Manager for blocking when I could see
> that the temp table hadn't been dropped and didn't see any blocking
> there.
>|||When I queried the global table (see last 20 or so lines of original
post for query details), I believe I was just doing it from one of the
user db's and it found it and brought back data. I don't think it
would matter if I queried for it from TEMPDB since it was a global
table...or would it?|||The global temporary table IS stored in the TEMPDB database. But if you're
getting records back the it does exist.
"apan" <anapatterson@.yahoo.com> wrote in message
news:1148666005.668894.271130@.y43g2000cwc.googlegroups.com...
> When I queried the global table (see last 20 or so lines of original
> post for query details), I believe I was just doing it from one of the
> user db's and it found it and brought back data. I don't think it
> would matter if I queried for it from TEMPDB since it was a global
> table...or would it?
>

Drop table statement didn't actually drop table...

drop table

I upgraded my system from Sql2000 to sql2005.

And now i have a problem.

I have job wich runs every minute and perform the following

select * into tabler_1 from .....

begin tran

if exists(select * from sysobjects where xtype ='u' and name = 'tabler')

drop table tabler

exec sp_rename tabler_1, tabler

commit

And on client side i have IIS which runs asp pages which connect to database with

ado connction using ODBC and runs procedures wich performs select * from tabler

and there is a problem they some times failes with error tabler does not exists.

It worked fine in sql2000

all connections opened with default setting.

(transaction isolation level read commited)

What abou this here:

if not exists

(

SELECT * FROM INFORMATION_SCHEMA.TABLES

WHERE TABLE_NAME = 'tabler_1'

AND TABLE_TYPE = 'BASE_TABLE'

)

select * into tabler_1 from SomeTable

if exists

(

SELECT * FROM INFORMATION_SCHEMA.TABLES

WHERE TABLE_NAME = 'tabler'

AND TABLE_TYPE = 'BASE_TABLE'

)

BEGIN

DROP TABLE tabler

EXEC sp_rename tabler_1, tabler

END

Make sure that you check the existence of the table before doing a SELECT INTO.

HTH, Jens Suessmeyer.

http://www.sqlserver2005.de

|||

It is not a problem.

it make drop very nice.

the problem is on the client which runs store procedure like this

create procedure test_sp

select * from tabler

create procedure test_sp_d

exec ('select * from tabler')

when i droping table i do it in transaction so schema is locked

the procedures have to wait for commit and then select

it worked fine sql2000

sql

Monday, March 19, 2012

Drop replication Pull job without Subscription publication databas

Publisher/Distribution job was removed but on Subscriver was not, since then
we are not able to remobe Pull job without Subscription publication database
on Subscriver server, any help how to remove it?
Identify the job by its name and then use sp_dropjob to remove it.
"Jose Milhazes" <Jose Milhazes@.discussions.microsoft.com> wrote in message
news:FFB75B9A-C4EF-42CA-8B27-313E70972490@.microsoft.com...
> Publisher/Distribution job was removed but on Subscriver was not, since
> then
> we are not able to remobe Pull job without Subscription publication
> database
> on Subscriver server, any help how to remove it?
|||Thanks, job was removed
"Hilary Cotter" wrote:

> Identify the job by its name and then use sp_dropjob to remove it.
> "Jose Milhazes" <Jose Milhazes@.discussions.microsoft.com> wrote in message
> news:FFB75B9A-C4EF-42CA-8B27-313E70972490@.microsoft.com...
>
>

Tuesday, March 27, 2012

Dropping and Recreating Tables

Dropping and Recreating Tables

Dropping and Recreating Tables

Wednesday, March 21, 2012

Drop table statement didn't actually drop table...

Drop table statement didn't actually drop table...

drop table

Monday, March 19, 2012

Drop replication Pull job without Subscription publication databas

DSO in SQL2005

Blog Archive

About Me