?
Solved

Alert for SQLServer:Databases:Percent Log

Posted on 2011-09-08
9
Medium Priority
?
380 Views
Last Modified: 2012-05-12
I have received alert for Desc: SQLServer:Databases:  Percent Log Used:  DATABASENAME Value = 77.3333333333333;
but when i check in the perfmon it is showing the average value between 17 and 18 and max value between 39 and 46.
I dont know why i got this alerts?If i am getting this alert what should i check in DB?
0
Comment
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 3
  • 2
9 Comments
 
LVL 17

Expert Comment

by:dbaSQL
ID: 36504014
Go into SSMS, your server name \ SQL Server Agent \ Alerts.   Look for the alert with that description.
0
 
LVL 40

Expert Comment

by:lcohan
ID: 36505503
just little bit more details how to do that - under the SQL Agent->right click Alerts->select New alert and set it like below to suit your needs:

  tlog alert
0
 
LVL 40

Expert Comment

by:lcohan
ID: 36505596
Sorry didn't read your question carefully....you should check your DB files uder DB properties and make sure you have regular bakups - FULL and T-logs otherwise your logs will just grow huge in time.

you can run query below to see space alocated/used in your DB:

use your_db
go

--check db files
SELECT name,size/128.0 as SizeInMB ,size/128.0 - CAST(FILEPROPERTY(name, 'SpaceUsed') AS int)/128.0 AS AvailableSpaceInMB
FROM sys.database_files;
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 5

Author Comment

by:VIVEKANANDHAN_PERIASAMY
ID: 36508100
Thanks Experts!

I tried belowDBCC command to find out the log space,

dbcc sqlperf(logspace)

But I have doubt in this,
My log file initial growth is 15101 and there is no autogrowth and alerts is configured for more than 60%
when the alert was received it was 77.33% but when checked the space it was just 0.54%.
How this could have decreased from 77.33% to 0.54%.
How the used space are decreasing from higher value to lower value?What is the happening internally?
0
 
LVL 17

Accepted Solution

by:
dbaSQL earned 2000 total points
ID: 36510214
SQL reserves a minimum of space for each log, by default, and it is always reported as 'in use'.  It may have been that you had active transactions at the time of the alert, and possibly a log record was flushed to disk in between the alert and when you checked it.  Hence, the decreased % used.

Check out this article, it gives a good explanation of the sqlperf.  http://support.microsoft.com/kb/281879

This one also provides more detail about the SQL Server I/O, and data nd log records being flushed to disk.  Or, 'what is happening internally':
http://technet.microsoft.com/en-us/library/cc966500.aspx

0
 
LVL 5

Author Comment

by:VIVEKANANDHAN_PERIASAMY
ID: 36511833
thanks Understood,

I have some doubts in this, When we use recovery with 'stopat' or 'stopbefore'syntax , then from where did the old data's are fetched? is it not from ldf file where transaction logs acts as pointer to the data in the ldf file?
0
 
LVL 17

Expert Comment

by:dbaSQL
ID: 36512117
well, i'd need a better idea of what was actually occurring from the time of the alert, to the point that you checked it.  were you actually in the process of retoring the database to a point in time, using stopat?  If restoring from a full backup, SQL is recreating the db in entirety.  It includes the tran log records (or your ldf), and after the restore completes, all of the uncommitted transactions are rolled back.  

The backups themselves, of course, copy all commited data to the db, and all uncommitted log entries (ldf).  When it starts, it puts a marker on the tran log, but the activity (transactions) will continue while the backup is running.  At the point of completion, it just captures all transactions since the starting marker.  The uncommitted transactions aren't backed up, but they are copied in, and then rolled back.

surely you're aware of this already.  i guess i would need a better understanding of the actual activity, at time of the alert and after, in order to help.
0
 
LVL 5

Author Comment

by:VIVEKANANDHAN_PERIASAMY
ID: 36513769
thanks actually i got the answer from your previous post.
I will open a new question on this.

Appreciated your work!
0
 
LVL 17

Expert Comment

by:dbaSQL
ID: 36513775
glad to help!
0

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

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…
What if you have to shut down the entire Citrix infrastructure for hardware maintenance, software upgrades or "the unknown"? I developed this plan for "the unknown" and hope that it helps you as well. This article explains how to properly shut down …
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.
Suggested Courses

777 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