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 combination. Show all posts
Showing posts with label combination. Show all posts
Sunday, March 11, 2012
Thursday, February 16, 2012
2005 Inconsistent Results with VarBinary fields
Hello,
I seem to be encountering some inconsistent results with full text searching
against a combination of VarChar(max) and VarBinary(max) fields.
I have a table which has both a VarChar(max) field and a VarBinary(max)
field. The table is meant to either have text or an attachement (or both).
I have indexed both fields. When I do a particular search against just the
Varbinary field I get 16 results. When I do the same query, but with both
columns specified, I only get 8 results. Maybe there is a good reason for
this, but it seems like adding more fields to the search should not result
in fewer hits. And I have verified that the records it is missing truly
contain the word I am looking for. Below are the relevant queries.
Any help would be appreciated. Thanks!
Randall
-- ***** Table ****
create table dbo.z_FullTextChild (
FullTextChildID uniqueidentifier ROWGUIDCOL NOT NULL default newid(),
FullTextMainID uniqueidentifier not null,
LongText VarChar(max) null,
AttachmentFileName VarChar(256) null,
AttachmentFileType VarChar(15) null,
Attachment VarBinary(max),
constraint PK_FullTextChild primary key (FullTextChildID),
constraint PK_FullTextChild_FullTextMain foreign key (FullTextMainID)
references dbo.z_FullTextMain(FullTextMainID) on update cascade on delete
cascade
)
go
-- ******** Full Text Index *********
create fulltext index on dbo.z_FullTextChild
(LongText, Attachment TYPE COLUMN AttachmentFileType )
key index PK_FullTextChild
on ftTracker
with CHANGE_TRACKING AUTO
go
-- ******* Query that yields 16 results ***************
select ftc.LongText, ftc.AttachmentFileName, ft.Rank
from z_FullTextChild ftc
join freetexttable (z_FullTextChild, (Attachment), 'INFORMATION', 100) ft
on ft.[KEY]=ftc.FullTextChildID
where
ftc.AttachmentFileName is not null
order by ft.Rank desc
-- ******** Query that yields 8 results (only difference is in selected
columns) ******************
select ftc.LongText, ftc.AttachmentFileName, ft.Rank
from z_FullTextChild ftc
join freetexttable (z_FullTextChild, (Attachment, LongText), 'INFORMATION',
100) ft on ft.[KEY]=ftc.FullTextChildID
where
ftc.AttachmentFileName is not null
order by ft.Rank desc
-- ******* Select @.@.Version ***********
Microsoft SQL Server 2005 - 9.00.1187.07 (Intel X86) May 24 2005 18:22:46
Copyright (c) 1988-2005 Microsoft Corporation Beta Edition on Windows NT
5.1 (Build 2600: Service Pack 2)
I have tried to repro this problem and am unable to do so. Can you perhaps
do this query?
select ftc.LongText, ftc.AttachmentFileName, ft.Rank
from z_FullTextChild ftc
join freetexttable (z_FullTextChild, (LongText), 'INFORMATION',
100) ft on ft.[KEY]=ftc.FullTextChildID
where
ftc.AttachmentFileName is not null
order by ft.Rank desc
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Randall" <randall@.randall.com> wrote in message
news:uYYBzNuhFHA.2472@.TK2MSFTNGP15.phx.gbl...
> Hello,
> I seem to be encountering some inconsistent results with full text
searching
> against a combination of VarChar(max) and VarBinary(max) fields.
> I have a table which has both a VarChar(max) field and a VarBinary(max)
> field. The table is meant to either have text or an attachement (or
both).
> I have indexed both fields. When I do a particular search against just
the
> Varbinary field I get 16 results. When I do the same query, but with both
> columns specified, I only get 8 results. Maybe there is a good reason for
> this, but it seems like adding more fields to the search should not result
> in fewer hits. And I have verified that the records it is missing truly
> contain the word I am looking for. Below are the relevant queries.
> Any help would be appreciated. Thanks!
> Randall
> -- ***** Table ****
> create table dbo.z_FullTextChild (
> FullTextChildID uniqueidentifier ROWGUIDCOL NOT NULL default newid(),
> FullTextMainID uniqueidentifier not null,
> LongText VarChar(max) null,
> AttachmentFileName VarChar(256) null,
> AttachmentFileType VarChar(15) null,
> Attachment VarBinary(max),
> constraint PK_FullTextChild primary key (FullTextChildID),
> constraint PK_FullTextChild_FullTextMain foreign key (FullTextMainID)
> references dbo.z_FullTextMain(FullTextMainID) on update cascade on delete
> cascade
> )
> go
> -- ******** Full Text Index *********
> create fulltext index on dbo.z_FullTextChild
> (LongText, Attachment TYPE COLUMN AttachmentFileType )
> key index PK_FullTextChild
> on ftTracker
> with CHANGE_TRACKING AUTO
> go
> -- ******* Query that yields 16 results ***************
> select ftc.LongText, ftc.AttachmentFileName, ft.Rank
> from z_FullTextChild ftc
> join freetexttable (z_FullTextChild, (Attachment), 'INFORMATION', 100) ft
> on ft.[KEY]=ftc.FullTextChildID
> where
> ftc.AttachmentFileName is not null
> order by ft.Rank desc
> -- ******** Query that yields 8 results (only difference is in selected
> columns) ******************
> select ftc.LongText, ftc.AttachmentFileName, ft.Rank
> from z_FullTextChild ftc
> join freetexttable (z_FullTextChild, (Attachment, LongText),
'INFORMATION',
> 100) ft on ft.[KEY]=ftc.FullTextChildID
> where
> ftc.AttachmentFileName is not null
> order by ft.Rank desc
> -- ******* Select @.@.Version ***********
> Microsoft SQL Server 2005 - 9.00.1187.07 (Intel X86) May 24 2005
18:22:46
> Copyright (c) 1988-2005 Microsoft Corporation Beta Edition on Windows NT
> 5.1 (Build 2600: Service Pack 2)
>
>
|||Thanks for the reply.
That particular query yields no hits because currently none of the records
that have attachments have any LongText associated with them (although that
could change in the future).
Randall
"Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
news:O4eEKfuhFHA.1048@.tk2msftngp13.phx.gbl...
>I have tried to repro this problem and am unable to do so. Can you perhaps
> do this query?
> select ftc.LongText, ftc.AttachmentFileName, ft.Rank
> from z_FullTextChild ftc
> join freetexttable (z_FullTextChild, (LongText), 'INFORMATION',
> 100) ft on ft.[KEY]=ftc.FullTextChildID
> where
> ftc.AttachmentFileName is not null
> order by ft.Rank desc
>
> --
> Hilary Cotter
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
> Looking for a FAQ on Indexing Services/SQL FTS
> http://www.indexserverfaq.com
> "Randall" <randall@.randall.com> wrote in message
> news:uYYBzNuhFHA.2472@.TK2MSFTNGP15.phx.gbl...
> searching
> both).
> the
> 'INFORMATION',
> 18:22:46
>
I seem to be encountering some inconsistent results with full text searching
against a combination of VarChar(max) and VarBinary(max) fields.
I have a table which has both a VarChar(max) field and a VarBinary(max)
field. The table is meant to either have text or an attachement (or both).
I have indexed both fields. When I do a particular search against just the
Varbinary field I get 16 results. When I do the same query, but with both
columns specified, I only get 8 results. Maybe there is a good reason for
this, but it seems like adding more fields to the search should not result
in fewer hits. And I have verified that the records it is missing truly
contain the word I am looking for. Below are the relevant queries.
Any help would be appreciated. Thanks!
Randall
-- ***** Table ****
create table dbo.z_FullTextChild (
FullTextChildID uniqueidentifier ROWGUIDCOL NOT NULL default newid(),
FullTextMainID uniqueidentifier not null,
LongText VarChar(max) null,
AttachmentFileName VarChar(256) null,
AttachmentFileType VarChar(15) null,
Attachment VarBinary(max),
constraint PK_FullTextChild primary key (FullTextChildID),
constraint PK_FullTextChild_FullTextMain foreign key (FullTextMainID)
references dbo.z_FullTextMain(FullTextMainID) on update cascade on delete
cascade
)
go
-- ******** Full Text Index *********
create fulltext index on dbo.z_FullTextChild
(LongText, Attachment TYPE COLUMN AttachmentFileType )
key index PK_FullTextChild
on ftTracker
with CHANGE_TRACKING AUTO
go
-- ******* Query that yields 16 results ***************
select ftc.LongText, ftc.AttachmentFileName, ft.Rank
from z_FullTextChild ftc
join freetexttable (z_FullTextChild, (Attachment), 'INFORMATION', 100) ft
on ft.[KEY]=ftc.FullTextChildID
where
ftc.AttachmentFileName is not null
order by ft.Rank desc
-- ******** Query that yields 8 results (only difference is in selected
columns) ******************
select ftc.LongText, ftc.AttachmentFileName, ft.Rank
from z_FullTextChild ftc
join freetexttable (z_FullTextChild, (Attachment, LongText), 'INFORMATION',
100) ft on ft.[KEY]=ftc.FullTextChildID
where
ftc.AttachmentFileName is not null
order by ft.Rank desc
-- ******* Select @.@.Version ***********
Microsoft SQL Server 2005 - 9.00.1187.07 (Intel X86) May 24 2005 18:22:46
Copyright (c) 1988-2005 Microsoft Corporation Beta Edition on Windows NT
5.1 (Build 2600: Service Pack 2)
I have tried to repro this problem and am unable to do so. Can you perhaps
do this query?
select ftc.LongText, ftc.AttachmentFileName, ft.Rank
from z_FullTextChild ftc
join freetexttable (z_FullTextChild, (LongText), 'INFORMATION',
100) ft on ft.[KEY]=ftc.FullTextChildID
where
ftc.AttachmentFileName is not null
order by ft.Rank desc
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Randall" <randall@.randall.com> wrote in message
news:uYYBzNuhFHA.2472@.TK2MSFTNGP15.phx.gbl...
> Hello,
> I seem to be encountering some inconsistent results with full text
searching
> against a combination of VarChar(max) and VarBinary(max) fields.
> I have a table which has both a VarChar(max) field and a VarBinary(max)
> field. The table is meant to either have text or an attachement (or
both).
> I have indexed both fields. When I do a particular search against just
the
> Varbinary field I get 16 results. When I do the same query, but with both
> columns specified, I only get 8 results. Maybe there is a good reason for
> this, but it seems like adding more fields to the search should not result
> in fewer hits. And I have verified that the records it is missing truly
> contain the word I am looking for. Below are the relevant queries.
> Any help would be appreciated. Thanks!
> Randall
> -- ***** Table ****
> create table dbo.z_FullTextChild (
> FullTextChildID uniqueidentifier ROWGUIDCOL NOT NULL default newid(),
> FullTextMainID uniqueidentifier not null,
> LongText VarChar(max) null,
> AttachmentFileName VarChar(256) null,
> AttachmentFileType VarChar(15) null,
> Attachment VarBinary(max),
> constraint PK_FullTextChild primary key (FullTextChildID),
> constraint PK_FullTextChild_FullTextMain foreign key (FullTextMainID)
> references dbo.z_FullTextMain(FullTextMainID) on update cascade on delete
> cascade
> )
> go
> -- ******** Full Text Index *********
> create fulltext index on dbo.z_FullTextChild
> (LongText, Attachment TYPE COLUMN AttachmentFileType )
> key index PK_FullTextChild
> on ftTracker
> with CHANGE_TRACKING AUTO
> go
> -- ******* Query that yields 16 results ***************
> select ftc.LongText, ftc.AttachmentFileName, ft.Rank
> from z_FullTextChild ftc
> join freetexttable (z_FullTextChild, (Attachment), 'INFORMATION', 100) ft
> on ft.[KEY]=ftc.FullTextChildID
> where
> ftc.AttachmentFileName is not null
> order by ft.Rank desc
> -- ******** Query that yields 8 results (only difference is in selected
> columns) ******************
> select ftc.LongText, ftc.AttachmentFileName, ft.Rank
> from z_FullTextChild ftc
> join freetexttable (z_FullTextChild, (Attachment, LongText),
'INFORMATION',
> 100) ft on ft.[KEY]=ftc.FullTextChildID
> where
> ftc.AttachmentFileName is not null
> order by ft.Rank desc
> -- ******* Select @.@.Version ***********
> Microsoft SQL Server 2005 - 9.00.1187.07 (Intel X86) May 24 2005
18:22:46
> Copyright (c) 1988-2005 Microsoft Corporation Beta Edition on Windows NT
> 5.1 (Build 2600: Service Pack 2)
>
>
|||Thanks for the reply.
That particular query yields no hits because currently none of the records
that have attachments have any LongText associated with them (although that
could change in the future).
Randall
"Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
news:O4eEKfuhFHA.1048@.tk2msftngp13.phx.gbl...
>I have tried to repro this problem and am unable to do so. Can you perhaps
> do this query?
> select ftc.LongText, ftc.AttachmentFileName, ft.Rank
> from z_FullTextChild ftc
> join freetexttable (z_FullTextChild, (LongText), 'INFORMATION',
> 100) ft on ft.[KEY]=ftc.FullTextChildID
> where
> ftc.AttachmentFileName is not null
> order by ft.Rank desc
>
> --
> Hilary Cotter
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
> Looking for a FAQ on Indexing Services/SQL FTS
> http://www.indexserverfaq.com
> "Randall" <randall@.randall.com> wrote in message
> news:uYYBzNuhFHA.2472@.TK2MSFTNGP15.phx.gbl...
> searching
> both).
> the
> 'INFORMATION',
> 18:22:46
>
Subscribe to:
Posts (Atom)