December 14, 2009 at 1:05 pm
I have SQL 2005 running on a Windows Server 2003 and I have a question about Buffer Cache Hit Ratio and Page Life Expectancy
First: What does Buffer Cache hit ratio tell me and why is mine always at around 100%. Is this good or bad?
Second: Page life Expectancy is usually up to about 3000 but sometimes I see it down to about 50 and don’t understand why such a difference.
Third: I have a test database server and have two production databases just sitting on them doing nothing. No transaction, no backups, nothing happening and the Transaction log file grows like crazy. The recover is full. I put them there to test a application service pack and then forgot about them. I check the server the other day and DATA drive was full. The database is 10GB and the tran log is 128GB. I ran shrinkdabase and it went down to 117GB. No open transaction.
My database are running fine, but wondered about these items. Please help if you can.
Thanks
Jeff
Jeff
December 14, 2009 at 1:22 pm
jayoub1 (12/14/2009)
I have SQL 2005 running on a Windows Server 2003 and I have a question about Buffer Cache Hit Ratio and Page Life ExpectancyFirst: What does Buffer Cache hit ratio tell me and why is mine always at around 100%. Is this good or bad?
It is good to have it as high as possible. It represents, the % of time the queries / hits are being answered using the data inside the buffer.
Second: Page life Expectancy is usually up to about 3000 but sometimes I see it down to about 50 and don’t understand why such a difference.
The # of seconds a page is residing in the memory. Least used pages are always flushed and the frequently used pages are held in the buffer. The higher this values the better. Is the value just dropping off from 3000 to 50 straight away? If it is showing 50 after a restart, that may be a common value and would increases.
Refer Books Online for more detailed Infomation...
Third: I have a test database server and have two production databases just sitting on them doing nothing. No transaction, no backups, nothing happening and the Transaction log file grows like crazy. The recover is full. I put them there to test a application service pack and then forgot about them. I check the server the other day and DATA drive was full. The database is 10GB and the tran log is 128GB. I ran shrinkdabase and it went down to 117GB. No open transaction.
Confusing to say the least. You have Test and Prod db's on the same machine? Why are you not taking any backups? If the server crashes is it ok to loose all the data?
Blog -- LearnSQLWithBru
Join on Facebook Page Facebook.comLearnSQLWithBru
Twitter -- BruMedishetty
December 14, 2009 at 1:24 pm
jayoub1 (12/14/2009)
I have SQL 2005 running on a Windows Server 2003 and I have a question about Buffer Cache Hit Ratio and Page Life ExpectancyFirst: What does Buffer Cache hit ratio tell me and why is mine always at around 100%. Is this good or bad?
Per Books Online: "Percentage of pages found in the buffer cache without having to read from disk. The ratio is the total number of cache hits divided by the total number of cache lookups over the last few thousand page accesses. After a long period of time, the ratio moves very little. Because reading from the cache is much less expensive than reading from disk, you want this ratio to be high. Generally, you can increase the buffer cache hit ratio by increasing the amount of memory available to SQL Server."
So, close to 100% is good.
Second: Page life Expectancy is usually up to about 3000 but sometimes I see it down to about 50 and don’t understand why such a difference.
Again, per BOL: "Number of seconds a page will stay in the buffer pool without references."
Because you're running mostly out of your cache (as per the above stat), some of the pages will stay in memory without use for a while, others will be in use. That's all that means. If the number is really low, it usually means pages are being forced out of memory even if they would be used.
Third: I have a test database server and have two production databases just sitting on them doing nothing. No transaction, no backups, nothing happening and the Transaction log file grows like crazy. The recover is full. I put them there to test a application service pack and then forgot about them. I check the server the other day and DATA drive was full. The database is 10GB and the tran log is 128GB. I ran shrinkdabase and it went down to 117GB. No open transaction.
If the databases are in Full recovery, the tran log will remain full till you fun a log backup. Run one, then see whether you end up with more room available in the log file.
My database are running fine, but wondered about these items. Please help if you can.
Thanks
Jeff
Does that help?
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
December 14, 2009 at 3:44 pm
Thanks for the help. i wont worry about the Buffer and the Page life, but the transaction log file is kind of weird.
The server is doing nothing. It was used just to test a service pack and now it sits as a hot stand by server incase something happens. I checked the DATA drive and there was only 2 GB left from a 200GB hard drive. The tran log is suddenly 128GB and the db is only 10Gb. It is like the tran log will just keep growing even though there is no activity.
I am new to the db world and just was wondering why this happens. I will detach and delete the databases because they are not being used.
Also, I could not run a tran log backup because there was not enough disk space
Jeff
December 14, 2009 at 5:17 pm
nothing running at all? Even just running reindex on the database will cause log file growth and if you have growth factor at default 10% its going to grow in some pretty big chunks as it gets bigger.
In what way is it a hot standby server? Are you actively copying live data to it by any means?
---------------------------------------------------------------------
December 14, 2009 at 6:15 pm
George is right. In what way is it Hot Standby Server.
If it is a Hot Standby Server why are you planning to detach and delete the databases?
Have you got any info what is configured on this Server? If Databases are deleted what in that case if your Primary Database Server fails?
If you are managing them currently, look for more infor from your team or manager without additional details, deleting databases should not be considered.
Blog -- LearnSQLWithBru
Join on Facebook Page Facebook.comLearnSQLWithBru
Twitter -- BruMedishetty
December 15, 2009 at 7:03 am
The log file won't grow without some sort of activity. That activity could be automated maintenance plans, could be something running that nobody told you about, could be from a hacking attempt, could be an infinite number of other things. But, regardless, there's gotta be activity.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
December 15, 2009 at 8:58 am
Q: nothing running at all? Even just running reindex on the database will cause log file growth and if you have growth factor at default 10% its going to grow in some pretty big chunks as it gets bigger.
Q: In what way is it a hot standby server? Are you actively copying live data to it by any means?
Answer: Yes nothing at all, no reindexing nothing. It is set to 10% autogrowth. I have backup, reindex, tran log backups running on the production server, but did not set this up on the test server when i restored only the production databasess.
This is just a spare server with SQL installed. If the production server goes down I restore to the spare and connect it up. Restoring my db takes about 30 minutes max. No live data
Q: George is right. In what way is it Hot Standby Server.
A: Just has Windows Server 2003 and SQL 2005 installed and making noise.
Q: If it is a Hot Standby Server why are you planning to detach and delete the databases?
A: I have already detached the db, nobody is off line, we have 30 users only. I would like to recover the drive space.
Q: Have you got any info what is configured on this Server? If Databases are deleted what in that case if your Primary Database Server fails?
A: I just restore and connect the application. It takes me about 30 Minutes total since OS and SQL 2005 are already installed.
Q: If you are managing them currently, look for more infor from your team or manager without additional details, deleting databases should not be considered.
A: I used the server for testing only and deleting will not be an issue.
I am telling you no activity and log files just grow like weeds as long as it is attached. The same thing happened at my home. I restored the db from my work to a server i had at home and found the same issue. I had to detach and delete there too.
Its not a big deal just thought i
Jeff
December 15, 2009 at 9:28 am
jayoub1 (12/15/2009)
Q: nothing running at all? Even just running reindex on the database will cause log file growth and if you have growth factor at default 10% its going to grow in some pretty big chunks as it gets bigger.Q: In what way is it a hot standby server? Are you actively copying live data to it by any means?
Answer: Yes nothing at all, no reindexing nothing. It is set to 10% autogrowth. I have backup, reindex, tran log backups running on the production server, but did not set this up on the test server when i restored only the production databasess.
This is just a spare server with SQL installed. If the production server goes down I restore to the spare and connect it up. Restoring my db takes about 30 minutes max. No live data
Q: George is right. In what way is it Hot Standby Server.
A: Just has Windows Server 2003 and SQL 2005 installed and making noise.
Q: If it is a Hot Standby Server why are you planning to detach and delete the databases?
A: I have already detached the db, nobody is off line, we have 30 users only. I would like to recover the drive space.
Q: Have you got any info what is configured on this Server? If Databases are deleted what in that case if your Primary Database Server fails?
A: I just restore and connect the application. It takes me about 30 Minutes total since OS and SQL 2005 are already installed.
Q: If you are managing them currently, look for more infor from your team or manager without additional details, deleting databases should not be considered.
A: I used the server for testing only and deleting will not be an issue.
I am telling you no activity and log files just grow like weeds as long as it is attached. The same thing happened at my home. I restored the db from my work to a server i had at home and found the same issue. I had to detach and delete there too.
Its not a big deal just thought i
If there's no known activity, then there's hidden activity. Parse the log file. ApexSQL and Lumigent both have products that will look into it for you and allow you see what the activity is.
If you've already deleted them, then you'll never know. If it's because of a security issue, you'll never know that either. If your system had a worm, or has been hacked, or something of that sort, you'll never know.
The log doesn't grow without activity. Thus, if there was growth, there was activity. It really is as simple as that. If you've deleted those log files, and you're happy with not knowing, that's fine. Personally, if I had a situation like that, I'd want to know what was going on. If the server has been compromised, by malware or a hacker, that would matter to me. If it doesn't matter to you, then it doesn't matter to you.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
December 16, 2009 at 10:21 am
You are now making me curious about what is happening. I detached but did not delete the files yet, so I can look into this situation.
I am a new dba so I don’t know how to Parse the log file, or use these products ApexSQL and Lumigent. I will look into them and see how to do this.
Thanks,
Jeff
Jeff
December 16, 2009 at 10:55 am
Even if you have no 3rd part tool, you can find what's going on with the databases. You have Activity Monitor start there, look if you have any kind of SQL Agent Jobs, or Maintenance Plan.
There is Profiler, you can use that if still no clues are found, and remember Profiler should be used keeping in mind that it would be performance overhead (in your case you say no one is using it) so that should not be an issue i guess.
Blog -- LearnSQLWithBru
Join on Facebook Page Facebook.comLearnSQLWithBru
Twitter -- BruMedishetty
December 17, 2009 at 6:49 am
Bru Medishetty (12/16/2009)
Even if you have no 3rd part tool, you can find what's going on with the databases. You have Activity Monitor start there, look if you have any kind of SQL Agent Jobs, or Maintenance Plan.There is Profiler, you can use that if still no clues are found, and remember Profiler should be used keeping in mind that it would be performance overhead (in your case you say no one is using it) so that should not be an issue i guess.
That'll get ongoing activity. But if you want to get what caused the log to grow, the best place to look is in the log. More direct that way.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply