Solved

SQL Svr 2005 - Transaction log not shrinking after backup - help

Posted on 2006-06-12
13
302 Views
Last Modified: 2008-03-10
Hello,

I have a transaction log that has grown to over 10gb and is endangering the health of my server.

This log has been backed up via SQL server and Veritas (I did both this morning) and it has not gone down in size.  I tried the shrink option, and it seemed to only get larger (coincidence perhaps).

I am down to 150mb free on this server and need to get this resolved ASAP.

Any help you can provide would be great!

Thanks.
0
Comment
Question by:caw01
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 6
  • 6
13 Comments
 
LVL 143

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 500 total points
ID: 16888419
you have to repeat the backup log + shrink operations (with some transactions in between).
If you want I can explain the technical reasons why the shrink will rarely work upon the first backup.
0
 
LVL 6

Expert Comment

by:davbouchard
ID: 16888451
0
 

Author Comment

by:caw01
ID: 16888642
I just looked at the properties on the DB and it is showing the last log backup date as None.  I must be doing something wrong....  How can I confirm the logs are getting backed up?

When you do a db backup, does it only backup the db and not the logs?

0
SharePoint Admin?

Enable Your Employees To Focus On The Core With Intuitive Onscreen Guidance That is With You At The Moment of Need.

 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 16888676
>When you do a db backup, does it only backup the db and not the logs?
indeed. A full backup does not really backup the transaction log, you have to perform both apart (and even at different times as they cannot run at the same time)
0
 

Author Comment

by:caw01
ID: 16888706
How would I perform a log backup?  Either using SQL or Veritas?  Shouldn't Backup Exec be catching these?

0
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 16888773
>How would I perform a log backup?  Either using SQL or Veritas?  Shouldn't Backup Exec be catching these?
you can do it with veritas, simply using the backup level of incremental/differental (not sure which one of these) instead of full will give you the transaction log backup
0
 

Author Comment

by:caw01
ID: 16888828
Ahh, we do a full every night.  So that is preventing the logs from getting backed up?  So what way is there around this problem?
0
 

Author Comment

by:caw01
ID: 16888841
how would I do a quick and dirty log backup in SQL Management Studio just to empty the logs?  Thats what I need to have done at this point...  I am quickly running out of disk space...
0
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 16888864
>Ahh, we do a full every night.  So that is preventing the logs from getting backed up?  So what way is there around this problem?
simply add another job with a non-full level to run every 4 hours for example. that will do the job.

quick and dirty? well, right-click the database and choose backup from there. in the screens, you can choose what to backup.
0
 

Author Comment

by:caw01
ID: 16889099
Well, I haven't been able to select the logs to be backed up.  I get the choice of database or files, and when I choose files, I don't ge the browse box.  

I did set the recovery model to "simple" and then was able to shrink the DB back down to 500k.  Crisis averted...  But, I'm not sure what the simple vs. full recovery model is.  Time to start reading.

- Chris
0
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 16889122
>I did set the recovery model to "simple"

if you set the recovery model to simple, this is nothing else than telling the transaction log to automatically clear the registered transaction as they are committed. the consequences: you cannot perform transaction log backup on this databases, and hence cannot restore to a point in time.

by setting the recovery model to full (which should be the recommended setting on most production databases), transactions stay in the log until they get cleared by either a transaction log backup or a truncate log statement.
0
 

Author Comment

by:caw01
ID: 16889208
Ok, thanks for the info.  Now that the crisis is over, I'll call Veritas and see if I can figure out why the logs are not getting backed up.  I'm assuming the SQL agent should have this covered and am hoping that I am just doing something wrong.
0
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 16889217
>and am hoping that I am just doing something wrong.
did you try running the backup with non-full level?
that's for quite sure what is happening
0

Featured Post

Get MySQL database support online, now!

At Percona’s web store you can order your MySQL database support needs in minutes. No hassles, no fuss, just pick and click. Pay online with a credit card.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.

617 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question