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

MSDE 2000 Release A Supported systems

The addendum to the Readme file for MSDE 2000 Release A states "MSDE 2000 Release A is supported on Microsoft Windows Server 2003, Web Edition".
Does this mean that it executes only under Windows 2000 Server 2003 ?
I am running Windows 2000 Pro, it doesnt work. Does anyone run MSDE 2000 Rel A under other windows versions?
Mark Ferguson
hi Mark,
"Mark Ferguson" <MarkFerguson@.discussions.microsoft.com> ha scritto nel
messaggio news:F7D2BF11-BE8F-4AD7-B1AE-FAA334E68CE7@.microsoft.com...
> The addendum to the Readme file for MSDE 2000 Release A states "MSDE 2000
Release A is supported on Microsoft Windows Server 2003, Web Edition".
> Does this mean that it executes only under Windows 2000 Server 2003 ?
> I am running Windows 2000 Pro, it doesnt work. Does anyone run MSDE 2000
Rel
>A under other windows versions?
I think that extract shoul'd be intended as "Microsoft Windows Server 2003,
Web Edition can only run MSDE and not a full blown SQL Server edition"...
MSDE can be installed up to Win98 boxes, more system requirements at
http://www.microsoft.com/sql/msde/pr...fo/sysreqs.asp
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
DbaMgr2k ver 0.8.0 - DbaMgr ver 0.54.0
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
-- remove DMO to reply

MSDE 2000 Release A - Instalation Problem in Windows XP SP2

I can't install MSDE release A in Windows XP SP2. I verified if Server
Service is started and it is ok. I also verified if File and Printer sharing
is checked on network properties and it is ok. The windows firewall also is
disabled but the instalation doesn't work. I run the follow comand setup.exe
sapwd="suportebsb." /L*v C:/MSDELog.log.
Anyone can help me?
Regards,
Cicero Galdino
******** This is the lastest lines in log instalation files
*******************
2005-06-01 16:49:35.64 spid4 Default collation successfully changed.
2005-06-01 16:49:35.67 spid4 Recovery complete.
2005-06-01 16:49:35.67 spid4 SQL global counter collection task is
created.
2005-06-01 16:49:35.69 spid4 Warning: override, autoexec procedures
skipped.
2005-06-01 16:49:43.36 spid4 SQL Server is terminating due to 'stop'
request from Service Control Manager.
=== Logging stopped: 01/06/2005 16:59:54 ===
MSI (c) (18:48) [16:59:54:724]: Note: 1: 1708
MSI (c) (18:48) [16:59:54:724]: Product: Microsoft SQL Server Desktop Engine
-- Installation operation failed.
MSI (c) (18:48) [16:59:54:724]: Grabbed execution mutex.
MSI (c) (18:48) [16:59:54:724]: Cleaning up uninstalled install packages, if
any exist
MSI (c) (18:48) [16:59:54:740]: MainEngineThread is returning 1603
=== Verbose logging stopped: 01/06/2005 16:59:54 ===
hi Cicero,
Cicero wrote:
> I can't install MSDE release A in Windows XP SP2. I verified if Server
> Service is started and it is ok. I also verified if File and Printer
> sharing is checked on network properties and it is ok. The windows
> firewall also is disabled but the instalation doesn't work. I run the
> follow comand setup.exe sapwd="suportebsb." /L*v C:/MSDELog.log.
please inspect your C:/MSDELog.log for
RETURN VALUE 3
entrie(s), that reports exeptions duting the install process...
about 10/15 lines before each entry some (sometime cryptic) descritpion of
the problem will be available
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
DbaMgr2k ver 0.12.0 - DbaMgr ver 0.58.0
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
-- remove DMO to reply

MSDE 2000 named instance with my application

I am writing an application and wish to distribute MSDE with it.
I was considering using a batch file to do the install using the line
setup INSTANCENAME="InstanceName" SAPWD="AStrongSAPwd"
However, the next step would be to call osql to either script the schema or
sp_attach_db the database. Well this raises the issue of once installed
with a named instance how do I do osql -E -S MACHINENAME\InstanceName
without knowing it ahead of time? Is there perhaps 1. an easier way or 2. a
different syntax to achieving it? I am pretty sure I will run into more
grief when attempting to set up the ODBC connections also.
Maybe I'd just be better off rewriting this to use an Access database
instead
hi Bradley,
"Bradley M. Small" <BSmall@.XNOSPAMXmjsi.com> ha scritto nel messaggio
news:ejW7scifEHA.1424@.tk2msftngp13.phx.gbl...
> I am writing an application and wish to distribute MSDE with it.
> I was considering using a batch file to do the install using the line
> setup INSTANCENAME="InstanceName" SAPWD="AStrongSAPwd"
> However, the next step would be to call osql to either script the schema
or
> sp_attach_db the database. Well this raises the issue of once installed
> with a named instance how do I do osql -E -S MACHINENAME\InstanceName
> without knowing it ahead of time? Is there perhaps 1. an easier way or 2.
a
> different syntax to achieving it? I am pretty sure I will run into more
> grief when attempting to set up the ODBC connections also.
> Maybe I'd just be better off rewriting this to use an Access database
> instead
you don't actually need to reference the Machine name... just refer to it as
"(local)" or "."
so, the full instance name will be
(local)\InstanceName
stay this MSDE... you'll love it =;-D
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
DbaMgr2k ver 0.8.0 - DbaMgr ver 0.54.0
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
-- remove DMO to reply
|||"Andrea Montanari" <andrea.sqlDMO@.virgilio.it> wrote in message
news:2npor3F3baroU1@.uni-berlin.de...
> hi Bradley,
> "Bradley M. Small" <BSmall@.XNOSPAMXmjsi.com> ha scritto nel messaggio
> news:ejW7scifEHA.1424@.tk2msftngp13.phx.gbl...
> you don't actually need to reference the Machine name... just refer to it
as
> "(local)" or "."
> so, the full instance name will be
> (local)\InstanceName
> stay this MSDE... you'll love it =;-D
> --
Great!!! Seemed like there had to be a better syntax
Now, all I need to figure out is how to add the odbc connection from a batch
file. Is that even possible?

msde 2000 install failes on xp

Hi
I cant install MSDE 2000 on one XP machine
Cant figure out whats wrong
Because log file too large
Download it here
www.deltmar.ee/logfile.zip
Regards;
Mex
hi Mex,
Meelis Lilbok wrote:
> Hi
> I cant install MSDE 2000 on one XP machine
> Cant figure out whats wrong
> Because log file too large
> Download it here
> www.deltmar.ee/logfile.zip
>
the relevant part of the log is
Setup failed to configure the server. Refer to the server error logs and
setup error logs for more information.
Action ended 14:59:46: InstallFinalize. Return value 3.
usually depending on re-intallation on partially cleaned machines..
please have a look at
http://support.microsoft.com/default...99&Product=sql
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
DbaMgr2k ver 0.16.0 - DbaMgr ver 0.61.0
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
-- remove DMO to reply

MSDE 2000 Install - setup.ini

I have installed MSDE 2000 on my Windows 2000 laptop using the SP3 setup.exe and associated setup.ini file.
So far I have included the following parameters in the setup.ini file -
SAPWD=<my own SA password>
SECURITYMODE=SQL
COLLATION=SQL_Latin1_General_CP1_CI_AS

My next test is to connect to a database on the laptop from my desktop (via my LAN) using SQL Server 2000 Enterprise Manager and/or Query Analyser.

The problem is that although I can 'see' my laptop (ping/My Network Places etc.), I can not see the MSDE installation hense my database on the laptop. The message I get is '[Microsoft]ODBC SQL server driver [DBNETLIB] SQL Server does not exist or access denied'

Have I missed a parameter i.e. do I need to set up a parameter to ensure I can connect to the installation from remote?

Being new to installing MSDE, are there any other parameters I have missed?

Ian

p.s. Anyone know of a simple to follow install instruction set for MSDE 2000 which shows what parameters can be set upSolved the problem myself.
With Service Pack 3, msde has added a new parameter to the install = DISABLENETWORKPROTOCOLS. This parameter is defaulted to 1 which blocks remote access to the database. By adding the parameter with a value of 0 to the setup.ini file I can now connect from remote.
Ian

Originally posted by Ian Grant
I have installed MSDE 2000 on my Windows 2000 laptop using the SP3 setup.exe and associated setup.ini file.
So far I have included the following parameters in the setup.ini file -
SAPWD=<my own SA password>
SECURITYMODE=SQL
COLLATION=SQL_Latin1_General_CP1_CI_AS

My next test is to connect to a database on the laptop from my desktop (via my LAN) using SQL Server 2000 Enterprise Manager and/or Query Analyser.

The problem is that although I can 'see' my laptop (ping/My Network Places etc.), I can not see the MSDE installation hense my database on the laptop. The message I get is '[Microsoft]ODBC SQL server driver [DBNETLIB] SQL Server does not exist or access denied'

Have I missed a parameter i.e. do I need to set up a parameter to ensure I can connect to the installation from remote?

Being new to installing MSDE, are there any other parameters I have missed?

Ian

p.s. Anyone know of a simple to follow install instruction set for MSDE 2000 which shows what parameters can be set up

MSDE 2000 and merge modules

I have a vb.net desktop application that uses msde 2000 for its databasing. I am trying to create a setup file using the output of my desktop application and the msde 2000 merge modules in order to install my own build plus an instance of msde 2000.

The setup runs properly and then asks me to restart my machine. After the restart, however, there is no such instance running on my machine. I am adding the following internal properties to my msi file using ocra:

SQLMSDESelected 1
SqlInstanceName Midas
SqlSaPwd Password1
SqlSecurityMode SQL
SqlDataDir C:\Program Files\Microsoft SQL Server\MSSQL$Midas\Data
SqlProgramDir C:\Program Files\Microsoft SQL Server
This should create an instance of msde on my machine but when I try to connect using enterprize manager or query analizer (I connect to <<machinename>>\Midas), then I get the standard can not connect error.

I am at my witt's end and would really appreciate any help.

Thanks.

Hi,

maybe you could help me....

I'm trying to create a setup file using the VS2003 "Setup Project" and MSDE2000SP4 Merge Modules (editing properties with ORCA).

But when I run the installation file, it shows the following message:

"The Error Code is 2920".

Do you know how to fix that ?

Thanks,

Jo?o Paulo.

sql

MSDE 2000 and merge modules

I have a vb.net desktop application that uses msde 2000 for its databasing. I am trying to create a setup file using the output of my desktop application and the msde 2000 merge modules in order to install my own build plus an instance of msde 2000.

The setup runs properly and then asks me to restart my machine. After the restart, however, there is no such instance running on my machine. I am adding the following internal properties to my msi file using ocra:

SQLMSDESelected 1
SqlInstanceName Midas
SqlSaPwd Password1
SqlSecurityMode SQL
SqlDataDir C:\Program Files\Microsoft SQL Server\MSSQL$Midas\Data
SqlProgramDir C:\Program Files\Microsoft SQL Server
This should create an instance of msde on my machine but when I try to connect using enterprize manager or query analizer (I connect to <<machinename>>\Midas), then I get the standard can not connect error.

I am at my witt's end and would really appreciate any help.

Thanks.

Hi,

maybe you could help me....

I'm trying to create a setup file using the VS2003 "Setup Project" and MSDE2000SP4 Merge Modules (editing properties with ORCA).

But when I run the installation file, it shows the following message:

"The Error Code is 2920".

Do you know how to fix that ?

Thanks,

Jo?o Paulo.

Msde 2000

Hi

I installed the MSDE that comes with Office 2000. When connecting to my mdf, I received an error saying that the header file is invalid. I understand that the MSDE shipped with Office 2000 uses the SQL 7 engine, therefore it does not recognize the my SQL 2000 database file format. Can someone tell me where can I get a copy of the correct version of MSDE that will work with my SQL 2000 mdf?

Thanks

SHKMSDE 2000 (http://www.microsoft.com/sql/msde/downloads/download.asp)|||Thank You

msde - uses too much memory and is getting very slow!

Juergen,
What else is this computer used for?
If only MSDE, then the system page file can be reduced to a minimum (or even
eliminated) -thereby reducing the memory 'pressure' on SQL Server (MSDE).
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
You can't help someone get up a hill without getting a little closer to the
top yourself.
- H. Norman Schwarzkopf
"Jrgen Strutzenberger" <stju@.gtech.at> wrote in message
news:eOxWF3zAHHA.996@.TK2MSFTNGP02.phx.gbl...
> hi newsgroup!
> we have already some msde databases installed and we didn't had any
> troubles until now!
> the OS is a Windows XP prof. SP2 with MSDE Version; msde desktop engine
> 8.00.7630SP3
> now our trouble is that the sqlserver.exe process is using more and more
> ram and the pagefile grows and grows!
> The PC (server) HP DL385 has 2 GB RAM so this shuldn't be the problem! So
> when the sqlserver.exe process is running about 2-3 days the amout of
> memory usages grows to 1,3GB and about this size the PC is realy getting
> slow, so you can't work anymore!
> The problem is solved when i start and stop the sqlserver, but this isn't
> how i will solve the problem!
> So does anyone have the some troubles, and does anybody now how to solve
> this problem?
> kind regards
> Juergen Strutzenberger
>
What has changed in the past short period of time? New VB.NET program?
Does the VB.NET program properly dispose of all connection objects created?
You may want to run performance monitor to discover what is using the cpu
cycles and the memory.
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
You can't help someone get up a hill without getting a little closer to the
top yourself.
- H. Norman Schwarzkopf
"Jrgen Strutzenberger" <stju@.gtech.at> wrote in message
news:eaVNo1BBHHA.3396@.TK2MSFTNGP02.phx.gbl...
> we also run a selfe made programm, witch is programmed with VB.net!
> we already uninstalled some programs we didn't need!
> we also removed the Norton AV because we thought that this is using to
> much ressources and the pc is getting slow!
> bye
> juergen
> "Arnie Rowland" <arnie@.1568.com> schrieb im Newsbeitrag
> news:eSaidM1AHHA.4808@.TK2MSFTNGP03.phx.gbl...
>
|||What has changed in the past short period of time? New VB.NET program?
Does the VB.NET program properly dispose of all connection objects created?
You may want to run performance monitor to discover what is using the cpu
cycles and the memory.
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
You can't help someone get up a hill without getting a little closer to the
top yourself.
- H. Norman Schwarzkopf
"Jrgen Strutzenberger" <stju@.gtech.at> wrote in message
news:eaVNo1BBHHA.3396@.TK2MSFTNGP02.phx.gbl...
> we also run a selfe made programm, witch is programmed with VB.net!
> we already uninstalled some programs we didn't need!
> we also removed the Norton AV because we thought that this is using to
> much ressources and the pc is getting slow!
> bye
> juergen
> "Arnie Rowland" <arnie@.1568.com> schrieb im Newsbeitrag
> news:eSaidM1AHHA.4808@.TK2MSFTNGP03.phx.gbl...
>
|||What has changed in the past short period of time? New VB.NET program?
Does the VB.NET program properly dispose of all connection objects created?
You may want to run performance monitor to discover what is using the cpu
cycles and the memory.
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
You can't help someone get up a hill without getting a little closer to the
top yourself.
- H. Norman Schwarzkopf
"Jrgen Strutzenberger" <stju@.gtech.at> wrote in message
news:eaVNo1BBHHA.3396@.TK2MSFTNGP02.phx.gbl...
> we also run a selfe made programm, witch is programmed with VB.net!
> we already uninstalled some programs we didn't need!
> we also removed the Norton AV because we thought that this is using to
> much ressources and the pc is getting slow!
> bye
> juergen
> "Arnie Rowland" <arnie@.1568.com> schrieb im Newsbeitrag
> news:eSaidM1AHHA.4808@.TK2MSFTNGP03.phx.gbl...
>

msde

i have an msde database i wuold like to load or unload data from or to a flat file. does any one know if this is possible and if so what is the code to do so. i would prefer to rune the commands in osql or visual basic.
Thank You,
Thomaslook into bulk import|||use BULK INSERT sql statement. this is just like bcp.

id look in BOL for more info

msdbdata.mdf file is over 5 GB, cannot shrink it

I have problem with my sql server developer edition.

I began reciving alerts regarding disk space, and found out that the msdb files are 5 GB!!! the data file is full, so I cannot shrink it, but the table usage shows only a few MB used by tables.

its not advisable to shrink the system dbs except tempdb,but in your case just check if any user tables are created in msdb if so just delete the same to free diskspace.... then try shrinking msdb but b4 shrinking ensure you have the latest backup of all the dbs....
|||

There are no user tables on this server.

It a local developer version, I use for development and small checks, there is no back up nither.

msdb or file system

When I log into Integrated Services on my SQL server, I see [Stored Package] -- File System and MSDB.

When I deploy/import my SSIS packages which should it go under? Is there a difference and if so what the difference?

thanks

HAHAHA! You asked the exact same question as someone else and within something like two hours.

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=2008865&SiteID=1

Since that question is unanswered, here's the deal.

Where you ultimately choose to store your packages, is up to you. They each do the same thing, but with some differences.

With File System storage, you can simply map a network drive to the server and just copy your packages over to the server. With SQL Server, you need to import into MSDB.

Some like to use File System, but to me, the permissions are harder to work with. Because SSIS does not save passwords, you'll have to use a configuration file to specify passwords for your connection managers. Either that, or you'll have to use EncryptSensitiveWithPassword and then specify a decryption password with the command line.

SQL Server has the same problems unless you tell it to use "SQL Server Roles and Storage" when importing. This is what I choose to use. Access is controlled via SQL Server roles/security.

Search this forum for other ideas regarding file system versus MSDB.|||

LOL

thanks, I'm not a big fan of using the file system for much and due to the permissions within the tool then going through the act of congress to get permissions setup on a network drive to use, isn't worth the hassle.

It would take me longer to get that setup (just from the network side) then it would for me to create a package, test it, import it and roll it out to production and create 10 more.

So I think I'll go the MSDB route.

|||More information:

http://blogs.conchango.com/jamiethomson/archive/2006/02/20/SSIS_3A00_-Deploy-to-file-system-or-SQL-Server.aspx

http://www.sqljunkies.com/WebLog/knight_reign/archive/2005/05/05/13523.aspx|||

thanks, I was just reading the sqljunkies.com one actually.

So far I'm not finding a clear cut answer, I guess it really depends on the person deploying the packages.

its kind of like, what language is better C# or VB.NET? no clear answer, its developer preference.

thanks again

msdb log backup failed

Hello guys,
I am sorry if I insist but I am still having this error about msdb log
file failed.
BACKUP failed to complete the command BACKUP LOG [msdb] TO [systemLog]
WITH NOINIT , NOUNLOAD , NAME = N'msdb backup', NOSKIP , STATS = 10, NOFORMAT
I do not understand why.
Could someone help me on solve this problem.
InaHi,
Can you verify the recovery model of MSDB database by executing below
command:-
select databasepropertyex('msdb','recovery')
If the result return SIMPLE then you can not perform a transaction log
backup. Incase if you need to do a log backup then change the recovery
model to FULL using below command
ALTER DATABASE MSDB SET RECOVERY FULL
After to regroup the backup chain; perform a FULL MSDB backup and then then
start the log backup.,
Thanks
Hari
"ina" <roberta.inalbon@.gmail.com> wrote in message
news:1159778124.630986.307670@.h48g2000cwc.googlegroups.com...
> Hello guys,
> I am sorry if I insist but I am still having this error about msdb log
> file failed.
> BACKUP failed to complete the command BACKUP LOG [msdb] TO [systemLog]
> WITH NOINIT , NOUNLOAD , NAME = N'msdb backup', NOSKIP , STATS => 10, NOFORMAT
>
> I do not understand why.
> Could someone help me on solve this problem.
> Ina
>|||Thanks Hari,
It was set to simple. I changed as you advice me. One question when I
schedule the schedule log backup do you thing i can do something
between 4 Am until 10 Pm (every hour) because the backup of MSDB is 23
PM ?
ina
Hari Prasad wrote:
> Hi,
> Can you verify the recovery model of MSDB database by executing below
> command:-
> select databasepropertyex('msdb','recovery')
> If the result return SIMPLE then you can not perform a transaction log
> backup. Incase if you need to do a log backup then change the recovery
> model to FULL using below command
> ALTER DATABASE MSDB SET RECOVERY FULL
> After to regroup the backup chain; perform a FULL MSDB backup and then then
> start the log backup.,
> Thanks
> Hari
>
> "ina" <roberta.inalbon@.gmail.com> wrote in message
> news:1159778124.630986.307670@.h48g2000cwc.googlegroups.com...
> > Hello guys,
> >
> > I am sorry if I insist but I am still having this error about msdb log
> > file failed.
> >
> > BACKUP failed to complete the command BACKUP LOG [msdb] TO [systemLog]
> > WITH NOINIT , NOUNLOAD , NAME = N'msdb backup', NOSKIP , STATS => > 10, NOFORMAT
> >
> >
> > I do not understand why.
> >
> > Could someone help me on solve this problem.
> >
> > Ina
> >

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]
> >>
> >>
> >>
>
>