friends,
I m in a process of makin a website tat will store the vital
statistics of users.Users can turn out to be in millions!!!
Can u help me to kno how much space will tat take and how much space
should i book online to keep my site in wrkin condition.
Ur help sought.
Thanx
samraatWithout table definitions (Including indexes) that would be impossible.
100 milllion users at an average of 8 bytes per row is small
100 million users at an average of 7800 bytes per row with 70 indexes most of them compound could be nasty.
Like I said, without definitions the permetations are endless.
Allan Mitchell (Microsoft SQL Server MVP)
MCSE,MCDBA
www.SQLDTS.com
I support PASS - the definitive, global community
for SQL Server professionals - http://www.sqlpass.org|||samabhik,
> I m in a process of makin a website tat will store the vital
> statistics of users.Users can turn out to be in millions!!!
> Can u help me to kno how much space will tat take and how much
> space should i book online to keep my site in wrkin condition.
Once you have your schema, you can calculate how much space you will
need. Refer to the topic Estimating Database Size in Books Online
for an example.
Linda
Showing posts with label store. Show all posts
Showing posts with label store. Show all posts
Wednesday, March 28, 2012
Friday, March 23, 2012
how much datas we can store in a table
hello world,
we r developing a software for our company.one table is for client support
and feedback.in tht table more than one pages of datas has to be inserted pe
r
client.is this possible?if not is it possible to shift the datas in tht tabl
e
to other table automatically when tht table is full.if so pls send the
queries for tht.Hi
There are limits to things like number of columns in a table and size of
database see:
http://msdn.microsoft.com/library/d...br />
8dbn.asp
The number of rows your table contains is limited by your hardware
constraints.
It is not clear exactly what you require, if you wish to archive/delete old
data, then you will need a means of identifying that data, but you don't giv
e
any information regarding your table structure or data. Posting DDL (Create
table statements etc...) http://www.aspfaq.com/etiquette.asp?id=5006 and
example data as insert statements http://vyaskn.tripod.com/code.htm#inserts
would help.
To periodically run your archive task check out SQLAgentService in books
online and how to create/run jobs or at
http://msdn.microsoft.com/library/d...br />
6x0l.asp
John
"nikhil" wrote:
> hello world,
> we r developing a software for our company.one table is for client support
> and feedback.in tht table more than one pages of datas has to be inserted
per
> client.is this possible?if not is it possible to shift the datas in tht ta
ble
> to other table automatically when tht table is full.if so pls send the
> queries for tht.
>
we r developing a software for our company.one table is for client support
and feedback.in tht table more than one pages of datas has to be inserted pe
r
client.is this possible?if not is it possible to shift the datas in tht tabl
e
to other table automatically when tht table is full.if so pls send the
queries for tht.Hi
There are limits to things like number of columns in a table and size of
database see:
http://msdn.microsoft.com/library/d...br />
8dbn.asp
The number of rows your table contains is limited by your hardware
constraints.
It is not clear exactly what you require, if you wish to archive/delete old
data, then you will need a means of identifying that data, but you don't giv
e
any information regarding your table structure or data. Posting DDL (Create
table statements etc...) http://www.aspfaq.com/etiquette.asp?id=5006 and
example data as insert statements http://vyaskn.tripod.com/code.htm#inserts
would help.
To periodically run your archive task check out SQLAgentService in books
online and how to create/run jobs or at
http://msdn.microsoft.com/library/d...br />
6x0l.asp
John
"nikhil" wrote:
> hello world,
> we r developing a software for our company.one table is for client support
> and feedback.in tht table more than one pages of datas has to be inserted
per
> client.is this possible?if not is it possible to shift the datas in tht ta
ble
> to other table automatically when tht table is full.if so pls send the
> queries for tht.
>
how much datas we can store in a table
hello world,
we r developing a software for our company.one table is for client support
and feedback.in tht table more than one pages of datas has to be inserted per
client.is this possible?if not is it possible to shift the datas in tht table
to other table automatically when tht table is full.if so pls send the
queries for tht.
Hi
There are limits to things like number of columns in a table and size of
database see:
http://msdn.microsoft.com/library/de...ar_ts_8dbn.asp
The number of rows your table contains is limited by your hardware
constraints.
It is not clear exactly what you require, if you wish to archive/delete old
data, then you will need a means of identifying that data, but you don't give
any information regarding your table structure or data. Posting DDL (Create
table statements etc...) http://www.aspfaq.com/etiquette.asp?id=5006 and
example data as insert statements http://vyaskn.tripod.com/code.htm#inserts
would help.
To periodically run your archive task check out SQLAgentService in books
online and how to create/run jobs or at
http://msdn.microsoft.com/library/de...ar_cs_6x0l.asp
John
"nikhil" wrote:
> hello world,
> we r developing a software for our company.one table is for client support
> and feedback.in tht table more than one pages of datas has to be inserted per
> client.is this possible?if not is it possible to shift the datas in tht table
> to other table automatically when tht table is full.if so pls send the
> queries for tht.
>
we r developing a software for our company.one table is for client support
and feedback.in tht table more than one pages of datas has to be inserted per
client.is this possible?if not is it possible to shift the datas in tht table
to other table automatically when tht table is full.if so pls send the
queries for tht.
Hi
There are limits to things like number of columns in a table and size of
database see:
http://msdn.microsoft.com/library/de...ar_ts_8dbn.asp
The number of rows your table contains is limited by your hardware
constraints.
It is not clear exactly what you require, if you wish to archive/delete old
data, then you will need a means of identifying that data, but you don't give
any information regarding your table structure or data. Posting DDL (Create
table statements etc...) http://www.aspfaq.com/etiquette.asp?id=5006 and
example data as insert statements http://vyaskn.tripod.com/code.htm#inserts
would help.
To periodically run your archive task check out SQLAgentService in books
online and how to create/run jobs or at
http://msdn.microsoft.com/library/de...ar_cs_6x0l.asp
John
"nikhil" wrote:
> hello world,
> we r developing a software for our company.one table is for client support
> and feedback.in tht table more than one pages of datas has to be inserted per
> client.is this possible?if not is it possible to shift the datas in tht table
> to other table automatically when tht table is full.if so pls send the
> queries for tht.
>
how much datas we can store in a table
hello world,
we r developing a software for our company.one table is for client support
and feedback.in tht table more than one pages of datas has to be inserted per
client.is this possible?if not is it possible to shift the datas in tht table
to other table automatically when tht table is full.if so pls send the
queries for tht.Hi
There are limits to things like number of columns in a table and size of
database see:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/architec/8_ar_ts_8dbn.asp
The number of rows your table contains is limited by your hardware
constraints.
It is not clear exactly what you require, if you wish to archive/delete old
data, then you will need a means of identifying that data, but you don't give
any information regarding your table structure or data. Posting DDL (Create
table statements etc...) http://www.aspfaq.com/etiquette.asp?id=5006 and
example data as insert statements http://vyaskn.tripod.com/code.htm#inserts
would help.
To periodically run your archive task check out SQLAgentService in books
online and how to create/run jobs or at
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/architec/8_ar_cs_6x0l.asp
John
"nikhil" wrote:
> hello world,
> we r developing a software for our company.one table is for client support
> and feedback.in tht table more than one pages of datas has to be inserted per
> client.is this possible?if not is it possible to shift the datas in tht table
> to other table automatically when tht table is full.if so pls send the
> queries for tht.
>sql
we r developing a software for our company.one table is for client support
and feedback.in tht table more than one pages of datas has to be inserted per
client.is this possible?if not is it possible to shift the datas in tht table
to other table automatically when tht table is full.if so pls send the
queries for tht.Hi
There are limits to things like number of columns in a table and size of
database see:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/architec/8_ar_ts_8dbn.asp
The number of rows your table contains is limited by your hardware
constraints.
It is not clear exactly what you require, if you wish to archive/delete old
data, then you will need a means of identifying that data, but you don't give
any information regarding your table structure or data. Posting DDL (Create
table statements etc...) http://www.aspfaq.com/etiquette.asp?id=5006 and
example data as insert statements http://vyaskn.tripod.com/code.htm#inserts
would help.
To periodically run your archive task check out SQLAgentService in books
online and how to create/run jobs or at
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/architec/8_ar_cs_6x0l.asp
John
"nikhil" wrote:
> hello world,
> we r developing a software for our company.one table is for client support
> and feedback.in tht table more than one pages of datas has to be inserted per
> client.is this possible?if not is it possible to shift the datas in tht table
> to other table automatically when tht table is full.if so pls send the
> queries for tht.
>sql
how much bytes needed in sql server
Hi i want to know how much bytes will sql server take to store a
single alphabet like "a". i need to know similarly for all data types
(including images).here i am doing a application where i need to
predict the amount of space required in sql server to store the user
fed dynamic data.
If you use varchar, that's one byte. If you use nvarchar, it's two bytes.
Look up the "Data types" in Books Online. With this as a starting point,
you should be able to find the storeage consumption fo every data type.
online.sbcglobal.netwrote:
>
>
>
- Show quoted text -
thanks for all the replies...
They are referring to what is (perhaps confusingly) named "SQL Server 2005
Books Online", as noted at the end of Erland's message.
http://technet.microsoft.com/en-us/...r/bb428874.aspx
Andrew|||In addition to the web URL, the documentation is also available via local
help. One method to find the relevant topic is by entering "estimating
table size" in the index search.
>>
>>
>>
>- Show quoted text -
>
thanks for all the replies...
>
I dont find any useful books in online..
Can u people suggest some useful URL regarding to my problem ?
>
Regards
visu
>
single alphabet like "a". i need to know similarly for all data types
(including images).here i am doing a application where i need to
predict the amount of space required in sql server to store the user
fed dynamic data.
Give me a handy solution.
Regards
visuvisu (k.visube@.gmail.com) writes:
Quote:
Originally Posted by
Hi i want to know how much bytes will sql server take to store a
single alphabet like "a".
If you use varchar, that's one byte. If you use nvarchar, it's two bytes.
Quote:
Originally Posted by
i need to know similarly for all data types
(including images).here i am doing a application where i need to
predict the amount of space required in sql server to store the user
fed dynamic data.
Look up the "Data types" in Books Online. With this as a starting point,
you should be able to find the storeage consumption fo every data type.
--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||To add on to Erland's response, see the Books Online for topics on
estimating table and index size. In addition to the storage requirements of
individual columns, you'll need to add additional overhead for row, page,
etc.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"visu" <k.visube@.gmail.comwrote in message
news:1181746030.311842.326400@.o11g2000prd.googlegr oups.com...
Quote:
Originally Posted by
Hi i want to know how much bytes will sql server take to store a
single alphabet like "a". i need to know similarly for all data types
(including images).here i am doing a application where i need to
predict the amount of space required in sql server to store the user
fed dynamic data.
>
Give me a handy solution.
>
Regards
visu
>
online.sbcglobal.netwrote:
Quote:
Originally Posted by
To add on to Erland's response, see the Books Online for topics on
estimating table and index size. In addition to the storage requirements of
individual columns, you'll need to add additional overhead for row, page,
etc.
>
--
Hope this helps.
>
Dan Guzman
SQL Server MVP
>
"visu" <k.vis...@.gmail.comwrote in message
>
news:1181746030.311842.326400@.o11g2000prd.googlegr oups.com...
>
>
>
Quote:
Originally Posted by
Hi i want to know how much bytes will sql server take to store a
single alphabet like "a". i need to know similarly for all data types
(including images).here i am doing a application where i need to
predict the amount of space required in sql server to store the user
fed dynamic data.
>
Quote:
Originally Posted by
Give me a handy solution.
>
Quote:
Originally Posted by
Regards
visu- Hide quoted text -
>
- Show quoted text -
thanks for all the replies...
I dont find any useful books in online..
Can u people suggest some useful URL regarding to my problem ?
Regards
visu|||visu wrote:
Quote:
Originally Posted by
I dont find any useful books in online..
Can u people suggest some useful URL regarding to my problem ?
They are referring to what is (perhaps confusingly) named "SQL Server 2005
Books Online", as noted at the end of Erland's message.
http://technet.microsoft.com/en-us/...r/bb428874.aspx
Andrew|||In addition to the web URL, the documentation is also available via local
help. One method to find the relevant topic is by entering "estimating
table size" in the index search.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"visu" <k.visube@.gmail.comwrote in message
news:1181912433.105526.223140@.x35g2000prf.googlegr oups.com...
Quote:
Originally Posted by
On Jun 14, 5:40 pm, "Dan Guzman" <guzma...@.nospam-
online.sbcglobal.netwrote:
Quote:
Originally Posted by
>To add on to Erland's response, see the Books Online for topics on
>estimating table and index size. In addition to the storage requirements
>of
>individual columns, you'll need to add additional overhead for row, page,
>etc.
>>
>--
>Hope this helps.
>>
>Dan Guzman
>SQL Server MVP
>>
>"visu" <k.vis...@.gmail.comwrote in message
>>
>news:1181746030.311842.326400@.o11g2000prd.googlegr oups.com...
>>
>>
>>
Quote:
Originally Posted by
Hi i want to know how much bytes will sql server take to store a
single alphabet like "a". i need to know similarly for all data types
(including images).here i am doing a application where i need to
predict the amount of space required in sql server to store the user
fed dynamic data.
>>
Quote:
Originally Posted by
Give me a handy solution.
>>
Quote:
Originally Posted by
Regards
visu- Hide quoted text -
>>
>- Show quoted text -
>
thanks for all the replies...
>
I dont find any useful books in online..
Can u people suggest some useful URL regarding to my problem ?
>
Regards
visu
>
How much allocated space is actually used?
Hi,
This is SQL 2000.
3G has been allocated to store data for a database. Restoring this database
takes very long. Yeah, I know, even if only 5M is used, 3G has to be
restored.
So is there any way to tell how much allocated space is actually used by a
database?
Thanks in advance,
BingCheck out sp_spaceused in the BOL.
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
.
"bing" <bing@.discussions.microsoft.com> wrote in message
news:20A1B26B-0E49-4EDF-B389-7EEB0DA13D01@.microsoft.com...
Hi,
This is SQL 2000.
3G has been allocated to store data for a database. Restoring this database
takes very long. Yeah, I know, even if only 5M is used, 3G has to be
restored.
So is there any way to tell how much allocated space is actually used by a
database?
Thanks in advance,
Bing|||Thanks so much for the response!
So if the result I got was:
database_size: 3120.06MB
unallocated_space: 172.34 MB
The actually used space is database_size - unallocated_space = 3120.06 -
172.34 = 2947.72. Is the formula right?
If there is just 172.34 left, should I add more space to the database now?
Bing
"Tom Moreau" wrote:
> Check out sp_spaceused in the BOL.
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Columnist, SQL Server Professional
> Toronto, ON Canada
> www.pinpub.com
> ..
> "bing" <bing@.discussions.microsoft.com> wrote in message
> news:20A1B26B-0E49-4EDF-B389-7EEB0DA13D01@.microsoft.com...
> Hi,
> This is SQL 2000.
> 3G has been allocated to store data for a database. Restoring this databa
se
> takes very long. Yeah, I know, even if only 5M is used, 3G has to be
> restored.
> So is there any way to tell how much allocated space is actually used by a
> database?
> Thanks in advance,
> Bing
>|||Well, it wouldn't hurt. What really matters is if you're intending to add
more data. It's better to add the space before you need it, since that will
avoid an autogrow event - update activity stalls while the server goes and
allocates the space. If you expand a data file manually before you hit the
autogrow, then you avoid blocking your updates.
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
.
"bing" <bing@.discussions.microsoft.com> wrote in message
news:A385F721-96CD-40F6-B97C-27E3E42962D0@.microsoft.com...
Thanks so much for the response!
So if the result I got was:
database_size: 3120.06MB
unallocated_space: 172.34 MB
The actually used space is database_size - unallocated_space = 3120.06 -
172.34 = 2947.72. Is the formula right?
If there is just 172.34 left, should I add more space to the database now?
Bing
"Tom Moreau" wrote:
> Check out sp_spaceused in the BOL.
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Columnist, SQL Server Professional
> Toronto, ON Canada
> www.pinpub.com
> ..
> "bing" <bing@.discussions.microsoft.com> wrote in message
> news:20A1B26B-0E49-4EDF-B389-7EEB0DA13D01@.microsoft.com...
> Hi,
> This is SQL 2000.
> 3G has been allocated to store data for a database. Restoring this
> database
> takes very long. Yeah, I know, even if only 5M is used, 3G has to be
> restored.
> So is there any way to tell how much allocated space is actually used by a
> database?
> Thanks in advance,
> Bing
>|||I prefer to either write my own procedures or use the undocumented DBCC SHOW
FILESTATS commands
instead of sp_spaceused. One problem with sp_spaceused is that it doesn't se
parate data from log.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"bing" <bing@.discussions.microsoft.com> wrote in message
news:20A1B26B-0E49-4EDF-B389-7EEB0DA13D01@.microsoft.com...
> Hi,
> This is SQL 2000.
> 3G has been allocated to store data for a database. Restoring this databa
se
> takes very long. Yeah, I know, even if only 5M is used, 3G has to be
> restored.
> So is there any way to tell how much allocated space is actually used by a
> database?
> Thanks in advance,
> Bing|||Thanks so much for the information. I've found the codes provided on
http://www.databasejournal.com/feat...10894_3414111_2
very helpful. It put data space and log space usage together.
Bing
"Tibor Karaszi" wrote:
> I prefer to either write my own procedures or use the undocumented DBCC SH
OWFILESTATS commands
> instead of sp_spaceused. One problem with sp_spaceused is that it doesn't
separate data from log.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "bing" <bing@.discussions.microsoft.com> wrote in message
> news:20A1B26B-0E49-4EDF-B389-7EEB0DA13D01@.microsoft.com...
>
>
This is SQL 2000.
3G has been allocated to store data for a database. Restoring this database
takes very long. Yeah, I know, even if only 5M is used, 3G has to be
restored.
So is there any way to tell how much allocated space is actually used by a
database?
Thanks in advance,
BingCheck out sp_spaceused in the BOL.
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
.
"bing" <bing@.discussions.microsoft.com> wrote in message
news:20A1B26B-0E49-4EDF-B389-7EEB0DA13D01@.microsoft.com...
Hi,
This is SQL 2000.
3G has been allocated to store data for a database. Restoring this database
takes very long. Yeah, I know, even if only 5M is used, 3G has to be
restored.
So is there any way to tell how much allocated space is actually used by a
database?
Thanks in advance,
Bing|||Thanks so much for the response!
So if the result I got was:
database_size: 3120.06MB
unallocated_space: 172.34 MB
The actually used space is database_size - unallocated_space = 3120.06 -
172.34 = 2947.72. Is the formula right?
If there is just 172.34 left, should I add more space to the database now?
Bing
"Tom Moreau" wrote:
> Check out sp_spaceused in the BOL.
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Columnist, SQL Server Professional
> Toronto, ON Canada
> www.pinpub.com
> ..
> "bing" <bing@.discussions.microsoft.com> wrote in message
> news:20A1B26B-0E49-4EDF-B389-7EEB0DA13D01@.microsoft.com...
> Hi,
> This is SQL 2000.
> 3G has been allocated to store data for a database. Restoring this databa
se
> takes very long. Yeah, I know, even if only 5M is used, 3G has to be
> restored.
> So is there any way to tell how much allocated space is actually used by a
> database?
> Thanks in advance,
> Bing
>|||Well, it wouldn't hurt. What really matters is if you're intending to add
more data. It's better to add the space before you need it, since that will
avoid an autogrow event - update activity stalls while the server goes and
allocates the space. If you expand a data file manually before you hit the
autogrow, then you avoid blocking your updates.
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
.
"bing" <bing@.discussions.microsoft.com> wrote in message
news:A385F721-96CD-40F6-B97C-27E3E42962D0@.microsoft.com...
Thanks so much for the response!
So if the result I got was:
database_size: 3120.06MB
unallocated_space: 172.34 MB
The actually used space is database_size - unallocated_space = 3120.06 -
172.34 = 2947.72. Is the formula right?
If there is just 172.34 left, should I add more space to the database now?
Bing
"Tom Moreau" wrote:
> Check out sp_spaceused in the BOL.
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Columnist, SQL Server Professional
> Toronto, ON Canada
> www.pinpub.com
> ..
> "bing" <bing@.discussions.microsoft.com> wrote in message
> news:20A1B26B-0E49-4EDF-B389-7EEB0DA13D01@.microsoft.com...
> Hi,
> This is SQL 2000.
> 3G has been allocated to store data for a database. Restoring this
> database
> takes very long. Yeah, I know, even if only 5M is used, 3G has to be
> restored.
> So is there any way to tell how much allocated space is actually used by a
> database?
> Thanks in advance,
> Bing
>|||I prefer to either write my own procedures or use the undocumented DBCC SHOW
FILESTATS commands
instead of sp_spaceused. One problem with sp_spaceused is that it doesn't se
parate data from log.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"bing" <bing@.discussions.microsoft.com> wrote in message
news:20A1B26B-0E49-4EDF-B389-7EEB0DA13D01@.microsoft.com...
> Hi,
> This is SQL 2000.
> 3G has been allocated to store data for a database. Restoring this databa
se
> takes very long. Yeah, I know, even if only 5M is used, 3G has to be
> restored.
> So is there any way to tell how much allocated space is actually used by a
> database?
> Thanks in advance,
> Bing|||Thanks so much for the information. I've found the codes provided on
http://www.databasejournal.com/feat...10894_3414111_2
very helpful. It put data space and log space usage together.
Bing
"Tibor Karaszi" wrote:
> I prefer to either write my own procedures or use the undocumented DBCC SH
OWFILESTATS commands
> instead of sp_spaceused. One problem with sp_spaceused is that it doesn't
separate data from log.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "bing" <bing@.discussions.microsoft.com> wrote in message
> news:20A1B26B-0E49-4EDF-B389-7EEB0DA13D01@.microsoft.com...
>
>
How much allocated space is actually used?
Hi,
This is SQL 2000.
3G has been allocated to store data for a database. Restoring this database
takes very long. Yeah, I know, even if only 5M is used, 3G has to be
restored.
So is there any way to tell how much allocated space is actually used by a
database?
Thanks in advance,
Bing
Check out sp_spaceused in the BOL.
Tom
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
..
"bing" <bing@.discussions.microsoft.com> wrote in message
news:20A1B26B-0E49-4EDF-B389-7EEB0DA13D01@.microsoft.com...
Hi,
This is SQL 2000.
3G has been allocated to store data for a database. Restoring this database
takes very long. Yeah, I know, even if only 5M is used, 3G has to be
restored.
So is there any way to tell how much allocated space is actually used by a
database?
Thanks in advance,
Bing
|||Thanks so much for the response!
So if the result I got was:
database_size: 3120.06MB
unallocated_space: 172.34 MB
The actually used space is database_size - unallocated_space = 3120.06 -
172.34 = 2947.72. Is the formula right?
If there is just 172.34 left, should I add more space to the database now?
Bing
"Tom Moreau" wrote:
> Check out sp_spaceused in the BOL.
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Columnist, SQL Server Professional
> Toronto, ON Canada
> www.pinpub.com
> ..
> "bing" <bing@.discussions.microsoft.com> wrote in message
> news:20A1B26B-0E49-4EDF-B389-7EEB0DA13D01@.microsoft.com...
> Hi,
> This is SQL 2000.
> 3G has been allocated to store data for a database. Restoring this database
> takes very long. Yeah, I know, even if only 5M is used, 3G has to be
> restored.
> So is there any way to tell how much allocated space is actually used by a
> database?
> Thanks in advance,
> Bing
>
|||Well, it wouldn't hurt. What really matters is if you're intending to add
more data. It's better to add the space before you need it, since that will
avoid an autogrow event - update activity stalls while the server goes and
allocates the space. If you expand a data file manually before you hit the
autogrow, then you avoid blocking your updates.
Tom
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
..
"bing" <bing@.discussions.microsoft.com> wrote in message
news:A385F721-96CD-40F6-B97C-27E3E42962D0@.microsoft.com...
Thanks so much for the response!
So if the result I got was:
database_size: 3120.06MB
unallocated_space: 172.34 MB
The actually used space is database_size - unallocated_space = 3120.06 -
172.34 = 2947.72. Is the formula right?
If there is just 172.34 left, should I add more space to the database now?
Bing
"Tom Moreau" wrote:
> Check out sp_spaceused in the BOL.
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Columnist, SQL Server Professional
> Toronto, ON Canada
> www.pinpub.com
> ..
> "bing" <bing@.discussions.microsoft.com> wrote in message
> news:20A1B26B-0E49-4EDF-B389-7EEB0DA13D01@.microsoft.com...
> Hi,
> This is SQL 2000.
> 3G has been allocated to store data for a database. Restoring this
> database
> takes very long. Yeah, I know, even if only 5M is used, 3G has to be
> restored.
> So is there any way to tell how much allocated space is actually used by a
> database?
> Thanks in advance,
> Bing
>
|||I prefer to either write my own procedures or use the undocumented DBCC SHOWFILESTATS commands
instead of sp_spaceused. One problem with sp_spaceused is that it doesn't separate data from log.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"bing" <bing@.discussions.microsoft.com> wrote in message
news:20A1B26B-0E49-4EDF-B389-7EEB0DA13D01@.microsoft.com...
> Hi,
> This is SQL 2000.
> 3G has been allocated to store data for a database. Restoring this database
> takes very long. Yeah, I know, even if only 5M is used, 3G has to be
> restored.
> So is there any way to tell how much allocated space is actually used by a
> database?
> Thanks in advance,
> Bing
|||Thanks so much for the information. I've found the codes provided on
http://www.databasejournal.com/featu...0894_3414111_2
very helpful. It put data space and log space usage together.
Bing
"Tibor Karaszi" wrote:
> I prefer to either write my own procedures or use the undocumented DBCC SHOWFILESTATS commands
> instead of sp_spaceused. One problem with sp_spaceused is that it doesn't separate data from log.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "bing" <bing@.discussions.microsoft.com> wrote in message
> news:20A1B26B-0E49-4EDF-B389-7EEB0DA13D01@.microsoft.com...
>
>
This is SQL 2000.
3G has been allocated to store data for a database. Restoring this database
takes very long. Yeah, I know, even if only 5M is used, 3G has to be
restored.
So is there any way to tell how much allocated space is actually used by a
database?
Thanks in advance,
Bing
Check out sp_spaceused in the BOL.
Tom
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
..
"bing" <bing@.discussions.microsoft.com> wrote in message
news:20A1B26B-0E49-4EDF-B389-7EEB0DA13D01@.microsoft.com...
Hi,
This is SQL 2000.
3G has been allocated to store data for a database. Restoring this database
takes very long. Yeah, I know, even if only 5M is used, 3G has to be
restored.
So is there any way to tell how much allocated space is actually used by a
database?
Thanks in advance,
Bing
|||Thanks so much for the response!
So if the result I got was:
database_size: 3120.06MB
unallocated_space: 172.34 MB
The actually used space is database_size - unallocated_space = 3120.06 -
172.34 = 2947.72. Is the formula right?
If there is just 172.34 left, should I add more space to the database now?
Bing
"Tom Moreau" wrote:
> Check out sp_spaceused in the BOL.
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Columnist, SQL Server Professional
> Toronto, ON Canada
> www.pinpub.com
> ..
> "bing" <bing@.discussions.microsoft.com> wrote in message
> news:20A1B26B-0E49-4EDF-B389-7EEB0DA13D01@.microsoft.com...
> Hi,
> This is SQL 2000.
> 3G has been allocated to store data for a database. Restoring this database
> takes very long. Yeah, I know, even if only 5M is used, 3G has to be
> restored.
> So is there any way to tell how much allocated space is actually used by a
> database?
> Thanks in advance,
> Bing
>
|||Well, it wouldn't hurt. What really matters is if you're intending to add
more data. It's better to add the space before you need it, since that will
avoid an autogrow event - update activity stalls while the server goes and
allocates the space. If you expand a data file manually before you hit the
autogrow, then you avoid blocking your updates.
Tom
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
..
"bing" <bing@.discussions.microsoft.com> wrote in message
news:A385F721-96CD-40F6-B97C-27E3E42962D0@.microsoft.com...
Thanks so much for the response!
So if the result I got was:
database_size: 3120.06MB
unallocated_space: 172.34 MB
The actually used space is database_size - unallocated_space = 3120.06 -
172.34 = 2947.72. Is the formula right?
If there is just 172.34 left, should I add more space to the database now?
Bing
"Tom Moreau" wrote:
> Check out sp_spaceused in the BOL.
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Columnist, SQL Server Professional
> Toronto, ON Canada
> www.pinpub.com
> ..
> "bing" <bing@.discussions.microsoft.com> wrote in message
> news:20A1B26B-0E49-4EDF-B389-7EEB0DA13D01@.microsoft.com...
> Hi,
> This is SQL 2000.
> 3G has been allocated to store data for a database. Restoring this
> database
> takes very long. Yeah, I know, even if only 5M is used, 3G has to be
> restored.
> So is there any way to tell how much allocated space is actually used by a
> database?
> Thanks in advance,
> Bing
>
|||I prefer to either write my own procedures or use the undocumented DBCC SHOWFILESTATS commands
instead of sp_spaceused. One problem with sp_spaceused is that it doesn't separate data from log.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"bing" <bing@.discussions.microsoft.com> wrote in message
news:20A1B26B-0E49-4EDF-B389-7EEB0DA13D01@.microsoft.com...
> Hi,
> This is SQL 2000.
> 3G has been allocated to store data for a database. Restoring this database
> takes very long. Yeah, I know, even if only 5M is used, 3G has to be
> restored.
> So is there any way to tell how much allocated space is actually used by a
> database?
> Thanks in advance,
> Bing
|||Thanks so much for the information. I've found the codes provided on
http://www.databasejournal.com/featu...0894_3414111_2
very helpful. It put data space and log space usage together.
Bing
"Tibor Karaszi" wrote:
> I prefer to either write my own procedures or use the undocumented DBCC SHOWFILESTATS commands
> instead of sp_spaceused. One problem with sp_spaceused is that it doesn't separate data from log.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "bing" <bing@.discussions.microsoft.com> wrote in message
> news:20A1B26B-0E49-4EDF-B389-7EEB0DA13D01@.microsoft.com...
>
>
How much allocated space is actually used?
Hi,
This is SQL 2000.
3G has been allocated to store data for a database. Restoring this database
takes very long. Yeah, I know, even if only 5M is used, 3G has to be
restored.
So is there any way to tell how much allocated space is actually used by a
database?
Thanks in advance,
BingCheck out sp_spaceused in the BOL.
--
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
.
"bing" <bing@.discussions.microsoft.com> wrote in message
news:20A1B26B-0E49-4EDF-B389-7EEB0DA13D01@.microsoft.com...
Hi,
This is SQL 2000.
3G has been allocated to store data for a database. Restoring this database
takes very long. Yeah, I know, even if only 5M is used, 3G has to be
restored.
So is there any way to tell how much allocated space is actually used by a
database?
Thanks in advance,
Bing|||Thanks so much for the response!
So if the result I got was:
database_size: 3120.06MB
unallocated_space: 172.34 MB
The actually used space is database_size - unallocated_space = 3120.06 -
172.34 = 2947.72. Is the formula right?
If there is just 172.34 left, should I add more space to the database now?
Bing
"Tom Moreau" wrote:
> Check out sp_spaceused in the BOL.
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Columnist, SQL Server Professional
> Toronto, ON Canada
> www.pinpub.com
> ..
> "bing" <bing@.discussions.microsoft.com> wrote in message
> news:20A1B26B-0E49-4EDF-B389-7EEB0DA13D01@.microsoft.com...
> Hi,
> This is SQL 2000.
> 3G has been allocated to store data for a database. Restoring this database
> takes very long. Yeah, I know, even if only 5M is used, 3G has to be
> restored.
> So is there any way to tell how much allocated space is actually used by a
> database?
> Thanks in advance,
> Bing
>|||Well, it wouldn't hurt. What really matters is if you're intending to add
more data. It's better to add the space before you need it, since that will
avoid an autogrow event - update activity stalls while the server goes and
allocates the space. If you expand a data file manually before you hit the
autogrow, then you avoid blocking your updates.
--
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
.
"bing" <bing@.discussions.microsoft.com> wrote in message
news:A385F721-96CD-40F6-B97C-27E3E42962D0@.microsoft.com...
Thanks so much for the response!
So if the result I got was:
database_size: 3120.06MB
unallocated_space: 172.34 MB
The actually used space is database_size - unallocated_space = 3120.06 -
172.34 = 2947.72. Is the formula right?
If there is just 172.34 left, should I add more space to the database now?
Bing
"Tom Moreau" wrote:
> Check out sp_spaceused in the BOL.
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Columnist, SQL Server Professional
> Toronto, ON Canada
> www.pinpub.com
> ..
> "bing" <bing@.discussions.microsoft.com> wrote in message
> news:20A1B26B-0E49-4EDF-B389-7EEB0DA13D01@.microsoft.com...
> Hi,
> This is SQL 2000.
> 3G has been allocated to store data for a database. Restoring this
> database
> takes very long. Yeah, I know, even if only 5M is used, 3G has to be
> restored.
> So is there any way to tell how much allocated space is actually used by a
> database?
> Thanks in advance,
> Bing
>|||I prefer to either write my own procedures or use the undocumented DBCC SHOWFILESTATS commands
instead of sp_spaceused. One problem with sp_spaceused is that it doesn't separate data from log.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"bing" <bing@.discussions.microsoft.com> wrote in message
news:20A1B26B-0E49-4EDF-B389-7EEB0DA13D01@.microsoft.com...
> Hi,
> This is SQL 2000.
> 3G has been allocated to store data for a database. Restoring this database
> takes very long. Yeah, I know, even if only 5M is used, 3G has to be
> restored.
> So is there any way to tell how much allocated space is actually used by a
> database?
> Thanks in advance,
> Bing|||Thanks so much for the information. I've found the codes provided on
http://www.databasejournal.com/features/mssql/article.php/10894_3414111_2
very helpful. It put data space and log space usage together.
Bing
"Tibor Karaszi" wrote:
> I prefer to either write my own procedures or use the undocumented DBCC SHOWFILESTATS commands
> instead of sp_spaceused. One problem with sp_spaceused is that it doesn't separate data from log.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "bing" <bing@.discussions.microsoft.com> wrote in message
> news:20A1B26B-0E49-4EDF-B389-7EEB0DA13D01@.microsoft.com...
> > Hi,
> >
> > This is SQL 2000.
> > 3G has been allocated to store data for a database. Restoring this database
> > takes very long. Yeah, I know, even if only 5M is used, 3G has to be
> > restored.
> > So is there any way to tell how much allocated space is actually used by a
> > database?
> >
> > Thanks in advance,
> >
> > Bing
>
>
This is SQL 2000.
3G has been allocated to store data for a database. Restoring this database
takes very long. Yeah, I know, even if only 5M is used, 3G has to be
restored.
So is there any way to tell how much allocated space is actually used by a
database?
Thanks in advance,
BingCheck out sp_spaceused in the BOL.
--
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
.
"bing" <bing@.discussions.microsoft.com> wrote in message
news:20A1B26B-0E49-4EDF-B389-7EEB0DA13D01@.microsoft.com...
Hi,
This is SQL 2000.
3G has been allocated to store data for a database. Restoring this database
takes very long. Yeah, I know, even if only 5M is used, 3G has to be
restored.
So is there any way to tell how much allocated space is actually used by a
database?
Thanks in advance,
Bing|||Thanks so much for the response!
So if the result I got was:
database_size: 3120.06MB
unallocated_space: 172.34 MB
The actually used space is database_size - unallocated_space = 3120.06 -
172.34 = 2947.72. Is the formula right?
If there is just 172.34 left, should I add more space to the database now?
Bing
"Tom Moreau" wrote:
> Check out sp_spaceused in the BOL.
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Columnist, SQL Server Professional
> Toronto, ON Canada
> www.pinpub.com
> ..
> "bing" <bing@.discussions.microsoft.com> wrote in message
> news:20A1B26B-0E49-4EDF-B389-7EEB0DA13D01@.microsoft.com...
> Hi,
> This is SQL 2000.
> 3G has been allocated to store data for a database. Restoring this database
> takes very long. Yeah, I know, even if only 5M is used, 3G has to be
> restored.
> So is there any way to tell how much allocated space is actually used by a
> database?
> Thanks in advance,
> Bing
>|||Well, it wouldn't hurt. What really matters is if you're intending to add
more data. It's better to add the space before you need it, since that will
avoid an autogrow event - update activity stalls while the server goes and
allocates the space. If you expand a data file manually before you hit the
autogrow, then you avoid blocking your updates.
--
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
.
"bing" <bing@.discussions.microsoft.com> wrote in message
news:A385F721-96CD-40F6-B97C-27E3E42962D0@.microsoft.com...
Thanks so much for the response!
So if the result I got was:
database_size: 3120.06MB
unallocated_space: 172.34 MB
The actually used space is database_size - unallocated_space = 3120.06 -
172.34 = 2947.72. Is the formula right?
If there is just 172.34 left, should I add more space to the database now?
Bing
"Tom Moreau" wrote:
> Check out sp_spaceused in the BOL.
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Columnist, SQL Server Professional
> Toronto, ON Canada
> www.pinpub.com
> ..
> "bing" <bing@.discussions.microsoft.com> wrote in message
> news:20A1B26B-0E49-4EDF-B389-7EEB0DA13D01@.microsoft.com...
> Hi,
> This is SQL 2000.
> 3G has been allocated to store data for a database. Restoring this
> database
> takes very long. Yeah, I know, even if only 5M is used, 3G has to be
> restored.
> So is there any way to tell how much allocated space is actually used by a
> database?
> Thanks in advance,
> Bing
>|||I prefer to either write my own procedures or use the undocumented DBCC SHOWFILESTATS commands
instead of sp_spaceused. One problem with sp_spaceused is that it doesn't separate data from log.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"bing" <bing@.discussions.microsoft.com> wrote in message
news:20A1B26B-0E49-4EDF-B389-7EEB0DA13D01@.microsoft.com...
> Hi,
> This is SQL 2000.
> 3G has been allocated to store data for a database. Restoring this database
> takes very long. Yeah, I know, even if only 5M is used, 3G has to be
> restored.
> So is there any way to tell how much allocated space is actually used by a
> database?
> Thanks in advance,
> Bing|||Thanks so much for the information. I've found the codes provided on
http://www.databasejournal.com/features/mssql/article.php/10894_3414111_2
very helpful. It put data space and log space usage together.
Bing
"Tibor Karaszi" wrote:
> I prefer to either write my own procedures or use the undocumented DBCC SHOWFILESTATS commands
> instead of sp_spaceused. One problem with sp_spaceused is that it doesn't separate data from log.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "bing" <bing@.discussions.microsoft.com> wrote in message
> news:20A1B26B-0E49-4EDF-B389-7EEB0DA13D01@.microsoft.com...
> > Hi,
> >
> > This is SQL 2000.
> > 3G has been allocated to store data for a database. Restoring this database
> > takes very long. Yeah, I know, even if only 5M is used, 3G has to be
> > restored.
> > So is there any way to tell how much allocated space is actually used by a
> > database?
> >
> > Thanks in advance,
> >
> > Bing
>
>
how many times the store procedure was execute when the server start
I was wondering if there was any way to know how many times the store procedure was execute when the server start.
When you say on server start do you mean when you boot the server / start the sql serivce? Are you refering to start-up procs (sp_procoption)?|||Not sure what you are looking for or what version of SQL Server you are using but one option with cached query plans in 2005, you can get execution counts from sys.dm_exec_query_stats.
You can view the execution counts with something along the lines of:
SELECT *
FROM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text(sql_handle)
ORDER BY execution_count desc
For a specific stored proc, you can use something like:
SELECT object_name(objectid), execution_count
FROM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text(sql_handle)
WHERE objectid = object_id('YourStoredProcedureName')
-Sue
how many times the store procedure was execute when the server start
I was wondering if there was any way to know how many times the store procedure was execute when the server start.
When you say on server start do you mean when you boot the server / start the sql serivce? Are you refering to start-up procs (sp_procoption)?|||Not sure what you are looking for or what version of SQL Server you are using but one option with cached query plans in 2005, you can get execution counts from sys.dm_exec_query_stats.
You can view the execution counts with something along the lines of:
SELECT *
FROM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text(sql_handle)
ORDER BY execution_count desc
For a specific stored proc, you can use something like:
SELECT object_name(objectid), execution_count
FROM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text(sql_handle)
WHERE objectid = object_id('YourStoredProcedureName')
-Sue
how many tables a dataset can store at a time?
hello friend,
how many tables a dataset can store at a time? can u tell me.
There is no restriction for normal usage, because you'll encounter OutOfMemoryException before reaching actual limit, which is 2^30 datatablessql
how many tables a dataset can store at a time? can u tell me.
Quote:
Originally Posted by mcasaurabhsumit
hello friend,
how many tables a dataset can store at a time? can u tell me.
There is no restriction for normal usage, because you'll encounter OutOfMemoryException before reaching actual limit, which is 2^30 datatablessql
Wednesday, March 7, 2012
How insert Arabic/urdu characters in SQL server
Hi
I am developing an application where i want to store the different language (i.e. chines,Arabic,urdu etc) character in database (SQL Server). so when i store arabic characters in SQL server , it stores the (???).
datatype of field is nvarchar.
is anybody know about the problem and solution.
Regards
Mubahsar Ghazi
The problem is called character conversion and you are getting it because you are using only NVarchar without doing column level collation for Arabic in your database. There are three collations for Arabic in SQL Server 2005. If you are using SQL Server 2000 you can still do column level collation, the links below deals with SQL Server 2005. Hope this helps.
144
binary.256
Binary
SQL_Latin1_General_1256_BIN
145
diction.256
Dictionary order, case-sensitive
SQL_Latin1_General_Cp1256_CS_AS_KI_WI
146
nocase.256
Dictionary order, case-insensitive
SQL_Latin1_General_Cp1256_CI_AS_KI_WI
http://msdn2.microsoft.com/en-us/library/ms144250(SQL.90).aspx
http://msdn2.microsoft.com/en-us/library/ms180175(SQL.90).aspx
|||
thanks for ur response
but i m still getting problems.....
i m pasting the whole code below.while executing it show the exceptionCast from string "?" to type 'Byte' is not valid.
.....................
Dim cAsString = HelpArea.Text ' HelpArea is text box where urdu/arabic characters can be writenconvertC = UniToVar(c) ' function written below
Cmd =
New SqlCommand("INSERT INTO tblCategoryDetail(FieldLang,FieldHelp)Values(' " & dpLanguage.SelectedItem.Text & " ', " & convertC(c) & ")", con)lblMsg.Text = "Values have stored in database"
con.Open()
Cmd.ExecuteNonQuery()
CatchexAs ExceptionlblMsg.Text = ex.Message
EndTrycon.Close()
Public
Function UniToVar(ByVal sTextAsString)AsObjectTry
Dim b()AsByte
b = Encoding.Unicode.GetBytes(sText)
Return b
Catch exAs Exception
lblMsg.Text = ex.Message
EndTry
EndFunction
|||i have posted the whole function in previous message......and need the solution in very short time...... hoping early resopnse by ur side
regards
Mubashar Ghazi
|||I want to query the database using SQL statements that contain Arabic letters... for example, this query won't return any results that start with the letter '?' even though if I try any english letters it works perfectly:
SELECT ArtistID, Name
FROM dbo.Artist
WHERE (Name LIKE '?' + '%')
I also tried switching:
SELECT ArtistID, Name
FROM dbo.Artist
WHERE (Name LIKE '%' + '?')
but it won't work.. any ideas ?
Thanks :)
||| See if the posthttp://forums.asp.net/t/1167422.aspx can be helpful.
How identify locking Store procedure
:eek: I've have a lot of locks in a SQL Server, and I'd like to identify which SPs are locking what tables, I've being trying with sp_who, sp_who2 and sp_lock, so i can identify the process number, but I don't have any idea what this process is doing (which sp is running? and what command?) and which table is locked by this process, can anybody send me some querys to get this information
I'm novice in SQL Server, and I've not access to the Enterprice manager console, and I've only have priveleges to read data from the database
Thanks for your help
Alfredo:eek: I've have a lot of locks in a SQL Server, and I'd like to identify which SPs are locking what tables, I've being trying with sp_who, sp_who2 and sp_lock, so i can identify the process number, but I don't have any idea what this process is doing (which sp is running? and what command?) and which table is locked by this process, can anybody send me some querys to get this information
I'm novice in SQL Server, and I've not access to the Enterprice manager console, and I've only have priveleges to read data from the database
Thanks for your help
Alfredo
Try this link (http://vyaskn.tripod.com/fn_get_sql.htm) to see if it can help you understand what's going on. Basically, you can only see the first 255(?) characters or so of a SPID's inputbuffer unless you use this function.
However, I should note that use of this function requires that you be running SQL 2000, SP3.
Regards,
hmscott|||hmscott, many thanks for your help, but, If I understand, this procedure requires privileges to write a store procedure at the server, and I can't do that, I only have privilges to read information.
regards
Alfredo|||are you sure you have a problem? locking is part of sql. every insert, update, and delete creates a lock and there are locks of different flavors.|||I can't make that SP work, it just goes on and I get nothing displayed in QA. After a while, I press the red stop button and all I get is some code snippet from the SP itself.|||I can't make that SP work, it just goes on and I get nothing displayed in QA. After a while, I press the red stop button and all I get is some code snippet from the SP itself.
Just to clarify, I don't use the SP itself (the one in blue text at the bottom of the link that I posted earlier). I used the page as a reference on how to use fn_get_sql (which is new to SP3). Instead, you can use the SQL BOL reference for fn_get_sql instead.
My apologies, I probably should have posted the SQL BOL reference first.
Regards,
hmscott|||hmscott, many thanks for your help, but, If I understand, this procedure requires privileges to write a store procedure at the server, and I can't do that, I only have privilges to read information.
regards
Alfredo
As I mentioned in another reply, the link is principally to highlight the functionality that is available by using the new (as of SP3) internal function called fn_get_sql. Instead, try looking up fn_get_sql in the SQL BOL (make sure that you get the updated version of the BOL). In particular, there is an example at the bottom of the SQL BOL reference that shows how to use fn_get_sql with the sysprocesses table:
DECLARE @.Handle binary(20)
SELECT @.Handle = sql_handle FROM sysprocesses WHERE spid = 52
SELECT * FROM ::fn_get_sql(@.Handle)
Not meaning to be rude or presumptuous, but if you don't have rights to create an SP on the DB, can you not engage your sysadmin/dba to assist?
Regards,
hmscott|||Is there any way at all to get the full SQL without SP3 ?|||Is there any way at all to get the full SQL without SP3 ?
Not that I am aware of.
You really ought to be running SP3 anyway. Too many vulnerabilities pre-SP3.
Regards,
hmscott
I'm novice in SQL Server, and I've not access to the Enterprice manager console, and I've only have priveleges to read data from the database
Thanks for your help
Alfredo:eek: I've have a lot of locks in a SQL Server, and I'd like to identify which SPs are locking what tables, I've being trying with sp_who, sp_who2 and sp_lock, so i can identify the process number, but I don't have any idea what this process is doing (which sp is running? and what command?) and which table is locked by this process, can anybody send me some querys to get this information
I'm novice in SQL Server, and I've not access to the Enterprice manager console, and I've only have priveleges to read data from the database
Thanks for your help
Alfredo
Try this link (http://vyaskn.tripod.com/fn_get_sql.htm) to see if it can help you understand what's going on. Basically, you can only see the first 255(?) characters or so of a SPID's inputbuffer unless you use this function.
However, I should note that use of this function requires that you be running SQL 2000, SP3.
Regards,
hmscott|||hmscott, many thanks for your help, but, If I understand, this procedure requires privileges to write a store procedure at the server, and I can't do that, I only have privilges to read information.
regards
Alfredo|||are you sure you have a problem? locking is part of sql. every insert, update, and delete creates a lock and there are locks of different flavors.|||I can't make that SP work, it just goes on and I get nothing displayed in QA. After a while, I press the red stop button and all I get is some code snippet from the SP itself.|||I can't make that SP work, it just goes on and I get nothing displayed in QA. After a while, I press the red stop button and all I get is some code snippet from the SP itself.
Just to clarify, I don't use the SP itself (the one in blue text at the bottom of the link that I posted earlier). I used the page as a reference on how to use fn_get_sql (which is new to SP3). Instead, you can use the SQL BOL reference for fn_get_sql instead.
My apologies, I probably should have posted the SQL BOL reference first.
Regards,
hmscott|||hmscott, many thanks for your help, but, If I understand, this procedure requires privileges to write a store procedure at the server, and I can't do that, I only have privilges to read information.
regards
Alfredo
As I mentioned in another reply, the link is principally to highlight the functionality that is available by using the new (as of SP3) internal function called fn_get_sql. Instead, try looking up fn_get_sql in the SQL BOL (make sure that you get the updated version of the BOL). In particular, there is an example at the bottom of the SQL BOL reference that shows how to use fn_get_sql with the sysprocesses table:
DECLARE @.Handle binary(20)
SELECT @.Handle = sql_handle FROM sysprocesses WHERE spid = 52
SELECT * FROM ::fn_get_sql(@.Handle)
Not meaning to be rude or presumptuous, but if you don't have rights to create an SP on the DB, can you not engage your sysadmin/dba to assist?
Regards,
hmscott|||Is there any way at all to get the full SQL without SP3 ?|||Is there any way at all to get the full SQL without SP3 ?
Not that I am aware of.
You really ought to be running SP3 anyway. Too many vulnerabilities pre-SP3.
Regards,
hmscott
Friday, February 24, 2012
How I do this Query ?
Hello, Everyone
I have a table that hold Phone Calls Data, I store in this table the following information :
- customer name
- vendor name (i`m a thirdparty company)
- call date (ex: 7/1/2007 00:00:00)
- destination of call
- duration of call
- other information
--
I want to create table for destination only, i want to disply destination, every hour and the rest of table columns from the above table
i want one row for each destination every hourSorry. Not clear what you want.
Is this for a class assignment?
I have a table that hold Phone Calls Data, I store in this table the following information :
- customer name
- vendor name (i`m a thirdparty company)
- call date (ex: 7/1/2007 00:00:00)
- destination of call
- duration of call
- other information
--
I want to create table for destination only, i want to disply destination, every hour and the rest of table columns from the above table
i want one row for each destination every hourSorry. Not clear what you want.
Is this for a class assignment?
How i can see how many user using database?
Hello,
I want do some store procedure for see how many users using database in this exactly moment.
I want do this because i need to stop DB for execute backup or restore.
And how i can kill all conection for do this.
thanks. Sorry i am learning !I see existe sp_who and sp_who2 , how i can filter user de one db ? example only msdb
Thanks|||Try this select statement to view connected users.
select spid, loginame,b.name db_name, hostname, program_name,a.status,
login_time, last_batch, lastwaittype
from master..sysprocesses a
join master..sysdatabases b on a.dbid = b.dbid
To kill all connections at once I would set server in single user mode and kill that last connection and login by myself.
The thing is as soon as you kill all connections they still can connect back.|||Well i used one store procedure for filters users, databases etc...
But now i dont know how i can see how many users ( only number ) is connected in one database and put one button in one windows aplication for kill all users.
thanks|||I already resolve problem for kill users. Now i just know how i can put in windows aplication some messsage ( Have x users conected in db )
Subscribe to:
Posts (Atom)