Free "space allocated" is 0

We set our SQL Server database to "Automatically grow file" and
"Unrestricted file growth". Why in the TaskPad view, the free "space
allocated" is 0 ? Thank you.
The file has a certain size. For that size, it seems you have 0 free. The file can grow as space is
needed. Note that grow hurts performance (growing 100MB can take some 10 seconds during the user who
is victim for the grow sits and wait for the operation to be performed). I suggest you pre-allocate
storage so you have some free space, and of course not shrink the files regularly
(http://www.karaszi.com/SQLServer/info_dont_shrink.asp).
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"fniles" <fniles@.pfmail.com> wrote in message news:O%23C8Fk$eFHA.2180@.TK2MSFTNGP12.phx.gbl...
> We set our SQL Server database to "Automatically grow file" and "Unrestricted file growth". Why in
> the TaskPad view, the free "space allocated" is 0 ? Thank you.
>
|||Thank you for your reply.
By checking the "Unrestricted file growth" and "Automatically grow file" ,
doesn't it suppose to allocate the space automatically (instead of having to
pre-allocate the storage manually) ?
Thank you.
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:%23w1N5JAfFHA.616@.TK2MSFTNGP12.phx.gbl...
> The file has a certain size. For that size, it seems you have 0 free. The
> file can grow as space is needed. Note that grow hurts performance
> (growing 100MB can take some 10 seconds during the user who is victim for
> the grow sits and wait for the operation to be performed). I suggest you
> pre-allocate storage so you have some free space, and of course not shrink
> the files regularly
> (http://www.karaszi.com/SQLServer/info_dont_shrink.asp).
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "fniles" <fniles@.pfmail.com> wrote in message
> news:O%23C8Fk$eFHA.2180@.TK2MSFTNGP12.phx.gbl...
>
|||Yes, but the users who need space during the grow need to wait for the grow to finish. Also, a lot
of grow will cause fragmentation at the file system level.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"fniles" <fniles@.pfmail.com> wrote in message news:%237ldhWAfFHA.1036@.tk2msftngp13.phx.gbl...
> Thank you for your reply.
> By checking the "Unrestricted file growth" and "Automatically grow file" , doesn't it suppose to
> allocate the space automatically (instead of having to pre-allocate the storage manually) ?
> Thank you.
>
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in message
> news:%23w1N5JAfFHA.616@.TK2MSFTNGP12.phx.gbl...
>
|||Thank you for your reply.
I am just wondering, why in my case even though "Unrestricted file growth"
and "Automatically grow file" are checked, it did not allocate the space
automatically ?
Is this the case like mentioned in your article
http://www.karaszi.com/SQLServer/info_dont_shrink.asp where "There are
situations where autogrow doesn't "catch up" with the space usage
requirements" ?
So, the better way is to always manually allocate the space ?
Thanks.
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:uPJhVkAfFHA.4040@.TK2MSFTNGP14.phx.gbl...
> Yes, but the users who need space during the grow need to wait for the
> grow to finish. Also, a lot of grow will cause fragmentation at the file
> system level.
> --
|||Space isn't allocated automatically in advance. Imagine someone want to add an order, for instance.
An insert is performed by the application. The file is full so the file need to be expanded, while
the user waits.
And imagine if the grow takes long time, so the application does a time-out during this. The grow
will be rolled back and we are back to square zero. For medium to large databases, you want to
pre-allocate storage.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"fniles" <fniles@.pfmail.com> wrote in message news:uH6KGzAfFHA.2732@.TK2MSFTNGP14.phx.gbl...
> Thank you for your reply.
> I am just wondering, why in my case even though "Unrestricted file growth" and "Automatically
> grow file" are checked, it did not allocate the space automatically ?
> Is this the case like mentioned in your article
> http://www.karaszi.com/SQLServer/info_dont_shrink.asp where "There are situations where autogrow
> doesn't "catch up" with the space usage requirements" ?
> So, the better way is to always manually allocate the space ?
> Thanks.
>
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in message
> news:uPJhVkAfFHA.4040@.TK2MSFTNGP14.phx.gbl...
>
|||I see. In my case the database is about 2 gig, so when someone want to add a
new record, the grow takes a long time, and it times out, so it did not
allocate the space.
The application did get the "Timeout expired" error.
I will pre-allocate the space from now on.
Thanks a lot for your help. I appreciate it.
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:uTIhk9AfFHA.1412@.TK2MSFTNGP09.phx.gbl...
> Space isn't allocated automatically in advance. Imagine someone want to
> add an order, for instance. An insert is performed by the application. The
> file is full so the file need to be expanded, while the user waits.
> And imagine if the grow takes long time, so the application does a
> time-out during this. The grow will be rolled back and we are back to
> square zero. For medium to large databases, you want to pre-allocate
> storage.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "fniles" <fniles@.pfmail.com> wrote in message
> news:uH6KGzAfFHA.2732@.TK2MSFTNGP14.phx.gbl...
>
|||One more thing.
I see there is a wizard to create an alert. If I want to create an alert
(right click on the database, View - Taskpad, tab "Wizards" - Create an
Alert) when it is time for me to pre-allocate space again, what "error
severity" shall I select from the wizard drop down box ?
Thanks.
|||Alerts are either based on perfmon counters. There's no perfmon counter for free space in a database
data file, but there is for a database tlog file. Or they are based on messages to eventlog. But
messages from SQL server are not written to eventlog when a database ix X percent full.
You can try http://www.dbmaint.com/util_proc.asp, the "warn if full db" procedure. I suggest you
read the code and adapt it to your needs.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"fniles" <fniles@.pfmail.com> wrote in message news:eiE00YBfFHA.1328@.TK2MSFTNGP12.phx.gbl...
> One more thing.
> I see there is a wizard to create an alert. If I want to create an alert (right click on the
> database, View - Taskpad, tab "Wizards" - Create an Alert) when it is time for me to pre-allocate
> space again, what "error severity" shall I select from the wizard drop down box ?
> Thanks.
>
|||Thanks a lot for your help.
When you said "perfmon counters" did you mean performance counters ?
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:ux5vfwHfFHA.1044@.tk2msftngp13.phx.gbl...
> Alerts are either based on perfmon counters. There's no perfmon counter
> for free space in a database data file, but there is for a database tlog
> file. Or they are based on messages to eventlog. But messages from SQL
> server are not written to eventlog when a database ix X percent full.
> You can try http://www.dbmaint.com/util_proc.asp, the "warn if full db"
> procedure. I suggest you read the code and adapt it to your needs.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "fniles" <fniles@.pfmail.com> wrote in message
> news:eiE00YBfFHA.1328@.TK2MSFTNGP12.phx.gbl...
>