SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Log File not Recovering


Log File not Recovering

Author
Message
abendigeri
abendigeri
SSC Rookie
SSC Rookie (39 reputation)SSC Rookie (39 reputation)SSC Rookie (39 reputation)SSC Rookie (39 reputation)SSC Rookie (39 reputation)SSC Rookie (39 reputation)SSC Rookie (39 reputation)SSC Rookie (39 reputation)

Group: General Forum Members
Points: 39 Visits: 878
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..
Welsh Corgi
Welsh Corgi
SSChampion
SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)

Group: General Forum Members
Points: 10083 Visits: 4894
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/
abendigeri
abendigeri
SSC Rookie
SSC Rookie (39 reputation)SSC Rookie (39 reputation)SSC Rookie (39 reputation)SSC Rookie (39 reputation)SSC Rookie (39 reputation)SSC Rookie (39 reputation)SSC Rookie (39 reputation)SSC Rookie (39 reputation)

Group: General Forum Members
Points: 39 Visits: 878
@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..
Sreekanth B
Sreekanth B
Mr or Mrs. 500
Mr or Mrs. 500 (598 reputation)Mr or Mrs. 500 (598 reputation)Mr or Mrs. 500 (598 reputation)Mr or Mrs. 500 (598 reputation)Mr or Mrs. 500 (598 reputation)Mr or Mrs. 500 (598 reputation)Mr or Mrs. 500 (598 reputation)Mr or Mrs. 500 (598 reputation)

Group: General Forum Members
Points: 598 Visits: 2056
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/
Welsh Corgi
Welsh Corgi
SSChampion
SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)

Group: General Forum Members
Points: 10083 Visits: 4894
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/
Sqlsavy
Sqlsavy
Old Hand
Old Hand (378 reputation)Old Hand (378 reputation)Old Hand (378 reputation)Old Hand (378 reputation)Old Hand (378 reputation)Old Hand (378 reputation)Old Hand (378 reputation)Old Hand (378 reputation)

Group: General Forum Members
Points: 378 Visits: 862
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
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search