January 19, 2012 at 6:06 am
Hi All
Doing a bit of work for a small company who has a big data warehouse for their size (600GB), the issue they have is that on a restart of the SQL services the main warehouse database goes into recovery and is there for around 6 hours.
I know this is normal process for SQL to do but I have never seen it take this long before even on larger DB's I have worked on.
The service was cleanly shutdown and we issued a checkpoint in each database prior to the shutdown to see if that would help matters.
Can anyone think of any ways to hopefully improve the speed of recovery?
Only thing that I didnt take a look at was the amount of VLF's as the log is approx 100GB and I read that this can potentially cause an issue in recovery.
Thanks
Ant
January 19, 2012 at 6:18 am
At first sight a 100 Gig T-Log for a 600 Gig Data Warehouse sounds a little excesive... how much data is ETLed in a daily basis against that DWH?
More important... what's the backup strategy for this database? How often is T-Log backup?
_____________________________________
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at Amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.January 19, 2012 at 6:20 am
The database is in simple recovery so no T-Log backups, full backup nightly after the data warehouse load.
There is approx 200GB which is transfered in due to data changes as its quite a heavy OLTP environment which they have across a multiple number of systems.
January 19, 2012 at 6:28 am
the no. of vlfs is very possibly the cause for the slow recovery. Reducing the number should help.
If the no is large I would shrink the log right down and then grow it manually in 8000MB chunks to your desired size.
I say 8000MB rather than 8GB as I believe there is a bug with growth factors that are exact multiples of 4GB.
---------------------------------------------------------------------
January 19, 2012 at 6:31 am
I'm willing to bet you have several hundred or thousand VLFs. It's the one thing that absolutely can cause this. Otherwise, massive transaction that hadn't completed at time or shutdown and had to roll back
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
January 19, 2012 at 6:44 am
running dbcc loginfo gave 32768 results, that I noticed that the log was set to grow in 1MB chunks so thats been changed to 8000MB.
whats the easiest way to reduce VLF's is it a shrinkfile then grow the log in 8000MB chunks?
January 19, 2012 at 6:47 am
anthony.green (1/19/2012)
running dbcc loginfo gave 32768 results, that I noticed that the log was set to grow in 1MB chunks so thats been changed to 8000MB.whats the easiest way to reduce VLF's is it a shrinkfile then grow the log in 8000MB chunks?
It sounds like a plan to me 🙂
_____________________________________
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at Amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.January 19, 2012 at 6:52 am
If the no is large I would shrink the log right down and then grow it manually in 8000MB chunks to your desired size.
---------------------------------------------------------------------
January 19, 2012 at 7:08 am
http://www.sqlskills.com/BLOGS/KIMBERLY/post/8-Steps-to-better-Transaction-Log-throughput.aspx
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
January 19, 2012 at 7:12 am
Thanks Gail, I will take a read.
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply