Create and Edit flat file from Sqlserver (SP).

HI All,
I use sqlserver2000 server.
I have a requirement to create a flat file(.txt) and dump the data into the file with some formatting.
I tried to use DTS but,
1. it doesnt allow me to put one row information of table to multiple line in the flat file.
2. Dynamically we cannot create n number of files we need from DTS.

Now, i am trying to create a file from sql server Stored Procedure and write data into it.

Can any one help me..
How to create and write to file from sqlserver (sp) .

Regards

Abdul lateef.Originally posted by a_lateef

I tried to use DTS but,
1. it doesnt allow me to put one row information of table to multiple line in the flat file.
2. Dynamically we cannot create n number of files we need from DTS.


Maybe if you add a ActiveX script step in the DTS package. Hope someone else can explain further...|||Greetings!

I personally would just write a script (pick a language of choice) to pull the data from the server, format it and then write it to a textfile. Although there are ways to do it in SQL Server it is very cumbersome and I think you will find that just about any other language will offer a lot more flexibility in this case. If you use something like a Visual Basic Script file you could then just set it up as a job to run when it was supposed to.

Maybe someone else could offer something else on this, good luck!

HTH!|||I am unclear as to what you want in the text file - could you explain in detail (what are your requirements) - maybe with an example.

Create a new maintenance plan

I have installed SQLServer 2005 from Small Business Server premium edition. The package has been upgraded to SP2 and all the fixes have been applied.

I want to create a new maintenance plan to automate my DB backup procedures using SQL Server Management Studio. I start Management Studio and right click on the management folder and I I see is "Refresh" there is no "Create New Maintenance" plan presented.

I have tried this using the sa built-in account and the server administrator account both of which have sysadmin priviledges. I have also tried it from a client machine with another sysadmin userid but they all give the same results.

I checked to see if SSIS was installed. The add/remove programs wizard for SQL Server shows Integration Services SP2 is installed but I went ahead anyway and re-installed without error.

It seems that either SSIS is not running or I my user roles are preventing the maintenance wizard from running. Is there some way that I can tell if, in fact, SSIS is properly installed and running?

Thanks.

To create or manage maintenance plans, you must be a member of the sysadmin fixed server role. Note that Object Explorer only displays maintenance plans if the user is a member of the sysadmin fixed server role.

See http://technet.microsoft.com/en-us/library/ms189036(SQL.90).aspxsql

Create a new maintenance plan

I have installed SQLServer 2005 from Small Business Server premium edition. The package has been upgraded to SP2 and all the fixes have been applied.

I want to create a new maintenance plan to automate my DB backup procedures using SQL Server Management Studio. I start Management Studio and right click on the management folder and I I see is "Refresh" there is no "Create New Maintenance" plan presented.

I have tried this using the sa built-in account and the server administrator account both of which have sysadmin priviledges. I have also tried it from a client machine with another sysadmin userid but they all give the same results.

I checked to see if SSIS was installed. The add/remove programs wizard for SQL Server shows Integration Services SP2 is installed but I went ahead anyway and re-installed without error.

It seems that either SSIS is not running or I my user roles are preventing the maintenance wizard from running. Is there some way that I can tell if, in fact, SSIS is properly installed and running?

Thanks.

To create or manage maintenance plans, you must be a member of the sysadmin fixed server role. Note that Object Explorer only displays maintenance plans if the user is a member of the sysadmin fixed server role.

See http://technet.microsoft.com/en-us/library/ms189036(SQL.90).aspx

Create a Mobile database on desktop

I have searched through this forum and the news group

microsoft.public.sqlserver.ce

for this topic. I only find a post from Darren Shaffer and I requested a document.

Unfortunately, there is not a clear example of how to create a mobile database (sdf) on a desktop machine which contains VS 2005 and SQL server 2005 by using Visual Basic 2005.

I am sure a clear example in VB for this problem would help a lot of people. Hence anybody who knows how to perform this task, Please help these people.

Thanks in advance

TalatAfter a little bit of search, I succeeded! Here is the guidelines for using an SQL server mobile dataset residing in your desktop computer on your desjktop (populating a SQL Server mobile database on your desktop). At the end of the guidelines, I give a sample program to populate an sqlce database from a text file.

I hope you will find these guidelines and sample program usefull.

You may contact me at toncu@.nospam.e-kolay.net (remove nospam for actual address)

1. create a Windowsapplication project
File->New Project
Choose Windows in Project Types and Windows Apllication from Templates

2. Insert a reference to SQL Server Mobile
Project->Add Reference->Browse
c:\Program Files\Microsoft Visual Studio 8
\Common7\IDE\System.Data.SqlServerCE.DLL

3. Insert
imports system.data.sqlserverce
at the top of your form file

4. Write your application just as you would as if your project was a Smartdeviceapplication.

i.e. use sqlconnection and other constructs.

A sample application is as follows:
(dbtableexist is a subroutine which shows how to process the SCHEMA.
In this subroutine, change TABLES to INDEXES and TABLE_NAME to INDEX_NAME to search for an index)

Imports system.data.SqlServerCe
Imports System.IO
Public Sub createaccessfile(ByVal a$)
'curdbpath is the path of the database e.g. d:\my sql databases\
Dim connStr As String = "Data Source = " & curdbpath & a$ & ".sdf"
Dim conn As SqlCeConnection = Nothing
Dim command As SqlCeCommand
Try
conn = New SqlCeConnection(connStr)
conn.Open()
command = conn.CreateCommand()
If dbtableexist(conn, "ACCESSFILE") Then
command.CommandText = "DROP TABLE ACCESSFILE"
command.ExecuteNonQuery()
End If
command.CommandText = "CREATE TABLE ACCESSFILE ( code nchar(20),field nchar(3),totalrecs nvarchar(255),indexbits ntext)"
command.ExecuteNonQuery()
command.CommandText = "CREATE INDEX codeindex on ACCESSFILE (field,code)"
command.ExecuteNonQuery()
Catch ex As SqlCeException
Dim m1 = ex.Message
Dim m2 = ex.Source
Dim m3 = ex.NativeError
MsgBox(m1 & m2 & m3, , "open " & a$ & " for accessfile creation")
Exit Sub
End Try

' Create and prepare a SQL statement.
command.CommandText = " insert into accessfile values (?,?,?,?);"

' Read and insert the lines from the file until the end
' of the file is reached.
Dim accfname As String = curdbpath & a$ & "_accessfile.inp"
Dim fs As FileStream = New FileStream(accfname, FileMode.Open)
Dim sr As New StreamReader(fs)
Dim line As String, codestr As String, fieldstr As String, totalrecsstr As String, indexbitsstr As String
Dim line1 As String, flagstr As String, field As Integer

line = sr.ReadLine : line1 = line
Dim currec As Integer = 0
' Note: Even though named parameterized queries are not supported, we still need
' to provide unique parameter names so that we can manipulate parameter collection;
command.Parameters.Add("@.code", "a")
command.Parameters.Add("@.field", 1)
command.Parameters.Add("@.totrecs", "c")
command.Parameters.Add("@.indexbits", "d")
Do
currec = currec + 1
If currec Mod 10 = 0 Then dispcaption(Str(currec))
flagstr = before(line, Chr(9)) : line = after(line, Chr(9))
codestr = before(line, Chr(9)) : line = after(line, Chr(9))
field = Val(before(line, Chr(9))) : line = after(line, Chr(9))
totalrecsstr = before(line, Chr(9)) : line = after(line, Chr(9))
indexbitsstr = line
Try
command.Parameters(0).Value = codestr
command.Parameters(1).Value = Format(field, "000")
command.Parameters(2).Value = totalrecsstr
command.Parameters(3).Value = indexbitsstr
' Calling Prepare after having set the Commandtext and parameters.
command.Prepare()
command.ExecuteNonQuery()
Catch ex As SqlCeException
Dim m1 = ex.Message
Dim m2 = ex.Source
Dim m3 = ex.NativeError
MsgBox(m1 & m2 & m3, , "Add record to Acessfile")
conn.Close()
sr.Close()
Exit Sub
End Try
line = sr.ReadLine : line1 = line
Loop Until line Is Nothing
sr.Close()
conn.Close()
End Sub
Private Function dbtableexist(ByVal dbconnection As SqlCeConnection, ByVal tablename As String) As Boolean
Dim cmd As SqlCeCommand, rdr As SqlCeDataReader
Dim iname As String, tablename1 As String, i As Integer
tablename1 = UCase(tablename)
cmd = dbconnection.CreateCommand
cmd.CommandType = CommandType.Text
cmd.CommandText = "select * from INFORMATION_SCHEMA.INDEXES"
Try
rdr = cmd.ExecuteReader
Catch ex As SqlCeException
Dim m1 = ex.Message
Dim m2 = ex.Source
Dim m3 = ex.NativeError
MsgBox(m1 & m2 & m3)
Stop
End Try
Do While rdr.Read
iname = rdr("TABLE_NAME")
If UCase(iname) = tablename1 Then dbtableexist = True : Exit Function
Loop
dbtableexist = False
End Function

creat a solution in SQLServer 2005 and intall that solution in SQLServer 2005 Express

Hi,

can i develop a solution in SQLServer 2005 with the analysis Services (OLAP cubes) and then put that solution in a client that only have the SQLServer 2005 Express?

Thaks

Larokas

Analysis Services are not part of SQL Server 2005 Express installation.

Edward.
--
This posting is provided "AS IS" with no warranties, and confers no rights.

CPU usage for SQLserver.exe increased!

hi,
I have sql server 2k with sp3 on win2k advanced server.
the sqlserver.exe is consuming 95 to 100% of cpu and the cpu remains at 100% percent most of the times.
I have checked with the profiler but could not find anything fishy.
any help would be highly appreciated.
Regards,
Harshal.Is there anything running at all? Perhaps a block/lock? I would suggest contacting PSS if this problem persists.|||what is pps?|||Originally posted by harshal_in
hi,
I have sql server 2k with sp3 on win2k advanced server.
the sqlserver.exe is consuming 95 to 100% of cpu and the cpu remains at 100% percent most of the times.
I have checked with the profiler but could not find anything fishy.
any help would be highly appreciated.
Regards,
Harshal.

I am currently running windows XP and having the same problem. The only way to fix it is to shut the program down through program manager and it does not affect my operating system. Have you found anything further on this issue>
?|||I could be wrong, but this sounds like the SQl Slammer worm. Microsoft have published details how to deal with it:
http://www.microsoft.com/security/slammer.asp

--Original Message--
I am currently running windows XP and having the same problem. The only way to fix it is to shut the program down through program manager and it does not affect my operating system. Have you found anything further on this issue

cpu problem

hello all

ok this is the issue , i have a one sqlserver 2005 instalation in two hardwares and one of those hardware work perfectly but the other one are consuming a lot of cpu and ram memory .

anyone know what is happen why the sql server ar conming a lot of cpu and memory.

regards

kofmaster

do you run Reporting Services on this server? this can consume a lot of CPU.

|||

The problem was a bad query that load the tables in memory every time that it 's executed

CPU licensing question

We moved from a 4 CPU server to a 2 CPU server and when we installed SQL
Server (Enterprise Edition) on the replacement server we specified 4 CPU
licenses without thinking. We would now like to apply the 2 "spare" CPU
licenses to a new server. Can we just go ahead and apply them (since they're
not useable on the current 2 CPU server) or do we need to somehow remove them
from the 2 CPU server first?
You can specify the number of CPU licenses you have from the SQL Server
license utility in Control Panel. It's a good idea to change it to the
appropriate number just to make life easier from an audit and license
management perspective.
joe.
"J Jetson" <JJetson@.discussions.microsoft.com> wrote in message
news:FE011F53-2309-4ACD-B79D-D906EAC73171@.microsoft.com...
> We moved from a 4 CPU server to a 2 CPU server and when we installed SQL
> Server (Enterprise Edition) on the replacement server we specified 4 CPU
> licenses without thinking. We would now like to apply the 2 "spare" CPU
> licenses to a new server. Can we just go ahead and apply them (since
they're
> not useable on the current 2 CPU server) or do we need to somehow remove
them
> from the 2 CPU server first?

CPU licensing question

We moved from a 4 CPU server to a 2 CPU server and when we installed SQL
Server (Enterprise Edition) on the replacement server we specified 4 CPU
licenses without thinking. We would now like to apply the 2 "spare" CPU
licenses to a new server. Can we just go ahead and apply them (since they're
not useable on the current 2 CPU server) or do we need to somehow remove the
m
from the 2 CPU server first?You can specify the number of CPU licenses you have from the SQL Server
license utility in Control Panel. It's a good idea to change it to the
appropriate number just to make life easier from an audit and license
management perspective.
joe.
"J Jetson" <JJetson@.discussions.microsoft.com> wrote in message
news:FE011F53-2309-4ACD-B79D-D906EAC73171@.microsoft.com...
> We moved from a 4 CPU server to a 2 CPU server and when we installed SQL
> Server (Enterprise Edition) on the replacement server we specified 4 CPU
> licenses without thinking. We would now like to apply the 2 "spare" CPU
> licenses to a new server. Can we just go ahead and apply them (since
they're
> not useable on the current 2 CPU server) or do we need to somehow remove
them
> from the 2 CPU server first?

CPU is 99% used by sqlserver

Hi all ,
I have an issue hope you can help:
We have 1 server SQL server 2000 ( Small business )
running with a Pentiem 3 + 1 Go RAM. And we have only 3
Workstation like clients.
Since severals weeks, the CPU od the server is used for
99% and all client request using ACCESS is very slow.
Do you know what happened?
Thanks a lot for your help.
CanCan,
You need to monitor the CPU usage using perfmon counters concurrently
running sql profiler to see whats happening in the background.
--
Dinesh.
SQL Server FAQ at
http://www.tkdinesh.com
"can" <canpama@.netscape.net> wrote in message
news:47c301c37616$eccb9970$a601280a@.phx.gbl...
> Hi all ,
> I have an issue hope you can help:
> We have 1 server SQL server 2000 ( Small business )
> running with a Pentiem 3 + 1 Go RAM. And we have only 3
> Workstation like clients.
> Since severals weeks, the CPU od the server is used for
> 99% and all client request using ACCESS is very slow.
> Do you know what happened?
> Thanks a lot for your help.
> Can
>|||Hi Dinesh ,
Thanks a lot to reply me . I visited your page , it is
nice and fun.
I have another question , i hope that you can help me.
My company has SQL Server 7 and clients using Access to
extract Data from Sql Server.
So my question is:
I would like to migrate all existing applications to Linus
Red Hat , i wonder if we can exporte all data to Postgress
or to My SQL or to DB2 ( Linux ) and i wonder if there are
the same product like ACCESS running with Linux:
Many thank.
Can
>--Original Message--
>Can,
>You need to monitor the CPU usage using perfmon counters
concurrently
>running sql profiler to see whats happening in the
background.
>--
>Dinesh.
>SQL Server FAQ at
>http://www.tkdinesh.com
>"can" <canpama@.netscape.net> wrote in message
>news:47c301c37616$eccb9970$a601280a@.phx.gbl...
>> Hi all ,
>> I have an issue hope you can help:
>> We have 1 server SQL server 2000 ( Small business )
>> running with a Pentiem 3 + 1 Go RAM. And we have only 3
>> Workstation like clients.
>> Since severals weeks, the CPU od the server is used for
>> 99% and all client request using ACCESS is very slow.
>> Do you know what happened?
>> Thanks a lot for your help.
>> Can
>>
>
>.
>|||Don't know if you've resolved this already...but I've had this happen on
several servers. It was a virus. I believe it was slammer. The
characteristics of it are similar. Rebooting would recover the CPU for a
while..but it would go back tying up the network, and locking up the server.
If you don't have an SA password...I would bet that it is slammer.
Gary
"can" <canpama@.netscape.net> wrote in message
news:47c301c37616$eccb9970$a601280a@.phx.gbl...
> Hi all ,
> I have an issue hope you can help:
> We have 1 server SQL server 2000 ( Small business )
> running with a Pentiem 3 + 1 Go RAM. And we have only 3
> Workstation like clients.
> Since severals weeks, the CPU od the server is used for
> 99% and all client request using ACCESS is very slow.
> Do you know what happened?
> Thanks a lot for your help.
> Can
>|||Can,
Thanks:)
>>to DB2
Try
INF: Configuring Data Sources for the Microsoft OLE DB Provider for DB2
http://www.support.microsoft.com/?id=218590
INF: Configuring Data Sources for the Microsoft OLE DB Provider for AS/400
and VSAM
http://www.support.microsoft.com/?id=219077
>>same product like ACCESS running with Linux:
Iam not aware about any.
Dinesh.
SQL Server FAQ at
http://www.tkdinesh.com
"can" <canpama@.netscape.net> wrote in message
news:3c1d01c3762a$9d2b7200$a301280a@.phx.gbl...
> Hi Dinesh ,
> Thanks a lot to reply me . I visited your page , it is
> nice and fun.
> I have another question , i hope that you can help me.
> My company has SQL Server 7 and clients using Access to
> extract Data from Sql Server.
> So my question is:
> I would like to migrate all existing applications to Linus
> Red Hat , i wonder if we can exporte all data to Postgress
> or to My SQL or to DB2 ( Linux ) and i wonder if there are
> the same product like ACCESS running with Linux:
> Many thank.
> Can
>
>
>
>
> >--Original Message--
> >Can,
> >
> >You need to monitor the CPU usage using perfmon counters
> concurrently
> >running sql profiler to see whats happening in the
> background.
> >
> >--
> >Dinesh.
> >SQL Server FAQ at
> >http://www.tkdinesh.com
> >
> >"can" <canpama@.netscape.net> wrote in message
> >news:47c301c37616$eccb9970$a601280a@.phx.gbl...
> >> Hi all ,
> >>
> >> I have an issue hope you can help:
> >>
> >> We have 1 server SQL server 2000 ( Small business )
> >> running with a Pentiem 3 + 1 Go RAM. And we have only 3
> >> Workstation like clients.
> >>
> >> Since severals weeks, the CPU od the server is used for
> >> 99% and all client request using ACCESS is very slow.
> >>
> >> Do you know what happened?
> >> Thanks a lot for your help.
> >> Can
> >>
> >>
> >
> >
> >.
> >

cpu at 100% by sqlserver.exe

Hi,
I've been seing some strange things on my sql server. The cpu reaches voor
100% usage, caused by the sqlserver.exe
I been looking at the periods and start/end time of the 100% usage. I cannot
explain it by looking at the start/end times of backup's, large queries etc
etc.
Has anyone seen this ?
Does anyone have an idea what may cause this?
Greetz,
Macdash
(macdash at home dot nl for replies if possible)
Hi
Run profiler and see what is running at that time.
Generaly 100% utilization is caused by a query not having suitable indexes,
but all data is in cache, so the table scan pegs the CPU at 100% as no disk
I/O is waited on.
Regards
Mike
"Erik" wrote:

> Hi,
> I've been seing some strange things on my sql server. The cpu reaches voor
> 100% usage, caused by the sqlserver.exe
> I been looking at the periods and start/end time of the 100% usage. I cannot
> explain it by looking at the start/end times of backup's, large queries etc
> etc.
> Has anyone seen this ?
> Does anyone have an idea what may cause this?
> Greetz,
> Macdash
> (macdash at home dot nl for replies if possible)
>
>
|||High CPU can be caused by many things... including Order by, group by, and
having clauses... You need to run profiler and check the queries that are
running during the period.
If CPU peaks and 100 and stays forever, it could be the slammer virus if you
are not service packed up..
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Erik" <macdash@.hotmail.com> wrote in message
news:%23EkpdAYxEHA.2564@.TK2MSFTNGP12.phx.gbl...
> Hi,
> I've been seing some strange things on my sql server. The cpu reaches voor
> 100% usage, caused by the sqlserver.exe
> I been looking at the periods and start/end time of the 100% usage. I
cannot
> explain it by looking at the start/end times of backup's, large queries
etc
> etc.
> Has anyone seen this ?
> Does anyone have an idea what may cause this?
> Greetz,
> Macdash
> (macdash at home dot nl for replies if possible)
>

cpu at 100% by sqlserver.exe

Hi,
I've been seing some strange things on my sql server. The cpu reaches voor
100% usage, caused by the sqlserver.exe
I been looking at the periods and start/end time of the 100% usage. I cannot
explain it by looking at the start/end times of backup's, large queries etc
etc.
Has anyone seen this ?
Does anyone have an idea what may cause this?
Greetz,
Macdash
(macdash at home dot nl for replies if possible)Hi
Run profiler and see what is running at that time.
Generaly 100% utilization is caused by a query not having suitable indexes,
but all data is in cache, so the table scan pegs the CPU at 100% as no disk
I/O is waited on.
Regards
Mike
"Erik" wrote:

> Hi,
> I've been seing some strange things on my sql server. The cpu reaches voor
> 100% usage, caused by the sqlserver.exe
> I been looking at the periods and start/end time of the 100% usage. I cann
ot
> explain it by looking at the start/end times of backup's, large queries et
c
> etc.
> Has anyone seen this ?
> Does anyone have an idea what may cause this?
> Greetz,
> Macdash
> (macdash at home dot nl for replies if possible)
>
>|||High CPU can be caused by many things... including Order by, group by, and
having clauses... You need to run profiler and check the queries that are
running during the period.
If CPU peaks and 100 and stays forever, it could be the slammer virus if you
are not service packed up..
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Erik" <macdash@.hotmail.com> wrote in message
news:%23EkpdAYxEHA.2564@.TK2MSFTNGP12.phx.gbl...
> Hi,
> I've been seing some strange things on my sql server. The cpu reaches voor
> 100% usage, caused by the sqlserver.exe
> I been looking at the periods and start/end time of the 100% usage. I
cannot
> explain it by looking at the start/end times of backup's, large queries
etc
> etc.
> Has anyone seen this ?
> Does anyone have an idea what may cause this?
> Greetz,
> Macdash
> (macdash at home dot nl for replies if possible)
>

cpu at 100% by sqlserver.exe

Hi,
I've been seing some strange things on my sql server. The cpu reaches voor
100% usage, caused by the sqlserver.exe
I been looking at the periods and start/end time of the 100% usage. I cannot
explain it by looking at the start/end times of backup's, large queries etc
etc.
Has anyone seen this ?
Does anyone have an idea what may cause this?
Greetz,
Macdash
(macdash at home dot nl for replies if possible)Hi
Run profiler and see what is running at that time.
Generaly 100% utilization is caused by a query not having suitable indexes,
but all data is in cache, so the table scan pegs the CPU at 100% as no disk
I/O is waited on.
Regards
Mike
"Erik" wrote:
> Hi,
> I've been seing some strange things on my sql server. The cpu reaches voor
> 100% usage, caused by the sqlserver.exe
> I been looking at the periods and start/end time of the 100% usage. I cannot
> explain it by looking at the start/end times of backup's, large queries etc
> etc.
> Has anyone seen this ?
> Does anyone have an idea what may cause this?
> Greetz,
> Macdash
> (macdash at home dot nl for replies if possible)
>
>|||High CPU can be caused by many things... including Order by, group by, and
having clauses... You need to run profiler and check the queries that are
running during the period.
If CPU peaks and 100 and stays forever, it could be the slammer virus if you
are not service packed up..
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Erik" <macdash@.hotmail.com> wrote in message
news:%23EkpdAYxEHA.2564@.TK2MSFTNGP12.phx.gbl...
> Hi,
> I've been seing some strange things on my sql server. The cpu reaches voor
> 100% usage, caused by the sqlserver.exe
> I been looking at the periods and start/end time of the 100% usage. I
cannot
> explain it by looking at the start/end times of backup's, large queries
etc
> etc.
> Has anyone seen this ?
> Does anyone have an idea what may cause this?
> Greetz,
> Macdash
> (macdash at home dot nl for replies if possible)
>

CPU 100%

Hi guys
My SQL Server 2000 SP3 have for no apparent reason begun running 100% cpu
and it does not drop off.
All the CPU is used by the SQLServer service.
Any ideas?
Thanks
Regards
Jonas
It seem to be our virus protection that triggered the SQLServer service to
use 100%CPU. Once virus protection was disabled CPU usage dropped to normal
level.
Regards
Jonas
"Jonas Larsen" <Jonas.Larsen@.Alcan.com> wrote in message
news:uF71v%238jEHA.704@.TK2MSFTNGP09.phx.gbl...
> Hi guys
> My SQL Server 2000 SP3 have for no apparent reason begun running 100% cpu
> and it does not drop off.
> All the CPU is used by the SQLServer service.
> Any ideas?
> Thanks
> Regards
> Jonas
>
>
|||check sysprocesses(nolock) order by cpu where status !='sleeping'
then dbcc inputbuffer(spid) find out what SQL is running.
"Jonas Larsen" wrote:

> Hi guys
> My SQL Server 2000 SP3 have for no apparent reason begun running 100% cpu
> and it does not drop off.
> All the CPU is used by the SQLServer service.
> Any ideas?
> Thanks
> Regards
> Jonas
>
>
|||Could you let me know what virus protection are you using? Also, operating
system and SQLServer details.
I ask because we're looking at installing virus protection and don't want
the same symptoms as you...
Thanks
|||Hi Griff
We use Trend Micro Office Scan. However this is the first time in more than
a year.
Regards
Jonas
"Griff" <Howling@.The.Moon> wrote in message
news:ue$ZfCAkEHA.2696@.TK2MSFTNGP10.phx.gbl...
> Could you let me know what virus protection are you using? Also,
operating
> system and SQLServer details.
> I ask because we're looking at installing virus protection and don't want
> the same symptoms as you...
> Thanks
>

CPU 100%

Hi guys
My SQL Server 2000 SP3 have for no apparent reason begun running 100% cpu
and it does not drop off.
All the CPU is used by the SQLServer service.
Any ideas?
Thanks
Regards
JonasIt seem to be our virus protection that triggered the SQLServer service to
use 100%CPU. Once virus protection was disabled CPU usage dropped to normal
level.
Regards
Jonas
"Jonas Larsen" <Jonas.Larsen@.Alcan.com> wrote in message
news:uF71v%238jEHA.704@.TK2MSFTNGP09.phx.gbl...
> Hi guys
> My SQL Server 2000 SP3 have for no apparent reason begun running 100% cpu
> and it does not drop off.
> All the CPU is used by the SQLServer service.
> Any ideas?
> Thanks
> Regards
> Jonas
>
>|||check sysprocesses(nolock) order by cpu where status !='sleeping'
then dbcc inputbuffer(spid) find out what SQL is running.
"Jonas Larsen" wrote:

> Hi guys
> My SQL Server 2000 SP3 have for no apparent reason begun running 100% cpu
> and it does not drop off.
> All the CPU is used by the SQLServer service.
> Any ideas?
> Thanks
> Regards
> Jonas
>
>|||Could you let me know what virus protection are you using? Also, operating
system and SQLServer details.
I ask because we're looking at installing virus protection and don't want
the same symptoms as you...
Thanks|||Hi Griff
We use Trend Micro Office Scan. However this is the first time in more than
a year.
Regards
Jonas
"Griff" <Howling@.The.Moon> wrote in message
news:ue$ZfCAkEHA.2696@.TK2MSFTNGP10.phx.gbl...
> Could you let me know what virus protection are you using? Also,
operating
> system and SQLServer details.
> I ask because we're looking at installing virus protection and don't want
> the same symptoms as you...
> Thanks
>

CPU 100%

Hi guys
My SQL Server 2000 SP3 have for no apparent reason begun running 100% cpu
and it does not drop off.
All the CPU is used by the SQLServer service.
Any ideas?
Thanks
Regards
JonasIt seem to be our virus protection that triggered the SQLServer service to
use 100%CPU. Once virus protection was disabled CPU usage dropped to normal
level.
Regards
Jonas
"Jonas Larsen" <Jonas.Larsen@.Alcan.com> wrote in message
news:uF71v%238jEHA.704@.TK2MSFTNGP09.phx.gbl...
> Hi guys
> My SQL Server 2000 SP3 have for no apparent reason begun running 100% cpu
> and it does not drop off.
> All the CPU is used by the SQLServer service.
> Any ideas?
> Thanks
> Regards
> Jonas
>
>|||check sysprocesses(nolock) order by cpu where status !='sleeping'
then dbcc inputbuffer(spid) find out what SQL is running.
"Jonas Larsen" wrote:
> Hi guys
> My SQL Server 2000 SP3 have for no apparent reason begun running 100% cpu
> and it does not drop off.
> All the CPU is used by the SQLServer service.
> Any ideas?
> Thanks
> Regards
> Jonas
>
>|||Could you let me know what virus protection are you using? Also, operating
system and SQLServer details.
I ask because we're looking at installing virus protection and don't want
the same symptoms as you...
Thanks|||Hi Griff
We use Trend Micro Office Scan. However this is the first time in more than
a year.
Regards
Jonas
"Griff" <Howling@.The.Moon> wrote in message
news:ue$ZfCAkEHA.2696@.TK2MSFTNGP10.phx.gbl...
> Could you let me know what virus protection are you using? Also,
operating
> system and SQLServer details.
> I ask because we're looking at installing virus protection and don't want
> the same symptoms as you...
> Thanks
>

Couple of questions

1) To use or not to use the 3GB switch. We have 8GB of RAM and just run SQL
Server with awe and PAE Enabled. I have heard that 3GB is good and I have
also heard 3 GB is bad with the above setup..
2) We have a 32 bit system and we run into worker thread issues such all 256
worker threads are used and all incoming connections are rejected. Should I
be increasing those worker threads to say 512 ?
3) What is stored in the address space above the 4GB limit ..i.e when i use
awe to address say 6GB , is it only data/index pages that reside in that
region of memory above the 4GB limit ?
4) Is user connection memory, procedure cache stored in mem to leave area ?
We are using SQL 2000 EE
ThanksAnswers Inline
"Hassan" <Hassan@.hotmail.com> wrote in message
news:%2300axugfGHA.2068@.TK2MSFTNGP02.phx.gbl...
> 1) To use or not to use the 3GB switch. We have 8GB of RAM and just run
> SQL Server with awe and PAE Enabled. I have heard that 3GB is good and I
> have also heard 3 GB is bad with the above setup..
>
3GB is usually OK up to 12 GB total system RAM. Above that it is not
recommended

> 2) We have a 32 bit system and we run into worker thread issues such all
> 256 worker threads are used and all incoming connections are rejected.
> Should I be increasing those worker threads to say 512 ?
How many processers do you have? Are they running above 90% at the time of
the incoming rejections?

> 3) What is stored in the address space above the 4GB limit ..i.e when i
> use awe to address say 6GB , is it only data/index pages that reside in
> that region of memory above the 4GB limit ?
>
AWE memory is not addressable and is used for data cache only.

> 4) Is user connection memory, procedure cache stored in mem to leave area
> ?
No, with one exception. If you are using large packets, then user
connection memory may come out of mem-to-leave if the system has to use OS
network buffers rather than SQL network buffers.

> We are using SQL 2000 EE
> Thanks
>
>
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP|||Thanks Geoff..
1) So what are we missing out on for not using 3GB if we have less than 12GB
of RAM ?
2) We have seen worker thread problems on our high OLTP systems. Some
possible causes could be h/w related such as disk IO issue causing slowness
in response times all revolving around blocking..So say if the blocking
sproc runs for 10 secs all of a sudden, but there are around 1000 sprocs
that want to execute and read of that blocked table, then in no time, we run
out of threads and connections are not honored.
3) With AWE , does data flow from the non addressable memory to addressable
memory ?
"Geoff N. Hiten" <SQLCraftsman@.gmail.com> wrote in message
news:ehrOAIhfGHA.3652@.TK2MSFTNGP02.phx.gbl...
> Answers Inline
> "Hassan" <Hassan@.hotmail.com> wrote in message
> news:%2300axugfGHA.2068@.TK2MSFTNGP02.phx.gbl...
> 3GB is usually OK up to 12 GB total system RAM. Above that it is not
> recommended
>
> How many processers do you have? Are they running above 90% at the time
> of the incoming rejections?
>
> AWE memory is not addressable and is used for data cache only.
>
> No, with one exception. If you are using large packets, then user
> connection memory may come out of mem-to-leave if the system has to use OS
> network buffers rather than SQL network buffers.
>
>
> --
> Geoff N. Hiten
> Senior Database Administrator
> Microsoft SQL Server MVP
>
>|||More Inline
"Hassan" <Hassan@.hotmail.com> wrote in message
news:%23dYzJghfGHA.1456@.TK2MSFTNGP04.phx.gbl...
> Thanks Geoff..
> 1) So what are we missing out on for not using 3GB if we have less than
> 12GB of RAM ?
>
/3GB allows for more SQL addressable memory. Depending on your system, this
may improve performance. As with any config change, test both before and
after to see if it improves things. In most cases with system memory above
4GB and less than or equal to 12GB, you will see an improvement.

> 2) We have seen worker thread problems on our high OLTP systems. Some
> possible causes could be h/w related such as disk IO issue causing
> slowness in response times all revolving around blocking..So say if the
> blocking sproc runs for 10 secs all of a sudden, but there are around 1000
> sprocs that want to execute and read of that blocked table, then in no
> time, we run out of threads and connections are not honored.
>
Monitor your current disk queue length counters for your data and log disks.
If you are having IO stalls, you will need to improve the IO subsystem
before any worker thread count improvements will help. This is why I
hesitate to recommend a specific change over the newsgroups. You need to
find the root cause of your performance issues. Sometimes one issue, such
as an I/O weakness, can show up as memory or CPU pressure to casual
observation.

> 3) With AWE , does data flow from the non addressable memory to
> addressable memory ?
>
That is why you have to limit the use of the /3GB switch. AWE is much like
the old EXTENDED and EXPANDED RAM from the '268 days where a window of low
(or in this case addressable) memory is used to map into the non-addressable
memory. This is done via the OS memory area, so if you squeeze it too much
on very large memory systems, you introduce some severe performance issues.
The OS needs the extra memory to handle to address windows.
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP

> "Geoff N. Hiten" <SQLCraftsman@.gmail.com> wrote in message
> news:ehrOAIhfGHA.3652@.TK2MSFTNGP02.phx.gbl...
>

Counting number of IO Accesses per DB

Hello,

I am trying to write a VB.NET monitoring application for a MS SQL
server.
For that I need to know the following:
1. How do I count the number of read/write accesses per database on
the server since the creation of the DB?
2. How do I get the last access time of the database ?

Any inputs would be really appreciated.
Thanks in advance.

-SourabhThanks Geoff...

---
Re: Counting number of IO Accesses per DB
From: Geoff N. Hiten
Date Posted: 4/13/2004 3:02:00 PM

The basic building block you are looking for is the T-SQL function
fn_virtualfilestats. You can use write a query, call it from ADO.Net, and
do something nifty to display the results using VB.Net.

--
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
Careerbuilder.com

I support the Professional Association for SQL Server
www.sqlpass.org

moharss@.auburn.edu (Sourabh) wrote in message news:<cff778d4.0404131042.1d3dbac0@.posting.google.com>...
> Hello,
> I am trying to write a VB.NET monitoring application for a MS SQL
> server.
> For that I need to know the following:
> 1. How do I count the number of read/write accesses per database on
> the server since the creation of the DB?
> 2. How do I get the last access time of the database ?
> Any inputs would be really appreciated.
> Thanks in advance.
> -Sourabh