how to increase tablespaces in Sql server

Can someone tell me how to increase tablespaces in SQL Server?
Is the syntax the same as Oracle? Is there a wizard in Enterprise Manager?
CheersThere is no concept of tablespaces in sql server...

well ok, if you consider creating separate file structures...then that would be close...

what are you trying to do?|||hiya

Yeh, I figured that (as in oracle you can increase the size of the tables),

The disk is 99% full (DB) and we are installing a new disk. We would like data to be written to the new disk (this is a temporary measurement until we upgrade the server) How do we set Map the data across?

Cheers|||Dump and Restore?|||The data for database files is written in parallel, while for log files sequencially.

How to Improve SQL Server performance with web application

Hi all,
I am facing some difficulties with web application performance. I have
web application in front end and SQL server 2000 Enterprise Edition in
Backend. Now , some web pages in application takes very long time
while getting data from SQL server. I want to improve the speed with
which the some web pages load.
Do anyone has idea of any third-party tools that can help me with
this situation. So, that I can constantly monitor performance of
Database with Web application.
Any help will be appriated.
Thanks in Advance.
alps
Hi
"alps" wrote:

> Hi all,
> I am facing some difficulties with web application performance. I have
> web application in front end and SQL server 2000 Enterprise Edition in
> Backend. Now , some web pages in application takes very long time
> while getting data from SQL server. I want to improve the speed with
> which the some web pages load.
> Do anyone has idea of any third-party tools that can help me with
> this situation. So, that I can constantly monitor performance of
> Database with Web application.
>
> Any help will be appriated.
> Thanks in Advance.
> alps
>
SQL Server profile will be able to monitor your database, but you may want
to control when it is running. From the profile you can analyse what queries
are running slowly. This analysis can be done by hand or using a tool such as
cleartrace http://www.cleardata.biz/cleartrace/default.aspx You should also
check for queries that return large amounts of data as this can take time to
render. Techniques such as paging may help to improve performance and only
return a manageable amount of data. You can analyses your web logs to find
pages that take a long time to load, you should be able to find free tools on
the internet that help you to do this.
Perfmon will also help you monitor system resources.
John

How to Improve SQL Server performance with web application

Hi all,
I am facing some difficulties with web application performance. I have
web application in front end and SQL server 2000 Enterprise Edition in
Backend. Now , some web pages in application takes very long time
while getting data from SQL server. I want to improve the speed with
which the some web pages load.
Do anyone has idea of any third-party tools that can help me with
this situation. So, that I can constantly monitor performance of
Database with Web application.
Any help will be appriated.
Thanks in Advance.
alpsHi
"alps" wrote:
> Hi all,
> I am facing some difficulties with web application performance. I have
> web application in front end and SQL server 2000 Enterprise Edition in
> Backend. Now , some web pages in application takes very long time
> while getting data from SQL server. I want to improve the speed with
> which the some web pages load.
> Do anyone has idea of any third-party tools that can help me with
> this situation. So, that I can constantly monitor performance of
> Database with Web application.
>
> Any help will be appriated.
> Thanks in Advance.
> alps
>
SQL Server profile will be able to monitor your database, but you may want
to control when it is running. From the profile you can analyse what queries
are running slowly. This analysis can be done by hand or using a tool such as
cleartrace http://www.cleardata.biz/cleartrace/default.aspx You should also
check for queries that return large amounts of data as this can take time to
render. Techniques such as paging may help to improve performance and only
return a manageable amount of data. You can analyses your web logs to find
pages that take a long time to load, you should be able to find free tools on
the internet that help you to do this.
Perfmon will also help you monitor system resources.
John

How to Improve SQL Server performance with web application

Hi all,
I am facing some difficulties with web application performance. I have
web application in front end and SQL server 2000 Enterprise Edition in
Backend. Now , some web pages in application takes very long time
while getting data from SQL server. I want to improve the speed with
which the some web pages load.
Do anyone has idea of any third-party tools that can help me with
this situation. So, that I can constantly monitor performance of
Database with Web application.
Any help will be appriated.
Thanks in Advance.
alpsHi
"alps" wrote:

> Hi all,
> I am facing some difficulties with web application performance. I have
> web application in front end and SQL server 2000 Enterprise Edition in
> Backend. Now , some web pages in application takes very long time
> while getting data from SQL server. I want to improve the speed with
> which the some web pages load.
> Do anyone has idea of any third-party tools that can help me with
> this situation. So, that I can constantly monitor performance of
> Database with Web application.
>
> Any help will be appriated.
> Thanks in Advance.
> alps
>
SQL Server profile will be able to monitor your database, but you may want
to control when it is running. From the profile you can analyse what queries
are running slowly. This analysis can be done by hand or using a tool such a
s
cleartrace http://www.cleardata.biz/cleartrace/default.aspx You should also
check for queries that return large amounts of data as this can take time to
render. Techniques such as paging may help to improve performance and only
return a manageable amount of data. You can analyses your web logs to find
pages that take a long time to load, you should be able to find free tools o
n
the internet that help you to do this.
Perfmon will also help you monitor system resources.
John

How to import data from MSDE to Sql Server?

I previously used MSDE as my web database, but now I've installed SQL Server Enterprise 2000 into my server. My question is how I can import data from MSDE to my new SQL server? I don't wanna lose all the data.
Thank you!Backup the database on MSDE. Restore them on the new SQL Server installation. After that, you'll want to sync up the logins. Search this site or SQLTeam.com for a script called sp_help_revlogin. You need to run that on the MSDE version, then apply the permissions to the new installation. After that you should be good to go.|||backup and restore is one of the options, but you can also just sp_detach_db/sp_attach_db

how to import an xml file to sql

I am using 2005 Enterprise and Standard. I need to know to to import an xml
file to both of these.
Thank you
Dee
Look up OPENROWSET in Books Online, specifically - look at the BULK option.
http://msdn2.microsoft.com/en-us/library/ms190312.aspx
ML
Matija Lah, SQL Server MVP
http://milambda.blogspot.com/
|||This is store data and is very large. I was hoping not to write all that
code and do not know how to execute all the applications for Bulk Load. The
xml file may need to be cleaned up as well.
Any further help will be appreciated. I do not know how to use openrowset.
Thank you
Dee
"ML" wrote:

> Look up OPENROWSET in Books Online, specifically - look at the BULK option.
> http://msdn2.microsoft.com/en-us/library/ms190312.aspx
>
> ML
> --
> Matija Lah, SQL Server MVP
> http://milambda.blogspot.com/
|||Have you tried the examples in Books Online?
You could also use SSIS to parse the XML and extract the information you
need, either way - you won't be able to avoid at least a bit of coding.
If you post a sample of the XML (not the whole thing, of course) then we can
help you come up with a more accurate solution.
ML
Matija Lah, SQL Server MVP
http://milambda.blogspot.com/
|||Here is a small example:
<Product Id="-av1011-bass">
<Code>AV1011-FS-BASS</Code>
<Description>Bass Industries Avalon [AV1011-FS-BASS]</Description>
<Url>http://www.bizchair.com/-av1011-bass.html</Url>
<Orderable>NO</Orderable>
<Taxable>NO</Taxable>
<Path>
<ProductRef Id="bass-industries"
Url="http://www.bizchair.com/bass-industries.html">Bass
Industries</ProductRef>
</Path>
<Caption><li>A blend of contemporary edge, softness and
comfort</li><li> A great synergy of design, special relationship and
function</li><li>Life time warranty on frames, springs, and moving
parts</li><li>2 year warranty</li><li><b>Come to BizChair.com for all your
Home Theater Furniture and for all your Home Furniture needs!</b></Caption>
</Product>
<Product Id="-ma1061-bass">
<Code>MA1061-FS-BASS</Code>
<Description>Bass Industries Matinee Lounger
[MA1061-FS-BASS]</Description>
<Url>http://www.bizchair.com/-ma1061-bass.html</Url>
<Orderable>NO</Orderable>
<Taxable>NO</Taxable>
<Path>
<ProductRef Id="bass-industries"
Url="http://www.bizchair.com/bass-industries.html">Bass
Industries</ProductRef>
</Path>
<Caption><li>Available in Black Leather Only</li><li>Straight Row
Only</li><li>Motorized Recline</li><li>Chaise Styling</li><li>Space
Saver</li><li>Life time warranty on all frames, springs and moving
parts</li><li>5 year warranty on leather</li><li>2 year warranty on all other
materials</li><li><b>Come to BizChair.com for all your Home Theater Furniture
and for all your Home Furniture needs!</b></Caption>
</Product>
For books online do I just look for SSIS?
Thanks
Dee
"ML" wrote:

> Have you tried the examples in Books Online?
> You could also use SSIS to parse the XML and extract the information you
> need, either way - you won't be able to avoid at least a bit of coding.
> If you post a sample of the XML (not the whole thing, of course) then we can
> help you come up with a more accurate solution.
>
> ML
> --
> Matija Lah, SQL Server MVP
> http://milambda.blogspot.com/
|||That's nice! Thanks! Now if you'd just post an example of the output you need.
Regarding SSIS - you could start with Books Online. There's also a web site
dedicated to SSIS:
http://www.sqlis.com/
ML
Matija Lah, SQL Server MVP
http://milambda.blogspot.com/
|||We were creating our store in access and we used queries. Our store was
housed on yahoo. We are getting away from the yahoo store and going to
another hosting company that supports SQL.
Here is a line from hone of our queries in access and I that they would like
for it to look the same in SQL but now in queries.
idpathnameabstractcodepricesale-pricecostmap-pricemarginship-weightavailabilityorderabletaxableleafseat-size1arm-heightback-sizecapacitydepthweightexterior-sizeinterior-sizeheightlengthwidthoverall-dimensionsdegree-of-swivelmainbridgereturnsale-price-textseat-depthseat-heightseat-thicknessseat-widthreclinerottomanshipping-infoshipping-optionscolor-optionsdimsdiameterquantity-per-cartonright-columnout-of-stockcustom-orderquickship60-day-guaranteefree-shippingis-top-sellertemplate-numberback-height-from-seatcredenzahutchcustom-optionscontentsoptionalrelated-samplesUserIDcaptionflat-ship-rate1Inside-deliveryshipping-costManufacturermpnUPCISBNASIN-Amazon
Numbercollectionoptionsdeliveryshipping-pricekeywordsproduct-typemade-inage-rangePromoFree-shipping2departmentmaterialsgenderartist-designergreen-certifiedCA
Product SummaryCA Product Short NameCA Promo
Textproduct-urlVendorCompletedUpdated
101cpu-mcoMacoMaco Side Pount CPU Holder [101CPU-MCO]Maco Side Pount CPU
Holder
[101CPU-MCO]101CPU-MCO175.0089.9987.50$0.00FALSETRUEFALSEFALSEFALSEFALSEFALSEFALSEFALSE5"<li>Under
desk side mount design is
expandable in depth only</li><li>Will fit all
brands of furniture</li><li>Keeps your CPU
elevated from dusty floors</li><li>Side
mount onl</li><li>16 gauge steel<\li><li>Perfect for school or library
settings</li><li>20"$0.00Maco Furniture101cpu$0.00steelschool
furniture, office furniture, home furnitureschool,
librarysteelFALSEhttp://www.bizchair.com/BizChair.comFALSE14-Jan-08
Thanks Dee
"ML" wrote:

> That's nice! Thanks! Now if you'd just post an example of the output you need.
> Regarding SSIS - you could start with Books Online. There's also a web site
> dedicated to SSIS:
> http://www.sqlis.com/
>
> ML
> --
> Matija Lah, SQL Server MVP
> http://milambda.blogspot.com/
|||Could you also post the table DDL as the text in your previous post didn't
quite make it accross as you'd expected.
For info on how to do that see this article:
http://www.aspfaq.com/etiquette.asp?id=5006
ML
Matija Lah, SQL Server MVP
http://milambda.blogspot.com/
|||I can't seem to get mine to work.
Thanks
Dee
"ML" wrote:

> Could you also post the table DDL as the text in your previous post didn't
> quite make it accross as you'd expected.
> For info on how to do that see this article:
> http://www.aspfaq.com/etiquette.asp?id=5006
>
> ML
> --
> Matija Lah, SQL Server MVP
> http://milambda.blogspot.com/
|||What doesn't seem to work?
Ok. I fixed your XML (it was not well-formed):
<?xml version="1.0" encoding="UTF-8"?>
<Products>
<Product Id="-av1011-bass">
<Code>AV1011-FS-BASS</Code>
<Description>Bass Industries Avalon [AV1011-FS-BASS]</Description>
<Url>http://www.bizchair.com/-av1011-bass.html</Url>
<Orderable>NO</Orderable>
<Taxable>NO</Taxable>
<Path>
<ProductRef Id="bass-industries"
Url="http://www.bizchair.com/bass-industries.html">Bass
Industries</ProductRef>
</Path>
<Caption>
<li>A blend of contemporary edge, softness and
comfort</li>
<li>A great synergy of design, special relationship and
function</li>
<li>Life time warranty on frames, springs, and moving
parts</li>
<li>2 year warranty</li>
<li>
<b>Come to BizChair.com for all your
Home Theater Furniture and for all your Home Furniture needs!</b>
</li>
</Caption>
</Product>
<Product Id="-ma1061-bass">
<Code>MA1061-FS-BASS</Code>
<Description>Bass Industries Matinee Lounger
[MA1061-FS-BASS]</Description>
<Url>http://www.bizchair.com/-ma1061-bass.html</Url>
<Orderable>NO</Orderable>
<Taxable>NO</Taxable>
<Path>
<ProductRef Id="bass-industries"
Url="http://www.bizchair.com/bass-industries.html">Bass
Industries</ProductRef>
</Path>
<Caption>
<li>Available in Black Leather Only</li>
<li>Straight Row
Only</li>
<li>Motorized Recline</li>
<li>Chaise Styling</li>
<li>Space
Saver</li>
<li>Life time warranty on all frames, springs and moving
parts</li>
<li>5 year warranty on leather</li>
<li>2 year warranty on all other
materials</li>
<li>
<b>Come to BizChair.com for all your Home Theater Furniture
and for all your Home Furniture needs!</b>
</li>
</Caption>
</Product>
</Products>
Save it to a folder (in my case that's "D:\Temp\Products.xml" - referenced
in the OPENROWSET function).
Then execute this example:
declare@.xmlvarchar(max)
declare@.xmlHandleint
-- Bulk load the xml from the file
set@.xml = (
select*
fromopenrowset
(
bulk'D:\Temp\Products.xml'
,single_blob
) Products
)
-- Prepare the DOM document
execsp_xml_preparedocument
@.xmlHandle output
,@.xml
-- Parse the XML
-- You can include this select statement in your insert statement
select*
fromopenxml
(
@.xmlHandle
,'/Products/Product'
)
with(
idvarchar(32)'@.Id'
,codevarchar(32)'Code'
,[description]varchar(1024)'Description'
,urlvarchar(1024)'Url'
,orderablevarchar(3)'NO'
,taxablevarchar(3)'NO'
,[path]varchar(1024)'Path'
,captionvarchar(1024)'Caption'
)
-- Never forget to remove the DOM document as soon as you're done using it.
execsp_xml_removedocument
@.xmlHandle
go
Let us know how you get along.
ML
Matija Lah, SQL Server MVP
http://milambda.blogspot.com/

How to identify which version we have?

How do I tell if I am running SQL2K Enterprise Edition or Standard Edition? We have a 2-node cluster. Does that imply Enterprise Edition, or could Standard do that as well? Thanks for any info.

If it's SQL 2000, only Enterprise is supported in a cluster configuration...if SQL 2005, Standard is also supported.

You can run the following to find your the edition of the server you are running...just connect via Query Analyzer to the instance you want to verify and execute:

select SERVERPROPERTY('Edition')

|||Thanks, that was very helpful. We are kicking around upgrade questions. The price for 2005 Enterprise is very much higher than Standard, but because we have 2000 Enterprise we were wondering if we needed that version in 2005 flavor as well. It looks like, with just a dual-node cluster, 2005 Standard fills the functionality that 2000 Enterprise had at least for clustering. Of course we don't get online recovery, online indexing, etc, but at least we could move to SQL 2005 Standard, get similar clustering functionality to what we have now and save $50K. That would take a very big bite out of our budget.

How to identify which edition im i running?

Good day,
I would like to identify which EDITION of reporting services im running,
enterprise or standard. I know that you can check the version by going to
http://localhost/reportserver. but I want to know the edition.
thanks in advance
Oren ZipporiHi Oren,
The edition you are running is similar to the edition of SQL Server ou
are running it on.
I=2Ee., if you are running SQL Server Standard Edition, then your
Reporting Services Edition is Standard.
Kind regards,
Bj=F6rn|||Hello Oren,
You can do it programmatically like this using the webservice API:
ReportingService2005 rs = new ReportingService2005();
rs.Credentials = System.Net.CredentialCache.DefaultCredentials;
rs.ServerInfoHeaderValue = new ServerInfoHeader();
rs.ListChildren("/", false);
Console.WriteLine(rs.ServerInfoHeaderValue.ReportServerVersion);
Console.WriteLine(rs.ServerInfoHeaderValue.ReportServerEdition);
Console.WriteLine(rs.ServerInfoHeaderValue.ReportServerVersionNumber);
-Chris
> Good day,
> I would like to identify which EDITION of reporting services im
> running,
> enterprise or standard. I know that you can check the version by going
> to
> http://localhost/reportserver. but I want to know the edition.
> thanks in advance
> Oren Zippori

How to Identify user defined data types

How can you tell which datatypes in a given database are user defined
(with a query, not by looking in Enterprise Manager)? This is for SQL
Server 2000.SELECT domain_name
FROM information_schema.domains

--
David Portas
SQL Server MVP
--|||Bruce (sandell@.pacbell.net) writes:
> How can you tell which datatypes in a given database are user defined
> (with a query, not by looking in Enterprise Manager)? This is for SQL
> Server 2000.

SELECT * FROM systypes WHERE xusertype > 255

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp

How to identify the Reporting Services Edition

Hi all, I need to know the reporting services, if it is Enterprise, Standard
or Developer. I found only documents that tell me how to identify the
version, but not the edition.
Anyone knows how to do this'
Thank you very much!!!
Alexandre Calderaro
Avanade Italy
MSCDBASorry,
After post the question I found the answer in the most obvious place:
Control Panel, Add Remove Programs...
Anyway
Thanks
"Alex" wrote:
> Hi all, I need to know the reporting services, if it is Enterprise, Standard
> or Developer. I found only documents that tell me how to identify the
> version, but not the edition.
> Anyone knows how to do this'
> Thank you very much!!!
> Alexandre Calderaro
> Avanade Italy
> MSCDBA
>|||For 2000, the version information is located at the bottom of the main page
of your Report Server
RTM: 8.00.743.00
SP1 : 8.00.878.00
SP2 : 8.00.1038.00
The edition information is stored in the following registry key:
HKLM\SOFTWARE\Microsoft\Microsoft SQL Server\80\Reporting
Services\ProductCode
Standard: {B19FEFE7-069D-4FC4-8FDF-19661EAB6CE4}
Enterprise: {33FE9EED-1976-4A51-A7AF-332D9BBB9400}
Developer: {2879CA50-1599-4F4B-B9EC-1110C1094C16}
Evaluation: {7C93251A-BFB4-4EB8-A57C-81B875BB12E4}
Med Bouchenafa
"Alex" <Alex@.discussions.microsoft.com> a écrit dans le message de news:
92C3C825-D8BB-43C5-9342-F4807D12D157@.microsoft.com...
> Hi all, I need to know the reporting services, if it is Enterprise,
> Standard
> or Developer. I found only documents that tell me how to identify the
> version, but not the edition.
> Anyone knows how to do this'
> Thank you very much!!!
> Alexandre Calderaro
> Avanade Italy
> MSCDBA
>

how to Identify a rogue SQL process on a server

Hi,
Is there a way in ISQL or in SQL enterprise manager to isolate a SQL PID
which is hammering the server.
Every now and then, our DB server get hammered (4* CPUs running at over 80%
usage), and I can see a list of process in management\process info. but it
shows the total cpu counter rather then the process that hammering the
server now.
Is there a way to identify which SQL process is hammering the server'
Windows 2000 Sp4 / SQL 2000 SP3a
Kind regards,
Jeremy Byrski
www.CentralR.comEXEC sp_who2
"news.microsoft.com" <jeremy.byrski@.No.Spam.CentralR.com> wrote in message
news:O6EQC9AJFHA.732@.TK2MSFTNGP12.phx.gbl...
> Hi,
>
> Is there a way in ISQL or in SQL enterprise manager to isolate a SQL PID
> which is hammering the server.
>
> Every now and then, our DB server get hammered (4* CPUs running at over
80%
> usage), and I can see a list of process in management\process info. but
it
> shows the total cpu counter rather then the process that hammering the
> server now.
>
> Is there a way to identify which SQL process is hammering the server'
>
> Windows 2000 Sp4 / SQL 2000 SP3a
>
> Kind regards,
> Jeremy Byrski
> www.CentralR.com
>
>
>|||you can use sp_who2 to see what activity is going on, or use enterprise
manager, under "Management" > "Current Activity"
Simon Worth
"news.microsoft.com" <jeremy.byrski@.No.Spam.CentralR.com> wrote in message
news:O6EQC9AJFHA.732@.TK2MSFTNGP12.phx.gbl...
> Hi,
>
> Is there a way in ISQL or in SQL enterprise manager to isolate a SQL PID
> which is hammering the server.
>
> Every now and then, our DB server get hammered (4* CPUs running at over
80%
> usage), and I can see a list of process in management\process info. but
it
> shows the total cpu counter rather then the process that hammering the
> server now.
>
> Is there a way to identify which SQL process is hammering the server'
>
> Windows 2000 Sp4 / SQL 2000 SP3a
>
> Kind regards,
> Jeremy Byrski
> www.CentralR.com
>
>
>|||You can also use PerfMon and sysprocesses to find the spid which is using up
a lot of CPU time. Find the highest Thread<sqlservr#<instance> )\%Processor
Time counter in Perfmon and match it to Thread(sqlservr#<instance> )\ID
Thread, which corresponds to the kpid column in sysprocesses. Use that
information to run dbcc inputbuffer(<spid> ). More information is available
at http://support.microsoft.com/defaul...b;en-us;117559.
Adrian
"news.microsoft.com" <jeremy.byrski@.No.Spam.CentralR.com> wrote in message
news:O6EQC9AJFHA.732@.TK2MSFTNGP12.phx.gbl...
> Hi,
>
> Is there a way in ISQL or in SQL enterprise manager to isolate a SQL PID
> which is hammering the server.
>
> Every now and then, our DB server get hammered (4* CPUs running at over
> 80% usage), and I can see a list of process in management\process info.
> but it shows the total cpu counter rather then the process that hammering
> the server now.
>
> Is there a way to identify which SQL process is hammering the server'
>
> Windows 2000 Sp4 / SQL 2000 SP3a
>
> Kind regards,
> Jeremy Byrski
> www.CentralR.com
>
>
>|||Hi Simon, and Aeron
I did try that, but it seems the display the Total CPU time since maybe the
SQL service has started.
I'm looking to identify a SQL Process ID that it taking a lot of CPU usage n
ow... in realtime, so that we can identify the query that is causing the hig
h CPU usage on the system in a snapshot of time.
Kind regards,
Jeremy Byrski
www.CentralR.com
"Simon Worth" <REMOVEFIRST_simon.worth@.gmail.com> wrote in message news:Oa0LGBBJFHA.4060@.TK2
MSFTNGP14.phx.gbl...
> you can use sp_who2 to see what activity is going on, or use enterprise
> manager, under "Management" > "Current Activity"
>
> --
> Simon Worth
>
>
> "news.microsoft.com" <jeremy.byrski@.No.Spam.CentralR.com> wrote in message
> news:O6EQC9AJFHA.732@.TK2MSFTNGP12.phx.gbl...
> 80%
> it
>
>|||Hi Aidan.
I cant spot which performance object which sas the sql threads listed...
could you point me in the right direction...'
Thanks a million,
Kind regards,
Jeremy Byrski
www.CentralR.com
"Adrian Zajkeskovic" <nospam@.rogers.com> wrote in message
news:AqCdnRvv_Y7aw7PfRVn-iQ@.rogers.com...
> You can also use PerfMon and sysprocesses to find the spid which is using
> up a lot of CPU time. Find the highest
> Thread<sqlservr#<instance> )\%Processor Time counter in Perfmon and match
> it to Thread(sqlservr#<instance> )\ID Thread, which corresponds to the kpid
> column in sysprocesses. Use that information to run dbcc
> inputbuffer(<spid> ). More information is available at
> http://support.microsoft.com/defaul...b;en-us;117559.
> Adrian
>
>
> "news.microsoft.com" <jeremy.byrski@.No.Spam.CentralR.com> wrote in message
> news:O6EQC9AJFHA.732@.TK2MSFTNGP12.phx.gbl...
>|||Wow Adrian - that' s pretty cool. Got any more tips like that one?
Steve.
"Adrian Zajkeskovic" <nospam@.rogers.com> wrote in message
news:AqCdnRvv_Y7aw7PfRVn-iQ@.rogers.com...
> You can also use PerfMon and sysprocesses to find the spid which is using
up
> a lot of CPU time. Find the highest Thread<sqlservr#<instance> )\%Processor
> Time counter in Perfmon and match it to Thread(sqlservr#<instance> )\ID
> Thread, which corresponds to the kpid column in sysprocesses. Use that
> information to run dbcc inputbuffer(<spid> ). More information is available
> at http://support.microsoft.com/defaul...b;en-us;117559.
> Adrian
>
>
> "news.microsoft.com" <jeremy.byrski@.No.Spam.CentralR.com> wrote in message
> news:O6EQC9AJFHA.732@.TK2MSFTNGP12.phx.gbl...
hammering[vbcol=seagreen]
>

how to Identify a rogue SQL process on a server

Hi,
Is there a way in ISQL or in SQL enterprise manager to isolate a SQL PID
which is hammering the server.
Every now and then, our DB server get hammered (4* CPUs running at over 80%
usage), and I can see a list of process in management\process info. but it
shows the total cpu counter rather then the process that hammering the
server now.
Is there a way to identify which SQL process is hammering the server'
Windows 2000 Sp4 / SQL 2000 SP3a
Kind regards,
Jeremy Byrski
www.CentralR.comEXEC sp_who2
"news.microsoft.com" <jeremy.byrski@.No.Spam.CentralR.com> wrote in message
news:O6EQC9AJFHA.732@.TK2MSFTNGP12.phx.gbl...
> Hi,
>
> Is there a way in ISQL or in SQL enterprise manager to isolate a SQL PID
> which is hammering the server.
>
> Every now and then, our DB server get hammered (4* CPUs running at over
80%
> usage), and I can see a list of process in management\process info. but
it
> shows the total cpu counter rather then the process that hammering the
> server now.
>
> Is there a way to identify which SQL process is hammering the server'
>
> Windows 2000 Sp4 / SQL 2000 SP3a
>
> Kind regards,
> Jeremy Byrski
> www.CentralR.com
>
>
>|||you can use sp_who2 to see what activity is going on, or use enterprise
manager, under "Management" > "Current Activity"
--
Simon Worth
"news.microsoft.com" <jeremy.byrski@.No.Spam.CentralR.com> wrote in message
news:O6EQC9AJFHA.732@.TK2MSFTNGP12.phx.gbl...
> Hi,
>
> Is there a way in ISQL or in SQL enterprise manager to isolate a SQL PID
> which is hammering the server.
>
> Every now and then, our DB server get hammered (4* CPUs running at over
80%
> usage), and I can see a list of process in management\process info. but
it
> shows the total cpu counter rather then the process that hammering the
> server now.
>
> Is there a way to identify which SQL process is hammering the server'
>
> Windows 2000 Sp4 / SQL 2000 SP3a
>
> Kind regards,
> Jeremy Byrski
> www.CentralR.com
>
>
>|||You can also use PerfMon and sysprocesses to find the spid which is using up
a lot of CPU time. Find the highest Thread<sqlservr#<instance>)\%Processor
Time counter in Perfmon and match it to Thread(sqlservr#<instance>)\ID
Thread, which corresponds to the kpid column in sysprocesses. Use that
information to run dbcc inputbuffer(<spid>). More information is available
at http://support.microsoft.com/default.aspx?scid=kb;en-us;117559.
Adrian
"news.microsoft.com" <jeremy.byrski@.No.Spam.CentralR.com> wrote in message
news:O6EQC9AJFHA.732@.TK2MSFTNGP12.phx.gbl...
> Hi,
>
> Is there a way in ISQL or in SQL enterprise manager to isolate a SQL PID
> which is hammering the server.
>
> Every now and then, our DB server get hammered (4* CPUs running at over
> 80% usage), and I can see a list of process in management\process info.
> but it shows the total cpu counter rather then the process that hammering
> the server now.
>
> Is there a way to identify which SQL process is hammering the server'
>
> Windows 2000 Sp4 / SQL 2000 SP3a
>
> Kind regards,
> Jeremy Byrski
> www.CentralR.com
>
>
>|||This is a multi-part message in MIME format.
--=_NextPart_000_0029_01C524D6.7E3E2390
Content-Type: text/plain;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
Hi Simon, and Aeron

I did try that, but it seems the display the Total CPU time since maybe =the SQL service has started.

I'm looking to identify a SQL Process ID that it taking a lot of CPU =usage now... in realtime, so that we can identify the query that is =causing the high CPU usage on the system in a snapshot of time.

Kind regards,
Jeremy Byrski
www.CentralR.com
"Simon Worth" <REMOVEFIRST_simon.worth@.gmail.com> wrote in message =news:Oa0LGBBJFHA.4060@.TK2MSFTNGP14.phx.gbl...
> you can use sp_who2 to see what activity is going on, or use =enterprise
> manager, under "Management" > "Current Activity"
> > -- > Simon Worth
> > > "news.microsoft.com" <jeremy.byrski@.No.Spam.CentralR.com> wrote in =message
> news:O6EQC9AJFHA.732@.TK2MSFTNGP12.phx.gbl...
>> Hi,
>>
>> Is there a way in ISQL or in SQL enterprise manager to isolate a SQL =PID
>> which is hammering the server.
>>
>> Every now and then, our DB server get hammered (4* CPUs running at =over
> 80%
>> usage), and I can see a list of process in management\process info. =but
> it
>> shows the total cpu counter rather then the process that hammering =the
>> server now.
>>
>> Is there a way to identify which SQL process is hammering the =server'
>>
>> Windows 2000 Sp4 / SQL 2000 SP3a
>>
>> Kind regards,
>> Jeremy Byrski
>> www.CentralR.com
>>
>>
> >
--=_NextPart_000_0029_01C524D6.7E3E2390
Content-Type: text/html;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
&

---

Hi Simon, and Aeron
I did try that, but it =seems the display the Total CPU time since maybe the SQL service has started.
I'm looking to identify a =SQL Process ID that it taking a lot of CPU usage now... in realtime, so that =we can identify the query that is causing the high CPU usage on the system in a =snapshot of time.
Kind =regards,Jeremy Byrskiwww.CentralR.com
"Simon Worth" wrote in message news:Oa0LGBBJFHA.4060@.TK2MSFTNGP14.phx.gbl...> you can use =sp_who2 to see what activity is going on, or use enterprise> manager, under ="Management" > "Current Activity"> > -- > Simon Worth> => > "news.microsoft.com" wrote in message> news:O6EQC9AJFHA.732@.TK2MSFTNGP12.phx.gbl...> =Hi,>>>> Is there a way in ISQL or in SQL enterprise manager to isolate a SQL PID> which is hammering the server.>>>> Every now and =then, our DB server get hammered (4* CPUs running at over> 80%> =usage), and I can see a list of process in management\process info. =but> it> shows the total cpu counter rather then the process that hammering the> server now.>>>> Is there a way to =identify which SQL process is hammering the server'>>>> Windows 2000 =Sp4 / SQL 2000 SP3a>>>> Kind regards,>> Jeremy Byrski>> =www.CentralR.com>>>>>>= >

---

--=_NextPart_000_0029_01C524D6.7E3E2390--|||Hi Aidan.
I cant spot which performance object which sas the sql threads listed...
could you point me in the right direction...'
Thanks a million,
Kind regards,
Jeremy Byrski
www.CentralR.com
"Adrian Zajkeskovic" <nospam@.rogers.com> wrote in message
news:AqCdnRvv_Y7aw7PfRVn-iQ@.rogers.com...
> You can also use PerfMon and sysprocesses to find the spid which is using
> up a lot of CPU time. Find the highest
> Thread<sqlservr#<instance>)\%Processor Time counter in Perfmon and match
> it to Thread(sqlservr#<instance>)\ID Thread, which corresponds to the kpid
> column in sysprocesses. Use that information to run dbcc
> inputbuffer(<spid>). More information is available at
> http://support.microsoft.com/default.aspx?scid=kb;en-us;117559.
> Adrian
>
>
> "news.microsoft.com" <jeremy.byrski@.No.Spam.CentralR.com> wrote in message
> news:O6EQC9AJFHA.732@.TK2MSFTNGP12.phx.gbl...
>> Hi,
>>
>> Is there a way in ISQL or in SQL enterprise manager to isolate a SQL PID
>> which is hammering the server.
>>
>> Every now and then, our DB server get hammered (4* CPUs running at over
>> 80% usage), and I can see a list of process in management\process info.
>> but it shows the total cpu counter rather then the process that hammering
>> the server now.
>>
>> Is there a way to identify which SQL process is hammering the server'
>>
>> Windows 2000 Sp4 / SQL 2000 SP3a
>>
>> Kind regards,
>> Jeremy Byrski
>> www.CentralR.com
>>
>>
>|||Wow Adrian - that' s pretty cool. Got any more tips like that one?
Steve.
"Adrian Zajkeskovic" <nospam@.rogers.com> wrote in message
news:AqCdnRvv_Y7aw7PfRVn-iQ@.rogers.com...
> You can also use PerfMon and sysprocesses to find the spid which is using
up
> a lot of CPU time. Find the highest Thread<sqlservr#<instance>)\%Processor
> Time counter in Perfmon and match it to Thread(sqlservr#<instance>)\ID
> Thread, which corresponds to the kpid column in sysprocesses. Use that
> information to run dbcc inputbuffer(<spid>). More information is available
> at http://support.microsoft.com/default.aspx?scid=kb;en-us;117559.
> Adrian
>
>
> "news.microsoft.com" <jeremy.byrski@.No.Spam.CentralR.com> wrote in message
> news:O6EQC9AJFHA.732@.TK2MSFTNGP12.phx.gbl...
> > Hi,
> >
> >
> >
> > Is there a way in ISQL or in SQL enterprise manager to isolate a SQL PID
> > which is hammering the server.
> >
> >
> >
> > Every now and then, our DB server get hammered (4* CPUs running at over
> > 80% usage), and I can see a list of process in management\process info.
> > but it shows the total cpu counter rather then the process that
hammering
> > the server now.
> >
> >
> >
> > Is there a way to identify which SQL process is hammering the server'
> >
> >
> >
> > Windows 2000 Sp4 / SQL 2000 SP3a
> >
> >
> >
> > Kind regards,
> >
> > Jeremy Byrski
> >
> > www.CentralR.com
> >
> >
> >
> >
> >
>

how to Identify a rogue SQL process on a server

Hi,
Is there a way in ISQL or in SQL enterprise manager to isolate a SQL PID
which is hammering the server.
Every now and then, our DB server get hammered (4* CPUs running at over 80%
usage), and I can see a list of process in management\process info. but it
shows the total cpu counter rather then the process that hammering the
server now.
Is there a way to identify which SQL process is hammering the server?
Windows 2000 Sp4 / SQL 2000 SP3a
Kind regards,
Jeremy Byrski
www.CentralR.com
EXEC sp_who2
"news.microsoft.com" <jeremy.byrski@.No.Spam.CentralR.com> wrote in message
news:O6EQC9AJFHA.732@.TK2MSFTNGP12.phx.gbl...
> Hi,
>
> Is there a way in ISQL or in SQL enterprise manager to isolate a SQL PID
> which is hammering the server.
>
> Every now and then, our DB server get hammered (4* CPUs running at over
80%
> usage), and I can see a list of process in management\process info. but
it
> shows the total cpu counter rather then the process that hammering the
> server now.
>
> Is there a way to identify which SQL process is hammering the server?
>
> Windows 2000 Sp4 / SQL 2000 SP3a
>
> Kind regards,
> Jeremy Byrski
> www.CentralR.com
>
>
>
|||you can use sp_who2 to see what activity is going on, or use enterprise
manager, under "Management" > "Current Activity"
Simon Worth
"news.microsoft.com" <jeremy.byrski@.No.Spam.CentralR.com> wrote in message
news:O6EQC9AJFHA.732@.TK2MSFTNGP12.phx.gbl...
> Hi,
>
> Is there a way in ISQL or in SQL enterprise manager to isolate a SQL PID
> which is hammering the server.
>
> Every now and then, our DB server get hammered (4* CPUs running at over
80%
> usage), and I can see a list of process in management\process info. but
it
> shows the total cpu counter rather then the process that hammering the
> server now.
>
> Is there a way to identify which SQL process is hammering the server?
>
> Windows 2000 Sp4 / SQL 2000 SP3a
>
> Kind regards,
> Jeremy Byrski
> www.CentralR.com
>
>
>
|||You can also use PerfMon and sysprocesses to find the spid which is using up
a lot of CPU time. Find the highest Thread<sqlservr#<instance>)\%Processor
Time counter in Perfmon and match it to Thread(sqlservr#<instance>)\ID
Thread, which corresponds to the kpid column in sysprocesses. Use that
information to run dbcc inputbuffer(<spid>). More information is available
at http://support.microsoft.com/default...;en-us;117559.
Adrian
"news.microsoft.com" <jeremy.byrski@.No.Spam.CentralR.com> wrote in message
news:O6EQC9AJFHA.732@.TK2MSFTNGP12.phx.gbl...
> Hi,
>
> Is there a way in ISQL or in SQL enterprise manager to isolate a SQL PID
> which is hammering the server.
>
> Every now and then, our DB server get hammered (4* CPUs running at over
> 80% usage), and I can see a list of process in management\process info.
> but it shows the total cpu counter rather then the process that hammering
> the server now.
>
> Is there a way to identify which SQL process is hammering the server?
>
> Windows 2000 Sp4 / SQL 2000 SP3a
>
> Kind regards,
> Jeremy Byrski
> www.CentralR.com
>
>
>
|||Hi Simon, and Aeron
I did try that, but it seems the display the Total CPU time since maybe the SQL service has started.
I'm looking to identify a SQL Process ID that it taking a lot of CPU usage now... in realtime, so that we can identify the query that is causing the high CPU usage on the system in a snapshot of time.
Kind regards,
Jeremy Byrski
www.CentralR.com
"Simon Worth" <REMOVEFIRST_simon.worth@.gmail.com> wrote in message news:Oa0LGBBJFHA.4060@.TK2MSFTNGP14.phx.gbl...
> you can use sp_who2 to see what activity is going on, or use enterprise
> manager, under "Management" > "Current Activity"
> --
> Simon Worth
>
> "news.microsoft.com" <jeremy.byrski@.No.Spam.CentralR.com> wrote in message
> news:O6EQC9AJFHA.732@.TK2MSFTNGP12.phx.gbl...
> 80%
> it
>
|||Hi Aidan.
I cant spot which performance object which sas the sql threads listed...
could you point me in the right direction...?
Thanks a million,
Kind regards,
Jeremy Byrski
www.CentralR.com
"Adrian Zajkeskovic" <nospam@.rogers.com> wrote in message
news:AqCdnRvv_Y7aw7PfRVn-iQ@.rogers.com...
> You can also use PerfMon and sysprocesses to find the spid which is using
> up a lot of CPU time. Find the highest
> Thread<sqlservr#<instance>)\%Processor Time counter in Perfmon and match
> it to Thread(sqlservr#<instance>)\ID Thread, which corresponds to the kpid
> column in sysprocesses. Use that information to run dbcc
> inputbuffer(<spid>). More information is available at
> http://support.microsoft.com/default...;en-us;117559.
> Adrian
>
>
> "news.microsoft.com" <jeremy.byrski@.No.Spam.CentralR.com> wrote in message
> news:O6EQC9AJFHA.732@.TK2MSFTNGP12.phx.gbl...
>
|||Wow Adrian - that' s pretty cool. Got any more tips like that one?
Steve.
"Adrian Zajkeskovic" <nospam@.rogers.com> wrote in message
news:AqCdnRvv_Y7aw7PfRVn-iQ@.rogers.com...
> You can also use PerfMon and sysprocesses to find the spid which is using
up[vbcol=seagreen]
> a lot of CPU time. Find the highest Thread<sqlservr#<instance>)\%Processor
> Time counter in Perfmon and match it to Thread(sqlservr#<instance>)\ID
> Thread, which corresponds to the kpid column in sysprocesses. Use that
> information to run dbcc inputbuffer(<spid>). More information is available
> at http://support.microsoft.com/default...;en-us;117559.
> Adrian
>
>
> "news.microsoft.com" <jeremy.byrski@.No.Spam.CentralR.com> wrote in message
> news:O6EQC9AJFHA.732@.TK2MSFTNGP12.phx.gbl...
hammering
>

How to hide table columns for users without permission in SQL Tool

Hi,
I already set select permission to certain columns in a table for a user.
BUt the user still can view all the columns in the Enterprise Manager/ SQL
Mgt Studio. HOw to hide those columns without permission ?
Hi
If restrict access to the table by use stored procedures then the user does
not need permissions on the table directly. You can also be granular to
column level what permissions are granted.
Not giving them access to Enterprise Manager may also be an idea.
John
"Wan" wrote:

> Hi,
> I already set select permission to certain columns in a table for a user.
> BUt the user still can view all the columns in the Enterprise Manager/ SQL
> Mgt Studio. HOw to hide those columns without permission ?
|||Hi
Just to add, you may not exclude the user seeing the existance of the column
even if you have denied them permissions to see the data.
John
"John Bell" wrote:
[vbcol=seagreen]
> Hi
> If restrict access to the table by use stored procedures then the user does
> not need permissions on the table directly. You can also be granular to
> column level what permissions are granted.
> Not giving them access to Enterprise Manager may also be an idea.
> John
> "Wan" wrote:

How to hide table columns for users without permission in SQL Tool

Hi,
I already set select permission to certain columns in a table for a user.
BUt the user still can view all the columns in the Enterprise Manager/ SQL
Mgt Studio. HOw to hide those columns without permission ?Hi
If restrict access to the table by use stored procedures then the user does
not need permissions on the table directly. You can also be granular to
column level what permissions are granted.
Not giving them access to Enterprise Manager may also be an idea.
John
"Wan" wrote:
> Hi,
> I already set select permission to certain columns in a table for a user.
> BUt the user still can view all the columns in the Enterprise Manager/ SQL
> Mgt Studio. HOw to hide those columns without permission ?|||Hi
Just to add, you may not exclude the user seeing the existance of the column
even if you have denied them permissions to see the data.
John
"John Bell" wrote:
> Hi
> If restrict access to the table by use stored procedures then the user does
> not need permissions on the table directly. You can also be granular to
> column level what permissions are granted.
> Not giving them access to Enterprise Manager may also be an idea.
> John
> "Wan" wrote:
> > Hi,
> > I already set select permission to certain columns in a table for a user.
> > BUt the user still can view all the columns in the Enterprise Manager/ SQL
> > Mgt Studio. HOw to hide those columns without permission ?

How to hide table columns for users without permission in SQL Tool

Hi,
I already set select permission to certain columns in a table for a user.
BUt the user still can view all the columns in the Enterprise Manager/ SQL
Mgt Studio. HOw to hide those columns without permission ?Hi
If restrict access to the table by use stored procedures then the user does
not need permissions on the table directly. You can also be granular to
column level what permissions are granted.
Not giving them access to Enterprise Manager may also be an idea.
John
"Wan" wrote:

> Hi,
> I already set select permission to certain columns in a table for a user.
> BUt the user still can view all the columns in the Enterprise Manager/ SQ
L
> Mgt Studio. HOw to hide those columns without permission ?