hello
i have a question regarding sql agent. Is it ok to restart the agent any time (i need to restart it for enabling the email alert option in sql agent properties..otherwise it doesnt work)? Does restarting sql agent affect replication jobs and other scheduled jobs? Are there any issues restarting agent on production?
Any help appreciated
Thanks
I have a problem where developers will stop the SQLServer service during the day and then they will not remember to restart the SQLAgent. It does not seem to restart when you stop the Server service and restart the services with out a reboot. Is there a setting somewhere in Enterprise Manager where I can tell the Agent service to restart with the Service service?
View 4 Replies View RelatedHello everybody
I setup Sql agent for autostart with sql server and autorestart if stoped
but after rebooting NT or after stoping SQL server it never starts ,I have to start it manualy
Where to look for problem ?
I need to restart (becouse of the Database Mail :( ) SQL Server Agent on a live server which acts as a distributor for a lot of replications. I know that it shouldn't cause any problem, but I want to confirm that it want couse a subscriptions to be reinitiated.
Thanks in advance for quick reply
my VS 2005 keeps on restarting even if im executing a small query only. what could be causing this?
View 2 Replies View RelatedIf SQL is restarted for any reason, I need SQL server to send emails to others letting them know that the server is has restarted once it comes back up.
Is there an Error # that I can build an Alert on? Ideas???
Thank you,
tw
Why does restarting my server improve SQL's performance so dramatically? What can I do to achieve the same effect, without restarting? Thanks for any ideas.
View 8 Replies View RelatedHi,
I am running SQL Server Enterprise manager. I was able to connected to the database properly. But after restarting the PC I am getting the following error.
"System.NullReferenceException: Object reference not set to an instance of an object"
The following event where logged in event viewer.
under security: 560, 562, 538, 528.
under system: 7035, 7036
could anbody give hint how to solve this issue?
Create a table with an Identity column, insert data / restart the server / insert more data / restart the server/ insert some more data.
My data looks like this :
Identity column
1
2
3
1002
1003
1004
1005
2002
2004
It looks like the indentity value gain +~1000 after most server restart (sometimes identity stay the same). This can be very dangerous for some datatype! The only thing google told me was this : URL...but microsoft did not comment on it yet!
I want to schedule a sql job to run at specific time for restarting the sql services(MSSQLServer and SQLServerAgent) and also want to send notification to all the users 5 minutes before restarting them. Please let me know. Thanks in advance.
View 4 Replies View RelatedThe situation is as follows: Database A is being replicated to database B on a networked server. The application vendor is in the process of doing customizations which require frequent table changes. So the vendor (who has access to the replicated DB) stops replication in order to upgrade some tables. But I am finding that either he has forgotten to restart replication or, more than likely, has broken it.
So, if anyone is able to comment on a process which will allow changes to database tables and the restoration of replication, I would be very grateful. thanks in advance.
If you create a #temp table on stored procedure #1
and then call another stored procedure #2 from #1; if stored procedure #2 has an error in it, the #temp table will not release even though stored procedure #1 has a drop table statement in it.
In a cursor, I declare a table variable like so:
DECLARE @TempTable TABLE(RowID INT IDENTITY, valueID int)
I then insert into that table from another table. The purpose is to get a list that looks like this after the insert:
RowID valueID1 348972 345223 94822
etc....
However, the next time through my loop (cursor) I want to restart my RowID identity property, because the next batch of valueID's should then again have a RowID starting from 1.
I tried
delete from @TempTable DBCC CHECKIDENT(@TempTable , RESEED, 0)
but I get 'Must declare the variable @TempTable table' error.
Is there a way to destroy and recreate that @TempTable variable?
Hello,
I have moved the system databases master and mssqlsystermresource database. And after that I was unable to restart the MSSQL Server service. This is SQL Server 2005
I had moved the master db under the minimal configuration. using the parameters /f and /T3608
What can I do to restart the SQL
Thanks
Hello,
I have moved the system databases master and mssqlsystermresource database. And after that I was unable to restart the MSSQL Server service. This is SQL Server 2005
I had moved the master db under the minimal configuration. using the parameters /f and /T3608
What can I do to restart the SQL
Thanks
Hello,
I have moved the system databases master and mssqlsystermresource database. And after that I was unable to restart the MSSQL Server service. This is SQL Server 2005
I had moved the master db under the minimal configuration. using the parameters /f and /T3608
What can I do to restart the SQL
Thanks
Hi,I've got a full text index which works fine, SQLSERVER2000/WIN 2000 SERVER.The system requires to update indexes immediately, so I use a timestampfield to enable this. No problems so far.Now, I've got a stored procedures which nearly daily inserts about 10.000rows. When doing this while full text indexing is active, all users startcomplaining about performance. In order to work around this problem I trieddoing the following ...Create myStoredProcedure-- begin of stored procedureexec sp_fulltext_table 'adsfull', 'stop_background_updateindex'exec sp_fulltext_table 'adsfull', 'stop_change_tracking'---- insert 10.000 rows---- end of stored procedureexec sp_fulltext_table 'adsfull', 'start_change_tracking'exec sp_fulltext_table 'adsfull', 'start_background_updateindex'Now, it seems this doesn't work. SQL Server keeps tracking changes andupdating indexes. Also if I cut away the stop instructions and paste theminto query analyzer before starting the stored procedure.So, if I check the status via select fulltextcatalogproperty('FTADS','Populatestatus') ... it returns value 6 (incremental in progress) insteadof 0 (idle) while executing(0=idle, 1=full population in progress, 6=incremental in progress, 9=changetracking)The only way I can resolve this issue is to stop the indexing via theenterprise manager and to restart after the stored procedure is executed.Any help appreciated.--Kind regards,Perre Van Wilrijk,Remove capitals to get my real email address,
View 1 Replies View RelatedWe are running SQL Server 2014 Web Edition (64-bit). On three occasions now we have had two of our live secondary log shipping databases become unusable through the log shipping process. We ship logs to these databases every five minutes, and generally the process works fine. However, on three occasions in as many months we have suddenly received alerts warning us that the transaction log restores could not be performed on the secondary databases. The SQL Server Error Log revealed the following:
Date,Source,Severity,Message
10/13/2015 04:01:16,spid52,Unknown,Setting database option SINGLE_USER to ON for database 'ObfuscatedName'.
10/13/2015 04:01:17,spid52,Unknown,Starting up database 'ObfuscatedName'.
10/13/2015 04:01:17,spid52,Unknown,Recovery is writing a checkpoint in database 'ObfuscatedName' (8). This is an informational message only.
[code].....
An error occurred during recovery, preventing the database 'ObfuscatedName' (8:0) from restarting. Diagnose the recovery errors and fix them, or restore from a known good backup. If errors are not corrected or expected, contact Technical Support."
The extract above shows two previously successful restores to the secondary database. Suddenly, at 04:11:16, the database can no longer be started up or restored to. This is despite CHECKDB giving a clean bill of health only five minutes earlier. The result is that the last three lines are then repeated ad infinitum.
The only way we have managed to get around this issue so far is to replace the secondary database with a recent backup of the primary. Once this is in place, any outstanding transaction logs are successfully applied when the log shipping restore job next runs. However, this seems rather drastic.It is rather concerning that our warm-standby databases continue to become corrupted without any apparent explanation from SQL Server.
Upon restarting user defined function took seemingly forever to run
I am learning about nested while loops being used in some interdependent user defined functions. They seemed to work OK for a while.
Later, remembering how I lost the database due to hard disk reformatting, I backed up the database and copied it to a rewritable CD.
As the data is not really significant I deleted the database and practiced restoring the database from the CD.
This morning I restarted the user defined function and ran it. After more than half an hour with no result I gave up. Normally it took much less time to run such a user defined function.
I re-ran some other UDF and they worked. However, after I made some minor amendments to the TSQL scripts, saved the UDF and re-ran it, and it again seemed to take forever to run, even when I had set the counter in to while loop to 2.
I don€™t know what had gone wrong.
I went to register my copy of SQL 2005 EXPRESS. It didn€™t seem to help.
Suggestion and advice are much appreciated.
We have an ASP application installed on one powerful Win 2003 andthe SQL Server on another Win 2003.We have two copies of the application (identical, one for productionand one for test purposes) that are operational. The 2nd applicationis connected to the same SQL Server but a test copy of the productiondatabase.Today, running the application, a place where it shows a list of records,the ASP application was timing out after 30 seconds for a lot of users.I backed up the production db and restored it over the test db and testedthe ASP application; within 10-15 seconds the ASP page loaded the listof the records, so i didn't get any timeout. So i started to think thatmaybethe test db when it was being restored, SQL Server would do some kind ofdata cleanup and defragment the new db.But, then i checked the Task Manager, SQL Server was almost using 1.2GBof memory and the server had only 100megs available.As soon as i restarted the SQL Server, the ASP page did no longer timeouton the production database.So now i am trying to figure out why the SQL Server restart fixed myproblem?Unfortunately i did not check if there were a lot of connections in theProcess infounder the Current Activity in SQL EM.Do you think there were a lot of sessions that weren't killed by theapplication?And that was causing the timeout? But why only for the Production db and notalsofor the test db?I mean, i am puzzled as to why before restarting the SQL Server, running theASPapplication connected to the TEST database did not time out!As always, I appreciate any feedback, comments.Thank you
View 1 Replies View RelatedWe just switched from Sql server 2008R2 to Sql server 2012.I am facing one problem with identity Columns "When ever i restarts my sql server,the seed value for each identity column is increased by 1000 (For int identity column it is 1000 and for big int it is 10000).
"For Example if seed value of any table was 3 then after restarting sql server will be 1003 if i again restart sql server it will be 2003 and so on."
After searching on google i found that it is a new feature (don't know what is use of it) in sql server 2012 and having only two solution if you want old identity concept
1. Use sequence object -
a) I am using same database in sql server 2008 and 2012 both so can't use sequence in 2008.
b) if i go with sequence then need not change save procedure for each table,which is bulky task for us.
2. Use Trace Flag 272 (-T272)
I can go with this solution because there is need not do any changes in my application.Some one suggested me that add -T272 in startup parameter,after this sql server identity column will work normal as previous version.I did the same but it is not working.
I don't want to do any changes in my database structure.
how to use this -T272 or why it is not working.
I don't want to use this new identity feature how to suppress it. Why -T272 is not working.
Why does Error log show database restarting everytime a transactionlog backup runs?It also runs a checkdb as well.The log backups are scheduled through a maintenance plan.Is this normal?
View 1 Replies View Relatedwhen I run a package from a command window using dtexec, the job immediately says success.
DTExec: The package execution returned DTSER_SUCCESS (0).
Started: 3:37:41 PM
Finished: 3:37:43 PM
Elapsed: 2.719 seconds
However the Job is still in th agent and the status is executing. The implications of this are not good. Is this how the sql server agent job task is supposed to work by design.
Thanks,
Larry
Hi All,
I would appreciate any help here at all. I am pulling my hair out!
I am unable to start the snapshot agent or log reader agent from within SQL management studio. it fails on executing step 2 with unknown username or bad password.
I have checked all account details and they are correctly using my domain admin account. I can connect to SQL using teh same account and it also has sysadmin permissions.
If i copy the step 2 paramters and start from the cmd prompt (again logged in using the same domain account) they both start fine.
Any ideas would be gratefully received.
I am testing peer to peer replication in our environment. I simulated a three node peer to peer topology and a local distributor.
For some wierd reason I cannot get the Log Reader Agent and snapshot agent to start. The domain account under which SQL Server Agent runs has administrator previlage on the box. I also use a domain account for SQL Server Service. (none of the passwords changed).
This is the error I am getting - "Executed as user: abc. A required privilege is not held by the client. The step failed"
Any ideas???
Also this domain account is a member of
SQLServer2005MSSQLUsers$ServerName$MSSQLSERVER
SQLServer2005MSAgentUser$ServerName$MSSQLSERVER
We just moved source server to newer, bigger box ... Windows 2003 and Active Directory ... Snapshot agent worked but distribution failed ... Same login as on older machine, login is sysadm, used DCOMCNFG to allow ability to launch process ... What are we missing?
View 4 Replies View RelatedSQLServerAgent could not be started (reason: Unable to connect to server '(local)'; SQLServerAgent cannot start).
Is any has the same problem ?
hi.I have a sql2000 database.I have orders in a table with orderdate and ordertime.I want to force database to delete any order if person didnt pay for it after 3 days.I think I can check that with sqlAgent.but I dont know how.I checked most sites but couldnt find any sources.any help would be appreciated.
View 11 Replies View RelatedI am running SQL 2000 with SP3a and Windows Server 2003 successfully. I have a associate that is attempting to set up the same configuration. Our settings match identically, but his SQL agent will not start. I have checked everything I can think of but it still will not start on his network, yet works perfectly fine on mine. The error he is getting is that the SA account failed to log in properly, yet the password is correct. Any suggestions?
View 3 Replies View Relatedhi iam new to SSIS. I have two packages which are almost identical, only difference is the table name in the query to extract data just a test package.the package is created under my windows account, with the default protection level. I have scheduled them using SQL server agent. one runs succesfully and the other fails with error
Message
Executed as user: NAdbasvc-alticor. ... 9.00.3042.00 for 32-bit Copyright (C) Microsoft Corp 1984-2005. All rights reserved. Started: 11:28:25 AM Error: 2007-07-10 11:28:26.21 Code: 0xC020838F Source: Staging LKPDataReader Source [1] Description: An error occurred executing the provided SQL command. End Error Error: 2007-07-10 11:28:26.21 Code: 0xC004706B Source: Staging DTS.Pipeline Description: "component "LKPDataReader Source" (1)" failed validation and returned validation status "VS_ISBROKEN". End Error Error: 2007-07-10 11:28:26.21 Code: 0xC004700C Source: Staging DTS.Pipeline Description: One or more component failed validation. End Error Error: 2007-07-10 11:28:26.21 Code: 0xC0024107 Source: Staging Description: There were errors during task validation. End Error DTExec: The package execution returned DTSER_FAILURE (1). Started: 11:28:25 AM Finished: 11:28:26 AM Elapsed: 0.906 seconds. The package execution fai... The step failed.
I understand that it is something to do with
1.protection level
2.the user account under which the package is running
3.the SQL agent account used to run job
can someone be a detailed in explaining how to get this running and one big ? is why one package fails while the other with exactly same settings is running perfectly.
Please help i bid a lot of google on this but still confusing
Hi, I am new to SSIS. I createdtwo packages which are almost identical, only difference is the table name in the query to extract data just a test package.the package is created under my windows account, with the default protection level. I have scheduled them using SQL server agent. one runs succesfully and the other fails with error
Message
Executed as user: NAdbasvc-alticor. ... 9.00.3042.00 for 32-bit Copyright (C) Microsoft Corp 1984-2005. All rights reserved. Started: 11:28:25 AM Error: 2007-07-10 11:28:26.21 Code: 0xC020838F Source: Staging LKPDataReader Source [1] Description: An error occurred executing the provided SQL command. End Error Error: 2007-07-10 11:28:26.21 Code: 0xC004706B Source: Staging DTS.Pipeline Description: "component "LKPDataReader Source" (1)" failed validation and returned validation status "VS_ISBROKEN". End Error Error: 2007-07-10 11:28:26.21 Code: 0xC004700C Source: Staging DTS.Pipeline Description: One or more component failed validation. End Error Error: 2007-07-10 11:28:26.21 Code: 0xC0024107 Source: Staging Description: There were errors during task validation. End Error DTExec: The package execution returned DTSER_FAILURE (1). Started: 11:28:25 AM Finished: 11:28:26 AM Elapsed: 0.906 seconds. The package execution fai... The step failed.
I understand that it is something to do with
1.protection level
2.the user account under which the package is running
3.the SQL agent account used to run job
can someone be a detailed in explaining how to get this running and one big ? is why one package fails while the other with exactly same settings is running perfectly.
Please help i bid a lot of google on this but still confusing
I got a foglight alert that sql agent is down on one sql 2000 server
But now sql services are up and running fine.
How do i investigate that why sql agent was down
Please advice?
I have successfully created a DTS package, and would like to use SQL Server Job Agent to schedule it (instead of right-clicking on the package and scheduling a plan because the dates that I need to run this package are not fixed). How would I proceed? I am using SQL Server 2000.
Thanks...