Hi, when there are lot of processes executing the select statement on a table, and I KILL all these processes (KILL spid) and drop the table, is the KILL statement have the first priority to execute and drop table can be done in short time? I am afraid that the select statement will take long time to be roll back if the table is huge...please advise.
View 11 RepliesHi
We are in the process of moving some data from one structure to another. The complete script takes very long time to complete in out production environment.
The script can maybe filtered even more, so it can run in small steps. The only problem here is that we would like it to be transactional.
But i came to wonder - are there any way to tell before starting a t-sql statement that it should run with low priority? This way it could run for 2 days, without having any severe impact on the production environment?
Best regards
/Anders
Forgive me for what may be a dumb question but i`m having trouble using the kill statement in a stored procedure. The problem is that i always get a syntax error when using the kill statement with a local variable as the parameter.
For example some part of the code is:
declare @myspid smallint
...
exec kill @myspid
...
Please help!
I'm trying to kill a bunch of processes in SQL 6.5 and I can't. I'm running the only machine with SQL tools installed on it (the server) and it won't let me kill them. I try the GUI screens and the Kill statement in ISQL_w. Is there any way around this?
I've stopped the SQL Server and rebooted the NT Server. Is there anyway I can get rid of these processes. They are locking some tables and keeping me from inserting data within my code. Very frustrating.
Thanks
Hi,
consider my table,
s.no priority status
1 2 pending
2 3 pending
3 0 completed
4 1 pending
5 0 completed
Now, if I insert a record as,
6 1 pending
the priority must be reset as follows,
s.no priority status
1 3 pending
2 4 pending
3 0 completed
4 2 pending
5 0 completed
6 1 pending
Im using c# and sql server. How can i do it?
Thanks,
Jasmeeta.
Hi guys.
Is there anyway to increase the priority of a Service in NT services in processing like we have in SQL Server.
-MAK
Hi,
Is it possible to change/set the priority of databases? (Win2K, SQL 2K)
We have an evnironment which is somewhat lacking in resource, and would like to place the priority of one of the databases lower than the other so that the processing on the main database is carried out before the secondary one.
If there isn't any way to do this for the database level - is it possible for a specific user?
Hopefully I havnt been too vague here - but if there is any more info I should be passing on let me know.
Thanks in advance.
Cheers
Hi,
In my Sequence container task, I have 3 data flow task ( one data flow task is based on the constraint option Expression). 3 are running simultaneously, is there a way to assign a priorty which one should finish first before the other starts.
If I make all of them in one sequence then the conditional task come in between which may stop the execution of the 3rd task depending upon condition.
Please Guide.
Is it possible in SQL Server to set the priority at which a query will run
for certain users. I have some users who's queries I never want to
take over the database. These queries may take 10's of mintes to complete,
and will slow down the server and affect the performance of more important queries. Is there a way to keep this from happeneing?
Thanks
Hello. I'm still a newbie in using MSSQL 2000. Got a code here in this site for sending formatted email usp_send_cdosysmail? How do i set the priority of the email?
View 1 Replies View RelatedHi,
I have a table which contains a Create datetimefield which has a default on the current date , a priorityfield and another datetimefield which will be the due date and has to be calculated by the first date and the priority field,
How can i do this and what fields must i have.
Does someone does this?
can someone help me with this?
Kind regards Wim
Hi all.
Does anyone know how to set a database priority? I have a SQL Server that contains multipule databases. One of the databases is very high priority. I need to make sure that no matter what is running on the server that this database's processes take priority and nothing slows it down.
Thanks.
I've searched enough to know that this ability probably doesn't existin our current setup, thus the reason I'm coming to the community now.When doing a database backup in Enterprise Manager (SQL Server 2000sp4), is there a priority level setting so that I can put less load onthe CPU? Our small office uses one server for everything and when I doa 12:00 pm full backup of our live DB, the server slows to a crawl forabout 4 or 5 minutes. I'd like to see the backup take longer but putless load on the server so we can to use it. If this setting doesn'texist, I may need to explore another backup method...but that's anothertopic for another day.Thanks,Gabe
View 9 Replies View RelatedI have a report with a category that filters for "top N" categories, but it is preventing the entire data set from being evaluated so that the series subtotals are incorrect. Is there a way to change the precedence, so that the subtotals are computed across the entire data set, and the "top N" is evaluated afterwards?
View 4 Replies View RelatedHello,
I have a long stored procedure that performs some inserts and updates on the table that users are accessing through a user interface. We this stored procedure runs users experience slowness, is there any way I can get the stored procedure runs with a lowest priority so that user will be able to perform their tasks first.
I have a table.
Highlight-------------
Id
Name
Detail
StartDate
EndDate
Priority
I want to make a query which returns 1 Highlight in the current date.
But remember I have already set the Hightlight Priority 1 to 5. And I want that Hight Priority rows select more times than Low Priority Rows.
DECLARE @Table TABLE
(minv_code INT,
alert_msg varchar(10),
alert_time Datetime)
INSERT INTO @Table VALUES
(873939, 'Reverse', '7/24/2015 3:31:18'),
(873939, 'Tamper', '7/24/2015 3:30:00'),
(873939, 'Meter', '7/24/2015 3:31:22'),
(873940, 'Reverse', '7/24/2015 3:30:00'),
(873940, 'Tamper', '7/24/2015 3:31:22')
I want to select the data priority wise . The o/p should look like
first row - 873939, 'Meter', '7/24/2015 3:31:22'
second row - 873939, 'Tamper', '7/24/2015 3:30:00'
third row - 873939, 'Reverse', '7/24/2015 3:31:18'
fourth row -873940, 'Tamper', '7/24/2015 3:31:22'
fifth row - 873940, 'Reverse', '7/24/2015 3:30:00'
Is there a way to give a particular query greater system resources,such as share of the processor?I have two queries that were started from Query Analyzer. One is verylong, the other rather short. However the long one is taking almostall the processor resources and not allowing the short one to complete.I'd like to allocate more of the processor to the short one until itis done.I am the admin on this system and have access to Enterprise Mgr.Thanks.
View 1 Replies View Related
Not sure if this is the forum to post this question to - if there's a more appropriate forum please let me know.
I have a SQL Server 2005 box which gets accessed in two ways - an application with it's own login and sprocs which get accessed through reporting services with its own login. What I've found is that the application will get timeouts on its queries when people are running reports which use time intensive sprocs (> 1 minute to run).
Is there a way to give a particular login a higher priority so that it won't timeout when other logins access these sprocs?
Thanks!
Craig
Hi all,
I have a stored proc. which is going to run 3 days long. So , i was looking for some setting (or) priority , which i can set in the stored proc.
so that, if someone else is using the server, they should get high CPU Priority, and my process ( Stored Proc) should remain in suspended state, until CPU & Disk activity is low.
Note : The only way others are getting affected is because of hardware resources, because nobody is using those tables which is being used by STORED PROCEDURE.
Can anybody tell plz, how to achieve this.
Thanks.
I want to priorities three items one by one every next day Item1, Item2 and Item3.For example:
Feb 01, 2015: Item1, Item 2, Item3
Feb 02, 2015: Item2, Item3, Item1
Feb 03, 2015: Item3, Item1, Item2
Feb 04, 2015: Item1, Item2, Item3
Hi all,
Our company relies a lot on the fulltext search, so my question is that how to start fulltext search in high priority (I have not seen that option as sql database engine, which you can select to start as high priority). Another question is that whether we can fulltext index a partitioned table and if possible how to do that.
Thanks,
Ning
Hello,
I have a long stored procedure that performs some inserts and updates on the table that users are accessing through a user interface. We this stored procedure runs users experience slowness, is there any way I can get the stored procedure runs with a lowest priority so that user will be able to perform their tasks first.
Hi,
Im using a stored procedure to send an email using CDOSYS. Im using the code i got here
http://support.microsoft.com/defaul...;312839&sd=tech
I can successfully send an email, but would like to know how i can add additional fields like setting the importance or priority of the how email. How would i change this stored procedure to do this.
here is the code...
CREATE PROCEDURE [dbo].[sp_send_cdosysmail]
@From varchar(100) ,
@To varchar(100) ,
@Subject varchar(100)=" ",
@Body varchar(4000) =" "
/************************************************** *******************
This stored procedure takes the parameters and sends an e-mail.
All the mail configurations are hard-coded in the stored procedure.
Comments are added to the stored procedure where necessary.
References to the CDOSYS objects are at the following MSDN Web site:
http://msdn.microsoft.com/library/d...s_messaging.asp
************************************************** *********************/
AS
Declare @iMsg int
Declare @hr int
Declare @source varchar(255)
Declare @description varchar(500)
Declare @output varchar(1000)
--************* Create the CDO.Message Object ************************
EXEC @hr = sp_OACreate 'CDO.Message', @iMsg OUT
--***************Configuring the Message Object ******************
-- This is to configure a remote SMTP server.
-- http://msdn.microsoft.com/library/d...n_sendusing.asp
EXEC @hr = sp_OASetProperty @iMsg, 'Configuration.fields("http://schemas.microsoft.com/cdo/configuration/sendusing").Value','2'
-- This is to configure the Server Name or IP address.
-- Replace MailServerName by the name or IP of your SMTP Server.
EXEC @hr = sp_OASetProperty @iMsg, 'Configuration.fields("http://schemas.microsoft.com/cdo/configuration/smtpserver").Value', 'MailServerName'
-- Save the configurations to the message object.
EXEC @hr = sp_OAMethod @iMsg, 'Configuration.Fields.Update', null
-- Set the e-mail parameters.
EXEC @hr = sp_OASetProperty @iMsg, 'To', @To
EXEC @hr = sp_OASetProperty @iMsg, 'From', @From
EXEC @hr = sp_OASetProperty @iMsg, 'Subject', @Subject
-- If you are using HTML e-mail, use 'HTMLBody' instead of 'TextBody'.
EXEC @hr = sp_OASetProperty @iMsg, 'TextBody', @Body
EXEC @hr = sp_OAMethod @iMsg, 'Send', NULL
-- Sample error handling.
IF @hr <>0
select @hr
BEGIN
EXEC @hr = sp_OAGetErrorInfo NULL, @source OUT, @description OUT
IF @hr = 0
BEGIN
SELECT @output = ' Source: ' + @source
PRINT @output
SELECT @output = ' Description: ' + @description
PRINT @output
END
ELSE
BEGIN
PRINT ' sp_OAGetErrorInfo failed.'
RETURN
END
END
-- Do some error handling after each step if you have to.
-- Clean up the objects created.
EXEC @hr = sp_OADestroy @iMsg
go
Hi,
Im using a stored procedure to send an email using CDOSYS. Im using the code i got here
http://support.microsoft.com/default.aspx?scid=kb;en-us;312839&sd=tech
I can successfully send an email, but would like to know how i can add additional fields like setting the importance or priority of the how email. How would i change this stored procedure to do this.
here is the code...
CREATE PROCEDURE [dbo].[sp_send_cdosysmail]
@From varchar(100) ,
@To varchar(100) ,
@Subject varchar(100)=" ",
@Body varchar(4000) =" "
/************************************************** *******************
This stored procedure takes the parameters and sends an e-mail.
All the mail configurations are hard-coded in the stored procedure.
Comments are added to the stored procedure where necessary.
References to the CDOSYS objects are at the following MSDN Web site:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/cdosys/html/_cdosys_messaging.asp
************************************************** *********************/
AS
Declare @iMsg int
Declare @hr int
Declare @source varchar(255)
Declare @description varchar(500)
Declare @output varchar(1000)
--************* Create the CDO.Message Object ************************
EXEC @hr = sp_OACreate 'CDO.Message', @iMsg OUT
--***************Configuring the Message Object ******************
-- This is to configure a remote SMTP server.
-- http://msdn.microsoft.com/library/default.asp?url=/library/en-us/cdosys/html/_cdosys_schema_configuration_sendusing.asp
EXEC @hr = sp_OASetProperty @iMsg, 'Configuration.fields("http://schemas.microsoft.com/cdo/configuration/sendusing").Value','2'
-- This is to configure the Server Name or IP address.
-- Replace MailServerName by the name or IP of your SMTP Server.
EXEC @hr = sp_OASetProperty @iMsg, 'Configuration.fields("http://schemas.microsoft.com/cdo/configuration/smtpserver").Value', 'MailServerName'
-- Save the configurations to the message object.
EXEC @hr = sp_OAMethod @iMsg, 'Configuration.Fields.Update', null
-- Set the e-mail parameters.
EXEC @hr = sp_OASetProperty @iMsg, 'To', @To
EXEC @hr = sp_OASetProperty @iMsg, 'From', @From
EXEC @hr = sp_OASetProperty @iMsg, 'Subject', @Subject
-- If you are using HTML e-mail, use 'HTMLBody' instead of 'TextBody'.
EXEC @hr = sp_OASetProperty @iMsg, 'TextBody', @Body
EXEC @hr = sp_OAMethod @iMsg, 'Send', NULL
-- Sample error handling.
IF @hr <>0
select @hr
BEGIN
EXEC @hr = sp_OAGetErrorInfo NULL, @source OUT, @description OUT
IF @hr = 0
BEGIN
SELECT @output = ' Source: ' + @source
PRINT @output
SELECT @output = ' Description: ' + @description
PRINT @output
END
ELSE
BEGIN
PRINT ' sp_OAGetErrorInfo failed.'
RETURN
END
END
-- Do some error handling after each step if you have to.
-- Clean up the objects created.
EXEC @hr = sp_OADestroy @iMsg
go
Hi,
I've noticed declining performance on our main accounting server. We have a Windows 2003 server running SQL 2000 Enterprise and 8 GBs of RAM.
Has anyone used the "boost SQL Server priority on windows" before? If so, were there any major benefits in using it?
This server is excusively used for SQL btw...
Thanks,
DJ
DECLARE @Table TABLE
(minv_code INT,
alert_msg varchar(10),
alert_time Datetime)
INSERT INTO @Table VALUES
(873939, 'Reverse', '7/24/2015 3:31:18'),
(873939, 'Tamper', '7/24/2015 3:30:00'),
(873939, 'Meter', '7/24/2015 3:31:22'),
(873940, 'Reverse', '7/24/2015 3:30:00'),
(873940, 'Tamper', '7/24/2015 3:31:22')
i want to select the data priority wise the o/p should look like
first row - 873939, 'Meter', '7/24/2015 3:31:22'
second row - 873939, 'Tamper', '7/24/2015 3:30:00'
third row - 873939, 'Reverse', '7/24/2015 3:31:18'
fourth row -873940, 'Tamper', '7/24/2015 3:31:22'
fifth row - 873940, 'Reverse', '7/24/2015 3:30:00'
Hello everyone
Here is the situation I'm trying to set-up a package to ftp some files down if that part fails email me but if successful rename the 2 files to something else.
so what I set-up was a FTP tasks with the remote path set as a expresion since I needed to Download todays files.
no problem there have that part working.
so then I set a notifaction event with failure constraint to email me. pretty easy so far.
then I set 2 tasks to rename the 2 files downloaded I'm sure I could do this part with a loop but what seperate tasks so I knew if any failed or could set-up notifaction tasks if I wanted. in the 2 rename tasks I set the sourcepath to an expression so it would know to read todays files and they could change from day to day.
then the destination part is hard coded for now to a file name.
now here is were the issue comes in I go to save it and it says the files don't exist from the rename piece. well of course not they haven't been FTPed yet.
so I tried delayevaluation on both the file system tasks but a no go still errors.
What I need it to do is basically say wait till the ftp is run these files won't be there. any help in the right direction would be appreciated.
thank in advance
Troy
A similar question has been asked in a different thread, but didn't get a conclusive answer. Is there a way to set process priority on a specific process (alternatively - processes started by a specific user), similar to what the Task Manager allows in Windows?
I need to archive some data from an OLTP database. Unfortunately, some of the tables are growing rather large, and copying the data, and especially removing it based on record age, is a resource-intensive operation. My database must be available 24/7, and I can't have queries timing out, or I start losing business real fast. I was thinking of firing off the archive script and setting the process priority on it to something lower than normal, so it won't interfere with my OLTP processes.
Maybe someone can suggest a better solution?
Hi,
I need information on adjusting the priority of the replication thread(s) within SQL Server 2000. All I've been able to find is how-to's on adjusting the priority of the SQL Server process itself, as well as information on how to adjust the relative priority of changes made to the database by subscribers.
Here's the situation, from time to time our replication system goes down, it starts itself up after waiting 5 minutes but this causes a problem. When its starting up it seems to be taking up far too many CPU cycles. As such the server is unable to process SQL queries, which forces its subscribers to time out. This is a bad thing as the subscribers are control systems on an assembly line. What I want to do is lower the priority of replication so that the server can still process queries while the replication agent is restarting itself. Is this possible? If so how can it be done?
Thanks,
Geoff Dupuis
Hi,
Anyone can help me for the Sql query?
I want to sort a table with a priority column, e.g. in the following...
Table A
======
value
======
9
3
1
7
4
======
After sorting:
Table A
========
no value
========
1 1
2 3
3 4
4 7
5 9
========
Anyone can help me?
Thanks.
Daniel.
Hi all,I have a table with this structure:Customer ID | Transaction date | Transaction type1 | 1/2/2008 | F1 | 1/4/2007 | M1 | 1/2/2008 | R2 | 1//5/2007 | M2 | 1/6/2007 | RFor each customer ID, I need to retrive only the records with the mostrecent transaction date, and I did:Customer ID | Transaction date | Transaction type1 | 1/2/2008 | F1 | 1/2/2008 | R2 | 1/6/2007 | RThe problem is, each customer can make more than one transaction inthe same day (see above). In these cases, I need to retrieve only onerecord per customer, based on the importance of the transaction: F ismore "important" than R which is more "important" than M. So, in theexample above, I would only retrieve transaction F for customer 1 anddisregard transaction R: they were made on the same date, but I ammore interested in F than in R.Do you have any suggestions on how I could achieve this in SQL?I use Microsoft SQL server 2005.Thank you for your help!
View 1 Replies View RelatedI have a package that loads staging tables from an Oracle source DB. In the data flow tab I have 30+ read table/write table task combinations. When I run the package 3-4 of the read/write combos execute at a time. What I'm trying to control is the priority order of the combo execution. My goal is to minimize to total load time by having the larger table transfers run first and the smaller table transfers fill in until they are all complete. Currently, the largest table (16 million) transfers last (because it was the last combo that I created?).
Thanks,
Dave