Application not being able to write to SQL Database

Question

Application not being able to write to SQL Database

danielnunez86

SSC Veteran

Points: 287
More actions
August 16, 2019 at 4:56 pm

#3671913

hello,
I am fairly new to SQL Server. I have an issue where one of our application on PC-A stops writing to the SQL Database which on a SQL Server on PC-B. I have not been able to pin point why. It seems to be randomly and I do not think its a network connection because when there is a SQL disconnect between the PCs, the application shuts down which is not happening, its just not writing new data. I looked at the SQL logs while there are errors, the database ID does not match the DB ID of the affected database. Any idea on where to start troubleshooting?
January 31, 2021 at 11:24 am

This was removed by the editor as SPAM

Viewing 6 posts - 1 through 5 (of 5 total)

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

Chris Harshman SSC-Forever Points: 42192 More actions · Answer 1

If your application doesn't have a log that you can use, I'd start by seeing if there are any sessions actually connected to PC-B from PC-A:

SELECT s.session_id, s.host_name, s.program_name, s.login_name, s.login_time, s.status AS session_status, s.cpu_time, s.total_scheduled_time, s.total_elapsed_time, s.reads, s.writes, s.row_count
  FROM sys.dm_exec_sessions s
  WHERE s.is_user_process = 1
    AND s.host_name = 'PC-A'
  ORDER BY session_id;

Then if you want to monitor activity to see what is executing, you could setup an extended event session or profiler trace that filters on that hostname to see what commands are getting passed from PC-A.

danielnunez86 SSC Veteran Points: 287 More actions · Answer 2

Thanks for the reply. I will research how to do what you just told me (again, I am really new to this) and hopefully this helps me out. I will let you know my findings. Any recommendation on where to learn SQL troubleshooting etc? I bought some courses on Udemy and use W3 schools but don't help much with this issue.

danielnunez86 SSC Veteran Points: 287 More actions · Answer 3

So i checked with their IT team and they verified the connection does exist but it cannot write to the DB for some reason. This server hosts many applications and the issue only seems to happen with our application. Any idea on the best way to figure out why our application cannot write to the DB?

Lowell SSC Guru Points: 323518 More actions · Answer 4

does the application NEVER write to the database and needs to be fixed, or does the applicaiton writing to the database works for a while, and the stops writing?

there are two reason that an application might not write to the database that i would check first.

permissions and blocking due to open transactions.

permissions should be easy to check

is the user for that login, in the database in question, in more than one custom role?

does the login for the app have db_datawriter and EXECUTE permissions on stored procedures?

does it have db_denydatawriter that removes those permissions?

check the effective permissions:

USE AppDatabase;
GO
EXECUTE AS LOGIN = 'mydomain\MyAppUser'
SELECT * FROM [sys].[fn_my_permissions](NULL,'SERVER')
SELECT * FROM [sys].[fn_my_permissions](NULL,'DATABASE')
Revert;

to check if there is blocking, i would use sp_who2 or sp_whoisactive and see if there are any open transactions.

if you report back with some results, we could give some additional pointers.

This reply was modified 5 years, 2 months ago by Lowell.

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!