Results 1 to 11 of 11
  1. #1
    Join Date
    Oct 2004
    Posts
    4

    Unanswered: super green newbie dba - tran log problem... help, please?

    Well, I can't backup up my tran log - too big. I knew enough to know you have to back it up, didn't know enough to know that tran log backup is not included in "Full backup". Contractor installation set Growth to "un-restricted". Me, too swamped to delve deeply in time to learn this is a recipe for disaster, and then it was too late - System down because tran log filled up the disk...

    (Ironically, this happened the morning of the first day of some training they sent me to - Microsoft's Programming a Microsoft SQL 2000 Database...)

    So, some details... The drives on the system :
    ___ c: size 12G, free 7G
    ___ d: size 44.9G, free 10M
    ___ e: size 44.9G, free 13G

    Tran log info :
    ___ filename .... d:\Microsoft SQL Server\MSSQL\data\horizon_Log.LDF
    ___ name ........ horizon_Log
    ___ size ........ 5606392
    ___ maxsize ..... -1
    ___ growth ...... 10
    ___ status ...... 1081410

    ___ filename ...E:\Horizon_Log2\Horizon_Log2_Temp
    ___ name ........ horizon_1_Log
    ___ size ........ 167168
    ___ maxsize ..... 640000
    ___ growth ...... 640
    ___ status ...... 32834


    (there are 2 because the other totally green newbie dba added 1 the day the system went down...)


    I *thought* the addition of the 2nd tran log file meant the 1st file was no longer part of the picture, and that my available free disk space was sufficient to backup the 2nd tran log file.

    Clearly I'm missing something - when I try to back up, I get this in SQL server error log :
    ___ Operating system error 112(There is not enough space on the disk.)


    ___ Does that mean it's trying to backup both the 1st & the 2nd tran logs?
    ___ Or is it only backing up the 2nd log, but trying to use D: (with only 10M)?
    ___ Or, is it using C: or E:, but tran log backups need some multiple of the size of the actual log, and there's not enough space even though there are multiple G?


    I've been reading & googling. So far tried to truncate both files.
    ( dbcc shrinkfile('horizon_1_log',truncateonly) )
    No luck, got these :
    ___ Cannot shrink log file 2 (horizon_Log) because all logical log files are in use.
    ___ Cannot shrink log file 3 (horizon_1_Log) because all logical log files are in use.


    ___ How do I get them to be not "in use"?
    ___ Must the system be down for that? (certainly seems to...)


    Also, checked out the EMPTYFILE option. Books Online says :
    "Migrates all data from the specified file to other files in the same filegroup"

    ___ Migrates to which "other files", exactly?
    ___ Is this the right way to go?
    ___ If so, how would I proceed after achieving a successul EMPTYFILE operation?


    Also...
    ___ In the database's Properties dialog, Transaction Log tab, there's a Delete button. Could that button be used to get me out of this difficulty?


    Last, but not least... If I'm going about this altogether wrongly, can I get a pointer or two?

    TIA for any and all feedback...

  2. #2
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Ok, this is one of those "bad things" that sometimes you just gotta do because the alternatives are worse. We are going to completely jettison the contents of the log into outer space...

    See KB#272318. You've already had step one fail, so you can start at step 2.

    Once you get things under control again (and can make a backup and get it offline), then I'd suggest that you pursue the DBCC SHRINKFILE with EMPTYFILE to remove the extraneous log file.

    -PatP

  3. #3
    Join Date
    Oct 2004
    Posts
    4
    Hi Pat,

    Thanks...

    I'm having trouble with "backup log ..."

    Query Analyzer seems to think "log" is a system function, instead of a key word. (it's color-coding it magenta).

    When I try to run this :
    ---backup LOG horizon with truncate_only
    ---go

    I get :
    ---Server: Msg 170, Level 15, State 1, Line 1
    ---Line 1: Incorrect syntax near 'horizon'.

    If I substitute "database" for "log" QA color codes it as a keyword. (haven't run it that way yet...)

    How do I make it see "log" as a keyword?

  4. #4
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Don't sweat the highlighting, that isn't a problem. My first guess is that you are running in one of the older compatibility modes, (maybe even 6.5 ?) that doesn't recognize the BACKUP keyword. Your syntax as posted is correct for SQL-2000.

    -PatP

  5. #5
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Sorry, utter brain-fart on my part (I'm pretty sorely sleep deprived at the moment)... Assuming that you are running a database in an older compatability mode, you need to use:
    Code:
    DUMP LOG horizion WITH TRUNCATE_ONLY
    -PatP

  6. #6
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    I KNEW it was too FISHY....After reviewing their offer for a 1-year-contact I thougt (rightfully so) that their situation is easy to fix but hard to maintain. Did their DBA leave or got nuked? He/she either left a lot of mess, did it intentianally, or ran out of resources, - it's a given. I really had a bad feeling about it. Is it in Dallas by any chance? And after doing half-blinded evaluation of their environment I realized that $85K was just not worth it...Was I right?
    "The data in a record depends on the Key to the record, the Whole Key, and
    nothing but the Key, so help me Codd."

  7. #7
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    PM me or use ICQ so I can help.
    "The data in a record depends on the Key to the record, the Whole Key, and
    nothing but the Key, so help me Codd."

  8. #8
    Join Date
    Oct 2004
    Posts
    4
    Thanks, Pat.

    Turns out the system is in 6.5 mode. It's supporting a 3rd party app - I called them, they say their app wont work any other way. Yuck...

    Such is life...

    Does anyone know where there's a repository of MS SQL 6.5 docs? Googling and searching microsoft.com hasn't gotten me anywhere as yet...

    L

  9. #9
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    In 6.5 mode and would not work any other way? Have you looked at the database? And who's the vendor? That's a claim that needs to have a solid proof. Yes I've seen 6.5 code that would just not perform even in 7.0, but that does not mean that it can't perform even if you rewrite it (not all of it of course).
    "The data in a record depends on the Key to the record, the Whole Key, and
    nothing but the Key, so help me Codd."

  10. #10
    Join Date
    Oct 2004
    Posts
    4

    Dynix - Horizon Cataloging - 6.5 compatibility mode...

    Vendor is Dynix,
    product is Horizon,
    which they got by buying some other company (maybe AmeriTech, but my memory is foggy, and I have the feeling there was more than one change of hands...).

    They've been good to us, support-wise, we've had this product for 6 yrs or so, the product is an on-line library catalog system, seriously entrenched, and very much suits the needs of the user-base (librarians and borrowers), so there's no way we're going to drop them and move to a different product.

    Anyone out there who's dealt with Horizon, and knows the deep dirt about this situation?

  11. #11
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    I'd start by pestering the fine folks at Microsoft. They should be able to provide you with "back rev" documentation since you already have a license for a later version.

    I've kept the documentation for earlier versions, just in case I'd ever need them, but rarely if ever have to use that documentation.

    -PatP

Posting Permissions

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