If I have a server with 4GB of RAM and I am running SQL
Server 2k Standard edition (which support up to 2GB of
RAM) does it help anything to utilize the 3 GB switch.Hi
You should use performance monitor to check your memory usage before making
any changes. In general if the server is only running SQL Server it would
probably not be of benefit.
John
"JI" <jidawgs@.hotmail.com> wrote in message
news:04fc01c3d6bf$ba7ede00$a001280a@.phx.gbl...
> If I have a server with 4GB of RAM and I am running SQL
> Server 2k Standard edition (which support up to 2GB of
> RAM) does it help anything to utilize the 3 GB switch.
Showing posts with label 2gb. Show all posts
Showing posts with label 2gb. Show all posts
Tuesday, March 20, 2012
Sunday, March 11, 2012
2GB Limitation question
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:
..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:
2GB Limit in MSDE 2000
I have a database that has reached the MSDE 2GB limit.
I have tried running the sp_spaceused command and I can't see how the
results equalls 2GB
Can someone explain what values are used by MSDE to determine that the
database has reached the 2GB limit?
Sp_SpaceUsed Resuls:
Database Size 1903.38MB
Unused 0.16MB
Reserved 1947864KB
Data 1038296KB
Index Size 204584KB
unused 704984KB
Hi
Database Size 1903.38MB
If you have it set to 100Mb autogrow, it can't grow any more as it would
exceed 2GB.
Regards
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"azar" <azar@.discussions.microsoft.com> wrote in message
news:8FBCCD5B-F8C7-4F91-8CCB-F94A1151BD8D@.microsoft.com...
>I have a database that has reached the MSDE 2GB limit.
> I have tried running the sp_spaceused command and I can't see how the
> results equalls 2GB
> Can someone explain what values are used by MSDE to determine that the
> database has reached the 2GB limit?
> Sp_SpaceUsed Resuls:
> Database Size 1903.38MB
> Unused 0.16MB
> Reserved 1947864KB
> Data 1038296KB
> Index Size 204584KB
> unused 704984KB
|||What are you storing in the database that has pushed it to this limit?
Pictures? Documents?
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant
Microsoft MVP
www.betav.com/blog/billva
www.betav.com
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________
"azar" <azar@.discussions.microsoft.com> wrote in message
news:8FBCCD5B-F8C7-4F91-8CCB-F94A1151BD8D@.microsoft.com...
>I have a database that has reached the MSDE 2GB limit.
> I have tried running the sp_spaceused command and I can't see how the
> results equalls 2GB
> Can someone explain what values are used by MSDE to determine that the
> database has reached the 2GB limit?
> Sp_SpaceUsed Resuls:
> Database Size 1903.38MB
> Unused 0.16MB
> Reserved 1947864KB
> Data 1038296KB
> Index Size 204584KB
> unused 704984KB
I have tried running the sp_spaceused command and I can't see how the
results equalls 2GB
Can someone explain what values are used by MSDE to determine that the
database has reached the 2GB limit?
Sp_SpaceUsed Resuls:
Database Size 1903.38MB
Unused 0.16MB
Reserved 1947864KB
Data 1038296KB
Index Size 204584KB
unused 704984KB
Hi
Database Size 1903.38MB
If you have it set to 100Mb autogrow, it can't grow any more as it would
exceed 2GB.
Regards
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"azar" <azar@.discussions.microsoft.com> wrote in message
news:8FBCCD5B-F8C7-4F91-8CCB-F94A1151BD8D@.microsoft.com...
>I have a database that has reached the MSDE 2GB limit.
> I have tried running the sp_spaceused command and I can't see how the
> results equalls 2GB
> Can someone explain what values are used by MSDE to determine that the
> database has reached the 2GB limit?
> Sp_SpaceUsed Resuls:
> Database Size 1903.38MB
> Unused 0.16MB
> Reserved 1947864KB
> Data 1038296KB
> Index Size 204584KB
> unused 704984KB
|||What are you storing in the database that has pushed it to this limit?
Pictures? Documents?
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant
Microsoft MVP
www.betav.com/blog/billva
www.betav.com
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________
"azar" <azar@.discussions.microsoft.com> wrote in message
news:8FBCCD5B-F8C7-4F91-8CCB-F94A1151BD8D@.microsoft.com...
>I have a database that has reached the MSDE 2GB limit.
> I have tried running the sp_spaceused command and I can't see how the
> results equalls 2GB
> Can someone explain what values are used by MSDE to determine that the
> database has reached the 2GB limit?
> Sp_SpaceUsed Resuls:
> Database Size 1903.38MB
> Unused 0.16MB
> Reserved 1947864KB
> Data 1038296KB
> Index Size 204584KB
> unused 704984KB
2GB Limit
What happens when an SQL2K MSDE database reaches 2GB? I've actually seen
MSDE databases reach that size and nothing happened.
Thanks,
Ademar.
hi Ademar,
Ademar Nunes wrote:
> What happens when an SQL2K MSDE database reaches 2GB? I've actually
> seen MSDE databases reach that size and nothing happened.
when you exceed that limit, nex time the Storage Engine requires a file
allocation beyond the allocated space, thus trying expanding the data file,
an exception will be raised..
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
DbaMgr2k ver 0.15.0 - DbaMgr ver 0.60.0
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
-- remove DMO to reply
|||Maybe the 2GB+ installation I'm referring to is actually a Personal edition
and not MSDE.
I'll run the SELECT @.@.VERSION statement on it and I'm assuming that will
tell me if it is MSDE.
Thanks,
Ademar.
"Andrea Montanari" <andrea.sqlDMO@.virgilio.it> wrote in message
news:3o8jmjF4mu4pU1@.individual.net...
> hi Ademar,
> Ademar Nunes wrote:
> when you exceed that limit, nex time the Storage Engine requires a file
> allocation beyond the allocated space, thus trying expanding the data
> file, an exception will be raised..
> --
> Andrea Montanari (Microsoft MVP - SQL Server)
> http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
> DbaMgr2k ver 0.15.0 - DbaMgr ver 0.60.0
> (my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
> interface)
> -- remove DMO to reply
>
|||Hi,
If it is Personal edition; there is no db size limitation. See details in
below URL
http://msdn.microsoft.com/library/?u...asp?frame=true
Thanks
Hari
SQL Server MVP
"Ademar Nunes" <anunes@.myemail.com> wrote in message
news:%23VOAP38sFHA.3500@.TK2MSFTNGP09.phx.gbl...
> Maybe the 2GB+ installation I'm referring to is actually a Personal
> edition and not MSDE.
> I'll run the SELECT @.@.VERSION statement on it and I'm assuming that will
> tell me if it is MSDE.
> Thanks,
> Ademar.
> "Andrea Montanari" <andrea.sqlDMO@.virgilio.it> wrote in message
> news:3o8jmjF4mu4pU1@.individual.net...
>
MSDE databases reach that size and nothing happened.
Thanks,
Ademar.
hi Ademar,
Ademar Nunes wrote:
> What happens when an SQL2K MSDE database reaches 2GB? I've actually
> seen MSDE databases reach that size and nothing happened.
when you exceed that limit, nex time the Storage Engine requires a file
allocation beyond the allocated space, thus trying expanding the data file,
an exception will be raised..
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
DbaMgr2k ver 0.15.0 - DbaMgr ver 0.60.0
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
-- remove DMO to reply
|||Maybe the 2GB+ installation I'm referring to is actually a Personal edition
and not MSDE.
I'll run the SELECT @.@.VERSION statement on it and I'm assuming that will
tell me if it is MSDE.
Thanks,
Ademar.
"Andrea Montanari" <andrea.sqlDMO@.virgilio.it> wrote in message
news:3o8jmjF4mu4pU1@.individual.net...
> hi Ademar,
> Ademar Nunes wrote:
> when you exceed that limit, nex time the Storage Engine requires a file
> allocation beyond the allocated space, thus trying expanding the data
> file, an exception will be raised..
> --
> Andrea Montanari (Microsoft MVP - SQL Server)
> http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
> DbaMgr2k ver 0.15.0 - DbaMgr ver 0.60.0
> (my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
> interface)
> -- remove DMO to reply
>
|||Hi,
If it is Personal edition; there is no db size limitation. See details in
below URL
http://msdn.microsoft.com/library/?u...asp?frame=true
Thanks
Hari
SQL Server MVP
"Ademar Nunes" <anunes@.myemail.com> wrote in message
news:%23VOAP38sFHA.3500@.TK2MSFTNGP09.phx.gbl...
> Maybe the 2GB+ installation I'm referring to is actually a Personal
> edition and not MSDE.
> I'll run the SELECT @.@.VERSION statement on it and I'm assuming that will
> tell me if it is MSDE.
> Thanks,
> Ademar.
> "Andrea Montanari" <andrea.sqlDMO@.virgilio.it> wrote in message
> news:3o8jmjF4mu4pU1@.individual.net...
>
2Gb Access Database became nearly 4Gb migrated to SQL Server Express - why?
I have just installed SQL Server Express 2005 to get around the 2Gb database size limits under Access 2003. My simple 2Gb Access database (simple meaning only 10 tables with 2-10 fields each, vast majority of the data in one of them and mainly Long Integers) became nearly 4Gb when migrated using SSMA - not great when the SQL Server Express database limit is 4Gb. Shrinking makes no difference.
Can anyone suggest why in general a SQL Server Express database would be nearly twice the size as its Access equivalent. Are there some useful tricks or techniques to reduce the apparently massive overhead?
hi,
inspect your "string" based columns to see how many of them are now nvarchar (National chars, DBCS, which requires 2 time the size of a varchar)... if you think you do not need to store your data in nvarchar columns you can perhaps gain some "additional space" for free..
regards
|||That did it. I'm hugely grateful!
Subscribe to:
Posts (Atom)