Is the 2 GB limitation the combination of the .MDF and .LDF or only the
..MDF?
If both, can you turn off the LOG file?
Thanks,
Jack T.
I believe it doesn't include the log file
Michael Culley
"Jack T." <71045.3122_No_Spam_Here@.compuserve.com> wrote in message
news:efwFmaQmEHA.2340@.TK2MSFTNGP11.phx.gbl...
> Is the 2 GB limitation the combination of the .MDF and .LDF or only the
> .MDF?
> If both, can you turn off the LOG file?
> Thanks,
> Jack T.
>
|||see
http://msdn.microsoft.com/library/de...ar_ts_8dbn.asp
3 The data portion of a database cannot exceed 2 GB in size when using the
SQL Server 2000 Desktop Engine (MSDE 2000) or the Microsoft Data Engine
(MSDE) 1.0. The total size of the database, including log files, can exceed 2
GB provided the sum of the sizes of the data files remains 2 GB or lower.
"Jack T." wrote:
> Is the 2 GB limitation the combination of the .MDF and .LDF or only the
> ..MDF?
> If both, can you turn off the LOG file?
> Thanks,
> Jack T.
>
>
|||Awesome! As long as the MDF's per instances don't exceed the 2GB limit. I
can live with that.
Thanks,
Jack T.
|||Does MSDE support SHRINK in the same way that SQL Server 2000 does?
If so you might want to shrink the database and reclaim any unsed space each
night
Rich
"Jack T." wrote:
> Awesome! As long as the MDF's per instances don't exceed the 2GB limit. I
> can live with that.
> Thanks,
> Jack T.
>
>
|||Yes, but why would that be necessary? SQL Server will re-use the deallocated
space within the database automatically so there's no need to play with the
file size. Shrinking and expanding are expensive in terms of performance.
Sincerely,
Stephen Dybing
This posting is provided "AS IS" with no warranties, and confers no rights.
"Richard Yeo" <RichardYeo@.discussions.microsoft.com> wrote in message
news:6DABD8E8-06E3-4035-A8C1-3AC71AF48453@.microsoft.com...[vbcol=seagreen]
> Does MSDE support SHRINK in the same way that SQL Server 2000 does?
> If so you might want to shrink the database and reclaim any unsed space
> each
> night
> Rich
>
> "Jack T." wrote:
|||Stephen
If SQL Server automatically reuses ALL space from deleted records whats the
point of the SHRINK command?
Rich
"Stephen Dybing [MSFT]" wrote:
> Yes, but why would that be necessary? SQL Server will re-use the deallocated
> space within the database automatically so there's no need to play with the
> file size. Shrinking and expanding are expensive in terms of performance.
> --
> Sincerely,
> Stephen Dybing
> This posting is provided "AS IS" with no warranties, and confers no rights.
> "Richard Yeo" <RichardYeo@.discussions.microsoft.com> wrote in message
> news:6DABD8E8-06E3-4035-A8C1-3AC71AF48453@.microsoft.com...
>
>
|||hi Richard,
"Richard Yeo" <RichardYeo@.discussions.microsoft.com> ha scritto nel
messaggio news:F1BD0469-6F9C-40A5-956F-D568B9200B3E@.microsoft.com...
> Stephen
> If SQL Server automatically reuses ALL space from deleted records whats
the
> point of the SHRINK command?
to reduce it's size once a lot of delete operations have drastically reduced
the used data pages... so that unused pages will be release to the
underlying OS...
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
DbaMgr2k ver 0.9.1 - DbaMgr ver 0.55.1
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
-- remove DMO to reply
|||So SHRINK is important ;)
Sorry just pulling Stephens leg I knew this all along.
MSDE is often used as a log and therefore lots of deletes will be used when
the old log information is no longer required. Therefore shrink is important.
Don't forget to backup your transaction logs regularly to prevent them
growing continuously.
You should also consider running an integrity check regularly.
Rich
"Jack T." wrote:
> Is the 2 GB limitation the combination of the .MDF and .LDF or only the
> ..MDF?
> If both, can you turn off the LOG file?
> Thanks,
> Jack T.
>
>
|||Uh no, the log file size does not factor into that 2GB database limitation.
When the rows are deleted from your table, they are gone and new rows can
use that space. SHRINK is still not necessary for this situation and if
performance is an issue, please remember that shrinking and growing the file
is expensive. It's possible that SHRINK is necessary in some situations for
some people, but I think it's highly overrated.
Sincerely,
Stephen Dybing
This posting is provided "AS IS" with no warranties, and confers no rights.
"Richard Yeo" <RichardYeo@.discussions.microsoft.com> wrote in message
news:55CC05A1-238C-4FB3-A117-466C3A6F0570@.microsoft.com...[vbcol=seagreen]
> So SHRINK is important ;)
> Sorry just pulling Stephens leg I knew this all along.
> MSDE is often used as a log and therefore lots of deletes will be used
> when
> the old log information is no longer required. Therefore shrink is
> important.
> Don't forget to backup your transaction logs regularly to prevent them
> growing continuously.
> You should also consider running an integrity check regularly.
> Rich
> "Jack T." wrote:
Showing posts with label limitation. Show all posts
Showing posts with label limitation. Show all posts
Sunday, March 11, 2012
Thursday, February 9, 2012
2005 - Common Table Expressions Question
I am excited about this new feature in SQL Server 2005. However, it seems to
me there is a rather severe limitation to CTE's. That is the fact that any
queries that call the CTE must call them right after the CTE definition. For
example, Example #1 works, Example #2 fails, Example #3 works. I suppose
there is some logical technical reason why this must be but this seems, IMO,
to significantly limit the usefullness of CTE's especially as a replacement
for temporary tables (which can be referenced anytime after it is declared).
Any chance this limitation will be lifted before release?
-- Example #1
Use AdventureWorks
Go;
With MyCTE (ListPrice,SellPrice) As
(
Select ListPrice,ListPrice * .95
From Production.Product
)
Select * From MyCTE -- Select all records from the CTE
-- Example #2
Use AdventureWorks
Go;
With MyCTE (ListPrice,SellPrice) As
(
Select ListPrice,ListPrice * .95
From Production.Product
)
Select * From Production.Location -- Select all records from a physical
table in the DB
Select * From MyCTE -- Select all records from the CTE
-- Example #3
Use AdventureWorks
Go;
With MyCTE (ListPrice,SellPrice) As
(
Select ListPrice,ListPrice * .95
From Production.Product
)
Select * From MyCTE -- Select all records from the CTE
Select * From Production.Location -- Select all records from a physical
table in the DB>I am excited about this new feature in SQL Server 2005.
Please post to the SQL Server 2005 newsgroups.
http://www.aspfaq.com/sql2005/show.asp?id=1|||You should be able to achieve the desired result of #2 by writing
Select * From Production.Location;
With MyCTE (ListPrice,SellPrice) As
(
Select ListPrice,ListPrice * .95
From Production.Product
)
Select * From MyCTE
If this does not meet your requirements, please explain what
the problem is.
Steve Kass
Drew University
"Amos J. Soma" <amos_j_soma@.yahoo.com> wrote in message
news:ffSdnT55Xf7vgG_fRVn-jQ@.buckeye-express.com...
>I am excited about this new feature in SQL Server 2005. However, it seems
>to me there is a rather severe limitation to CTE's. That is the fact that
>any queries that call the CTE must call them right after the CTE
>definition. For example, Example #1 works, Example #2 fails, Example #3
>works. I suppose there is some logical technical reason why this must be
>but this seems, IMO, to significantly limit the usefullness of CTE's
>especially as a replacement for temporary tables (which can be referenced
>anytime after it is declared). Any chance this limitation will be lifted
>before release?
>
> -- Example #1
> Use AdventureWorks
> Go;
> With MyCTE (ListPrice,SellPrice) As
> (
> Select ListPrice,ListPrice * .95
> From Production.Product
> )
> Select * From MyCTE -- Select all records from the CTE
> -- Example #2
> Use AdventureWorks
> Go;
> With MyCTE (ListPrice,SellPrice) As
> (
> Select ListPrice,ListPrice * .95
> From Production.Product
> )
> Select * From Production.Location -- Select all records from a physical
> table in the DB
> Select * From MyCTE -- Select all records from the CTE
> -- Example #3
> Use AdventureWorks
> Go;
> With MyCTE (ListPrice,SellPrice) As
> (
> Select ListPrice,ListPrice * .95
> From Production.Product
> )
> Select * From MyCTE -- Select all records from the CTE
> Select * From Production.Location -- Select all records from a physical
> table in the DB
>|||The point of CTEs is that they are scoped to the query. Non-recursive
CTEs are primarily hints for the optimizer and as a bonus they also
shorten the syntax compared to repeating a subquery / derived table.
If you want to persist the definition of a query then SQL2000 already
gives you views, procs and table-valued functions. I don't see that we
could gain much from yet another similar structure but you can email
sqlwish@.microsoft.com with suggestions for post-Yukon. 2005
functionality is pretty much wrapped up.
David Portas
SQL Server MVP
--
me there is a rather severe limitation to CTE's. That is the fact that any
queries that call the CTE must call them right after the CTE definition. For
example, Example #1 works, Example #2 fails, Example #3 works. I suppose
there is some logical technical reason why this must be but this seems, IMO,
to significantly limit the usefullness of CTE's especially as a replacement
for temporary tables (which can be referenced anytime after it is declared).
Any chance this limitation will be lifted before release?
-- Example #1
Use AdventureWorks
Go;
With MyCTE (ListPrice,SellPrice) As
(
Select ListPrice,ListPrice * .95
From Production.Product
)
Select * From MyCTE -- Select all records from the CTE
-- Example #2
Use AdventureWorks
Go;
With MyCTE (ListPrice,SellPrice) As
(
Select ListPrice,ListPrice * .95
From Production.Product
)
Select * From Production.Location -- Select all records from a physical
table in the DB
Select * From MyCTE -- Select all records from the CTE
-- Example #3
Use AdventureWorks
Go;
With MyCTE (ListPrice,SellPrice) As
(
Select ListPrice,ListPrice * .95
From Production.Product
)
Select * From MyCTE -- Select all records from the CTE
Select * From Production.Location -- Select all records from a physical
table in the DB>I am excited about this new feature in SQL Server 2005.
Please post to the SQL Server 2005 newsgroups.
http://www.aspfaq.com/sql2005/show.asp?id=1|||You should be able to achieve the desired result of #2 by writing
Select * From Production.Location;
With MyCTE (ListPrice,SellPrice) As
(
Select ListPrice,ListPrice * .95
From Production.Product
)
Select * From MyCTE
If this does not meet your requirements, please explain what
the problem is.
Steve Kass
Drew University
"Amos J. Soma" <amos_j_soma@.yahoo.com> wrote in message
news:ffSdnT55Xf7vgG_fRVn-jQ@.buckeye-express.com...
>I am excited about this new feature in SQL Server 2005. However, it seems
>to me there is a rather severe limitation to CTE's. That is the fact that
>any queries that call the CTE must call them right after the CTE
>definition. For example, Example #1 works, Example #2 fails, Example #3
>works. I suppose there is some logical technical reason why this must be
>but this seems, IMO, to significantly limit the usefullness of CTE's
>especially as a replacement for temporary tables (which can be referenced
>anytime after it is declared). Any chance this limitation will be lifted
>before release?
>
> -- Example #1
> Use AdventureWorks
> Go;
> With MyCTE (ListPrice,SellPrice) As
> (
> Select ListPrice,ListPrice * .95
> From Production.Product
> )
> Select * From MyCTE -- Select all records from the CTE
> -- Example #2
> Use AdventureWorks
> Go;
> With MyCTE (ListPrice,SellPrice) As
> (
> Select ListPrice,ListPrice * .95
> From Production.Product
> )
> Select * From Production.Location -- Select all records from a physical
> table in the DB
> Select * From MyCTE -- Select all records from the CTE
> -- Example #3
> Use AdventureWorks
> Go;
> With MyCTE (ListPrice,SellPrice) As
> (
> Select ListPrice,ListPrice * .95
> From Production.Product
> )
> Select * From MyCTE -- Select all records from the CTE
> Select * From Production.Location -- Select all records from a physical
> table in the DB
>|||The point of CTEs is that they are scoped to the query. Non-recursive
CTEs are primarily hints for the optimizer and as a bonus they also
shorten the syntax compared to repeating a subquery / derived table.
If you want to persist the definition of a query then SQL2000 already
gives you views, procs and table-valued functions. I don't see that we
could gain much from yet another similar structure but you can email
sqlwish@.microsoft.com with suggestions for post-Yukon. 2005
functionality is pretty much wrapped up.
David Portas
SQL Server MVP
--
Subscribe to:
Posts (Atom)