Author |
Topic |
colekata
Starting Member
12 Posts |
Posted - 2011-03-14 : 07:12:24
|
GREETINGS EXPERTS,My office server had a couple of stored procedures associated with 'windows scheduled task' so that everynight it can access an FTP server, dump the flat files into our server and feed data into our DB tables.Everything was fine unless it stopped updating the data since 28th Feb.I ran the scheduled task manually and it shows several rows were affected BUT no new data is there inside the tables.------------------------------------------------------------------------------------------This is what I found suspicious in ERRORLOG on and since 28th Feb (the d-day): 2011-02-28 11:11:13.57 Server SQL Server is terminating because of a system shutdown. This is an informational message only. No user action is required.2011-02-28 11:11:18.43 spid13s Service Broker manager has shut down.2011-02-28 11:11:18.43 spid13s Error: 17054, Severity: 16, State: 1.2011-02-28 11:11:18.43 spid13s The current event was not reported to the Windows Events log. Operating system error = (null). You may need to clear the Windows Events log if it is full.2011-02-28 11:11:18.60 spid7s SQL Trace was stopped due to server shutdown. Trace ID = '1'. This is an informational message only; no user action is required.2011-02-28 11:27:37.79 Server SQL Server is now ready for client connections. This is an informational message; no user action is required. 2011-02-28 11:28:20.54 spid52 Setting database option COMPATIBILITY_LEVEL to 100 for database ReportServer.2011-02-28 11:28:20.63 spid52 Setting database option COMPATIBILITY_LEVEL to 100 for database ReportServerTempDB.2011-02-28 11:28:24.52 spid7s Recovery is complete. This is an informational message only. No user action is required.2011-02-28 12:00:49.04 Logon Error: 17836, Severity: 20, State: 17.AND2011-03-02 00:00:50.33 spid21s This instance of SQL Server has been using a process ID of 1528 since 2/28/2011 11:28:24 AM (local) 2/28/2011 11:28:24 AM (UTC). This is an informational message only; no user action is required.2011-03-02 09:16:23.96 Logon Error: 17836, Severity: 20, State: 17.------------------------------------------------------------------------------------------Please help if you can as lots of data is awaiting and SQL Server is doing nothing! Many many thanks!! |
|
GhantaBro
Posting Yak Master
215 Posts |
Posted - 2011-03-14 : 15:06:23
|
Page your DBA. |
|
|
GilaMonster
Master Smack Fu Yak Hacker
4507 Posts |
Posted - 2011-03-14 : 15:36:25
|
There are no errors in that log that indicate serious problems with the SQL Server. The last few messages indicate that SQL is running.What results does the scheduled task give? Can you run the queries manually in Management Studio?--Gail ShawSQL Server MVP |
|
|
GilaMonster
Master Smack Fu Yak Hacker
4507 Posts |
Posted - 2011-03-14 : 15:36:53
|
quote: Originally posted by GhantaBro Page your DBA.
You know, that's not very helpful...--Gail ShawSQL Server MVP |
|
|
colekata
Starting Member
12 Posts |
Posted - 2011-03-14 : 16:35:37
|
Hello Gilamonster,Thanks for your reply.When I am running the scheduled task manually, it is showing that the rows are affected but actually the records are remainng te same.I can run the stored procedures from SSMS but it's just the same.In short, records stopped updating all of a sudden, even though other things are just fine! |
|
|
GilaMonster
Master Smack Fu Yak Hacker
4507 Posts |
Posted - 2011-03-14 : 16:54:53
|
Are you sure that when you run it manually you're connecting to the right DB?No errors? Is the procedure doing proper error handling? sounds almost like a transaction is getting rolled back. Going to be hard to debug remotely.--Gail ShawSQL Server MVP |
|
|
colekata
Starting Member
12 Posts |
Posted - 2011-03-14 : 17:15:54
|
Hello Gilamonster,I just doublechecked after you asked. DB connection is OK. |
|
|
GilaMonster
Master Smack Fu Yak Hacker
4507 Posts |
Posted - 2011-03-14 : 18:32:58
|
quote: Originally posted by GilaMonster No errors? Is the procedure doing proper error handling? sounds almost like a transaction is getting rolled back. Going to be hard to debug remotely.
--Gail ShawSQL Server MVP |
|
|
colekata
Starting Member
12 Posts |
Posted - 2011-03-15 : 04:29:28
|
Thanks brother, could you please tell me how do I fix it in case the transactions are getting rolled back? |
|
|
GilaMonster
Master Smack Fu Yak Hacker
4507 Posts |
Posted - 2011-03-15 : 05:48:29
|
Without seeing your code, no. You need to check and see if any errors are being thrown. You need to identify why there are errors if they are. You then need, based on that, to identify what is wrong.This is straightforward debugging. Work through the proc a statement at a time and see what's happening, what errors (if any) and at what point things go wrong--Gail ShawSQL Server MVP |
|
|
GhantaBro
Posting Yak Master
215 Posts |
Posted - 2011-03-15 : 12:37:20
|
quote: Originally posted by GilaMonster Without seeing your code, no. You need to check and see if any errors are being thrown. You need to identify why there are errors if they are. You then need, based on that, to identify what is wrong.This is straightforward debugging. Work through the proc a statement at a time and see what's happening, what errors (if any) and at what point things go wrong--Gail ShawSQL Server MVP
If it is just one SP that is having issues then I would suggest to test it in DEV environment... Like Gail said, go through the code and run each step one at a time and make sure your tables are exactly the way it will be in production... u might come across a step where it is failing... also, if the SP doesn't have error handling routines, you said add them.. try-catch in Sql 2008 is very easy to use... |
|
|
X002548
Not Just a Number
15586 Posts |
|
|