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.
| Author |
Topic |
|
shamking
Starting Member
7 Posts |
 Posted - 2005-09-08 : 05:27:15
|
| SQL server VersionMicrosoft SQL Server 2000 - 8.00.760 (Intel X86) Dec 17 2002 14:22:05 Copyright (c) 1988-2003 Microsoft Corporation Standard Edition on Windows NT 5.0 (Build 2195: Service Pack 4) Our project is a java application which uses SQL server as a backend.One part of the system is a messaging component. We are currently experiencing a problem with our system in that approx every second or third night between the hours of 10pm and 8am a transaction exceptions is logged which cause our messaging component to fail and requires a restart of our application.Usually this happens around the time of our database backup but not always. The database is over 40GB in size and takes 30mins to complete.The reason we are suspicious of the database backup is that for 2 weeks the backups where failing before we noticed and we did not have any problems with our server. Last night we stopped the backups and we did not have a problem whereas the previous two nights we required a restart.I created the database backup using the maintenance plans.Any ideas I would be very grateful.Thanks |
|
|
Thrasymachus
Constraint Violating Yak Guru
483 Posts |
Posted - 2005-09-08 : 11:00:52
|
| I am a little fuzzy on this (I am a little fizzy today). There are 2 things that can not be done during backup operation. Creation and dropping of database files and database shrinking.There is one more thing that could be happening and ths has never happened to me but I remember reading about it. The backup operation can happen while the DB is in use. Be patient if I screw up some of the details I am doing this from memory, but I believe what is backed up starts with the oldest active transaction and the backup completes when this transaction completes or maybe until a set timeout happens. If I am right about this, it is concievable that you may have a seriously long running transaction or an explicit transaction without a commit statement hanging around out there.====================================================Regards,Sean Roussy"pimpin ain't easy, but someone has to do it" -- Pimpin Whitefolks(?) |
 |
|
|
shamking
Starting Member
7 Posts |
Posted - 2005-09-09 : 07:01:59
|
| Thanks Sean for your replyWe are not creating, dropping or shrinking any database files duringdatabase backup other than creation to temp tables.Your second point is very interesting. Of hand I cant think of any long running transaction so my question now is, what tools or methods can I use to help me discovery if I have long running transaction?Thanks again. |
|
|
|
Thrasymachus
Constraint Violating Yak Guru
483 Posts |
Posted - 2005-09-09 : 08:10:54
|
| Profiler. It can be found at in your Microsoft SQL Server Progams folder. There is predeined templates and a helpful one would probably be the SQLProfilerTSQL_SPs.tdf. The output can take a little learning to decipher, but the Books Online documentation is pretty good in this regard. From the COntents Tab go to Administering SQL Server-->Monitoring Server Performance and Activity--Monitoring with SQL Profiler. I usually setup my traces to run from my local or development SQL Server monitoring a prodiction box and I write the results to a SQL table for easy querying.You may also want to take a gander at sp_who and sp_lock and the current activity in the EM at the time just before the backup.====================================================Regards,Sean Roussy"pimpin ain't easy, but someone has to do it" -- Pimpin Whitefolks(?) |
|
|
|
|
|
|
Database Backup - (Transaction timeout)