How To Determine How Much RAM My SQL Server Is Actually Using???

Jul 20, 2005

I've been doing a lot of reading on this and my head is starting to
hurt! It seems to be quite a feat to work out how much memory is
actually being used by our server.

I'm running W2K advanced server with SQL 2000 EE, 8GB of RAM, a min of
4GB and a max of 6GB is assigned to SQL server.

I'm trying to work out whether we've assigned enough or too
much/little memory to SQL server. My first thought was to let SQL
dymanically manage its own memory and see how much it uses, of course
when AWE (/3GB /PAE) is enabled it will just use all that is
available.

In perfmon "target server memory" = 6.1GB, "total server memory" =
6.1GB, "total pages" = 768000 ( x 8KB = 6.1GB).

My second thought was to use "total pages" - the average "free pages"
= average mem used, therefore giving me the average amount of memory
used by SQL. I found out that SQL uses a min of 4GB (the min we
assigned) and the max of all the memory, 6GB.

Is there an easier way of finding out how much memory is actually used
in this situation or is going by the above average the best way?

What i'm unsure about is will SQL just use all memory assigned to it
until it has the whole DB in memory? 20GB including indexes etc....

Any help would be greatly apprechiated.

View 2 Replies


ADVERTISEMENT

SQL Server Admin 2014 :: Determine Which Server Is Currently Active For Availability Groups

Nov 25, 2014

I want two write a small script to determine which is the currently active (primary) server in the AG.

Right now, I see that using SELECT * FROM SYS.dm_hadr_availability_replica_states I can determine the role. However, when the server goes down and switches to the secondary node, I don't believe that the role changes (or does it?). How do I determine which is the active node?

View 9 Replies View Related

SQL 2012 :: Determine FILEGROUP On DB In Server

Dec 4, 2014

I must determine FILEGROUP on my DB in SQL Server. Because when i wants to retrieve files which is stored in db, it would be face with error about FILEGROUP.

View 1 Replies View Related

SQL Server 2012 :: Determine FILEGROUP On DB?

Dec 4, 2014

i must determin FILEGROUP on my DB in SQL Server. because when i wants to retrieve files which is stored in db, it would be face with error about FILEGROUP.

View 1 Replies View Related

How To Determine The Version Of SQL Server Programatically.

Aug 16, 2007

What is the official supported way to determine the version of SQL server and installed Service Pack programatically.
Different components such as Analysis Services or SSIS can have service pack applied at different times. So what is the right officially supported way to determine the version of those components.

Thank you in advance.

View 7 Replies View Related

SQL Server 2008 :: How To Determine Baseline For Servers

Apr 9, 2015

I've been put in a situation where I have a number of SQL DB's running on 2005/2008 which I have responsibility for. I've been given limited information so am looking at a starting point to determine where I go from here.

I have of course ensured there is a backup strategy in place to secure the data.

View 1 Replies View Related

SQL Server 2014 :: Possible To Determine Dataflow Within A Database?

May 14, 2015

Is it possible to determine the data flow within a database? e.g. I start by adding/inserting a record in MainTable1, then through a trigger SubTable1, SubTable2 have records inserted into, SubTable3 has a record updated into, then a StoredProcedure1 is run which updates first SubTable4, then a StoredProcedure2 is run which adds a record in SubTable5. So by simply adding a record in MainTable1, I affect 5 other tables.

Imagine a more complex process than this one now (which I'm currently facing and I want to win time and precision; I will still investigate it myself (if I will find an answer first - I will post it here)) Is there a way I could "extract" this flow by using T-SQL?

View 7 Replies View Related

SQL Server 2012 :: How To Determine Metadata On DBCC Results

May 26, 2015

I can find many examples of loading DBCC results into tables. They all begin with a create table statement defining the results. My question is , other than trial and error, is there a way to determine what data types will be returned. Sure you can say that first element looks like an integer, but is it really a bigint, and that text string can be varchar(max) but will char(2) work.

I'm not looking for an answer for a specific DBCC function, but rather a generic way I can determine the characteristics of any DBCC result set.

I tried

SELECT *
INTO #tmp
FROM OPENROWSET('SQLOLEDB',
'Server=ray;Trusted_Connection=Yes;Database=Ed_sandbox',
'Set FmtOnly OFF; DBCC loginfo WITH tableresults ')

but I got back

Msg 11527, Level 16, State 1, Procedure sp_describe_first_result_set, Line 1

The metadata could not be determined because statement 'DBCC loginfo WITH tableresults' does not support metadata discovery.

View 1 Replies View Related

Transact SQL :: How To Determine Remote Server Is Shutdown By Agent

Sep 28, 2015

I create a job name test connection by SQL AGent

Background:

- Have server1 and server2
- The job has 3 steps:
 + Step1: check server1 is running, next step2
               Server1 is shutdown or can not ping, next step3
 + Step2: do anything, for Example: run batch exe on server1
 + Step3: do anything, for Example: run batch exe on server2

Step1, i am using ping server1 command,

My problem is if server1 is shutdown, my command (ping server1) is also return true

and in the fact, job is run by follow Step1 -> Step2.

Expectation the job is run by follow step1 -> Step3

View 5 Replies View Related

How To Determine Which Service Pack Level Your SQL Server Is Running And Column Name Spacing Bug?

Aug 10, 2007

I have visual studio 2005 with XP home. I'm trying to find out which service pack my SQL server is running as I've been told that Service pack 2 has a few bugs.

For example ADO Applications Report a Syntax Error When Column Names Contain Spaces. This appears to be an error i am recieving. I just wanted to confirm that it is a microsoft bug rather then something Im doing wrong.
Microsoft meantions the bug here: http://support.microsoft.com/kb/264687



I've been looking everywhere. I do not have the "query analyzer window" where you can find this out as I have VS 2005. How would I find this out.

Thank you

View 6 Replies View Related

How To Determine, Inside A Function, If A Linked-server-query Returned Results

Feb 13, 2004

Hi, have configured an ODBC linked server for an Adaptive Server Anywhere (ASA6.0) database.
I have to write a function (not a procedure) that receives a number (@Code) and returns 1 if it was found on a table in the linked server, or 0 if not. Looks very simple...
One problem, is that the queries on a linked-server must be made through the OPENQUERY statement, which doesen't support dynamic parameters. I've solved this making the whole query a string, and executing it, something like this:

SET @SQL='SELECT * FROM OPENQUERY(CAT_ASA, ''SELECT code FROM countries WHERE code=' + @Code + ''')'
EXEC sp_executesql @SQL

(CAT_ASA is the linked-server's name)

Then, i would use @@ROWCOUNT to determine if the code exists or not. But before this, a problem appears: sp_executesql is not allowed within a function (only extended procedures are allowed).
Does somebody know how to make what i want?? I prefer to avoid using temporary tables.
Thanks!

View 3 Replies View Related

SQL Server 2012 :: Determine Read / Write Frequency On Database Table

Oct 6, 2015

How do I determine the read/write frequency on a database table? I am trying to do this on a 2012 and 2008 R2 servers.

View 2 Replies View Related

SQL Server 2012 :: Determine Which Column Is Causing Error Converting Data Type Varchar To Numeric?

Aug 14, 2014

I'm moving data from one database to another (INSERT INTO ... SELECT ... FROM ....) and am encountering this error:

Msg 8114, Level 16, State 5, Line 6
Error converting data type varchar to numeric.

My problem is that Line 6 is:

set @brn_pk = '0D4BDE66347C440F'

so that is obviously not the problem and my query has almost 200 columns. I can go through one by one and compare what column is int in my destination table and what is varchar in my source tables, but that could take quite a while. How I can work out what column is causing the problem?

View 3 Replies View Related

How To Determine The Value For The Bar?

Jan 22, 2008

Hi all,


How to determine the value for the bar in Neural Network Viewer? I know the value is displaying in the tooltips when we pointing to the bar in the table but i don't know how to get them. So where can i get those of calculation or data of score, probability of value1 and 2, and lift for value1 and 2 ? Is it get from the Microsoft Neural Network Content Viewer? which column and how to calculate? If not, please advise.


Hope my question is clear.



I am looking forward to hearing from you shortly and thanks a lot in advance.

Thanks!



With best regards,



Yours sincerely,
xuenly

View 8 Replies View Related

Determine Next Available Order ID

Sep 21, 2006

I am trying to determine the next available order id using the method below.  It works provided the table has a record in it.  If it doesn't I get the error "Input string was not in a correct format."  I am certain that it is because the query is returning a value of NULL.  How can get around that or check for the NULL value?' Establish data connection...Dim sqlConn As New SqlConnection(ConfigurationSettings.AppSettings("connectionstring"))'Determine order id number...Dim order_id As IntegerDim strSQL As StringstrSQL = "Select MAX(order_id) from mkt_order"Dim sqlCmd As New SqlCommand(strSQL, sqlConn)Dim sqlDA As New SqlDataAdapter(sqlCmd)Dim sqlDS As New DataSetsqlDA.Fill(sqlDS, "item")If sqlDS.Tables(0).Rows.Count <> 0 Thenorder_id = Convert.ToInt32(sqlDS.Tables(0).Rows(0)(0).ToString()) + 1Elseorder_id = 1End If

View 1 Replies View Related

How To Determine Cause Of Timeout?

Aug 3, 1999

Good day,

We have a web application (ASP) running on SQL Server 7.0.
Recently, the users are getting quite a lot of timeouts on
the database:

Microsoft OLE DB Provider for ODBC Drivers error '80040e31'

[Microsoft][ODBC SQL Server Driver]Timeout expired

The database is not supposed to be doing too much work, so I can't
understand why these timeouts are occuring. How can I determine
the cause of the timeouts?

The cause could probably anything from a trigger that's taking
too long, a query that's taking too long, or simply bad database design.

I've looked at SQL Server's Profiler, but could not yet use it
successfully to give me any hints of what could cause the timeouts.

Any ideas of how I can use Profiler, Performance Monitor, or any other
tool(s) to see what is happening in the background in the database,
i.e. how much processing a trigger is using, etc.

Thanks very much!
---
Gert Lombard
OSI Airport Systems
South Africa

View 1 Replies View Related

How To Determine Frequency Of

Dec 28, 2006

I'm new to full text catalogs and we have a vendor who's code utilizes them. The database server is SQL 2005 and I am noticing the following message in the SQL log every minute.

Changing the status to MERGE for full-text catalog "ResearchCatalog" (5) in database "DBA_Test" (11). This is an informational message only. No user action is required.

A SQL job is running the following command every minute.

exec sp_fulltext_catalog 'ResearchCatalog', 'start_incremental'

What is the typical frequency for running an incremental?

Can the messages be suppressed?

Thanks, Dave

View 3 Replies View Related

How To Determine Dbas?

Jul 23, 2005

for sql servers how can i determine who [what accounts] are the dbas?

View 1 Replies View Related

How To Determine When And If SQL Agent Job Will Run Again?

Dec 1, 2006

I need to determine when (maybe) and if (definitely) a SQL Agent job will run again. I need to maintain a table of the next pending execution for each job. I need to be able to update this table from within a SQL Agent job, but preferably from within an executing SSIS package in the job. Is this possible and if so, any suggestions on how?

Thanks

View 3 Replies View Related

Determine That This Is Deterministic

May 3, 2008

Hi all... This is the definition on the M/S site:

"Deterministic functions always return the same result any time they are called with a specific set of input values and given the same state of the database. Nondeterministic functions may return different results each time they are called with a specific set of input values even if the database state that they access remains the same."

Good... straight forward, right? Ok.... try entering thses command seperately:


Create Table Readings (ReadingDate DateTime Not Null);



Create Function [dbo].[funct_SameDate](@datReadingDate Datetime)

Returns DateTime

As

Begin

return @datReadingDate;

End


Alter table Readings add

[TempColumn] as (dbo.[funct_SameDate](ReadingDate)) PERSISTED NOT NULL;


Error on last command returns:

Computed column 'TempColumn' in table 'Readings' cannot be persisted because the column is non-deterministic.


Can someone please explain this to me? The same value is always being returned.


This does work:


Create Table Readings (ReadingDate DateTime Not Null);

Alter table Readings add [TempColumn] as (ReadingDate) PERSISTED NOT NULL;


I obviously want to do more things inside the function, but I can't get by the first step.

Any suggestions?

Thanks!

Forch

View 6 Replies View Related

Determine First Row Of New Page

Dec 27, 2007

Hi,

I have a report that has a table with detail grouping. This table shows the sales by day for each product. The users only want to see the date field for the first item in the group. After that, they do not want to display this field (to reduce the data on the report). However, when the data wraps to a second page, they want the date to appear on the first row of the new page.

Is there any way to determine if a row is the first row on a page?

I tried using the RowCount, but that continues from the previous page.

Any thoughts?

Thanks!

David.

View 8 Replies View Related

Determine A Role

Jun 18, 2007

hi,



since I am kind o'new with SQL, I preffer get an advice fro you pro's: I created an application which performs access to a database on an SQL server. the application will be used by a few different users, each on a different computer. the application calls stored procedures, updatesinserts records in tables on the SQL and delete rows. what would be the best role to define the users activity ? How do I limit their activity ONLY to the specified actions ?

View 1 Replies View Related

Determine Next Order Id

May 17, 2007

I'm working on a sproc that determines the next order id for a specified customer. The table has

custid int,

ordernum varchar(10)



Data is:

1000, 1000-001

1000, 1000-002

1001, 1001-001

1000, 1000-003



I need to know the next ordernum for the specified custid. For example, GetNextOrderNum(1000) should return 1000-004. GetNextOrderNum(1002) should return 1002-001 (since there aren't any orders yet).



I honestly don't know where to begin.



Can someone please help?

View 6 Replies View Related

Need To Determine Which Db Is Pricipal

Jun 6, 2007

Hi All,

i am working with database mirroring and i need to be able to determine which database will be principal.

for example if server A is principle and server B is mirrored and server A is down, then server B take owner and become principle, after fixing server A i want it to be principle again,

is it possible ??

View 4 Replies View Related

How To Determine Last LSN For Database

Feb 13, 2008



What is the most reliable way to determine the last LSN of a database? I've looked in sys.database_files to no avail. I've also looked in msdb.dbo.backupset which is accurate but only based on backups already performed not the current state of the database.

-joe

View 5 Replies View Related

INSERT And Determine ID Question

Dec 11, 2006

With an INSERT statement I add a record to a table.
Then I want to get the (autonumber) ID of the newly created record.
 What is the fastest and best way to do this?

View 7 Replies View Related

Determine Service Pack In 7.0?

Apr 15, 2002

I need to determine which service pack we are running on our sql servers. I run SELECT @@VERSION and get it tells me that we are running 7.00.1020. I have a listing from google that tells me the value for each service pack, but my version doesn't match anything on the list.

Can you tell which service pack I am running based on the results of my query?

Thanks!

View 2 Replies View Related

How To Determine When To Re-Index Programmatically?

May 13, 2002

Great Monday Morning to one and all,

Setup: SQL7 w/SP4 running on W2K Pro

Table in Question:


I have a system that processes inserts that originate from automatic data collection subsystems on manufacuturing cells. The system processes about 2500 records a day. The system is isolated with no ready support or attention. My goal is to automate any and every reasonable admin task. My present activity centers on re-indexing the main table (receives the data from the inserts, supplies the data for web based reporting).

The table - tb_production_log - receives inserts that are time stamped and bear a Machine_id. The table has a clustered index built on the Machine_id (int) and Date_time (time of data's acquisition). The table only receives Inserts, the records are never Updated. No inserts are out of time sequence (no older records ever have to be 'wedged' in amongst existing records). Ulitmately, the table is tested daily for records with age > 365 days. Such records are Deleted.

For the past week, I have been running a monitoring stored procedure on my test box to track the fragmentation of the tb_production_log table. It's based on DBCC SHOWCONTIG with some extra tests. After capturing the SHOWCONTIG data, the sp runs a test query against the table to emulate a typical User report. I track the time this query takes. The query covers records over the last 7 days. (approx. 17,500 records involved). In addition, I track the time it takes Inserts to run. Inserts are done in batches from an external app. I get a RecordsPerSecond data point for each batch.

View 3 Replies View Related

How To Determine When To Re-Index Programmatically?

May 13, 2002

Great Monday Morning to one and all,

Setup: SQL7 w/SP4 running on W2K Pro

Table in Question:


I have a system that processes inserts that originate from automatic data collection subsystems on manufacuturing cells. The system processes about 2500 records a day. The system is isolated with no ready support or attention. My goal is to automate any and every reasonable admin task. My present activity centers on re-indexing the main table (receives the data from the inserts, supplies the data for web based reporting).

The table - tb_production_log - receives inserts that are time stamped and bear a Machine_id. The table has a clustered index built on the Machine_id (int) and Date_time (time of data's acquisition). The table only receives Inserts, the records are never Updated. No inserts are out of time sequence (no older records ever have to be 'wedged' in amongst existing records). Ulitmately, the table is tested daily for records with age > 365 days. Such records are Deleted.

For the past week, I have been running a monitoring stored procedure on my test box to track the fragmentation of the tb_production_log table. It's based on DBCC SHOWCONTIG with some extra tests. After capturing the SHOWCONTIG data, the sp runs a test query against the table to emulate a typical User report. I track the time this query takes. The query covers records over the last 7 days. (approx. 17,500 records involved). In addition, I track the time it takes Inserts to run. Inserts are done in batches from an external app. I get a RecordsPerSecond data point for each batch.

View 2 Replies View Related

How To Determine Service Pack?

Jun 5, 2002

Hi,

I am trying to determine what service pack I am running on a SQL 7 server. Can somebody tell me how to do this?

I have an article from technet showing me how to do it for SQL 6.5 but not 7. I assume it is the same method but I need to know which product version number relates to which service pack.


Thanks

View 2 Replies View Related

How To Determine The Edition Installed

Feb 28, 2002

Dear all,

Please tell me how to determne the editiion of mssql installed. I can only know the version but cannot know the edition.

Regards,
Simon

View 6 Replies View Related

How To Determine Login Name From User

Apr 13, 1999

How do I determine the Login name from the user name, in SQL?

For example, I have a Login called Accounting with users Bob and Sue. How do I know from Bob or Sue's user name that they are members of the Accounting Login?

I am using NT Authentication.

Thanks!

View 2 Replies View Related

Need To Determine If Records Are Already In Database

Feb 21, 2001

Problem: I can add 4 items to a SQL 7.0 database but I need to redirect the user if all 4 items exist in the same Record elsewhere in the Database.

Clearly I need some kind of statement like "if category1 = var1 AND category2 = var 2 AND..." but don't know where to put it.

Code:

strSQL = "SELECT * FROM tblUserQuarter"

Set objRS = Server.CreateObject("ADODB.Recordset")
objRS.Open strSQL, objConn,adOpenStatic , adLockOptimistic

'Insert choices from report.asp into tblUserQuarter
objRS.AddNew
objRS("cboFY") = Request.Form("cboFY")
objRS("Quarter") = Request.Form("Quarter")
objRS("ID") = Request.Form("ID")
objRS("Report") = Request.Form("Report")
objRS.Update

objRS.Close
Set objRS = Nothing
objConn.Close
set objConn = Nothing

View 1 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved