Results 1 to 6 of 6
  1. #1
    Join Date
    Sep 2002
    Posts
    53

    Question Unanswered: Shrinking Log File

    I have a log file that has gotten out of control. I have read and tried to execute DBCC SHRINKFILE to reduce the size of the file but it does not seem to work. The file is still the same size. Is there another command that I have overlooked? Any assistance would be greatly appreciated!

    Thanks in Advance!
    KDK

  2. #2
    Join Date
    Sep 2002
    Posts
    53

    Re: Shrinking Log File

    Originally posted by KrustyDeKlown
    I have a log file that has gotten out of control. I have read and tried to execute DBCC SHRINKFILE to reduce the size of the file but it does not seem to work. The file is still the same size. Is there another command that I have overlooked? Any assistance would be greatly appreciated!

    Thanks in Advance!

    I found several other posts on this issue, sorry for my haste.
    KDK

  3. #3
    Join Date
    Sep 2002
    Posts
    53

    Angry Back in Trouble

    Well I was trying to resolve the issue like some of the other posts recommended. However, I have quickly found out that there is not enough space left on the disks to backup the log file. What does one do in this case? It is a SQL 2000 server and from my understanding you have to backup the log in order to shrink the file. Any recommendations are welcomed at this point as I am now stumped on how to resolved this problem.

    Thanks in advance!
    KDK

  4. #4
    Join Date
    Feb 2002
    Posts
    2,232
    Do you need the transaction log backup up or is it ok to just blow it away ?

  5. #5
    Join Date
    Sep 2002
    Posts
    53

    Talking Problem Solved

    Ok, for anyone (like myself p) who foolishly lets the size of the transaction log get too big to backup here is the solution.

    USE <DBNAME>
    GO
    BACKUP LOG <DBNAME> WITH TRUNACATE_ONLY
    GO
    DBCC SHRINKFILE (<FILENAME>,<FILE_ID>) TRUNCATEONLY
    GO
    DBCC SQLPERF (LOGSPACE)
    GO

    PS: To get the filename and file_id for a particular database you just run this command:

    USE <DBNAME>
    GO
    SELECT * FROM DBO.SYSFILES
    GO

    Hope no one ever needs this as I think I gave myself braindamage by beating my head against the wall on this one.
    KDK

  6. #6
    Join Date
    Nov 2002
    Posts
    24
    Sorry to you seem to have made a typo in you code so I have inserted the correct form below + example:

    DBCC SHRINKFILE
    ( { file_name | file_id } ....> note the | means OR
    { [ , target_size ]
    | [ , { EMPTYFILE | NOTRUNCATE | TRUNCATEONLY } ]
    }
    )

    USE <your dbname>
    GO
    DBCC SHRINKFILE (<your data file name>, 7)
    GO

    this example might shrink the database to 7MB I say might because on occasion you see a model database that has been altered and the default size has been altered. You should remember you cannot shrink smaller that the model database sizes.
    TECMAN

Posting Permissions

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