Solved

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

Posted on 2006-06-12
13
272 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
 
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
NAS Cloud Backup Strategies

This article explains backup scenarios when using network storage. We review the so-called “3-2-1 strategy” and summarize the methods you can use to send NAS data to the cloud

 

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

Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

Question has a verified solution.

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

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
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.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

867 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

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now