Solved

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

Posted on 2006-06-12
13
260 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
Comment Utility
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
Comment Utility
0
 

Author Comment

by:caw01
Comment Utility
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]
Comment Utility
>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
Comment Utility
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]
Comment Utility
>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
Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

 

Author Comment

by:caw01
Comment Utility
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
Comment Utility
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]
Comment Utility
>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
Comment Utility
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]
Comment Utility
>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
Comment Utility
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]
Comment Utility
>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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

Everyone has problem when going to load data into Data warehouse (EDW). They all need to confirm that data quality is good but they don't no how to proceed. Microsoft has provided new task within SSIS 2008 called "Data Profiler Task". It solve th…
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
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 UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

762 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

12 Experts available now in Live!

Get 1:1 Help Now