Page 1 of 3 123 LastLast
Results 1 to 15 of 36
  1. #1
    Join Date
    Oct 2003
    Posts
    268

    Unanswered: Recovery of database - 23 Days?!?

    I cancelled an INSERT operation on a database. Now I get:

    "Recovery of database 'BounceRepository' (6) is 0% complete (approximately 2024654 more seconds)"

    That is another 23 days of straight recover time!!! The original INSERT only ran for about 30 hours.

    In Windows Performance Monitor, I can see that the system isn't disk or CPU limited. What is the server doing? How can I make it go faster?

  2. #2
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Ah, don't get too wired up about the estimated recovery time. That is one of the most ill-tuned features of SQL-2000. I'd guess you have something like 6-8 hours for recovery, depending on what else the box might be doing.

    You can make the box go faster right now by carrying it to the nearest window. They accellerate at something like 9.8 m/sec once they go out the window, so it doesn't take very long before they are going pretty fast!

    To cut down on recovery time in the future, you want to set the recovery interval down a bit.

    -PatP

  3. #3
    Join Date
    Oct 2003
    Posts
    268
    I wish I believed you:

    At 10:30 AM, it estimated 0% complete and 2039505 seconds left
    At 5:57 PM, it estimated 1% complete and 2011108 seconds left.

    So far, that's pretty accurate.

    This system isn't being used by anything else at all. It's completely off-line until it is recovered. I don't understand why it isn't disk bound or CPU bound. What could it be waiting for?

    Secondly, from the MSDN link you posted:
    "recovery interval does not affect the time it takes to undo long-running transactions."

  4. #4
    Join Date
    Oct 2003
    Posts
    706

    Unhappy Time to get out the small nukes, dude

    Unfortunately, database recovery is one of those "black box" processes which are undocumented. And sometimes what actually seems to be happening is that the data structures are hosed and the recovery engine is simply running around in a loop.

    Usually these problems occur in a database that is very highly indexed, and the computer is blindly trying to update each and every index with each and every insert. With millions of records that could easily take ... well ... a month.

    What you really need to achieve here is to be able to get back to your data, and to destroy all of the indexes. Bludgeon your way back to a point, somehow someway, where you just have the data and no indexes.
    ChimneySweep(R): fast, automatic
    table repair at a click of the
    mouse! http://www.sundialservices.com

  5. #5
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Actually, it's 9.8 meters/second^2

    Physics scales well even past the Enterprise application level and clear up to galactic, where performance starts to suffer.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  6. #6
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Quote Originally Posted by blindman
    Actually, it's 9.8 meters/second^2

    Physics scales well even past the Enterprise application level and clear up to galactic, where performance starts to suffer.
    Hey! Somebody et my
    (a superscript 2). I'm bummed.

    -PatP

  7. #7
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    [THUD (Falling off barsto...office chair)]
    [QUOTE=RogerWilco]The original INSERT only ran for about 30 hours.
    [QUOTE]

    You're joking....

    [/THUD (Falling off barsto...office chair)]
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  8. #8
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    He's not joking. But while Roger has posted several questions on this forum regarding how slow his queries are running, I don't recall him answering questions about how his tables are indexed. Really, that's the first thing to look at. Insufficient indexing or over-indexed tables can severly impact performance.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  9. #9
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    F that

    Anything taking 30 hours is ridiculous....

    Go with minimally logged operations...select * INTO

    bcp

    BULK INSERT

    Samll batches with commits...

    anything....
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  10. #10
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Quote Originally Posted by blindman
    He's not joking. But while Roger has posted several questions on this forum regarding how slow his queries are running, I don't recall him answering questions about how his tables are indexed. Really, that's the first thing to look at. Insufficient indexing or over-indexed tables can severly impact performance.
    At this point I really don't think that Roger wants help, it seems that he just wanted a place to vent.

    -PatP

  11. #11
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Well, he has plenty of time do that!

    Sorry Roger. I do feel for you, and best of luck getting everything back up and running.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  12. #12
    Join Date
    Oct 2003
    Posts
    268
    Thank you Pat, blindman, and Brett! The three of you have been very helpful in answering several of my queries. Believe me, I seriously want help!!! Although, I do sometimes cross into venting. You can understand my frustration; 23 day+ recovery times and 50 hour+ long (and running) UPDATE commands with no way to get progress or safely abort!??!

    I'm have a very extensive programming background and I know lots about data structures and databases but I readily admit that my database expertise is inadequate for the tasks that I am faced with.

    Any of you three interested in moonlighting doing hourly contract work? Over phone/Internet is fine but would any of you by chance live in Texas? Email me rate information and the times and number of hours you'd be able to work at wilco909-at-hotmail.com.

    fyi, the recovery process is still running and so far the estimates are scarily accurate:
    Estimated at 4% complete and 1946255 more seconds.

  13. #13
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    When Roger says that he "cancelled" the insert, I take it he simply killed the SQL Server service, because I don't see any reference to something that would indicate a "kill" being issued against a spid. This will yield a VERY long recovery process. To avoid it, you can detach the database, rename the original log file (I assume it's in hundreds of gigs by now), and then use CREATE DATABASE ... FOR ATTACH.

    Let us know how it went.
    "The data in a record depends on the Key to the record, the Whole Key, and
    nothing but the Key, so help me Codd."

  14. #14
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    Where in Texas?
    "The data in a record depends on the Key to the record, the Whole Key, and
    nothing but the Key, so help me Codd."

  15. #15
    Join Date
    Oct 2003
    Posts
    268
    Quote Originally Posted by blindman
    I don't recall him answering questions about how his tables are indexed. Really, that's the first thing to look at. Insufficient indexing or over-indexed tables can severly impact performance.
    I do appreciate the responses and hate to not get back to you on things like that.

    I did a BULK INSERT from a flat file into a basic non-indexed table. And then did a INSERT INTO SELECT into the following table. I intended to cancel and rerun as a DTS package and now the cancel is expected to take weeks!!!

    Code:
    	CREATE TABLE Removables
    	(
    		EmailUser			VARCHAR(50) NOT NULL,
    		EmailDomain			VARCHAR(50) NOT NULL,
    		BounceCategoryID		INTEGER NOT NULL,
    		EntryDate			DATETIME DEFAULT getdate() NOT NULL,
    
    		CONSTRAINT PK_Removables PRIMARY KEY CLUSTERED (EmailUser, EmailDomain)
    	)
    	ALTER TABLE Removables WITH CHECK ADD CONSTRAINT FK_Removables_BounceCategoryID FOREIGN KEY (BounceCategoryID) REFERENCES BounceCategories

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •