| Author |
 Topic  |
|
|
SwePeso
Patron Saint of Lost Yaks
Sweden
29138 Posts |
 Posted - 12/08/2009 : 15:53:24
|
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
35007 Posts |
|
|
tkizer
Almighty SQL Goddess
USA
35007 Posts |
Posted - 12/08/2009 : 16:11:52
|
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." |
 |
|
|
SwePeso
Patron Saint of Lost Yaks
Sweden
29138 Posts |
Posted - 12/08/2009 : 18:20:45
|
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" |
|
|
|
tkizer
Almighty SQL Goddess
USA
35007 Posts |
Posted - 12/08/2009 : 18:26:53
|
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." |
|
|
|
SwePeso
Patron Saint of Lost Yaks
Sweden
29138 Posts |
|
|
SwePeso
Patron Saint of Lost Yaks
Sweden
29138 Posts |
Posted - 12/09/2009 : 05:33:32
|
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 hereAnd 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 |
|
|
|
tkizer
Almighty SQL Goddess
USA
35007 Posts |
|
|
SwePeso
Patron Saint of Lost Yaks
Sweden
29138 Posts |
Posted - 12/10/2009 : 13:22:23
|
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" |
|
|
|
SwePeso
Patron Saint of Lost Yaks
Sweden
29138 Posts |
Posted - 12/10/2009 : 14:32:20
|
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" |
|
|
| |
Topic |
|
What is the cause?
