We help IT Professionals succeed at work.

Backup Sqlserver 2008

Mortarello
Mortarello used Ask the Experts™
on
Guys,

I have my log file size of 800 mb and i did a backup. The Backup is fine but my log file still the same size. because I thought after the backup, the log file size would be less.

Am i correct ?

Many Thanks
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
SQL Service Manager
Commented:
yes correct but if you do a backup while the system is running hard then there is the probability to have the emptied part of the log between used parts of the log file and you cannot decrese the size when you shrink it. to check that run

DBCC LOGINFO

if you have LSN's with status 2 at both the start and end of the list and in between 0. then it means emptied logs are between used logs and you cannot delete emptied logs. in such a case run log backup again and check with DBCC LOGINFO until you have 0 statused LSN's at the end. after that shrink the log file.

Author

Commented:
Tks, I did the backup 3 times and shrink.

Now file size is 15 mb. :)

Tks
Guy Hengel [angelIII / a3]Billing Engineer
Most Valuable Expert 2014
Top Expert 2009

Commented:
no, not correct.

the LOG BACKUP will NOT decrease the log file size .... just mark the space used internally as reusable.
the FULL BACKUP will not act on the log file at all in that regards.

note: you should, in regular operations, NOT try to regularly reduce the log file, as that will just require the db to increment the file size again ...
instead, make sure you just run LOG backups regularly enough.

Author

Commented:

You mean that if I do regular log backup. The file is not going to grow because he is going to use the reusable area ?

Tks angelll.
Metehan OzculluSQL Service Manager

Commented:
yes it will use the emptied log area. to get that area back to os you should shrink the file.

Author

Commented:
Ok.

Thank you very much  mozcullu and angelIII