• 0

SQL help needed


Question

Hi All.

First off, apologies if this is not the correct place for this post.

I have been having a problem with one of our databases on our SQL server 2005 edition and the problem seems to come back to the logs (transaction I think). They are getting too big and every now and then disables access to the database. I can get around this by detaching the database then re-attaching it but I want to cure the problem rather than have a work around. How do I set the SQL backup job to clear the logs after the backup has completed? Unfortunately I am not very good with SQL and am learning as I go so would like a step by step guide to do the changes.

Thanks in advance.

J.

Link to comment
Share on other sites

4 answers to this question

Recommended Posts

  • 0

Hi,

You should be able to use the following command to truncate your logfile:

CHECKPOINT

The checkpoint command truncates the unused parts of the log. If this still doesn't help, you could try doing the following:

BACKUP LOG <database> WITH TRUNCATE_ONLY DBCC SHRINKFILE('<logfilename>', 1)

Replacing <database> with your database name, and <logfilename> the the filename of your log file.

Hope that helps.

Link to comment
Share on other sites

  • 0

The question to ask is, does the database require a transaction log?

If it does, do as suggested above, if not, open Microsoft Sql Server Management Studio, right click on the DB that doesnt require the transaction log, open the properties window for the database, goto the options, and change the Recovery Model to "Simple". In short that stops the transaction log ever getting used, and as such never worring about it having filled up.

Rememeber though, if you do this, you can effectively truncate your transaction log files down to a "0" size.

I do this on all my development databases, what with all the deleting/creation of data caused during the development process.

Link to comment
Share on other sites

  • 0

Thanks for the info.

I just looked at the databases option and the Recovery model is set to Simple. This is the actual reply from their support people which will probably explain things clearer,

"Judging from the previous calls it seems pretty certain that it is the log file getting to big. The long term solution therefore, is to prevent the back up from incrementing the log file. This can be achieved by changing the scheduled SQL back up to clear the inactive transactions."

Other than what Jonathans sugests is there any other option that would cause logs to be written that I can turn off? I am reluctant to start using codes as I am not an SQL person.

Thanks again!

Link to comment
Share on other sites

  • 0
The question to ask is, does the database require a transaction log?

If it does, do as suggested above, if not, open Microsoft Sql Server Management Studio, right click on the DB that doesnt require the transaction log, open the properties window for the database, goto the options, and change the Recovery Model to "Simple". In short that stops the transaction log ever getting used, and as such never worring about it having filled up.

Rememeber though, if you do this, you can effectively truncate your transaction log files down to a "0" size.

I do this on all my development databases, what with all the deleting/creation of data caused during the development process.

didn't actually know you could "turn logging off" (for want of a better phrase) in MS SQL

I really wish you could turn it on/off on a table by table basis..

Link to comment
Share on other sites

This topic is now closed to further replies.
  • Recently Browsing   0 members

    • No registered users viewing this page.