Results 1 to 9 of 9
  1. #1
    Join Date
    Dec 2002
    Location
    California
    Posts
    32

    Unanswered: TSQL and Log Files

    I currently have a log file that grows very large and I want to write a script to periodicly dump/truncate the files. Form my research it appears to me that the ony way to do this is with a DBCC SHRINKDATABASE command. The problem comes into play that this is a TSQL command I don't know how to execute a TSQL command. I have poked around in Enterprise Manager, but I cant seem to figure it out. Is this the right command to use, and should I even be in Enterprises Manager to use this command?

  2. #2
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Where's your DBA?

    DUMP The database nightly...

    Dump the transaction logs every 1/2 hours....

    You should be fine...

    How big is the log?
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  3. #3
    Join Date
    Dec 2002
    Location
    California
    Posts
    32
    I'm sorry but I don't know what a DBA is. If its a database administrator then that would be me. How do I dump the database adn DB transaction logs? Sorry if this sounds realy stupid and offends anybody, but I'm new to MS

  4. #4
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    How big is the log?

    Yes DBA is database admin...

    I would buy an entry level book asap...

    http://www.sqlteam.com/store.asp

    Did you install the client side tools?

    Do you know what enterprise manager, Query Analyzer, Books Online are?

    I would install these first....

    You could walk through the Maintenancxe Plan wizard...

    I'll dig up some code, but you should set up a scheduled job...
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  5. #5
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    OK a 2 gig log file....

    Yup, guess you haven't ever dumped the database....

    In enterprise manager, right click on your db and chose properties...

    Click on the Options tab and tell us what the recovery model is set to....
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  6. #6
    Join Date
    Dec 2002
    Location
    California
    Posts
    32
    The recovery mode is set to full

  7. #7
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Here's a sample out of BOL

    Code:
    USE master
    EXEC sp_addumpdevice 'disk', 'MyNwind_2',
       'c:\Program Files\Microsoft SQL Server\MSSQL\BACKUP\MyNwind_2.dat'
    
    --Create the log backup device.
    USE master
    EXEC sp_addumpdevice 'disk', 'MyNwindLog1',
       'c:\Program Files\Microsoft SQL Server\MSSQL\BACKUP\MyNwindLog1.dat'
    
    -- Back up the full MyNwind database.
    BACKUP DATABASE MyNwind TO MyNwind_2
    
    -- Update activity has occurred since the full database backup.
    
    -- Back up the log of the MyNwind database.
    BACKUP LOG MyNwind 
       TO MyNwindLog1
    Then apply the DBBC SHRINKFILE
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  8. #8
    Join Date
    Dec 2002
    Location
    California
    Posts
    32
    Much Thanks

  9. #9
    Join Date
    Feb 2004
    Location
    San Antonio, TX
    Posts
    565
    buddy you need to buy a copy of
    Microsoft Sql Server 2000 : Administrator's Pocket Consultant Immediately

Posting Permissions

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