Log Backup

Question

Log Backup

SQL_Surfer

SSCrazy Eights

Points: 8316
More actions
March 25, 2013 at 3:34 pm

#291381

After I take the log backup, shouldn't I see the decrease in size of my transaction log file? Do I need to do anything to see the decrease in size of my transaction log file?

Viewing 15 posts - 1 through 15 (of 16 total)

You must be logged in to reply to this topic. Login to reply

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 1

SQL_Surfer (3/25/2013)
After I take the log backup, shouldn't I see the decrease in size of my transaction log file?

No. Log backups just mark space in the file as reusable.

Do I need to do anything to see the decrease in size of my transaction log file?

Why do you want to?

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

SQL_Surfer SSCrazy Eights Points: 8316 More actions · Answer 2

Ah...I thought it truncates the log as well. My drive is running out of space so I am thinking taking log bacup will shrink the size of transaction log and free up some space.

george sibbald SSC Guru Points: 104210 More actions · Answer 3

no , a log backup simply backs up the inactive part of the log file, it does not reduce the size of the file on disk.

Only a dbcc shrinkfile will reduce the size of the file but you do not need to run that as part of general database maintenance, only as a one off if the log has become bloated and you are short on disk space.

---------------------------------------------------------------------

george sibbald SSC Guru Points: 104210 More actions · Answer 4

SQL_Surfer (3/25/2013)
Ah...I thought it truncates the log as well. My drive is running out of space so I am thinking taking log bacup will shrink the size of transaction log and free up some space.

has the log not been backed up before?

---------------------------------------------------------------------

SQL_Surfer SSCrazy Eights Points: 8316 More actions · Answer 5

SQL_Surfer

SSCrazy Eights

Points: 8316

March 25, 2013 at 3:43 pm

#1600545

No. This is the first time doing it.

george sibbald SSC Guru Points: 104210 More actions · Answer 6

then the log will have been filling up and growing, consuming your disk space. Set up regular log backups and existing space in the log file will be reused, preventing further growth, you may not need to do anything else if current sizes and disk free space are acceptable.

---------------------------------------------------------------------

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 7

SQL_Surfer (3/25/2013)
Ah...I thought it truncates the log as well.

Yup, that's exactly what it does, truncating the log is the process of marking 0 or more virtual log files as reusable.

My drive is running out of space so I am thinking taking log bacup will shrink the size of transaction log and free up some space.

If the DB didn't have log backups before and as a result the log is too big, you can do a once-off shrink of the log file.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Grant Fritchey SSC Guru Points: 398679 More actions · Answer 8

And then, if your databases are in FULL recovery, you need to set up regular log backups. I'd suggest at least once an hour.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

SQL_Surfer SSCrazy Eights Points: 8316 More actions · Answer 9

Did the log backup, and ran the shrinkfile command for transaction log, but the size is still not getting smaller.

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 10

Then the active portion of the log is at the end of the file. Wait a while, let more log backups run, try again to shrink later.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

george sibbald SSC Guru Points: 104210 More actions · Answer 11

run dbcc loginfo(yourdbname)

the row with a status column value of 2 is the last active vlf, you will not be able to shrink beyond that point.

edit forgot to spell check!

---------------------------------------------------------------------

SQL_Surfer SSCrazy Eights Points: 8316 More actions · Answer 12

SQL_Surfer

SSCrazy Eights

Points: 8316

March 26, 2013 at 9:01 am

#1600859

yes.there is a row with status 2.

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 13

There will always be at least one row with a status of 2.

What George is asking is whether the last row in the output has a status of 2.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

SQL_Surfer SSCrazy Eights Points: 8316 More actions · Answer 14

No. Last row doesn't have status 2. I will try to shrink lil later today and see if that will reduce the size. I tried taking log backups from yesterday every 2 hours. So, it will have plenty of log backups by later this afternoon.