Share via

recovering space just eaten by my rollback

db042190 1,516 Reputation points
2020-08-24T15:55:26.927+00:00

hi i'm sure i just consumed a lot of log space by rolling back the results of an in flight voluminous insert.

my c: drive shows i'm down to bare bones.

how can i recover that space before i move on? do i need to recover it, ie will sql blow it away on its own under some circumstance? if i do recover it, what is the safest command to submit?

SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
13,365 questions
Accepted answer
  1. Jeffrey Williams 1,891 Reputation points
    2020-08-24T21:35:53.667+00:00

    Shrinking the log file after an exceptional event is okay - but you don't want to get in the habit of shrinking it as a general practice because it will just grow and suffer from file fragmentation.

    The command you issued is okay - except you did not provide a size so it was shrunk back to the initial size which may not be large enough for normal day to day operation. Since this is on your workstation that is probably okay.

    0 comments

3 additional answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. db042190 1,516 Reputation points
    2020-08-25T17:48:25.283+00:00

    thx criszhan, i believe the rollback was done and committed. and yet my c: drive showed a fraction of the pre crazy query's 200 gig available left. why does this forum only allow one answer to be marked as such? often there are multiple valid answers.

    1 person found this answer helpful.
    0 comments
  2. db042190 1,516 Reputation points
    2020-08-24T16:17:27.883+00:00

    this seems to have recovered a lot. hope i didnt break anything DBCC SHRINKFILE (N'mydbname_log').

    does anybody know how to see just your own post list in this new forum?

    0 comments
  3. Cris Zhan-MSFT 6,616 Reputation points
    2020-08-25T03:26:23.277+00:00

    Hi,

    SQL Server will periodically clean up the log records that are no longer needed in the log file and release the occupied space to make it reusable. When configuring your database with the Simple recovery model, the SQL Server Transaction Log will be marked as inactive and truncated automatically after committing the active transaction.

    But to reduce the physical size of a physical log file, you must shrink the log file.
    https://learn.microsoft.com/en-us/sql/relational-databases/logs/manage-the-size-of-the-transaction-log-file?view=sql-server-ver15

    ===============================================

    If the response helped, do "Accept Answer" and upvote it.

    0 comments