Re: Multiple databases performance

Tech-Archive recommends: Fix windows errors by optimizing your registry


No, not at commit time, that would be too costly. SQL Server's goal is for the REDO phase to not take longer than 1 minute - that is what controls how often the checkpoint occurs.

--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi


"Jonny Bergdahl" <jonnybergdahl@xxxxxxxxxxxxxxxx> wrote in message news:eGn7tYX9JHA.200@xxxxxxxxxxxxxxxxxxxxxxx
Ok, that is interesting. So the question is when the checkpoint occurs? Is that whenever a transaction has successfully been commited?

Regards;
/jb

"Tibor Karaszi" <tibor_please.no.email_karaszi@xxxxxxxxxxxxxxxxxx> skrev i meddelandet news:eKZiPGX9JHA.4948@xxxxxxxxxxxxxxxxxxxxxxx
>I don't know if I have understood this correctly, but I have >assumed that when a power failure occurs and SQL Server restarts, >it replays the whole log file.

No, it will go to where the most recent checkpoint occurred and then first REDO from there and lastly possibly UNDO open transactions.

In 2005, we have fast recovery, where SQL Server can possibly start before checkpoint, so it during REDO can lock pages which then will be rolled back (so if we have EE can get into the database after REDO), but that is a minor adjustment to above principal.

--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi


"Jonny Bergdahl" <jonnybergdahl@xxxxxxxxxxxxxxxx> wrote in message news:etEQylW9JHA.1380@xxxxxxxxxxxxxxxxxxxxxxx
I don't know if I have understood this correctly, but I have assumed that when a power failure occurs and SQL Server restarts, it replays the whole log file. In that case the recovery time it would depend on how large the log is, which in turn depends on how often the backup job is run.

Are You saying that it only replays the part of the log file that has been committed but not flushed?

Regards;
/jb

"Linchi Shea" <LinchiShea@xxxxxxxxxxxxxxxxxxxxxxxxx> skrev i meddelandet news:99B0A912-D45C-4CF1-85CA-A57BE9695860@xxxxxxxxxxxxxxxx
What startup times would I be looking at after say a power loss? Any other
issues?

It depends on how much rollback and/or roll forward SQL Server has to
perform in each database, and that in turn depends on how much change has not
been committed to the transaction logs and/or not flushed to data files on
power loss. If you have many databases, it would take longer for SQL Server
to get to some databases.

Linchi

"Jonny Bergdahl" wrote:

> Yes, that is a much different animal (Geoff has some very > rigid opinions
> about that). I was just addressing from the SSMS side of > things.

What startup times would I be looking at after say a power loss? Any other
issues?

Regards;
/jb

"Aaron Bertrand [SQL Server MVP]" <ten.xoc@xxxxxxxxxxxxxx> skrev i
meddelandet news:C6669E1F.2E948%ten.xoc@xxxxxxxxxxxxxxxxx
> Yes, that is a much different animal (Geoff has some very > rigid opinions
> about that). I was just addressing from the SSMS side of > things.
>
>
>
> On 6/23/09 2:34 PM, in article
> F9F2ADF8-7265-4CAD-B490-C8A50FF47FCE@xxxxxxxxxxxxx, "Linchi > Shea"
> <LinchiShea@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote:
>
>> Yeah, i tried 2,000 databases, and Management Studio appears >> to handle
>> them
>> fine. I'm not sure if there is any implication on the startup >> process
>> when
>> SQL Server has to go through them to analyze, redo/undo, etc.
>>
>> Linchi
>>
>> "Aaron Bertrand [SQL Server MVP]" wrote:
>>
>>>> I created 300 hundred databases on a test machine, and >>>> Management
>>>> Studio
>>>> appears to be handling them fine. I didn't do a thorough >>>> check though.
>>>
>>> I have a system with over 500 and am accessing pretty much >>> verything -
>>> remotely, no less - via Object Explorer, with no noticeable >>> effects.
>>>
>>>
>






.

Relevant Pages

  • Re: Multiple databases performance
    ... REDO shouldn't take more than one minute. ... And you are right that in vast majority of startups, it takes drastically shorter time since a checkpoint is performed for you when you shutdown SQL Server. ... That would mean that very few of the databases actually would have to replay the log. ... >assumed that when a power failure occurs and SQL Server>restarts, it replays the whole log file. ...
    (microsoft.public.sqlserver.server)
  • isolation levels
    ... I am a developer developing web applications using ASP.Net, PHP and SQL Server. ... I did connect to the sql server and opened query analyser and then selected Northwind db and wrote the batch below ... (I realise that this will commit in milliseconds, that is why I did not commit it-assuming I had a different query inside the transaction that it will take 15 seconds to commit) ...
    (microsoft.public.sqlserver.programming)
  • Re: Multi-Database Transactions
    ... SQL Server has supported the concept of cross-database (or even cross ... "2-phase commit". ... > I've been looking into adding transaction capabilities into my object ... people were talking about data _sources_ instead of databases. ...
    (microsoft.public.dotnet.framework.adonet)
  • Re: Repeatable read. What is it exactly...
    ... SQL Server will hold locks on the data you have read ... can change change the data until you commit or rollback. ... SQL Server locks the data you read. ... > as transaction Isolation levels go. ...
    (microsoft.public.sqlserver.server)
  • Re: transaction with unknown nbr of commands
    ... into a temp table or table variable in the stored procedure code. ... the possibility that some rows would commit and others not. ... >> explicit transaction with a BEGIN TRAN statement, ... Does SQL Server 2000 have some capabilities that I should ...
    (microsoft.public.dotnet.framework.adonet)