SQL doesnt respond in the morning

Question

SQL doesnt respond in the morning

Dante

Grasshopper

Points: 23
More actions
September 18, 2004 at 11:32 pm

#66918

Hi
We have an Access ADE with a SQL Server 2K backend that has been working on several sites for up to two years. Now, on one site only, each morning when users try to log on, they get a timeout. Access reports a timeout when trying to execute a stored procedure that does the user name/password verification.
If they open the database manualy (shift key down), open a table and modify a piece of data, then all the users can log on and work normally. Can't see any blocks. Haven't a clue where to start with this one. The SQL administrators say nothing has changed. However there have been some changes in the network topology as the main group of users have been relocated some miles away so are on a WAN.
Any help much appreciated.
David

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 1

if nothing has changed on the sqlserver side I'd check with the network guys... especially since it's something that has changed recently. But I can't help you any further than that.

WSquared SSCrazy Points: 2393 More actions · Answer 2

If it is the command that is timing out, then try increasing the command timeout property

Iain Parker SSC-Addicted Points: 409 More actions · Answer 3

Have you tried re-creating the ADP? I would have said it was some sort of connection issue casued by network changes, but the fact that they can connect manually implies that it is something else. Don't know what else to suggest, sorry.

iain

Regards,Iain

Dante Grasshopper Points: 23 More actions · Answer 4

We have tried the Timeout value but it doesn't have any impact.

The ADE is in use on approx 10 desktops, and they were all working OK up to a couple of weeks ago, so I don't suspect it.

I think I'll have to invent some additional error reporting to see if I can trap anything that might be if use to the network guys.

Thanks for your responses - Any more

Peter Tillotson Hall of Fame Points: 3325 More actions · Answer 5

Did you increase both the command and connection timeouts? It sounds likely to be a connection timeout as the user ID verification would be the first thing run (I guess). Also, make sure you don't have 'Auto close' on for the database.

Peter

Alamir SSCertifiable Points: 6085 More actions · Answer 6

here is the solution of the Hang Problem between Access and SQL server 2000 or 7

When you want a report which based on complicated queries joined from several tables, These tables are updated frequently, then you lock these tables (or you will wait for previous transaction lock on these tables to be completed to put your locks on them) .. so all other poeple using these tables are locked too (so hang happened - because they are in the queue of lock of these tables)!!

if you use a query in Access and make a report based on it, and in that query "properties" you choose "No locks" in "Record locks", the query will lock some tables or pages (so although you choose "No locks", it still makes locks!! -the lock type is IS lock- .. because it is a bug in Access with SQL Server)

the best thing is to run the query on the server (by making Pass-Through Query in Access or View on SQL Server) .. and with each table name in the query, add this statement "with (nolock)" .. so you read data as it is (not recently updated = not commited transaction), and you don't wait for locks to be free and you don't lock these tables, and any other transaction will complete successfully (for you and others) .

you will write something like this in your Pass-Through Query in Access:

select Invoices.*, Situation.*

from Situation with (nolock) INNER JOIN Invoices with (nolock)

ON Situation.SituationID = Invoices.Situation

where Situation.SituationID =1

Disadv:

- when using "with (nolock)", you will not seen comitted transaction (changed one) at this moment ... but you can see it at any other time.

Adv:

- no hang at all

- very fast Response

- Little summary for Locks in SQL Log file.

also : you may need to add more Memory (RAM) when server still hangs a little after transfering your queries to path-through queries.... becuase every transaction or query is done on server first, then processed data is passed to your PC.

ALSO:

try to put the DATA and the LOG file of SQL Database on a partition that is Formatted with 32 K or 64 K Cluster Size (not the defalut = 4 K)

because the default Page size in SQL in memory is 8K ,

And If u use the defulat Cluster Size while formating partioin, then to get 1 page (8K), you need 2 hits for the HardDisk (4 K each)

But If u use 32 K Cluster Size while formatiing partioin, then on 1 hit for the HardDisk (32 K each) , you get 4 pages into Memory (more faster ... because the slowest thing that slow the whole system is to read/write from Hard Disk)

I use 32 K not 64 K.. because with 64 K you may have a lot of empty pages in memory ( lost in memeory and your DB may be graw more)

this of course will help you to solve the problem of hang more and more .. bye

Alamir Mohamed
Alamir_mohamed@yahoo.com

Dante Grasshopper Points: 23 More actions · Answer 7

Hi Guys

Thanks for your responses.

Took a long time, but we changed the code to set the timeout and we changed the Conection timeout in the File, Connection dialog. Neither made any difference.

After a few days, we got a report from the SQL Server DBA (IT is outsourced) that the transaction log was as big as the database and was at some sort of limit - I don't yet know if they impose disk space usage limits.

The big log was probably caused when the data was imported into this system a couple of monnths ago.

Anyway, they shrank the log and the problem has disappeared.

Does this make any sense to anyone? So far as we know, no data has been lost or rejected.

Thanks

David