Page 1 of 2 12 LastLast
Results 1 to 15 of 19
  1. #1
    Join Date
    Aug 2002
    Posts
    5

    Unanswered: *_log.ldf became giant...

    excuse me for posting again but this was one of the missing.

    how can i shrink _log.ldf file? it is about 1,39 gb. thanks...

  2. #2
    Join Date
    Aug 2002
    Location
    UK
    Posts
    87
    You can always do a quick bodge .... shut down the database service (not machine!) delete the log and then start the service again.

    If you can also use a maintainence script or stored procedure that will backup the database and then 'flush the log' file.

  3. #3
    Join Date
    Aug 2002
    Posts
    5
    i tried that but it did not create a new log file and the working programs halted. so i had to restore it from backup.
    by the way where will i write the script? never mind, i understand nothing from sql server.

  4. #4
    Join Date
    May 2002
    Location
    Timbaktu
    Posts
    185
    Run

    BACK TRAN DBNAME WITH NOLOG
    AFTER THIS

    USE DBNAME
    DBCC SHRINKFILE(LOGFILENAME,TRUNCATEONLY)

  5. #5
    Join Date
    Aug 2002
    Posts
    5
    in the command prompt?

  6. #6
    Join Date
    Aug 2002
    Location
    UK
    Posts
    87
    Thats a Transact-SQL Reference.

    nice one sqlserver2k .

  7. #7
    Join Date
    May 2002
    Location
    Timbaktu
    Posts
    185
    It would be BACKUP TRAN....

  8. #8
    Join Date
    Aug 2002
    Posts
    5
    yet i do not know what to do. could you please tell me step by step like telling to a dummy, eheh. i am just a student and the guys who are responsible from server are on vacation. thanks..

  9. #9
    Join Date
    May 2002
    Location
    Timbaktu
    Posts
    185
    Go to the Query Analyzer and
    run the query,also make sure that you replace the DBNAME by your database name and Log file name with the name of the Log file of the database.

  10. #10
    Join Date
    Aug 2002
    Posts
    15
    This ONLY works for the TEMPDB log file. Be careful of the advice you give.

    Originally posted by WingMan
    You can always do a quick bodge .... shut down the database service (not machine!) delete the log and then start the service again.

    If you can also use a maintainence script or stored procedure that will backup the database and then 'flush the log' file.

  11. #11
    Join Date
    Jun 2002
    Location
    USA
    Posts
    21
    Hi,

    I replied for this some days back, but I dont know why the info. is missing here???

    Here is the simple and Quick Solution. But the database will not be available for awhile.

    Use detach and attachdb

    1. First detach the database by using

    Example : EXEC sp_detach_db 'pubs', 'true'

    2. Delete the log file (or) To be safe ,Rename the log file to some name

    3. Attach the database without log file.

    EXEC sp_attach_db @dbname = N'pubs',
    @filename1 = N'c:\Program Files\Microsoft SQL Server\MSSQL\Data\pubs.mdf',
    @filename2 = N'c:\Program Files\Microsoft SQL Server\MSSQL\Data\pubs_log.ldf'

    Make sure the paths are correct.

    When you attach without log file, the SQL Server will automatically create a log file with small in size , ithink it will be 1mb.

    Try it . I will appreciate a response.

    Have fun.

    Varad01

  12. #12
    Join Date
    Aug 2002
    Posts
    5
    hi to all.

    i opened the query analyzer and wrote the code:

    BACK TRAN DBNAME WITH NOLOG
    AFTER THIS

    USE DBNAME
    DBCC SHRINKFILE(LOGFILENAME,TRUNCATEONLY)


    instead of dbname i wrote CariH. instead of logfilename, I wrote CariH_log, this is the big file. I got the following message: incorrect syntax near the keyword tran

    friend varad01, in the announcement there is written why some posts are missing. and i did not tried your solution yet will i write this one also in the query analyzer?

    thanks...Regards...
    Last edited by cutcopypaste; 08-21-02 at 08:20.

  13. #13
    Join Date
    Aug 2002
    Posts
    1

    Thumbs up

    Use the Dump tran DataBaseName with no_log ... This would do the trick for the query u asked ...

  14. #14
    Join Date
    Jun 2002
    Location
    USA
    Posts
    21
    Basically "Dump Tran" is a Command used in SQL Server 6.5 which is equivalent to "Backup Tran" in SQL Server 7.0 and 2000.

    Dump Tran/Backup Tran with "NO_LOG" will clear the log file which must be used only at critical times if you dont worry about losing data.

    Dump Tran/Backup Tran will not reduce the size of the log file, unless you have enabled "Auto Shrink" for the database. I can say in 2 steps the same thing, 1. Clear transactions in log files and then 2. Shrink the log file.Thats the concept involved on this.

    Varad01

  15. #15
    Join Date
    Sep 2002
    Location
    Mancos, CO
    Posts
    73

    Re: *_log.ldf became giant...

    Originally posted by cutcopypaste
    excuse me for posting again but this was one of the missing.

    how can i shrink _log.ldf file? it is about 1,39 gb. thanks...
    Assuming that you are using SQL 2000, what may be happening is that the log file will not shrink unless the database is backed up. Microsofts new "feature" that makes you use the SQL backup.

    The best way to keep the log file from ever becoming large is to create a backup device and then set a regularly scheduled backup for your database.

    When we upgraded to SQL 2000 we had a similar problem on a database.

    To setup a backup device:

    Open Enterprise Manager, open Management
    right click on backup and select new backup device
    pick a name, then select Filename and select where you want the file stored then select ok.

    Now that you have a backup device.

    open databases
    right click on your database, all tasks, backup database
    select add...
    Click on backup device, then select your device from the list, select ok

    Select Database complete (or database differential if size is a concern and using this as an additional restore option is unimportant).
    Select Overwrite existing media
    click in the schedule checkbox then open the schedule screen
    select recurring then setup how often you want this to occur. Keep in mind that this will overwrite your existing file, but sounds like this is not your primary backup.

    "ok" out of everything and you should be set.

    Brent

Posting Permissions

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