What is the cause? - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

Data Corruption Issues

What is the cause?

Author

Topic

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-12-08 : 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

38200 Posts

Posted - 2009-12-08 : 16:09:45

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."

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2009-12-08 : 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

30421 Posts

Posted - 2009-12-08 : 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

38200 Posts

Posted - 2009-12-08 : 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

30421 Posts

Posted - 2009-12-09 : 04:01:20

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"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-12-09 : 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 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"

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2009-12-09 : 11:09:59

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."

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-12-10 : 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

30421 Posts

Posted - 2009-12-10 : 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"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources