I have a textbox in my page footer with an expression like this: Sum(iif(
(ReportItems!textbox56.Value = "Complete"),1,0)). Basically it counts how
many times "Complete" comes up in a page. I also have a parameter which can
filter out any "Complete" values using the visibility of the detail row. My
problem is that I still want a count of the Complete values, even if they are
filtered out on the page. When I run the report with no filters, it shows me
exactly how many Completes there are per page. However, it will not count
the Completes when they are filtered out by page. How can I get the page
footer to count them even when they are hidden?Sorry, this is not supported in RS 2000. The good news is that this will
change in RS 2005 and conditionally (expression-based) hidden items won't be
taken into account in page header/footer calculations.
-- Robert
This posting is provided "AS IS" with no warranties, and confers no rights.
"dachrist" <dachrist@.discussions.microsoft.com> wrote in message
news:D32037E2-4E92-4BB0-B622-09794DEAF21F@.microsoft.com...
>I have a textbox in my page footer with an expression like this: Sum(iif(
> (ReportItems!textbox56.Value = "Complete"),1,0)). Basically it counts how
> many times "Complete" comes up in a page. I also have a parameter which
> can
> filter out any "Complete" values using the visibility of the detail row.
> My
> problem is that I still want a count of the Complete values, even if they
> are
> filtered out on the page. When I run the report with no filters, it shows
> me
> exactly how many Completes there are per page. However, it will not count
> the Completes when they are filtered out by page. How can I get the page
> footer to count them even when they are hidden?|||Though in simple terms its not possible. But there is work around-
Make custom dll which has a method which takes filter expression as input
and connects to same datasource using ado.net and fetches the same records.
Then calculate no of "complete"s in table and return the value to report.
Please see using custom assemblies in Reporting Services. Its very powerful!
Suneet Moha
-----
"dachrist" wrote:
> I have a textbox in my page footer with an expression like this: Sum(iif(
> (ReportItems!textbox56.Value = "Complete"),1,0)). Basically it counts how
> many times "Complete" comes up in a page. I also have a parameter which can
> filter out any "Complete" values using the visibility of the detail row. My
> problem is that I still want a count of the Complete values, even if they are
> filtered out on the page. When I run the report with no filters, it shows me
> exactly how many Completes there are per page. However, it will not count
> the Completes when they are filtered out by page. How can I get the page
> footer to count them even when they are hidden?
Showing posts with label page. Show all posts
Showing posts with label page. Show all posts
Tuesday, March 27, 2012
Sunday, February 19, 2012
Could not allocate new page for database TEMPDB?
"Could not allocate new page for database 'TEMPDB'. There are no more pages available in filegroup DEFAULT. Space can be created by dropping objects, adding additional files, or allowing file growth."
I get this error when running a query on another database. But why?
Both data and transaction files on TEMPDB are set to "automatically grow file" and "unrestricted file growth" and there is 70GB of free space on the disk drive. Shouldn't the files just grow? Why would this happen?because temporary table store in tempdb|||because temporary table store in tempdb
Thanks for the reply. I don't understand what you are saying but it sounds like you know what the issue is. Could you please elaborate a little bit?|||You say ：I get this error when running a query
you are running a query , can you show me the query ?
do you create temporary table in your query?|||You say ：I get this error when running a query
you are running a query , can you show me the query ?
do you create temporary table in your query?
No, I'm not explicitly doing anything with TEMPDB.
My query is very straight forward:
SELECT TOP 100 SampleDescriptor, COUNT(*) RepCount FROM DataSamples GROUP BY SampleDescriptor ORDER BY RepCount DESC
On my dev system, with 30 test records, this works fine.
On the production server with 188 million records (yes, a LOT of records!), this fails with the TEMPDB error that I mentioned.
I'm trying a simple: SELECT COUNT(*) FROM DataSamples now. It's been running for over 30 minutes and still going. I wonder if it will hit a similar error...|||When you get the error, have you checked the size of tempdb (both data and log) and free space of the hard drive?
I assume that tempdb is located in the hard drive with 70GB free space.|||Yes, of course. The size of both tempdb data and transaction files are 2MB and they are located on the drive with 68GB free. All drives on that system have plenty of space (although for tempdb, D: should be the only one that matters):
C: has 25 GB free
D: has 68 GB free
E: has 124 GB free|||What do sp_spaceused and sp_helpdb return?
BTW, which version and service pack of SQL Server?
I get this error when running a query on another database. But why?
Both data and transaction files on TEMPDB are set to "automatically grow file" and "unrestricted file growth" and there is 70GB of free space on the disk drive. Shouldn't the files just grow? Why would this happen?because temporary table store in tempdb|||because temporary table store in tempdb
Thanks for the reply. I don't understand what you are saying but it sounds like you know what the issue is. Could you please elaborate a little bit?|||You say ：I get this error when running a query
you are running a query , can you show me the query ?
do you create temporary table in your query?|||You say ：I get this error when running a query
you are running a query , can you show me the query ?
do you create temporary table in your query?
No, I'm not explicitly doing anything with TEMPDB.
My query is very straight forward:
SELECT TOP 100 SampleDescriptor, COUNT(*) RepCount FROM DataSamples GROUP BY SampleDescriptor ORDER BY RepCount DESC
On my dev system, with 30 test records, this works fine.
On the production server with 188 million records (yes, a LOT of records!), this fails with the TEMPDB error that I mentioned.
I'm trying a simple: SELECT COUNT(*) FROM DataSamples now. It's been running for over 30 minutes and still going. I wonder if it will hit a similar error...|||When you get the error, have you checked the size of tempdb (both data and log) and free space of the hard drive?
I assume that tempdb is located in the hard drive with 70GB free space.|||Yes, of course. The size of both tempdb data and transaction files are 2MB and they are located on the drive with 68GB free. All drives on that system have plenty of space (although for tempdb, D: should be the only one that matters):
C: has 25 GB free
D: has 68 GB free
E: has 124 GB free|||What do sp_spaceused and sp_helpdb return?
BTW, which version and service pack of SQL Server?
Could not allocate new page for database 'TEMPDB'.
I recently had a routine using a table variable involved in a fairly
complex query using a number of fairly large tables which ended with
the following error.
Server: Msg 1101, Level 17, State 10, Line 40
Could not allocate new page for database 'TEMPDB'. There are no more
pages
available in filegroup DEFAULT. Space can be created by dropping
objects,
adding additional files, or allowing file growth.
The tempdb is on autogrow.
The drive on which the tempdb resided was filling up after running for
a number of minutes. The drive had a couple of GB of space left until
the routine started. I looked into it a bit and read that temp
tables were better performers with larger tables than table vars so
tried using a temp table instead. The routine finished in a timely
manner without expanding the tempdb significantly from it's previous
size before the routines initial run(rebuilt tempdb). Any idea why the
big difference in performance and effect on the tempdb? The rest of
the routine remainded the same with the exception of the table var -->
temp table.
ThanksUse of temp tables and table v ariables will affect the query plans used
in a procedure.
It is likely that the use of the table variables has resulted in a query
plan that involved heavy use of worktables i.e sorting and thus the filling
up of tempdb.
If you can post the query plans for the different code (and the code) it
migth help to diagnose the problem.
> I recently had a routine using a table variable involved in a fairly
> complex query using a number of fairly large tables which ended with
> the following error.
> Server: Msg 1101, Level 17, State 10, Line 40
> Could not allocate new page for database 'TEMPDB'. There are no more
> pages
> available in filegroup DEFAULT. Space can be created by dropping
> objects,
> adding additional files, or allowing file growth.
> The tempdb is on autogrow.
> The drive on which the tempdb resided was filling up after running for
> a number of minutes. The drive had a couple of GB of space left until
> the routine started. I looked into it a bit and read that temp
> tables were better performers with larger tables than table vars so
> tried using a temp table instead. The routine finished in a timely
> manner without expanding the tempdb significantly from it's previous
> size before the routines initial run(rebuilt tempdb). Any idea why
> the
> big difference in performance and effect on the tempdb? The rest of
> the routine remainded the same with the exception of the table var -->
> temp table.
> Thanks
>|||I took a look at the query plans, they are identical. Odd thing is
that this used to run on the same environment in a timely manner using
the table var a w
ago. Finding out the truth here may be difficult
as our application is being hosted by a client and we do not have
security permission to view their sensitive data.
Thanks for the post!
complex query using a number of fairly large tables which ended with
the following error.
Server: Msg 1101, Level 17, State 10, Line 40
Could not allocate new page for database 'TEMPDB'. There are no more
pages
available in filegroup DEFAULT. Space can be created by dropping
objects,
adding additional files, or allowing file growth.
The tempdb is on autogrow.
The drive on which the tempdb resided was filling up after running for
a number of minutes. The drive had a couple of GB of space left until
the routine started. I looked into it a bit and read that temp
tables were better performers with larger tables than table vars so
tried using a temp table instead. The routine finished in a timely
manner without expanding the tempdb significantly from it's previous
size before the routines initial run(rebuilt tempdb). Any idea why the
big difference in performance and effect on the tempdb? The rest of
the routine remainded the same with the exception of the table var -->
temp table.
ThanksUse of temp tables and table v ariables will affect the query plans used
in a procedure.
It is likely that the use of the table variables has resulted in a query
plan that involved heavy use of worktables i.e sorting and thus the filling
up of tempdb.
If you can post the query plans for the different code (and the code) it
migth help to diagnose the problem.
> I recently had a routine using a table variable involved in a fairly
> complex query using a number of fairly large tables which ended with
> the following error.
> Server: Msg 1101, Level 17, State 10, Line 40
> Could not allocate new page for database 'TEMPDB'. There are no more
> pages
> available in filegroup DEFAULT. Space can be created by dropping
> objects,
> adding additional files, or allowing file growth.
> The tempdb is on autogrow.
> The drive on which the tempdb resided was filling up after running for
> a number of minutes. The drive had a couple of GB of space left until
> the routine started. I looked into it a bit and read that temp
> tables were better performers with larger tables than table vars so
> tried using a temp table instead. The routine finished in a timely
> manner without expanding the tempdb significantly from it's previous
> size before the routines initial run(rebuilt tempdb). Any idea why
> the
> big difference in performance and effect on the tempdb? The rest of
> the routine remainded the same with the exception of the table var -->
> temp table.
> Thanks
>|||I took a look at the query plans, they are identical. Odd thing is
that this used to run on the same environment in a timely manner using
the table var a w
ago. Finding out the truth here may be difficultas our application is being hosted by a client and we do not have
security permission to view their sensitive data.
Thanks for the post!
Could not allocate new page for database TEMPDB.
I tried to create a new table in VIEW to accomplish some urgent work and I have received the following message:
Server: Msg 1101, Level 17, State 10, Line 1
Could not allocate new page for database 'TEMPDB'. There are no more pages available in filegroup DEFAULT. Space can be created by dropping objects, adding additional files, or allowing file growth.
It should be recognized that the MS SQL Server performance is slower than before. What files the TEMPDB contains, which need to be cleared from time to time, and how can I solve this above mentioned matter.
Thank you.Hi,
execute following statment;
use tempdb
go
sp_helpfile
If growth is set to 0 (Zero), your tempdb can't allocate more space. See books online to read how to change growth and how to allocate.
/Mada|||Thank you !!
I will try to use it and I will search for books online to have the solution.
Best regards.
Server: Msg 1101, Level 17, State 10, Line 1
Could not allocate new page for database 'TEMPDB'. There are no more pages available in filegroup DEFAULT. Space can be created by dropping objects, adding additional files, or allowing file growth.
It should be recognized that the MS SQL Server performance is slower than before. What files the TEMPDB contains, which need to be cleared from time to time, and how can I solve this above mentioned matter.
Thank you.Hi,
execute following statment;
use tempdb
go
sp_helpfile
If growth is set to 0 (Zero), your tempdb can't allocate more space. See books online to read how to change growth and how to allocate.
/Mada|||Thank you !!
I will try to use it and I will search for books online to have the solution.
Best regards.
Could not allocate new page for database TEMPDB
I get the following error when doing a variety of basic queries on other databases:
"Could not allocate new page for database 'TEMPDB'. There are no more pages available in filegroup DEFAULT. Space can be created by dropping objects, adding additional files, or allowing file growth."
This doesn't make any sense since they are set to auto grow and there is plenty of disk space to do so.
Both data and transaction files of tempdb are set to:
"Automatically grow file" is checked
"Maximum file size" is set to "Unrestricted file growth"
Growth rate of 10%
Both tempdb data file and transaction file are on D: but all drives have ample space:
c: 25 GB free
D: 69 GB free
E: 175 GB free
sp_spaceused returns the following for tempdb:
name size unallocated space
tempdb 4.00 MB 1.45 MB
reserved data index_size unused
568 KB 176 KB 304 KB 88 KB
sp_helpdb returned for tempdb:
tempdb
4.00 MB sa 2 Apr 23 2004 Status=ONLINE, Updateability=READ_WRITE, UserAccess=MULTI_USER, Recovery=SIMPLE, Version=539, Collation=SQL_Latin1_General_CP1_CI_AS, SQLSortOrder=52, IsAutoCreateStatistics, IsAutoUpdateStatistics 80
This is with SQL Server 2000 (Personal Edition)
Product Version: 8.00.760 (SP3)
Is this some strange limitation of Personal Edition? Is it worth upgrading to Standard Edition?doesn't have anything to do with edition, it's your transaction. if it affects a lot of data, and in your case if work tables are needed to be created (this happens in tempdb) for sorting or grouping purposes, or if your indexes created with sort_in_tempdb, the data device will continue to grow for as much as it's needed based on your increment setting (default of 10% is not a good idea) once the limit is reached but the transaction has not completed, - it gets rolled back and the allocated size goes back to almost 0. if the "auto shrink" option is set, upon completion of rollback the allocated size goes down too. this explains why your sp_spaceused shows such a small number.|||doesn't have anything to do with edition, it's your transaction. if it affects a lot of data, and in your case if work tables are needed to be created (this happens in tempdb) for sorting or grouping purposes, or if your indexes created with sort_in_tempdb, the data device will continue to grow for as much as it's needed based on your increment setting (default of 10% is not a good idea) once the limit is reached but the transaction has not completed, - it gets rolled back and the allocated size goes back to almost 0. if the "auto shrink" option is set, upon completion of rollback the allocated size goes down too. this explains why your sp_spaceused shows such a small number.
Bingo! The query actually caused tempdb to use the whole 27GB available on the C: drive, failed, shrunk to a few MB, and gave the illusion that space wasn't an issue. I moved tempdb to the E: drive, and it works perfectly.
Thanks!
"Could not allocate new page for database 'TEMPDB'. There are no more pages available in filegroup DEFAULT. Space can be created by dropping objects, adding additional files, or allowing file growth."
This doesn't make any sense since they are set to auto grow and there is plenty of disk space to do so.
Both data and transaction files of tempdb are set to:
"Automatically grow file" is checked
"Maximum file size" is set to "Unrestricted file growth"
Growth rate of 10%
Both tempdb data file and transaction file are on D: but all drives have ample space:
c: 25 GB free
D: 69 GB free
E: 175 GB free
sp_spaceused returns the following for tempdb:
name size unallocated space
tempdb 4.00 MB 1.45 MB
reserved data index_size unused
568 KB 176 KB 304 KB 88 KB
sp_helpdb returned for tempdb:
tempdb
4.00 MB sa 2 Apr 23 2004 Status=ONLINE, Updateability=READ_WRITE, UserAccess=MULTI_USER, Recovery=SIMPLE, Version=539, Collation=SQL_Latin1_General_CP1_CI_AS, SQLSortOrder=52, IsAutoCreateStatistics, IsAutoUpdateStatistics 80
This is with SQL Server 2000 (Personal Edition)
Product Version: 8.00.760 (SP3)
Is this some strange limitation of Personal Edition? Is it worth upgrading to Standard Edition?doesn't have anything to do with edition, it's your transaction. if it affects a lot of data, and in your case if work tables are needed to be created (this happens in tempdb) for sorting or grouping purposes, or if your indexes created with sort_in_tempdb, the data device will continue to grow for as much as it's needed based on your increment setting (default of 10% is not a good idea) once the limit is reached but the transaction has not completed, - it gets rolled back and the allocated size goes back to almost 0. if the "auto shrink" option is set, upon completion of rollback the allocated size goes down too. this explains why your sp_spaceused shows such a small number.|||doesn't have anything to do with edition, it's your transaction. if it affects a lot of data, and in your case if work tables are needed to be created (this happens in tempdb) for sorting or grouping purposes, or if your indexes created with sort_in_tempdb, the data device will continue to grow for as much as it's needed based on your increment setting (default of 10% is not a good idea) once the limit is reached but the transaction has not completed, - it gets rolled back and the allocated size goes back to almost 0. if the "auto shrink" option is set, upon completion of rollback the allocated size goes down too. this explains why your sp_spaceused shows such a small number.
Bingo! The query actually caused tempdb to use the whole 27GB available on the C: drive, failed, shrunk to a few MB, and gave the illusion that space wasn't an issue. I moved tempdb to the E: drive, and it works perfectly.
Thanks!
Tuesday, February 14, 2012
Corrupted distribution.mdf
We have started getting the following in the application event log:
Error: 823, Severity: 24, State: 2
I/O error (torn page) detected during read at offset 0x00000015ba000 in file
'd:\sqldata\MSSQL\Data\distribution.MDF'.
and is being generated during distribution cleanup.
We have NO backup of the distribution database.
How can I repair the database?
In hopes to recreate replication, I've tried to disable publishing and
distribution, but I get the following error:
SQL Server Enterprice manager could not disable the distribution on xxxxx
Error 823: I/O error (torn page) detected during read at offset
0x0000001115ba00 in file .../distribution.MDF
Also ran DBCC CHECKDB and get:
... CHECKDB found 0 allocation errors and 2 consistency errors in database
'distribution'.
repair_allow_data_loss is the minimum repair level for the errors found by
DBCC CHECKDB (distribution ) ...
It's seems I can't run the repair_allow_data_loss on the the distribution
database even if in single user mode. (tried starting in single user mode,
but can't get in query analyzer to run the repair - is there another way?)
Any Ideas on repairing/rebuilding the database would be appreciated.
resolved myself by:
- generate creation script to recreate current replication configuration
- manually deleting distribution database
- disable publishing and distribution
- run creation script
Error: 823, Severity: 24, State: 2
I/O error (torn page) detected during read at offset 0x00000015ba000 in file
'd:\sqldata\MSSQL\Data\distribution.MDF'.
and is being generated during distribution cleanup.
We have NO backup of the distribution database.
How can I repair the database?
In hopes to recreate replication, I've tried to disable publishing and
distribution, but I get the following error:
SQL Server Enterprice manager could not disable the distribution on xxxxx
Error 823: I/O error (torn page) detected during read at offset
0x0000001115ba00 in file .../distribution.MDF
Also ran DBCC CHECKDB and get:
... CHECKDB found 0 allocation errors and 2 consistency errors in database
'distribution'.
repair_allow_data_loss is the minimum repair level for the errors found by
DBCC CHECKDB (distribution ) ...
It's seems I can't run the repair_allow_data_loss on the the distribution
database even if in single user mode. (tried starting in single user mode,
but can't get in query analyzer to run the repair - is there another way?)
Any Ideas on repairing/rebuilding the database would be appreciated.
resolved myself by:
- generate creation script to recreate current replication configuration
- manually deleting distribution database
- disable publishing and distribution
- run creation script
Subscribe to:
Posts (Atom)