Unanswered: Sql server stops responding during transaction log backups
I am hoping this is an easy one to answer, I have setup a brand new sql server there is virtually nothing on it. The db I am using is about 40MB in size, I have it set to do a full backup once a day and transaction logs every 2hrs. The problem is that it seems that the database is in accessible at the time of the transaction log backups. My users are complaining that the web application they use that accesses the database is hanging at the same time as the transaction log backups. The files are only 30kb in size and take 2 secs to complete. The server is ridiculously overprovisioned for this single little database, what could be the problem?
First, I'm surprised your users even noticed that the database was inaccessible for a mere two seconds. Please explain that.
Second, I'd suspect that whatever backup job or utility you are using is (unnecessarily) placing the database in "single user" mode during the process.
If it's not practically useful, then it's practically useless.