Solved

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

Posted on 2006-06-12
13
277 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
  • 6
  • 6
13 Comments
 
LVL 142

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
Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

 
LVL 142

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 142

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 142

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 142

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 142

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

Live: Real-Time Solutions, Start Here

Receive instant 1:1 support from technology experts, using our real-time conversation and whiteboard interface. Your first 5 minutes are always free.

Question has a verified solution.

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

Suggested Solutions

Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
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.

808 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