December 14, 2011 at 9:05 am
Hello all,
for several days I had this issue with on database, it is ALWAYS doing a chekpoint and it is preventing any work since it block everyone else
right now that db is 80gb and have a log of 14gb, is there somethin I can do?
Now that db i on single user mode and I can do nothing with it ince the checkpoint is in there
Please any help will be greatly appreciated
I am using a 16 cpu with 72 RAM, sql server 2008 R2
December 14, 2011 at 9:34 am
I'm not sure how a checkpoint "blocks everybody else". Can you clarify?
A checkpoint is simply SQL Server writing data out of RAM onto the hard drive (essentially). Are you running into serious I/O issues or something?
- 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, 2011 at 9:43 am
the checkpoint spid is blocking all the other sessions on that databse, I can do anything on that database, I know this is not isolated, internet is plagued with people complaining about checkppoint on 2008
It couldn't be an I/O issue since there is nothing else running on that server
December 14, 2011 at 9:48 am
I don't think this is a wide problem, and I have rarely seen anyone complaining about the checkpoint process.
If you run SP_who, is it there you see the checkpoint process blocking?
What is your recovery interval set to? (http://msdn.microsoft.com/en-us/library/ms190770.aspx)
Is it a large server? Could this apply? http://blogs.msdn.com/b/joaol/archive/2008/11/20/sql-server-checkpoint-problems.aspx
As Gus mentioned, the checkpoint merely writes log and data pages that have changed from memory to disk.
December 14, 2011 at 9:53 am
I've never seen this posted here.
So +1 on the rare issue.
December 14, 2011 at 9:59 am
the recovery is set to 0
the spwho2 shows this
14 BACKGROUND sa . .DataStaging_v001CHECKPOINT32441712/14 08:40:5414 0
so it seems like doing nothing but still blocking sessions
The link you send is for 2005 mine is 2008 r2 would that patch be safely applied?
December 14, 2011 at 10:01 am
Unless there's a version matching your sql install then no.
December 14, 2011 at 10:05 am
ricardo_chicas (12/14/2011)
the checkpoint spid is blocking all the other sessions on that databse, I can do anything on that database, I know this is not isolated, internet is plagued with people complaining about checkppoint on 2008It couldn't be an I/O issue since there is nothing else running on that server
I just did a search for checkpoint issues blocking normal database use in SQL 2008, and I can't say that the internet is plagued by it, but there are a few instances out there.
Most commonly, the problem is that I/O subsystems are already overloaded, and the recovery interval is set far too long, or backups are running far more frequently than warranted on already overloaded I/O channels.
One of the common characteristics of the ones I looked at is having log and data files on the same I/O channels and disks on high-traffic servers, on hardware that could barely keep up with the regular load.
Also common to all the complaints is too little RAM available for the necessary data and plan caches, resulting in a lot of physical reads/writes for the most common queries. That adds a lot to the I/O load, of course.
Poorly written code, overuse of cursors, overuse of nested views, and other common "overload-the-server" type issues with the code, are also common contributors to the problem.
There was a problem with the way SQL 2005 handled the checkpoint throttling for a while, but that was fixed years ago. I don't see anything about a known issue in SQL 2008. Doesn't mean it doesn't exist, just means I don't see one in my searching.
So, have you looked into the load on your I/O systems, and into things like cache lifespan and physical vs logical reads in your most common queries? Are your log and data files on separate I/O channels and disks?
- 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, 2011 at 1:33 pm
Without seeing more sp_who output, you can't determine that the checkpoint is blocking everyone. I might recommend you get a script for the block tree, or get sp_whoisactive and use that to debug a bit.
Gus advice is correct. You cannot apply a 2005 patch to 2008. I was merely wondering if those symptoms are similar or perhaps the same. That issue should be fixed in 2008 and R2, but you never know.
If you are seeing constant checkpoints, then your I/O channel is overloaded. You can increase the recovery interval to something like 5 minutes and see if checkpoints decrease of symptoms change, but that's not a fix. It's more a troubleshooting tool to understand what's wrong.
December 14, 2011 at 2:11 pm
That the server is not running anything else does not mean that you don't have an IO isuse, and these issues comes in all flavors. One I have experienced is flat battery on a raid controller with battery backed cache. To maintain data safety, the controller then turned off write cache. The performance drop was enormous.
December 15, 2011 at 7:43 am
fot the last 16 hours I've been trying to shrink the log file, it is 35gb now, I am the only user, I close all access to the server but me, the database is in single user mode
the database is in Simple mode, and my shrink has been blocked by a checkpoint all that time
December 15, 2011 at 7:46 am
That's not a SPID blocking issue. That means you need to issue a checkpoint, then rebuild the log. The problem isn't the presence of a checkpoint, it's the absence of it.
- 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, 2011 at 7:47 am
ricardo_chicas (12/15/2011)
fot the last 16 hours I've been trying to shrink the log file, it is 35gb now, I am the only user, I close all access to the server but me, the database is in single user modethe database is in Simple mode, and my shrink has been blocked by a checkpoint all that time
You still haven't answered the questions about your disk setup and file structure. Those would really help us a lot. i.e. Are the logs on separate PHYSICAL disk from the data files... Things like this.
Jared
Jared
CE - Microsoft
December 15, 2011 at 7:57 am
That server use san space, I have two drives but i know that doesn't means that they are fully separate, still I am using a different channel for each one, all the other databases on that server are working perfectly fine and they use that same SAN, the network utilization is always below 15%, all I want right now is to be able to reduce the size of that log
December 15, 2011 at 8:00 am
Try this:
CHECKPOINT 60;
Issue that command from the database you are trying to shrink the log file for.
Once that runs, run DBCC ShrinkFile on the log file you are trying to shrink.
Post the output/results for both of those commands here.
- 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 15 posts - 1 through 15 (of 16 total)
You must be logged in to reply to this topic. Login to reply