SQL 2008 database has over 9600 stuck transactions in status 2, can't shrink the Log File.

Hi,

Can you backup the transaction log, instead of a shrink.

backup type in backup properties, set it to transaciton log instead of full

I dont think this will work as any transactions which are still open and not committed will not be caught by a transaction log backup.

Are you using replication?

Evil, does the trans. log backup not try to flush out and commit open transactions first ?

It will only flush transactions that have actually committed after the checkpoint process or log writter process. Otherwise it would not be able to maintain atomicity.

If a transaction gets stuck (which i have seen in the past, doesnt happen very often though). As the log is syncronous it cannot clear down anything after this. But i think this problem is to do with replication which hasnt been able to send a transaction and therefore is not being removed.

nice one, thanks Evil :-)

Sorry just re-read one of my statements. The checkpoint process allows the committed transaction to be removed. So it would go (1) transaction commits, (2) checkpoint done, (3) then the transaction is ready to be rmove not the other way round.

so in step 1) evil, when you say transaction commits, these are commits on transactions that are NOT on status 2 ?

It dependant where the user is getting this information from....

According to the decription for the dm_tran_active_transactions DMV, transaction state 2 is "Active transaction" which means it has not committed. Although Transaction Status is also included in this DMV it is unsupported so probably best leave that one alone :)

http://msdn.microsoft.com/en-us/library/ms174302.aspx

Every single transaction has 1 of 2 outcomes. Either commit or rollback. If a rollback happens it is dropped from the transaction log and data is left unchanged in the database. If the transaction commits then this remains in the log file until something happens to remove it (t-log backup, change of recovery mode to simple)

ah, that's clear,
gentleman, thanks for the explanation.

No probs.

Another thing regarding transactions and transaction log backups.

A backup will need to take a snapshot of a database. If it were to take a snapshot of an open transaction, when restored how will it know if that transaction completed successfully? Therefore it cannot restore that transaction and what would have been the point of backing it up to begin with.

I promise it all makes sense at some point..... ;-)

If you want some more information let me know and i will share a few links on it.

But it can restore that transaction if you've taken transaction log backups also of your db and restore those on top of the full back up. It won't just drop that transaction ?

if you've got any low level documentation would be great. Most of the stuff I've found is high level overview stuff. Cheers

Here is an article on checkpointing.... Looks like i was wrong regarding wether the transaction log backs up active transactions.... but it just rolls them back upon restore as it runs through the transactions. But this still means that any data in a transaction log that is part of an active transaction will not be cleared down. Have a look at the long running transactions section for this.

http://msdn.microsoft.com/en-us/library/ms189573.aspx

Will post some more soon but have a meeting :-)

Added info:  This DB is a stand alone setup, no replication going on.

So no ideas on how to get these transactions out from status 2??

Where are you seeing "transaction status 2"?

evil, I think it's the dbcc loginfo showing that

N3admin,

can you run this

SELECT * FROM sys.databases;

what does the column  'log_reuse_wait_desc' say for your database ?

EvilPostIt.  See the first post and you'll see that I have over 9600 transactions in status 2.

Humpdy, thanks for helping..

SELECT * FROM sys.databases:
The Column 'log_reuse_wait_desc' says "REPLICATION"

So what does this mean??
I'm not that good at SQL, I'm a Network Engineer so I need lay-mens terms..

Sorry, just realised the columns are offset. :(

ok, that's a good sign N3Admin,

EvilPostit was correct with his comment
**But i think this problem is to do with replication which hasnt been able to send a transaction and therefore is not being removed. **

this basically means your replication is behind.

Yeah... happed with the copy paste.  I should have fixed it, but I was running on 18hrs of work and no sleep at the time.

So I've checked another DB that runs the same software and I ran the "SELECT * FROM sys.databases"

The 'log_reuse_wait_desc' for it is "nothing".   So I'm guessing that this is the issue?  How do I change the one with "Replication" to be "nothing"?  or is it something else?

yeah, it's a good sign cause now we know what it is.

Can you go under Jobs, which is under SQL Server Agent.
Can you do a print screen of it ?

This database must have been restored from a sql 2000 instance which was replicating.

It is a DB that was originally on SQL 2000, in a stand alone server setting...  So this doesn't have anything to do with the stuck transactions huh?

I'm afraid I gotta run for a train now,
but I'm sure evil will be able to fix it quite quickly for you.

ain't that right evil :-)

The only other thing I see as different between the DB with issues and one that doesn't is the 'log_reuse_wait' on the DB with issues has a value of 6 whereas the DB without issues is set at 0.

NOTE: The DB with issues is on SQL 2008 and the DB without issues is 2005.  Same DB though.

what do you see under Replication in SQL Management studio.
Do you see anything under local publication or local subscription ?

here is a print screen that was previously requested.


jobs.jpg

Note: disregard anything with the TopsTemp DB, it's just a Temp DB.  The DB I'm having issues with it TopsData

what do you see under Replication in SQL Management studio.
Do you see anything under local publication or local subscription ?

Both the Publications and Subscriptions are empty.

please run this

EXEC sp_repldone @xactid = NULL, @xact_segno = NULL, @numtrans = 0,     @time = 0, @reset = 1

if it runs successfully,
then backup the transaction log. I'm here for 20 more minutes buddy !

Sorry if this sounds dumb, but is there a way to copy the entire contents of one DB over to a fresh empty one leaving the log behind??

I think I did that about 5yrs ago on SQL 2000 when there was a stuck log file.

BTW, these DB's are always in SIMPLE  RECOVERY mode.

Yes, you can create a blank database,
import your data, stored procedures, etc,
drop the current database, and then rename your current database.
You can do this easily by right clicking on the new database, clicking tasks, choosing import data, then choose your source and destination, it's pretty self explanatory from there.


before that though, I would try the command I gave you, change the full recovery mode to FULL and do a transaction log backup.

I already tried doing that and it didn't change any of the transactions... I've read that sometimes it takes two backups of the transaction log... I'll try it, but it's going to be slow.

Did it give you a success message after you executed
EXEC sp_repldone @xactid = NULL, @xact_segno = NULL, @numtrans = 0, @time = 0, @reset = 1

one transaction log backup should do the trick once the above ran ok

I try to run the command 'EXEC sp_repldone @xactid = NULL, @xact_segno = NULL, @numtrans = 0,     @time = 0, @reset = 1'

it says "Unable to execute procedure. The database is not published"

do I need to change it from simple to full before this is run?

no, that won't make any difference..

can you run these, from within your database

select * from MSrepl_transactions

and

select * from MSreplication_queue

do you get any results from either of these, remember, make sure you're querying in your database, not the master database or any other one

Yeah, making sure to run it against the correct DB.

select * from MSrepl_transactions = Invalid Object name 'MSrepl_transactions'

select * from MSreplication_queue = Invalid object name 'MSreplications_queue'

Can u run
Dbcc ckeckdb('your_database')

Running, it's going to take some time, it's a 20G database and I've copied the Virtual Machine over to a different SAN that's typically only used for backups and is slower than the Live SAN.

Ran it, 0 allocation errors and 0 consistency errors to.

I think a full DB contents copy is what I'm going to have to do unless someone else has an idea?

You could do that or wait till tomorrow morning and we hopefully get an answer

I'll wait, I think the proper thing to do is get the DB cleaned up.

I don't turn to Experts Exchange often, but when I do it's a life saver!

can you do me a favour please,
what is the output when you run this, can you see rows relating to your database.

sp_who

also, how many entries do you have when you run

select * from sys.sysprocesses

anything suspicious there...what I'm thinking is from the sp_who or sysprocesses, there may be some related SPIDS lingering which we can kill

Domain "Administrator" (my login I presume) and login "sa".  The sa status is 'background'

when I run select * from sys.sysprocesses I get a total of 30 entries:

Sorry the paste doesn't line up properly most likely.

spid      kpid      blocked      waittype      waittime      lastwaittype      waitresource      dbid      uid      cpu      physical_io      memusage      login_time      last_batch      ecid      open_tran      status
1      1712      0      0x0000      0      PREEMPTIVE_XE_CALLBACKEXECUTE                                                                                                                                                                                                                                                                               0      1      0      0      0      2010-11-24 10:05:56.550      2010-11-24 10:05:56.550      0      0      background                    
2      1732      0      0x0158      22639      XE_TIMER_EVENT                                                                                                                                                                                                                                                                                              0      1      0      0      0      2010-11-24 10:05:56.550      2010-11-24 10:05:56.550      0      0      background                    
3      1736      0      0x015A      8994315      XE_DISPATCHER_WAIT                                                                                                                                                                                                                                                                                          0      1      0      0      0      2010-11-24 10:05:56.550      2010-11-24 10:05:56.550      0      0      background                    
4      1776      0      0x0060      687      LAZYWRITER_SLEEP                                                                                                                                                                                                                                                                                            0      1      31      0      0      2010-11-24 10:05:56.550      2010-11-24 10:05:56.550      0      0      background                    
5      1780      0      0x007F      426940      LOGMGR_QUEUE                                                                                                                                                                                                                                                                                                0      1      15      0      0      2010-11-24 10:05:56.550      2010-11-24 10:05:56.550      0      0      background                    
6      1904      0      0x0080      412      REQUEST_FOR_DEADLOCK_SEARCH                                                                                                                                                                                                                                                                                 0      1      0      0      0      2010-11-24 10:05:56.550      2010-11-24 10:05:56.550      0      0      background                    
7      1908      0      0x009D      8900266      KSOURCE_WAKEUP                                                                                                                                                                                                                                                                                              1      1      0      0      0      2010-11-24 10:05:56.550      2010-11-24 10:05:56.550      0      0      background                    
8      0      0      0x0000      0      MISCELLANEOUS                                                                                                                                                                                                                                                                                               1      1      0      0      0      2010-11-24 10:05:56.550      2010-11-24 10:05:56.550      0      0      sleeping                      
9      1976      0      0x00A2      1563      SQLTRACE_BUFFER_FLUSH                                                                                                                                                                                                                                                                                       1      1      0      0      0      2010-11-24 10:05:56.550      2010-11-24 10:05:56.550      0      0      background                    
10      2184      0      0x015E      85      BROKER_TO_FLUSH                                                                                                                                                                                                                                                                                             1      1      110      0      0      2010-11-24 10:05:56.550      2010-11-24 10:05:56.550      0      0      background                    
11      1960      0      0x007E      8991846      ONDEMAND_TASK_QUEUE                                                                                                                                                                                                                                                                                         1      1      0      0      0      2010-11-24 10:05:56.550      2010-11-24 10:05:56.550      0      0      background                    
12      1956      0      0x0081      426975      CHECKPOINT_QUEUE                                                                                                                                                                                                                                                                                            6      1      15      0      0      2010-11-24 10:05:56.550      2010-11-24 10:05:56.550      0      0      background                    
13      2268      0      0x00AE      8987655      BROKER_EVENTHANDLER                                                                                                                                                                                                                                                                                         1      1      0      41      0      2010-11-24 10:05:56.550      2010-11-24 10:05:56.550      0      0      background                    
14      2172      0      0x00A9      8987909      BROKER_TRANSMITTER                                                                                                                                                                                                                                                                                          1      1      0      0      0      2010-11-24 10:05:56.550      2010-11-24 10:05:56.550      0      0      background                    
15      0      0      0x0000      0      MISCELLANEOUS                                                                                                                                                                                                                                                                                               1      1      0      0      0      2010-11-24 10:05:56.550      2010-11-24 10:05:56.550      0      0      sleeping                      
16      1968      0      0x00A9      8987908      BROKER_TRANSMITTER                                                                                                                                                                                                                                                                                          1      1      0      0      0      2010-11-24 10:05:56.550      2010-11-24 10:05:56.550      0      0      background                    
17      0      0      0x0000      0      MISCELLANEOUS                                                                                                                                                                                                                                                                                               1      1      0      2      0      2010-11-24 10:05:56.550      2010-11-24 10:05:56.550      0      0      sleeping                      
18      0      0      0x0000      0      MISCELLANEOUS                                                                                                                                                                                                                                                                                               1      1      0      4      0      2010-11-24 10:05:56.550      2010-11-24 10:05:56.550      0      0      sleeping                      
19      0      0      0x0000      0      MISCELLANEOUS                                                                                                                                                                                                                                                                                               1      1      0      0      0      2010-11-24 10:05:56.550      2010-11-24 10:05:56.550      0      0      sleeping                      
20      0      0      0x0000      0      MISCELLANEOUS                                                                                                                                                                                                                                                                                               1      1      0      2      0      2010-11-24 10:05:56.550      2010-11-24 10:05:56.550      0      0      sleeping                      
21      0      0      0x0000      0      MISCELLANEOUS                                                                                                                                                                                                                                                                                               1      1      0      0      0      2010-11-24 10:05:56.550      2010-11-24 10:05:56.550      0      0      sleeping                      
22      0      0      0x0000      0      MISCELLANEOUS                                                                                                                                                                                                                                                                                               1      1      0      0      0      2010-11-24 10:05:56.550      2010-11-24 10:05:56.550      0      0      sleeping                      
23      0      0      0x0000      0      MISCELLANEOUS                                                                                                                                                                                                                                                                                               1      1      0      0      0      2010-11-24 10:05:56.550      2010-11-24 10:05:56.550      0      0      sleeping                      
24      0      0      0x0000      0      MISCELLANEOUS                                                                                                                                                                                                                                                                                               1      1      0      0      0      2010-11-24 10:05:56.550      2010-11-24 10:05:56.550      0      0      sleeping                      
51      0      0      0x0000      0      MISCELLANEOUS                                                                                                                                                                                                                                                                                               4      1      125      90      2      2010-11-24 10:05:59.363      2010-11-24 10:06:01.410      0      0      sleeping                      
52      0      0      0x0000      0      MISCELLANEOUS                                                                                                                                                                                                                                                                                               1      1      63      18      3      2010-11-24 10:08:20.567      2010-11-24 10:08:28.253      0      0      sleeping                      
53      0      0      0x0000      0      MISCELLANEOUS                                                                                                                                                                                                                                                                                               6      1      15      4      2      2010-11-24 10:08:33.423      2010-11-24 10:08:42.423      0      0      sleeping                      
54      0      0      0x0000      0      MISCELLANEOUS                                                                                                                                                                                                                                                                                               6      1      31      3      2      2010-11-24 10:10:38.380      2010-11-24 10:10:42.737      0      0      sleeping                      
55      0      0      0x0000      0      MISCELLANEOUS                                                                                                                                                                                                                                                                                               6      1      16      6      2      2010-11-24 12:30:48.550      2010-11-24 12:30:53.550      0      0      sleeping                      
56      3916      0      0x0000      0      PREEMPTIVE_OS_WAITFORSINGLEOBJEC                                                                                                                                                                                                                                                                            6      1      0      4      2      2010-11-24 12:33:29.910      2010-11-24 12:33:30.207      0      0      runnable                      

K thanks . And sp_who please and will have a further look in the morning

thanks!!!!

sp_who: again, sorry for the formatting not perfect on the paste.
spid      ecid      status      loginame      hostname      blk      dbname      cmd      request_id
1      0      background                          sa                                                                                                                                            0          NULL      RESOURCE MONITOR      0
2      0      background                          sa                                                                                                                                            0          NULL      XE TIMER              0
3      0      background                          sa                                                                                                                                            0          NULL      XE DISPATCHER         0
4      0      background                          sa                                                                                                                                            0          NULL      LAZY WRITER           0
5      0      background                          sa                                                                                                                                            0          NULL      LOG WRITER            0
6      0      background                          sa                                                                                                                                            0          NULL      LOCK MONITOR          0
7      0      background                          sa                                                                                                                                            0          master      SIGNAL HANDLER        0
8      0      sleeping                            sa                                                                                                                                            0          master      TASK MANAGER          0
9      0      background                          sa                                                                                                                                            0          master      TRACE QUEUE TASK      0
10      0      background                          sa                                                                                                                                            0          master      BRKR TASK             0
11      0      background                          sa                                                                                                                                            0          master      TASK MANAGER          0
12      0      background                          sa                                                                                                                                            0          TopsData      CHECKPOINT            0
13      0      background                          sa                                                                                                                                            0          master      BRKR EVENT HNDLR      0
14      0      background                          sa                                                                                                                                            0          master      BRKR TASK             0
15      0      sleeping                            sa                                                                                                                                            0          master      TASK MANAGER          0
16      0      background                          sa                                                                                                                                            0          master      BRKR TASK             0
17      0      sleeping                            sa                                                                                                                                            0          master      TASK MANAGER          0
18      0      sleeping                            sa                                                                                                                                            0          master      TASK MANAGER          0
19      0      sleeping                            sa                                                                                                                                            0          master      TASK MANAGER          0
20      0      sleeping                            sa                                                                                                                                            0          master      TASK MANAGER          0
21      0      sleeping                            sa                                                                                                                                            0          master      TASK MANAGER          0
22      0      sleeping                            sa                                                                                                                                            0          master      TASK MANAGER          0
23      0      sleeping                            sa                                                                                                                                            0          master      TASK MANAGER          0
24      0      sleeping                            sa                                                                                                                                            0          master      TASK MANAGER          0
51      0      sleeping                            NT AUTHORITY\SYSTEM      REYESDS1                                                                                                                              0          msdb      AWAITING COMMAND      0
52      0      sleeping                            REYESHC\Administrator      REYESDS1                                                                                                                              0          master      AWAITING COMMAND      0
53      0      sleeping                            REYESHC\Administrator      REYESDS1                                                                                                                              0          TopsData      AWAITING COMMAND      0
54      0      sleeping                            REYESHC\Administrator      REYESDS1                                                                                                                              0          TopsData      AWAITING COMMAND      0
55      0      runnable                            REYESHC\Administrator      REYESDS1                                                                                                                              0          TopsData      SELECT                0

is topsdata the db you're having issues with ?

correct, TopsData is the problem DB.

ok, time to change the recovery model to Full, and then do a transaction log backup. We need to see what happens to the log after this. that ok to do for you ?

Okay, I'll change it to full, then do a transaction log backup, should I choose the truncate option?

Please

Had to run a full backup after putting the DB in Full recovery, then ran a transaction log backup with no change, still can't change the initial log file size.

hi,
can you run this,

DBCC SQLPERF(LOGSPACE);

GO

what does it say about topsdata

USE topsdata
DBCC SHRINKFILE(Name of your T-Log here)

I just read that shrink may do the trick after the transaction log was backed up, which you did.

Could you do another DBCC OPENTRAN.

If the transactions are still here then the issue is still unsolved.

Sorry about dropping off the conversation last night. Moving house and living without internet is great fun!

do you have any entries in
select * from sys.dm_tran_session_transactions

and if you can also check this dmv

select * from sys.dm_tran_active_transactions

defo worth a shot Evil

I would be curious to see the results from
select * from sys.dm_tran_active_transactions

Those transactions should show up there ?

Has that not already been checked? I mentioned it earlier i thought....

not sure if he tried it,

i know he checked the dbcc loginfo which shows the status of 2.

But I'm thinking, if those transactions show in sys.dm_tran_active_transactions of status 2
we'll have the transaction id then.

Do you mean process id?

From the output of what he did from sys.sysprocesses there are no processes with open transactions.

Somehow the initial log space has changed (as if someone put the DB in FULL).  I need to return it to 1mb.  As you can see below that's not what it's set at now.


DBCC SQLPERF(LOGSPACE);

GO
=
Database Name      Log Size (MB)      Log Space Used (%)      Status
master                     0.7421875             56.31579      0
tempdb                     0.4921875             60.81349      0
model                     0.4921875             51.5873      0
msdb                     3.054688                      22.12276      0
TopsFDB                      0.9921875      29.18307      0
TopsData                      5416.742      98.77191      0

Ran:
USE topsdata
DBCC SHRINKFILE(Name of your T-Log here)

No change..

DBCC OPENTRAN
=
Transaction information for database 'TopsData'.

Replicated Transaction Information:
        Oldest distributed LSN     : (0:0:0)
        Oldest non-distributed LSN : (966578:361:1)

Stuff still stuck in there!

select * from sys.dm_tran_active_transactions

?

select * from sys.dm_tran_active_transactions    =


transaction_id      name      transaction_begin_time      transaction_type      transaction_uow      transaction_state      transaction_status      transaction_status2      dtc_state      dtc_status      dtc_isolation_level      filestream_transaction_id
171      worktable      2010-11-25 09:13:33.513      2      NULL      2      0      0      0      0      0      NULL
174      worktable      2010-11-25 09:13:33.513      2      NULL      2      0      0      0      0      0      NULL
176      worktable      2010-11-25 09:13:33.513      2      NULL      2      0      0      0      0      0      NULL
178      worktable      2010-11-25 09:13:33.513      2      NULL      2      0      0      0      0      0      NULL
181      worktable      2010-11-25 09:13:33.513      2      NULL      2      0      0      0      0      0      NULL
183      worktable      2010-11-25 09:13:33.513      2      NULL      2      0      0      0      0      0      NULL
34221      SELECT      2010-11-25 09:26:39.653      2      NULL      2      0      0      0      0      0      NULL

I made another copy of the Virtual Machine, and booted it up which is why the transaction_begin_time is today.

FYI

DBCC LOGINFO still showing all those open tranactions in Status 2...

Here is the first 76 lines of DBCC LOGINFO

FileId  FileSize      tartOffset      FSeqNo Status      Parity CreateLSN
2      253952      8192      966608      2      128      0
2      253952      262144      966578      2      64      0
2      253952      516096      966579      2      128      0
2      278528      770048      966607      2      128      0
2      524288      1048576      966580      2      64      966579000000025600109
2      524288      1572864      966581      2      64      966579000000025600109
2      524288      2097152      966582      2      64      966579000000025600109
2      524288      2621440      966583      2      64      966579000000025600109
2      524288      3145728      966584      2      64      966581000000049100200
2      524288      3670016      966585      2      64      966581000000049100200
2      524288      4194304      966586      2      64      966581000000049100200
2      524288      4718592      966587      2      64      966581000000049100200
2      524288      5242880      966588      2      64      966583000000012900183
2      524288      5767168      966589      2      64      966583000000012900183
2      524288      6291456      966590      2      64      966583000000012900183
2      524288      6815744      966591      2      64      966583000000012900183
2      524288      7340032      966592      2      64      966584000000082900200
2      524288      7864320      966593      2      64      966584000000082900200
2      524288      8388608      966594      2      64      966584000000082900200
2      524288      8912896      966595      2      64      966584000000082900200
2      524288      9437184      966596      2      64      966586000000049000237
2      524288      9961472      966597      2      64      966586000000049000237
2      524288      10485760      966598      2      64      966586000000049000237
2      524288      11010048      966599      2      64      966586000000049000237
2      524288      11534336      966600      2      64      966588000000013500216
2      524288      12058624      966601      2      64      966588000000013500216
2      524288      12582912      966602      2      64      966588000000013500216
2      524288      13107200      966603      2      64      966588000000013500216
2      524288      13631488      966604      2      64      966589000000083900189
2      524288      14155776      966605      2      64      966589000000083900189
2      524288      14680064      966606      2      64      966589000000083900189
2      524288      15204352      966609      2      64      966608000000004100003
2      524288      15728640      966610      2      64      966609000000049100023
2      524288      16252928      966611      2      64      966609000000049100023
2      524288      16777216      966612      2      64      966609000000049100023
2      524288      17301504      966613      2      64      966609000000049100023
2      524288      17825792      966614      2      64      966613000000066200004
2      524288      18350080      966615      2      64      966613000000066200004
2      524288      18874368      966616      2      64      966613000000066200004
2      524288      19398656      966617      2      64      966613000000066200004
2      524288      19922944      966618      2      64      966617000000041200003
2      524288      20447232      966619      2      64      966617000000041200003
2      524288      20971520      966620      2      64      966617000000041200003
2      524288      21495808      966621      2      64      966617000000041200003
2      524288      22020096      966622      2      64      966621000000055700050
2      524288      22544384      966623      2      64      966621000000055700050
2      524288      23068672      966624      2      64      966621000000055700050
2      524288      23592960      966625      2      64      966621000000055700050
2      524288      24117248      966626      2      64      966625000000066200004
2      524288      24641536      966627      2      64      966625000000066200004
2      524288      25165824      966628      2      64      966625000000066200004
2      524288      25690112      966629      2      64      966625000000066200004
2      524288      26214400      966630      2      64      966629000000056900002
2      524288      26738688      966631      2      64      966629000000056900002
2      524288      27262976      966632      2      64      966629000000056900002
2      524288      27787264      966633      2      64      966629000000056900002
2      524288      28311552      966634      2      64      966633000000046800003
2      524288      28835840      966635      2      64      966633000000046800003
2      524288      29360128      966636      2      64      966633000000046800003
2      524288      29884416      966637      2      64      966633000000046800003
2      524288      30408704      966638      2      64      966637000000039500018
2      524288      30932992      966639      2      64      966637000000039500018
2      524288      31457280      966640      2      64      966637000000039500018
2      524288      31981568      966641      2      64      966637000000039500018
2      524288      32505856      966642      2      64      966641000000058300003
2      524288      33030144      966643      2      64      966641000000058300003
2      524288      33554432      966644      2      64      966641000000058300003
2      524288      34078720      966645      2      64      966641000000058300003
2      524288      34603008      966646      2      64      966645000000066200004
2      524288      35127296      966647      2      64      966645000000066200004
2      524288      35651584      966648      2      64      966645000000066200004
2      524288      36175872      966649      2      64      966645000000066200004
2      524288      36700160      966650      2      64      966649000000063300017
2      524288      37224448      966651      2      64      966649000000063300017
2      524288      37748736      966652      2      64      966649000000063300017
2      524288      38273024      966653      2      64      966649000000063300017

Hey Evil, I think I read something about what you're talking about, enable replication, possibly having to setup another DB to do so, then tagging the logs as "backed up", then disabling replication and returning the DB to SIMPLE.

Unfortunately I can't find the location of the info right now, I remember they didn't have the specific step by step instructions and I don't know how to do that.

I do remember there was a script to make the log file think it was fully backed up so the log could be shrunk though.

It's interesting the first for transactions done have a CreateLSN number... Although I have no idea what that means.  EVERY SINGLE ONE of the 9600+ have a number in the CreateLSN row.

Did u run evils command ? His last one ?
What message u get ?

EXEC sp_repldone @xactid = NULL, @xact_segno = NULL, @numtrans = 0,     @time = 0, @reset = 1
says

Unable to execute procedure. The database is not published. Execute the procedure in a database that is published for replication.

Can u setup replication . Just do it temporarily and to say replicate just one table in the tops database . U can do that in the gui . We basically want to mimic a replication scenario and then revert back to evils command

Yeah, but I don't know how, I've never done that.

I'm going to need a little hand holding to get it done.

I got a paypal gift for the person that gets me through this, oh and consolation paypal gift for the second person. I can't thank you guys enough!!!

No need for that . I'm just curious for the resolution so don't take a short cut on us :-)

I'm back at a machine in two hours though.

Okay, then how about a case of beer? aaahahaha

No problem, it's ThanksGiving day here so I'll be on and off throughout the day myself.

Actually in the mean time . Can take a backup of the db and t log . U can get rid of any sensitive data ? I would like to restore it on my environment ?

unfortunately I can't, it's healthcare data.

But I can give you a login to the copy of the server!

I won't post any login info here though.   You can send me a email to orders@n3sys.com and I'll email you the login info in two separate emails so you can get on.

Remember, the Server is much slower than normal because I have it running off a backup SAN that only has 5400rpm drives, but you don't have to worry about screwing anything up because it's a COPY of the real deal.

Quality. Thought that it was stuck replication. Nice one Humpdy.

Two heads better than one evil :-)

So did you do it in your local environment in the end?

I RDP'd into his test environment where he had created a database restore from production.

So adding and removing replication did the trick then?

it certainly did thx....along with a few backup transaction logs, all went back to normal again.

Good to know. Will keep that in mind for the future.

yeah, was an interesting EE thread, never came accross anything like this before, was a good learning curve for me

Try this one on for size?

https://www.experts-exchange.com/questions/26604961/Torn-Page-Recovery.html

god, that's just plain ugly man !!!!!!!!!!!! ;-)

If you want to know more about SQL Server internals this is the site to go to.

http://www.sqlskills.com

Have a look at any of the blogs under resources.

wicked, I'm going to post something now in a few mins, would appreciate your input.