How do I know if the database log file is almost full?

Question

How do I know if the database log file is almost full?

Loner

SSC-Insane

Points: 21279
More actions
May 14, 2008 at 2:22 pm

#187686

In SQL Server 2000, there is a nice task pad to show me the database and the log file is xxx% used and xxx% left. That is when I will do a shrink database or shrink log file. Is there anything liked that in SQL Server 2005 ? When I look at the property of the database, it tells me the size of the database and how much space is available but it does not say anything about the log file. When I look at the 'Files' tab, it shows the initial size but not the current size or how much it has grown.
Am I missing something?;)

Viewing 8 posts - 1 through 7 (of 7 total)

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

colin.Leversuch-Roberts SSC Guru Points: 52643 More actions · Answer 1

Yes! Standard reports will show disk usage by file. Right click on the database and examine std reports.

Shrinking files, especially databases isn't good practice - you'll suffer internal fragmentation and almost certainly ntfs fragmentation too

[font="Comic Sans MS"]The GrumpyOldDBA[/font]
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/

Loner SSC-Insane Points: 21279 More actions · Answer 2

Loner

SSC-Insane

Points: 21279

May 14, 2008 at 3:15 pm

#815560

What is the best way to shrink the database?

Jared Hunt Mr or Mrs. 500 Points: 507 More actions · Answer 3

Jared Hunt

Mr or Mrs. 500

Points: 507

May 14, 2008 at 8:49 pm

#815601

Use DBCC SHRINKDATABASE OR DBCC SHRINKFILE.

Jeffrey Williams SSC Guru Points: 89773 More actions · Answer 4

The best way to shrink is: DON'T. Size your database and log files appropriately and expand them as needed.

See: http://www.karaszi.com/SQLServer/info_dont_shrink.asp

Jeffrey Williams
Problems are opportunities brilliantly disguised as insurmountable obstacles.

How to post questions to get better answers faster
Managing Transaction Logs

Skandalis Hall of Fame Points: 3517 More actions · Answer 5

jeff.williams3188 (5/14/2008)
The best way to shrink is: DON'T. Size your database and log files appropriately and expand them as needed.
See: http://www.karaszi.com/SQLServer/info_dont_shrink.asp

Hi Jeff,

Will backing up the transaction log not also be ok?

Gail Shaw SSC Guru Points: 1004494 More actions · Answer 6

Backing up he log doesn't change the size of the log file on disk. It just removes inactive log records (after backing them up) and clears space in the log file

Edit: I shouldn't try typing before first coffee....

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

Skandalis Hall of Fame Points: 3517 More actions · Answer 7

Thanks Gail. Just double-checked on my side as well... 56GB :blink: