SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 Data Corruption Issues
 What is the cause?
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

SwePeso
Patron Saint of Lost Yaks

Sweden
30277 Posts

Posted - 12/08/2009 :  15:53:24  Show Profile  Visit SwePeso's Homepage  Reply with Quote
The last few times I had to restart SQL Server service, one of my databases (50GB) takes unusual long time to start, due to "In Recovery". Other databases (30GB) is available immediately.

So this time I issued

1) DBCC CHECKDB
2) CHECKPOINT
3) BACKUP DATABASE (full) (db 5,000,000 pages, log 12 pages)
4) SHUTDOWN

After restarting SQL Server service, the database is still not available after 60 minutes. What can cause this behaviour?
What can be done to resolve the long recovery time?



N 56°04'39.26"
E 12°55'05.63"

tkizer
Almighty SQL Goddess

USA
37165 Posts

Posted - 12/08/2009 :  16:09:45  Show Profile  Visit tkizer's Homepage  Reply with Quote
Large transactions can cause this. We had a largish database take several hours to recover as the ALTER INDEX job was running when we did an emergency restart. What a bad idea that was!

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

"Let's begin with the premise that everything you've done up until this point is wrong."
Go to Top of Page

tkizer
Almighty SQL Goddess

USA
37165 Posts

Posted - 12/08/2009 :  16:11:52  Show Profile  Visit tkizer's Homepage  Reply with Quote
Another thing that can cause this if you have many log growths. I forget what they are called, but to workaround it you make sure that all log growths are in 8GB chunks unless you've got small databases. We had a system with thousands of log growths and when it went into recovery mode at startup, it took 2-3 days to complete. Microsoft gave the recommendation to us about the 8GB chunks as a result.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

"Let's begin with the premise that everything you've done up until this point is wrong."
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
30277 Posts

Posted - 12/08/2009 :  18:20:45  Show Profile  Visit SwePeso's Homepage  Reply with Quote
You mean VLF's?
The growth is 4 GB, and nothing (I really mean nothing) was running on the database server and the database in particular while the 4 steps were made.
The other databases have replication running every minute, my database has not yet anything of that kind.




N 56°04'39.26"
E 12°55'05.63"
Go to Top of Page

tkizer
Almighty SQL Goddess

USA
37165 Posts

Posted - 12/08/2009 :  18:26:53  Show Profile  Visit tkizer's Homepage  Reply with Quote
Yes VLFs! We had a system with 80,000 VLFs that took 2-3 days to recover. It's not for a system that I support, but I heard about it from another DBA. I specify 8GB for my log growths as a result of that Microsoft case.


I don't have any other ideas as to why you are encountering the issue. I'd suggest opening a case with Microsoft for assistance.


Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

"Let's begin with the premise that everything you've done up until this point is wrong."
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
30277 Posts

Posted - 12/09/2009 :  04:01:20  Show Profile  Visit SwePeso's Homepage  Reply with Quote
http://www.sqlskills.com/BLOGS/KIMBERLY/post/Transaction-Log-VLFs-too-many-or-too-few.aspx

"DBCC LOGINFO" says I have 33,636 VLF's. Waaaay to much! Log file is 24 GB.



N 56°04'39.26"
E 12°55'05.63"
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
30277 Posts

Posted - 12/09/2009 :  05:33:32  Show Profile  Visit SwePeso's Homepage  Reply with Quote
Update:

My database is in simple recovery mode, so I issued these commands about 20 times
--> Script starts here
CHECKPOINT
GO

DBCC SHRINKFILE(logYoda, TRUNCATEONLY) 
GO

ALTER DATABASE Yoda
MODIFY FILE 
( 
      NAME = logYoda
    , SIZE = 8192
) 
GO

DBCC LOGINFO
GO
--< Script end here
And now my log file is 8GB with 19 VLF's only.

Here is a handy script for checking number of VLF's per database.
EXEC master.dbo.sp_msforeachdb 'USE "?"; SELECT ''?'' AS [Database];DBCC loginfo'


N 56°04'39.26"
E 12°55'05.63"

Edited by - SwePeso on 12/09/2009 05:53:28
Go to Top of Page

tkizer
Almighty SQL Goddess

USA
37165 Posts

Posted - 12/09/2009 :  11:09:59  Show Profile  Visit tkizer's Homepage  Reply with Quote
Thanks Peter.

Did it help with recovery time at startup?

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

"Let's begin with the premise that everything you've done up until this point is wrong."
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
30277 Posts

Posted - 12/10/2009 :  13:22:23  Show Profile  Visit SwePeso's Homepage  Reply with Quote
We'll see tonight. The hosting company is doing a major overhaul tonight (started 20 minutes ago).
They send me a SMS when done so I can see. Before today, it has always taken about 60-90 minutes for the recovery stuff.
I'll post back when I know more.



N 56°04'39.26"
E 12°55'05.63"
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
30277 Posts

Posted - 12/10/2009 :  14:32:20  Show Profile  Visit SwePeso's Homepage  Reply with Quote
Alrighty then!
Server is restarted and the database previously known as "VLF 33636" was up and running in less than a minute!



N 56°04'39.26"
E 12°55'05.63"
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.12 seconds. Powered By: Snitz Forums 2000