October 25, 2013 at 7:04 am
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..
October 25, 2013 at 7:47 am
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/
October 25, 2013 at 9:12 am
@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..
October 25, 2013 at 2:26 pm
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/
October 25, 2013 at 5:16 pm
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/
October 28, 2013 at 10:11 am
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
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply