Showing posts with label size. Show all posts

Monday, March 12, 2012

Limits

Is there a database size limitation with the desktop SQL edition'SQL Server Express is designed to meet the needs of simple applications. It
is limited to using 1 CPU and up to 1GB RAM, with a 4GB maximum database
size.
Regards,
Dave Patrick ...Please no email replies - reply in newsgroup.
Microsoft Certified Professional
Microsoft MVP [Windows]
http://www.microsoft.com/protect
"Richard Douglass" wrote:
| Is there a database size limitation with the desktop SQL edition'
|
|
||||The size of a database cannot exceed 2 GB when using the SQL Server
2000 Desktop Engine or the Microsoft Data Engine (MSDE) 1.0
Denis the SQL Menace
http://sqlservercode.blogspot.com/
Richard Douglass wrote:
> Is there a database size limitation with the desktop SQL edition'

Limits

Is there a database size limitation with the desktop SQL edition'The size of a database cannot exceed 2 GB when using the SQL Server
2000 Desktop Engine or the Microsoft Data Engine (MSDE) 1.0
Denis the SQL Menace
http://sqlservercode.blogspot.com/
Richard Douglass wrote:
> Is there a database size limitation with the desktop SQL edition'|||SQL Server Express is designed to meet the needs of simple applications. It
is limited to using 1 CPU and up to 1GB RAM, with a 4GB maximum database
size.
--
Regards,
Dave Patrick ...Please no email replies - reply in newsgroup.
Microsoft Certified Professional
Microsoft MVP [Windows]
http://www.microsoft.com/protect
"Richard Douglass" wrote:
| Is there a database size limitation with the desktop SQL edition'
|
|
|

Limiting transaction Log file size

Please forgive my inability to fully comprehend this topic. I have posted
other messages regarding similar file issues and followed the
recommendations under the message topic SQL Server 2k transaction log file
I have a transaction file that is set to unlimited growth. as of now it is
about 8.5GB. I want the file to be smaller.
I have successfully backed up the log, shrunk the file down to 700MB, and
changed the log method to simple recovery.
The problem is the file allocation is still at the 8.5GB and when I try and
change the file growth restriction to say 2.5GB, SQL will not allow the
growth restriction to be less than the allocated space (8.5GB).
Is there a way to change the allocated space and/or the file growth
restriction size?
I hope my scenario is clear.
WBSee http://www.karaszi.com/SQLServer/info_dont_shrink.asp for details on shrinking transaction log
files.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"WB" <none> wrote in message news:eQ9vVNmYGHA.1200@.TK2MSFTNGP03.phx.gbl...
> Please forgive my inability to fully comprehend this topic. I have posted
> other messages regarding similar file issues and followed the
> recommendations under the message topic SQL Server 2k transaction log file
> I have a transaction file that is set to unlimited growth. as of now it is
> about 8.5GB. I want the file to be smaller.
> I have successfully backed up the log, shrunk the file down to 700MB, and
> changed the log method to simple recovery.
> The problem is the file allocation is still at the 8.5GB and when I try and
> change the file growth restriction to say 2.5GB, SQL will not allow the
> growth restriction to be less than the allocated space (8.5GB).
> Is there a way to change the allocated space and/or the file growth
> restriction size?
> I hope my scenario is clear.
> WB
>|||Thank you for you response. I have re-examined your link and still do not
find the answer. the information you provided relates to shrinking the
file, which I have completed.
I want to change the amount of space allocated in the transaction log file.
It is to big at 8.5GB and I want it to be a maximum of 2.5GB.
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:%231j0aRmYGHA.3424@.TK2MSFTNGP02.phx.gbl...
> See http://www.karaszi.com/SQLServer/info_dont_shrink.asp for details on
shrinking transaction log
> files.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "WB" <none> wrote in message news:eQ9vVNmYGHA.1200@.TK2MSFTNGP03.phx.gbl...
> > Please forgive my inability to fully comprehend this topic. I have
posted
> > other messages regarding similar file issues and followed the
> > recommendations under the message topic SQL Server 2k transaction log
file
> >
> > I have a transaction file that is set to unlimited growth. as of now it
is
> > about 8.5GB. I want the file to be smaller.
> >
> > I have successfully backed up the log, shrunk the file down to 700MB,
and
> > changed the log method to simple recovery.
> >
> > The problem is the file allocation is still at the 8.5GB and when I try
and
> > change the file growth restriction to say 2.5GB, SQL will not allow the
> > growth restriction to be less than the allocated space (8.5GB).
> >
> > Is there a way to change the allocated space and/or the file growth
> > restriction size?
> >
> > I hope my scenario is clear.
> >
> > WB
> >
> >
>|||I'm afraid I don't follow you...
You say that file is 700MB. How did you determine that number?
You also say that the file allocation is 8.5GB. I fail to see how the allocation is larger than the
file size. Where did you see this number? Or did you mean the max file size?
For the above two numbers, use DBCC SQLPERF(LOGSPACE)
How do you try to change the max file size? You can try ALTER DATABASE ... MODIFY FILE, perhaps the
GUI is doing something strange...
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"WB" <none> wrote in message news:eN$AGemYGHA.3516@.TK2MSFTNGP03.phx.gbl...
> Thank you for you response. I have re-examined your link and still do not
> find the answer. the information you provided relates to shrinking the
> file, which I have completed.
> I want to change the amount of space allocated in the transaction log file.
> It is to big at 8.5GB and I want it to be a maximum of 2.5GB.
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
> message news:%231j0aRmYGHA.3424@.TK2MSFTNGP02.phx.gbl...
>> See http://www.karaszi.com/SQLServer/info_dont_shrink.asp for details on
> shrinking transaction log
>> files.
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://www.solidqualitylearning.com/
>> Blog: http://solidqualitylearning.com/blogs/tibor/
>>
>> "WB" <none> wrote in message news:eQ9vVNmYGHA.1200@.TK2MSFTNGP03.phx.gbl...
>> > Please forgive my inability to fully comprehend this topic. I have
> posted
>> > other messages regarding similar file issues and followed the
>> > recommendations under the message topic SQL Server 2k transaction log
> file
>> >
>> > I have a transaction file that is set to unlimited growth. as of now it
> is
>> > about 8.5GB. I want the file to be smaller.
>> >
>> > I have successfully backed up the log, shrunk the file down to 700MB,
> and
>> > changed the log method to simple recovery.
>> >
>> > The problem is the file allocation is still at the 8.5GB and when I try
> and
>> > change the file growth restriction to say 2.5GB, SQL will not allow the
>> > growth restriction to be less than the allocated space (8.5GB).
>> >
>> > Is there a way to change the allocated space and/or the file growth
>> > restriction size?
>> >
>> > I hope my scenario is clear.
>> >
>> > WB
>> >
>> >
>|||Its still not very clear what you have done so far. You can shrink the log
file, disable the auto grow and turn on "truncate log on checkpoint".
Jayesh
"WB" <none> wrote in message news:eQ9vVNmYGHA.1200@.TK2MSFTNGP03.phx.gbl...
> Please forgive my inability to fully comprehend this topic. I have posted
> other messages regarding similar file issues and followed the
> recommendations under the message topic SQL Server 2k transaction log file
> I have a transaction file that is set to unlimited growth. as of now it
> is
> about 8.5GB. I want the file to be smaller.
> I have successfully backed up the log, shrunk the file down to 700MB, and
> changed the log method to simple recovery.
> The problem is the file allocation is still at the 8.5GB and when I try
> and
> change the file growth restriction to say 2.5GB, SQL will not allow the
> growth restriction to be less than the allocated space (8.5GB).
> Is there a way to change the allocated space and/or the file growth
> restriction size?
> I hope my scenario is clear.
> WB
>|||I was deriving my numbers from Enterprise Manager by looking at the
properties of the database.
When I try your suggested method the result is the same. It shows the log
size in MB as being 8,415 and the % of space used as 8.7. This would seem
to translate to the numbers I provided.
Yes, what I want to do is change the max file size.
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:ObZ3b%23qYGHA.4760@.TK2MSFTNGP03.phx.gbl...
> I'm afraid I don't follow you...
> You say that file is 700MB. How did you determine that number?
> You also say that the file allocation is 8.5GB. I fail to see how the
allocation is larger than the
> file size. Where did you see this number? Or did you mean the max file
size?
> For the above two numbers, use DBCC SQLPERF(LOGSPACE)
> How do you try to change the max file size? You can try ALTER DATABASE ...
MODIFY FILE, perhaps the
> GUI is doing something strange...
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "WB" <none> wrote in message news:eN$AGemYGHA.3516@.TK2MSFTNGP03.phx.gbl...
> > Thank you for you response. I have re-examined your link and still do
not
> > find the answer. the information you provided relates to shrinking the
> > file, which I have completed.
> >
> > I want to change the amount of space allocated in the transaction log
file.
> > It is to big at 8.5GB and I want it to be a maximum of 2.5GB.
> >
> > "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote
in
> > message news:%231j0aRmYGHA.3424@.TK2MSFTNGP02.phx.gbl...
> >> See http://www.karaszi.com/SQLServer/info_dont_shrink.asp for details
on
> > shrinking transaction log
> >> files.
> >>
> >> --
> >> Tibor Karaszi, SQL Server MVP
> >> http://www.karaszi.com/sqlserver/default.asp
> >> http://www.solidqualitylearning.com/
> >> Blog: http://solidqualitylearning.com/blogs/tibor/
> >>
> >>
> >> "WB" <none> wrote in message
news:eQ9vVNmYGHA.1200@.TK2MSFTNGP03.phx.gbl...
> >> > Please forgive my inability to fully comprehend this topic. I have
> > posted
> >> > other messages regarding similar file issues and followed the
> >> > recommendations under the message topic SQL Server 2k transaction log
> > file
> >> >
> >> > I have a transaction file that is set to unlimited growth. as of now
it
> > is
> >> > about 8.5GB. I want the file to be smaller.
> >> >
> >> > I have successfully backed up the log, shrunk the file down to 700MB,
> > and
> >> > changed the log method to simple recovery.
> >> >
> >> > The problem is the file allocation is still at the 8.5GB and when I
try
> > and
> >> > change the file growth restriction to say 2.5GB, SQL will not allow
the
> >> > growth restriction to be less than the allocated space (8.5GB).
> >> >
> >> > Is there a way to change the allocated space and/or the file growth
> >> > restriction size?
> >> >
> >> > I hope my scenario is clear.
> >> >
> >> > WB
> >> >
> >> >
> >>
> >
> >
>|||So the current size it 8,415 and utilization it 8.7%. To shrink the file size, see
http://www.karaszi.com/SQLServer/info_dont_shrink.asp. You can then set a max size, but is more than
that is needed, the modifications will fail.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"WB" <none> wrote in message news:uvT8UvwYGHA.1888@.TK2MSFTNGP02.phx.gbl...
>I was deriving my numbers from Enterprise Manager by looking at the
> properties of the database.
> When I try your suggested method the result is the same. It shows the log
> size in MB as being 8,415 and the % of space used as 8.7. This would seem
> to translate to the numbers I provided.
> Yes, what I want to do is change the max file size.
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
> message news:ObZ3b%23qYGHA.4760@.TK2MSFTNGP03.phx.gbl...
>> I'm afraid I don't follow you...
>> You say that file is 700MB. How did you determine that number?
>> You also say that the file allocation is 8.5GB. I fail to see how the
> allocation is larger than the
>> file size. Where did you see this number? Or did you mean the max file
> size?
>> For the above two numbers, use DBCC SQLPERF(LOGSPACE)
>> How do you try to change the max file size? You can try ALTER DATABASE ...
> MODIFY FILE, perhaps the
>> GUI is doing something strange...
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://www.solidqualitylearning.com/
>>
>> "WB" <none> wrote in message news:eN$AGemYGHA.3516@.TK2MSFTNGP03.phx.gbl...
>> > Thank you for you response. I have re-examined your link and still do
> not
>> > find the answer. the information you provided relates to shrinking the
>> > file, which I have completed.
>> >
>> > I want to change the amount of space allocated in the transaction log
> file.
>> > It is to big at 8.5GB and I want it to be a maximum of 2.5GB.
>> >
>> > "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote
> in
>> > message news:%231j0aRmYGHA.3424@.TK2MSFTNGP02.phx.gbl...
>> >> See http://www.karaszi.com/SQLServer/info_dont_shrink.asp for details
> on
>> > shrinking transaction log
>> >> files.
>> >>
>> >> --
>> >> Tibor Karaszi, SQL Server MVP
>> >> http://www.karaszi.com/sqlserver/default.asp
>> >> http://www.solidqualitylearning.com/
>> >> Blog: http://solidqualitylearning.com/blogs/tibor/
>> >>
>> >>
>> >> "WB" <none> wrote in message
> news:eQ9vVNmYGHA.1200@.TK2MSFTNGP03.phx.gbl...
>> >> > Please forgive my inability to fully comprehend this topic. I have
>> > posted
>> >> > other messages regarding similar file issues and followed the
>> >> > recommendations under the message topic SQL Server 2k transaction log
>> > file
>> >> >
>> >> > I have a transaction file that is set to unlimited growth. as of now
> it
>> > is
>> >> > about 8.5GB. I want the file to be smaller.
>> >> >
>> >> > I have successfully backed up the log, shrunk the file down to 700MB,
>> > and
>> >> > changed the log method to simple recovery.
>> >> >
>> >> > The problem is the file allocation is still at the 8.5GB and when I
> try
>> > and
>> >> > change the file growth restriction to say 2.5GB, SQL will not allow
> the
>> >> > growth restriction to be less than the allocated space (8.5GB).
>> >> >
>> >> > Is there a way to change the allocated space and/or the file growth
>> >> > restriction size?
>> >> >
>> >> > I hope my scenario is clear.
>> >> >
>> >> > WB
>> >> >
>> >> >
>> >>
>> >
>> >
>

Limiting transaction Log file size

Limiting size of a table in SQL server

hi,

im doing network monitoring app where basically i run a checks on servers every few minutes and log the data to a table. Naturally the table can get big, quite quickly. What I want is to be able to overwrite the table data at the start of each new day. Alternatively, rollup the data into a daily or weekly packets and then overwrite table data. How do i do this?Using a scheduled job in SQL Server would likely be the easiest. Create the code to do what you want in a stored procedure, and then schedule the stored procedure.

In Enterprise Manager, expand the database till you see Management, expand that and right click on Jobs below it and select New Job to create and schedule the job.

Friday, March 9, 2012

Limiting job history in SQL2005

Hi,
Under SQL Agent properties/History there are 2 limiting columns for Limit
size of job history log.
We have Maximum job history set to 12000 and maximum job history rows per
job at 150 but we only seem to be able to keep 2 or 3 days worth of
jobhistory even though looking in sysjobhistory I can only return 7300 rows.
I thought that it would keep a maximum of 150 per job to a total of 12000.
Is something else limiting the data in sysjobhistory?
Thanks
ChrisHi,
We found the problem. If you run a maintenance plan that has a History
Cleanup Task and its run by an admin level account then the
sp_purge_jobhistory does not include a @.job_name parameter. This combination
means that all job history gets cleaned up rather than just the job that has
the History Cleanup Task problem raised on Connect as bug 322320.
Chris
"Chris Wood" <anonymous@.microsoft.com> wrote in message
news:O9nm5EQPIHA.4272@.TK2MSFTNGP06.phx.gbl...
> Hi,
> Under SQL Agent properties/History there are 2 limiting columns for Limit
> size of job history log.
> We have Maximum job history set to 12000 and maximum job history rows per
> job at 150 but we only seem to be able to keep 2 or 3 days worth of
> jobhistory even though looking in sysjobhistory I can only return 7300
> rows.
> I thought that it would keep a maximum of 150 per job to a total of 12000.
> Is something else limiting the data in sysjobhistory?
> Thanks
> Chris
>|||Very interesting Chris. Thanks for the update.
--
Andrew J. Kelly SQL MVP
Solid Quality Mentors
"Chris Wood" <anonymous@.microsoft.com> wrote in message
news:eGw2yxcYIHA.4208@.TK2MSFTNGP04.phx.gbl...
> Hi,
> We found the problem. If you run a maintenance plan that has a History
> Cleanup Task and its run by an admin level account then the
> sp_purge_jobhistory does not include a @.job_name parameter. This
> combination means that all job history gets cleaned up rather than just
> the job that has the History Cleanup Task problem raised on Connect as bug
> 322320.
> Chris
> "Chris Wood" <anonymous@.microsoft.com> wrote in message
> news:O9nm5EQPIHA.4272@.TK2MSFTNGP06.phx.gbl...
>> Hi,
>> Under SQL Agent properties/History there are 2 limiting columns for Limit
>> size of job history log.
>> We have Maximum job history set to 12000 and maximum job history rows per
>> job at 150 but we only seem to be able to keep 2 or 3 days worth of
>> jobhistory even though looking in sysjobhistory I can only return 7300
>> rows.
>> I thought that it would keep a maximum of 150 per job to a total of
>> 12000.
>> Is something else limiting the data in sysjobhistory?
>> Thanks
>> Chris
>|||Unfortunately Andrew I have not seen any reposnse to my Connect post.
Thanks
Chris
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:Or3yu6cYIHA.4808@.TK2MSFTNGP05.phx.gbl...
> Very interesting Chris. Thanks for the update.
> --
> Andrew J. Kelly SQL MVP
> Solid Quality Mentors
>
> "Chris Wood" <anonymous@.microsoft.com> wrote in message
> news:eGw2yxcYIHA.4208@.TK2MSFTNGP04.phx.gbl...
>> Hi,
>> We found the problem. If you run a maintenance plan that has a History
>> Cleanup Task and its run by an admin level account then the
>> sp_purge_jobhistory does not include a @.job_name parameter. This
>> combination means that all job history gets cleaned up rather than just
>> the job that has the History Cleanup Task problem raised on Connect as
>> bug 322320.
>> Chris
>> "Chris Wood" <anonymous@.microsoft.com> wrote in message
>> news:O9nm5EQPIHA.4272@.TK2MSFTNGP06.phx.gbl...
>> Hi,
>> Under SQL Agent properties/History there are 2 limiting columns for
>> Limit size of job history log.
>> We have Maximum job history set to 12000 and maximum job history rows
>> per job at 150 but we only seem to be able to keep 2 or 3 days worth of
>> jobhistory even though looking in sysjobhistory I can only return 7300
>> rows.
>> I thought that it would keep a maximum of 150 per job to a total of
>> 12000.
>> Is something else limiting the data in sysjobhistory?
>> Thanks
>> Chris
>>
>|||Actually I just had a look at the sp_purge_jobhistory sp and the bottom line
is that if you don't specify a job ID or name it will delete all history
beyond the date specified. It makes no difference how many rows you have set
in SQL Agent as it does not even check for that. So if you want to keep x
many days worth you need to ensure that the date specified in the Maint Plan
for the history cleanup is far enough out so the history is maintained.
There is no link between the two. The Agent behavior was that way long
before the maint plan had the hist cleanup task and they simply didn't
factor in agents settings when they wrote the task for the maint plans. They
should have have had you specify a minimum # of rows deform they deleted
them by date only.
--
Andrew J. Kelly SQL MVP
Solid Quality Mentors
"Chris Wood" <anonymous@.microsoft.com> wrote in message
news:OoK7jCdYIHA.1208@.TK2MSFTNGP03.phx.gbl...
> Unfortunately Andrew I have not seen any reposnse to my Connect post.
> Thanks
> Chris
> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> news:Or3yu6cYIHA.4808@.TK2MSFTNGP05.phx.gbl...
>> Very interesting Chris. Thanks for the update.
>> --
>> Andrew J. Kelly SQL MVP
>> Solid Quality Mentors
>>
>> "Chris Wood" <anonymous@.microsoft.com> wrote in message
>> news:eGw2yxcYIHA.4208@.TK2MSFTNGP04.phx.gbl...
>> Hi,
>> We found the problem. If you run a maintenance plan that has a History
>> Cleanup Task and its run by an admin level account then the
>> sp_purge_jobhistory does not include a @.job_name parameter. This
>> combination means that all job history gets cleaned up rather than just
>> the job that has the History Cleanup Task problem raised on Connect as
>> bug 322320.
>> Chris
>> "Chris Wood" <anonymous@.microsoft.com> wrote in message
>> news:O9nm5EQPIHA.4272@.TK2MSFTNGP06.phx.gbl...
>> Hi,
>> Under SQL Agent properties/History there are 2 limiting columns for
>> Limit size of job history log.
>> We have Maximum job history set to 12000 and maximum job history rows
>> per job at 150 but we only seem to be able to keep 2 or 3 days worth of
>> jobhistory even though looking in sysjobhistory I can only return 7300
>> rows.
>> I thought that it would keep a maximum of 150 per job to a total of
>> 12000.
>> Is something else limiting the data in sysjobhistory?
>> Thanks
>> Chris
>>
>>
>|||Andrew,
That's part of the problem. BOL states that if you are an admin then a
straight sp_purge_jobhistory, without the @.job_name will purge ALL
jobhistory. This is my problem. The maintenance plan does not include the
@.job_name parameter otherwise this might have worked fine. We had to look at
all our jobs that do a cleanup and are run by an admin account to make sure
that we have made the cleanup period long enough.
Chris
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:uW1Q5tdYIHA.5984@.TK2MSFTNGP06.phx.gbl...
> Actually I just had a look at the sp_purge_jobhistory sp and the bottom
> line is that if you don't specify a job ID or name it will delete all
> history beyond the date specified. It makes no difference how many rows
> you have set in SQL Agent as it does not even check for that. So if you
> want to keep x many days worth you need to ensure that the date specified
> in the Maint Plan for the history cleanup is far enough out so the history
> is maintained. There is no link between the two. The Agent behavior was
> that way long before the maint plan had the hist cleanup task and they
> simply didn't factor in agents settings when they wrote the task for the
> maint plans. They should have have had you specify a minimum # of rows
> deform they deleted them by date only.
> --
> Andrew J. Kelly SQL MVP
> Solid Quality Mentors
>
> "Chris Wood" <anonymous@.microsoft.com> wrote in message
> news:OoK7jCdYIHA.1208@.TK2MSFTNGP03.phx.gbl...
>> Unfortunately Andrew I have not seen any reposnse to my Connect post.
>> Thanks
>> Chris
>> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
>> news:Or3yu6cYIHA.4808@.TK2MSFTNGP05.phx.gbl...
>> Very interesting Chris. Thanks for the update.
>> --
>> Andrew J. Kelly SQL MVP
>> Solid Quality Mentors
>>
>> "Chris Wood" <anonymous@.microsoft.com> wrote in message
>> news:eGw2yxcYIHA.4208@.TK2MSFTNGP04.phx.gbl...
>> Hi,
>> We found the problem. If you run a maintenance plan that has a History
>> Cleanup Task and its run by an admin level account then the
>> sp_purge_jobhistory does not include a @.job_name parameter. This
>> combination means that all job history gets cleaned up rather than just
>> the job that has the History Cleanup Task problem raised on Connect as
>> bug 322320.
>> Chris
>> "Chris Wood" <anonymous@.microsoft.com> wrote in message
>> news:O9nm5EQPIHA.4272@.TK2MSFTNGP06.phx.gbl...
>> Hi,
>> Under SQL Agent properties/History there are 2 limiting columns for
>> Limit size of job history log.
>> We have Maximum job history set to 12000 and maximum job history rows
>> per job at 150 but we only seem to be able to keep 2 or 3 days worth
>> of jobhistory even though looking in sysjobhistory I can only return
>> 7300 rows.
>> I thought that it would keep a maximum of 150 per job to a total of
>> 12000.
>> Is something else limiting the data in sysjobhistory?
>> Thanks
>> Chris
>>
>>
>>
>|||Chris,
That only means that you need it to be in the sa or SQLAgentOperatorRole in
order to modify any jobs that are not you own. Since you don't specify a job
ID it has to assume you need to be in one of those two roles. If not it
throws an exception and does nothing. So if you want to keep a certain
amount of history for a particular job I suggest you create your won
scheduled job that calls sp_purge_jobhistory with the proper Job names and
dates for each. You may need to set up a table with any jobs that need a
date other than the default you pass. It's basically do it for every job by
date or one job at a time by date and job id or name. Unfortunately there is
no in between.
--
Andrew J. Kelly SQL MVP
Solid Quality Mentors
"Chris Wood" <anonymous@.microsoft.com> wrote in message
news:%232tTjgeYIHA.3652@.TK2MSFTNGP02.phx.gbl...
> Andrew,
> That's part of the problem. BOL states that if you are an admin then a
> straight sp_purge_jobhistory, without the @.job_name will purge ALL
> jobhistory. This is my problem. The maintenance plan does not include the
> @.job_name parameter otherwise this might have worked fine. We had to look
> at all our jobs that do a cleanup and are run by an admin account to make
> sure that we have made the cleanup period long enough.
> Chris
> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> news:uW1Q5tdYIHA.5984@.TK2MSFTNGP06.phx.gbl...
>> Actually I just had a look at the sp_purge_jobhistory sp and the bottom
>> line is that if you don't specify a job ID or name it will delete all
>> history beyond the date specified. It makes no difference how many rows
>> you have set in SQL Agent as it does not even check for that. So if you
>> want to keep x many days worth you need to ensure that the date specified
>> in the Maint Plan for the history cleanup is far enough out so the
>> history is maintained. There is no link between the two. The Agent
>> behavior was that way long before the maint plan had the hist cleanup
>> task and they simply didn't factor in agents settings when they wrote the
>> task for the maint plans. They should have have had you specify a minimum
>> # of rows deform they deleted them by date only.
>> --
>> Andrew J. Kelly SQL MVP
>> Solid Quality Mentors
>>
>> "Chris Wood" <anonymous@.microsoft.com> wrote in message
>> news:OoK7jCdYIHA.1208@.TK2MSFTNGP03.phx.gbl...
>> Unfortunately Andrew I have not seen any reposnse to my Connect post.
>> Thanks
>> Chris
>> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
>> news:Or3yu6cYIHA.4808@.TK2MSFTNGP05.phx.gbl...
>> Very interesting Chris. Thanks for the update.
>> --
>> Andrew J. Kelly SQL MVP
>> Solid Quality Mentors
>>
>> "Chris Wood" <anonymous@.microsoft.com> wrote in message
>> news:eGw2yxcYIHA.4208@.TK2MSFTNGP04.phx.gbl...
>> Hi,
>> We found the problem. If you run a maintenance plan that has a History
>> Cleanup Task and its run by an admin level account then the
>> sp_purge_jobhistory does not include a @.job_name parameter. This
>> combination means that all job history gets cleaned up rather than
>> just the job that has the History Cleanup Task problem raised on
>> Connect as bug 322320.
>> Chris
>> "Chris Wood" <anonymous@.microsoft.com> wrote in message
>> news:O9nm5EQPIHA.4272@.TK2MSFTNGP06.phx.gbl...
>> Hi,
>> Under SQL Agent properties/History there are 2 limiting columns for
>> Limit size of job history log.
>> We have Maximum job history set to 12000 and maximum job history rows
>> per job at 150 but we only seem to be able to keep 2 or 3 days worth
>> of jobhistory even though looking in sysjobhistory I can only return
>> 7300 rows.
>> I thought that it would keep a maximum of 150 per job to a total of
>> 12000.
>> Is something else limiting the data in sysjobhistory?
>> Thanks
>> Chris
>>
>>
>>
>|||Thanks again Andrew. MS could always fix the bug and include the @.job_name
in the script they produce.
I don't see us changing our backups as you have to be an admin to run the
xp_delete_file that comes from the Maintenance Cleanup Task (See bug 247463
in Connect).
Chris
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:eJzpwveYIHA.4272@.TK2MSFTNGP05.phx.gbl...
> Chris,
> That only means that you need it to be in the sa or SQLAgentOperatorRole
> in order to modify any jobs that are not you own. Since you don't specify
> a job ID it has to assume you need to be in one of those two roles. If not
> it throws an exception and does nothing. So if you want to keep a certain
> amount of history for a particular job I suggest you create your won
> scheduled job that calls sp_purge_jobhistory with the proper Job names and
> dates for each. You may need to set up a table with any jobs that need a
> date other than the default you pass. It's basically do it for every job
> by date or one job at a time by date and job id or name. Unfortunately
> there is no in between.
> --
> Andrew J. Kelly SQL MVP
> Solid Quality Mentors
>
> "Chris Wood" <anonymous@.microsoft.com> wrote in message
> news:%232tTjgeYIHA.3652@.TK2MSFTNGP02.phx.gbl...
>> Andrew,
>> That's part of the problem. BOL states that if you are an admin then a
>> straight sp_purge_jobhistory, without the @.job_name will purge ALL
>> jobhistory. This is my problem. The maintenance plan does not include the
>> @.job_name parameter otherwise this might have worked fine. We had to look
>> at all our jobs that do a cleanup and are run by an admin account to make
>> sure that we have made the cleanup period long enough.
>> Chris
>> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
>> news:uW1Q5tdYIHA.5984@.TK2MSFTNGP06.phx.gbl...
>> Actually I just had a look at the sp_purge_jobhistory sp and the bottom
>> line is that if you don't specify a job ID or name it will delete all
>> history beyond the date specified. It makes no difference how many rows
>> you have set in SQL Agent as it does not even check for that. So if you
>> want to keep x many days worth you need to ensure that the date
>> specified in the Maint Plan for the history cleanup is far enough out so
>> the history is maintained. There is no link between the two. The Agent
>> behavior was that way long before the maint plan had the hist cleanup
>> task and they simply didn't factor in agents settings when they wrote
>> the task for the maint plans. They should have have had you specify a
>> minimum # of rows deform they deleted them by date only.
>> --
>> Andrew J. Kelly SQL MVP
>> Solid Quality Mentors
>>
>> "Chris Wood" <anonymous@.microsoft.com> wrote in message
>> news:OoK7jCdYIHA.1208@.TK2MSFTNGP03.phx.gbl...
>> Unfortunately Andrew I have not seen any reposnse to my Connect post.
>> Thanks
>> Chris
>> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
>> news:Or3yu6cYIHA.4808@.TK2MSFTNGP05.phx.gbl...
>> Very interesting Chris. Thanks for the update.
>> --
>> Andrew J. Kelly SQL MVP
>> Solid Quality Mentors
>>
>> "Chris Wood" <anonymous@.microsoft.com> wrote in message
>> news:eGw2yxcYIHA.4208@.TK2MSFTNGP04.phx.gbl...
>> Hi,
>> We found the problem. If you run a maintenance plan that has a
>> History Cleanup Task and its run by an admin level account then the
>> sp_purge_jobhistory does not include a @.job_name parameter. This
>> combination means that all job history gets cleaned up rather than
>> just the job that has the History Cleanup Task problem raised on
>> Connect as bug 322320.
>> Chris
>> "Chris Wood" <anonymous@.microsoft.com> wrote in message
>> news:O9nm5EQPIHA.4272@.TK2MSFTNGP06.phx.gbl...
>>> Hi,
>>>
>>> Under SQL Agent properties/History there are 2 limiting columns for
>>> Limit size of job history log.
>>> We have Maximum job history set to 12000 and maximum job history
>>> rows per job at 150 but we only seem to be able to keep 2 or 3 days
>>> worth of jobhistory even though looking in sysjobhistory I can only
>>> return 7300 rows.
>>>
>>> I thought that it would keep a maximum of 150 per job to a total of
>>> 12000.
>>>
>>> Is something else limiting the data in sysjobhistory?
>>>
>>> Thanks
>>>
>>> Chris
>>>
>>
>>
>>
>>
>|||> Thanks again Andrew. MS could always fix the bug and include the @.job_name
> in the script they produce.
I think you are missing my point. The task in the Maint Plan does not have a
way to specify the job only a date. This isn't a bug it is simply how they
created the task. How do you expect them to specify the job name? Which job
name are they supposed to use? Like I said the task is not in any way shape
or form tied to SQL Agent. It simply deletes rows from MSDB that happen to
be the Agent history. But it knows nothing of the jobs themselves. If it
looped thru each job one by one and specified the name it would still delete
all the rows for each job that were after the date you specified. You end up
with the same result. So this isn't a bug and I am sure they will not fix it
as such. If you want anything other than that behavior you must create it
your self. The Maint Plans are extremely limiting in what they can do and
how they do it. And it is not always clear on what it actually does unless
you trace the plans actions. One of the main reasons why I almost never
recommend using Maint Plans in the first place.
--
Andrew J. Kelly SQL MVP
Solid Quality Mentors
"Chris Wood" <anonymous@.microsoft.com> wrote in message
news:um0fh8eYIHA.5132@.TK2MSFTNGP02.phx.gbl...
> Thanks again Andrew. MS could always fix the bug and include the @.job_name
> in the script they produce.
> I don't see us changing our backups as you have to be an admin to run the
> xp_delete_file that comes from the Maintenance Cleanup Task (See bug
> 247463 in Connect).
> Chris
> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> news:eJzpwveYIHA.4272@.TK2MSFTNGP05.phx.gbl...
>> Chris,
>> That only means that you need it to be in the sa or SQLAgentOperatorRole
>> in order to modify any jobs that are not you own. Since you don't specify
>> a job ID it has to assume you need to be in one of those two roles. If
>> not it throws an exception and does nothing. So if you want to keep a
>> certain amount of history for a particular job I suggest you create your
>> won scheduled job that calls sp_purge_jobhistory with the proper Job
>> names and dates for each. You may need to set up a table with any jobs
>> that need a date other than the default you pass. It's basically do it
>> for every job by date or one job at a time by date and job id or name.
>> Unfortunately there is no in between.
>> --
>> Andrew J. Kelly SQL MVP
>> Solid Quality Mentors
>>
>> "Chris Wood" <anonymous@.microsoft.com> wrote in message
>> news:%232tTjgeYIHA.3652@.TK2MSFTNGP02.phx.gbl...
>> Andrew,
>> That's part of the problem. BOL states that if you are an admin then a
>> straight sp_purge_jobhistory, without the @.job_name will purge ALL
>> jobhistory. This is my problem. The maintenance plan does not include
>> the @.job_name parameter otherwise this might have worked fine. We had to
>> look at all our jobs that do a cleanup and are run by an admin account
>> to make sure that we have made the cleanup period long enough.
>> Chris
>> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
>> news:uW1Q5tdYIHA.5984@.TK2MSFTNGP06.phx.gbl...
>> Actually I just had a look at the sp_purge_jobhistory sp and the bottom
>> line is that if you don't specify a job ID or name it will delete all
>> history beyond the date specified. It makes no difference how many rows
>> you have set in SQL Agent as it does not even check for that. So if
>> you want to keep x many days worth you need to ensure that the date
>> specified in the Maint Plan for the history cleanup is far enough out
>> so the history is maintained. There is no link between the two. The
>> Agent behavior was that way long before the maint plan had the hist
>> cleanup task and they simply didn't factor in agents settings when they
>> wrote the task for the maint plans. They should have have had you
>> specify a minimum # of rows deform they deleted them by date only.
>> --
>> Andrew J. Kelly SQL MVP
>> Solid Quality Mentors
>>
>> "Chris Wood" <anonymous@.microsoft.com> wrote in message
>> news:OoK7jCdYIHA.1208@.TK2MSFTNGP03.phx.gbl...
>> Unfortunately Andrew I have not seen any reposnse to my Connect post.
>> Thanks
>> Chris
>> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
>> news:Or3yu6cYIHA.4808@.TK2MSFTNGP05.phx.gbl...
>> Very interesting Chris. Thanks for the update.
>> --
>> Andrew J. Kelly SQL MVP
>> Solid Quality Mentors
>>
>> "Chris Wood" <anonymous@.microsoft.com> wrote in message
>> news:eGw2yxcYIHA.4208@.TK2MSFTNGP04.phx.gbl...
>>> Hi,
>>>
>>> We found the problem. If you run a maintenance plan that has a
>>> History Cleanup Task and its run by an admin level account then the
>>> sp_purge_jobhistory does not include a @.job_name parameter. This
>>> combination means that all job history gets cleaned up rather than
>>> just the job that has the History Cleanup Task problem raised on
>>> Connect as bug 322320.
>>>
>>> Chris
>>>
>>> "Chris Wood" <anonymous@.microsoft.com> wrote in message
>>> news:O9nm5EQPIHA.4272@.TK2MSFTNGP06.phx.gbl...
>>> Hi,
>>>
>>> Under SQL Agent properties/History there are 2 limiting columns for
>>> Limit size of job history log.
>>> We have Maximum job history set to 12000 and maximum job history
>>> rows per job at 150 but we only seem to be able to keep 2 or 3 days
>>> worth of jobhistory even though looking in sysjobhistory I can only
>>> return 7300 rows.
>>>
>>> I thought that it would keep a maximum of 150 per job to a total of
>>> 12000.
>>>
>>> Is something else limiting the data in sysjobhistory?
>>>
>>> Thanks
>>>
>>> Chris
>>>
>>>
>>>
>>
>>
>>
>|||I see your point Andrew. I hadn't thought about how the Maint Plan would use
the Maintenance Cleanup Task. I am not a Visual Studio user so I don't
always see how the toolset gets used in Management Studio.
It definitely sounds as though taking this task out of the maintenance plans
and creating a nightly job could be the best option.
Chris
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:%23I2X1bfYIHA.4172@.TK2MSFTNGP02.phx.gbl...
>> Thanks again Andrew. MS could always fix the bug and include the
>> @.job_name in the script they produce.
> I think you are missing my point. The task in the Maint Plan does not have
> a way to specify the job only a date. This isn't a bug it is simply how
> they created the task. How do you expect them to specify the job name?
> Which job name are they supposed to use? Like I said the task is not in
> any way shape or form tied to SQL Agent. It simply deletes rows from MSDB
> that happen to be the Agent history. But it knows nothing of the jobs
> themselves. If it looped thru each job one by one and specified the name
> it would still delete all the rows for each job that were after the date
> you specified. You end up with the same result. So this isn't a bug and I
> am sure they will not fix it as such. If you want anything other than that
> behavior you must create it your self. The Maint Plans are extremely
> limiting in what they can do and how they do it. And it is not always
> clear on what it actually does unless you trace the plans actions. One of
> the main reasons why I almost never recommend using Maint Plans in the
> first place.
> --
> Andrew J. Kelly SQL MVP
> Solid Quality Mentors
>
> "Chris Wood" <anonymous@.microsoft.com> wrote in message
> news:um0fh8eYIHA.5132@.TK2MSFTNGP02.phx.gbl...
>> Thanks again Andrew. MS could always fix the bug and include the
>> @.job_name in the script they produce.
>> I don't see us changing our backups as you have to be an admin to run the
>> xp_delete_file that comes from the Maintenance Cleanup Task (See bug
>> 247463 in Connect).
>> Chris
>> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
>> news:eJzpwveYIHA.4272@.TK2MSFTNGP05.phx.gbl...
>> Chris,
>> That only means that you need it to be in the sa or SQLAgentOperatorRole
>> in order to modify any jobs that are not you own. Since you don't
>> specify a job ID it has to assume you need to be in one of those two
>> roles. If not it throws an exception and does nothing. So if you want to
>> keep a certain amount of history for a particular job I suggest you
>> create your won scheduled job that calls sp_purge_jobhistory with the
>> proper Job names and dates for each. You may need to set up a table with
>> any jobs that need a date other than the default you pass. It's
>> basically do it for every job by date or one job at a time by date and
>> job id or name. Unfortunately there is no in between.
>> --
>> Andrew J. Kelly SQL MVP
>> Solid Quality Mentors
>>
>> "Chris Wood" <anonymous@.microsoft.com> wrote in message
>> news:%232tTjgeYIHA.3652@.TK2MSFTNGP02.phx.gbl...
>> Andrew,
>> That's part of the problem. BOL states that if you are an admin then a
>> straight sp_purge_jobhistory, without the @.job_name will purge ALL
>> jobhistory. This is my problem. The maintenance plan does not include
>> the @.job_name parameter otherwise this might have worked fine. We had
>> to look at all our jobs that do a cleanup and are run by an admin
>> account to make sure that we have made the cleanup period long enough.
>> Chris
>> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
>> news:uW1Q5tdYIHA.5984@.TK2MSFTNGP06.phx.gbl...
>> Actually I just had a look at the sp_purge_jobhistory sp and the
>> bottom line is that if you don't specify a job ID or name it will
>> delete all history beyond the date specified. It makes no difference
>> how many rows you have set in SQL Agent as it does not even check for
>> that. So if you want to keep x many days worth you need to ensure
>> that the date specified in the Maint Plan for the history cleanup is
>> far enough out so the history is maintained. There is no link between
>> the two. The Agent behavior was that way long before the maint plan
>> had the hist cleanup task and they simply didn't factor in agents
>> settings when they wrote the task for the maint plans. They should
>> have have had you specify a minimum # of rows deform they deleted them
>> by date only.
>> --
>> Andrew J. Kelly SQL MVP
>> Solid Quality Mentors
>>
>> "Chris Wood" <anonymous@.microsoft.com> wrote in message
>> news:OoK7jCdYIHA.1208@.TK2MSFTNGP03.phx.gbl...
>> Unfortunately Andrew I have not seen any reposnse to my Connect post.
>> Thanks
>> Chris
>> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
>> news:Or3yu6cYIHA.4808@.TK2MSFTNGP05.phx.gbl...
>>> Very interesting Chris. Thanks for the update.
>>>
>>> --
>>> Andrew J. Kelly SQL MVP
>>> Solid Quality Mentors
>>>
>>>
>>> "Chris Wood" <anonymous@.microsoft.com> wrote in message
>>> news:eGw2yxcYIHA.4208@.TK2MSFTNGP04.phx.gbl...
>>> Hi,
>>>
>>> We found the problem. If you run a maintenance plan that has a
>>> History Cleanup Task and its run by an admin level account then the
>>> sp_purge_jobhistory does not include a @.job_name parameter. This
>>> combination means that all job history gets cleaned up rather than
>>> just the job that has the History Cleanup Task problem raised on
>>> Connect as bug 322320.
>>>
>>> Chris
>>>
>>> "Chris Wood" <anonymous@.microsoft.com> wrote in message
>>> news:O9nm5EQPIHA.4272@.TK2MSFTNGP06.phx.gbl...
>>> Hi,
>>>
>>> Under SQL Agent properties/History there are 2 limiting columns
>>> for Limit size of job history log.
>>> We have Maximum job history set to 12000 and maximum job history
>>> rows per job at 150 but we only seem to be able to keep 2 or 3
>>> days worth of jobhistory even though looking in sysjobhistory I
>>> can only return 7300 rows.
>>>
>>> I thought that it would keep a maximum of 150 per job to a total
>>> of 12000.
>>>
>>> Is something else limiting the data in sysjobhistory?
>>>
>>> Thanks
>>>
>>> Chris
>>>
>>>
>>>
>>>
>>
>>
>>
>>
>

Wednesday, March 7, 2012

Limitations of SQL Server 2000 Personal Edition

Can someone tell me what the record limit is or db size is for PE of SQL
Server 2000. I'm trying to import 33.6 million records, and I keep getting
an error message that says the data contains an extra column at 454K records
.
I've tried the HELP, but I only see the Terrabyte limits. Is PE less than
than. The db is set to automatically grow.
TIA
MarkYour error message isn't related to capacity. The system is telling you
that there is an extra column in a record. SQL requires a fixed number of
columns inan import source file. Most likely you are using a comma or tab
delimited file. Inside that file there is a character string that has an
extra delimiter character so SQL interprets that as an extra column. This
is fairly common on a data import from a non-scrubbed source.
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP
"MChrist" <MChrist@.discussions.microsoft.com> wrote in message
news:41821F63-5257-411B-9C8F-19E0CC3E6FF2@.microsoft.com...
> Can someone tell me what the record limit is or db size is for PE of SQL
> Server 2000. I'm trying to import 33.6 million records, and I keep
> getting
> an error message that says the data contains an extra column at 454K
> records.
> I've tried the HELP, but I only see the Terrabyte limits. Is PE less than
> than. The db is set to automatically grow.
> TIA
> Mark|||Hello Geoff,
Thanks for taking the time to answer my post. I realized the message it
probably correct, although I suspect it's an error because I loaded similar
files to the server at my office, and now when I'm trying to load them on my
PC at home, I'm running into this problem.
It could be a problem in the tab delimited file as you say, but I've tried 2
of the 3 text files, and I get the same error message at record 454,157.
Since these text files aren't the exact same files as I loaded at work, it's
possible that there is a cliche in my PC creating slightly different version
s
of the files than my work PC, but not likely to generate the error at the
same point within the file.
Do you know though if there are limitations on the size of the db on the PE
version?
TIA
Mark
"Geoff N. Hiten" wrote:

> Your error message isn't related to capacity. The system is telling you
> that there is an extra column in a record. SQL requires a fixed number of
> columns inan import source file. Most likely you are using a comma or tab
> delimited file. Inside that file there is a character string that has an
> extra delimiter character so SQL interprets that as an extra column. This
> is fairly common on a data import from a non-scrubbed source.
> --
> Geoff N. Hiten
> Senior Database Administrator
> Microsoft SQL Server MVP
> "MChrist" <MChrist@.discussions.microsoft.com> wrote in message
> news:41821F63-5257-411B-9C8F-19E0CC3E6FF2@.microsoft.com...
>
>|||There are memory and processor usage limitations, but no database size
limitations in PE. Lok up "Maximum Capacity Limitations" in BOL. Also,
pre-expand your SQL Data files to hold the entire import. Auto-grow can
sometimes cause timeout issues.
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP
"MChrist" <MChrist@.discussions.microsoft.com> wrote in message
news:6FA717DB-6D9D-4480-ADE8-4B64C9DB5DC6@.microsoft.com...[vbcol=seagreen]
> Hello Geoff,
> Thanks for taking the time to answer my post. I realized the message it
> probably correct, although I suspect it's an error because I loaded
> similar
> files to the server at my office, and now when I'm trying to load them on
> my
> PC at home, I'm running into this problem.
> It could be a problem in the tab delimited file as you say, but I've tried
> 2
> of the 3 text files, and I get the same error message at record 454,157.
> Since these text files aren't the exact same files as I loaded at work,
> it's
> possible that there is a cliche in my PC creating slightly different
> versions
> of the files than my work PC, but not likely to generate the error at the
> same point within the file.
> Do you know though if there are limitations on the size of the db on the
> PE
> version?
> TIA
> Mark
> "Geoff N. Hiten" wrote:
>

Limitations of SQL Server 2000 Personal Edition

Can someone tell me what the record limit is or db size is for PE of SQL
Server 2000. I'm trying to import 33.6 million records, and I keep getting
an error message that says the data contains an extra column at 454K records.
I've tried the HELP, but I only see the Terrabyte limits. Is PE less than
than. The db is set to automatically grow.
TIA
Mark
Your error message isn't related to capacity. The system is telling you
that there is an extra column in a record. SQL requires a fixed number of
columns inan import source file. Most likely you are using a comma or tab
delimited file. Inside that file there is a character string that has an
extra delimiter character so SQL interprets that as an extra column. This
is fairly common on a data import from a non-scrubbed source.
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP
"MChrist" <MChrist@.discussions.microsoft.com> wrote in message
news:41821F63-5257-411B-9C8F-19E0CC3E6FF2@.microsoft.com...
> Can someone tell me what the record limit is or db size is for PE of SQL
> Server 2000. I'm trying to import 33.6 million records, and I keep
> getting
> an error message that says the data contains an extra column at 454K
> records.
> I've tried the HELP, but I only see the Terrabyte limits. Is PE less than
> than. The db is set to automatically grow.
> TIA
> Mark
|||Hello Geoff,
Thanks for taking the time to answer my post. I realized the message it
probably correct, although I suspect it's an error because I loaded similar
files to the server at my office, and now when I'm trying to load them on my
PC at home, I'm running into this problem.
It could be a problem in the tab delimited file as you say, but I've tried 2
of the 3 text files, and I get the same error message at record 454,157.
Since these text files aren't the exact same files as I loaded at work, it's
possible that there is a cliche in my PC creating slightly different versions
of the files than my work PC, but not likely to generate the error at the
same point within the file.
Do you know though if there are limitations on the size of the db on the PE
version?
TIA
Mark
"Geoff N. Hiten" wrote:

> Your error message isn't related to capacity. The system is telling you
> that there is an extra column in a record. SQL requires a fixed number of
> columns inan import source file. Most likely you are using a comma or tab
> delimited file. Inside that file there is a character string that has an
> extra delimiter character so SQL interprets that as an extra column. This
> is fairly common on a data import from a non-scrubbed source.
> --
> Geoff N. Hiten
> Senior Database Administrator
> Microsoft SQL Server MVP
> "MChrist" <MChrist@.discussions.microsoft.com> wrote in message
> news:41821F63-5257-411B-9C8F-19E0CC3E6FF2@.microsoft.com...
>
>
|||There are memory and processor usage limitations, but no database size
limitations in PE. Lok up "Maximum Capacity Limitations" in BOL. Also,
pre-expand your SQL Data files to hold the entire import. Auto-grow can
sometimes cause timeout issues.
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP
"MChrist" <MChrist@.discussions.microsoft.com> wrote in message
news:6FA717DB-6D9D-4480-ADE8-4B64C9DB5DC6@.microsoft.com...[vbcol=seagreen]
> Hello Geoff,
> Thanks for taking the time to answer my post. I realized the message it
> probably correct, although I suspect it's an error because I loaded
> similar
> files to the server at my office, and now when I'm trying to load them on
> my
> PC at home, I'm running into this problem.
> It could be a problem in the tab delimited file as you say, but I've tried
> 2
> of the 3 text files, and I get the same error message at record 454,157.
> Since these text files aren't the exact same files as I loaded at work,
> it's
> possible that there is a cliche in my PC creating slightly different
> versions
> of the files than my work PC, but not likely to generate the error at the
> same point within the file.
> Do you know though if there are limitations on the size of the db on the
> PE
> version?
> TIA
> Mark
> "Geoff N. Hiten" wrote:

Limitations of SQL Server 2000 Personal Edition

Can someone tell me what the record limit is or db size is for PE of SQL
Server 2000. I'm trying to import 33.6 million records, and I keep getting
an error message that says the data contains an extra column at 454K records.
I've tried the HELP, but I only see the Terrabyte limits. Is PE less than
than. The db is set to automatically grow.
TIA
MarkYour error message isn't related to capacity. The system is telling you
that there is an extra column in a record. SQL requires a fixed number of
columns inan import source file. Most likely you are using a comma or tab
delimited file. Inside that file there is a character string that has an
extra delimiter character so SQL interprets that as an extra column. This
is fairly common on a data import from a non-scrubbed source.
--
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP
"MChrist" <MChrist@.discussions.microsoft.com> wrote in message
news:41821F63-5257-411B-9C8F-19E0CC3E6FF2@.microsoft.com...
> Can someone tell me what the record limit is or db size is for PE of SQL
> Server 2000. I'm trying to import 33.6 million records, and I keep
> getting
> an error message that says the data contains an extra column at 454K
> records.
> I've tried the HELP, but I only see the Terrabyte limits. Is PE less than
> than. The db is set to automatically grow.
> TIA
> Mark|||Hello Geoff,
Thanks for taking the time to answer my post. I realized the message it
probably correct, although I suspect it's an error because I loaded similar
files to the server at my office, and now when I'm trying to load them on my
PC at home, I'm running into this problem.
It could be a problem in the tab delimited file as you say, but I've tried 2
of the 3 text files, and I get the same error message at record 454,157.
Since these text files aren't the exact same files as I loaded at work, it's
possible that there is a cliche in my PC creating slightly different versions
of the files than my work PC, but not likely to generate the error at the
same point within the file.
Do you know though if there are limitations on the size of the db on the PE
version?
TIA
Mark
"Geoff N. Hiten" wrote:
> Your error message isn't related to capacity. The system is telling you
> that there is an extra column in a record. SQL requires a fixed number of
> columns inan import source file. Most likely you are using a comma or tab
> delimited file. Inside that file there is a character string that has an
> extra delimiter character so SQL interprets that as an extra column. This
> is fairly common on a data import from a non-scrubbed source.
> --
> Geoff N. Hiten
> Senior Database Administrator
> Microsoft SQL Server MVP
> "MChrist" <MChrist@.discussions.microsoft.com> wrote in message
> news:41821F63-5257-411B-9C8F-19E0CC3E6FF2@.microsoft.com...
> > Can someone tell me what the record limit is or db size is for PE of SQL
> > Server 2000. I'm trying to import 33.6 million records, and I keep
> > getting
> > an error message that says the data contains an extra column at 454K
> > records.
> >
> > I've tried the HELP, but I only see the Terrabyte limits. Is PE less than
> > than. The db is set to automatically grow.
> >
> > TIA
> >
> > Mark
>
>|||There are memory and processor usage limitations, but no database size
limitations in PE. Lok up "Maximum Capacity Limitations" in BOL. Also,
pre-expand your SQL Data files to hold the entire import. Auto-grow can
sometimes cause timeout issues.
--
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP
"MChrist" <MChrist@.discussions.microsoft.com> wrote in message
news:6FA717DB-6D9D-4480-ADE8-4B64C9DB5DC6@.microsoft.com...
> Hello Geoff,
> Thanks for taking the time to answer my post. I realized the message it
> probably correct, although I suspect it's an error because I loaded
> similar
> files to the server at my office, and now when I'm trying to load them on
> my
> PC at home, I'm running into this problem.
> It could be a problem in the tab delimited file as you say, but I've tried
> 2
> of the 3 text files, and I get the same error message at record 454,157.
> Since these text files aren't the exact same files as I loaded at work,
> it's
> possible that there is a cliche in my PC creating slightly different
> versions
> of the files than my work PC, but not likely to generate the error at the
> same point within the file.
> Do you know though if there are limitations on the size of the db on the
> PE
> version?
> TIA
> Mark
> "Geoff N. Hiten" wrote:
>> Your error message isn't related to capacity. The system is telling you
>> that there is an extra column in a record. SQL requires a fixed number
>> of
>> columns inan import source file. Most likely you are using a comma or
>> tab
>> delimited file. Inside that file there is a character string that has an
>> extra delimiter character so SQL interprets that as an extra column.
>> This
>> is fairly common on a data import from a non-scrubbed source.
>> --
>> Geoff N. Hiten
>> Senior Database Administrator
>> Microsoft SQL Server MVP
>> "MChrist" <MChrist@.discussions.microsoft.com> wrote in message
>> news:41821F63-5257-411B-9C8F-19E0CC3E6FF2@.microsoft.com...
>> > Can someone tell me what the record limit is or db size is for PE of
>> > SQL
>> > Server 2000. I'm trying to import 33.6 million records, and I keep
>> > getting
>> > an error message that says the data contains an extra column at 454K
>> > records.
>> >
>> > I've tried the HELP, but I only see the Terrabyte limits. Is PE less
>> > than
>> > than. The db is set to automatically grow.
>> >
>> > TIA
>> >
>> > Mark
>>

limitations of express edition

What are the limitations of sql server express edition? For example oracle released Oracle express and its limitation is of maximum user data size of 4 gb.

Have a look at the following document, which compares the SQL Server editions:

SQL Server 2005 Features Comparison|||Thanks a lot. This is exactly what I was looking for.|||SQL Server 2005 Features Comparison - this chart is not so detailed as I need. In comparison table for SQL 2000 was item about http/https connectivity (supported only by SQL 2000 Enterprise Edition) but in the comparison table for SQL 2005 is missing. Could you help me - I'd like to now if I need SQL 2005 Enterprise Edition for http/https connection to Analysis Services or it is already possible with Standard Edition.
Thanks

limitations of express edition

What are the limitations of sql server express edition? For example oracle released Oracle express and its limitation is of maximum user data size of 4 gb.

Have a look at the following document, which compares the SQL Server editions:

Limitations of access

What are th limitations of access versus MSDE? Like file size, maximum
database etc. Or they the same.
What is the maximum file size for MSDE?
Help!Hi
http://msdn.microsoft.com/library/d...br />
8dbn.asp
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/
"docsql" <docsql@.noemail.nospam> wrote in message
news:uS6E$3FxFHA.916@.TK2MSFTNGP10.phx.gbl...
> What are th limitations of access versus MSDE? Like file size, maximum
> database etc. Or they the same.
> What is the maximum file size for MSDE?
> Help!
>|||did you hear "docsql" <docsql@.noemail.nospam> say in news:uS6E$3FxFHA.916
@.TK2MSFTNGP10.phx.gbl:

> What are th limitations of access versus MSDE? Like file size, maximum
> database etc. Or they the same.
> What is the maximum file size for MSDE?
> Help!
>
http://www.microsoft.com/sql/msde/p...o/features.mspx
http://www.microsoft.com/office/acc...info/guide.mspx
Neil MacMurchy
http://spaces.msn.com/members/neilmacmurchy
http://spaces.msn.com/members/mctblogs|||Hello,
You may want to refer to the following articles:
Maximum Capacity Specifications
http://msdn.microsoft.com/library/d...-us/architec/8_
ar_ts_8dbn.asp
Specifications for an Access database and for an Access Project
http://support.microsoft.com/defaul...kb;en-us;302524
Access specifications
http://office.microsoft.com/en-us/a...1868081033.aspx
321518 When to choose MSDE 2000 as the database engine for your application
http://support.microsoft.com/?id=321518
I hope the information is helpful.
Sophie Guo
Microsoft Online Partner Support
Get Secure! - www.microsoft.com/security
========================================
=============
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
========================================
=============
This posting is provided "AS IS" with no warranties, and confers no rights.

Limitations of access

What are th limitations of access versus MSDE? Like file size, maximum
database etc. Or they the same.
What is the maximum file size for MSDE?
Help!
Hi
http://msdn.microsoft.com/library/de...ar_ts_8dbn.asp
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/
"docsql" <docsql@.noemail.nospam> wrote in message
news:uS6E$3FxFHA.916@.TK2MSFTNGP10.phx.gbl...
> What are th limitations of access versus MSDE? Like file size, maximum
> database etc. Or they the same.
> What is the maximum file size for MSDE?
> Help!
>
|||did you hear "docsql" <docsql@.noemail.nospam> say in news:uS6E$3FxFHA.916
@.TK2MSFTNGP10.phx.gbl:

> What are th limitations of access versus MSDE? Like file size, maximum
> database etc. Or they the same.
> What is the maximum file size for MSDE?
> Help!
>
http://www.microsoft.com/sql/msde/pr.../features.mspx
http://www.microsoft.com/office/acce...nfo/guide.mspx
Neil MacMurchy
http://spaces.msn.com/members/neilmacmurchy
http://spaces.msn.com/members/mctblogs
|||Hello,
You may want to refer to the following articles:
Maximum Capacity Specifications
http://msdn.microsoft.com/library/de...us/architec/8_
ar_ts_8dbn.asp
Specifications for an Access database and for an Access Project
http://support.microsoft.com/default...b;en-us;302524
Access specifications
http://office.microsoft.com/en-us/as...868081033.aspx
321518 When to choose MSDE 2000 as the database engine for your application
http://support.microsoft.com/?id=321518
I hope the information is helpful.
Sophie Guo
Microsoft Online Partner Support
Get Secure! - www.microsoft.com/security
================================================== ===
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
================================================== ===
This posting is provided "AS IS" with no warranties, and confers no rights.

Limitations of access

What are th limitations of access versus MSDE? Like file size, maximum
database etc. Or they the same.
What is the maximum file size for MSDE?
Help!Hi
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/architec/8_ar_ts_8dbn.asp
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/
"docsql" <docsql@.noemail.nospam> wrote in message
news:uS6E$3FxFHA.916@.TK2MSFTNGP10.phx.gbl...
> What are th limitations of access versus MSDE? Like file size, maximum
> database etc. Or they the same.
> What is the maximum file size for MSDE?
> Help!
>|||did you hear "docsql" <docsql@.noemail.nospam> say in news:uS6E$3FxFHA.916
@.TK2MSFTNGP10.phx.gbl:
> What are th limitations of access versus MSDE? Like file size, maximum
> database etc. Or they the same.
> What is the maximum file size for MSDE?
> Help!
>
http://www.microsoft.com/sql/msde/productinfo/features.mspx
http://www.microsoft.com/office/access/prodinfo/guide.mspx
--
Neil MacMurchy
http://spaces.msn.com/members/neilmacmurchy
http://spaces.msn.com/members/mctblogs|||Hello,
You may want to refer to the following articles:
Maximum Capacity Specifications
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/architec/8_
ar_ts_8dbn.asp
Specifications for an Access database and for an Access Project
http://support.microsoft.com/default.aspx?scid=kb;en-us;302524
Access specifications
http://office.microsoft.com/en-us/assistance/HP051868081033.aspx
321518 When to choose MSDE 2000 as the database engine for your application
http://support.microsoft.com/?id=321518
I hope the information is helpful.
Sophie Guo
Microsoft Online Partner Support
Get Secure! - www.microsoft.com/security
=====================================================When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
=====================================================This posting is provided "AS IS" with no warranties, and confers no rights.

Friday, February 24, 2012

Limitaions of MSDE

Hi,
Is the MSDE database still limited to 2GB. If yes is that the size of the .mdf file ?
Thanks,
-VJHi,
I believe that it is the mdf file (I don't know why they would car about how large you log files are!). I assume that you know that size isn't the only limitation to MSDE. Try searching the MS site, as I know they have a section devoted to MSDE with some good links to available enterprise manager work-alikes, etc... Might also want to look at the express vesrion of SQL Server 2005. You can also search these forums for info on that. BRN..|||MSDE(micorosft data engine) is limited to 2gig in capacity, ten concurrent write users and with caching 2500 or more read users not very sure. You could get the developer edition and install it as a named instance and register the MSDE so you can manage it with Enterprise manager.
MDF(microsoft data file) is not the log file it is the data file, the log file is LDF(log data file) both files are in the Data subfolder in Microsoft SQL Server folder under programs.
And there is very little need to grow 2gig log file because you can always create a job to run DBCC ShrinkFile after long operations that can grow the log file. DBCC shrinkFile returns space less what SQL Server calls Active log. Hope this helps.|||

thanks guys. I got what I needed. Chreers.

-VJ

|||Hi,
I'm sure the original poster appreceates the info you were able to pass along re: MDSE. BTW, I (and I'm guessing the OP), know that the .ldf is the (a), log file for SQL Server/MSDE. My comment about MS not caring about how large a log file you could have implied that the size restriction must be on the .mdf (data), file. It's been a while since I set up my MSDE installation, but I recall that I had a tough time getting the mdf and ldf assigned to different drives. The MSDE didn't make that simple. I'm sure you know how much more effecient it is when the two file types are on seperate drives - even if both are on a IDE controller.
BTW, can you have multiple named instances of the server with the developer edition of SQL Server 2000, on the same licence? That would help in some testing I was planning. Thanks, BRN..|||Hi Brian,
I am sorry did not intend to offend you and I think you can install multiple licenses but not multiple instances because the developer edition is only $37.00 on the web. Try the thread below for more info. Hope this helps.
http://forums.asp.net/1076159/ShowPost.aspx|||

Hi,

Thanks for the link; I'll check it out. BRN..

Monday, February 20, 2012

Limit table size

Is there a way to limit a tables size in a database?

Davids Learning

hi,

table size, or database size, is physicaly limited by storage systems.. but SQLExpress has another software upper limit, and a database can not exceed 4gb in the data files sum...

but you can not provide a "limit" for a table... you can perhaps define some constraint to limit the row number stored by the table in a particular range, but the actual "size" of the table will depend on the table design, on the index(es) implementation and the like..

what are you trying to do?

regards

|||

I am wanting to limit how many entries are in this log, i would like to keep the last 200 entries from current to back

Cant that be done?

Davids Learning

|||

you can define a stored procedure, and perform deletetion if there are more rows then you like to be stored, or, a trigger FOR INSERT.. both methods as

SET NOCOUNT ON;

USE tempdb;

CREATE TABLE dbo.TestLog (

Id int NOT NULL IDENTITY PRIMARY KEY,

Data varchar(10) NOT NULL

);

CREATE PROCEDURE dbo.Add2Log (

@.Data varchar(10)

)

AS BEGIN

INSERT INTO dbo.TestLog VALUES (@.Data);
DECLARE @.r int;

SELECT @.r = COUNT(*) FROM dbo.TestLog;

IF @.r > 5 BEGIN

PRINT 'Deleting older data';

DELETE FROM dbo.TestLog

WHERE Id NOT IN (SELECT TOP 5 Id FROM dbo.TestLog ORDER BY Id DESC);

END;

PRINT 'controlling via stored procedure';

DECLARE @.i int, @.Data varchar(10);

SET @.i = 1;

WHILE @.i < 21 BEGIN

SET @.Data = CONVERT(varchar, @.i);

EXEC dbo.Add2Log @.Data = @.Data;

SET @.i = @.i + 1;

END;

SELECT * FROM dbo.TestLog;

DROP PROCEDURE dbo.Add2Log

CREATE TRIGGER tr_I_TestLog ON dbo.TestLog

FOR INSERT

AS BEGIN

IF @.@.ROWCOUNT = 0 RETURN;

DECLARE @.r int;

SELECT @.r = COUNT(*) FROM dbo.TestLog;

IF @.r > 5 BEGIN

PRINT 'Deleting older data';

DELETE FROM dbo.TestLog

WHERE Id NOT IN (SELECT TOP 5 Id FROM dbo.TestLog ORDER BY Id DESC);

END;

PRINT 'controlling via INSERT trigger';

DECLARE @.i int;

SET @.i = 100;

WHILE @.i < 121 BEGIN

INSERT INTO dbo.TestLog VALUES (CONVERT(varchar, @.i));

SET @.i = @.i + 1;

END;

SELECT * FROM dbo.TestLog;

DROP TABLE dbo.TestLog;

regards

limit size of image column

I'm trying to limit the size of images that can be inserted into an Image
type column. The client side tools are not letting me do this so I thought I
could do it in a trigger or constraint in the backend. So I discovered that
I can't use Image datatypes in constraints. And also discovering that I
can't use it the way I thought I could in a trigger. HEre's what I was goign
to do:
IF UPDATE(ItemPic)
BEGIN
IF (SELECT DATALENGTH(ItemPic) AS ItemPicSize FROM INSERTED) > 100000
BEGIN
ROLLBACK TRAN
SET @.sMsg = 'Item Picture cannot exceed 100Kb.'
RAISERROR (@.sMsg, 16, 1)
RETURN
END
END
Which of course doesn't work because I get the error "Cannot use text,
ntext, or image columns in the 'inserted' and 'deleted' tables."
Is there a good way to do this? I won't be allowing very large images so
maybe a different data type would work?
Thanks,
KeithHow are they getting the image file to the database server? Presumably,
they are using a web app or a windows app (I don't know what "client side
tools" are, and why they don't let you do something), in which case surely
there is a place along the pipeline that you can validate the size BEFORE
you run the INSERT statement.
"Keith G Hicks" <krh@.comcast.net> wrote in message
news:OUJSYHTaGHA.3328@.TK2MSFTNGP02.phx.gbl...
> I'm trying to limit the size of images that can be inserted into an Image
> type column. The client side tools are not letting me do this so I thought
> I
> could do it in a trigger or constraint in the backend. So I discovered
> that
> I can't use Image datatypes in constraints. And also discovering that I
> can't use it the way I thought I could in a trigger. HEre's what I was
> goign
> to do:
> IF UPDATE(ItemPic)
> BEGIN
> IF (SELECT DATALENGTH(ItemPic) AS ItemPicSize FROM INSERTED) > 100000
> BEGIN
> ROLLBACK TRAN
> SET @.sMsg = 'Item Picture cannot exceed 100Kb.'
> RAISERROR (@.sMsg, 16, 1)
> RETURN
> END
> END
> Which of course doesn't work because I get the error "Cannot use text,
> ntext, or image columns in the 'inserted' and 'deleted' tables."
> Is there a good way to do this? I won't be allowing very large images so
> maybe a different data type would work?
> Thanks,
> Keith
>|||Read more about Image column
[url]http://www.microsoft.com/technet/prodtechnol/sql/2000/reskit/part3/c1161.mspx[/url
]
Madhivanan|||I've already spoken to the developers of the client side tools I'm using
(3rd party windows app dev tools). I'm using one of their image controls
which has no ability to test or limit the size before the fact.
The only other part of the pipeline is in ado. I'd have to do it in the
OnBeforePost event of the ado dataset but accordign to the Delphi ado help,
there's no way to determine the data size of a blob control's data. The
following is from the help file (note the line "this does not work for BLOB
fields"):
with Field1 do
begin
if not IsBlob { this does not work for BLOB fields }
begin
{ Allocate space }
GetMem(MyBuffer, DataSize);
try
if not GetData(MyBuffer) then
MessageDlg(DisplayName + ' is NULL', mtInformation, [mbOK], 0)
else
{ Do something with the data };
finally
{ Free the space }
FreeMem(MyBuffer, DataSize);
end;
end;
end;
Which is why I came here as a last resort because I agree, the situation
should be handled before it ever gets to the db.
Keith
"Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:OcB$30TaGHA.1200@.TK2MSFTNGP03.phx.gbl...
How are they getting the image file to the database server? Presumably,
they are using a web app or a windows app (I don't know what "client side
tools" are, and why they don't let you do something), in which case surely
there is a place along the pipeline that you can validate the size BEFORE
you run the INSERT statement.|||Try using an INSTEAD OF tigger. In pseudo code:
if no images larger than 10000 exist
insert rows
else
rollback
raiserror
ML
http://milambda.blogspot.com/|||That was actually the very first sql thing I tried. But I have foreign keys
with cascade updates/deletes between this and other tables that are not
allowing me to hae an INSTEAD OF UPDATE, INSERT trigger on this table.
"ML" <ML@.discussions.microsoft.com> wrote in message
news:2FFA09F9-2D72-4AE9-8FE2-BC1FE6D42A76@.microsoft.com...
Try using an INSTEAD OF tigger. In pseudo code:
if no images larger than 10000 exist
insert rows
else
rollback
raiserror
ML
http://milambda.blogspot.com/|||Well, I think the answer for me at this point is to call a stored procedure
from the trigger and test teh size that way. That seems to be working sort
of. There are a couple of glitches but those are in a new post. I'm all set
in this one. Thanks for the answers folks. - keith|||Another approach may be to get the ADO call from Delphi to call a stored
procedure instead. Then insert into that stored procedure the code to
validate the size of the file.
If that's possible, then surely it would be possible to check the file size
before even making the call to a stored procedure. You know, what's the
point sending a 10Mb Graphic to the SQL Server cloging up the network, only
to have SQL say know and through the whole lot away.
Regards
Colin Dawson
www.cjdawson.com
"Keith G Hicks" <krh@.comcast.net> wrote in message
news:es9MQtUaGHA.428@.TK2MSFTNGP02.phx.gbl...
> Well, I think the answer for me at this point is to call a stored
> procedure
> from the trigger and test teh size that way. That seems to be working sort
> of. There are a couple of glitches but those are in a new post. I'm all
> set
> in this one. Thanks for the answers folks. - keith
>

limit on SQL CE upgrade.exe?

Is there a size limit on the upgrade.exe to convert the SQL 2.0 SDF file to the SQL Mobile 2005/SqL CE 3.0?

I have a 184M SDF that I tried putting through the upgrade but i got an error that it exceed a 128M limit? Is there a way around it? maybe another parameter I need to pass in?

Thanks

Dorothy

This has been identified as Bug in upgrade.exe. MS is currently working on a HOTFIX to fix this issue.

You might want to call MS and get HOTFIX which should fix upgrade.exe issue for upgrade of SDF file > 128 MB.

Regards,

Akshay

|||

The hotfix is now available (by request) via this kb article: http://support.microsoft.com/default.aspx?scid=kb;en-us;935769&sd=rss&spid=2852

FIX: Error message when you upgrade a database from SQL Server 2000 Windows CE Edition 2.0 to SQL Server 2005 Mobile Edition: "The database file is larger than the configured maximum database size"

limit on SQL CE upgrade.exe?

Is there a size limit on the upgrade.exe to convert the SQL 2.0 SDF file to the SQL Mobile 2005/SqL CE 3.0?

I have a 184M SDF that I tried putting through the upgrade but i got an error that it exceed a 128M limit? Is there a way around it? maybe another parameter I need to pass in?

Thanks

Dorothy

This has been identified as Bug in upgrade.exe. MS is currently working on a HOTFIX to fix this issue.

You might want to call MS and get HOTFIX which should fix upgrade.exe issue for upgrade of SDF file > 128 MB.

Regards,

Akshay

|||

The hotfix is now available (by request) via this kb article: http://support.microsoft.com/default.aspx?scid=kb;en-us;935769&sd=rss&spid=2852

FIX: Error message when you upgrade a database from SQL Server 2000 Windows CE Edition 2.0 to SQL Server 2005 Mobile Edition: "The database file is larger than the configured maximum database size"

Monday, March 12, 2012

Limits

Limits

Limiting transaction Log file size

Limiting transaction Log file size

Limiting size of a table in SQL server

Friday, March 9, 2012

Limiting job history in SQL2005

Wednesday, March 7, 2012

Limitations of SQL Server 2000 Personal Edition

Limitations of SQL Server 2000 Personal Edition

Limitations of SQL Server 2000 Personal Edition

limitations of express edition

limitations of express edition

Limitations of access

Limitations of access

Limitations of access

Friday, February 24, 2012

Limitaions of MSDE

Monday, February 20, 2012

Limit table size

limit size of image column

limit on SQL CE upgrade.exe?

FIX: Error message when you upgrade a database from SQL Server 2000 Windows CE Edition 2.0 to SQL Server 2005 Mobile Edition: "The database file is larger than the configured maximum database size"

limit on SQL CE upgrade.exe?

FIX: Error message when you upgrade a database from SQL Server 2000 Windows CE Edition 2.0 to SQL Server 2005 Mobile Edition: "The database file is larger than the configured maximum database size"

LIMIT problems

Blog Archive

About Me