Solved

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

Posted on 2006-06-12
13
295 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
Guide to Performance: Optimization & Monitoring

Nowadays, monitoring is a mixture of tools, systems, and codes—making it a very complex process. And with this complexity, comes variables for failure. Get DZone’s new Guide to Performance to learn how to proactively find these variables and solve them before a disruption occurs.

 
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

Online Training Solution

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action. Forget about retraining and skyrocket knowledge retention rates.

Question has a verified solution.

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

Suggested Solutions

International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.

752 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