I am trying to find out if there is a way that I can automatically have sql
server send a file from a folder and ftp it to a dialer.
what happens currently is the server generates a text file and manually i
have to ftp it to a dialer, i was hoping i can automate this.Use a scheduled application that wakes up and checks the folder for new
files to FTP. FTP is much simpler to accomplish from command line, C#, etc.
To do it in SQL would just mean putting a T-SQL wrapper around those
applications, and it doesn't make a lot of sense. Seems like taping two
hammers together and trying to hang a picture with the heaviest end.
"Justin" <Justin@.discussions.microsoft.com> wrote in message
news:8D29C359-19D7-45A8-8004-CCD3C4E640CB@.microsoft.com...
>I am trying to find out if there is a way that I can automatically have sql
> server send a file from a folder and ftp it to a dialer.
> what happens currently is the server generates a text file and manually i
> have to ftp it to a dialer, i was hoping i can automate this.|||I would look into using a CmdExec step of a SQL Server Agent job. The
command-line FTP application is scriptable.
-Alan
Showing posts with label automatically. Show all posts
Showing posts with label automatically. Show all posts
Tuesday, March 27, 2012
Friday, March 23, 2012
Frustrated with Parameters Automatically Reloading the Report
Hello.
Here's the scenario: I have two report parameters, both with default values.
As I would expect, the report loads automatically in Preview mode. But why,
when I try to change either parameter value, it seems to automatically reload
the report on simply setting focus to the text box for the other parameter.
I can't seem to stop this from happening, and it actually prevents me from
being able to run the report with any other values than the default values.
Am I missing something?
Thanks,
JohnJohn,
The easiest way to stop it is not to populate parameter 2 with a default and
make sure the parameter does NOT allow blanks.
Normally you can stop the report, change the parameters and click view
report to re-run it.
But remember the 'preview' doesn't necessarily behave the same as when
deployed. I don't use the preview mode much anymore because of this. I find
it better to deploy (or run) to a developement area (use solutions
configuration to switch between Debug and Production to handle this) and then
you get a more accurate picture of it's likely behaviour in the wild.
It's not that much extra hassle to do it this way, if you right click the
report in the solution explorer window you can deploy or deploy & Run just
that report.
Solutions Configurator properties can be accessed by clicking the project
name in the Solution Explorer and selecting properties. You can set different
deployment folders for Debug and Production modes here.
Regards
Chris
"JohnJohn" wrote:
> Hello.
> Here's the scenario: I have two report parameters, both with default values.
> As I would expect, the report loads automatically in Preview mode. But why,
> when I try to change either parameter value, it seems to automatically reload
> the report on simply setting focus to the text box for the other parameter.
> I can't seem to stop this from happening, and it actually prevents me from
> being able to run the report with any other values than the default values.
> Am I missing something?
> Thanks,
> John
Here's the scenario: I have two report parameters, both with default values.
As I would expect, the report loads automatically in Preview mode. But why,
when I try to change either parameter value, it seems to automatically reload
the report on simply setting focus to the text box for the other parameter.
I can't seem to stop this from happening, and it actually prevents me from
being able to run the report with any other values than the default values.
Am I missing something?
Thanks,
JohnJohn,
The easiest way to stop it is not to populate parameter 2 with a default and
make sure the parameter does NOT allow blanks.
Normally you can stop the report, change the parameters and click view
report to re-run it.
But remember the 'preview' doesn't necessarily behave the same as when
deployed. I don't use the preview mode much anymore because of this. I find
it better to deploy (or run) to a developement area (use solutions
configuration to switch between Debug and Production to handle this) and then
you get a more accurate picture of it's likely behaviour in the wild.
It's not that much extra hassle to do it this way, if you right click the
report in the solution explorer window you can deploy or deploy & Run just
that report.
Solutions Configurator properties can be accessed by clicking the project
name in the Solution Explorer and selecting properties. You can set different
deployment folders for Debug and Production modes here.
Regards
Chris
"JohnJohn" wrote:
> Hello.
> Here's the scenario: I have two report parameters, both with default values.
> As I would expect, the report loads automatically in Preview mode. But why,
> when I try to change either parameter value, it seems to automatically reload
> the report on simply setting focus to the text box for the other parameter.
> I can't seem to stop this from happening, and it actually prevents me from
> being able to run the report with any other values than the default values.
> Am I missing something?
> Thanks,
> John
Wednesday, March 7, 2012
Freetexttable Not Finding Inflectional Forms
We're using SQL Server 2005 service pack 1. It was my understanding that
FreeTextTable automatically includes inflectional forms. We're not able to
get any inflectional forms of words during the search (even if we use
Contains with the special syntax). Are we missing something in the server
configuration? Or is there a bug somewhere?
Thanks,
Krip
Are you wrapping your freetext search in double quotes - this disables the
stemming (inflectional search)?
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Krip" <amk@.kynetix.com> wrote in message
news:9E599183-45E3-4CA8-8628-540038B91EB6@.microsoft.com...
> We're using SQL Server 2005 service pack 1. It was my understanding that
> FreeTextTable automatically includes inflectional forms. We're not able
> to get any inflectional forms of words during the search (even if we use
> Contains with the special syntax). Are we missing something in the server
> configuration? Or is there a bug somewhere?
> Thanks,
> Krip
>
|||Hilary,
Nope, not wrapping with double quotes. Here's the clause:
INNER JOIN FreeTextTable(myTable, myField, 'tests') as FTT
I have 'test' in the data but 'tests' doesn't find it. That's just one
example (fox works but not foxes; landed works but not landing).
The following doesn't work either:
SELECT *
FROM myTable
WHERE CONTAINS(*, 'FORMSOF (INFLECTIONAL, foxes)')
Also, I've now installed SP2 and rebuilt the catalag - same issue.
Is there some place to enable inflectional forms? Or is there a dictionary
to populate?
Thanks,
Krip
|||Perhaps it is a language issue, what does this return? sp_configure 'default
full-text language'
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Krip" <amk@.kynetix.com> wrote in message
news:3424FB65-92CF-40BA-B08E-436298FABB96@.microsoft.com...
> Hilary,
> Nope, not wrapping with double quotes. Here's the clause:
> INNER JOIN FreeTextTable(myTable, myField, 'tests') as FTT
> I have 'test' in the data but 'tests' doesn't find it. That's just one
> example (fox works but not foxes; landed works but not landing).
> The following doesn't work either:
> SELECT *
> FROM myTable
> WHERE CONTAINS(*, 'FORMSOF (INFLECTIONAL, foxes)')
> Also, I've now installed SP2 and rebuilt the catalag - same issue.
> Is there some place to enable inflectional forms? Or is there a
> dictionary to populate?
> Thanks,
> Krip
>
>
|||Hilary,
It returns the following:
name: default full-text language
minimum: 0
maximum: 2147483647
config_value: 1033
run_value: 1033
Thanks for your help,
Krip
FreeTextTable automatically includes inflectional forms. We're not able to
get any inflectional forms of words during the search (even if we use
Contains with the special syntax). Are we missing something in the server
configuration? Or is there a bug somewhere?
Thanks,
Krip
Are you wrapping your freetext search in double quotes - this disables the
stemming (inflectional search)?
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Krip" <amk@.kynetix.com> wrote in message
news:9E599183-45E3-4CA8-8628-540038B91EB6@.microsoft.com...
> We're using SQL Server 2005 service pack 1. It was my understanding that
> FreeTextTable automatically includes inflectional forms. We're not able
> to get any inflectional forms of words during the search (even if we use
> Contains with the special syntax). Are we missing something in the server
> configuration? Or is there a bug somewhere?
> Thanks,
> Krip
>
|||Hilary,
Nope, not wrapping with double quotes. Here's the clause:
INNER JOIN FreeTextTable(myTable, myField, 'tests') as FTT
I have 'test' in the data but 'tests' doesn't find it. That's just one
example (fox works but not foxes; landed works but not landing).
The following doesn't work either:
SELECT *
FROM myTable
WHERE CONTAINS(*, 'FORMSOF (INFLECTIONAL, foxes)')
Also, I've now installed SP2 and rebuilt the catalag - same issue.
Is there some place to enable inflectional forms? Or is there a dictionary
to populate?
Thanks,
Krip
|||Perhaps it is a language issue, what does this return? sp_configure 'default
full-text language'
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Krip" <amk@.kynetix.com> wrote in message
news:3424FB65-92CF-40BA-B08E-436298FABB96@.microsoft.com...
> Hilary,
> Nope, not wrapping with double quotes. Here's the clause:
> INNER JOIN FreeTextTable(myTable, myField, 'tests') as FTT
> I have 'test' in the data but 'tests' doesn't find it. That's just one
> example (fox works but not foxes; landed works but not landing).
> The following doesn't work either:
> SELECT *
> FROM myTable
> WHERE CONTAINS(*, 'FORMSOF (INFLECTIONAL, foxes)')
> Also, I've now installed SP2 and rebuilt the catalag - same issue.
> Is there some place to enable inflectional forms? Or is there a
> dictionary to populate?
> Thanks,
> Krip
>
>
|||Hilary,
It returns the following:
name: default full-text language
minimum: 0
maximum: 2147483647
config_value: 1033
run_value: 1033
Thanks for your help,
Krip
Friday, February 24, 2012
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...
>
"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...
>
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 fi
le can grow as space is
needed. Note that grow hurts performance (growing 100MB can take some 10 sec
onds during the user who
is victim for the grow sits and wait for the operation to be performed). I s
uggest you pre-allocate
storage so you have some free space, and of course not shrink the files regu
larly
(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 "Unrestric
ted 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 st
orage manually) ?
> Thank you.
>
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote i
n 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 data
bases, 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...[
vbcol=seagreen]
> 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 sit
uations 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 i
n message
> news:uPJhVkAfFHA.4040@.TK2MSFTNGP14.phx.gbl...
>[/vbcol]|||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 messa
ges to eventlog. But
messages from SQL server are not written to eventlog when a database ix X pe
rcent full.
You can try http://www.dbmaint.com/util_proc.asp, the "warn if full db" proc
edure. 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...[
vbcol=seagreen]
> 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 dow
n box ?
> Thanks.
>[/vbcol]|||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...
>
"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 fi
le can grow as space is
needed. Note that grow hurts performance (growing 100MB can take some 10 sec
onds during the user who
is victim for the grow sits and wait for the operation to be performed). I s
uggest you pre-allocate
storage so you have some free space, and of course not shrink the files regu
larly
(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 "Unrestric
ted 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 st
orage manually) ?
> Thank you.
>
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote i
n 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 data
bases, 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...[
vbcol=seagreen]
> 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 sit
uations 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 i
n message
> news:uPJhVkAfFHA.4040@.TK2MSFTNGP14.phx.gbl...
>[/vbcol]|||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 messa
ges to eventlog. But
messages from SQL server are not written to eventlog when a database ix X pe
rcent full.
You can try http://www.dbmaint.com/util_proc.asp, the "warn if full db" proc
edure. 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...[
vbcol=seagreen]
> 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 dow
n box ?
> Thanks.
>[/vbcol]|||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...
>
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...
>> 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.
>|||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...
>> 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.
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...
>> 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.
>> --
>|||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...
>> 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.
>> --
>>
>|||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...
>> 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.
>>
>|||Yes.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"fniles" <fniles@.pfmail.com> wrote in message news:%234l5YnLfFHA.3584@.TK2MSFTNGP09.phx.gbl...
> 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...
>> 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.
>>
>>
>
"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...
>> 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.
>|||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...
>> 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.
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...
>> 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.
>> --
>|||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...
>> 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.
>> --
>>
>|||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...
>> 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.
>>
>|||Yes.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"fniles" <fniles@.pfmail.com> wrote in message news:%234l5YnLfFHA.3584@.TK2MSFTNGP09.phx.gbl...
> 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...
>> 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.
>>
>>
>
Subscribe to:
Posts (Atom)