Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Everything stopped working!

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 every
night 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.

AND

2011-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.
Go to Top of Page

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 Shaw
SQL Server MVP
Go to Top of Page

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 Shaw
SQL Server MVP
Go to Top of Page

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!
Go to Top of Page

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 Shaw
SQL Server MVP
Go to Top of Page

colekata
Starting Member

12 Posts

Posted - 2011-03-14 : 17:15:54
Hello Gilamonster,

I just doublechecked after you asked. DB connection is OK.
Go to Top of Page

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 Shaw
SQL Server MVP
Go to Top of Page

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?
Go to Top of Page

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 Shaw
SQL Server MVP
Go to Top of Page

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 Shaw
SQL 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...
Go to Top of Page

X002548
Not Just a Number

15586 Posts

Posted - 2011-03-15 : 14:24:40
I have to say, I didn't read the post, but wanted to say that the Subject line is great

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx


Want to help yourself?

http://msdn.microsoft.com/en-us/library/ms130214.aspx

http://weblogs.sqlteam.com/brettk/

http://brettkaiser.blogspot.com/


Go to Top of Page
   

- Advertisement -