|
database
newsgroups
|
|||||||||||||||||||||||
|
|||||||||||||||||||||||
|
Having a little issue... is it possible to have multiple connections all get rolled back to the same point? I have a small database that seems to have been busy - but magically lost 5 hours of data (several 100,000 records...) This is across multiple databases. In one of them I do have a large identity gap - in the others the 'identity' type columns are self rolled incrementers. Tables that are not used by the connection pool seem to be fine and populated normally. In a rollback I would normally expect to see _some_ data missing - but not _everything_ from 20 odd connections all binned for 5 hours! The only real clue I have is that one monitoring process that counts the rows in a table (usually 200 - 400) running from SQLAgent took 5 hours to complete! Other than that NOTHING in any server log, nothing in the sql logs. SqlServer 2k, win2003 Please - any serious suggestions - including any useful monitoring ideas for future reference happily received! > Having a little issue... is it possible to have multiple connections Tricky... I don't think that any of below applies to you, but just in case:> all get rolled back to the same point? 1. Bound transactions. See sp_getbindtoken and sp_bindsession. It allow several connections share the same transaction space. 2. Some type of restore. Should impact the whole database, though. -- Show quoteTibor Karaszi, SQL Server MVP http://www.karaszi.com/sqlserver/default.asp http://www.solidqualitylearning.com/ <Wangk***@yahoo.com> wrote in message news:1115986816.917025.137880@g44g2000cwa.googlegroups.com... > Hi > > > Having a little issue... is it possible to have multiple connections > all get rolled back to the same point? I have a small database that > seems to have been busy - but magically lost 5 hours of data (several > 100,000 records...) > > This is across multiple databases. In one of them I do have a large > identity gap - in the others the 'identity' type columns are self > rolled incrementers. Tables that are not used by the connection pool > seem to be fine and populated normally. > > In a rollback I would normally expect to see _some_ data missing - but > not _everything_ from 20 odd connections all binned for 5 hours! > > The only real clue I have is that one monitoring process that counts > the rows in a table (usually 200 - 400) running from SQLAgent took 5 > hours to complete! Other than that NOTHING in any server log, nothing > in the sql logs. > > SqlServer 2k, win2003 > > Please - any serious suggestions - including any useful monitoring > ideas for future reference happily received! > Any idea of any type of restore this could be? I have never come
across a partial restore - certainly not for a single file database. I had wondered if there was something like this. > The only real clue I have is that one monitoring process that counts This looks to me like transactions are not being committed as expected since > the rows in a table (usually 200 - 400) running from SQLAgent took 5 > hours to complete! Other than that NOTHING in any server log, nothing > in the sql logs. your monitoring process is apparently being blocked. You can run DBCC OPENTRAN to identify the oldest uncommitted transaction. Use Profiler to identify transactions that are started but not committed. Are you doing your own connection pooling? In this case, connections should never be returned to the pool with an open transaction. You can execute a COMMIT or ROLLBACK these before returning to the pool: IF @@TRANCOUNT > 0 ROLLBACK or WHILE @@TRANCOUNT > 0 COMMIT -- Show quoteHope this helps. Dan Guzman SQL Server MVP <Wangk***@yahoo.com> wrote in message news:1115986816.917025.137880@g44g2000cwa.googlegroups.com... > Hi > > > Having a little issue... is it possible to have multiple connections > all get rolled back to the same point? I have a small database that > seems to have been busy - but magically lost 5 hours of data (several > 100,000 records...) > > This is across multiple databases. In one of them I do have a large > identity gap - in the others the 'identity' type columns are self > rolled incrementers. Tables that are not used by the connection pool > seem to be fine and populated normally. > > In a rollback I would normally expect to see _some_ data missing - but > not _everything_ from 20 odd connections all binned for 5 hours! > > The only real clue I have is that one monitoring process that counts > the rows in a table (usually 200 - 400) running from SQLAgent took 5 > hours to complete! Other than that NOTHING in any server log, nothing > in the sql logs. > > SqlServer 2k, win2003 > > Please - any serious suggestions - including any useful monitoring > ideas for future reference happily received! > Thanks
Connection pooling is jsql and jboss. First time we have seen this issue. Hopefully the last - but as I have no clear idea what went wrong... What is the recover model setting for the database?
Also, do your server logs mention provide any clues as to what happened leading up to the event? <Wangk***@yahoo.com> wrote in message Show quote news:1115986816.917025.137880@g44g2000cwa.googlegroups.com... > Hi > > > Having a little issue... is it possible to have multiple connections > all get rolled back to the same point? I have a small database that > seems to have been busy - but magically lost 5 hours of data (several > 100,000 records...) > > This is across multiple databases. In one of them I do have a large > identity gap - in the others the 'identity' type columns are self > rolled incrementers. Tables that are not used by the connection pool > seem to be fine and populated normally. > > In a rollback I would normally expect to see _some_ data missing - but > not _everything_ from 20 odd connections all binned for 5 hours! > > The only real clue I have is that one monitoring process that counts > the rows in a table (usually 200 - 400) running from SQLAgent took 5 > hours to complete! Other than that NOTHING in any server log, nothing > in the sql logs. > > SqlServer 2k, win2003 > > Please - any serious suggestions - including any useful monitoring > ideas for future reference happily received! > Full on the main db, simple on the one with an identity gap.
Transaction logs are backed up every 10 minutes. The backups worked fine during this period, and were of expected size. Last one was 7 times larger than expected. Nothing in server logs, nothing in sql logs. Still trying to get some sense out of the TLog backups. Perhaps you should have full recovery on a database that receives complex
transactions. I use simple recovery myself, but only on a reporting database that is bulk loaded. <Wangk***@yahoo.com> wrote in message Show quote news:1115993174.029499.121330@g47g2000cwa.googlegroups.com... > Full on the main db, simple on the one with an identity gap. > Transaction logs are backed up every 10 minutes. The backups worked > fine during this period, and were of expected size. Last one was 7 > times larger than expected. > Nothing in server logs, nothing in sql logs. Still trying to get some > sense out of the TLog backups. > The transactional database is full, the simple one is some simple
tracking data - eg how many sessions active, how many connections, etc - stuff I really couldnt care less about frankly. However the rollback occurs through all databases referenced by the connection pool. Sorry, a little extra info I should have added:
I have been told that the support guys were having some issues prior to this being noticed. There was a deadlock that had to be explicitly killed. It certainly seems like you had an open transaction for a long time which then were rolled back.
-- Show quoteTibor Karaszi, SQL Server MVP http://www.karaszi.com/sqlserver/default.asp http://www.solidqualitylearning.com/ <Wangk***@yahoo.com> wrote in message news:1115994303.239565.153190@g14g2000cwa.googlegroups.com... > Sorry, a little extra info I should have added: > > I have been told that the support guys were having some issues prior to > this being noticed. There was a deadlock that had to be explicitly > killed. > Yup. The wierd bit is that it had 10-20 connections at the time - ALL
of them rolled back.  |
|||||||||||||||||||||||
Other interesting topics