estimating tempdb usage

Is there any way to estimate how much space will be needed by tempdb?
I have a user executing a simple query similar to:
select orders.customerid, sum(quantity*unitprice) as amount
from orders inner join [order details]
on orders.orderid=[order details].orderid
group by orders.customerid
In other words, two thin (not too many columns) tables, equi-join,
summarizing and a group by. The only trouble is her two tables have
28000 rows and nearly 800 million rows. Her query dies after a few
hours when tempdb autogrows and runs out of disk space at 17 gig. I
know tempdb is used for work tables, joins, sorts, group bys, etc.
But knowing the columns sizes and number of rows, is there a way to
estimate how much tempdb will be needed? She's basically the only
user on the system.
By the way, this is SQL Server 2000.
Thanks,
ScottI guess it would be better to Grow the tempdb first, then run oyur query.
I had the same problem on SQL Server 6.5 to 2000 migration with the log
files (we had big tables as well) , the Autogrowth didn´t work. So we had to
create the database and transaction logs big enough to support migration
process before initiate it.
HTH
"scott parmelee" <s_parmelee@.hotmail.com> escreveu na mensagem
news:e14a8116.0311130819.725c0a4@.posting.google.com...
> Is there any way to estimate how much space will be needed by tempdb?
> I have a user executing a simple query similar to:
> select orders.customerid, sum(quantity*unitprice) as amount
> from orders inner join [order details]
> on orders.orderid=[order details].orderid
> group by orders.customerid
> In other words, two thin (not too many columns) tables, equi-join,
> summarizing and a group by. The only trouble is her two tables have
> 28000 rows and nearly 800 million rows. Her query dies after a few
> hours when tempdb autogrows and runs out of disk space at 17 gig. I
> know tempdb is used for work tables, joins, sorts, group bys, etc.
> But knowing the columns sizes and number of rows, is there a way to
> estimate how much tempdb will be needed? She's basically the only
> user on the system.
> By the way, this is SQL Server 2000.
> Thanks,
> Scott

Estimating Log File Size

Dear All,
Yesterday I was given a sp to calculate the size of tables
and the overall size of data files.
Is there something similar for log files, i.e. an
algorithm to calculate the size now and say 2 years time
of a log file ?
Thanks
Jim
Jimbo
Look at sp_helpfile as well as sysfiles system table
It is hard to estimate what is your log file will be in the next two years.
"Jimbo" <anonymous@.discussions.microsoft.com> wrote in message
news:33ff01c47ec6$197af750$a501280a@.phx.gbl...
> Dear All,
> Yesterday I was given a sp to calculate the size of tables
> and the overall size of data files.
> Is there something similar for log files, i.e. an
> algorithm to calculate the size now and say 2 years time
> of a log file ?
> Thanks
> Jim
|||Probably the best way to estimate future sizes is to capture growth over
time and predict from that...
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Jimbo" <anonymous@.discussions.microsoft.com> wrote in message
news:33ff01c47ec6$197af750$a501280a@.phx.gbl...
> Dear All,
> Yesterday I was given a sp to calculate the size of tables
> and the overall size of data files.
> Is there something similar for log files, i.e. an
> algorithm to calculate the size now and say 2 years time
> of a log file ?
> Thanks
> Jim
|||Hi,
You could also use the below command to get current log size and usage
dbcc sqlperf(logspace)
Estimating the log size for next 2 years will be hard.. That depends up on
the amount of batch operation and frequency in which
you perform the transaction log backup. Normally it is not required to
project Log size because the log file will be cleared once
you perform the transaction log backup.
Thanks
Hari
MCDBA
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:eFgFomsfEHA.3932@.TK2MSFTNGP09.phx.gbl...
> Jimbo
> Look at sp_helpfile as well as sysfiles system table
> It is hard to estimate what is your log file will be in the next two
years.
>
> "Jimbo" <anonymous@.discussions.microsoft.com> wrote in message
> news:33ff01c47ec6$197af750$a501280a@.phx.gbl...
>

Estimating Log File Size

Dear All,
Yesterday I was given a sp to calculate the size of tables
and the overall size of data files.
Is there something similar for log files, i.e. an
algorithm to calculate the size now and say 2 years time
of a log file ?
Thanks
JimJimbo
Look at sp_helpfile as well as sysfiles system table
It is hard to estimate what is your log file will be in the next two years.
"Jimbo" <anonymous@.discussions.microsoft.com> wrote in message
news:33ff01c47ec6$197af750$a501280a@.phx.gbl...
> Dear All,
> Yesterday I was given a sp to calculate the size of tables
> and the overall size of data files.
> Is there something similar for log files, i.e. an
> algorithm to calculate the size now and say 2 years time
> of a log file ?
> Thanks
> Jim|||Probably the best way to estimate future sizes is to capture growth over
time and predict from that...
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Jimbo" <anonymous@.discussions.microsoft.com> wrote in message
news:33ff01c47ec6$197af750$a501280a@.phx.gbl...
> Dear All,
> Yesterday I was given a sp to calculate the size of tables
> and the overall size of data files.
> Is there something similar for log files, i.e. an
> algorithm to calculate the size now and say 2 years time
> of a log file ?
> Thanks
> Jim|||Hi,
You could also use the below command to get current log size and usage
dbcc sqlperf(logspace)
Estimating the log size for next 2 years will be hard.. That depends up on
the amount of batch operation and frequency in which
you perform the transaction log backup. Normally it is not required to
project Log size because the log file will be cleared once
you perform the transaction log backup.
Thanks
Hari
MCDBA
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:eFgFomsfEHA.3932@.TK2MSFTNGP09.phx.gbl...
> Jimbo
> Look at sp_helpfile as well as sysfiles system table
> It is hard to estimate what is your log file will be in the next two
years.
>
> "Jimbo" <anonymous@.discussions.microsoft.com> wrote in message
> news:33ff01c47ec6$197af750$a501280a@.phx.gbl...
>

Estimating Log File Size

Dear All,
Yesterday I was given a sp to calculate the size of tables
and the overall size of data files.
Is there something similar for log files, i.e. an
algorithm to calculate the size now and say 2 years time
of a log file ?
Thanks
JimJimbo
Look at sp_helpfile as well as sysfiles system table
It is hard to estimate what is your log file will be in the next two years.
"Jimbo" <anonymous@.discussions.microsoft.com> wrote in message
news:33ff01c47ec6$197af750$a501280a@.phx.gbl...
> Dear All,
> Yesterday I was given a sp to calculate the size of tables
> and the overall size of data files.
> Is there something similar for log files, i.e. an
> algorithm to calculate the size now and say 2 years time
> of a log file ?
> Thanks
> Jim|||Probably the best way to estimate future sizes is to capture growth over
time and predict from that...
--
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Jimbo" <anonymous@.discussions.microsoft.com> wrote in message
news:33ff01c47ec6$197af750$a501280a@.phx.gbl...
> Dear All,
> Yesterday I was given a sp to calculate the size of tables
> and the overall size of data files.
> Is there something similar for log files, i.e. an
> algorithm to calculate the size now and say 2 years time
> of a log file ?
> Thanks
> Jim|||Hi,
You could also use the below command to get current log size and usage
dbcc sqlperf(logspace)
Estimating the log size for next 2 years will be hard.. That depends up on
the amount of batch operation and frequency in which
you perform the transaction log backup. Normally it is not required to
project Log size because the log file will be cleared once
you perform the transaction log backup.
Thanks
Hari
MCDBA
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:eFgFomsfEHA.3932@.TK2MSFTNGP09.phx.gbl...
> Jimbo
> Look at sp_helpfile as well as sysfiles system table
> It is hard to estimate what is your log file will be in the next two
years.
>
> "Jimbo" <anonymous@.discussions.microsoft.com> wrote in message
> news:33ff01c47ec6$197af750$a501280a@.phx.gbl...
> > Dear All,
> >
> > Yesterday I was given a sp to calculate the size of tables
> > and the overall size of data files.
> >
> > Is there something similar for log files, i.e. an
> > algorithm to calculate the size now and say 2 years time
> > of a log file ?
> >
> > Thanks
> > Jim
>

Errors during import and export

I am unable to import anything from one sql 2000 database to another using the 2005 Management Studio - When I try I get an error similar to this -

Could not set up data flow connections.

The connection type "OLEDB" specified for connection manager {ccccc}" is not recognized as a valid connection manager type. This error is returned when an attempt is made to create a connection manager for an unknown connection type. Check the spelling in the connection type name.
(SQL Server Import and Export Wizard)

Thanks Richard

I saw this solution on another MSDN forum which worked for me:

"It is a SSIS issue.

Open SQL Server Configuration Manager. Right click on SQL Server

Integration Services. Select properties and in the Log On tab change

the built-in account."

I'm only a newbie to this so I don't know what the implications of this change are, other than I was able to import my Access Db.

Errors during import and export

I am unable to import anything from one sql 2000 database to another using the 2005 Management Studio - When I try I get an error similar to this -

Could not set up data flow connections.

The connection type "OLEDB" specified for connection manager {ccccc}" is not recognized as a valid connection manager type. This error is returned when an attempt is made to create a connection manager for an unknown connection type. Check the spelling in the connection type name.
(SQL Server Import and Export Wizard)

Thanks Richard

I saw this solution on another MSDN forum which worked for me:

"It is a SSIS issue. Open SQL Server Configuration Manager. Right click on SQL Server Integration Services. Select properties and in the Log On tab change the built-in account."

I'm only a newbie to this so I don't know what the implications of this change are, other than I was able to import my Access Db.

Error: 'table that is required for a join cannot be reached'

Hello all,

I'm running into an odd error when trying to process a newly created dimension, I've looked at the 2 postings with a similar error, but they don't appear to pertain. Here is the error:

Error 1 Errors in the high-level relational engine. The 'dbo_Company_Address' table that is required for a join cannot be reached based on the relationships in the data source view.

Error 2 Errors in the OLAP storage engine: An error occurred while the dimension, with the ID of 'Company', Name of 'Company-Location' was being processed.

Error 3 Errors in the OLAP storage engine: An error occurred while the 'Company' attribute of the 'Company-Location' dimension from the 'datawarehouse' database was being processed.

... processing then fails.

I was running into the same error using a data view with the full database schema and working cubes, so I started over, trying to simplify: I created a new project for a new database, created a new DSV, reduced the number of tables and confirmed that all relationships were correct (I did need to add some relationships where the wizard did not pick up on some links). I confirmed that the DSV did show the relation between dbo.Company and dbo.Company_Address. This was discovered by the DSV wizard. The PK and FK columns both show as int(4) in the DSV, as they should... Next, I created a single dimension using the dbo.Company table. Interestingly, the Dimension wizard did NOT pick up on the FK relation to dbo.Company_Address, though it picked up on two other relations, and added tables and hierarchies. I then added the dbo.Company_Address table to the Dimension (without adding any attributes or attribute relations), and processed the project. This processed OK. Next, I Added the PK (Company_Address_RECID) of the dbo.Company_Address table to the dimension attributes, created an attribute relation for that attribute under the Company attribute, and reprocessed the project. I got the above error again...

The database I am trying to process comes from a third-party .NET application, which means that I can't easily change the underlying DB schema. For reference, the source DB is running under MSSQL 2000 (sp4).

I've also done numerous reports against this database (most using these tables and this pretty basic relation), so I don't think there is an issue with the underlying DB, but you never know.

... Anyone have any ideas?

It is hard to say what is going on.

The error indicates specifically problem with DSV. Not with database , not with the way you define your PK FK relationships in the SQL Server database. It is DVS.

I have seen such errors in the past. And in every case I would first look and will think everything is okay. But eventually this got to be DSV.

Try to simplify your case even more. Have only two tables, create only 2 attributes based on each table.

Edward.
--
This posting is provided "AS IS" with no warranties, and confers no rights.

|||Edward,

Thanks for answering.

> The error indicates specifically problem with DSV.

That's the impression I was left with, glad to be able to start narrowing issues...

I went ahead and tried narrowing down possibilities. Here are the results:

Changed DSV to contain ONLY the tables for the dimension (Company, Company_Address, Company_Type, Company_Status). Wizard picked up on relations from all but Company_Address.
|||

Feel free to contact me by removing 'noreply.online.' from my Display E-mail.

I will try take a look at your problem.

Edward.
--
This posting is provided "AS IS" with no warranties, and confers no rights.

|||Just updating this posting with the off line discussion so far. Edward has been unable to followup on this issue, and I really do need an answer...

--Jason

---

> --Original Message--
> From: Jpiterak
> Sent: Friday, June 09, 2006 9:29 AM
> To: Edward Melomed
> Subject: Error: 'table that is required for a join cannot be reached'
>
> Hello Edward,
> Again, thanks for taking the time to help with this...
>
> I'm attaching the contents of the .dsv and .dim xml to this email... Let
> me know if there is anything else I can send to help track this down (do
> you need the full project? -- I'd just need to cleanse any login
> info...).
>
> Thanks again,
> --Jason
Edward Melomed wrote:
> Are you having a case when a single company can have several addresses?
>
> One way to deal with this problem is to define single "primary" company
> address. So company and address have one-to-one relationship.
> Create a named query in DSV joining Company and Company address tables.
> Make sure you resolve ambiguity with addresses.
>
> If you absolutely need to maintain several addresses for company, you
> can create separate many-to-many dimension for Company address.
>
> Take a look at the AdventureWorks sample project shipped with the AS
> 2005. It has example of many-to-many dimensions.
>
>
> Edward.
> Analysis Services team. Microsoft Corp.
>


Hi Edward,
Thanks again for getting back to me...

The thing is, the relation between Company and Company_Address isn't
many to many, it's one to many (a company has several addresses, each of
which belong to one and only one company...)
All my other one to many relations seem to work fine, this one is just
plain stubbornly refusing to work.

Is there something in the xml schema files that presents the
co->co_addr relationship as something other than one to many, perhaps?

Anyway, thanks again for taking the time.

Take care,
--Jason

|||

Let me try to take another stab at it and explain it in a different way:

In the relational database you have one-to-many relationship between your company address and your company.

But the way you've defined your dimension, is you've marked your company as a key of the dimension and company address as a related attribute. This is just opposite to how you want to define your dimension.

The key attribute should be most granular attribute in your dimension and all other attributes are less granular.

So many examples... Take a look at the AdventureWorks database. You have Customer dimension there.

Any dimension you look, you see the relationships are one-to-many between attributes and the key attribute in the dimension.

Makes sense?

Edward.
--
This posting is provided "AS IS" with no warranties, and confers no rights.

Error: Subreport could not be shown

Hi All,

I've researched for a day and have perused every similar post but to no avail.

I'm building a very simple master-detail report using the report builder in VS 2005. I am not using a forms project.

All

parameters seem to be set up correctly because when data exists in my

subreport, it displays perfectly. The problem is that when I pass a

parameter to the subreport and the subreport returns 0 records, I

receive the infamous "Error: Subreport could not be shown" message in

my textbox.

I've tried setting the NoRows property but it seems

that it's overridden. I've tried setting various sections of the

subreport (from within the subreport) to Visible = False based on a

condition but again, it seems that the error is generated before this.

Could I be overlooking something? Any ideas?

Other notes:

-

I'm not Deploying these reports to the Report Server yet, the error is

strictly within the IDE, but upon performing a test, I receieve the

same error even after deployment.

- Using VS 2005 w / no Service Packs

Thanks!Solved it!

On the sub-report itself, I had to specify that my parameter should be "Non-queried" instead of "From query".

This stopped my subreport from generating the "Error: Subreport could not be shown" message.