Log File Alert and Negative Free Space!? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

SQL Server Administration (2005)

Log File Alert and Negative Free Space!?

Author

Topic

gwater
Starting Member

15 Posts

Posted - 2007-09-24 : 17:08:18

We've got an alert setup on our production database to warn us when the log file(s) exceeds 7 gigs. The Alert is triggering:

"The SQL Server performance counter 'Log File(s) Size (KB)' (instance 'Lexus') of object 'SQLServer:Databases' is now above the threshold of 7000000.00 (the current value is 7057656.00)."

However, according to the file system, the database properties screen files, and properties tabs, the log files combined are at under 5 gigs so this alert should not be going off.

The scary part is, when going to right-click on the database, choosing "tasks" and going to "Shrink > Files", the "Free Space" shows negative numbers for the first log file:

Log 1
Currently Allocated space: 118.69 MB
Available Free Space: -5323.24 MB (-4485%)

Log 2
Currently Allocated space: 4853.13 MB
Available Free Space: 411.20 MB (7%)

Has anybody ever run into this? Should I be worried that there is a bigger issue at hand?

Thanks!

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2007-09-24 : 17:09:51

Run DBCC UPDATEUSAGE or sp_spaceused with @updateusage = TRUE to correct inaccuracies.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

gwater
Starting Member

15 Posts

Posted - 2007-09-24 : 17:21:53

Thank you, I ran both. Both executed successfully.

The first (smaller) log file still shows -5327.46 MB Free space.

Output of sp_spaceused:

database_name database_size unallocated space
---------- ------------------ ------------------
Lexus 14081.19 MB 660.62 MB

reserved data index_size unused
------------------ ------------------ ------------------ ------------------
7627528 KB 4309992 KB 2755840 KB 561696 KB

gwater
Starting Member

15 Posts

Posted - 2007-09-24 : 17:38:19

The latest alert is: The SQL Server performance counter 'Log File(s) Size (KB)' (instance 'Lexus') of object 'SQLServer:Databases' is now above the threshold of 7000000.00 (the current value is 8212280.00).

Actual physical size on the disk is
124,452,864 + 6,137,446,400 = 6,261,899,264

UPDATED 3:27PM PST:
The SQL Server performance counter 'Log File(s) Size (KB)' (instance 'Lexus') of object 'SQLServer:Databases' is now above the threshold of 7000000.00 (the current value is 8212280.00).

DBCC SQLPERF(LOGSPACE) Says:
Database Name Log Size (MB) Log Space Used (%) Status
Lexus 5971.797 98.01733 0

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2007-09-24 : 18:25:25

This script will give you a better analysis of how much space each database file is using.

Get Server Database File Information
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=89058

CODO ERGO SUM

gwater
Starting Member

15 Posts

Posted - 2007-09-24 : 18:31:28

So should I not be worried that SQL Server Management Studio shows -5735.87 MB (-4832%) space free on the database shrink screen!?

rmiao
Master Smack Fu Yak Hacker

7266 Posts

Posted - 2007-09-24 : 23:07:43

Don't worry, it'll display correct number once you updated usage.

gwater
Starting Member

15 Posts

Posted - 2007-09-25 : 11:16:03

quote:
Originally posted by rmiao

Don't worry, it'll display correct number once you updated usage.

I've already updated usage several times. Today it says -8100.83 MB (-4880%) Free space.

Also, the log file is growing large. One full backup and several log backups later, it is larger than the database at 8 gigs now.

Van
Constraint Violating Yak Guru

462 Posts

Posted - 2007-09-25 : 11:18:37

Did you check to see if there are any open transactions?

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2007-09-25 : 11:31:56

In regards to the log file "growing large", how often are you performing transaction log backups? What maintenance jobs do you have in place each day?

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

gwater
Starting Member

15 Posts

Posted - 2007-09-25 : 11:45:59

Its running in full recovery with log backups every 45 minutes, Full backup once a day. These are the only maintenance-related jobs running each daym but there are several data manipulation jobs.

Its just odd that suddenly the log is around 2x what it normally is without any change to the number of connections, transactions, or jobs.

The last time this happened, the mirror had trouble syncing during an index reoganization, so the log file grew from waiting to commit the transactions, but this time mirroring is reporting everything in sync and running fine.

What started as an inaccurate alert with weird free space numbers, has now ended up at the actual alert level which has never happened as far as I can remember, even with the mirroring issue a few months ago.

quote:
Originally posted by tkizer

In regards to the log file "growing large", how often are you performing transaction log backups? What maintenance jobs do you have in place each day?

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2007-09-25 : 11:55:32

What type of database mirroring are you using? Async or sync?

You mention an index reorg, so do you have that running as a maintenance job at night?

You should perhaps increase the frequency of your tlog backups. We backup ours every 15 minutes. We also have async dataase mirroring in place. The only time that the tlog fills up or grows is when we have some large transaction running that is taking a bit to get copied to the DR site. It used to be with SQL 2000 that the index reorg used the tlog a bit too, but 2005 seems to have resolved that issue.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

gwater
Starting Member

15 Posts

Posted - 2007-09-25 : 12:31:30

We're using Async mirroring as well.

We do not currently have index reorg running as a job. Because of how it effected mirroring, I am running it myself "every now and then" on weekends. It has been a while since the last reorg.

Is there a performance hit to ongoing transactions by increasing log backups? What, in your opinion, are the costs to the server/database of incerasing log backups? Or does it just mean more to apply in the event of a restore?

quote:
Originally posted by tkizer

What type of database mirroring are you using? Async or sync?

You mention an index reorg, so do you have that running as a maintenance job at night?

You should perhaps increase the frequency of your tlog backups. We backup ours every 15 minutes. We also have async dataase mirroring in place. The only time that the tlog fills up or grows is when we have some large transaction running that is taking a bit to get copied to the DR site. It used to be with SQL 2000 that the index reorg used the tlog a bit too, but 2005 seems to have resolved that issue.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2007-09-25 : 12:39:36

We have not seen any performance issues with the frequency of our tlog backups. I'd say there's less of an impact the more often you run them as there's less to backup. You of course wouldn't want it running every minute though.

I don't mind applying more backups in the case of a restore as I can easily script the restore commands out. There's not any more work for me if there were 10 files to restore or 100. You just need the right script to generate the info for you.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

gwater
Starting Member

15 Posts

Posted - 2007-09-25 : 13:12:07

Thank you for all your help so far Tara, you and this message board are a great resource for Software Engineers with DBA duties thrown onto the mix in the job descriptions of small companies!

Another question: is there a way to view (in any meaningful way) which transactions are waiting to be committed or mirrored or not being backed up/purged in the log file?

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2007-09-25 : 13:14:13

Check out DBCC OPENTRAN. For the database mirroring part, I haven't yet found it. I'm sure it's in a DMV somewhere though.

If you also have replication on this database like we do, then that's also a culprit for the tlog issue.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Van
Constraint Violating Yak Guru

462 Posts

Posted - 2007-09-25 : 13:14:58

Did you check for open transactions?

dbcc opentran

Does it return anything or say that there are no transactions to be committed. If it returns something, what's the date on it?

Also, you mention that you are still running tlog backups. How big are they now? Are they similar in size (every 45 min) as they normally are before the problem happened?

gwater
Starting Member

15 Posts

Posted - 2007-09-25 : 13:18:23

quote:
Originally posted by tkizer

Check out DBCC OPENTRAN. For the database mirroring part, I haven't yet found it. I'm sure it's in a DMV somewhere though.

If you also have replication on this database like we do, then that's also a culprit for the tlog issue.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Transaction information for database 'Lexus'.

Replicated Transaction Information:
Oldest distributed LSN : (1149423:42844:4)
Oldest non-distributed LSN : (1149423:42846:1)
DBCC execution completed. If DBCC printed error messages, contact your system administrator.

Mean anything to you?

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2007-09-25 : 13:28:54

You've got replication in place for this database. Check out the monitor to see its status.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

gwater
Starting Member

15 Posts

Posted - 2007-09-25 : 13:31:34

quote:
Originally posted by Van

Did you check for open transactions?

dbcc opentran

Does it return anything or say that there are no transactions to be committed. If it returns something, what's the date on it?

How would I check the date of the indicated transactions?
Oldest distributed LSN : (1149423:42844:4)
Oldest non-distributed LSN : (1149423:42846:1)

quote:

Also, you mention that you are still running tlog backups. How big are they now? Are they similar in size (every 45 min) as they normally are before the problem happened?


Last few backup log files:

CURRENT              LAST WEEKDAY AVG
173,774 KB  10:22AM    11,361 KB 
638,614 KB   9:37AM     3,425 KB 
  5,956 KB   8:52AM     4,705 KB      
  5,124 KB   8:07AM     8,481 KB
  1,604 KB   7:22AM     4,321 KB
  3,588 KB   6:37AM     2,657 KB

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2007-09-25 : 13:33:44

They are for replication and aren't transactions from say an application. You would just need to check replication monitor to ensure that it is working. Also check that all of the jobs are running successfully on the distributor server.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources