'Primary filegroup is full

I have an application that receives the error that the primary filegroup is
full a couple of times at day. I can bump up the database size but I would
think having it set to grow at 10 percent I shouldn't have to worry about
this. I just looked and there is 3 percent of available space. How do I
make sure there is always available space?
ThanksNever set the DB to a % for autogrow since the size changes with each
growth. Determine what size in MB can be grown in about 10 seconds or less
and use that amount instead. But just make the data files large enough to
hold what you expect for a long time out and you should never need to use
autogrow. Do not shrink the database or turn on autoshrink.
--
Andrew J. Kelly SQL MVP
Solid Quality Mentors
"Don" <Don@.discussions.microsoft.com> wrote in message
news:52A45F80-F8C3-4766-9F3C-D1BE2D3E9066@.microsoft.com...
>I have an application that receives the error that the primary filegroup is
> full a couple of times at day. I can bump up the database size but I
> would
> think having it set to grow at 10 percent I shouldn't have to worry about
> this. I just looked and there is 3 percent of available space. How do I
> make sure there is always available space?
> Thanks
>|||Be sure you checked "Enable Autogrowth" of your database in the Primary File
Group and then be sure that Maximum File Size is "Unrestricted File Growth"
if you do not wanna bother yourself with this issue again.
You can do these settings from the properties of your database.
And do neglect setting values of File Growth and Miximum File Size according
to your system needs. These settings affect SQL Server's performance
(especially for huge ones).
--
Ekrem Ã?nsoy
"Don" <Don@.discussions.microsoft.com> wrote in message
news:52A45F80-F8C3-4766-9F3C-D1BE2D3E9066@.microsoft.com...
>I have an application that receives the error that the primary filegroup is
> full a couple of times at day. I can bump up the database size but I
> would
> think having it set to grow at 10 percent I shouldn't have to worry about
> this. I just looked and there is 3 percent of available space. How do I
> make sure there is always available space?
> Thanks
>|||Don,
Pay attention to all of what Andrew is saying, it is important. Nothing, and
I mean nothing replaces knowing your enviornment and what your database
resource needs are.
If you allow growth (or shrinkage) you are fragmenting the partition/drive
your database resides on and will pay for it in performance.
Jay
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:OlR4Lii8HHA.4420@.TK2MSFTNGP02.phx.gbl...
> Never set the DB to a % for autogrow since the size changes with each
> growth. Determine what size in MB can be grown in about 10 seconds or less
> and use that amount instead. But just make the data files large enough to
> hold what you expect for a long time out and you should never need to use
> autogrow. Do not shrink the database or turn on autoshrink.
> --
> Andrew J. Kelly SQL MVP
> Solid Quality Mentors
>
> "Don" <Don@.discussions.microsoft.com> wrote in message
> news:52A45F80-F8C3-4766-9F3C-D1BE2D3E9066@.microsoft.com...
>>I have an application that receives the error that the primary filegroup
>>is
>> full a couple of times at day. I can bump up the database size but I
>> would
>> think having it set to grow at 10 percent I shouldn't have to worry about
>> this. I just looked and there is 3 percent of available space. How do I
>> make sure there is always available space?
>> Thanks
>|||Thanks for the help, so I went into Enterprise Manager and bumped up the size
for 4 GB to 5GB but get the error Error 5041 Modify File Failed File. It
does not exiist/
when trying to save the new setting
"Jay" wrote:
> Don,
> Pay attention to all of what Andrew is saying, it is important. Nothing, and
> I mean nothing replaces knowing your enviornment and what your database
> resource needs are.
> If you allow growth (or shrinkage) you are fragmenting the partition/drive
> your database resides on and will pay for it in performance.
> Jay
> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> news:OlR4Lii8HHA.4420@.TK2MSFTNGP02.phx.gbl...
> > Never set the DB to a % for autogrow since the size changes with each
> > growth. Determine what size in MB can be grown in about 10 seconds or less
> > and use that amount instead. But just make the data files large enough to
> > hold what you expect for a long time out and you should never need to use
> > autogrow. Do not shrink the database or turn on autoshrink.
> >
> > --
> > Andrew J. Kelly SQL MVP
> > Solid Quality Mentors
> >
> >
> > "Don" <Don@.discussions.microsoft.com> wrote in message
> > news:52A45F80-F8C3-4766-9F3C-D1BE2D3E9066@.microsoft.com...
> >>I have an application that receives the error that the primary filegroup
> >>is
> >> full a couple of times at day. I can bump up the database size but I
> >> would
> >> think having it set to grow at 10 percent I shouldn't have to worry about
> >> this. I just looked and there is 3 percent of available space. How do I
> >> make sure there is always available space?
> >>
> >> Thanks
> >>
> >
>
>|||Did you change the name of the file by mistake in the dialog? Have you
tried it again? If that doesn't work you might want to try and use ALTER
DATABASE instead.
--
Andrew J. Kelly SQL MVP
Solid Quality Mentors
"Don" <Don@.discussions.microsoft.com> wrote in message
news:77A8F234-BD7D-4683-AB2E-FF0A8F9315B3@.microsoft.com...
> Thanks for the help, so I went into Enterprise Manager and bumped up the
> size
> for 4 GB to 5GB but get the error Error 5041 Modify File Failed File. It
> does not exiist/
> when trying to save the new setting
>
> "Jay" wrote:
>> Don,
>> Pay attention to all of what Andrew is saying, it is important. Nothing,
>> and
>> I mean nothing replaces knowing your enviornment and what your database
>> resource needs are.
>> If you allow growth (or shrinkage) you are fragmenting the
>> partition/drive
>> your database resides on and will pay for it in performance.
>> Jay
>> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
>> news:OlR4Lii8HHA.4420@.TK2MSFTNGP02.phx.gbl...
>> > Never set the DB to a % for autogrow since the size changes with each
>> > growth. Determine what size in MB can be grown in about 10 seconds or
>> > less
>> > and use that amount instead. But just make the data files large enough
>> > to
>> > hold what you expect for a long time out and you should never need to
>> > use
>> > autogrow. Do not shrink the database or turn on autoshrink.
>> >
>> > --
>> > Andrew J. Kelly SQL MVP
>> > Solid Quality Mentors
>> >
>> >
>> > "Don" <Don@.discussions.microsoft.com> wrote in message
>> > news:52A45F80-F8C3-4766-9F3C-D1BE2D3E9066@.microsoft.com...
>> >>I have an application that receives the error that the primary
>> >>filegroup
>> >>is
>> >> full a couple of times at day. I can bump up the database size but I
>> >> would
>> >> think having it set to grow at 10 percent I shouldn't have to worry
>> >> about
>> >> this. I just looked and there is 3 percent of available space. How
>> >> do I
>> >> make sure there is always available space?
>> >>
>> >> Thanks
>> >>
>> >
>>|||Thanks for the help, I did figure out what the issue was. The drive was set
as Fat32 (4 GB limit) so I had to change how the 300 GB drive was set.
Thanks
"Andrew J. Kelly" wrote:
> Did you change the name of the file by mistake in the dialog? Have you
> tried it again? If that doesn't work you might want to try and use ALTER
> DATABASE instead.
> --
> Andrew J. Kelly SQL MVP
> Solid Quality Mentors
>
> "Don" <Don@.discussions.microsoft.com> wrote in message
> news:77A8F234-BD7D-4683-AB2E-FF0A8F9315B3@.microsoft.com...
> > Thanks for the help, so I went into Enterprise Manager and bumped up the
> > size
> > for 4 GB to 5GB but get the error Error 5041 Modify File Failed File. It
> > does not exiist/
> > when trying to save the new setting
> >
> >
> > "Jay" wrote:
> >
> >> Don,
> >>
> >> Pay attention to all of what Andrew is saying, it is important. Nothing,
> >> and
> >> I mean nothing replaces knowing your enviornment and what your database
> >> resource needs are.
> >>
> >> If you allow growth (or shrinkage) you are fragmenting the
> >> partition/drive
> >> your database resides on and will pay for it in performance.
> >>
> >> Jay
> >>
> >> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> >> news:OlR4Lii8HHA.4420@.TK2MSFTNGP02.phx.gbl...
> >> > Never set the DB to a % for autogrow since the size changes with each
> >> > growth. Determine what size in MB can be grown in about 10 seconds or
> >> > less
> >> > and use that amount instead. But just make the data files large enough
> >> > to
> >> > hold what you expect for a long time out and you should never need to
> >> > use
> >> > autogrow. Do not shrink the database or turn on autoshrink.
> >> >
> >> > --
> >> > Andrew J. Kelly SQL MVP
> >> > Solid Quality Mentors
> >> >
> >> >
> >> > "Don" <Don@.discussions.microsoft.com> wrote in message
> >> > news:52A45F80-F8C3-4766-9F3C-D1BE2D3E9066@.microsoft.com...
> >> >>I have an application that receives the error that the primary
> >> >>filegroup
> >> >>is
> >> >> full a couple of times at day. I can bump up the database size but I
> >> >> would
> >> >> think having it set to grow at 10 percent I shouldn't have to worry
> >> >> about
> >> >> this. I just looked and there is 3 percent of available space. How
> >> >> do I
> >> >> make sure there is always available space?
> >> >>
> >> >> Thanks
> >> >>
> >> >
> >>
> >>
> >>
>

Preventing deadlocks

I've noticed a couple of very nasty deadlocks on our new server lately, and
I'd like some advice on how to "ignore" them. What's happening is that
someone's machine and/or Access dies into the VB debugger in the middle of a
SELECT, and the process locks. This pretty much stops all access to the
server, and ends backups as well!
I don't see any way to prevent these problems on the user machine, so how to
I ignore them on the server? Is there some timeout I've forgot to set when we
set up the machine?
Also, when I attempt to kill the process in Manager it seems to either not
work, or take a very long time. Is this normal?
If it takes a long time to kill the process, it's probably doing a rollback.
"Maury Markowitz" wrote:

> I've noticed a couple of very nasty deadlocks on our new server lately, and
> I'd like some advice on how to "ignore" them. What's happening is that
> someone's machine and/or Access dies into the VB debugger in the middle of a
> SELECT, and the process locks. This pretty much stops all access to the
> server, and ends backups as well!
> I don't see any way to prevent these problems on the user machine, so how to
> I ignore them on the server? Is there some timeout I've forgot to set when we
> set up the machine?
> Also, when I attempt to kill the process in Manager it seems to either not
> work, or take a very long time. Is this normal?
|||Maury Markowitz wrote:
> I've noticed a couple of very nasty deadlocks on our new server
> lately, and I'd like some advice on how to "ignore" them. What's
> happening is that someone's machine and/or Access dies into the VB
> debugger in the middle of a SELECT, and the process locks. This
> pretty much stops all access to the server, and ends backups as well!
> I don't see any way to prevent these problems on the user machine, so
> how to I ignore them on the server? Is there some timeout I've forgot
> to set when we set up the machine?
> Also, when I attempt to kill the process in Manager it seems to
> either not work, or take a very long time. Is this normal?
A deadlock does not kill a connection. It only raises an error. The
server doesn't really do anything other than select the deadlock victim
and raise the error. The rest is up to the application. If you have an
application that is encountering a deadlock and locking up, then the
connection is likely remaining connected to the server. However, the
transaction is automatically rolled back by SQL Server, so it's not
clear what is causing your issue.
Are you sure you are having deadlock problems and not blocking issues?
I'm starting to think from your post that you may have confused the
terms. A deadlock is when two or more spids are requesting access to
objects that are locked by the other spids. In effect, they would all
block each other forever. So, SQL Server terminates one of the
transactions (the deadlock victim). In a blocking situation, one spid
requests a lock on a resource that is locked by another spid. Unless you
specify a lock timeout in your code, the first spid will wait forever
for the resource.
So which situation is yours?
David Gugick
Imceda Software
www.imceda.com
|||"David Gugick" wrote:
> A deadlock does not kill a connection. It only raises an error.
That's the problem. The deadlock is occuring because one application died
out on the network and is holding its query open. Then when someone else
issues the same query, it gets stuck. At least I think that's what's
happening.

> connection is likely remaining connected to the server. However, the
> transaction is automatically rolled back by SQL Server, so it's not
> clear what is causing your issue.
Hmmm.

> Are you sure you are having deadlock problems and not blocking issues?
You're right, I'm having blocking issues.

> specify a lock timeout in your code, the first spid will wait forever
> for the resource.
This sounds like the solution. Is this something that can be set globally in
Access? We set up the system to have 60 second timeouts, but this error
appears to occur when the Access app in question has "crashed" into the VB
Debugger.
Maury
|||Maury Markowitz wrote:
> "David Gugick" wrote:
> That's the problem. The deadlock is occuring because one application
> died out on the network and is holding its query open. Then when
> someone else issues the same query, it gets stuck. At least I think
> that's what's happening.
>
That's a blocking issue, not a deadlocking issue as you now know. SQL
Server will eventually release those locks, but it can take some time
until it clears up the connection. Why is the application dying in the
first place?
There is a lock timeout and query timeout that you should be able to set
from your Access code. The lock timeout determines how long a process
will wait on a locked resource before the query is automatically
cancelled. I believe you'll still need to issue a rollback if that
happens.
I don't know enough about Access to help more than that.
David Gugick
Imceda Software
www.imceda.com
|||"David Gugick" wrote:
> That's a blocking issue, not a deadlocking issue as you now know. SQL
> Server will eventually release those locks, but it can take some time
> until it clears up the connection. Why is the application dying in the
> first place?
For different reasons each time, typically due to edge cases that

> There is a lock timeout and query timeout that you should be able to set
> from your Access code.
Ok, I'm going to poke about and see if I can find this. The locks in
question are lasting overnight though, so I don't think it's quite that
simple.
Is there some setting on the server side I can set for this? None of our
queries take more than 30 seconds (my self-imposed limit) so setting this to
5 minutes or something would be entirely reasonable.
|||Maury Markowitz wrote:
> "David Gugick" wrote:
> For different reasons each time, typically due to edge cases that
>
> Ok, I'm going to poke about and see if I can find this. The locks in
> question are lasting overnight though, so I don't think it's quite
> that simple.
> Is there some setting on the server side I can set for this? None of
> our queries take more than 30 seconds (my self-imposed limit) so
> setting this to 5 minutes or something would be entirely reasonable.
If the locks are remaining overnight, it could be because the
application is not throwing an exception and dying, but is locked up
with its connection active on the server. Is this a possibility? I think
I meant setting the lock timeout for the clients who are being locked
out of the system, not the long running process. For that, a query
timeout could help depending on the situation. At least if you use a
lock timeout when this process seems to lock other users out, they 'll
get a nice message saying that they should call support because the
system is having issues and then you can quickly address the problem. It
doesn't prevent the problem, which is either SQL based or application
based.
David Gugick
Imceda Software
www.imceda.com

Preventing deadlocks

I've noticed a couple of very nasty deadlocks on our new server lately, and
I'd like some advice on how to "ignore" them. What's happening is that
someone's machine and/or Access dies into the VB debugger in the middle of a
SELECT, and the process locks. This pretty much stops all access to the
server, and ends backups as well!
I don't see any way to prevent these problems on the user machine, so how to
I ignore them on the server? Is there some timeout I've forgot to set when we
set up the machine?
Also, when I attempt to kill the process in Manager it seems to either not
work, or take a very long time. Is this normal?If it takes a long time to kill the process, it's probably doing a rollback.
"Maury Markowitz" wrote:
> I've noticed a couple of very nasty deadlocks on our new server lately, and
> I'd like some advice on how to "ignore" them. What's happening is that
> someone's machine and/or Access dies into the VB debugger in the middle of a
> SELECT, and the process locks. This pretty much stops all access to the
> server, and ends backups as well!
> I don't see any way to prevent these problems on the user machine, so how to
> I ignore them on the server? Is there some timeout I've forgot to set when we
> set up the machine?
> Also, when I attempt to kill the process in Manager it seems to either not
> work, or take a very long time. Is this normal?|||Maury Markowitz wrote:
> I've noticed a couple of very nasty deadlocks on our new server
> lately, and I'd like some advice on how to "ignore" them. What's
> happening is that someone's machine and/or Access dies into the VB
> debugger in the middle of a SELECT, and the process locks. This
> pretty much stops all access to the server, and ends backups as well!
> I don't see any way to prevent these problems on the user machine, so
> how to I ignore them on the server? Is there some timeout I've forgot
> to set when we set up the machine?
> Also, when I attempt to kill the process in Manager it seems to
> either not work, or take a very long time. Is this normal?
A deadlock does not kill a connection. It only raises an error. The
server doesn't really do anything other than select the deadlock victim
and raise the error. The rest is up to the application. If you have an
application that is encountering a deadlock and locking up, then the
connection is likely remaining connected to the server. However, the
transaction is automatically rolled back by SQL Server, so it's not
clear what is causing your issue.
Are you sure you are having deadlock problems and not blocking issues?
I'm starting to think from your post that you may have confused the
terms. A deadlock is when two or more spids are requesting access to
objects that are locked by the other spids. In effect, they would all
block each other forever. So, SQL Server terminates one of the
transactions (the deadlock victim). In a blocking situation, one spid
requests a lock on a resource that is locked by another spid. Unless you
specify a lock timeout in your code, the first spid will wait forever
for the resource.
So which situation is yours?
--
David Gugick
Imceda Software
www.imceda.com|||"David Gugick" wrote:
> A deadlock does not kill a connection. It only raises an error.
That's the problem. The deadlock is occuring because one application died
out on the network and is holding its query open. Then when someone else
issues the same query, it gets stuck. At least I think that's what's
happening.
> connection is likely remaining connected to the server. However, the
> transaction is automatically rolled back by SQL Server, so it's not
> clear what is causing your issue.
Hmmm.
> Are you sure you are having deadlock problems and not blocking issues?
You're right, I'm having blocking issues.
> specify a lock timeout in your code, the first spid will wait forever
> for the resource.
This sounds like the solution. Is this something that can be set globally in
Access? We set up the system to have 60 second timeouts, but this error
appears to occur when the Access app in question has "crashed" into the VB
Debugger.
Maury|||Maury Markowitz wrote:
> "David Gugick" wrote:
>> A deadlock does not kill a connection. It only raises an error.
> That's the problem. The deadlock is occuring because one application
> died out on the network and is holding its query open. Then when
> someone else issues the same query, it gets stuck. At least I think
> that's what's happening.
>
That's a blocking issue, not a deadlocking issue as you now know. SQL
Server will eventually release those locks, but it can take some time
until it clears up the connection. Why is the application dying in the
first place?
There is a lock timeout and query timeout that you should be able to set
from your Access code. The lock timeout determines how long a process
will wait on a locked resource before the query is automatically
cancelled. I believe you'll still need to issue a rollback if that
happens.
I don't know enough about Access to help more than that.
David Gugick
Imceda Software
www.imceda.com|||"David Gugick" wrote:
> That's a blocking issue, not a deadlocking issue as you now know. SQL
> Server will eventually release those locks, but it can take some time
> until it clears up the connection. Why is the application dying in the
> first place?
For different reasons each time, typically due to edge cases that
> There is a lock timeout and query timeout that you should be able to set
> from your Access code.
Ok, I'm going to poke about and see if I can find this. The locks in
question are lasting overnight though, so I don't think it's quite that
simple.
Is there some setting on the server side I can set for this? None of our
queries take more than 30 seconds (my self-imposed limit) so setting this to
5 minutes or something would be entirely reasonable.|||Maury Markowitz wrote:
> "David Gugick" wrote:
>> That's a blocking issue, not a deadlocking issue as you now know. SQL
>> Server will eventually release those locks, but it can take some
>> time until it clears up the connection. Why is the application dying
>> in the first place?
> For different reasons each time, typically due to edge cases that
>> There is a lock timeout and query timeout that you should be able to
>> set from your Access code.
> Ok, I'm going to poke about and see if I can find this. The locks in
> question are lasting overnight though, so I don't think it's quite
> that simple.
> Is there some setting on the server side I can set for this? None of
> our queries take more than 30 seconds (my self-imposed limit) so
> setting this to 5 minutes or something would be entirely reasonable.
If the locks are remaining overnight, it could be because the
application is not throwing an exception and dying, but is locked up
with its connection active on the server. Is this a possibility? I think
I meant setting the lock timeout for the clients who are being locked
out of the system, not the long running process. For that, a query
timeout could help depending on the situation. At least if you use a
lock timeout when this process seems to lock other users out, they 'll
get a nice message saying that they should call support because the
system is having issues and then you can quickly address the problem. It
doesn't prevent the problem, which is either SQL based or application
based.
--
David Gugick
Imceda Software
www.imceda.com

prevent zero length entries

I have a couple critical fields where I am setting them to no nulls but just
realized that they can be set to zero length strings very easily. Other than
a trigger or handling the validation in the front end, is there an easy way
to handle this?
MS Access is my front end so interestingly enough, if you select an the
entire text in a field and hit the delete key it trys to set to NULL not ""
so it's ok. But in EM you can just hit the delete key and it goes to ""
instead of NULL. Obviously end users are goign to use the front end and not
EM so this isn't really a problem but I was just wondering about other front
ends that might not behave the same way. They might set to "" on hitting the
delete key.
THanks,
KeithKeith G Hicks wrote:
> I have a couple critical fields where I am setting them to no nulls but ju
st
> realized that they can be set to zero length strings very easily. Other th
an
> a trigger or handling the validation in the front end, is there an easy wa
y
> to handle this?
> MS Access is my front end so interestingly enough, if you select an the
> entire text in a field and hit the delete key it trys to set to NULL not "
"
> so it's ok. But in EM you can just hit the delete key and it goes to ""
> instead of NULL. Obviously end users are goign to use the front end and no
t
> EM so this isn't really a problem but I was just wondering about other fro
nt
> ends that might not behave the same way. They might set to "" on hitting t
he
> delete key.
> THanks,
> Keith
>
A check constraint of LEN(ColumnName) > 0 would do it, or you could work
in a more complex requirement. Honestly, if you're worried about people
just putting in an empty string, and you require at least one char,
they'll learn that if they put in 1 char they can save the data. If you
bump the requirement up to 2, they'll put in 2. :D
Zach|||keith,
create a check constraint on table, with expression: datalength(col)>0
hth,
dean
"Keith G Hicks" <krh@.comcast.net> wrote in message
news:%23w4ZvbwCFHA.1932@.TK2MSFTNGP14.phx.gbl...
> I have a couple critical fields where I am setting them to no nulls but
just
> realized that they can be set to zero length strings very easily. Other
than
> a trigger or handling the validation in the front end, is there an easy
way
> to handle this?
> MS Access is my front end so interestingly enough, if you select an the
> entire text in a field and hit the delete key it trys to set to NULL not
""
> so it's ok. But in EM you can just hit the delete key and it goes to ""
> instead of NULL. Obviously end users are goign to use the front end and
not
> EM so this isn't really a problem but I was just wondering about other
front
> ends that might not behave the same way. They might set to "" on hitting
the
> delete key.
> THanks,
> Keith
>|||Thank you to both of you. That works just fine of course.
I feel sort of silly now. :)|||"Zach Wells" wrote:
<snip>

> Honestly, if you're worried about people just putting in
> an empty string, and you require at least one char, they'll
> learn that if they put in 1 char they can save the data. If
> you bump the requirement up to 2, they'll put in 2. :D
LOL! Never underestimate the ability of users to circumvent your data
validation routines. We had a situation just like that. You could even map
out the evolution of the validation routine and the users' innovation
(especially since the application had no way of exactly determining what a
correct string was for that particular column). The progression looked
something like:
.
NA
N/A
XXXX
DONTKNOW
:)
Craig