Help?DTS jobs/linked servers preventing Progress db backups?
-
The problem is the network admin and Progress dba are blaming my SQL jobs (that pull data daily from Progress databases) for preventing Progress database backups from completing. I need to know how to determine if this is the case; and to learn what I should be doing in my DTS jobs or reports (some of which connect directly to Progress using OPENQUERY/linked servers or Progress ODBCs) to make sure connections are closed. Here are details...
To populate a SQLServer 2000 datawarehouse, several DTS jobs pull data nightly from several Progress databases. A few jobs run hourly througout the day to keep important tables fairly current. In addition, users run some of my reports (in VB, Excel, Crystal). Although most reports pull from the SQLServer databases, there are many that get real time information via linked servers that connect to the Progress databases.
My jobs quit running long before the backups run; and no users should be running reports during that time either (which is about 1am). They might leave Excel/Crystal apps open on their pcs though.
The Progress dba is going to reset the Progress odbc broker service which is necessary for any odbc connections (i.e., my DTS jobs, reports) to Progress. However, I'd still like to know if it's my fault. If so, ok. If not, then I want to defend my self.
1. Do the DTS jobs leave connections to the Progress db open even after they complete.
a. some connect via DTS db connections and use a "Transform Data
Tasks"
b. some SQL jobs simply execute a storeprocedure that connects
to Progress via a linked server using OPENQUERY.
2. Regarding reports that could be left open on a user's pc, what is the recommendation for ensuring connections are killed.
a. Does an OPENQUERY called to a linked server leave a connection open. Does it make a difference if the application (Crystal, Excel/VB) that called it is left open? I always have code in vb/ado programs set the db connection = "nothing".
I could run a SQL job to kill all connections. or restart the SQLServer, but I'd rather learn any TSQL/VB programing measures I should take. Any help would be greatly appreciated. Thanks!
smv929 -
Hi,
No dts jobs closes the connection as the job completes. u can query sp_who to see that ur dts still have connection or not ( i mean running query).
Yes sure it may be a problem that ur leaves the report open .
Is there any problem in backup of database while the reports are open.
To defend urself in the best way :
show ur admin the result of sp_who or sp_who2.
If the result have ur wharehouse server name or ip then see why the connection is still open by
querying the spid.
To get more information see this link:
http://www.windowsitpro.com/SQLServer/Article/ArticleID/8992/SQLServer_8992.html
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/adminsql/ad_1_server_4uuq.asp
http://www.dbazine.com/sql/sql-articles/sharma2
HTH
from
Killer
-
Which ODBC driver are you using to link to the Progress database? If its the standard Merant driver, make sure you select 'READ UNCOMMITTED' as the Isolation level on the advanced tab of the ODBC setup.
We ran into very similar problems when querying tables with having other isolation levels set and querying columns that weren't specifically indexed. The query would just run and run and eventually start locking out other connections.
For the DTS packages, make sure 'Close connection on Completion' is selected on the Workflow Properties of the Transform Data Tasks.
--------------------
Colt 45 - the original point and click interface
-
We do use the Merant 3.7 driver. The "Read Uncommitted" is checked. However, it's useful to know that does indeed make a difference. I always selected it just in case.
I changed the workflow property on all the DTS packages to "disconnect after completion" as suggested above. This seemed to help. The processes ran ok last night.
Thanks to hall for helping through this situation as well as teaching me something in the meantime.
smv929
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply