Results 1 to 8 of 8
  1. #1
    Join Date
    Jan 2005
    Location
    Atlanta
    Posts
    58

    Question Unanswered: How do I view the amount of the log that is used?

    How do I view the amount of the log that is used?

    How can I tell how often a checkpoint is being executed?

    I have a lot of data to insert into a table (via a SQL insert from another database on the same server), and I do not want to fill the log ... so I will do a SET ROWCOUNT 100000, insert records, wait for the checkpoint to run which will clear the log, ... and repeat this process untill all the records are inserted.

    Thanks

  2. #2
    Join Date
    May 2004
    Location
    Seattle
    Posts
    1,313
    it would probably be better to use BCP or similar for this, rather than insert/select.

    with bcp you can specify the batch size on the cmd line with -b so you don't have to babysit the thing.

  3. #3
    Join Date
    Jan 2005
    Location
    Atlanta
    Posts
    58

    Question

    You are right ... in this case not all of the records from the source table are inserted into the destination table, so I could create a working table with some complex SQL, bcp the working table data out, and then back into the destination table with "-b100000" ... and set the recovery mode to SIMPLE so that a checkpoint would be done at the end of each batch.

    However, for future use I would still like to know ...

    How do I view the amount of the log that is used?

    How can I tell how often a checkpoint is being executed?

    Thanks.

  4. #4
    Join Date
    Jan 2005
    Location
    Atlanta
    Posts
    58

    Question sp_helpdb

    On Sybase the sp_helpdb system stored procecure gives me

    HTML Code:
    log only free kbytes = 5099998
    as part of the output.

    I do not see this output from MS SQL sp_helpdb procedure.

    Any clues on how to ...
    - view the amount of the log that is used?
    - tell how often a checkpoint is being executed?

  5. #5
    Join Date
    Jan 2004
    Location
    In a large office with bad lighting
    Posts
    1,040
    Look up DBCC SQLPERF in BOL

    -- This is all just a Figment of my Imagination --

  6. #6
    Join Date
    May 2004
    Location
    Seattle
    Posts
    1,313
    Quote Originally Posted by tomstone_98
    ...so I could create a working table with some complex SQL, bcp the working table data out...
    no need for a scratch table if you use the queryout option with bcp.

  7. #7
    Join Date
    Jan 2005
    Location
    Atlanta
    Posts
    58
    thank you tomh53, and jezemine ...

  8. #8
    Join Date
    Jan 2004
    Location
    In a large office with bad lighting
    Posts
    1,040
    BTW ... where in ATL (approx) are you located? I'm NE of Atlanta in Gwinnett.

    -- This is all just a Figment of my Imagination --

Posting Permissions

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