Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Log File not Recovering Expand / Collapse
Author
Message
Posted Friday, October 25, 2013 7:04 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Tuesday, May 13, 2014 1:26 PM
Points: 26, Visits: 664
Hi SSC,
I have an issue with a database.
Due to some other issue, the network team has restarted SQL services.
Meanwhile, the log file for this database was full, it filledup whole drive. I have attached another logfile.

When the server was broughtup, i saw the database was in recovery mode.
And after a long time, db was accessible, but the log still says,
"Recovery of database 'MAMart' (5) is 99% complete (approximately 30 seconds remain). Phase 2 of 3. This is an informational message only. No user action is required."

I see no process even after 3 hrs.
Later i did dbcc opentran, but it told there is active transaction and the SPID is 66s !

Transaction information for database 'MAMart'.

Oldest active transaction:
SPID (server process ID): 66s
UID (user ID) : -1
Name : DELETE
LSN : (139245:82471:1)
Start time : Oct 24 2013 2:49:57:477AM
SID : 0x010500000000000515000000db8bf01f99944a3271d22e78dc200000
DBCC execution completed. If DBCC printed error messages, contact your system administrator.

Database is in Simple recovery mode. I tried many times running checkpoint with no luck.
Still not able to shrink log file.


Please share your ideas on debugging..Much Appreciated..
Post #1508446
Posted Friday, October 25, 2013 7:47 AM


SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Today @ 9:19 AM
Points: 4,228, Visits: 4,280
I would be interested in finding out what it takes to resolve this issue.

Your Network Team should not be restarting SQL Server without consulting with the DBA.

You want to make sure that there are no open transactions and that you prevent the Transaction log from consuming all of the disk space. That is a disaster waiting to happen.

Are you performing regular transaction log backups?

I hope that you find a solution.


For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Post #1508464
Posted Friday, October 25, 2013 9:12 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Tuesday, May 13, 2014 1:26 PM
Points: 26, Visits: 664
@Welsh,
Thanks,
I agree, but thats the environment

There are no open transactions...But when i run DBCC opentran its says, one open transaction is there and the SPID is 66s !

I have never seen such SPID...Drive is full now..
Post #1508500
Posted Friday, October 25, 2013 2:26 PM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Today @ 11:27 AM
Points: 284, Visits: 1,144
Probably it's because of the number(and Size of each VLF) of VLF's your Log file have at the point when your Network team force rebooted the box! You can use trace flag 3004(Use @ ur own risk) for investigating slow restores...
See if this helps: http://sqlbuzz.wordpress.com/2011/09/14/diagnosing-database-restores-trace-flag-3004/
Post #1508592
Posted Friday, October 25, 2013 5:16 PM


SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Today @ 9:19 AM
Points: 4,228, Visits: 4,280
abendigeri (10/25/2013)
@Welsh,
Thanks,
I agree, but thats the environment

There are no open transactions...But when i run DBCC opentran its says, one open transaction is there and the SPID is 66s !

I have never seen such SPID...Drive is full now..


If your disk is full you are not going anywhere.


For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Post #1508626
Posted Monday, October 28, 2013 10:11 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Tuesday, September 9, 2014 8:11 AM
Points: 232, Visits: 818
can you not change your recovery model from simple to full and try taking log backups?

Because you can't take log backups when recovery model is simple
Post #1509008
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse