Solved

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

Posted on 2010-11-23
96
1,419 Views
Last Modified: 2012-05-10
Hello everyone,

I've been banging away at this issue and need help. The software vendor gave up. Software Vendor states to keep the DB in SQL 2000 80 compatible mode only  (eyeroll).

The problem: The log file initial size is now 5091 when it should be 1.

I've tried to shrink the log file through the GUI and scripted and then changing the initial size with no luck.

Info:

Server 2003 64bit STD
SQL 2008 STD
DBCC opentran:
Replicated Transaction Information:
        Oldest distributed LSN     : (0:0:0)
        Oldest non-distributed LSN : (966578:361:1)

DBCC LOGINFO: (just a few of the over 9600, most look like below).
FileId      FileSize      StartOffset      FSeqNo      Status      Parity      CreateLSN
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

How can I flush out the stuck transactions so I can get this sucker shrunk?

Thanks guys!!!!  Love Experts-Exchange.com

0
Comment
Question by:N3admin
  • 43
  • 34
  • 19
96 Comments
 
LVL 10

Expert Comment

by:Humpdy
ID: 34203131
Hi,

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

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

Expert Comment

by:EvilPostIt
ID: 34203845
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?
0
 
LVL 10

Expert Comment

by:Humpdy
ID: 34203854
Evil, does the trans. log backup not try to flush out and commit open transactions first ?
0
 
LVL 16

Expert Comment

by:EvilPostIt
ID: 34203878
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.
0
 
LVL 10

Expert Comment

by:Humpdy
ID: 34203945
nice one, thanks Evil :-)
0
 
LVL 16

Expert Comment

by:EvilPostIt
ID: 34203946
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.
0
 
LVL 10

Expert Comment

by:Humpdy
ID: 34204146
so in step 1) evil, when you say transaction commits, these are commits on transactions that are NOT on status 2 ?
0
 
LVL 16

Expert Comment

by:EvilPostIt
ID: 34204241
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)
0
 
LVL 10

Expert Comment

by:Humpdy
ID: 34204254
ah, that's clear,
gentleman, thanks for the explanation.
0
 
LVL 16

Expert Comment

by:EvilPostIt
ID: 34204277
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.
0
 
LVL 10

Expert Comment

by:Humpdy
ID: 34204356
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 ?
0
 
LVL 10

Expert Comment

by:Humpdy
ID: 34204368
if you've got any low level documentation would be great. Most of the stuff I've found is high level overview stuff. Cheers
0
 
LVL 16

Expert Comment

by:EvilPostIt
ID: 34204506
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 :-)
0
 
LVL 2

Author Comment

by:N3admin
ID: 34207086
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??
0
 
LVL 16

Expert Comment

by:EvilPostIt
ID: 34207096
Where are you seeing "transaction status 2"?
0
 
LVL 10

Expert Comment

by:Humpdy
ID: 34207174
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 ?
0
 
LVL 2

Author Comment

by:N3admin
ID: 34207336
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..
0
 
LVL 16

Expert Comment

by:EvilPostIt
ID: 34207339
Sorry, just realised the columns are offset. :(
0
 
LVL 10

Expert Comment

by:Humpdy
ID: 34207363
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.
0
 
LVL 2

Author Comment

by:N3admin
ID: 34207366
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?
0
 
LVL 10

Expert Comment

by:Humpdy
ID: 34207379
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 ?
0
 
LVL 16

Expert Comment

by:EvilPostIt
ID: 34207382
This database must have been restored from a sql 2000 instance which was replicating.
0
 
LVL 2

Author Comment

by:N3admin
ID: 34207400
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?
0
 
LVL 10

Expert Comment

by:Humpdy
ID: 34207411
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 :-)
0
 
LVL 2

Author Comment

by:N3admin
ID: 34207431
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.
0
 
LVL 10

Expert Comment

by:Humpdy
ID: 34207445
what do you see under Replication in SQL Management studio.
Do you see anything under local publication or local subscription ?
0
 
LVL 2

Author Comment

by:N3admin
ID: 34207465
here is a print screen that was previously requested.


jobs.jpg
0
 
LVL 2

Author Comment

by:N3admin
ID: 34207474
Note: disregard anything with the TopsTemp DB, it's just a Temp DB.  The DB I'm having issues with it TopsData
0
 
LVL 2

Author Comment

by:N3admin
ID: 34207480
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.
0
 
LVL 10

Expert Comment

by:Humpdy
ID: 34207495
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 !
0
 
LVL 2

Author Comment

by:N3admin
ID: 34207511
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.
0
 
LVL 10

Expert Comment

by:Humpdy
ID: 34207534
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.
0
 
LVL 2

Author Comment

by:N3admin
ID: 34207601
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.
0
 
LVL 10

Expert Comment

by:Humpdy
ID: 34207629
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
0
 
LVL 2

Author Comment

by:N3admin
ID: 34207634
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?
0
 
LVL 10

Expert Comment

by:Humpdy
ID: 34207672
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
0
 
LVL 2

Author Comment

by:N3admin
ID: 34207722
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'

0
 
LVL 10

Expert Comment

by:Humpdy
ID: 34207982
Can u run
Dbcc ckeckdb('your_database')
0
 
LVL 2

Author Comment

by:N3admin
ID: 34208137
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.
0
 
LVL 2

Author Comment

by:N3admin
ID: 34208554
Ran it, 0 allocation errors and 0 consistency errors to.
0
 
LVL 2

Author Comment

by:N3admin
ID: 34208658
I think a full DB contents copy is what I'm going to have to do unless someone else has an idea?
0
 
LVL 10

Expert Comment

by:Humpdy
ID: 34208691
You could do that or wait till tomorrow morning and we hopefully get an answer
0
 
LVL 2

Author Comment

by:N3admin
ID: 34208705
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!
0
 
LVL 10

Expert Comment

by:Humpdy
ID: 34208730
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
0
 
LVL 10

Expert Comment

by:Humpdy
ID: 34208749
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
0
 
LVL 2

Author Comment

by:N3admin
ID: 34208752
Domain "Administrator" (my login I presume) and login "sa".  The sa status is 'background'
0
 
LVL 2

Author Comment

by:N3admin
ID: 34208786
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                      
0
 
LVL 10

Expert Comment

by:Humpdy
ID: 34208831
K thanks . And sp_who please and will have a further look in the morning
0
Highfive + Dolby Voice = No More Audio Complaints!

Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

 
LVL 2

Author Comment

by:N3admin
ID: 34208874
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
0
 
LVL 10

Expert Comment

by:Humpdy
ID: 34208966
is topsdata the db you're having issues with ?
0
 
LVL 2

Author Comment

by:N3admin
ID: 34208976
correct, TopsData is the problem DB.
0
 
LVL 10

Expert Comment

by:Humpdy
ID: 34209298
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 ?
0
 
LVL 2

Author Comment

by:N3admin
ID: 34209413
Okay, I'll change it to full, then do a transaction log backup, should I choose the truncate option?
0
 
LVL 10

Expert Comment

by:Humpdy
ID: 34210784
Please
0
 
LVL 2

Author Comment

by:N3admin
ID: 34210809
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.



0
 
LVL 10

Expert Comment

by:Humpdy
ID: 34211054
hi,
can you run this,

DBCC SQLPERF(LOGSPACE);

GO

what does it say about topsdata
0
 
LVL 10

Expert Comment

by:Humpdy
ID: 34211063
USE topsdata
DBCC SHRINKFILE(Name of your T-Log here)

0
 
LVL 10

Expert Comment

by:Humpdy
ID: 34211067
I just read that shrink may do the trick after the transaction log was backed up, which you did.
0
 
LVL 16

Expert Comment

by:EvilPostIt
ID: 34211193
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!
0
 
LVL 10

Expert Comment

by:Humpdy
ID: 34211427
do you have any entries in
select * from sys.dm_tran_session_transactions
0
 
LVL 10

Expert Comment

by:Humpdy
ID: 34211437
and if you can also check this dmv

select * from sys.dm_tran_active_transactions
0
 
LVL 16

Assisted Solution

by:EvilPostIt
EvilPostIt earned 250 total points
ID: 34211644
.See if you agree with my train of thought.....

The database thinks it has replication enabled and it therefore no able to remove these transactions from the log.

So....
enable replication
execute EXEC sp_repldone @xactid = NULL, @xact_segno = NULL, @numtrans = 0,     @time = 0, @reset = 1
clear log
disable replication

What do you think Humpdy?

0
 
LVL 10

Expert Comment

by:Humpdy
ID: 34211706
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 ?
0
 
LVL 16

Expert Comment

by:EvilPostIt
ID: 34211711
Has that not already been checked? I mentioned it earlier i thought....
0
 
LVL 10

Expert Comment

by:Humpdy
ID: 34211749
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.
0
 
LVL 16

Expert Comment

by:EvilPostIt
ID: 34211830
Do you mean process id?

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

Author Comment

by:N3admin
ID: 34213459
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
0
 
LVL 2

Author Comment

by:N3admin
ID: 34213481
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!
0
 
LVL 10

Expert Comment

by:Humpdy
ID: 34213483
select * from sys.dm_tran_active_transactions

?
0
 
LVL 2

Author Comment

by:N3admin
ID: 34213487
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
0
 
LVL 2

Author Comment

by:N3admin
ID: 34213497
I made another copy of the Virtual Machine, and booted it up which is why the transaction_begin_time is today.
0
 
LVL 2

Author Comment

by:N3admin
ID: 34213505
FYI

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

Author Comment

by:N3admin
ID: 34213511
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
0
 
LVL 2

Author Comment

by:N3admin
ID: 34213527
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.
0
 
LVL 2

Author Comment

by:N3admin
ID: 34213544
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.
0
 
LVL 10

Expert Comment

by:Humpdy
ID: 34213545
Did u run evils command ? His last one ?
What message u get ?
0
 
LVL 2

Author Comment

by:N3admin
ID: 34213558
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.
0
 
LVL 10

Expert Comment

by:Humpdy
ID: 34213575
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
0
 
LVL 2

Author Comment

by:N3admin
ID: 34213584
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!!!
0
 
LVL 10

Expert Comment

by:Humpdy
ID: 34213609
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.
0
 
LVL 2

Author Comment

by:N3admin
ID: 34213613
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.
0
 
LVL 10

Expert Comment

by:Humpdy
ID: 34213649
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 ?
0
 
LVL 2

Author Comment

by:N3admin
ID: 34213672
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.
0
 
LVL 10

Accepted Solution

by:
Humpdy earned 250 total points
ID: 34215790
so, this is now resolved.

another blank database was setup. Imported one table from the topsdata database into the new database.
Setup replication from the topsdatabase using the one table as an article.
DBCC loginfo showed all transactions changed to status zero
Changed recovery model to full.
Backed up transaction log
DBCC loginfo then showed transactions were no longer there. Was able to redue to the trans log then to 1mb.
thanks to evilpostit  also for his help on this.
0
 
LVL 16

Expert Comment

by:EvilPostIt
ID: 34216002
Quality. Thought that it was stuck replication. Nice one Humpdy.
0
 
LVL 10

Expert Comment

by:Humpdy
ID: 34216012
Two heads better than one evil :-)
0
 
LVL 16

Expert Comment

by:EvilPostIt
ID: 34216021
So did you do it in your local environment in the end?
0
 
LVL 10

Expert Comment

by:Humpdy
ID: 34216036
I RDP'd into his test environment where he had created a database restore from production.
0
 
LVL 16

Expert Comment

by:EvilPostIt
ID: 34216048
So adding and removing replication did the trick then?
0
 
LVL 10

Expert Comment

by:Humpdy
ID: 34216053
it certainly did thx....along with a few backup transaction logs, all went back to normal again.
0
 
LVL 16

Expert Comment

by:EvilPostIt
ID: 34216064
Good to know. Will keep that in mind for the future.
0
 
LVL 10

Expert Comment

by:Humpdy
ID: 34216067
yeah, was an interesting EE thread, never came accross anything like this before, was a good learning curve for me
0
 
LVL 16

Expert Comment

by:EvilPostIt
ID: 34216079
0
 
LVL 10

Expert Comment

by:Humpdy
ID: 34216237
god, that's just plain ugly man !!!!!!!!!!!! ;-)
0
 
LVL 16

Expert Comment

by:EvilPostIt
ID: 34216384
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.
0
 
LVL 10

Expert Comment

by:Humpdy
ID: 34216393
wicked, I'm going to post something now in a few mins, would appreciate your input.
0

Featured Post

Zoho SalesIQ

Hassle-free live chat software re-imagined for business growth. 2 users, always free.

Join & Write a Comment

Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Viewers will learn how the fundamental information of how to create a table.

708 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now