Dear Subscribers,
My SQL Server 7 is running on Win NT 4.0 SP6a system with RAID5. I suspect
that one of the system disk volumes has a problem with its file system, and
I was advised to check that by running CHKDSK /F /R. The problem is the
database files are stored on this volume and I'm not sure what kind of
effect the disk checker will have on them if it finds any errors. Will
everything be OK?
Many thanks!Hi
I am not sure what effect it will have as the files will be in use, and if
there was any corruption SQL Server may have problems anyhow!
I suggest that you back up the databases before trying this and make sure
they are retained on some reliable media.
John
"Oskars Salnins" <osalnins@.inbox.lv> wrote in message
news:uwmiilFVDHA.532@.TK2MSFTNGP10.phx.gbl...
> Dear Subscribers,
> My SQL Server 7 is running on Win NT 4.0 SP6a system with RAID5. I suspect
> that one of the system disk volumes has a problem with its file system,
and
> I was advised to check that by running CHKDSK /F /R. The problem is the
> database files are stored on this volume and I'm not sure what kind of
> effect the disk checker will have on them if it finds any errors. Will
> everything be OK?
>
> Many thanks!
>|||Thanks John.
Surely I would stop SQL server before carrying out the check. This far I
didn't notice any problems with SQL Server itself (i.e. DBCC CHECKDB shows
all DB's are clean). I think that means DB files aren't affected.
"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:3f240abf$0$10766$afc38c87@.news.easynet.co.uk...
> Hi
> I am not sure what effect it will have as the files will be in use, and if
> there was any corruption SQL Server may have problems anyhow!
> I suggest that you back up the databases before trying this and make sure
> they are retained on some reliable media.
> John
> "Oskars Salnins" <osalnins@.inbox.lv> wrote in message
> news:uwmiilFVDHA.532@.TK2MSFTNGP10.phx.gbl...
> > Dear Subscribers,
> >
> > My SQL Server 7 is running on Win NT 4.0 SP6a system with RAID5. I
suspect
> > that one of the system disk volumes has a problem with its file system,
> and
> > I was advised to check that by running CHKDSK /F /R. The problem is the
> > database files are stored on this volume and I'm not sure what kind of
> > effect the disk checker will have on them if it finds any errors. Will
> > everything be OK?
> >
> >
> > Many thanks!
> >
> >
>|||Oskars,
> Surely I would stop SQL server before carrying out the check.
Yes you would.
> This far I
> didn't notice any problems with SQL Server itself (i.e. DBCC CHECKDB shows
> all DB's are clean). I think that means DB files aren't affected.
What symptoms are you getting that makes you think a chkdsk is needed?
Neil Pike MVP/MCSE. Protech Computing Ltd
Reply here - no email
SQL FAQ (484 entries) see
http://forumsb.compuserve.com/gvforums/UK/default.asp?SRV=MSDevApps
(faqxxx.zip in lib 7)
or www.ntfaq.com/Articles/Index.cfm?DepartmentID=800
or www.sqlserverfaq.com
or www.mssqlserver.com/faq|||Neil,
The system's paging file and DB files are stored on the same disk volume,
and I got into paging file corruption issue as discussed in this KB article:
http://support.microsoft.com/default.aspx?kbid=216446
which suggests to run chkdsk /F /R to see if it corrects the problem. I've
already followed the other suggestion from the article - rebuilt the paging
file and moved it to another disk volume. Nevertheless, I'd also like to
know what caused the corruption, and fix that. Server hardware is clean so I
suspect the file system. Besides, if the paging file got corrupt then the
same thing could probably happen to DB files as well.
"Neil Pike" <neilpike@.compuserve.com> wrote in message
news:VA.000060fd.0fe50a5f@.compuserve.com...
> Oskars,
> > Surely I would stop SQL server before carrying out the check.
> Yes you would.
> > This far I
> > didn't notice any problems with SQL Server itself (i.e. DBCC CHECKDB
shows
> > all DB's are clean). I think that means DB files aren't affected.
> What symptoms are you getting that makes you think a chkdsk is needed?
> Neil Pike MVP/MCSE. Protech Computing Ltd
> Reply here - no email
> SQL FAQ (484 entries) see
> http://forumsb.compuserve.com/gvforums/UK/default.asp?SRV=MSDevApps
> (faqxxx.zip in lib 7)
> or www.ntfaq.com/Articles/Index.cfm?DepartmentID=800
> or www.sqlserverfaq.com
> or www.mssqlserver.com/faq
>|||Oskars - the the chkdsk /f /r output say that it found and fixed problems?
The Q article you listed is a tad vague - just because you got an A or a 1E
blue screen doesn't mean you had a corrupt page file. This is one of 100's or
1000's of reasons for the same blue screen...
> The system's paging file and DB files are stored on the same disk volume,
> and I got into paging file corruption issue as discussed in this KB article:
> http://support.microsoft.com/default.aspx?kbid=216446
> which suggests to run chkdsk /F /R to see if it corrects the problem. I've
> already followed the other suggestion from the article - rebuilt the paging
> file and moved it to another disk volume. Nevertheless, I'd also like to
> know what caused the corruption, and fix that. Server hardware is clean so I
> suspect the file system. Besides, if the paging file got corrupt then the
> same thing could probably happen to DB files as well.
Neil Pike MVP/MCSE. Protech Computing Ltd
Reply here - no email
SQL FAQ (484 entries) see
http://forumsb.compuserve.com/gvforums/UK/default.asp?SRV=MSDevApps
(faqxxx.zip in lib 7)
or www.ntfaq.com/Articles/Index.cfm?DepartmentID=800
or www.sqlserverfaq.com
or www.mssqlserver.com/faq
Showing posts with label disk. Show all posts
Showing posts with label disk. Show all posts
Sunday, March 11, 2012
Wednesday, March 7, 2012
CheckPoint question
Is the quantity of data written to disk during a checkpoint, related to the
size of the table? The checkpoint takes place when the log file is 70% full.
Compare these two identical files on separate servers except for rec amts.
10M rec file has checkpoint 47MG.
100M rec file has checkpoint 81MG.
Can I expect that as the tables get larger the checkpoint will become larger?
Thanks,
Don
SQL2000
"donsql22222" <donsql22222@.discussions.microsoft.com> wrote in message
news:B081E9A7-E100-4BC3-91A3-DEAEBEF72553@.microsoft.com...
> Is the quantity of data written to disk during a checkpoint, related to
> the
> size of the table? The checkpoint takes place when the log file is 70%
> full.
> Compare these two identical files on separate servers except for rec amts.
> 10M rec file has checkpoint 47MG.
> 100M rec file has checkpoint 81MG.
> Can I expect that as the tables get larger the checkpoint will become
> larger?
Checkpoint writes the dirty pages back to the database files, so the size
depends on the number of changes since the last checkpoint and how many of
those pages have been flushed by the lazywriter thread. Also the recovery
interval server parameter affects checkpoint size as well as the amount of
memory on the server.
David
|||I've changed the recovery interval to 1, 100, 1000 respectively, and saw no
change at all in the frequency of the flush or the amount of data flushed.
It always flushes when the log file is 70% full.
I'd be really interested in manipulating the amount of data stored in memory
and/or the frequency of the flush...
Any advice much appreicated.
Don
"David Browne" wrote:
> "donsql22222" <donsql22222@.discussions.microsoft.com> wrote in message
> news:B081E9A7-E100-4BC3-91A3-DEAEBEF72553@.microsoft.com...
> Checkpoint writes the dirty pages back to the database files, so the size
> depends on the number of changes since the last checkpoint and how many of
> those pages have been flushed by the lazywriter thread. Also the recovery
> interval server parameter affects checkpoint size as well as the amount of
> memory on the server.
> David
>
>
|||The 70% deal is because you have the recovery mode set to SIMPLE or you have
never done a proper FULL backup. The tran log will be truncated at 70% full
in Simple mode. This in turn forces a checkpoint to occur. But that just
means that the amount of data in the tran log is still less than SQL Server
thinks it will take to recover in 1 minute. If the log file was larger you
would probably see checkpoints before the 70% full mark. What is the reason
for wanting to change this? If checkpoints are causing issues with
performance you really need to address the source of the trouble and not try
to tweak around it. That means placing the log file on a Raid 1 or raid 10
by itself and a good amount of write back cache will help as well.
Andrew J. Kelly SQL MVP
"donsql22222" <donsql22222@.discussions.microsoft.com> wrote in message
news:EEEE73EA-9911-4302-9D2A-9E8C1083EA86@.microsoft.com...[vbcol=seagreen]
> I've changed the recovery interval to 1, 100, 1000 respectively, and saw
> no
> change at all in the frequency of the flush or the amount of data flushed.
> It always flushes when the log file is 70% full.
> I'd be really interested in manipulating the amount of data stored in
> memory
> and/or the frequency of the flush...
> Any advice much appreicated.
> Don
>
> "David Browne" wrote:
|||I'm trying to solve an IO problem of when the checkpoint occurs, it writes a
large amount of data onto the disk and this is causing SELECT durations to
skyrocket at this time.
I have around 20 servers so upgrading them to Raid Arrays would be costly.
If I can solve the problem with a tweak, it would be worth the effort.
I've tried FULL and SIMPLE and it has no effect on when the log gets
checkpointed. It's always when it reaches 70% which is what BOL says so it's
in line with expectations. I've got the logs truncated and they're only
taking up approx 7MG.
However, if I could tweak the checkpoint so that it occured say at 50%, then
that amount of data being written would be less and hence less IO and hence
less effect on the SELECT durations.
The LDF and MDF are on their own physical drives.
Thx,
Don
"Andrew J. Kelly" wrote:
> The 70% deal is because you have the recovery mode set to SIMPLE or you have
> never done a proper FULL backup. The tran log will be truncated at 70% full
> in Simple mode. This in turn forces a checkpoint to occur. But that just
> means that the amount of data in the tran log is still less than SQL Server
> thinks it will take to recover in 1 minute. If the log file was larger you
> would probably see checkpoints before the 70% full mark. What is the reason
> for wanting to change this? If checkpoints are causing issues with
> performance you really need to address the source of the trouble and not try
> to tweak around it. That means placing the log file on a Raid 1 or raid 10
> by itself and a good amount of write back cache will help as well.
> --
> Andrew J. Kelly SQL MVP
>
> "donsql22222" <donsql22222@.discussions.microsoft.com> wrote in message
> news:EEEE73EA-9911-4302-9D2A-9E8C1083EA86@.microsoft.com...
>
>
|||You can adjust the recovery interval so it checkpoints more often and hence
less at any one time. But it will happen more often. So in the end you
will still have interruption in the long run. While you can tweak some
there is no getting around the fact that you need proper hardware to handle
certain situations. You can't tweak some things and I/O capacity is one of
them. It has a certain limit and you have apparently reached it. The best
thing to help limit the interruptions of checkpoints is a good caching disk
controller with lots of write back cache. If you are using single disks you
don't have much choice. You may be able to make things a little better with
the recovery interval but it won't work magic.
Andrew J. Kelly SQL MVP
"donsql22222" <donsql22222@.discussions.microsoft.com> wrote in message
news:411989F9-EDE4-45FA-B02C-6C75DE0EBAAA@.microsoft.com...[vbcol=seagreen]
> I'm trying to solve an IO problem of when the checkpoint occurs, it writes
> a
> large amount of data onto the disk and this is causing SELECT durations to
> skyrocket at this time.
> I have around 20 servers so upgrading them to Raid Arrays would be costly.
> If I can solve the problem with a tweak, it would be worth the effort.
> I've tried FULL and SIMPLE and it has no effect on when the log gets
> checkpointed. It's always when it reaches 70% which is what BOL says so
> it's
> in line with expectations. I've got the logs truncated and they're only
> taking up approx 7MG.
> However, if I could tweak the checkpoint so that it occured say at 50%,
> then
> that amount of data being written would be less and hence less IO and
> hence
> less effect on the SELECT durations.
> The LDF and MDF are on their own physical drives.
> Thx,
> Don
>
> "Andrew J. Kelly" wrote:
|||"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:e8JtwKvTGHA.5108@.TK2MSFTNGP11.phx.gbl...
> You can adjust the recovery interval so it checkpoints more often and
> hence less at any one time. But it will happen more often. So in the end
> you will still have interruption in the long run. While you can tweak
> some there is no getting around the fact that you need proper hardware to
> handle certain situations. You can't tweak some things and I/O capacity is
> one of them. It has a certain limit and you have apparently reached it.
> The best thing to help limit the interruptions of checkpoints is a good
> caching disk controller with lots of write back cache. If you are using
> single disks you don't have much choice. You may be able to make things a
> little better with the recovery interval but it won't work magic.
> --
> Andrew J. Kelly SQL MVP
>
Also, if checkpoints negatively affect SELECT queries, then the SELECT
queries must be driving physical IO. This is probably the root of the
problem. Reduce the amount of IO generated by the queries through analyzing
and improving their performance, or add more memory.
David
size of the table? The checkpoint takes place when the log file is 70% full.
Compare these two identical files on separate servers except for rec amts.
10M rec file has checkpoint 47MG.
100M rec file has checkpoint 81MG.
Can I expect that as the tables get larger the checkpoint will become larger?
Thanks,
Don
SQL2000
"donsql22222" <donsql22222@.discussions.microsoft.com> wrote in message
news:B081E9A7-E100-4BC3-91A3-DEAEBEF72553@.microsoft.com...
> Is the quantity of data written to disk during a checkpoint, related to
> the
> size of the table? The checkpoint takes place when the log file is 70%
> full.
> Compare these two identical files on separate servers except for rec amts.
> 10M rec file has checkpoint 47MG.
> 100M rec file has checkpoint 81MG.
> Can I expect that as the tables get larger the checkpoint will become
> larger?
Checkpoint writes the dirty pages back to the database files, so the size
depends on the number of changes since the last checkpoint and how many of
those pages have been flushed by the lazywriter thread. Also the recovery
interval server parameter affects checkpoint size as well as the amount of
memory on the server.
David
|||I've changed the recovery interval to 1, 100, 1000 respectively, and saw no
change at all in the frequency of the flush or the amount of data flushed.
It always flushes when the log file is 70% full.
I'd be really interested in manipulating the amount of data stored in memory
and/or the frequency of the flush...
Any advice much appreicated.
Don
"David Browne" wrote:
> "donsql22222" <donsql22222@.discussions.microsoft.com> wrote in message
> news:B081E9A7-E100-4BC3-91A3-DEAEBEF72553@.microsoft.com...
> Checkpoint writes the dirty pages back to the database files, so the size
> depends on the number of changes since the last checkpoint and how many of
> those pages have been flushed by the lazywriter thread. Also the recovery
> interval server parameter affects checkpoint size as well as the amount of
> memory on the server.
> David
>
>
|||The 70% deal is because you have the recovery mode set to SIMPLE or you have
never done a proper FULL backup. The tran log will be truncated at 70% full
in Simple mode. This in turn forces a checkpoint to occur. But that just
means that the amount of data in the tran log is still less than SQL Server
thinks it will take to recover in 1 minute. If the log file was larger you
would probably see checkpoints before the 70% full mark. What is the reason
for wanting to change this? If checkpoints are causing issues with
performance you really need to address the source of the trouble and not try
to tweak around it. That means placing the log file on a Raid 1 or raid 10
by itself and a good amount of write back cache will help as well.
Andrew J. Kelly SQL MVP
"donsql22222" <donsql22222@.discussions.microsoft.com> wrote in message
news:EEEE73EA-9911-4302-9D2A-9E8C1083EA86@.microsoft.com...[vbcol=seagreen]
> I've changed the recovery interval to 1, 100, 1000 respectively, and saw
> no
> change at all in the frequency of the flush or the amount of data flushed.
> It always flushes when the log file is 70% full.
> I'd be really interested in manipulating the amount of data stored in
> memory
> and/or the frequency of the flush...
> Any advice much appreicated.
> Don
>
> "David Browne" wrote:
|||I'm trying to solve an IO problem of when the checkpoint occurs, it writes a
large amount of data onto the disk and this is causing SELECT durations to
skyrocket at this time.
I have around 20 servers so upgrading them to Raid Arrays would be costly.
If I can solve the problem with a tweak, it would be worth the effort.
I've tried FULL and SIMPLE and it has no effect on when the log gets
checkpointed. It's always when it reaches 70% which is what BOL says so it's
in line with expectations. I've got the logs truncated and they're only
taking up approx 7MG.
However, if I could tweak the checkpoint so that it occured say at 50%, then
that amount of data being written would be less and hence less IO and hence
less effect on the SELECT durations.
The LDF and MDF are on their own physical drives.
Thx,
Don
"Andrew J. Kelly" wrote:
> The 70% deal is because you have the recovery mode set to SIMPLE or you have
> never done a proper FULL backup. The tran log will be truncated at 70% full
> in Simple mode. This in turn forces a checkpoint to occur. But that just
> means that the amount of data in the tran log is still less than SQL Server
> thinks it will take to recover in 1 minute. If the log file was larger you
> would probably see checkpoints before the 70% full mark. What is the reason
> for wanting to change this? If checkpoints are causing issues with
> performance you really need to address the source of the trouble and not try
> to tweak around it. That means placing the log file on a Raid 1 or raid 10
> by itself and a good amount of write back cache will help as well.
> --
> Andrew J. Kelly SQL MVP
>
> "donsql22222" <donsql22222@.discussions.microsoft.com> wrote in message
> news:EEEE73EA-9911-4302-9D2A-9E8C1083EA86@.microsoft.com...
>
>
|||You can adjust the recovery interval so it checkpoints more often and hence
less at any one time. But it will happen more often. So in the end you
will still have interruption in the long run. While you can tweak some
there is no getting around the fact that you need proper hardware to handle
certain situations. You can't tweak some things and I/O capacity is one of
them. It has a certain limit and you have apparently reached it. The best
thing to help limit the interruptions of checkpoints is a good caching disk
controller with lots of write back cache. If you are using single disks you
don't have much choice. You may be able to make things a little better with
the recovery interval but it won't work magic.
Andrew J. Kelly SQL MVP
"donsql22222" <donsql22222@.discussions.microsoft.com> wrote in message
news:411989F9-EDE4-45FA-B02C-6C75DE0EBAAA@.microsoft.com...[vbcol=seagreen]
> I'm trying to solve an IO problem of when the checkpoint occurs, it writes
> a
> large amount of data onto the disk and this is causing SELECT durations to
> skyrocket at this time.
> I have around 20 servers so upgrading them to Raid Arrays would be costly.
> If I can solve the problem with a tweak, it would be worth the effort.
> I've tried FULL and SIMPLE and it has no effect on when the log gets
> checkpointed. It's always when it reaches 70% which is what BOL says so
> it's
> in line with expectations. I've got the logs truncated and they're only
> taking up approx 7MG.
> However, if I could tweak the checkpoint so that it occured say at 50%,
> then
> that amount of data being written would be less and hence less IO and
> hence
> less effect on the SELECT durations.
> The LDF and MDF are on their own physical drives.
> Thx,
> Don
>
> "Andrew J. Kelly" wrote:
|||"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:e8JtwKvTGHA.5108@.TK2MSFTNGP11.phx.gbl...
> You can adjust the recovery interval so it checkpoints more often and
> hence less at any one time. But it will happen more often. So in the end
> you will still have interruption in the long run. While you can tweak
> some there is no getting around the fact that you need proper hardware to
> handle certain situations. You can't tweak some things and I/O capacity is
> one of them. It has a certain limit and you have apparently reached it.
> The best thing to help limit the interruptions of checkpoints is a good
> caching disk controller with lots of write back cache. If you are using
> single disks you don't have much choice. You may be able to make things a
> little better with the recovery interval but it won't work magic.
> --
> Andrew J. Kelly SQL MVP
>
Also, if checkpoints negatively affect SELECT queries, then the SELECT
queries must be driving physical IO. This is probably the root of the
problem. Reduce the amount of IO generated by the queries through analyzing
and improving their performance, or add more memory.
David
CheckPoint question
Is the quantity of data written to disk during a checkpoint, related to the
size of the table? The checkpoint takes place when the log file is 70% full.
Compare these two identical files on separate servers except for rec amts.
10M rec file has checkpoint 47MG.
100M rec file has checkpoint 81MG.
Can I expect that as the tables get larger the checkpoint will become larger
?
Thanks,
Don
SQL2000"donsql22222" <donsql22222@.discussions.microsoft.com> wrote in message
news:B081E9A7-E100-4BC3-91A3-DEAEBEF72553@.microsoft.com...
> Is the quantity of data written to disk during a checkpoint, related to
> the
> size of the table? The checkpoint takes place when the log file is 70%
> full.
> Compare these two identical files on separate servers except for rec amts.
> 10M rec file has checkpoint 47MG.
> 100M rec file has checkpoint 81MG.
> Can I expect that as the tables get larger the checkpoint will become
> larger?
Checkpoint writes the dirty pages back to the database files, so the size
depends on the number of changes since the last checkpoint and how many of
those pages have been flushed by the lazywriter thread. Also the recovery
interval server parameter affects checkpoint size as well as the amount of
memory on the server.
David|||I've changed the recovery interval to 1, 100, 1000 respectively, and saw no
change at all in the frequency of the flush or the amount of data flushed.
It always flushes when the log file is 70% full.
I'd be really interested in manipulating the amount of data stored in memory
and/or the frequency of the flush...
Any advice much appreicated.
Don
"David Browne" wrote:
> "donsql22222" <donsql22222@.discussions.microsoft.com> wrote in message
> news:B081E9A7-E100-4BC3-91A3-DEAEBEF72553@.microsoft.com...
> Checkpoint writes the dirty pages back to the database files, so the size
> depends on the number of changes since the last checkpoint and how many of
> those pages have been flushed by the lazywriter thread. Also the recovery
> interval server parameter affects checkpoint size as well as the amount of
> memory on the server.
> David
>
>|||The 70% deal is because you have the recovery mode set to SIMPLE or you have
never done a proper FULL backup. The tran log will be truncated at 70% full
in Simple mode. This in turn forces a checkpoint to occur. But that just
means that the amount of data in the tran log is still less than SQL Server
thinks it will take to recover in 1 minute. If the log file was larger you
would probably see checkpoints before the 70% full mark. What is the reason
for wanting to change this? If checkpoints are causing issues with
performance you really need to address the source of the trouble and not try
to tweak around it. That means placing the log file on a Raid 1 or raid 10
by itself and a good amount of write back cache will help as well.
Andrew J. Kelly SQL MVP
"donsql22222" <donsql22222@.discussions.microsoft.com> wrote in message
news:EEEE73EA-9911-4302-9D2A-9E8C1083EA86@.microsoft.com...[vbcol=seagreen]
> I've changed the recovery interval to 1, 100, 1000 respectively, and saw
> no
> change at all in the frequency of the flush or the amount of data flushed.
> It always flushes when the log file is 70% full.
> I'd be really interested in manipulating the amount of data stored in
> memory
> and/or the frequency of the flush...
> Any advice much appreicated.
> Don
>
> "David Browne" wrote:
>|||I'm trying to solve an IO problem of when the checkpoint occurs, it writes a
large amount of data onto the disk and this is causing SELECT durations to
skyrocket at this time.
I have around 20 servers so upgrading them to Raid Arrays would be costly.
If I can solve the problem with a tweak, it would be worth the effort.
I've tried FULL and SIMPLE and it has no effect on when the log gets
checkpointed. It's always when it reaches 70% which is what BOL says so it's
in line with expectations. I've got the logs truncated and they're only
taking up approx 7MG.
However, if I could tweak the checkpoint so that it occured say at 50%, then
that amount of data being written would be less and hence less IO and hence
less effect on the SELECT durations.
The LDF and MDF are on their own physical drives.
Thx,
Don
"Andrew J. Kelly" wrote:
> The 70% deal is because you have the recovery mode set to SIMPLE or you ha
ve
> never done a proper FULL backup. The tran log will be truncated at 70% fu
ll
> in Simple mode. This in turn forces a checkpoint to occur. But that just
> means that the amount of data in the tran log is still less than SQL Serve
r
> thinks it will take to recover in 1 minute. If the log file was larger you
> would probably see checkpoints before the 70% full mark. What is the reaso
n
> for wanting to change this? If checkpoints are causing issues with
> performance you really need to address the source of the trouble and not t
ry
> to tweak around it. That means placing the log file on a Raid 1 or raid 1
0
> by itself and a good amount of write back cache will help as well.
> --
> Andrew J. Kelly SQL MVP
>
> "donsql22222" <donsql22222@.discussions.microsoft.com> wrote in message
> news:EEEE73EA-9911-4302-9D2A-9E8C1083EA86@.microsoft.com...
>
>|||You can adjust the recovery interval so it checkpoints more often and hence
less at any one time. But it will happen more often. So in the end you
will still have interruption in the long run. While you can tweak some
there is no getting around the fact that you need proper hardware to handle
certain situations. You can't tweak some things and I/O capacity is one of
them. It has a certain limit and you have apparently reached it. The best
thing to help limit the interruptions of checkpoints is a good caching disk
controller with lots of write back cache. If you are using single disks you
don't have much choice. You may be able to make things a little better with
the recovery interval but it won't work magic.
Andrew J. Kelly SQL MVP
"donsql22222" <donsql22222@.discussions.microsoft.com> wrote in message
news:411989F9-EDE4-45FA-B02C-6C75DE0EBAAA@.microsoft.com...[vbcol=seagreen]
> I'm trying to solve an IO problem of when the checkpoint occurs, it writes
> a
> large amount of data onto the disk and this is causing SELECT durations to
> skyrocket at this time.
> I have around 20 servers so upgrading them to Raid Arrays would be costly.
> If I can solve the problem with a tweak, it would be worth the effort.
> I've tried FULL and SIMPLE and it has no effect on when the log gets
> checkpointed. It's always when it reaches 70% which is what BOL says so
> it's
> in line with expectations. I've got the logs truncated and they're only
> taking up approx 7MG.
> However, if I could tweak the checkpoint so that it occured say at 50%,
> then
> that amount of data being written would be less and hence less IO and
> hence
> less effect on the SELECT durations.
> The LDF and MDF are on their own physical drives.
> Thx,
> Don
>
> "Andrew J. Kelly" wrote:
>|||"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:e8JtwKvTGHA.5108@.TK2MSFTNGP11.phx.gbl...
> You can adjust the recovery interval so it checkpoints more often and
> hence less at any one time. But it will happen more often. So in the end
> you will still have interruption in the long run. While you can tweak
> some there is no getting around the fact that you need proper hardware to
> handle certain situations. You can't tweak some things and I/O capacity is
> one of them. It has a certain limit and you have apparently reached it.
> The best thing to help limit the interruptions of checkpoints is a good
> caching disk controller with lots of write back cache. If you are using
> single disks you don't have much choice. You may be able to make things a
> little better with the recovery interval but it won't work magic.
> --
> Andrew J. Kelly SQL MVP
>
Also, if checkpoints negatively affect SELECT queries, then the SELECT
queries must be driving physical IO. This is probably the root of the
problem. Reduce the amount of IO generated by the queries through analyzing
and improving their performance, or add more memory.
David
size of the table? The checkpoint takes place when the log file is 70% full.
Compare these two identical files on separate servers except for rec amts.
10M rec file has checkpoint 47MG.
100M rec file has checkpoint 81MG.
Can I expect that as the tables get larger the checkpoint will become larger
?
Thanks,
Don
SQL2000"donsql22222" <donsql22222@.discussions.microsoft.com> wrote in message
news:B081E9A7-E100-4BC3-91A3-DEAEBEF72553@.microsoft.com...
> Is the quantity of data written to disk during a checkpoint, related to
> the
> size of the table? The checkpoint takes place when the log file is 70%
> full.
> Compare these two identical files on separate servers except for rec amts.
> 10M rec file has checkpoint 47MG.
> 100M rec file has checkpoint 81MG.
> Can I expect that as the tables get larger the checkpoint will become
> larger?
Checkpoint writes the dirty pages back to the database files, so the size
depends on the number of changes since the last checkpoint and how many of
those pages have been flushed by the lazywriter thread. Also the recovery
interval server parameter affects checkpoint size as well as the amount of
memory on the server.
David|||I've changed the recovery interval to 1, 100, 1000 respectively, and saw no
change at all in the frequency of the flush or the amount of data flushed.
It always flushes when the log file is 70% full.
I'd be really interested in manipulating the amount of data stored in memory
and/or the frequency of the flush...
Any advice much appreicated.
Don
"David Browne" wrote:
> "donsql22222" <donsql22222@.discussions.microsoft.com> wrote in message
> news:B081E9A7-E100-4BC3-91A3-DEAEBEF72553@.microsoft.com...
> Checkpoint writes the dirty pages back to the database files, so the size
> depends on the number of changes since the last checkpoint and how many of
> those pages have been flushed by the lazywriter thread. Also the recovery
> interval server parameter affects checkpoint size as well as the amount of
> memory on the server.
> David
>
>|||The 70% deal is because you have the recovery mode set to SIMPLE or you have
never done a proper FULL backup. The tran log will be truncated at 70% full
in Simple mode. This in turn forces a checkpoint to occur. But that just
means that the amount of data in the tran log is still less than SQL Server
thinks it will take to recover in 1 minute. If the log file was larger you
would probably see checkpoints before the 70% full mark. What is the reason
for wanting to change this? If checkpoints are causing issues with
performance you really need to address the source of the trouble and not try
to tweak around it. That means placing the log file on a Raid 1 or raid 10
by itself and a good amount of write back cache will help as well.
Andrew J. Kelly SQL MVP
"donsql22222" <donsql22222@.discussions.microsoft.com> wrote in message
news:EEEE73EA-9911-4302-9D2A-9E8C1083EA86@.microsoft.com...[vbcol=seagreen]
> I've changed the recovery interval to 1, 100, 1000 respectively, and saw
> no
> change at all in the frequency of the flush or the amount of data flushed.
> It always flushes when the log file is 70% full.
> I'd be really interested in manipulating the amount of data stored in
> memory
> and/or the frequency of the flush...
> Any advice much appreicated.
> Don
>
> "David Browne" wrote:
>|||I'm trying to solve an IO problem of when the checkpoint occurs, it writes a
large amount of data onto the disk and this is causing SELECT durations to
skyrocket at this time.
I have around 20 servers so upgrading them to Raid Arrays would be costly.
If I can solve the problem with a tweak, it would be worth the effort.
I've tried FULL and SIMPLE and it has no effect on when the log gets
checkpointed. It's always when it reaches 70% which is what BOL says so it's
in line with expectations. I've got the logs truncated and they're only
taking up approx 7MG.
However, if I could tweak the checkpoint so that it occured say at 50%, then
that amount of data being written would be less and hence less IO and hence
less effect on the SELECT durations.
The LDF and MDF are on their own physical drives.
Thx,
Don
"Andrew J. Kelly" wrote:
> The 70% deal is because you have the recovery mode set to SIMPLE or you ha
ve
> never done a proper FULL backup. The tran log will be truncated at 70% fu
ll
> in Simple mode. This in turn forces a checkpoint to occur. But that just
> means that the amount of data in the tran log is still less than SQL Serve
r
> thinks it will take to recover in 1 minute. If the log file was larger you
> would probably see checkpoints before the 70% full mark. What is the reaso
n
> for wanting to change this? If checkpoints are causing issues with
> performance you really need to address the source of the trouble and not t
ry
> to tweak around it. That means placing the log file on a Raid 1 or raid 1
0
> by itself and a good amount of write back cache will help as well.
> --
> Andrew J. Kelly SQL MVP
>
> "donsql22222" <donsql22222@.discussions.microsoft.com> wrote in message
> news:EEEE73EA-9911-4302-9D2A-9E8C1083EA86@.microsoft.com...
>
>|||You can adjust the recovery interval so it checkpoints more often and hence
less at any one time. But it will happen more often. So in the end you
will still have interruption in the long run. While you can tweak some
there is no getting around the fact that you need proper hardware to handle
certain situations. You can't tweak some things and I/O capacity is one of
them. It has a certain limit and you have apparently reached it. The best
thing to help limit the interruptions of checkpoints is a good caching disk
controller with lots of write back cache. If you are using single disks you
don't have much choice. You may be able to make things a little better with
the recovery interval but it won't work magic.
Andrew J. Kelly SQL MVP
"donsql22222" <donsql22222@.discussions.microsoft.com> wrote in message
news:411989F9-EDE4-45FA-B02C-6C75DE0EBAAA@.microsoft.com...[vbcol=seagreen]
> I'm trying to solve an IO problem of when the checkpoint occurs, it writes
> a
> large amount of data onto the disk and this is causing SELECT durations to
> skyrocket at this time.
> I have around 20 servers so upgrading them to Raid Arrays would be costly.
> If I can solve the problem with a tweak, it would be worth the effort.
> I've tried FULL and SIMPLE and it has no effect on when the log gets
> checkpointed. It's always when it reaches 70% which is what BOL says so
> it's
> in line with expectations. I've got the logs truncated and they're only
> taking up approx 7MG.
> However, if I could tweak the checkpoint so that it occured say at 50%,
> then
> that amount of data being written would be less and hence less IO and
> hence
> less effect on the SELECT durations.
> The LDF and MDF are on their own physical drives.
> Thx,
> Don
>
> "Andrew J. Kelly" wrote:
>|||"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:e8JtwKvTGHA.5108@.TK2MSFTNGP11.phx.gbl...
> You can adjust the recovery interval so it checkpoints more often and
> hence less at any one time. But it will happen more often. So in the end
> you will still have interruption in the long run. While you can tweak
> some there is no getting around the fact that you need proper hardware to
> handle certain situations. You can't tweak some things and I/O capacity is
> one of them. It has a certain limit and you have apparently reached it.
> The best thing to help limit the interruptions of checkpoints is a good
> caching disk controller with lots of write back cache. If you are using
> single disks you don't have much choice. You may be able to make things a
> little better with the recovery interval but it won't work magic.
> --
> Andrew J. Kelly SQL MVP
>
Also, if checkpoints negatively affect SELECT queries, then the SELECT
queries must be driving physical IO. This is probably the root of the
problem. Reduce the amount of IO generated by the queries through analyzing
and improving their performance, or add more memory.
David
CheckPoint question
Is the quantity of data written to disk during a checkpoint, related to the
size of the table? The checkpoint takes place when the log file is 70% full.
Compare these two identical files on separate servers except for rec amts.
10M rec file has checkpoint 47MG.
100M rec file has checkpoint 81MG.
Can I expect that as the tables get larger the checkpoint will become larger?
Thanks,
Don
SQL2000"donsql22222" <donsql22222@.discussions.microsoft.com> wrote in message
news:B081E9A7-E100-4BC3-91A3-DEAEBEF72553@.microsoft.com...
> Is the quantity of data written to disk during a checkpoint, related to
> the
> size of the table? The checkpoint takes place when the log file is 70%
> full.
> Compare these two identical files on separate servers except for rec amts.
> 10M rec file has checkpoint 47MG.
> 100M rec file has checkpoint 81MG.
> Can I expect that as the tables get larger the checkpoint will become
> larger?
Checkpoint writes the dirty pages back to the database files, so the size
depends on the number of changes since the last checkpoint and how many of
those pages have been flushed by the lazywriter thread. Also the recovery
interval server parameter affects checkpoint size as well as the amount of
memory on the server.
David|||I've changed the recovery interval to 1, 100, 1000 respectively, and saw no
change at all in the frequency of the flush or the amount of data flushed.
It always flushes when the log file is 70% full.
I'd be really interested in manipulating the amount of data stored in memory
and/or the frequency of the flush...
Any advice much appreicated.
Don
"David Browne" wrote:
> "donsql22222" <donsql22222@.discussions.microsoft.com> wrote in message
> news:B081E9A7-E100-4BC3-91A3-DEAEBEF72553@.microsoft.com...
> > Is the quantity of data written to disk during a checkpoint, related to
> > the
> > size of the table? The checkpoint takes place when the log file is 70%
> > full.
> >
> > Compare these two identical files on separate servers except for rec amts.
> >
> > 10M rec file has checkpoint 47MG.
> > 100M rec file has checkpoint 81MG.
> >
> > Can I expect that as the tables get larger the checkpoint will become
> > larger?
> Checkpoint writes the dirty pages back to the database files, so the size
> depends on the number of changes since the last checkpoint and how many of
> those pages have been flushed by the lazywriter thread. Also the recovery
> interval server parameter affects checkpoint size as well as the amount of
> memory on the server.
> David
>
>|||The 70% deal is because you have the recovery mode set to SIMPLE or you have
never done a proper FULL backup. The tran log will be truncated at 70% full
in Simple mode. This in turn forces a checkpoint to occur. But that just
means that the amount of data in the tran log is still less than SQL Server
thinks it will take to recover in 1 minute. If the log file was larger you
would probably see checkpoints before the 70% full mark. What is the reason
for wanting to change this? If checkpoints are causing issues with
performance you really need to address the source of the trouble and not try
to tweak around it. That means placing the log file on a Raid 1 or raid 10
by itself and a good amount of write back cache will help as well.
--
Andrew J. Kelly SQL MVP
"donsql22222" <donsql22222@.discussions.microsoft.com> wrote in message
news:EEEE73EA-9911-4302-9D2A-9E8C1083EA86@.microsoft.com...
> I've changed the recovery interval to 1, 100, 1000 respectively, and saw
> no
> change at all in the frequency of the flush or the amount of data flushed.
> It always flushes when the log file is 70% full.
> I'd be really interested in manipulating the amount of data stored in
> memory
> and/or the frequency of the flush...
> Any advice much appreicated.
> Don
>
> "David Browne" wrote:
>> "donsql22222" <donsql22222@.discussions.microsoft.com> wrote in message
>> news:B081E9A7-E100-4BC3-91A3-DEAEBEF72553@.microsoft.com...
>> > Is the quantity of data written to disk during a checkpoint, related to
>> > the
>> > size of the table? The checkpoint takes place when the log file is 70%
>> > full.
>> >
>> > Compare these two identical files on separate servers except for rec
>> > amts.
>> >
>> > 10M rec file has checkpoint 47MG.
>> > 100M rec file has checkpoint 81MG.
>> >
>> > Can I expect that as the tables get larger the checkpoint will become
>> > larger?
>> Checkpoint writes the dirty pages back to the database files, so the size
>> depends on the number of changes since the last checkpoint and how many
>> of
>> those pages have been flushed by the lazywriter thread. Also the
>> recovery
>> interval server parameter affects checkpoint size as well as the amount
>> of
>> memory on the server.
>> David
>>|||I'm trying to solve an IO problem of when the checkpoint occurs, it writes a
large amount of data onto the disk and this is causing SELECT durations to
skyrocket at this time.
I have around 20 servers so upgrading them to Raid Arrays would be costly.
If I can solve the problem with a tweak, it would be worth the effort.
I've tried FULL and SIMPLE and it has no effect on when the log gets
checkpointed. It's always when it reaches 70% which is what BOL says so it's
in line with expectations. I've got the logs truncated and they're only
taking up approx 7MG.
However, if I could tweak the checkpoint so that it occured say at 50%, then
that amount of data being written would be less and hence less IO and hence
less effect on the SELECT durations.
The LDF and MDF are on their own physical drives.
Thx,
Don
"Andrew J. Kelly" wrote:
> The 70% deal is because you have the recovery mode set to SIMPLE or you have
> never done a proper FULL backup. The tran log will be truncated at 70% full
> in Simple mode. This in turn forces a checkpoint to occur. But that just
> means that the amount of data in the tran log is still less than SQL Server
> thinks it will take to recover in 1 minute. If the log file was larger you
> would probably see checkpoints before the 70% full mark. What is the reason
> for wanting to change this? If checkpoints are causing issues with
> performance you really need to address the source of the trouble and not try
> to tweak around it. That means placing the log file on a Raid 1 or raid 10
> by itself and a good amount of write back cache will help as well.
> --
> Andrew J. Kelly SQL MVP
>
> "donsql22222" <donsql22222@.discussions.microsoft.com> wrote in message
> news:EEEE73EA-9911-4302-9D2A-9E8C1083EA86@.microsoft.com...
> > I've changed the recovery interval to 1, 100, 1000 respectively, and saw
> > no
> > change at all in the frequency of the flush or the amount of data flushed.
> > It always flushes when the log file is 70% full.
> >
> > I'd be really interested in manipulating the amount of data stored in
> > memory
> > and/or the frequency of the flush...
> >
> > Any advice much appreicated.
> >
> > Don
> >
> >
> > "David Browne" wrote:
> >
> >>
> >> "donsql22222" <donsql22222@.discussions.microsoft.com> wrote in message
> >> news:B081E9A7-E100-4BC3-91A3-DEAEBEF72553@.microsoft.com...
> >> > Is the quantity of data written to disk during a checkpoint, related to
> >> > the
> >> > size of the table? The checkpoint takes place when the log file is 70%
> >> > full.
> >> >
> >> > Compare these two identical files on separate servers except for rec
> >> > amts.
> >> >
> >> > 10M rec file has checkpoint 47MG.
> >> > 100M rec file has checkpoint 81MG.
> >> >
> >> > Can I expect that as the tables get larger the checkpoint will become
> >> > larger?
> >>
> >> Checkpoint writes the dirty pages back to the database files, so the size
> >> depends on the number of changes since the last checkpoint and how many
> >> of
> >> those pages have been flushed by the lazywriter thread. Also the
> >> recovery
> >> interval server parameter affects checkpoint size as well as the amount
> >> of
> >> memory on the server.
> >>
> >> David
> >>
> >>
> >>
>
>|||You can adjust the recovery interval so it checkpoints more often and hence
less at any one time. But it will happen more often. So in the end you
will still have interruption in the long run. While you can tweak some
there is no getting around the fact that you need proper hardware to handle
certain situations. You can't tweak some things and I/O capacity is one of
them. It has a certain limit and you have apparently reached it. The best
thing to help limit the interruptions of checkpoints is a good caching disk
controller with lots of write back cache. If you are using single disks you
don't have much choice. You may be able to make things a little better with
the recovery interval but it won't work magic.
--
Andrew J. Kelly SQL MVP
"donsql22222" <donsql22222@.discussions.microsoft.com> wrote in message
news:411989F9-EDE4-45FA-B02C-6C75DE0EBAAA@.microsoft.com...
> I'm trying to solve an IO problem of when the checkpoint occurs, it writes
> a
> large amount of data onto the disk and this is causing SELECT durations to
> skyrocket at this time.
> I have around 20 servers so upgrading them to Raid Arrays would be costly.
> If I can solve the problem with a tweak, it would be worth the effort.
> I've tried FULL and SIMPLE and it has no effect on when the log gets
> checkpointed. It's always when it reaches 70% which is what BOL says so
> it's
> in line with expectations. I've got the logs truncated and they're only
> taking up approx 7MG.
> However, if I could tweak the checkpoint so that it occured say at 50%,
> then
> that amount of data being written would be less and hence less IO and
> hence
> less effect on the SELECT durations.
> The LDF and MDF are on their own physical drives.
> Thx,
> Don
>
> "Andrew J. Kelly" wrote:
>> The 70% deal is because you have the recovery mode set to SIMPLE or you
>> have
>> never done a proper FULL backup. The tran log will be truncated at 70%
>> full
>> in Simple mode. This in turn forces a checkpoint to occur. But that just
>> means that the amount of data in the tran log is still less than SQL
>> Server
>> thinks it will take to recover in 1 minute. If the log file was larger
>> you
>> would probably see checkpoints before the 70% full mark. What is the
>> reason
>> for wanting to change this? If checkpoints are causing issues with
>> performance you really need to address the source of the trouble and not
>> try
>> to tweak around it. That means placing the log file on a Raid 1 or raid
>> 10
>> by itself and a good amount of write back cache will help as well.
>> --
>> Andrew J. Kelly SQL MVP
>>
>> "donsql22222" <donsql22222@.discussions.microsoft.com> wrote in message
>> news:EEEE73EA-9911-4302-9D2A-9E8C1083EA86@.microsoft.com...
>> > I've changed the recovery interval to 1, 100, 1000 respectively, and
>> > saw
>> > no
>> > change at all in the frequency of the flush or the amount of data
>> > flushed.
>> > It always flushes when the log file is 70% full.
>> >
>> > I'd be really interested in manipulating the amount of data stored in
>> > memory
>> > and/or the frequency of the flush...
>> >
>> > Any advice much appreicated.
>> >
>> > Don
>> >
>> >
>> > "David Browne" wrote:
>> >
>> >>
>> >> "donsql22222" <donsql22222@.discussions.microsoft.com> wrote in message
>> >> news:B081E9A7-E100-4BC3-91A3-DEAEBEF72553@.microsoft.com...
>> >> > Is the quantity of data written to disk during a checkpoint, related
>> >> > to
>> >> > the
>> >> > size of the table? The checkpoint takes place when the log file is
>> >> > 70%
>> >> > full.
>> >> >
>> >> > Compare these two identical files on separate servers except for rec
>> >> > amts.
>> >> >
>> >> > 10M rec file has checkpoint 47MG.
>> >> > 100M rec file has checkpoint 81MG.
>> >> >
>> >> > Can I expect that as the tables get larger the checkpoint will
>> >> > become
>> >> > larger?
>> >>
>> >> Checkpoint writes the dirty pages back to the database files, so the
>> >> size
>> >> depends on the number of changes since the last checkpoint and how
>> >> many
>> >> of
>> >> those pages have been flushed by the lazywriter thread. Also the
>> >> recovery
>> >> interval server parameter affects checkpoint size as well as the
>> >> amount
>> >> of
>> >> memory on the server.
>> >>
>> >> David
>> >>
>> >>
>> >>
>>|||"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:e8JtwKvTGHA.5108@.TK2MSFTNGP11.phx.gbl...
> You can adjust the recovery interval so it checkpoints more often and
> hence less at any one time. But it will happen more often. So in the end
> you will still have interruption in the long run. While you can tweak
> some there is no getting around the fact that you need proper hardware to
> handle certain situations. You can't tweak some things and I/O capacity is
> one of them. It has a certain limit and you have apparently reached it.
> The best thing to help limit the interruptions of checkpoints is a good
> caching disk controller with lots of write back cache. If you are using
> single disks you don't have much choice. You may be able to make things a
> little better with the recovery interval but it won't work magic.
> --
> Andrew J. Kelly SQL MVP
>
Also, if checkpoints negatively affect SELECT queries, then the SELECT
queries must be driving physical IO. This is probably the root of the
problem. Reduce the amount of IO generated by the queries through analyzing
and improving their performance, or add more memory.
David
size of the table? The checkpoint takes place when the log file is 70% full.
Compare these two identical files on separate servers except for rec amts.
10M rec file has checkpoint 47MG.
100M rec file has checkpoint 81MG.
Can I expect that as the tables get larger the checkpoint will become larger?
Thanks,
Don
SQL2000"donsql22222" <donsql22222@.discussions.microsoft.com> wrote in message
news:B081E9A7-E100-4BC3-91A3-DEAEBEF72553@.microsoft.com...
> Is the quantity of data written to disk during a checkpoint, related to
> the
> size of the table? The checkpoint takes place when the log file is 70%
> full.
> Compare these two identical files on separate servers except for rec amts.
> 10M rec file has checkpoint 47MG.
> 100M rec file has checkpoint 81MG.
> Can I expect that as the tables get larger the checkpoint will become
> larger?
Checkpoint writes the dirty pages back to the database files, so the size
depends on the number of changes since the last checkpoint and how many of
those pages have been flushed by the lazywriter thread. Also the recovery
interval server parameter affects checkpoint size as well as the amount of
memory on the server.
David|||I've changed the recovery interval to 1, 100, 1000 respectively, and saw no
change at all in the frequency of the flush or the amount of data flushed.
It always flushes when the log file is 70% full.
I'd be really interested in manipulating the amount of data stored in memory
and/or the frequency of the flush...
Any advice much appreicated.
Don
"David Browne" wrote:
> "donsql22222" <donsql22222@.discussions.microsoft.com> wrote in message
> news:B081E9A7-E100-4BC3-91A3-DEAEBEF72553@.microsoft.com...
> > Is the quantity of data written to disk during a checkpoint, related to
> > the
> > size of the table? The checkpoint takes place when the log file is 70%
> > full.
> >
> > Compare these two identical files on separate servers except for rec amts.
> >
> > 10M rec file has checkpoint 47MG.
> > 100M rec file has checkpoint 81MG.
> >
> > Can I expect that as the tables get larger the checkpoint will become
> > larger?
> Checkpoint writes the dirty pages back to the database files, so the size
> depends on the number of changes since the last checkpoint and how many of
> those pages have been flushed by the lazywriter thread. Also the recovery
> interval server parameter affects checkpoint size as well as the amount of
> memory on the server.
> David
>
>|||The 70% deal is because you have the recovery mode set to SIMPLE or you have
never done a proper FULL backup. The tran log will be truncated at 70% full
in Simple mode. This in turn forces a checkpoint to occur. But that just
means that the amount of data in the tran log is still less than SQL Server
thinks it will take to recover in 1 minute. If the log file was larger you
would probably see checkpoints before the 70% full mark. What is the reason
for wanting to change this? If checkpoints are causing issues with
performance you really need to address the source of the trouble and not try
to tweak around it. That means placing the log file on a Raid 1 or raid 10
by itself and a good amount of write back cache will help as well.
--
Andrew J. Kelly SQL MVP
"donsql22222" <donsql22222@.discussions.microsoft.com> wrote in message
news:EEEE73EA-9911-4302-9D2A-9E8C1083EA86@.microsoft.com...
> I've changed the recovery interval to 1, 100, 1000 respectively, and saw
> no
> change at all in the frequency of the flush or the amount of data flushed.
> It always flushes when the log file is 70% full.
> I'd be really interested in manipulating the amount of data stored in
> memory
> and/or the frequency of the flush...
> Any advice much appreicated.
> Don
>
> "David Browne" wrote:
>> "donsql22222" <donsql22222@.discussions.microsoft.com> wrote in message
>> news:B081E9A7-E100-4BC3-91A3-DEAEBEF72553@.microsoft.com...
>> > Is the quantity of data written to disk during a checkpoint, related to
>> > the
>> > size of the table? The checkpoint takes place when the log file is 70%
>> > full.
>> >
>> > Compare these two identical files on separate servers except for rec
>> > amts.
>> >
>> > 10M rec file has checkpoint 47MG.
>> > 100M rec file has checkpoint 81MG.
>> >
>> > Can I expect that as the tables get larger the checkpoint will become
>> > larger?
>> Checkpoint writes the dirty pages back to the database files, so the size
>> depends on the number of changes since the last checkpoint and how many
>> of
>> those pages have been flushed by the lazywriter thread. Also the
>> recovery
>> interval server parameter affects checkpoint size as well as the amount
>> of
>> memory on the server.
>> David
>>|||I'm trying to solve an IO problem of when the checkpoint occurs, it writes a
large amount of data onto the disk and this is causing SELECT durations to
skyrocket at this time.
I have around 20 servers so upgrading them to Raid Arrays would be costly.
If I can solve the problem with a tweak, it would be worth the effort.
I've tried FULL and SIMPLE and it has no effect on when the log gets
checkpointed. It's always when it reaches 70% which is what BOL says so it's
in line with expectations. I've got the logs truncated and they're only
taking up approx 7MG.
However, if I could tweak the checkpoint so that it occured say at 50%, then
that amount of data being written would be less and hence less IO and hence
less effect on the SELECT durations.
The LDF and MDF are on their own physical drives.
Thx,
Don
"Andrew J. Kelly" wrote:
> The 70% deal is because you have the recovery mode set to SIMPLE or you have
> never done a proper FULL backup. The tran log will be truncated at 70% full
> in Simple mode. This in turn forces a checkpoint to occur. But that just
> means that the amount of data in the tran log is still less than SQL Server
> thinks it will take to recover in 1 minute. If the log file was larger you
> would probably see checkpoints before the 70% full mark. What is the reason
> for wanting to change this? If checkpoints are causing issues with
> performance you really need to address the source of the trouble and not try
> to tweak around it. That means placing the log file on a Raid 1 or raid 10
> by itself and a good amount of write back cache will help as well.
> --
> Andrew J. Kelly SQL MVP
>
> "donsql22222" <donsql22222@.discussions.microsoft.com> wrote in message
> news:EEEE73EA-9911-4302-9D2A-9E8C1083EA86@.microsoft.com...
> > I've changed the recovery interval to 1, 100, 1000 respectively, and saw
> > no
> > change at all in the frequency of the flush or the amount of data flushed.
> > It always flushes when the log file is 70% full.
> >
> > I'd be really interested in manipulating the amount of data stored in
> > memory
> > and/or the frequency of the flush...
> >
> > Any advice much appreicated.
> >
> > Don
> >
> >
> > "David Browne" wrote:
> >
> >>
> >> "donsql22222" <donsql22222@.discussions.microsoft.com> wrote in message
> >> news:B081E9A7-E100-4BC3-91A3-DEAEBEF72553@.microsoft.com...
> >> > Is the quantity of data written to disk during a checkpoint, related to
> >> > the
> >> > size of the table? The checkpoint takes place when the log file is 70%
> >> > full.
> >> >
> >> > Compare these two identical files on separate servers except for rec
> >> > amts.
> >> >
> >> > 10M rec file has checkpoint 47MG.
> >> > 100M rec file has checkpoint 81MG.
> >> >
> >> > Can I expect that as the tables get larger the checkpoint will become
> >> > larger?
> >>
> >> Checkpoint writes the dirty pages back to the database files, so the size
> >> depends on the number of changes since the last checkpoint and how many
> >> of
> >> those pages have been flushed by the lazywriter thread. Also the
> >> recovery
> >> interval server parameter affects checkpoint size as well as the amount
> >> of
> >> memory on the server.
> >>
> >> David
> >>
> >>
> >>
>
>|||You can adjust the recovery interval so it checkpoints more often and hence
less at any one time. But it will happen more often. So in the end you
will still have interruption in the long run. While you can tweak some
there is no getting around the fact that you need proper hardware to handle
certain situations. You can't tweak some things and I/O capacity is one of
them. It has a certain limit and you have apparently reached it. The best
thing to help limit the interruptions of checkpoints is a good caching disk
controller with lots of write back cache. If you are using single disks you
don't have much choice. You may be able to make things a little better with
the recovery interval but it won't work magic.
--
Andrew J. Kelly SQL MVP
"donsql22222" <donsql22222@.discussions.microsoft.com> wrote in message
news:411989F9-EDE4-45FA-B02C-6C75DE0EBAAA@.microsoft.com...
> I'm trying to solve an IO problem of when the checkpoint occurs, it writes
> a
> large amount of data onto the disk and this is causing SELECT durations to
> skyrocket at this time.
> I have around 20 servers so upgrading them to Raid Arrays would be costly.
> If I can solve the problem with a tweak, it would be worth the effort.
> I've tried FULL and SIMPLE and it has no effect on when the log gets
> checkpointed. It's always when it reaches 70% which is what BOL says so
> it's
> in line with expectations. I've got the logs truncated and they're only
> taking up approx 7MG.
> However, if I could tweak the checkpoint so that it occured say at 50%,
> then
> that amount of data being written would be less and hence less IO and
> hence
> less effect on the SELECT durations.
> The LDF and MDF are on their own physical drives.
> Thx,
> Don
>
> "Andrew J. Kelly" wrote:
>> The 70% deal is because you have the recovery mode set to SIMPLE or you
>> have
>> never done a proper FULL backup. The tran log will be truncated at 70%
>> full
>> in Simple mode. This in turn forces a checkpoint to occur. But that just
>> means that the amount of data in the tran log is still less than SQL
>> Server
>> thinks it will take to recover in 1 minute. If the log file was larger
>> you
>> would probably see checkpoints before the 70% full mark. What is the
>> reason
>> for wanting to change this? If checkpoints are causing issues with
>> performance you really need to address the source of the trouble and not
>> try
>> to tweak around it. That means placing the log file on a Raid 1 or raid
>> 10
>> by itself and a good amount of write back cache will help as well.
>> --
>> Andrew J. Kelly SQL MVP
>>
>> "donsql22222" <donsql22222@.discussions.microsoft.com> wrote in message
>> news:EEEE73EA-9911-4302-9D2A-9E8C1083EA86@.microsoft.com...
>> > I've changed the recovery interval to 1, 100, 1000 respectively, and
>> > saw
>> > no
>> > change at all in the frequency of the flush or the amount of data
>> > flushed.
>> > It always flushes when the log file is 70% full.
>> >
>> > I'd be really interested in manipulating the amount of data stored in
>> > memory
>> > and/or the frequency of the flush...
>> >
>> > Any advice much appreicated.
>> >
>> > Don
>> >
>> >
>> > "David Browne" wrote:
>> >
>> >>
>> >> "donsql22222" <donsql22222@.discussions.microsoft.com> wrote in message
>> >> news:B081E9A7-E100-4BC3-91A3-DEAEBEF72553@.microsoft.com...
>> >> > Is the quantity of data written to disk during a checkpoint, related
>> >> > to
>> >> > the
>> >> > size of the table? The checkpoint takes place when the log file is
>> >> > 70%
>> >> > full.
>> >> >
>> >> > Compare these two identical files on separate servers except for rec
>> >> > amts.
>> >> >
>> >> > 10M rec file has checkpoint 47MG.
>> >> > 100M rec file has checkpoint 81MG.
>> >> >
>> >> > Can I expect that as the tables get larger the checkpoint will
>> >> > become
>> >> > larger?
>> >>
>> >> Checkpoint writes the dirty pages back to the database files, so the
>> >> size
>> >> depends on the number of changes since the last checkpoint and how
>> >> many
>> >> of
>> >> those pages have been flushed by the lazywriter thread. Also the
>> >> recovery
>> >> interval server parameter affects checkpoint size as well as the
>> >> amount
>> >> of
>> >> memory on the server.
>> >>
>> >> David
>> >>
>> >>
>> >>
>>|||"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:e8JtwKvTGHA.5108@.TK2MSFTNGP11.phx.gbl...
> You can adjust the recovery interval so it checkpoints more often and
> hence less at any one time. But it will happen more often. So in the end
> you will still have interruption in the long run. While you can tweak
> some there is no getting around the fact that you need proper hardware to
> handle certain situations. You can't tweak some things and I/O capacity is
> one of them. It has a certain limit and you have apparently reached it.
> The best thing to help limit the interruptions of checkpoints is a good
> caching disk controller with lots of write back cache. If you are using
> single disks you don't have much choice. You may be able to make things a
> little better with the recovery interval but it won't work magic.
> --
> Andrew J. Kelly SQL MVP
>
Also, if checkpoints negatively affect SELECT queries, then the SELECT
queries must be driving physical IO. This is probably the root of the
problem. Reduce the amount of IO generated by the queries through analyzing
and improving their performance, or add more memory.
David
Saturday, February 25, 2012
CHECKPOINT
wonder if data modified in the buffer data cache
are written to disk in the database file only at the end of the transaction
(commit/rollback) or if portion are written at each checkpoint ?
and next eventually rollback
example:
checkpoint1 checkpoint2 checkpoint3
T1 --!--!--!--commit
does data modified by T1 are partially written to disk at each checkpoint
or only when it is committed ?
In the doc i read
"A SQL Server 2000 checkpoint performs these processes in the current
database:
. Writes to the log file a record marking the start of the checkpoint.
...
Writes to disk all dirty log and data pages."
AT
--
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.551 / Virus Database: 343 - Release Date: 11/12/2003When you issue a COMMIT, the log buffer is flushed and this guarantees
modified data are permanently persisted. The associated data pages may or
may not have been written to disk at the time of the commit because these
are written asynchronously by worker threads, the lazy writer and the
checkpoint process. A checkpoint writes all dirty pages to disk so, in your
example, any data modified by T1 is written during each of the 3
checkpoints.
If the server were to crash immediately after the commit in your example,
SQL Server would start forward recovery from the log at checkpoint 3 until
the end of the log was reached and them rollback any uncommitted
transactions. The end result is that the data modified by T1 after the last
checkpoint would be in the database and no committed data are lost.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Alan" <t@.t.fr> wrote in message
news:3fe26d7c$0$22334$626a54ce@.news.free.fr...
> wonder if data modified in the buffer data cache
> are written to disk in the database file only at the end of the
transaction
> (commit/rollback) or if portion are written at each checkpoint ?
> and next eventually rollback
> example:
>
> checkpoint1 checkpoint2 checkpoint3
> T1 --!--!--!--commit
>
> does data modified by T1 are partially written to disk at each checkpoint
> or only when it is committed ?
>
> In the doc i read
> "A SQL Server 2000 checkpoint performs these processes in the current
> database:
> . Writes to the log file a record marking the start of the checkpoint.
> ...
> Writes to disk all dirty log and data pages."
>
> AT
>
> --
> Outgoing mail is certified Virus Free.
> Checked by AVG anti-virus system (http://www.grisoft.com).
> Version: 6.0.551 / Virus Database: 343 - Release Date: 11/12/2003
>|||Alan
You also posted this in .setup, where I provided an answer, although not
quite as detailed as Dan's.
In the future, please do not post the same question to multiple groups, in
order to only have one thread of responses to follow,
and so people who see your question will know it has been aswered and won't
waste time answering it again.
One comment to Dan... you say that 'any data modified by T1 is written
during EACH of the the 3 checkpoints'. This is not true.
Once the data is written at the first checkpoint, it is no longer dirty, and
will not be written again at subsequent checkpoints. As you say,
only the dirty data is written to disk, but the process of writing to disk
during checkpoint makes that data no longer dirty.
--
HTH
--
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com
"Dan Guzman" <danguzman@.nospam-earthlink.net> wrote in message
news:eypIt4exDHA.4060@.TK2MSFTNGP11.phx.gbl...
> When you issue a COMMIT, the log buffer is flushed and this guarantees
> modified data are permanently persisted. The associated data pages may or
> may not have been written to disk at the time of the commit because these
> are written asynchronously by worker threads, the lazy writer and the
> checkpoint process. A checkpoint writes all dirty pages to disk so, in
your
> example, any data modified by T1 is written during each of the 3
> checkpoints.
> If the server were to crash immediately after the commit in your example,
> SQL Server would start forward recovery from the log at checkpoint 3 until
> the end of the log was reached and them rollback any uncommitted
> transactions. The end result is that the data modified by T1 after the
last
> checkpoint would be in the database and no committed data are lost.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
>
> "Alan" <t@.t.fr> wrote in message
> news:3fe26d7c$0$22334$626a54ce@.news.free.fr...
> > wonder if data modified in the buffer data cache
> > are written to disk in the database file only at the end of the
> transaction
> > (commit/rollback) or if portion are written at each checkpoint ?
> > and next eventually rollback
> >
> > example:
> >
> >
> > checkpoint1 checkpoint2 checkpoint3
> >
> > T1 --!--!--!--commit
> >
> >
> >
> > does data modified by T1 are partially written to disk at each
checkpoint
> > or only when it is committed ?
> >
> >
> > In the doc i read
> > "A SQL Server 2000 checkpoint performs these processes in the current
> > database:
> > . Writes to the log file a record marking the start of the checkpoint.
> > ...
> >
> > Writes to disk all dirty log and data pages."
> >
> >
> >
> > AT
> >
> >
> >
> > --
> > Outgoing mail is certified Virus Free.
> > Checked by AVG anti-virus system (http://www.grisoft.com).
> > Version: 6.0.551 / Virus Database: 343 - Release Date: 11/12/2003
> >
> >
>|||> Once the data is written at the first checkpoint, it is no longer dirty,
and
> will not be written again at subsequent checkpoints. As you say,
> only the dirty data is written to disk, but the process of writing to disk
> during checkpoint makes that data no longer dirty.
Thanks for the clarification, Kalen. I should have made that point clearer
in my response.
--
Dan Guzman
SQL Server MVP
"Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
news:%239BALRfxDHA.2064@.TK2MSFTNGP10.phx.gbl...
> Alan
> You also posted this in .setup, where I provided an answer, although not
> quite as detailed as Dan's.
> In the future, please do not post the same question to multiple groups, in
> order to only have one thread of responses to follow,
> and so people who see your question will know it has been aswered and
won't
> waste time answering it again.
> One comment to Dan... you say that 'any data modified by T1 is written
> during EACH of the the 3 checkpoints'. This is not true.
> Once the data is written at the first checkpoint, it is no longer dirty,
and
> will not be written again at subsequent checkpoints. As you say,
> only the dirty data is written to disk, but the process of writing to disk
> during checkpoint makes that data no longer dirty.
> --
> HTH
> --
> Kalen Delaney
> SQL Server MVP
> www.SolidQualityLearning.com
>
> "Dan Guzman" <danguzman@.nospam-earthlink.net> wrote in message
> news:eypIt4exDHA.4060@.TK2MSFTNGP11.phx.gbl...
> > When you issue a COMMIT, the log buffer is flushed and this guarantees
> > modified data are permanently persisted. The associated data pages may
or
> > may not have been written to disk at the time of the commit because
these
> > are written asynchronously by worker threads, the lazy writer and the
> > checkpoint process. A checkpoint writes all dirty pages to disk so, in
> your
> > example, any data modified by T1 is written during each of the 3
> > checkpoints.
> >
> > If the server were to crash immediately after the commit in your
example,
> > SQL Server would start forward recovery from the log at checkpoint 3
until
> > the end of the log was reached and them rollback any uncommitted
> > transactions. The end result is that the data modified by T1 after the
> last
> > checkpoint would be in the database and no committed data are lost.
> >
> > --
> > Hope this helps.
> >
> > Dan Guzman
> > SQL Server MVP
> >
> >
> > "Alan" <t@.t.fr> wrote in message
> > news:3fe26d7c$0$22334$626a54ce@.news.free.fr...
> > > wonder if data modified in the buffer data cache
> > > are written to disk in the database file only at the end of the
> > transaction
> > > (commit/rollback) or if portion are written at each checkpoint ?
> > > and next eventually rollback
> > >
> > > example:
> > >
> > >
> > > checkpoint1 checkpoint2 checkpoint3
> > >
> > > T1 --!--!--!--commit
> > >
> > >
> > >
> > > does data modified by T1 are partially written to disk at each
> checkpoint
> > > or only when it is committed ?
> > >
> > >
> > > In the doc i read
> > > "A SQL Server 2000 checkpoint performs these processes in the current
> > > database:
> > > . Writes to the log file a record marking the start of the checkpoint.
> > > ...
> > >
> > > Writes to disk all dirty log and data pages."
> > >
> > >
> > >
> > > AT
> > >
> > >
> > >
> > > --
> > > Outgoing mail is certified Virus Free.
> > > Checked by AVG anti-virus system (http://www.grisoft.com).
> > > Version: 6.0.551 / Virus Database: 343 - Release Date: 11/12/2003
> > >
> > >
> >
> >
>
are written to disk in the database file only at the end of the transaction
(commit/rollback) or if portion are written at each checkpoint ?
and next eventually rollback
example:
checkpoint1 checkpoint2 checkpoint3
T1 --!--!--!--commit
does data modified by T1 are partially written to disk at each checkpoint
or only when it is committed ?
In the doc i read
"A SQL Server 2000 checkpoint performs these processes in the current
database:
. Writes to the log file a record marking the start of the checkpoint.
...
Writes to disk all dirty log and data pages."
AT
--
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.551 / Virus Database: 343 - Release Date: 11/12/2003When you issue a COMMIT, the log buffer is flushed and this guarantees
modified data are permanently persisted. The associated data pages may or
may not have been written to disk at the time of the commit because these
are written asynchronously by worker threads, the lazy writer and the
checkpoint process. A checkpoint writes all dirty pages to disk so, in your
example, any data modified by T1 is written during each of the 3
checkpoints.
If the server were to crash immediately after the commit in your example,
SQL Server would start forward recovery from the log at checkpoint 3 until
the end of the log was reached and them rollback any uncommitted
transactions. The end result is that the data modified by T1 after the last
checkpoint would be in the database and no committed data are lost.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Alan" <t@.t.fr> wrote in message
news:3fe26d7c$0$22334$626a54ce@.news.free.fr...
> wonder if data modified in the buffer data cache
> are written to disk in the database file only at the end of the
transaction
> (commit/rollback) or if portion are written at each checkpoint ?
> and next eventually rollback
> example:
>
> checkpoint1 checkpoint2 checkpoint3
> T1 --!--!--!--commit
>
> does data modified by T1 are partially written to disk at each checkpoint
> or only when it is committed ?
>
> In the doc i read
> "A SQL Server 2000 checkpoint performs these processes in the current
> database:
> . Writes to the log file a record marking the start of the checkpoint.
> ...
> Writes to disk all dirty log and data pages."
>
> AT
>
> --
> Outgoing mail is certified Virus Free.
> Checked by AVG anti-virus system (http://www.grisoft.com).
> Version: 6.0.551 / Virus Database: 343 - Release Date: 11/12/2003
>|||Alan
You also posted this in .setup, where I provided an answer, although not
quite as detailed as Dan's.
In the future, please do not post the same question to multiple groups, in
order to only have one thread of responses to follow,
and so people who see your question will know it has been aswered and won't
waste time answering it again.
One comment to Dan... you say that 'any data modified by T1 is written
during EACH of the the 3 checkpoints'. This is not true.
Once the data is written at the first checkpoint, it is no longer dirty, and
will not be written again at subsequent checkpoints. As you say,
only the dirty data is written to disk, but the process of writing to disk
during checkpoint makes that data no longer dirty.
--
HTH
--
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com
"Dan Guzman" <danguzman@.nospam-earthlink.net> wrote in message
news:eypIt4exDHA.4060@.TK2MSFTNGP11.phx.gbl...
> When you issue a COMMIT, the log buffer is flushed and this guarantees
> modified data are permanently persisted. The associated data pages may or
> may not have been written to disk at the time of the commit because these
> are written asynchronously by worker threads, the lazy writer and the
> checkpoint process. A checkpoint writes all dirty pages to disk so, in
your
> example, any data modified by T1 is written during each of the 3
> checkpoints.
> If the server were to crash immediately after the commit in your example,
> SQL Server would start forward recovery from the log at checkpoint 3 until
> the end of the log was reached and them rollback any uncommitted
> transactions. The end result is that the data modified by T1 after the
last
> checkpoint would be in the database and no committed data are lost.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
>
> "Alan" <t@.t.fr> wrote in message
> news:3fe26d7c$0$22334$626a54ce@.news.free.fr...
> > wonder if data modified in the buffer data cache
> > are written to disk in the database file only at the end of the
> transaction
> > (commit/rollback) or if portion are written at each checkpoint ?
> > and next eventually rollback
> >
> > example:
> >
> >
> > checkpoint1 checkpoint2 checkpoint3
> >
> > T1 --!--!--!--commit
> >
> >
> >
> > does data modified by T1 are partially written to disk at each
checkpoint
> > or only when it is committed ?
> >
> >
> > In the doc i read
> > "A SQL Server 2000 checkpoint performs these processes in the current
> > database:
> > . Writes to the log file a record marking the start of the checkpoint.
> > ...
> >
> > Writes to disk all dirty log and data pages."
> >
> >
> >
> > AT
> >
> >
> >
> > --
> > Outgoing mail is certified Virus Free.
> > Checked by AVG anti-virus system (http://www.grisoft.com).
> > Version: 6.0.551 / Virus Database: 343 - Release Date: 11/12/2003
> >
> >
>|||> Once the data is written at the first checkpoint, it is no longer dirty,
and
> will not be written again at subsequent checkpoints. As you say,
> only the dirty data is written to disk, but the process of writing to disk
> during checkpoint makes that data no longer dirty.
Thanks for the clarification, Kalen. I should have made that point clearer
in my response.
--
Dan Guzman
SQL Server MVP
"Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
news:%239BALRfxDHA.2064@.TK2MSFTNGP10.phx.gbl...
> Alan
> You also posted this in .setup, where I provided an answer, although not
> quite as detailed as Dan's.
> In the future, please do not post the same question to multiple groups, in
> order to only have one thread of responses to follow,
> and so people who see your question will know it has been aswered and
won't
> waste time answering it again.
> One comment to Dan... you say that 'any data modified by T1 is written
> during EACH of the the 3 checkpoints'. This is not true.
> Once the data is written at the first checkpoint, it is no longer dirty,
and
> will not be written again at subsequent checkpoints. As you say,
> only the dirty data is written to disk, but the process of writing to disk
> during checkpoint makes that data no longer dirty.
> --
> HTH
> --
> Kalen Delaney
> SQL Server MVP
> www.SolidQualityLearning.com
>
> "Dan Guzman" <danguzman@.nospam-earthlink.net> wrote in message
> news:eypIt4exDHA.4060@.TK2MSFTNGP11.phx.gbl...
> > When you issue a COMMIT, the log buffer is flushed and this guarantees
> > modified data are permanently persisted. The associated data pages may
or
> > may not have been written to disk at the time of the commit because
these
> > are written asynchronously by worker threads, the lazy writer and the
> > checkpoint process. A checkpoint writes all dirty pages to disk so, in
> your
> > example, any data modified by T1 is written during each of the 3
> > checkpoints.
> >
> > If the server were to crash immediately after the commit in your
example,
> > SQL Server would start forward recovery from the log at checkpoint 3
until
> > the end of the log was reached and them rollback any uncommitted
> > transactions. The end result is that the data modified by T1 after the
> last
> > checkpoint would be in the database and no committed data are lost.
> >
> > --
> > Hope this helps.
> >
> > Dan Guzman
> > SQL Server MVP
> >
> >
> > "Alan" <t@.t.fr> wrote in message
> > news:3fe26d7c$0$22334$626a54ce@.news.free.fr...
> > > wonder if data modified in the buffer data cache
> > > are written to disk in the database file only at the end of the
> > transaction
> > > (commit/rollback) or if portion are written at each checkpoint ?
> > > and next eventually rollback
> > >
> > > example:
> > >
> > >
> > > checkpoint1 checkpoint2 checkpoint3
> > >
> > > T1 --!--!--!--commit
> > >
> > >
> > >
> > > does data modified by T1 are partially written to disk at each
> checkpoint
> > > or only when it is committed ?
> > >
> > >
> > > In the doc i read
> > > "A SQL Server 2000 checkpoint performs these processes in the current
> > > database:
> > > . Writes to the log file a record marking the start of the checkpoint.
> > > ...
> > >
> > > Writes to disk all dirty log and data pages."
> > >
> > >
> > >
> > > AT
> > >
> > >
> > >
> > > --
> > > Outgoing mail is certified Virus Free.
> > > Checked by AVG anti-virus system (http://www.grisoft.com).
> > > Version: 6.0.551 / Virus Database: 343 - Release Date: 11/12/2003
> > >
> > >
> >
> >
>
Thursday, February 16, 2012
Checking for free disk space and getting mail when it falls below a certain limit
Hello
I have a script which checks the disk space and when it falls a certain size , it mails the dba mail box.
I would like to know how I can change it , as a percentage calculation.
For example when the free space is less than 20% of the total space on the drive I should be receiving a mail.
The script I have is :
declare @.MB_Free int
create table #FreeSpace(
Drive char(1),
MB_Free int)
insert into #FreeSpace exec xp_fixeddrives
-- Free Space on F drive Less than Threshold
if @.MB_Free < 4096
exec master.dbo.xp_sendmail
@.recipients ='dvaddi@.domain.edu',
@.subject ='SERVER X - Fresh Space Issue on D Drive',
@.message = 'Free space on D Drive
has dropped below 2 gig'
drop table #freespace
Thanks
Hi Vaddi -
Why not use the Alerts feature in Performance Monitor? The Logical Disk Performance Object has a Counter for % Free Space and you can select which drive letter you'd like to monitor. Once the limit is reached, you can have it email you using a WSH script.
HTH...
checking for free disk space and getting mail , when falls below a certain limit
Hello
I have a script which checks the disk space and when it falls a certain size , it mails the dba mail box.
I would like to know how I can change it , as a percentage calculation.
For example when the free space is less than 20% of the total space on the drive I should be receiving a mail.
The script I have is :
declare @.MB_Free int
create table #FreeSpace(
Drive char(1),
MB_Free int)
insert into #FreeSpace exec xp_fixeddrives
-- Free Space on F drive Less than Threshold
if @.MB_Free < 4096
exec master.dbo.xp_sendmail
@.recipients ='dvaddi@.domain.edu',
@.subject ='SERVER X - Fresh Space Issue on D Drive',
@.message = 'Free space on D Drive
has dropped below 2 gig'
drop table #freespace
ThanksHere's what I use:
set nocount on
declare @.MB_Threshold int
set @.MB_Threshold = 102400
declare @.From varchar(500)
declare @.Subject varchar(500)
declare @.Message varchar(500)
create table #FreeSpace(Drive char(1), MB_Free int)
insert into #FreeSpace exec master..xp_fixeddrives
select @.Message = isnull(@.Message + ', ', 'The following drives have dropped below ' + cast(@.MB_Threshold as varchar(10)) + ' MB free space: ') + Drive
from #FreeSpace
where MB_Free < @.MB_Threshold
set @.From = @.@.ServerName
set @.Subject = 'Drive space warning!'
if len(@.Message) > 0
begin
exec master.dbo.xp_smtp_sendmail
@.SERVER = 'exchange.foobar.corp',
@.FROM = @.From,
@.TO = N'blindman@.dbforums.com',
@.SUBJECT = @.Subject,
@.MESSAGE = @.Message
end
drop table #FreeSpace
go
I have a script which checks the disk space and when it falls a certain size , it mails the dba mail box.
I would like to know how I can change it , as a percentage calculation.
For example when the free space is less than 20% of the total space on the drive I should be receiving a mail.
The script I have is :
declare @.MB_Free int
create table #FreeSpace(
Drive char(1),
MB_Free int)
insert into #FreeSpace exec xp_fixeddrives
-- Free Space on F drive Less than Threshold
if @.MB_Free < 4096
exec master.dbo.xp_sendmail
@.recipients ='dvaddi@.domain.edu',
@.subject ='SERVER X - Fresh Space Issue on D Drive',
@.message = 'Free space on D Drive
has dropped below 2 gig'
drop table #freespace
ThanksHere's what I use:
set nocount on
declare @.MB_Threshold int
set @.MB_Threshold = 102400
declare @.From varchar(500)
declare @.Subject varchar(500)
declare @.Message varchar(500)
create table #FreeSpace(Drive char(1), MB_Free int)
insert into #FreeSpace exec master..xp_fixeddrives
select @.Message = isnull(@.Message + ', ', 'The following drives have dropped below ' + cast(@.MB_Threshold as varchar(10)) + ' MB free space: ') + Drive
from #FreeSpace
where MB_Free < @.MB_Threshold
set @.From = @.@.ServerName
set @.Subject = 'Drive space warning!'
if len(@.Message) > 0
begin
exec master.dbo.xp_smtp_sendmail
@.SERVER = 'exchange.foobar.corp',
@.FROM = @.From,
@.TO = N'blindman@.dbforums.com',
@.SUBJECT = @.Subject,
@.MESSAGE = @.Message
end
drop table #FreeSpace
go
Tuesday, February 14, 2012
Checking backup file questions
Hi All,
I have several questions about checking backup file :
a. When SQL Server receive a command :
BACKUP DATABASE test to DISK = '\\pc1\netdb\BTEST',
does it verify BTEST file before ending the job ?
b. Is there any special command for checking / verifying
a backup file ?
c. If I send a restore command using a file infected
by a virus, what action will SQL Server take ?
Thanks in advance
John S
*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!> a. When SQL Server receive a command :
> BACKUP DATABASE test to DISK = '\\pc1\netdb\BTEST',
> does it verify BTEST file before ending the job ?
No.
> b. Is there any special command for checking / verifying
> a backup file ?
You can use RESTORE VERIFYONLY to ensure the backup file format is valid
like the example below. However, this does only cursory validation. The
only way to be certain the backup is valid is to perform a test restore.
RESTORE VERIFYONLY
FROM DISK = '\\pc1\netdb\BTEST'
> c. If I send a restore command using a file infected
> by a virus, what action will SQL Server take ?
SQL Server will try to perform the restore but there is no telling what may
happen if the backup file was tampered with.
--
Hope this helps.
Dan Guzman
SQL Server MVP
Checking a database for physical damage
Hi,
Today I had one disk of my server array crashed, it is on raid 5 with
hot spare disks.
SQL Server reported some errors on event logger but apparently the hot
spare disks were used correctly since it is working right now.
So far is fine, but just in case I ran this command:
DBCC CHECKDB (MyProd) WITH ALL_ERRORMSGS
And it showed no errors.
Are there any other commands that I can run to check for integrity in a
database in a case like this?
If the CHECKDB was clean, you are in good shape. You might also include
DBCC CHECKCATALOG as part of your scheduled integrity checks but it unlikely
that a hardware error will introduce catalog problems.
You mention SQL Server errors but SQL Server should be oblivious to the
problem, assuming the controller gracefully handles such errors. If the
errors are due to the I/O subsystem become sluggish during the rebuild,
check to see if your array controller allows you to configure the rebuild
priority.
Hope this helps.
Dan Guzman
SQL Server MVP
<craigkenisston@.hotmail.com> wrote in message
news:1165648156.056709.236200@.f1g2000cwa.googlegro ups.com...
> Hi,
> Today I had one disk of my server array crashed, it is on raid 5 with
> hot spare disks.
> SQL Server reported some errors on event logger but apparently the hot
> spare disks were used correctly since it is working right now.
> So far is fine, but just in case I ran this command:
> DBCC CHECKDB (MyProd) WITH ALL_ERRORMSGS
> And it showed no errors.
> Are there any other commands that I can run to check for integrity in a
> database in a case like this?
>
|||Dan,
Thank for your comment. I did not expect to have SQL Server errors
either. But they were.
I guess as you suggest, the problem went during the rebuild, not
directly because the failure.
The errors I had were:
18052 :
Error: 3624, Severity: 20, State: 1.
And this:
17066 :
SQL Server Assertion: File:
<p:\sql\ntdbms\storeng\drs\include\record.inl>, line=1447
Failed Assertion = 'm_SizeRec > 0 && m_SizeRec <= MAXDATAROW'.
I also had other errores, but the only message they have is about
telling "The description for Event ID ( XXXX ) in Source ( MSSQLSERVER
) cannot be found. "
Regards,
Dan Guzman wrote:[vbcol=seagreen]
> If the CHECKDB was clean, you are in good shape. You might also include
> DBCC CHECKCATALOG as part of your scheduled integrity checks but it unlikely
> that a hardware error will introduce catalog problems.
> You mention SQL Server errors but SQL Server should be oblivious to the
> problem, assuming the controller gracefully handles such errors. If the
> errors are due to the I/O subsystem become sluggish during the rebuild,
> check to see if your array controller allows you to configure the rebuild
> priority.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> <craigkenisston@.hotmail.com> wrote in message
> news:1165648156.056709.236200@.f1g2000cwa.googlegro ups.com...
|||The assertion error indicates garbage was returned to SQL Server. I find it
strange that the controller would return bad data during the rebuild. Even
during the rebuild, the controller ought to be able to reconstruct the
original data and return it to SQL, albeit more slowly than a healthy array.
Anyway, I'm glad your DBCCs are clean and you are out of the woods.
Hope this helps.
Dan Guzman
SQL Server MVP
<craigkenisston@.hotmail.com> wrote in message
news:1165681327.852727.312450@.f1g2000cwa.googlegro ups.com...
> Dan,
> Thank for your comment. I did not expect to have SQL Server errors
> either. But they were.
> I guess as you suggest, the problem went during the rebuild, not
> directly because the failure.
> The errors I had were:
> 18052 :
> Error: 3624, Severity: 20, State: 1.
> And this:
> 17066 :
> SQL Server Assertion: File:
> <p:\sql\ntdbms\storeng\drs\include\record.inl>, line=1447
> Failed Assertion = 'm_SizeRec > 0 && m_SizeRec <= MAXDATAROW'.
>
> I also had other errores, but the only message they have is about
> telling "The description for Event ID ( XXXX ) in Source ( MSSQLSERVER
> ) cannot be found. "
>
> Regards,
>
> Dan Guzman wrote:
>
Today I had one disk of my server array crashed, it is on raid 5 with
hot spare disks.
SQL Server reported some errors on event logger but apparently the hot
spare disks were used correctly since it is working right now.
So far is fine, but just in case I ran this command:
DBCC CHECKDB (MyProd) WITH ALL_ERRORMSGS
And it showed no errors.
Are there any other commands that I can run to check for integrity in a
database in a case like this?
If the CHECKDB was clean, you are in good shape. You might also include
DBCC CHECKCATALOG as part of your scheduled integrity checks but it unlikely
that a hardware error will introduce catalog problems.
You mention SQL Server errors but SQL Server should be oblivious to the
problem, assuming the controller gracefully handles such errors. If the
errors are due to the I/O subsystem become sluggish during the rebuild,
check to see if your array controller allows you to configure the rebuild
priority.
Hope this helps.
Dan Guzman
SQL Server MVP
<craigkenisston@.hotmail.com> wrote in message
news:1165648156.056709.236200@.f1g2000cwa.googlegro ups.com...
> Hi,
> Today I had one disk of my server array crashed, it is on raid 5 with
> hot spare disks.
> SQL Server reported some errors on event logger but apparently the hot
> spare disks were used correctly since it is working right now.
> So far is fine, but just in case I ran this command:
> DBCC CHECKDB (MyProd) WITH ALL_ERRORMSGS
> And it showed no errors.
> Are there any other commands that I can run to check for integrity in a
> database in a case like this?
>
|||Dan,
Thank for your comment. I did not expect to have SQL Server errors
either. But they were.
I guess as you suggest, the problem went during the rebuild, not
directly because the failure.
The errors I had were:
18052 :
Error: 3624, Severity: 20, State: 1.
And this:
17066 :
SQL Server Assertion: File:
<p:\sql\ntdbms\storeng\drs\include\record.inl>, line=1447
Failed Assertion = 'm_SizeRec > 0 && m_SizeRec <= MAXDATAROW'.
I also had other errores, but the only message they have is about
telling "The description for Event ID ( XXXX ) in Source ( MSSQLSERVER
) cannot be found. "
Regards,
Dan Guzman wrote:[vbcol=seagreen]
> If the CHECKDB was clean, you are in good shape. You might also include
> DBCC CHECKCATALOG as part of your scheduled integrity checks but it unlikely
> that a hardware error will introduce catalog problems.
> You mention SQL Server errors but SQL Server should be oblivious to the
> problem, assuming the controller gracefully handles such errors. If the
> errors are due to the I/O subsystem become sluggish during the rebuild,
> check to see if your array controller allows you to configure the rebuild
> priority.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> <craigkenisston@.hotmail.com> wrote in message
> news:1165648156.056709.236200@.f1g2000cwa.googlegro ups.com...
|||The assertion error indicates garbage was returned to SQL Server. I find it
strange that the controller would return bad data during the rebuild. Even
during the rebuild, the controller ought to be able to reconstruct the
original data and return it to SQL, albeit more slowly than a healthy array.
Anyway, I'm glad your DBCCs are clean and you are out of the woods.
Hope this helps.
Dan Guzman
SQL Server MVP
<craigkenisston@.hotmail.com> wrote in message
news:1165681327.852727.312450@.f1g2000cwa.googlegro ups.com...
> Dan,
> Thank for your comment. I did not expect to have SQL Server errors
> either. But they were.
> I guess as you suggest, the problem went during the rebuild, not
> directly because the failure.
> The errors I had were:
> 18052 :
> Error: 3624, Severity: 20, State: 1.
> And this:
> 17066 :
> SQL Server Assertion: File:
> <p:\sql\ntdbms\storeng\drs\include\record.inl>, line=1447
> Failed Assertion = 'm_SizeRec > 0 && m_SizeRec <= MAXDATAROW'.
>
> I also had other errores, but the only message they have is about
> telling "The description for Event ID ( XXXX ) in Source ( MSSQLSERVER
> ) cannot be found. "
>
> Regards,
>
> Dan Guzman wrote:
>
Checking a database for physical damage
Hi,
Today I had one disk of my server array crashed, it is on raid 5 with
hot spare disks.
SQL Server reported some errors on event logger but apparently the hot
spare disks were used correctly since it is working right now.
So far is fine, but just in case I ran this command:
DBCC CHECKDB (MyProd) WITH ALL_ERRORMSGS
And it showed no errors.
Are there any other commands that I can run to check for integrity in a
database in a case like this?If the CHECKDB was clean, you are in good shape. You might also include
DBCC CHECKCATALOG as part of your scheduled integrity checks but it unlikely
that a hardware error will introduce catalog problems.
You mention SQL Server errors but SQL Server should be oblivious to the
problem, assuming the controller gracefully handles such errors. If the
errors are due to the I/O subsystem become sluggish during the rebuild,
check to see if your array controller allows you to configure the rebuild
priority.
--
Hope this helps.
Dan Guzman
SQL Server MVP
<craigkenisston@.hotmail.com> wrote in message
news:1165648156.056709.236200@.f1g2000cwa.googlegroups.com...
> Hi,
> Today I had one disk of my server array crashed, it is on raid 5 with
> hot spare disks.
> SQL Server reported some errors on event logger but apparently the hot
> spare disks were used correctly since it is working right now.
> So far is fine, but just in case I ran this command:
> DBCC CHECKDB (MyProd) WITH ALL_ERRORMSGS
> And it showed no errors.
> Are there any other commands that I can run to check for integrity in a
> database in a case like this?
>|||Dan,
Thank for your comment. I did not expect to have SQL Server errors
either. But they were.
I guess as you suggest, the problem went during the rebuild, not
directly because the failure.
The errors I had were:
18052 :
Error: 3624, Severity: 20, State: 1.
And this:
17066 :
SQL Server Assertion: File:
<p:\sql\ntdbms\storeng\drs\include\record.inl>, line=1447
Failed Assertion = 'm_SizeRec > 0 && m_SizeRec <= MAXDATAROW'.
I also had other errores, but the only message they have is about
telling "The description for Event ID ( XXXX ) in Source ( MSSQLSERVER
) cannot be found. "
Regards,
Dan Guzman wrote:
> If the CHECKDB was clean, you are in good shape. You might also include
> DBCC CHECKCATALOG as part of your scheduled integrity checks but it unlikely
> that a hardware error will introduce catalog problems.
> You mention SQL Server errors but SQL Server should be oblivious to the
> problem, assuming the controller gracefully handles such errors. If the
> errors are due to the I/O subsystem become sluggish during the rebuild,
> check to see if your array controller allows you to configure the rebuild
> priority.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> <craigkenisston@.hotmail.com> wrote in message
> news:1165648156.056709.236200@.f1g2000cwa.googlegroups.com...
> > Hi,
> >
> > Today I had one disk of my server array crashed, it is on raid 5 with
> > hot spare disks.
> > SQL Server reported some errors on event logger but apparently the hot
> > spare disks were used correctly since it is working right now.
> >
> > So far is fine, but just in case I ran this command:
> > DBCC CHECKDB (MyProd) WITH ALL_ERRORMSGS
> >
> > And it showed no errors.
> > Are there any other commands that I can run to check for integrity in a
> > database in a case like this?
> >|||The assertion error indicates garbage was returned to SQL Server. I find it
strange that the controller would return bad data during the rebuild. Even
during the rebuild, the controller ought to be able to reconstruct the
original data and return it to SQL, albeit more slowly than a healthy array.
Anyway, I'm glad your DBCCs are clean and you are out of the woods.
--
Hope this helps.
Dan Guzman
SQL Server MVP
<craigkenisston@.hotmail.com> wrote in message
news:1165681327.852727.312450@.f1g2000cwa.googlegroups.com...
> Dan,
> Thank for your comment. I did not expect to have SQL Server errors
> either. But they were.
> I guess as you suggest, the problem went during the rebuild, not
> directly because the failure.
> The errors I had were:
> 18052 :
> Error: 3624, Severity: 20, State: 1.
> And this:
> 17066 :
> SQL Server Assertion: File:
> <p:\sql\ntdbms\storeng\drs\include\record.inl>, line=1447
> Failed Assertion = 'm_SizeRec > 0 && m_SizeRec <= MAXDATAROW'.
>
> I also had other errores, but the only message they have is about
> telling "The description for Event ID ( XXXX ) in Source ( MSSQLSERVER
> ) cannot be found. "
>
> Regards,
>
> Dan Guzman wrote:
>> If the CHECKDB was clean, you are in good shape. You might also include
>> DBCC CHECKCATALOG as part of your scheduled integrity checks but it
>> unlikely
>> that a hardware error will introduce catalog problems.
>> You mention SQL Server errors but SQL Server should be oblivious to the
>> problem, assuming the controller gracefully handles such errors. If the
>> errors are due to the I/O subsystem become sluggish during the rebuild,
>> check to see if your array controller allows you to configure the rebuild
>> priority.
>> --
>> Hope this helps.
>> Dan Guzman
>> SQL Server MVP
>> <craigkenisston@.hotmail.com> wrote in message
>> news:1165648156.056709.236200@.f1g2000cwa.googlegroups.com...
>> > Hi,
>> >
>> > Today I had one disk of my server array crashed, it is on raid 5 with
>> > hot spare disks.
>> > SQL Server reported some errors on event logger but apparently the hot
>> > spare disks were used correctly since it is working right now.
>> >
>> > So far is fine, but just in case I ran this command:
>> > DBCC CHECKDB (MyProd) WITH ALL_ERRORMSGS
>> >
>> > And it showed no errors.
>> > Are there any other commands that I can run to check for integrity in a
>> > database in a case like this?
>> >
>
Today I had one disk of my server array crashed, it is on raid 5 with
hot spare disks.
SQL Server reported some errors on event logger but apparently the hot
spare disks were used correctly since it is working right now.
So far is fine, but just in case I ran this command:
DBCC CHECKDB (MyProd) WITH ALL_ERRORMSGS
And it showed no errors.
Are there any other commands that I can run to check for integrity in a
database in a case like this?If the CHECKDB was clean, you are in good shape. You might also include
DBCC CHECKCATALOG as part of your scheduled integrity checks but it unlikely
that a hardware error will introduce catalog problems.
You mention SQL Server errors but SQL Server should be oblivious to the
problem, assuming the controller gracefully handles such errors. If the
errors are due to the I/O subsystem become sluggish during the rebuild,
check to see if your array controller allows you to configure the rebuild
priority.
--
Hope this helps.
Dan Guzman
SQL Server MVP
<craigkenisston@.hotmail.com> wrote in message
news:1165648156.056709.236200@.f1g2000cwa.googlegroups.com...
> Hi,
> Today I had one disk of my server array crashed, it is on raid 5 with
> hot spare disks.
> SQL Server reported some errors on event logger but apparently the hot
> spare disks were used correctly since it is working right now.
> So far is fine, but just in case I ran this command:
> DBCC CHECKDB (MyProd) WITH ALL_ERRORMSGS
> And it showed no errors.
> Are there any other commands that I can run to check for integrity in a
> database in a case like this?
>|||Dan,
Thank for your comment. I did not expect to have SQL Server errors
either. But they were.
I guess as you suggest, the problem went during the rebuild, not
directly because the failure.
The errors I had were:
18052 :
Error: 3624, Severity: 20, State: 1.
And this:
17066 :
SQL Server Assertion: File:
<p:\sql\ntdbms\storeng\drs\include\record.inl>, line=1447
Failed Assertion = 'm_SizeRec > 0 && m_SizeRec <= MAXDATAROW'.
I also had other errores, but the only message they have is about
telling "The description for Event ID ( XXXX ) in Source ( MSSQLSERVER
) cannot be found. "
Regards,
Dan Guzman wrote:
> If the CHECKDB was clean, you are in good shape. You might also include
> DBCC CHECKCATALOG as part of your scheduled integrity checks but it unlikely
> that a hardware error will introduce catalog problems.
> You mention SQL Server errors but SQL Server should be oblivious to the
> problem, assuming the controller gracefully handles such errors. If the
> errors are due to the I/O subsystem become sluggish during the rebuild,
> check to see if your array controller allows you to configure the rebuild
> priority.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> <craigkenisston@.hotmail.com> wrote in message
> news:1165648156.056709.236200@.f1g2000cwa.googlegroups.com...
> > Hi,
> >
> > Today I had one disk of my server array crashed, it is on raid 5 with
> > hot spare disks.
> > SQL Server reported some errors on event logger but apparently the hot
> > spare disks were used correctly since it is working right now.
> >
> > So far is fine, but just in case I ran this command:
> > DBCC CHECKDB (MyProd) WITH ALL_ERRORMSGS
> >
> > And it showed no errors.
> > Are there any other commands that I can run to check for integrity in a
> > database in a case like this?
> >|||The assertion error indicates garbage was returned to SQL Server. I find it
strange that the controller would return bad data during the rebuild. Even
during the rebuild, the controller ought to be able to reconstruct the
original data and return it to SQL, albeit more slowly than a healthy array.
Anyway, I'm glad your DBCCs are clean and you are out of the woods.
--
Hope this helps.
Dan Guzman
SQL Server MVP
<craigkenisston@.hotmail.com> wrote in message
news:1165681327.852727.312450@.f1g2000cwa.googlegroups.com...
> Dan,
> Thank for your comment. I did not expect to have SQL Server errors
> either. But they were.
> I guess as you suggest, the problem went during the rebuild, not
> directly because the failure.
> The errors I had were:
> 18052 :
> Error: 3624, Severity: 20, State: 1.
> And this:
> 17066 :
> SQL Server Assertion: File:
> <p:\sql\ntdbms\storeng\drs\include\record.inl>, line=1447
> Failed Assertion = 'm_SizeRec > 0 && m_SizeRec <= MAXDATAROW'.
>
> I also had other errores, but the only message they have is about
> telling "The description for Event ID ( XXXX ) in Source ( MSSQLSERVER
> ) cannot be found. "
>
> Regards,
>
> Dan Guzman wrote:
>> If the CHECKDB was clean, you are in good shape. You might also include
>> DBCC CHECKCATALOG as part of your scheduled integrity checks but it
>> unlikely
>> that a hardware error will introduce catalog problems.
>> You mention SQL Server errors but SQL Server should be oblivious to the
>> problem, assuming the controller gracefully handles such errors. If the
>> errors are due to the I/O subsystem become sluggish during the rebuild,
>> check to see if your array controller allows you to configure the rebuild
>> priority.
>> --
>> Hope this helps.
>> Dan Guzman
>> SQL Server MVP
>> <craigkenisston@.hotmail.com> wrote in message
>> news:1165648156.056709.236200@.f1g2000cwa.googlegroups.com...
>> > Hi,
>> >
>> > Today I had one disk of my server array crashed, it is on raid 5 with
>> > hot spare disks.
>> > SQL Server reported some errors on event logger but apparently the hot
>> > spare disks were used correctly since it is working right now.
>> >
>> > So far is fine, but just in case I ran this command:
>> > DBCC CHECKDB (MyProd) WITH ALL_ERRORMSGS
>> >
>> > And it showed no errors.
>> > Are there any other commands that I can run to check for integrity in a
>> > database in a case like this?
>> >
>
Checking a database for physical damage
Hi,
Today I had one disk of my server array crashed, it is on raid 5 with
hot spare disks.
SQL Server reported some errors on event logger but apparently the hot
spare disks were used correctly since it is working right now.
So far is fine, but just in case I ran this command:
DBCC CHECKDB (MyProd) WITH ALL_ERRORMSGS
And it showed no errors.
Are there any other commands that I can run to check for integrity in a
database in a case like this?If the CHECKDB was clean, you are in good shape. You might also include
DBCC CHECKCATALOG as part of your scheduled integrity checks but it unlikely
that a hardware error will introduce catalog problems.
You mention SQL Server errors but SQL Server should be oblivious to the
problem, assuming the controller gracefully handles such errors. If the
errors are due to the I/O subsystem become sluggish during the rebuild,
check to see if your array controller allows you to configure the rebuild
priority.
Hope this helps.
Dan Guzman
SQL Server MVP
<craigkenisston@.hotmail.com> wrote in message
news:1165648156.056709.236200@.f1g2000cwa.googlegroups.com...
> Hi,
> Today I had one disk of my server array crashed, it is on raid 5 with
> hot spare disks.
> SQL Server reported some errors on event logger but apparently the hot
> spare disks were used correctly since it is working right now.
> So far is fine, but just in case I ran this command:
> DBCC CHECKDB (MyProd) WITH ALL_ERRORMSGS
> And it showed no errors.
> Are there any other commands that I can run to check for integrity in a
> database in a case like this?
>|||Dan,
Thank for your comment. I did not expect to have SQL Server errors
either. But they were.
I guess as you suggest, the problem went during the rebuild, not
directly because the failure.
The errors I had were:
18052 :
Error: 3624, Severity: 20, State: 1.
And this:
17066 :
SQL Server Assertion: File:
< p:\sql\ntdbms\storeng\drs\include\record
.inl>, line=1447
Failed Assertion = 'm_SizeRec > 0 && m_SizeRec <= MAXDATAROW'.
I also had other errores, but the only message they have is about
telling "The description for Event ID ( XXXX ) in Source ( MSSQLSERVER
) cannot be found. "
Regards,
Dan Guzman wrote:[vbcol=seagreen]
> If the CHECKDB was clean, you are in good shape. You might also include
> DBCC CHECKCATALOG as part of your scheduled integrity checks but it unlike
ly
> that a hardware error will introduce catalog problems.
> You mention SQL Server errors but SQL Server should be oblivious to the
> problem, assuming the controller gracefully handles such errors. If the
> errors are due to the I/O subsystem become sluggish during the rebuild,
> check to see if your array controller allows you to configure the rebuild
> priority.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> <craigkenisston@.hotmail.com> wrote in message
> news:1165648156.056709.236200@.f1g2000cwa.googlegroups.com...|||The assertion error indicates garbage was returned to SQL Server. I find it
strange that the controller would return bad data during the rebuild. Even
during the rebuild, the controller ought to be able to reconstruct the
original data and return it to SQL, albeit more slowly than a healthy array.
Anyway, I'm glad your DBCCs are clean and you are out of the woods.
Hope this helps.
Dan Guzman
SQL Server MVP
<craigkenisston@.hotmail.com> wrote in message
news:1165681327.852727.312450@.f1g2000cwa.googlegroups.com...
> Dan,
> Thank for your comment. I did not expect to have SQL Server errors
> either. But they were.
> I guess as you suggest, the problem went during the rebuild, not
> directly because the failure.
> The errors I had were:
> 18052 :
> Error: 3624, Severity: 20, State: 1.
> And this:
> 17066 :
> SQL Server Assertion: File:
> < p:\sql\ntdbms\storeng\drs\include\record
.inl>, line=1447
> Failed Assertion = 'm_SizeRec > 0 && m_SizeRec <= MAXDATAROW'.
>
> I also had other errores, but the only message they have is about
> telling "The description for Event ID ( XXXX ) in Source ( MSSQLSERVER
> ) cannot be found. "
>
> Regards,
>
> Dan Guzman wrote:
>
Today I had one disk of my server array crashed, it is on raid 5 with
hot spare disks.
SQL Server reported some errors on event logger but apparently the hot
spare disks were used correctly since it is working right now.
So far is fine, but just in case I ran this command:
DBCC CHECKDB (MyProd) WITH ALL_ERRORMSGS
And it showed no errors.
Are there any other commands that I can run to check for integrity in a
database in a case like this?If the CHECKDB was clean, you are in good shape. You might also include
DBCC CHECKCATALOG as part of your scheduled integrity checks but it unlikely
that a hardware error will introduce catalog problems.
You mention SQL Server errors but SQL Server should be oblivious to the
problem, assuming the controller gracefully handles such errors. If the
errors are due to the I/O subsystem become sluggish during the rebuild,
check to see if your array controller allows you to configure the rebuild
priority.
Hope this helps.
Dan Guzman
SQL Server MVP
<craigkenisston@.hotmail.com> wrote in message
news:1165648156.056709.236200@.f1g2000cwa.googlegroups.com...
> Hi,
> Today I had one disk of my server array crashed, it is on raid 5 with
> hot spare disks.
> SQL Server reported some errors on event logger but apparently the hot
> spare disks were used correctly since it is working right now.
> So far is fine, but just in case I ran this command:
> DBCC CHECKDB (MyProd) WITH ALL_ERRORMSGS
> And it showed no errors.
> Are there any other commands that I can run to check for integrity in a
> database in a case like this?
>|||Dan,
Thank for your comment. I did not expect to have SQL Server errors
either. But they were.
I guess as you suggest, the problem went during the rebuild, not
directly because the failure.
The errors I had were:
18052 :
Error: 3624, Severity: 20, State: 1.
And this:
17066 :
SQL Server Assertion: File:
< p:\sql\ntdbms\storeng\drs\include\record
.inl>, line=1447
Failed Assertion = 'm_SizeRec > 0 && m_SizeRec <= MAXDATAROW'.
I also had other errores, but the only message they have is about
telling "The description for Event ID ( XXXX ) in Source ( MSSQLSERVER
) cannot be found. "
Regards,
Dan Guzman wrote:[vbcol=seagreen]
> If the CHECKDB was clean, you are in good shape. You might also include
> DBCC CHECKCATALOG as part of your scheduled integrity checks but it unlike
ly
> that a hardware error will introduce catalog problems.
> You mention SQL Server errors but SQL Server should be oblivious to the
> problem, assuming the controller gracefully handles such errors. If the
> errors are due to the I/O subsystem become sluggish during the rebuild,
> check to see if your array controller allows you to configure the rebuild
> priority.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> <craigkenisston@.hotmail.com> wrote in message
> news:1165648156.056709.236200@.f1g2000cwa.googlegroups.com...|||The assertion error indicates garbage was returned to SQL Server. I find it
strange that the controller would return bad data during the rebuild. Even
during the rebuild, the controller ought to be able to reconstruct the
original data and return it to SQL, albeit more slowly than a healthy array.
Anyway, I'm glad your DBCCs are clean and you are out of the woods.
Hope this helps.
Dan Guzman
SQL Server MVP
<craigkenisston@.hotmail.com> wrote in message
news:1165681327.852727.312450@.f1g2000cwa.googlegroups.com...
> Dan,
> Thank for your comment. I did not expect to have SQL Server errors
> either. But they were.
> I guess as you suggest, the problem went during the rebuild, not
> directly because the failure.
> The errors I had were:
> 18052 :
> Error: 3624, Severity: 20, State: 1.
> And this:
> 17066 :
> SQL Server Assertion: File:
> < p:\sql\ntdbms\storeng\drs\include\record
.inl>, line=1447
> Failed Assertion = 'm_SizeRec > 0 && m_SizeRec <= MAXDATAROW'.
>
> I also had other errores, but the only message they have is about
> telling "The description for Event ID ( XXXX ) in Source ( MSSQLSERVER
> ) cannot be found. "
>
> Regards,
>
> Dan Guzman wrote:
>
Friday, February 10, 2012
Check Temp table size?
I have a tempdb which fills up the disk frequently. I want to determine the
size of each object in the tempdb.
I used EXEC sp_spaceused '#temptablename'. I got the "#temptablename" does
not exist error. I got the temp table names from INFORMATION_SCHEMA.TABLES.
Since the temp tables only valid for their own session, how can I check its
size of temp tables through Query Analyzer?
Another question is how to analyze the log file space usage? The log file
for my TempDB grows dramatically fast too. I need understand the reason.
Thanks a lot,
FL
EXEC tempdb..spaceused '#temptablename'
http://www.aspfaq.com/
(Reverse address to reply.)
"FLX" <nospam@.hotmail.com> wrote in message
news:e$uxjy91EHA.3368@.TK2MSFTNGP10.phx.gbl...
> I have a tempdb which fills up the disk frequently. I want to determine
the
> size of each object in the tempdb.
> I used EXEC sp_spaceused '#temptablename'. I got the "#temptablename" does
> not exist error. I got the temp table names from
INFORMATION_SCHEMA.TABLES.
> Since the temp tables only valid for their own session, how can I check
its
> size of temp tables through Query Analyzer?
> Another question is how to analyze the log file space usage? The log file
> for my TempDB grows dramatically fast too. I need understand the reason.
> Thanks a lot,
> FL
>
>
>
>
|||Thanks.
It did not solve my problem. Maybe my question was not clear. The
#temptablename was created in the stored procedure. Since it is a local temp
table, I can not access it from the Query Analyzer. What is the workaround?
I know if it is a global temp table I can access it.
FL
"Aaron [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:%23sIQO391EHA.1396@.tk2msftngp13.phx.gbl...[vbcol=seagreen]
> EXEC tempdb..spaceused '#temptablename'
> --
> http://www.aspfaq.com/
> (Reverse address to reply.)
>
>
> "FLX" <nospam@.hotmail.com> wrote in message
> news:e$uxjy91EHA.3368@.TK2MSFTNGP10.phx.gbl...
> the
does[vbcol=seagreen]
> INFORMATION_SCHEMA.TABLES.
> its
file
>
|||You want to check the size of a temp table created in a stored procedure,
from query analyzer OUTSIDE the scope of the stored procedure?
I don't think this is possible, unless you manually inspect
tempdb..sysobjects and guess which table is from the specific stored
procedure scope you are interested in. What do you expect will happen when
the stored procedure is being executed by 12 different people
simultaneously? Since the temp table only lives for the scope of the stored
procedure, why do you care aout its size OUTSIDE of that scope?
http://www.aspfaq.com/
(Reverse address to reply.)
"FLX" <nospam@.hotmail.com> wrote in message
news:u46sZM$1EHA.4004@.tk2msftngp13.phx.gbl...
> Thanks.
> It did not solve my problem. Maybe my question was not clear. The
> #temptablename was created in the stored procedure. Since it is a local
temp
> table, I can not access it from the Query Analyzer. What is the
workaround?[vbcol=seagreen]
> I know if it is a global temp table I can access it.
> FL
>
>
>
> "Aaron [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
> news:%23sIQO391EHA.1396@.tk2msftngp13.phx.gbl...
determine[vbcol=seagreen]
> does
check[vbcol=seagreen]
> file
reason.
>
|||> I don't think this is possible, unless you manually inspect
> tempdb..sysobjects and guess which table is from the specific stored
> procedure scope you are interested in.
This is unfortunate.
>What do you expect will happen when
> the stored procedure is being executed by 12 different people
> simultaneously?
I expect 12 temp tables with unique Table Names stored in the database.
Let's say there is a temp table "#tempTable" is created in the stored
procedure. Two people simultaneously execute the stored procedure. When I
ran the following query,
use tempdb
go
select *
FROM INFORMATION_SCHEMA.TABLES
In the tablename column, I saw:
#tempTable________________________________________ __________________________
_________________________________________000000058 617
#tempTable________________________________________ __________________________
_________________________________________000000058 628
Please notice the appendix are different.
>Since the temp table only lives for the scope of the stored
> procedure, why do you care aout its size OUTSIDE of that scope?
My tempdb is too large and I want to figure out the cause. One obvious
reason is that we used too many temp tables created in the stored
procedures. I know theoritically the temp table will be automatically
dropped when the stored procedure exits. However, I don't why my tempdb size
keeps growing and can not be shrinked.
Thanks a lot.
|||>
#tempTable________________________________________ __________________________
> _________________________________________000000058 617
>
#tempTable________________________________________ __________________________
> _________________________________________000000058 628
> Please notice the appendix are different.
Yes, those suffixes are generated by SQL Server internally to track #temp
tables from different sessions. Again, you would have to guess which one is
which.
> However, I don't why my tempdb size
> keeps growing and can not be shrinked.
http://www.aspfaq.com/2446
http://www.aspfaq.com/2471
|||> > However, I don't why my tempdb size
> http://www.aspfaq.com/2446
> http://www.aspfaq.com/2471
>
Very helpful link. However, when I ran the following query:
USE tempdb
GO
EXEC sp_spaceused @.updateusage = 'TRUE'
I got the result like:
database_name
database_size unallocated space
--- --
-- --
tempdb
4425.94 MB 4140.27 MB
reserved data index_size unused
-- -- -- --
624 KB 216 KB 320 KB 88 KB
My questions are:
1. What does the unallocated space mean? Free space? Unused space? Why I can
not reclaim the such space by shrinking db?
2.The data and index add up together are only 216 + 320 = 536 KB. It is less
than 1 MB. Why my tempdb is so large (> 4GB)? Any hint?
Thanks
|||The possible reasons for tempdb growth are listed in that article. Why is
your tempdb so large? I have no idea. Probably one or more of those
reasons. Why can't you shrink it? I have no idea. What else is going on
in the system? What command are you using to shrink the database? Does it
complete successfully, or do you get an error? If you get an error, what is
it?
http://www.aspfaq.com/
(Reverse address to reply.)
"FLX" <nospam@.hotmail.com> wrote in message
news:uDHsq7H2EHA.936@.TK2MSFTNGP12.phx.gbl...
> Very helpful link. However, when I ran the following query:
> USE tempdb
> GO
> EXEC sp_spaceused @.updateusage = 'TRUE'
> I got the result like:
> database_name
> database_size unallocated space
> --- --
--
> -- --
> tempdb
> 4425.94 MB 4140.27 MB
>
> reserved data index_size unused
> -- -- -- --
> 624 KB 216 KB 320 KB 88 KB
>
> My questions are:
> 1. What does the unallocated space mean? Free space? Unused space? Why I
can
> not reclaim the such space by shrinking db?
> 2.The data and index add up together are only 216 + 320 = 536 KB. It is
less
> than 1 MB. Why my tempdb is so large (> 4GB)? Any hint?
> Thanks
>
>
>
size of each object in the tempdb.
I used EXEC sp_spaceused '#temptablename'. I got the "#temptablename" does
not exist error. I got the temp table names from INFORMATION_SCHEMA.TABLES.
Since the temp tables only valid for their own session, how can I check its
size of temp tables through Query Analyzer?
Another question is how to analyze the log file space usage? The log file
for my TempDB grows dramatically fast too. I need understand the reason.
Thanks a lot,
FL
EXEC tempdb..spaceused '#temptablename'
http://www.aspfaq.com/
(Reverse address to reply.)
"FLX" <nospam@.hotmail.com> wrote in message
news:e$uxjy91EHA.3368@.TK2MSFTNGP10.phx.gbl...
> I have a tempdb which fills up the disk frequently. I want to determine
the
> size of each object in the tempdb.
> I used EXEC sp_spaceused '#temptablename'. I got the "#temptablename" does
> not exist error. I got the temp table names from
INFORMATION_SCHEMA.TABLES.
> Since the temp tables only valid for their own session, how can I check
its
> size of temp tables through Query Analyzer?
> Another question is how to analyze the log file space usage? The log file
> for my TempDB grows dramatically fast too. I need understand the reason.
> Thanks a lot,
> FL
>
>
>
>
|||Thanks.
It did not solve my problem. Maybe my question was not clear. The
#temptablename was created in the stored procedure. Since it is a local temp
table, I can not access it from the Query Analyzer. What is the workaround?
I know if it is a global temp table I can access it.
FL
"Aaron [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:%23sIQO391EHA.1396@.tk2msftngp13.phx.gbl...[vbcol=seagreen]
> EXEC tempdb..spaceused '#temptablename'
> --
> http://www.aspfaq.com/
> (Reverse address to reply.)
>
>
> "FLX" <nospam@.hotmail.com> wrote in message
> news:e$uxjy91EHA.3368@.TK2MSFTNGP10.phx.gbl...
> the
does[vbcol=seagreen]
> INFORMATION_SCHEMA.TABLES.
> its
file
>
|||You want to check the size of a temp table created in a stored procedure,
from query analyzer OUTSIDE the scope of the stored procedure?
I don't think this is possible, unless you manually inspect
tempdb..sysobjects and guess which table is from the specific stored
procedure scope you are interested in. What do you expect will happen when
the stored procedure is being executed by 12 different people
simultaneously? Since the temp table only lives for the scope of the stored
procedure, why do you care aout its size OUTSIDE of that scope?
http://www.aspfaq.com/
(Reverse address to reply.)
"FLX" <nospam@.hotmail.com> wrote in message
news:u46sZM$1EHA.4004@.tk2msftngp13.phx.gbl...
> Thanks.
> It did not solve my problem. Maybe my question was not clear. The
> #temptablename was created in the stored procedure. Since it is a local
temp
> table, I can not access it from the Query Analyzer. What is the
workaround?[vbcol=seagreen]
> I know if it is a global temp table I can access it.
> FL
>
>
>
> "Aaron [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
> news:%23sIQO391EHA.1396@.tk2msftngp13.phx.gbl...
determine[vbcol=seagreen]
> does
check[vbcol=seagreen]
> file
reason.
>
|||> I don't think this is possible, unless you manually inspect
> tempdb..sysobjects and guess which table is from the specific stored
> procedure scope you are interested in.
This is unfortunate.
>What do you expect will happen when
> the stored procedure is being executed by 12 different people
> simultaneously?
I expect 12 temp tables with unique Table Names stored in the database.
Let's say there is a temp table "#tempTable" is created in the stored
procedure. Two people simultaneously execute the stored procedure. When I
ran the following query,
use tempdb
go
select *
FROM INFORMATION_SCHEMA.TABLES
In the tablename column, I saw:
#tempTable________________________________________ __________________________
_________________________________________000000058 617
#tempTable________________________________________ __________________________
_________________________________________000000058 628
Please notice the appendix are different.
>Since the temp table only lives for the scope of the stored
> procedure, why do you care aout its size OUTSIDE of that scope?
My tempdb is too large and I want to figure out the cause. One obvious
reason is that we used too many temp tables created in the stored
procedures. I know theoritically the temp table will be automatically
dropped when the stored procedure exits. However, I don't why my tempdb size
keeps growing and can not be shrinked.
Thanks a lot.
|||>
#tempTable________________________________________ __________________________
> _________________________________________000000058 617
>
#tempTable________________________________________ __________________________
> _________________________________________000000058 628
> Please notice the appendix are different.
Yes, those suffixes are generated by SQL Server internally to track #temp
tables from different sessions. Again, you would have to guess which one is
which.
> However, I don't why my tempdb size
> keeps growing and can not be shrinked.
http://www.aspfaq.com/2446
http://www.aspfaq.com/2471
|||> > However, I don't why my tempdb size
> http://www.aspfaq.com/2446
> http://www.aspfaq.com/2471
>
Very helpful link. However, when I ran the following query:
USE tempdb
GO
EXEC sp_spaceused @.updateusage = 'TRUE'
I got the result like:
database_name
database_size unallocated space
--- --
-- --
tempdb
4425.94 MB 4140.27 MB
reserved data index_size unused
-- -- -- --
624 KB 216 KB 320 KB 88 KB
My questions are:
1. What does the unallocated space mean? Free space? Unused space? Why I can
not reclaim the such space by shrinking db?
2.The data and index add up together are only 216 + 320 = 536 KB. It is less
than 1 MB. Why my tempdb is so large (> 4GB)? Any hint?
Thanks
|||The possible reasons for tempdb growth are listed in that article. Why is
your tempdb so large? I have no idea. Probably one or more of those
reasons. Why can't you shrink it? I have no idea. What else is going on
in the system? What command are you using to shrink the database? Does it
complete successfully, or do you get an error? If you get an error, what is
it?
http://www.aspfaq.com/
(Reverse address to reply.)
"FLX" <nospam@.hotmail.com> wrote in message
news:uDHsq7H2EHA.936@.TK2MSFTNGP12.phx.gbl...
> Very helpful link. However, when I ran the following query:
> USE tempdb
> GO
> EXEC sp_spaceused @.updateusage = 'TRUE'
> I got the result like:
> database_name
> database_size unallocated space
> --- --
--
> -- --
> tempdb
> 4425.94 MB 4140.27 MB
>
> reserved data index_size unused
> -- -- -- --
> 624 KB 216 KB 320 KB 88 KB
>
> My questions are:
> 1. What does the unallocated space mean? Free space? Unused space? Why I
can
> not reclaim the such space by shrinking db?
> 2.The data and index add up together are only 216 + 320 = 536 KB. It is
less
> than 1 MB. Why my tempdb is so large (> 4GB)? Any hint?
> Thanks
>
>
>
Check Temp table size?
I have a tempdb which fills up the disk frequently. I want to determine the
size of each object in the tempdb.
I used EXEC sp_spaceused '#temptablename'. I got the "#temptablename" does
not exist error. I got the temp table names from INFORMATION_SCHEMA.TABLES.
Since the temp tables only valid for their own session, how can I check its
size of temp tables through Query Analyzer?
Another question is how to analyze the log file space usage? The log file
for my TempDB grows dramatically fast too. I need understand the reason.
Thanks a lot,
FLEXEC tempdb..spaceused '#temptablename'
--
http://www.aspfaq.com/
(Reverse address to reply.)
"FLX" <nospam@.hotmail.com> wrote in message
news:e$uxjy91EHA.3368@.TK2MSFTNGP10.phx.gbl...
> I have a tempdb which fills up the disk frequently. I want to determine
the
> size of each object in the tempdb.
> I used EXEC sp_spaceused '#temptablename'. I got the "#temptablename" does
> not exist error. I got the temp table names from
INFORMATION_SCHEMA.TABLES.
> Since the temp tables only valid for their own session, how can I check
its
> size of temp tables through Query Analyzer?
> Another question is how to analyze the log file space usage? The log file
> for my TempDB grows dramatically fast too. I need understand the reason.
> Thanks a lot,
> FL
>
>
>
>|||Thanks.
It did not solve my problem. Maybe my question was not clear. The
#temptablename was created in the stored procedure. Since it is a local temp
table, I can not access it from the Query Analyzer. What is the workaround?
I know if it is a global temp table I can access it.
FL
"Aaron [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:%23sIQO391EHA.1396@.tk2msftngp13.phx.gbl...
> EXEC tempdb..spaceused '#temptablename'
> --
> http://www.aspfaq.com/
> (Reverse address to reply.)
>
>
> "FLX" <nospam@.hotmail.com> wrote in message
> news:e$uxjy91EHA.3368@.TK2MSFTNGP10.phx.gbl...
> > I have a tempdb which fills up the disk frequently. I want to determine
> the
> > size of each object in the tempdb.
> >
> > I used EXEC sp_spaceused '#temptablename'. I got the "#temptablename"
does
> > not exist error. I got the temp table names from
> INFORMATION_SCHEMA.TABLES.
> > Since the temp tables only valid for their own session, how can I check
> its
> > size of temp tables through Query Analyzer?
> >
> > Another question is how to analyze the log file space usage? The log
file
> > for my TempDB grows dramatically fast too. I need understand the reason.
> >
> > Thanks a lot,
> > FL
> >
> >
> >
> >
> >
> >
> >
>|||You want to check the size of a temp table created in a stored procedure,
from query analyzer OUTSIDE the scope of the stored procedure?
I don't think this is possible, unless you manually inspect
tempdb..sysobjects and guess which table is from the specific stored
procedure scope you are interested in. What do you expect will happen when
the stored procedure is being executed by 12 different people
simultaneously? Since the temp table only lives for the scope of the stored
procedure, why do you care aout its size OUTSIDE of that scope?
--
http://www.aspfaq.com/
(Reverse address to reply.)
"FLX" <nospam@.hotmail.com> wrote in message
news:u46sZM$1EHA.4004@.tk2msftngp13.phx.gbl...
> Thanks.
> It did not solve my problem. Maybe my question was not clear. The
> #temptablename was created in the stored procedure. Since it is a local
temp
> table, I can not access it from the Query Analyzer. What is the
workaround?
> I know if it is a global temp table I can access it.
> FL
>
>
>
> "Aaron [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
> news:%23sIQO391EHA.1396@.tk2msftngp13.phx.gbl...
> > EXEC tempdb..spaceused '#temptablename'
> >
> > --
> > http://www.aspfaq.com/
> > (Reverse address to reply.)
> >
> >
> >
> >
> > "FLX" <nospam@.hotmail.com> wrote in message
> > news:e$uxjy91EHA.3368@.TK2MSFTNGP10.phx.gbl...
> > > I have a tempdb which fills up the disk frequently. I want to
determine
> > the
> > > size of each object in the tempdb.
> > >
> > > I used EXEC sp_spaceused '#temptablename'. I got the "#temptablename"
> does
> > > not exist error. I got the temp table names from
> > INFORMATION_SCHEMA.TABLES.
> > > Since the temp tables only valid for their own session, how can I
check
> > its
> > > size of temp tables through Query Analyzer?
> > >
> > > Another question is how to analyze the log file space usage? The log
> file
> > > for my TempDB grows dramatically fast too. I need understand the
reason.
> > >
> > > Thanks a lot,
> > > FL
> > >
> > >
> > >
> > >
> > >
> > >
> > >
> >
> >
>|||> I don't think this is possible, unless you manually inspect
> tempdb..sysobjects and guess which table is from the specific stored
> procedure scope you are interested in.
This is unfortunate.
>What do you expect will happen when
> the stored procedure is being executed by 12 different people
> simultaneously?
I expect 12 temp tables with unique Table Names stored in the database.
Let's say there is a temp table "#tempTable" is created in the stored
procedure. Two people simultaneously execute the stored procedure. When I
ran the following query,
use tempdb
go
select *
FROM INFORMATION_SCHEMA.TABLES
In the tablename column, I saw:
#tempTable__________________________________________________________________
_________________________________________000000058617
#tempTable__________________________________________________________________
_________________________________________000000058628
Please notice the appendix are different.
>Since the temp table only lives for the scope of the stored
> procedure, why do you care aout its size OUTSIDE of that scope?
My tempdb is too large and I want to figure out the cause. One obvious
reason is that we used too many temp tables created in the stored
procedures. I know theoritically the temp table will be automatically
dropped when the stored procedure exits. However, I don't why my tempdb size
keeps growing and can not be shrinked.
Thanks a lot.|||>
#tempTable__________________________________________________________________
> _________________________________________000000058617
>
#tempTable__________________________________________________________________
> _________________________________________000000058628
> Please notice the appendix are different.
Yes, those suffixes are generated by SQL Server internally to track #temp
tables from different sessions. Again, you would have to guess which one is
which.
> However, I don't why my tempdb size
> keeps growing and can not be shrinked.
http://www.aspfaq.com/2446
http://www.aspfaq.com/2471|||> > However, I don't why my tempdb size
> > keeps growing and can not be shrinked.
> http://www.aspfaq.com/2446
> http://www.aspfaq.com/2471
>
Very helpful link. However, when I ran the following query:
USE tempdb
GO
EXEC sp_spaceused @.updateusage = 'TRUE'
I got the result like:
database_name
database_size unallocated space
--- --
-- --
tempdb
4425.94 MB 4140.27 MB
reserved data index_size unused
-- -- -- --
624 KB 216 KB 320 KB 88 KB
My questions are:
1. What does the unallocated space mean? Free space? Unused space? Why I can
not reclaim the such space by shrinking db?
2.The data and index add up together are only 216 + 320 = 536 KB. It is less
than 1 MB. Why my tempdb is so large (> 4GB)? Any hint?
Thanks|||The possible reasons for tempdb growth are listed in that article. Why is
your tempdb so large? I have no idea. Probably one or more of those
reasons. Why can't you shrink it? I have no idea. What else is going on
in the system? What command are you using to shrink the database? Does it
complete successfully, or do you get an error? If you get an error, what is
it?
--
http://www.aspfaq.com/
(Reverse address to reply.)
"FLX" <nospam@.hotmail.com> wrote in message
news:uDHsq7H2EHA.936@.TK2MSFTNGP12.phx.gbl...
> > > However, I don't why my tempdb size
> > > keeps growing and can not be shrinked.
> >
> > http://www.aspfaq.com/2446
> > http://www.aspfaq.com/2471
> >
> Very helpful link. However, when I ran the following query:
> USE tempdb
> GO
> EXEC sp_spaceused @.updateusage = 'TRUE'
> I got the result like:
> database_name
> database_size unallocated space
> --- --
--
> -- --
> tempdb
> 4425.94 MB 4140.27 MB
>
> reserved data index_size unused
> -- -- -- --
> 624 KB 216 KB 320 KB 88 KB
>
> My questions are:
> 1. What does the unallocated space mean? Free space? Unused space? Why I
can
> not reclaim the such space by shrinking db?
> 2.The data and index add up together are only 216 + 320 = 536 KB. It is
less
> than 1 MB. Why my tempdb is so large (> 4GB)? Any hint?
> Thanks
>
>
>
size of each object in the tempdb.
I used EXEC sp_spaceused '#temptablename'. I got the "#temptablename" does
not exist error. I got the temp table names from INFORMATION_SCHEMA.TABLES.
Since the temp tables only valid for their own session, how can I check its
size of temp tables through Query Analyzer?
Another question is how to analyze the log file space usage? The log file
for my TempDB grows dramatically fast too. I need understand the reason.
Thanks a lot,
FLEXEC tempdb..spaceused '#temptablename'
--
http://www.aspfaq.com/
(Reverse address to reply.)
"FLX" <nospam@.hotmail.com> wrote in message
news:e$uxjy91EHA.3368@.TK2MSFTNGP10.phx.gbl...
> I have a tempdb which fills up the disk frequently. I want to determine
the
> size of each object in the tempdb.
> I used EXEC sp_spaceused '#temptablename'. I got the "#temptablename" does
> not exist error. I got the temp table names from
INFORMATION_SCHEMA.TABLES.
> Since the temp tables only valid for their own session, how can I check
its
> size of temp tables through Query Analyzer?
> Another question is how to analyze the log file space usage? The log file
> for my TempDB grows dramatically fast too. I need understand the reason.
> Thanks a lot,
> FL
>
>
>
>|||Thanks.
It did not solve my problem. Maybe my question was not clear. The
#temptablename was created in the stored procedure. Since it is a local temp
table, I can not access it from the Query Analyzer. What is the workaround?
I know if it is a global temp table I can access it.
FL
"Aaron [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:%23sIQO391EHA.1396@.tk2msftngp13.phx.gbl...
> EXEC tempdb..spaceused '#temptablename'
> --
> http://www.aspfaq.com/
> (Reverse address to reply.)
>
>
> "FLX" <nospam@.hotmail.com> wrote in message
> news:e$uxjy91EHA.3368@.TK2MSFTNGP10.phx.gbl...
> > I have a tempdb which fills up the disk frequently. I want to determine
> the
> > size of each object in the tempdb.
> >
> > I used EXEC sp_spaceused '#temptablename'. I got the "#temptablename"
does
> > not exist error. I got the temp table names from
> INFORMATION_SCHEMA.TABLES.
> > Since the temp tables only valid for their own session, how can I check
> its
> > size of temp tables through Query Analyzer?
> >
> > Another question is how to analyze the log file space usage? The log
file
> > for my TempDB grows dramatically fast too. I need understand the reason.
> >
> > Thanks a lot,
> > FL
> >
> >
> >
> >
> >
> >
> >
>|||You want to check the size of a temp table created in a stored procedure,
from query analyzer OUTSIDE the scope of the stored procedure?
I don't think this is possible, unless you manually inspect
tempdb..sysobjects and guess which table is from the specific stored
procedure scope you are interested in. What do you expect will happen when
the stored procedure is being executed by 12 different people
simultaneously? Since the temp table only lives for the scope of the stored
procedure, why do you care aout its size OUTSIDE of that scope?
--
http://www.aspfaq.com/
(Reverse address to reply.)
"FLX" <nospam@.hotmail.com> wrote in message
news:u46sZM$1EHA.4004@.tk2msftngp13.phx.gbl...
> Thanks.
> It did not solve my problem. Maybe my question was not clear. The
> #temptablename was created in the stored procedure. Since it is a local
temp
> table, I can not access it from the Query Analyzer. What is the
workaround?
> I know if it is a global temp table I can access it.
> FL
>
>
>
> "Aaron [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
> news:%23sIQO391EHA.1396@.tk2msftngp13.phx.gbl...
> > EXEC tempdb..spaceused '#temptablename'
> >
> > --
> > http://www.aspfaq.com/
> > (Reverse address to reply.)
> >
> >
> >
> >
> > "FLX" <nospam@.hotmail.com> wrote in message
> > news:e$uxjy91EHA.3368@.TK2MSFTNGP10.phx.gbl...
> > > I have a tempdb which fills up the disk frequently. I want to
determine
> > the
> > > size of each object in the tempdb.
> > >
> > > I used EXEC sp_spaceused '#temptablename'. I got the "#temptablename"
> does
> > > not exist error. I got the temp table names from
> > INFORMATION_SCHEMA.TABLES.
> > > Since the temp tables only valid for their own session, how can I
check
> > its
> > > size of temp tables through Query Analyzer?
> > >
> > > Another question is how to analyze the log file space usage? The log
> file
> > > for my TempDB grows dramatically fast too. I need understand the
reason.
> > >
> > > Thanks a lot,
> > > FL
> > >
> > >
> > >
> > >
> > >
> > >
> > >
> >
> >
>|||> I don't think this is possible, unless you manually inspect
> tempdb..sysobjects and guess which table is from the specific stored
> procedure scope you are interested in.
This is unfortunate.
>What do you expect will happen when
> the stored procedure is being executed by 12 different people
> simultaneously?
I expect 12 temp tables with unique Table Names stored in the database.
Let's say there is a temp table "#tempTable" is created in the stored
procedure. Two people simultaneously execute the stored procedure. When I
ran the following query,
use tempdb
go
select *
FROM INFORMATION_SCHEMA.TABLES
In the tablename column, I saw:
#tempTable__________________________________________________________________
_________________________________________000000058617
#tempTable__________________________________________________________________
_________________________________________000000058628
Please notice the appendix are different.
>Since the temp table only lives for the scope of the stored
> procedure, why do you care aout its size OUTSIDE of that scope?
My tempdb is too large and I want to figure out the cause. One obvious
reason is that we used too many temp tables created in the stored
procedures. I know theoritically the temp table will be automatically
dropped when the stored procedure exits. However, I don't why my tempdb size
keeps growing and can not be shrinked.
Thanks a lot.|||>
#tempTable__________________________________________________________________
> _________________________________________000000058617
>
#tempTable__________________________________________________________________
> _________________________________________000000058628
> Please notice the appendix are different.
Yes, those suffixes are generated by SQL Server internally to track #temp
tables from different sessions. Again, you would have to guess which one is
which.
> However, I don't why my tempdb size
> keeps growing and can not be shrinked.
http://www.aspfaq.com/2446
http://www.aspfaq.com/2471|||> > However, I don't why my tempdb size
> > keeps growing and can not be shrinked.
> http://www.aspfaq.com/2446
> http://www.aspfaq.com/2471
>
Very helpful link. However, when I ran the following query:
USE tempdb
GO
EXEC sp_spaceused @.updateusage = 'TRUE'
I got the result like:
database_name
database_size unallocated space
--- --
-- --
tempdb
4425.94 MB 4140.27 MB
reserved data index_size unused
-- -- -- --
624 KB 216 KB 320 KB 88 KB
My questions are:
1. What does the unallocated space mean? Free space? Unused space? Why I can
not reclaim the such space by shrinking db?
2.The data and index add up together are only 216 + 320 = 536 KB. It is less
than 1 MB. Why my tempdb is so large (> 4GB)? Any hint?
Thanks|||The possible reasons for tempdb growth are listed in that article. Why is
your tempdb so large? I have no idea. Probably one or more of those
reasons. Why can't you shrink it? I have no idea. What else is going on
in the system? What command are you using to shrink the database? Does it
complete successfully, or do you get an error? If you get an error, what is
it?
--
http://www.aspfaq.com/
(Reverse address to reply.)
"FLX" <nospam@.hotmail.com> wrote in message
news:uDHsq7H2EHA.936@.TK2MSFTNGP12.phx.gbl...
> > > However, I don't why my tempdb size
> > > keeps growing and can not be shrinked.
> >
> > http://www.aspfaq.com/2446
> > http://www.aspfaq.com/2471
> >
> Very helpful link. However, when I ran the following query:
> USE tempdb
> GO
> EXEC sp_spaceused @.updateusage = 'TRUE'
> I got the result like:
> database_name
> database_size unallocated space
> --- --
--
> -- --
> tempdb
> 4425.94 MB 4140.27 MB
>
> reserved data index_size unused
> -- -- -- --
> 624 KB 216 KB 320 KB 88 KB
>
> My questions are:
> 1. What does the unallocated space mean? Free space? Unused space? Why I
can
> not reclaim the such space by shrinking db?
> 2.The data and index add up together are only 216 + 320 = 536 KB. It is
less
> than 1 MB. Why my tempdb is so large (> 4GB)? Any hint?
> Thanks
>
>
>
Check Temp table size?
I have a tempdb which fills up the disk frequently. I want to determine the
size of each object in the tempdb.
I used EXEC sp_spaceused '#temptablename'. I got the "#temptablename" does
not exist error. I got the temp table names from INFORMATION_SCHEMA.TABLES.
Since the temp tables only valid for their own session, how can I check its
size of temp tables through Query Analyzer?
Another question is how to analyze the log file space usage? The log file
for my TempDB grows dramatically fast too. I need understand the reason.
Thanks a lot,
FLEXEC tempdb..spaceused '#temptablename'
http://www.aspfaq.com/
(Reverse address to reply.)
"FLX" <nospam@.hotmail.com> wrote in message
news:e$uxjy91EHA.3368@.TK2MSFTNGP10.phx.gbl...
> I have a tempdb which fills up the disk frequently. I want to determine
the
> size of each object in the tempdb.
> I used EXEC sp_spaceused '#temptablename'. I got the "#temptablename" does
> not exist error. I got the temp table names from
INFORMATION_SCHEMA.TABLES.
> Since the temp tables only valid for their own session, how can I check
its
> size of temp tables through Query Analyzer?
> Another question is how to analyze the log file space usage? The log file
> for my TempDB grows dramatically fast too. I need understand the reason.
> Thanks a lot,
> FL
>
>
>
>|||Thanks.
It did not solve my problem. Maybe my question was not clear. The
#temptablename was created in the stored procedure. Since it is a local temp
table, I can not access it from the Query Analyzer. What is the workaround?
I know if it is a global temp table I can access it.
FL
"Aaron [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:%23sIQO391EHA.1396@.tk2msftngp13.phx.gbl...
> EXEC tempdb..spaceused '#temptablename'
> --
> http://www.aspfaq.com/
> (Reverse address to reply.)
>
>
> "FLX" <nospam@.hotmail.com> wrote in message
> news:e$uxjy91EHA.3368@.TK2MSFTNGP10.phx.gbl...
> the
does[vbcol=seagreen]
> INFORMATION_SCHEMA.TABLES.
> its
file[vbcol=seagreen]
>|||You want to check the size of a temp table created in a stored procedure,
from query analyzer OUTSIDE the scope of the stored procedure?
I don't think this is possible, unless you manually inspect
tempdb..sysobjects and guess which table is from the specific stored
procedure scope you are interested in. What do you expect will happen when
the stored procedure is being executed by 12 different people
simultaneously? Since the temp table only lives for the scope of the stored
procedure, why do you care aout its size OUTSIDE of that scope?
http://www.aspfaq.com/
(Reverse address to reply.)
"FLX" <nospam@.hotmail.com> wrote in message
news:u46sZM$1EHA.4004@.tk2msftngp13.phx.gbl...
> Thanks.
> It did not solve my problem. Maybe my question was not clear. The
> #temptablename was created in the stored procedure. Since it is a local
temp
> table, I can not access it from the Query Analyzer. What is the
workaround?
> I know if it is a global temp table I can access it.
> FL
>
>
>
> "Aaron [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
> news:%23sIQO391EHA.1396@.tk2msftngp13.phx.gbl...
determine[vbcol=seagreen]
> does
check[vbcol=seagreen]
> file
reason.[vbcol=seagreen]
>|||> I don't think this is possible, unless you manually inspect
> tempdb..sysobjects and guess which table is from the specific stored
> procedure scope you are interested in.
This is unfortunate.
>What do you expect will happen when
> the stored procedure is being executed by 12 different people
> simultaneously?
I expect 12 temp tables with unique Table Names stored in the database.
Let's say there is a temp table "#tempTable" is created in the stored
procedure. Two people simultaneously execute the stored procedure. When I
ran the following query,
use tempdb
go
select *
FROM INFORMATION_SCHEMA.TABLES
In the tablename column, I saw:
#tempTable______________________________
____________________________________
________________________________________
_000000058617
#tempTable______________________________
____________________________________
________________________________________
_000000058628
Please notice the appendix are different.
>Since the temp table only lives for the scope of the stored
> procedure, why do you care aout its size OUTSIDE of that scope?
My tempdb is too large and I want to figure out the cause. One obvious
reason is that we used too many temp tables created in the stored
procedures. I know theoritically the temp table will be automatically
dropped when the stored procedure exits. However, I don't why my tempdb size
keeps growing and can not be shrinked.
Thanks a lot.|||>
#tempTable______________________________
____________________________________[vbc
ol=seagreen]
> ________________________________________
_000000058617
>[/vbcol]
#tempTable______________________________
____________________________________[vbc
ol=seagreen]
> ________________________________________
_000000058628
> Please notice the appendix are different.[/vbcol]
Yes, those suffixes are generated by SQL Server internally to track #temp
tables from different sessions. Again, you would have to guess which one is
which.
> However, I don't why my tempdb size
> keeps growing and can not be shrinked.
http://www.aspfaq.com/2446
http://www.aspfaq.com/2471|||> > However, I don't why my tempdb size
> http://www.aspfaq.com/2446
> http://www.aspfaq.com/2471
>
Very helpful link. However, when I ran the following query:
USE tempdb
GO
EXEC sp_spaceused @.updateusage = 'TRUE'
I got the result like:
database_name
database_size unallocated space
--- --
-- --
tempdb
4425.94 MB 4140.27 MB
reserved data index_size unused
-- -- -- --
624 KB 216 KB 320 KB 88 KB
My questions are:
1. What does the unallocated space mean? Free space? Unused space? Why I can
not reclaim the such space by shrinking db?
2.The data and index add up together are only 216 + 320 = 536 KB. It is less
than 1 MB. Why my tempdb is so large (> 4GB)? Any hint?
Thanks|||The possible reasons for tempdb growth are listed in that article. Why is
your tempdb so large? I have no idea. Probably one or more of those
reasons. Why can't you shrink it? I have no idea. What else is going on
in the system? What command are you using to shrink the database? Does it
complete successfully, or do you get an error? If you get an error, what is
it?
http://www.aspfaq.com/
(Reverse address to reply.)
"FLX" <nospam@.hotmail.com> wrote in message
news:uDHsq7H2EHA.936@.TK2MSFTNGP12.phx.gbl...
> Very helpful link. However, when I ran the following query:
> USE tempdb
> GO
> EXEC sp_spaceused @.updateusage = 'TRUE'
> I got the result like:
> database_name
> database_size unallocated space
> --- --
--
> -- --
> tempdb
> 4425.94 MB 4140.27 MB
>
> reserved data index_size unused
> -- -- -- --
> 624 KB 216 KB 320 KB 88 KB
>
> My questions are:
> 1. What does the unallocated space mean? Free space? Unused space? Why I
can
> not reclaim the such space by shrinking db?
> 2.The data and index add up together are only 216 + 320 = 536 KB. It is
less
> than 1 MB. Why my tempdb is so large (> 4GB)? Any hint?
> Thanks
>
>
>
size of each object in the tempdb.
I used EXEC sp_spaceused '#temptablename'. I got the "#temptablename" does
not exist error. I got the temp table names from INFORMATION_SCHEMA.TABLES.
Since the temp tables only valid for their own session, how can I check its
size of temp tables through Query Analyzer?
Another question is how to analyze the log file space usage? The log file
for my TempDB grows dramatically fast too. I need understand the reason.
Thanks a lot,
FLEXEC tempdb..spaceused '#temptablename'
http://www.aspfaq.com/
(Reverse address to reply.)
"FLX" <nospam@.hotmail.com> wrote in message
news:e$uxjy91EHA.3368@.TK2MSFTNGP10.phx.gbl...
> I have a tempdb which fills up the disk frequently. I want to determine
the
> size of each object in the tempdb.
> I used EXEC sp_spaceused '#temptablename'. I got the "#temptablename" does
> not exist error. I got the temp table names from
INFORMATION_SCHEMA.TABLES.
> Since the temp tables only valid for their own session, how can I check
its
> size of temp tables through Query Analyzer?
> Another question is how to analyze the log file space usage? The log file
> for my TempDB grows dramatically fast too. I need understand the reason.
> Thanks a lot,
> FL
>
>
>
>|||Thanks.
It did not solve my problem. Maybe my question was not clear. The
#temptablename was created in the stored procedure. Since it is a local temp
table, I can not access it from the Query Analyzer. What is the workaround?
I know if it is a global temp table I can access it.
FL
"Aaron [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:%23sIQO391EHA.1396@.tk2msftngp13.phx.gbl...
> EXEC tempdb..spaceused '#temptablename'
> --
> http://www.aspfaq.com/
> (Reverse address to reply.)
>
>
> "FLX" <nospam@.hotmail.com> wrote in message
> news:e$uxjy91EHA.3368@.TK2MSFTNGP10.phx.gbl...
> the
does[vbcol=seagreen]
> INFORMATION_SCHEMA.TABLES.
> its
file[vbcol=seagreen]
>|||You want to check the size of a temp table created in a stored procedure,
from query analyzer OUTSIDE the scope of the stored procedure?
I don't think this is possible, unless you manually inspect
tempdb..sysobjects and guess which table is from the specific stored
procedure scope you are interested in. What do you expect will happen when
the stored procedure is being executed by 12 different people
simultaneously? Since the temp table only lives for the scope of the stored
procedure, why do you care aout its size OUTSIDE of that scope?
http://www.aspfaq.com/
(Reverse address to reply.)
"FLX" <nospam@.hotmail.com> wrote in message
news:u46sZM$1EHA.4004@.tk2msftngp13.phx.gbl...
> Thanks.
> It did not solve my problem. Maybe my question was not clear. The
> #temptablename was created in the stored procedure. Since it is a local
temp
> table, I can not access it from the Query Analyzer. What is the
workaround?
> I know if it is a global temp table I can access it.
> FL
>
>
>
> "Aaron [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
> news:%23sIQO391EHA.1396@.tk2msftngp13.phx.gbl...
determine[vbcol=seagreen]
> does
check[vbcol=seagreen]
> file
reason.[vbcol=seagreen]
>|||> I don't think this is possible, unless you manually inspect
> tempdb..sysobjects and guess which table is from the specific stored
> procedure scope you are interested in.
This is unfortunate.
>What do you expect will happen when
> the stored procedure is being executed by 12 different people
> simultaneously?
I expect 12 temp tables with unique Table Names stored in the database.
Let's say there is a temp table "#tempTable" is created in the stored
procedure. Two people simultaneously execute the stored procedure. When I
ran the following query,
use tempdb
go
select *
FROM INFORMATION_SCHEMA.TABLES
In the tablename column, I saw:
#tempTable______________________________
____________________________________
________________________________________
_000000058617
#tempTable______________________________
____________________________________
________________________________________
_000000058628
Please notice the appendix are different.
>Since the temp table only lives for the scope of the stored
> procedure, why do you care aout its size OUTSIDE of that scope?
My tempdb is too large and I want to figure out the cause. One obvious
reason is that we used too many temp tables created in the stored
procedures. I know theoritically the temp table will be automatically
dropped when the stored procedure exits. However, I don't why my tempdb size
keeps growing and can not be shrinked.
Thanks a lot.|||>
#tempTable______________________________
____________________________________[vbc
ol=seagreen]
> ________________________________________
_000000058617
>[/vbcol]
#tempTable______________________________
____________________________________[vbc
ol=seagreen]
> ________________________________________
_000000058628
> Please notice the appendix are different.[/vbcol]
Yes, those suffixes are generated by SQL Server internally to track #temp
tables from different sessions. Again, you would have to guess which one is
which.
> However, I don't why my tempdb size
> keeps growing and can not be shrinked.
http://www.aspfaq.com/2446
http://www.aspfaq.com/2471|||> > However, I don't why my tempdb size
> http://www.aspfaq.com/2446
> http://www.aspfaq.com/2471
>
Very helpful link. However, when I ran the following query:
USE tempdb
GO
EXEC sp_spaceused @.updateusage = 'TRUE'
I got the result like:
database_name
database_size unallocated space
--- --
-- --
tempdb
4425.94 MB 4140.27 MB
reserved data index_size unused
-- -- -- --
624 KB 216 KB 320 KB 88 KB
My questions are:
1. What does the unallocated space mean? Free space? Unused space? Why I can
not reclaim the such space by shrinking db?
2.The data and index add up together are only 216 + 320 = 536 KB. It is less
than 1 MB. Why my tempdb is so large (> 4GB)? Any hint?
Thanks|||The possible reasons for tempdb growth are listed in that article. Why is
your tempdb so large? I have no idea. Probably one or more of those
reasons. Why can't you shrink it? I have no idea. What else is going on
in the system? What command are you using to shrink the database? Does it
complete successfully, or do you get an error? If you get an error, what is
it?
http://www.aspfaq.com/
(Reverse address to reply.)
"FLX" <nospam@.hotmail.com> wrote in message
news:uDHsq7H2EHA.936@.TK2MSFTNGP12.phx.gbl...
> Very helpful link. However, when I ran the following query:
> USE tempdb
> GO
> EXEC sp_spaceused @.updateusage = 'TRUE'
> I got the result like:
> database_name
> database_size unallocated space
> --- --
--
> -- --
> tempdb
> 4425.94 MB 4140.27 MB
>
> reserved data index_size unused
> -- -- -- --
> 624 KB 216 KB 320 KB 88 KB
>
> My questions are:
> 1. What does the unallocated space mean? Free space? Unused space? Why I
can
> not reclaim the such space by shrinking db?
> 2.The data and index add up together are only 216 + 320 = 536 KB. It is
less
> than 1 MB. Why my tempdb is so large (> 4GB)? Any hint?
> Thanks
>
>
>
Check Server Disk Space Daily
I know can execute perfmon program to determine disk space on the server.
I would like a simple SQL Server job (SQL Server 2000) I can set up to run
once a day. Check all drives on the server if unused disk space is less than
10% then send alert message.
Please help me with this task.
See if this helps.
Using xp_fixeddrives to Monitor Free Space
http://www.databasejournal.com/features/mssql/article.php/3080501
AMB
"Joe K." wrote:
> I know can execute perfmon program to determine disk space on the server.
> I would like a simple SQL Server job (SQL Server 2000) I can set up to run
> once a day. Check all drives on the server if unused disk space is less than
> 10% then send alert message.
> Please help me with this task.
|||Grab sp_diskspace from here http://www.sqldbatips.com/showcode.asp?ID=4 and
simply insert results into a table and run your check against it.
HTH,
Jasper Smith (SQL Server MVP)
http://www.sqldbatips.com
"Joe K." <JoeK@.discussions.microsoft.com> wrote in message
news:1C11A458-4317-40F0-A4E5-0C07520C88D5@.microsoft.com...
> I know can execute perfmon program to determine disk space on the server.
> I would like a simple SQL Server job (SQL Server 2000) I can set up to run
> once a day. Check all drives on the server if unused disk space is less
> than
> 10% then send alert message.
> Please help me with this task.
I would like a simple SQL Server job (SQL Server 2000) I can set up to run
once a day. Check all drives on the server if unused disk space is less than
10% then send alert message.
Please help me with this task.
See if this helps.
Using xp_fixeddrives to Monitor Free Space
http://www.databasejournal.com/features/mssql/article.php/3080501
AMB
"Joe K." wrote:
> I know can execute perfmon program to determine disk space on the server.
> I would like a simple SQL Server job (SQL Server 2000) I can set up to run
> once a day. Check all drives on the server if unused disk space is less than
> 10% then send alert message.
> Please help me with this task.
|||Grab sp_diskspace from here http://www.sqldbatips.com/showcode.asp?ID=4 and
simply insert results into a table and run your check against it.
HTH,
Jasper Smith (SQL Server MVP)
http://www.sqldbatips.com
"Joe K." <JoeK@.discussions.microsoft.com> wrote in message
news:1C11A458-4317-40F0-A4E5-0C07520C88D5@.microsoft.com...
> I know can execute perfmon program to determine disk space on the server.
> I would like a simple SQL Server job (SQL Server 2000) I can set up to run
> once a day. Check all drives on the server if unused disk space is less
> than
> 10% then send alert message.
> Please help me with this task.
Check Server Disk Space Daily
I know can execute perfmon program to determine disk space on the server.
I would like a simple SQL Server job (SQL Server 2000) I can set up to run
once a day. Check all drives on the server if unused disk space is less than
10% then send alert message.
Please help me with this task.Grab sp_diskspace from here http://www.sqldbatips.com/showcode.asp?ID=4 and
simply insert results into a table and run your check against it.
--
HTH,
Jasper Smith (SQL Server MVP)
http://www.sqldbatips.com
"Joe K." <JoeK@.discussions.microsoft.com> wrote in message
news:1C11A458-4317-40F0-A4E5-0C07520C88D5@.microsoft.com...
> I know can execute perfmon program to determine disk space on the server.
> I would like a simple SQL Server job (SQL Server 2000) I can set up to run
> once a day. Check all drives on the server if unused disk space is less
> than
> 10% then send alert message.
> Please help me with this task.
I would like a simple SQL Server job (SQL Server 2000) I can set up to run
once a day. Check all drives on the server if unused disk space is less than
10% then send alert message.
Please help me with this task.Grab sp_diskspace from here http://www.sqldbatips.com/showcode.asp?ID=4 and
simply insert results into a table and run your check against it.
--
HTH,
Jasper Smith (SQL Server MVP)
http://www.sqldbatips.com
"Joe K." <JoeK@.discussions.microsoft.com> wrote in message
news:1C11A458-4317-40F0-A4E5-0C07520C88D5@.microsoft.com...
> I know can execute perfmon program to determine disk space on the server.
> I would like a simple SQL Server job (SQL Server 2000) I can set up to run
> once a day. Check all drives on the server if unused disk space is less
> than
> 10% then send alert message.
> Please help me with this task.
Subscribe to:
Posts (Atom)