Want To Block All Other Db Activity While My Archive Process Is Running...
Oct 29, 2007i'd like to block all other processes that interact with my database while my archive process is running on that database. Can this be done?
ADVERTISEMENT
Process Block Itself
Jul 3, 2007Hi to everybody,
i have this problem.
sometimes a process in my SQL Server 2005 (upd 2) is blocked from itself. If I find in monitor it wait for a lock_M_Sch_M on a temporary table as you can see from
select * from sys.dm_tran_locks where request_session_id=51 and request_status <> 'GRANT'
resorce type : OBJECT
resource id : 218899226
request mode : Sch-M
request status WAIT
request_session id : 51
request life time : 33554432
or from
SELECT * FROM sys.dm_os_waiting_tasks WHERE SESSION_ID=51
waiting task address : 0x0000000000C2F198
session id : 51
wait type : LCK_M_SCH_M
resource address : 0x0000000201C71300
blocking task address : 0x0000000000C2F198
blocking session id = 51
resource description objectlock lockPartition=0 objid=218899226 subresource=FULL dbid=2 id=lock80d04900 mode=Sch-S associatedObjectId=218899226
I resolve this problem only with restart of sql server . that clear tempdb and eliminate this process
I think is not a problem about latch present from sql server 2000 sp4 (I read some document abount this problem)
So I haven't idea how to resolve this problem and how to kill this kind of process without to restart sql server
thanks in advance
Luca
End A Blocking Process (Was Block)
Feb 11, 2008Ho do I clear a kill process blocking others to run
View 3 Replies View RelatedCurrent Activity - Process Info
Jan 12, 2007Hi, please i need to know where sql save (table) the info that i see in Managment/Current Activity - Process Info
thanks so much
LOG: Process ID 59:0 Owns Resources That Are Block
Apr 11, 2007Process ID 59:0 owns resources that are blocking processes on Scheduler 2
I am getting tons of this into sql server log second day in a row (2000 sp4)
Noticed heavy usage prior
What is causing it? Solution?
Kalman Toth, Database, DW & BI Architect
SQL Server 2005 Training - http://www.sqlusa.com
Duplicate MAC In Current Activity -> Process Info
Jun 15, 2001Just recently I noticed something that I consider very strange on a SQL7.0 server with SP1.
In [Server -> Management -> Current Activity -> Process Info] I see a great number of rows(connections) around 70 or so that all have the same net_address value. However, the nt_loginame is different on each.
Q1: How can this be?
Q2: Isn't the net_address really the MAC of the network card?
Also, on a test SQL7.0 server with SP1 where the connections are far less (around 15 or so), I noticed the same problem. Luckily, because the connection count is low I was able to go around to each machine where the nt_loginame was used to log on and found that the net_address shown in [Current Acivity] didn't match any of these machines. Why?
-John
Managment/ Current Activity / Process Info
Jan 12, 2007Hi, I need to know where SQL save the info that I see in the Managment/ Current Activity / Process Info
thanks so much
Does A Background Process Block Sp_dboption Command
Jan 25, 2008Does an sp_dboption 'dbname','single user',true command execute succesfully when a background process is working on a database.
View 3 Replies View RelatedProcess Info (SQL Server Enterprise - Management - Current Activity)
Jul 23, 2005Dear AllI have problem with my database server which running SQL server 2000.The server running very slow. The worst case, to save a record requiredmore than 20-30 seconds.Since this problem, I usually monitoring Process Info from EnterpriseManager (Management - Current Activity), and I found a misteriousprocess as follow :1. User: SystemAccessTo: MasterStatus: BackgroundCommon: Task ManagerWaiting: >438 Million2. User: SystemAccessTo: MasterStatus: BackgroundCommon: Task ManagerPhysical IO: > 510003. User: Administrator (Join domain)Database: MSDBStatus: SleepingCommon: Awaiting CommandApp: SQL Agent Alert EngineCPU Usage: > 16 MillionAnybody know about these condition? Does it normal?ThanksMichael
View 3 Replies View RelatedSQL 2012 :: Job Activity Monitor - Start Time Of Currently Running Job?
Apr 14, 2015Is there anywhere in the SSMS to see the start time of a currently running job, currently running it's first step, without needing to run a query?
If not, why would Microsoft decide to not show it in the job activity monitor?
SSMS = Sql Server Management Studio
Performance Problem, Lots Of Disk Activity, Running Out Of Memory
Jul 20, 2005Fellas!!This is a very complicated one and it took me a few days to figure outexactly what's going on, but here's the final story:I have a production environment running on .NET with a SQL Server(2000, SP3). The SQL Server is on a dedicated Proliant computer with2GB RAM (the actual SQLServer.exe process has dynamic memoryassignment and can reach up to 1.6GB RAM). Nothing else is running onthat specific computer.Once the SQLServer is started, it hits 300MB RAM (the minimum that wasset in the configuration of the server - remember, it is dynamicallyaquired).Then there is a .NET program that requests just about all the data theSQL Server contains (apart from a single table that contains roughly1.6 million rows and another table that contains about 10000 rowswhich are all of type IMAGE).Once all the data is retrieved, the RAM is at about 400MB. From thereon, every update I make to the data on the server causes the RAM to goup by a bit (that updates are done in a Transaction which of course iscommitted at the end). It seems that BLOB updates are the majorproblem in all of this. For some reason, uploading a blob of size 9MBcauses the RAM to go up by roughly 20MB and after commit it gose down10MB (total gain of roughly 10MB RAM). Eventually the SQLServerprocess hits its upper limit (1.6GB) and at this point it startsslowing down.Some performance checks showed me the SQLServer has a lot of diskactivity, it seems it is reading and writing pages of data from/to theHD all the time (which causes the queries to be much much muchslower).We have a development environment running the exact same code (it isthe exact same in everything, except for the amount of data stored inthe DB). This does not happen there at all.I have a few questions:1. Why is the RAM going up after BLOB updates?2. Why is the RAM going up at all?3. How can I tell the DB which tables should remain in the RAM at alltime (never swapped back to the HD?) - DBCC PINTABLE does not seem todo the job.It does not seem to have anything to do with the .NET code.Thank you very much,M Yamo.
View 4 Replies View RelatedUser Connection Changes In Activity Monitor When Running Stored Proc
May 16, 2007Please forgive the simplicity of this question - I am not the dba type. When I connect to a server and look at my connection attributes in activity monitor, the user column shows the correct information for my domainusername. When I run a certain stored procedure in that connection, the domainusername changes to another person. We are not using execute as, setuser, or anything special to explicitly change the user. The stored procedure is in a schema that is owned by dbo (principal_id = 1 - I verified by checking sys.database_principals.)
Does anyone have any suggestions?
Thanks in advance,
John Hennesey
T-SQL (SS2K8) :: Why Block Scope Variables Exist Outside Of Block
Dec 3, 2014I have the following stored procedure to test scope of variables
alter proc updatePrereq
@pcntr int,
@pmax int
as
begin
[Code] ....
In the above script @i is declare in the if block only when the @pcntr value is 1. Assume the above stored procedure is called 5 times from this script
declare @z int
set @z = 1
declare @max int
set @max = 5
while @z <= @max
begin
exec dbo.updatePrereq @z, @max
set @z = @z + 1
end
go
As i said earlier `@i` variable exists only when `@pcntr` is `1`. Therefore when i call the stored procedure for the second time and so forth the control cannot enter the if block therefore @i variable wouldn't even exist. But the script prints the value in `@i` in each iteration, How comes this is possible should it throw an error saying `@i` variable does not exist when `@pcntr` values is greater than `1`?
Long Running Process
Jan 10, 2001Hi all,
I have 3 three scheduled job: one runs onece a day, one runs once per hour, and another runs every 17 minutes. It is a NetIQ application. I just scheduled SQL Server maintianace job last night which ran at 2:00Am and 4:00Am. This morning, I came in office and found all my jobs were still running; and they were all blocked by the first 3 jobs. I had to kill all of them. In this afternoon, I kicked off one of my many DTS packages which runs usually about 40 minutes, but it failed. I tried several times but no luck. I suspected one of user tables corrupted or one of stored procedures corrupted. After I recycle the server, and dropped the table and the stored procedure, and recreated them, the package went fine. The store procedure involves many updates and inserts.
The question I have is: is it possible to cause this problem because I killed the unfinished jobs (especially the sql maintanace job)?
NOTE: the sql maintanace job does not include the backup of database and transaction log.
Exact SQL For Running Process
Aug 27, 2007
For a long running stored procedure, how can I determine the SQL statement within the stored procedure that is currently running?
The Activity Monitor only shows the name of the stored procedure and whether it is a SELECT/INSERT/UPDATE, never the complete statement.
Thank you.
Running Process() Within SQLCLR
May 11, 2006By using impersonation I am able to push data to network path / old fashioned DBF's. Of course, I must use unsafe. I am unable, however to successfully run a "cmd.exe" process(), even if I populate the Username, Domain and password (with securestring). It authenticates correctly (checked by giving wrong password or bad username). But when it runs (even just a "cmd.exe"), I get an access is denied error...
StreamWriter sw = File.CreateText("C:\Error.txt");
WindowsIdentity clientId = null;
WindowsImpersonationContext impersonatedUser = null;
clientId = SqlContext.WindowsIdentity;
impersonatedUser = clientId.Impersonate();
System.Security.SecureString password = new System.Security.SecureString();
foreach (char c in "secret")
password.AppendChar(c);
Process p = new Process();
ProcessStartInfo si = new ProcessStartInfo("cmd.exe");
si.UserName = "MyName";
si.Password = password;
si.Domain = "MySecretDomain";
si.UseShellExecute = false;
try
{
p.StartInfo = si;
p.Start();
}
catch (Exception ex)
{
//handle the exception here (This exception handler will not handle the exception, but I get a
//Window popup (While executing my CLR!!!)
}
finally
{
sw.Close();
}
The message popup says: The application failed to initialize poperly (0xc0000142). Click on OK to terminate the application. I'm kind of at a loss...
I'm using a Windows 2003 server box with the latest SQL Server 2005, .NET 2.0/2005. Let me know if you need anything else.
Oh, just FYI - I am moving replicated data from SQL2005 server to a legacy app using DBF (FoxPro driver). I really need an external DOS app to process some data for the DOS application (Clipper).
Throttling A Long-running Process
Apr 13, 2007I have a SQL procedure that can take several minutes to complete. I allow users to initiate the process through a web site and view a progress bar. When the process is running, though, the site slows to a crawl or times out completely. That long running process seems to block all other queries on the database. Is there a way to give this process a low priority or somehow throttle its resource use so that the other web processes can get a chance to run in a timely manner?
Thanks for any advice.
Kill Process That's Running Xp_cmdshell
Feb 1, 2001I executed xp_cmdshell command.
More than 24 hours this process still running.
I tried killing this process with enterprise manager and query analyser, both
gave me a message saying its successfully killed. But when i do a sp_who,
the process still their executing.
how can I kill this process that's running xp_cmdshell
thanks
Running C#.exe From Execute Process Task
Mar 13, 2008My problem is that I am trying to set up an Execute Process Task to run a C# executable. The .exe captures information from an ftp site and downloads it into a directory on our server. Here is the command line run:
C:SAXOEodinReleaseSaxoEod
I have set the Execute Process Task up as follows:
Require Full name: True
Executable: C:SaxoEODobjReleaseSaxoEod.exe
FailTaskIfReturnCodeIsNotSuccessValue: False
SuccessValue:0
TimeOut:0
WindowStyle:Normal
I have the FailTaskIfReturnCodeIsNotSuccessValue set to False because when it is set to true I get the following error:
Error: 0xC0029151 at Execute Process Task, Execute Process Task: In Executing "C:SaxoEODobjReleaseSaxoEod.exe" "" at "", The process exit code was "-532459699" while the expected was "0".
Setting FailTaskIfReturnCodeIsNotSuccessValue to False does not work either because the files are not downloaded from the FTP site.
I am relatively new to the SSIS arena. I have googled this thing to death and have not found an answer. Any help in the right direction is appreciated. Thanks.
Dave Christman
Process Task Hangs Running Bcp
Jun 26, 2006
We are attempting to use Microsoft SQL Server Integration Services Designer
Version 9.00.2047.00 to run a BCP command from a Process task. We are able to do so without a problem when running it interactively, but when run as a SQL Server Agent job the bcp process never completes and the job hangs indefinitely.
We also tried using a Script task for this, with the same result.
Has anyone else run into this problem?
Thanks,
Ron
Obtain The Query Plan Of A Running Process
Sep 21, 2006Hi,Is there a way to findout the query plan of the executing process usingthe SPID/KPID information.Thanks in advance,Thyagu.D
View 1 Replies View RelatedError In DTSX At The End Of Long Running ETL Process
Feb 14, 2006Hi,
We have a DTSX in which parallel threads are processing ETL. There is a Final step in the DTS to do cleanup tasks. we are getting the following error after ETL completes. This is not consistently reproduced.
Description: An OLE DB error has occurred. Error code: 0x80004005. An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80004005 Description: "Login timeout expired". An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80004005 Description: "Unable to complete login process due to delay in prelogin response". An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80004005 Description: "Shared Memory Provider: Timeout error [258].
Connection manager is set as Localhost.
Can somebody help us out here?
thanks
The Process Is Running And Is Waiting For A Response From One Of The Backend CONNECTI
Sep 22, 2006We are useing merge replication with MSSQL Server 2000 for our two location, we have more than 6 database in replication on has issue following error and few time not updated data due to this error -
The process is running and is waiting for a response from one of the backend CONNECTION
With regards
R.MAll
What Task Can I Use To Stop My Control Flow Process From Running
Apr 24, 2007Hi
What SSIS Task or process can i use to stop my Control Flow Process from running?
I created a SQL Task to do a count on a table to see if there is data, if the count is > 0 then the Control Flow task must continue, else it uses a RAISERROR statement which i use with the event handler, but i want to put something in the event handler to stop the process then and not continue?
Any help will be greatly appreciated.
Kind Regards
Carel Greaves
Execute Process Task - Warnings When Running DTExec
Jun 22, 2007I have a manager SSIS package that calls a generic loader SSIS package, via the 'Execute Process Task'. The manger package (amongst other things) creates a config file and and then executes DTExec passing the config specification in the Arguments property, i.e. '
'/FILE "D:AudiencePackagesUKDW_Core_Loader_Persist.dtsx" /CONFIGFILE "D:AudienceDataSCPmystery.dtsconfig" /MAXCONCURRENT " -1 " /CHECKPOINTING OFF"
It works as expected, but, I always get the warning message 'File/Process "DTExec" is not in path.', I know that it is because the exe runs and loads the correct package which then performs the correct actions.
Have I dropped the ball here? I cannot see any reason for the warning as DelayValidation==True, I could set RequireFullFileName==True, but I do not know where SQL is installed on the various nodes.
Any input would be warmly received
Thanks
DB Engine :: Running Performance Monitor While ETL Process Is In Progress?
Jul 21, 2015know if running performance counters during ETL process is running will impact performance on the server, I have win 2008 r2 server with sql server 2008 r2. I want to measure performance of server while ETL is in progress.
View 4 Replies View RelatedSQL 2012 :: Execute Process Task Error When Running Under Agent?
Jul 11, 2014I have an execute process task in SSIS that executes a .Net program.When this is running as a Sql Server Agent job in Sql Server 2005, I have no issues. The operating system of the server is Windows Server 2003 R2.
Now I'm trying to get this working as a Sql Server Agent job in Sql Server 2012. The operating system of the server is Windows Server 2012 R2. And now I have issues.
If I run the SSIS package in Visual Studio debug mode, I have no issues! It's only when I try running the same package from a Sql Server Agent job that there are problems. The .Net program errors out with a System.TypeInitializationException.
I thought this might be an issue running the .Net program as 64 bit so I changed the agent setting to 32 bit. That didn't work. I thought it might be an issue with authorization (since I can run it in debug with me as the user). I put in a class in the program that changes the user through impersonation. That didn't work either.
Since I don't have a full-blown version of Visual Studio on the database server, I haven't been able to capture the error and debug the program to see where it is blowing up.
why something like this would work in Sql Server 2005/Windows 2003 but not in Sql Server 2012/Windows 2012 when run as an agent job? And on top of that, why would it work when I run it manually in debug mode but not when run as an agent job?
Running EXEs On Prod SQL Box Through Execute Process Task - Best Practices
Apr 8, 2008
Hi All,
We're trying to run an EXE from SSIS through "Execute Process" task.
The EXE folder contains other DLLs as well.
The EXE interacts with the database and reporting services and sends some e-mails(max 500 a day) out to customers.
My question is:
Is it ok to run this kind of EXE on the production SQL box?
If not, why?
(People argue that running EXEs is not advisable on production boxes)
Q: Why did Microsoft introduce "Execute Process" task when we cannot run EXEs on the production box?
If somebody can educate whether it's ok to run such EXEs on prod SQLs.
In either case, some explanation is greatly appreciated.
Thanks,
Siva.
Integration Services :: Error Running A Fuzzy Grouping Process
May 26, 2015I have a table that I need to identify similarities so I'm running a Fuzzy Grouping Process. I'm getting the follow errors and I can't identify the problema since all the fields are varchar, except for the first that is int but not use in the fuzzy.
select
MSSEndCustomerTPID
, orgname
, address1
, cityname
, statename
, countryname
from [sales].[vw_Fact_VolumeSales] a
inner join [GMOFBI].[dbo].[vw_Dim_MSS_Organization] b
on a.EndCustomerOrganizationKey=b.MSSOrganizationKey
[code]...
Running A Ssis Package In Sql Server Agent With Execute Process Task
Apr 1, 2008Hello
I'm trying to run a task that executes a script file (cmd). When i run it with in bids with my own users (domain admin) it works. When i start a cmd prompt and try to run the cmd file directly from the network location where it is it works (with my own rights and with the sql server agent user).
Now when i try to run in from smss > agent jobs > job and run job it never completes. Im not getting any error message either it just keeps on running on the step ??? It seems like a rights issue, but the account running the sql server agent is able to execute the cmd file directly from the command prompt.
There are no errors in any error logs anywhere and no error is displayed...
Ps. Im running the job step as a integration service pacgake.
Transaction (Process ID 135) Was Deadlocked On Lock Resources With Another Process And Has Been Chosen As The Deadlock Victim.
Nov 14, 2007
Hi,
I was trying to extract data from the source server using OLEDB Source and SQL Server Destination when i encountered this error:
"Transaction (Process ID 135) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.".
What must be done so that even if the table being queried is locked, i wouldn't experience any deadlock?
cherriesh
FCB::Open: Operating System Error 32(The Process Cannot Access The File Because It Is Being Used By Another Process.) Occurred W
Dec 3, 2007Hello all,
I am running into an interesting scenario on my desktop. I'm running developer edition on Windows XP Professional (9.00.3042.00 SP2 Developer Edition). OS is autopatched via corporate policy and I saw some patches go in last week. This machine is also a hand-me-down so I don't have a clean install of the databases on the machine but I am local admin.
So, starting last week after a forced remote reboot (also a policy) I noticed a few of the databases didn't start back up. I chalked it up to the hard shutdown and went along my merry way. Friday however I know I shut my machine down nicely and this morning when I booted up, I was in the same state I was last Wenesday. 7 of the 18 databases on my machine came up with
FCB:pen: Operating system error 32(The process cannot access the file because it is being used by another process.) occurred while creating or opening file 'C:Program FilesMicrosoft SQL ServerMSSQL.1MSSQLDataTest.mdf'. Diagnose and correct the operating system error, and retry the operation.
and it also logs
FCB:pen failed: Could not open file C:Program FilesMicrosoft SQL ServerMSSQL.1MSSQLDataTest.mdf for file number 1. OS error: 32(The process cannot access the file because it is being used by another process.).
I've caught references to the auto close feature being a possible culprit, no dice as the databases in question are set to False. Recovery mode varies on the databases from Simple to Full. If I cycle the SQL Server service, whatever transient issue it was having with those files is gone.
As much as I'd love to disable the virus scanner, network security would not be amused. The data and log files appear to have the same permissions as unaffected database files. Nothing's set to read only or archive as I've caught on other forums as possible gremlins. I have sufficient disk space and the databases are set for unrestricted growth.
Any thoughts on what I could look at? If it was everything coming up in RECOVERY_PENDING it's make more sense to me than a hit or miss type of thing I'm experiencing now.
[Execute Process Task] Error:The Process Exit Code Was -1 While The Expected Was 0.
Mar 11, 2008Dear list
Im designing a package that uses Microsofts preplog.exe to prepare web log files to be imported into SQL Server
What Im trying to do is convert this cmd that works into an execute process task
D:SSIS ProcessPrepweblogProcessLoad>preplog ex.log > out.log
the above dos cmd works 100%
However when I use the Execute Process Task I get this error
[Execute Process Task] Error: In Executing "D:SSIS ProcessPrepweblogProcessLoadpreplog.exe" "" at "D:SSIS ProcessPrepweblogProcessLoad", The process exit code was "-1" while the expected was "0".
There are two package varaibles
User::gsPreplogInput = ex.log
User::gsPreplogOutput = out.log
Here are the task properties
RequireFullFileName = True
Executable = D:SSIS ProcessPrepweblogProcessLoadpreplog.exe
Arguments =
WorkingDirectory = D:SSIS ProcessPrepweblogProcessLoad
StandardInputVariable = User::gsPreplogInput
StandardOutputVariable = User::gsPreplogOutput
StandardErrorVariable =
FailTaskIfReturnCodeIsNotSuccessValue = True
SuccessValue = 0
TimeOut = 0
thanks in advance
Dave