• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 381
  • Last Modified:

How do I manage my transaction log? My data is only 300 megs, my log is 48 GIGS!!! Help!

I have a database that, each night, I pull files from my mainframe and write them to SQL.  I first delete the existing file, then pull down the new data from the AS400.  I dont know anything about transaction logs, do I HAVE to have em?  Can I turn it off on this database?  How do I shrink it?  I obviously dont need records of all my old table iterations!!

Suggestions are welcome please!
0
JUSTICE
Asked:
JUSTICE
  • 3
  • 2
1 Solution
 
Scott PletcherSenior DBACommented:
Try shrinking the log by running these commands from QA:

BACKUP LOG yourDbName
WITH TRUNCATE_ONLY

DBCC SHRINKFILE (logical_file_name)
-- you can find the logical file name using EXEC sp_helpfile with current db = yourDbName
0
 
Scott PletcherSenior DBACommented:
If still not shrunk, use the BACKUP LOG ... TRUNCATE_ONLY one more time.
0
 
JUSTICEAuthor Commented:
Perfect!!  :)  Thank you!!!
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
BillAn1Commented:
if you don't want/need transaction logs, you can set the recovery model to simple
(from EM right-click the database, and then click Properties.Click the Options tab. click on Simple)
This will keep the transaction log from growing. However, it means that if you have a database failure, you can only recover to your last backup. Also, the transactionlog may still grow to a certain size, as it needs to keep details of a transaction until the transaction is complete. e.g. if you are loading a large amount of data in one transaction, it may still get large temporarily.
0
 
JUSTICEAuthor Commented:
Thats perfect.  All of this data is completely transitory, as I pull it from my AS400 each night, simply to make sure nobody has web access to our mainframe. :)
0
 
Scott PletcherSenior DBACommented:
Btw, I suggest setting the log size to a reasonable starting size yourself -- say 50M -- and changing the growth to a fixed amount, say 25-50M, rather than 10%.  10% seems to cause a much bigger performance hit when expanding than a fixed amount.

You can make these changes in EM by typing over the value in the Database "Properties" tab or from QA using ALTER DATABASE commands.

If you need additonal info, just let me know :-)

Thanks for the points!!
0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now