MSDE and clustered indexes.

We have this weird problem with our customers using MSDE. They keep running
up against the 2 gig size limit and their database stops functioning because
it can't allocate any more space. But if you examine the database, 75% of
the allocated space is unused. It can't be recovered by shrinking the
database. The only way we can reduce the database size is to copy all the
data into a temporary database, delete it from the original and then
re-insert in the original. Yesterday we did this and a customer's database
was reduced to 528 MB from 1.913 GB. We run a weely job that shrinks and
re-indexes everything but it doesn't have any effect on this problem. I
suspect that it has something to do with the fact that there are no
clustered indexes on any of the table so the re-index job is not doing
anything. But maybe my understanding of this is inaccurate. Is this problem
because of the lack of clustered indexes?If there are no clustered indexes, the table is essentially a "heap". Data
will be appended at the end. If the application delete large amounts of dat
a
and re-inserts it, the most likely be large gaps in the middle.
Objects such as triggers can cause "delete/insert" pairs instead of
"in-place" updates, fill-factor can be a potential cause, etc. Clustered
indexes may not help if the natural key is ever-increasing, since holes will
still exist after large deletes. Non-clustered indexes may help
space-reclamation.
Find out what type of activity is occuring and take appropriate action.
"Bob Castleman" wrote:

> We have this weird problem with our customers using MSDE. They keep runnin
g
> up against the 2 gig size limit and their database stops functioning becau
se
> it can't allocate any more space. But if you examine the database, 75% of
> the allocated space is unused. It can't be recovered by shrinking the
> database. The only way we can reduce the database size is to copy all the
> data into a temporary database, delete it from the original and then
> re-insert in the original. Yesterday we did this and a customer's database
> was reduced to 528 MB from 1.913 GB. We run a weely job that shrinks and
> re-indexes everything but it doesn't have any effect on this problem. I
> suspect that it has something to do with the fact that there are no
> clustered indexes on any of the table so the re-index job is not doing
> anything. But maybe my understanding of this is inaccurate. Is this proble
m
> because of the lack of clustered indexes?
>
>|||Any new devolepments with this issue, where the clustered indexes an issue o
r not? Anyone had a case like this?
Thanks.

MSDE 2GB Limit

Hi
A Client has an MSDE install - checked by running @.@.version (clearly
says developer edition in the result text).
However the size of the data file is over 3GB, even allowing for empty
spaces the amount of data far exceeds 2GB. Pls explain?
The database was filled by regular insert statements.
Am 31 Oct 2005 04:14:49 -0800 schrieb yitzak:

> Hi
> A Client has an MSDE install - checked by running @.@.version (clearly
> says developer edition in the result text).
> However the size of the data file is over 3GB, even allowing for empty
> spaces the amount of data far exceeds 2GB. Pls explain?
> The database was filled by regular insert statements.
MSDE always shows "Desktop Engine" and not "Developer Edition"!
bye,
Helmut

MSDE 2BG limit - for just the data file?

Hi -
I'm looking at an archive strategy for an MSDE database.
In ref to the 2GB "database size" limit: anyone know if
this applies to the log file size + data file size, or
just to the data file size?
I need to figure out the point at which archiving needs
to kick in and trim down an in-production database.
Thanks!
Andy
hi Andy,
Andy wrote:
> Hi -
> I'm looking at an archive strategy for an MSDE database.
> In ref to the 2GB "database size" limit: anyone know if
> this applies to the log file size + data file size, or
> just to the data file size?
> I need to figure out the point at which archiving needs
> to kick in and trim down an in-production database.
> Thanks!
> Andy
the 2gb limit only applies to the sum of data files, including primary
(.Mdf) and all eventual secondary (.Ndf) files...
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
DbaMgr2k ver 0.10.0 - DbaMgr ver 0.56.0
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
-- remove DMO to reply
|||Great, thanks Andrea!
|||Hi
2Gb for the data file, per database.
You could have more than one database.....
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/
"Andy" <anonymous@.discussions.microsoft.com> wrote in message
news:0dbd01c5092b$ac4a6e90$a601280a@.phx.gbl...
> Hi -
> I'm looking at an archive strategy for an MSDE database.
> In ref to the 2GB "database size" limit: anyone know if
> this applies to the log file size + data file size, or
> just to the data file size?
> I need to figure out the point at which archiving needs
> to kick in and trim down an in-production database.
> Thanks!
> Andy

msdb database size

My SQL Server 2005 SP2 msdb database is 230 GB. I tried shrinking it several
times and nothing changes. The log file is 1 mb. How can I tell what is
using up this space?USE msdb;
GO
SELECT TOP 10 OBJECT_NAME(id), *
FROM sys.sysindexes
WHERE indid IN (0,1)
ORDER BY used DESC;
--or
SELECT TOP 10 OBJECT_NAME([object_id]), *
FROM sys.dm_db_partition_stats
WHERE index_id IN (0,1)
ORDER BY in_row_reserved_page_count DESC;
"SQLGuru_not" <SQLGurunot@.discussions.microsoft.com> wrote in message
news:771E4963-D5B2-4CEB-9209-00FF3B5F75A4@.microsoft.com...
> My SQL Server 2005 SP2 msdb database is 230 GB. I tried shrinking it
> several
> times and nothing changes. The log file is 1 mb. How can I tell what is
> using up this space?|||Aaron,
Thank you. I ran the second query and got an object with this name:
queue_messages_391672443
I cannot find this object in the tables...
"Aaron Bertrand [SQL Server MVP]" wrote:
> USE msdb;
> GO
> SELECT TOP 10 OBJECT_NAME(id), *
> FROM sys.sysindexes
> WHERE indid IN (0,1)
> ORDER BY used DESC;
> --or
> SELECT TOP 10 OBJECT_NAME([object_id]), *
> FROM sys.dm_db_partition_stats
> WHERE index_id IN (0,1)
> ORDER BY in_row_reserved_page_count DESC;
>
> "SQLGuru_not" <SQLGurunot@.discussions.microsoft.com> wrote in message
> news:771E4963-D5B2-4CEB-9209-00FF3B5F75A4@.microsoft.com...
> > My SQL Server 2005 SP2 msdb database is 230 GB. I tried shrinking it
> > several
> > times and nothing changes. The log file is 1 mb. How can I tell what is
> > using up this space?
>
>|||Probably marked as a system table. This will generate a query that will
show you 10 sample rows from the table.
SELECT 'SELECT TOP 10 * FROM '
+ OBJECT_SCHEMA_NAME(object_id)
+ '.[' + name + ']'
FROM sys.all_objects
WHERE name = 'queue_messages_391672443';
Is it possible you are using service broker or event/query notifications and
messages are being placed on the queue but not sent out?
"SQLGuru_not" <SQLGurunot@.discussions.microsoft.com> wrote in message
news:6F5B5B70-F929-4D5D-8A28-34E3BF84D098@.microsoft.com...
> Aaron,
> Thank you. I ran the second query and got an object with this name:
> queue_messages_391672443
> I cannot find this object in the tables...
> "Aaron Bertrand [SQL Server MVP]" wrote:
>> USE msdb;
>> GO
>> SELECT TOP 10 OBJECT_NAME(id), *
>> FROM sys.sysindexes
>> WHERE indid IN (0,1)
>> ORDER BY used DESC;
>> --or
>> SELECT TOP 10 OBJECT_NAME([object_id]), *
>> FROM sys.dm_db_partition_stats
>> WHERE index_id IN (0,1)
>> ORDER BY in_row_reserved_page_count DESC;
>>
>> "SQLGuru_not" <SQLGurunot@.discussions.microsoft.com> wrote in message
>> news:771E4963-D5B2-4CEB-9209-00FF3B5F75A4@.microsoft.com...
>> > My SQL Server 2005 SP2 msdb database is 230 GB. I tried shrinking it
>> > several
>> > times and nothing changes. The log file is 1 mb. How can I tell what
>> > is
>> > using up this space?
>>|||I cannot find this table. The script returned a SQL statement with table
name that does not exist. We're not using service broker. What can we do to
stop the message and clear up them up?
"Aaron Bertrand [SQL Server MVP]" wrote:
> Probably marked as a system table. This will generate a query that will
> show you 10 sample rows from the table.
>
> SELECT 'SELECT TOP 10 * FROM '
> + OBJECT_SCHEMA_NAME(object_id)
> + '.[' + name + ']'
> FROM sys.all_objects
> WHERE name = 'queue_messages_391672443';
>
> Is it possible you are using service broker or event/query notifications and
> messages are being placed on the queue but not sent out?
>
>
>
> "SQLGuru_not" <SQLGurunot@.discussions.microsoft.com> wrote in message
> news:6F5B5B70-F929-4D5D-8A28-34E3BF84D098@.microsoft.com...
> > Aaron,
> > Thank you. I ran the second query and got an object with this name:
> > queue_messages_391672443
> >
> > I cannot find this object in the tables...
> >
> > "Aaron Bertrand [SQL Server MVP]" wrote:
> >
> >> USE msdb;
> >> GO
> >>
> >> SELECT TOP 10 OBJECT_NAME(id), *
> >> FROM sys.sysindexes
> >> WHERE indid IN (0,1)
> >> ORDER BY used DESC;
> >>
> >> --or
> >>
> >> SELECT TOP 10 OBJECT_NAME([object_id]), *
> >> FROM sys.dm_db_partition_stats
> >> WHERE index_id IN (0,1)
> >> ORDER BY in_row_reserved_page_count DESC;
> >>
> >>
> >>
> >> "SQLGuru_not" <SQLGurunot@.discussions.microsoft.com> wrote in message
> >> news:771E4963-D5B2-4CEB-9209-00FF3B5F75A4@.microsoft.com...
> >> > My SQL Server 2005 SP2 msdb database is 230 GB. I tried shrinking it
> >> > several
> >> > times and nothing changes. The log file is 1 mb. How can I tell what
> >> > is
> >> > using up this space?
> >>
> >>
> >>
>
>|||>I cannot find this table. The script returned a SQL statement with table
> name that does not exist. We're not using service broker. What can we do
> to
> stop the message and clear up them up?
Well if you "can't find the table" it's going to be hard for any of us to
suggest a way to "clear up" anything... maybe you should turn on Profiler
and watch for any T-SQL events that involve an object name like that... then
that might point you to some application you have running that is doing this
to you.
A|||Aaron,
Thank you for your assistance. I seem to be getting nowhere in getting to
the bottom of what is causing this. I don't know how Service Broker works
and whether it is responsible.
I'm running Profiler now and I am not getting anything. How do I view the
sys tables?
"Aaron Bertrand [SQL Server MVP]" wrote:
> >I cannot find this table. The script returned a SQL statement with table
> > name that does not exist. We're not using service broker. What can we do
> > to
> > stop the message and clear up them up?
> Well if you "can't find the table" it's going to be hard for any of us to
> suggest a way to "clear up" anything... maybe you should turn on Profiler
> and watch for any T-SQL events that involve an object name like that... then
> that might point you to some application you have running that is doing this
> to you.
> A
>
>|||> I'm running Profiler now and I am not getting anything. How do I view the
> sys tables?
There are tons of sys tables. Can you be more specific? What *EXACTLY* did
the query I sent earlier return? And what happened when you ran the output?
SELECT 'SELECT TOP 10 * FROM '
+ OBJECT_SCHEMA_NAME(object_id)
+ '.[' + name + ']'
FROM sys.all_objects
WHERE name = 'queue_messages_391672443';|||It generates this sql statement as output:
SELECT TOP 10 * FROM sys.[queue_messages_391672443]
"Aaron Bertrand [SQL Server MVP]" wrote:
> > I'm running Profiler now and I am not getting anything. How do I view the
> > sys tables?
> There are tons of sys tables. Can you be more specific? What *EXACTLY* did
> the query I sent earlier return? And what happened when you ran the output?
> SELECT 'SELECT TOP 10 * FROM '
> + OBJECT_SCHEMA_NAME(object_id)
> + '.[' + name + ']'
> FROM sys.all_objects
> WHERE name = 'queue_messages_391672443';
>
>|||And I'll ask again, what happens when you run *THAT* query?
"SQLGuru_not" <SQLGurunot@.discussions.microsoft.com> wrote in message
news:EFEE4A62-F1A4-4171-81CC-D804033AD2E3@.microsoft.com...
> It generates this sql statement as output:
> SELECT TOP 10 * FROM sys.[queue_messages_391672443]|||Thanks Aaron,
I get an error:
Msg 208, Level 16, State 1, Line 1
Invalid object name 'sys.queue_messages_391672443'.
"Aaron Bertrand [SQL Server MVP]" wrote:
> And I'll ask again, what happens when you run *THAT* query?
>
> "SQLGuru_not" <SQLGurunot@.discussions.microsoft.com> wrote in message
> news:EFEE4A62-F1A4-4171-81CC-D804033AD2E3@.microsoft.com...
> > It generates this sql statement as output:
> > SELECT TOP 10 * FROM sys.[queue_messages_391672443]
>
>|||I don't know, gremlins? Can you try running that query as sa or another
sysadmin? Maybe you can't select from it because you don't have privileges.
"SQLGuru_not" <SQLGurunot@.discussions.microsoft.com> wrote in message
news:9FAB03FA-9A5D-455B-BC15-DDB1A7036A73@.microsoft.com...
> Thanks Aaron,
> I get an error:
> Msg 208, Level 16, State 1, Line 1
> Invalid object name 'sys.queue_messages_391672443'.
>
> "Aaron Bertrand [SQL Server MVP]" wrote:
>> And I'll ask again, what happens when you run *THAT* query?
>>
>> "SQLGuru_not" <SQLGurunot@.discussions.microsoft.com> wrote in message
>> news:EFEE4A62-F1A4-4171-81CC-D804033AD2E3@.microsoft.com...
>> > It generates this sql statement as output:
>> > SELECT TOP 10 * FROM sys.[queue_messages_391672443]
>>|||With the help from Micrsoft, the culprit turned out to be a login
notification service that had gotten turned on (still a mysterie). The
service broker type service usese an internal table called LoginQueue that
was expanding by leaps and bound as result of login events. Since there was
no place for the notifications to be sent or received, the queue continued to
grow. We had to write a program to "receive messages" for 86 million
messages. Once the program received all the message we could then delete the
service and the associated internal table. Shrinking the database reclaimed
all the space.
Aaron, I really apreciate your involvement. Becaue of your query, we're
able to get Microsoft focused on how to identify the source and deal with it.
"Aaron Bertrand [SQL Server MVP]" wrote:
> I don't know, gremlins? Can you try running that query as sa or another
> sysadmin? Maybe you can't select from it because you don't have privileges.
>
> "SQLGuru_not" <SQLGurunot@.discussions.microsoft.com> wrote in message
> news:9FAB03FA-9A5D-455B-BC15-DDB1A7036A73@.microsoft.com...
> > Thanks Aaron,
> > I get an error:
> > Msg 208, Level 16, State 1, Line 1
> > Invalid object name 'sys.queue_messages_391672443'.
> >
> >
> > "Aaron Bertrand [SQL Server MVP]" wrote:
> >
> >> And I'll ask again, what happens when you run *THAT* query?
> >>
> >>
> >>
> >> "SQLGuru_not" <SQLGurunot@.discussions.microsoft.com> wrote in message
> >> news:EFEE4A62-F1A4-4171-81CC-D804033AD2E3@.microsoft.com...
> >> > It generates this sql statement as output:
> >> > SELECT TOP 10 * FROM sys.[queue_messages_391672443]
> >>
> >>
> >>
>
>