how to free memory used by prior query statement within a batch by TSQL?

Just Like these:

-- batch start
Select * from someTable --maybe a query which need much res(I/O,cpu,memory)

/*
can I do something here to free res used by prior statement?
*/

select * from someOtherTable
--batch end

The Sqls above are written in a procedure to automating test for some select querys.What about this:

select ...
DBCC DROPCLEANBUFFERS
select ...|||Won't that force a recompile of everything?

Gotta look that up...

btw...SQL Server will grab as much memory is available, and will only release it if it's not using it and something else needs it...

It's not very sociable...|||thanks for help, but it seemed not work as I hoped.

Sqlserver used 20M memory before I run the select query;
Sqlserver used 123M memory after I run the select query;
Sqlserver still used 123M memory after I run 'DBCC DROPCLEANBUFFERS', but I want memory used by Sqlserver not larger than 20M;

I don't know exactly how memory useage affect the performance of next query's execution, so I write down my primal Intention:

select... -- query A

/* do something here to make query B to be executed just as query A was not executed before( or minish query A's affection). */

select... -- query B

could I make it?|||here is my test plan:

there are query ABC..., and insert all querys into a table called querytbl;
open a cursor for all records from querytbl;
fetch next query from cursor;
while @.@.fetchstatus = 0
begin
exec query for 3 times and calculate average time spending;
fetch next query from cursor;
end
...

Is there any better test plan?(just test time spending)

How to find the SQL Server install directory from tsql

How do we find the SQL Server install directory using a T-sql command or from command prompt?

Thanks,
Vivek
You can find the path (with and without filename) to master.mdf from the master_files system view, if that's enough:

Code Snippet

select physical_name
from master.sys.master_files
where name = 'master';

declare @.tail int
set @.tail = (
select charindex('\',reverse(physical_name))
from master.sys.master_files
where name = 'master'
)
select substring(physical_name,1,len(physical_name)-@.tail)
from master.sys.master_files
where name = 'master';

The installation directory is a registry key, and a batch file to dig it out is suggested here: http://weblogs.asp.net/jgalloway/archive/2006/10.aspx. (I didn't try it, but there's a link to another page noting that the white space after delims is a tab followed by a space.)

Steve Kass
Drew University
http://www.stevekass.com
|||

All you need is in the link below but in C#. It is 23pages long so read it but you need admin permissions if it is a server. Hope this helps.

http://msdn2.microsoft.com/en-us/library/bb264562.aspx

|||The system databases are not under the SQL Server installed directory. They are in a different drive. If this is the case how do i get the install directory?
|||

One more approach on T-SQL itself..

Code Snippet

Create Table #Data

(

path Varchar(max)

)

Insert Into #Data

Exec xp_cmdshell 'path'

Declare @.Paths Varchar(max)

Set @.Paths = ';'

Select @.Paths = @.Paths + Replace(Path,'Path=','') From #Data Where Path is NOT NULL

select @.Paths = @.Paths + ';'

Create table #Number

(

Number Int

)

Declare @.i as Int

Set @.i = 1

While @.i<len(@.Paths)

Begin

Insert Into #Number Select @.i;

Set @.i=@.i+1

End

Select

Replace(Replace(paths,'\Tools\BINN\',''),'\Tools\BINN','') [SQL Server Paths]

From

(

Select

Substring(@.Paths,Number,Charindex(';',@.Paths,Number)-Number) Paths

From

#Number

Where

Substring(@.paths,Number-1,1) = ';'

) as Data

Where Paths Like '%Microsoft SQL Server%'

Drop Table #Number;

Drop Table #Data;

|||

One more easy approach .. using undocumented system procedure..

Code Snippet

Declare @.Path as varchar(100);

Set @.Path = NULL

Exec master..xp_regread 'HKEY_LOCAL_MACHINE', 'SOFTWARE\Microsoft\Microsoft SQL Server\70\Tools\ClientSetup', 'SQLPath', @.Path OUTPUT

Select @.Path as [Sql Server 7.0 path]

Set @.Path = NULL

Exec master..xp_regread 'HKEY_LOCAL_MACHINE', 'SOFTWARE\Microsoft\Microsoft SQL Server\80\Tools\ClientSetup', 'SQLPath', @.Path OUTPUT

Select @.Path as [Sql Server 2000 path]

Set @.Path = NULL

Exec master..xp_regread 'HKEY_LOCAL_MACHINE', 'SOFTWARE\Microsoft\Microsoft SQL Server\90\Tools\ClientSetup', 'SQLPath', @.Path OUTPUT

Select @.Path as [Sql Server 2005 path]

Set @.Path = NULL

Exec master..xp_regread 'HKEY_LOCAL_MACHINE', 'SOFTWARE\Microsoft\Microsoft SQL Server\100\Tools\ClientSetup', 'SQLPath', @.Path OUTPUT

Select @.Path as [Sql Server KATMAI path]

|||

You could use SMO and write a CLR function that would return the value.

It could be more reliable (thinking permission issues) than reading the registry with xp_regread.

For an idea, refer to Books Online, Topic: 'RootDirectory property'

There are several properties of the Server object that one could find useful.

|||Thanks Manivannan it works!!!
|||

The link below contains a SQLCLR TVF that can be used to get this information and others which are persisted in the registry only. For SQL Server 2000, you will have to access the registry keys directly from a batch file or use system databases path (which may be configured during setup to different location).

http://blogs.msdn.com/sqltips/archive/2005/08/19/SqlRegSettings.aspx

how to find statistics for a column

I am trying to code a tsql routine to check for the existence of statistics before a column is dropped. If the stats exist I want to drop the stats first and then drop the column. I Can do this manually but need it in a script.
MikeUm...why?

blindman|||First reason is that the column cannont be dropped while the statistic is pressent.
Second, there are 15 db's that need to have the obsolete column(s) dropped.
Third, all steps are documented.
Fourth, gui's take 2 long. Just run a command.
Fifth, There are dozens of obsoleted columns that are being dropped in the next release.

mike

eg.sample of the errors generated.
Server: Msg 5074, Level 16, State 8, Line 9

The statistics 'HHAltCouponName' is dependent on column 'HHAltCouponName'.

Server: Msg 4922, Level 16, State 1, Line 9

ALTER TABLE DROP COLUMN HHAltCouponName failed because one or more objects access this column.

How to find sql server agent running using tsql

How to find using TSQL or some sql server function that SQL Server agent
is running or stopped.
--
Thanks
Amish
SQL Server DBA
ExtraQuest"AM" <anonymous@.developersdex.com> wrote in message
news:O7lq85TkFHA.3144@.TK2MSFTNGP12.phx.gbl...
> How to find using TSQL or some sql server function that SQL Server
agent
> is running or stopped.
> --
> Thanks
> Amish
> SQL Server DBA
> ExtraQuest
>
Perhaps something like:
SELECT program_name
FROM sysprocesses
WHERE program_name like 'SQLAgent%'
I think that you should get at least one row back if it is running.
Rick Sawtell
MCT, MCSD, MCDBA|||See if this helps:
Code Makes Sure the SQL Agent Service Is Running
http://www.windowsitpro.com/Article/ArticleID/46083/46083.html
AMB
"AM" wrote:
> How to find using TSQL or some sql server function that SQL Server agent
> is running or stopped.
> --
> Thanks
> Amish
> SQL Server DBA
> ExtraQuest
>
>|||fn_agentservice_DMO()
http://www.sqldbatips.com/displaycode.asp?ID=36
--
HTH
Jasper Smith (SQL Server MVP)
http://www.sqldbatips.com
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"AM" <anonymous@.developersdex.com> wrote in message
news:O7lq85TkFHA.3144@.TK2MSFTNGP12.phx.gbl...
> How to find using TSQL or some sql server function that SQL Server
> agent
> is running or stopped.
> --
> Thanks
> Amish
> SQL Server DBA
> ExtraQuest
>

How to find sql server agent running using tsql

How to find using TSQL or some sql server function that SQL Server agent
is running or stopped.
Thanks
Amish
SQL Server DBA
ExtraQuest
"AM" <anonymous@.codecomments.com> wrote in message
news:O7lq85TkFHA.3144@.TK2MSFTNGP12.phx.gbl...
> How to find using TSQL or some sql server function that SQL Server
agent
> is running or stopped.
> --
> Thanks
> Amish
> SQL Server DBA
> ExtraQuest
>
Perhaps something like:
SELECT program_name
FROM sysprocesses
WHERE program_name like 'SQLAgent%'
I think that you should get at least one row back if it is running.
Rick Sawtell
MCT, MCSD, MCDBA
|||See if this helps:
Code Makes Sure the SQL Agent Service Is Running
http://www.windowsitpro.com/Article/...083/46083.html
AMB
"AM" wrote:

> How to find using TSQL or some sql server function that SQL Server agent
> is running or stopped.
> --
> Thanks
> Amish
> SQL Server DBA
> ExtraQuest
>
>
|||fn_agentservice_DMO()
http://www.sqldbatips.com/displaycode.asp?ID=36
HTH
Jasper Smith (SQL Server MVP)
http://www.sqldbatips.com
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"AM" <anonymous@.codecomments.com> wrote in message
news:O7lq85TkFHA.3144@.TK2MSFTNGP12.phx.gbl...
> How to find using TSQL or some sql server function that SQL Server
> agent
> is running or stopped.
> --
> Thanks
> Amish
> SQL Server DBA
> ExtraQuest
>

How to find sql server agent running using tsql

How to find using TSQL or some sql server function that SQL Server agent
is running or stopped.
Thanks
Amish
SQL Server DBA
ExtraQuest"AM" <anonymous@.codecomments.com> wrote in message
news:O7lq85TkFHA.3144@.TK2MSFTNGP12.phx.gbl...
> How to find using TSQL or some sql server function that SQL Server
agent
> is running or stopped.
> --
> Thanks
> Amish
> SQL Server DBA
> ExtraQuest
>
Perhaps something like:
SELECT program_name
FROM sysprocesses
WHERE program_name like 'SQLAgent%'
I think that you should get at least one row back if it is running.
Rick Sawtell
MCT, MCSD, MCDBA|||See if this helps:
Code Makes Sure the SQL Agent Service Is Running
http://www.windowsitpro.com/Article...6083/46083.html
AMB
"AM" wrote:

> How to find using TSQL or some sql server function that SQL Server age
nt
> is running or stopped.
> --
> Thanks
> Amish
> SQL Server DBA
> ExtraQuest
>
>|||fn_agentservice_DMO()
http://www.sqldbatips.com/displaycode.asp?ID=36
HTH
Jasper Smith (SQL Server MVP)
http://www.sqldbatips.com
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"AM" <anonymous@.codecomments.com> wrote in message
news:O7lq85TkFHA.3144@.TK2MSFTNGP12.phx.gbl...
> How to find using TSQL or some sql server function that SQL Server
> agent
> is running or stopped.
> --
> Thanks
> Amish
> SQL Server DBA
> ExtraQuest
>

how to find out if a TSQL table is being updated?

how to find out if a TSQL table is being updated?
for example if someone does an update to a table that takes a long time, is
there anyway for me to check the status of that update process?
There is no built-in and generally applicable method to determine the
progress of an UPDATE (e.g. to be able to answer how much longer it will take
for the UPDATE to complete). The reason being that UPDATE can be processed in
many different way.
If we are talking about a very large update, you may want to break it into
multiple smaller batches of update, and insert code yourself to report the
progress.
Linchi
"DR" wrote:

> how to find out if a TSQL table is being updated?
> for example if someone does an update to a table that takes a long time, is
> there anyway for me to check the status of that update process?
>
>

how to find out if a TSQL table is being updated?

how to find out if a TSQL table is being updated?
for example if someone does an update to a table that takes a long time, is
there anyway for me to check the status of that update process?Hi
What do you mean by a long time? If there are a significant number of rows
being updated then you may want to "batch" the update so that only a specific
number are updated and use a loop until all is complete. This may reduce the
number or extent of the locks on the table and reduce contention. sp_lock
will show the locks.
You could then output the number of iterations, but unless you know the
total number of rows to be updated this may not be useful.
John
"DR" wrote:
> how to find out if a TSQL table is being updated?
> for example if someone does an update to a table that takes a long time, is
> there anyway for me to check the status of that update process?
>
>

how to find out if a TSQL table is being updated?

how to find out if a TSQL table is being updated?
for example if someone does an update to a table that takes a long time, is
there anyway for me to check the status of that update process?
Answered in .programming. Please do not double-post. If you want to post
to multiple newsgroups, post to all relevant groups at once so people will
not waste time trying to answer questions that have already been answered.
"DR" <softwareengineer98037@.yahoo.com> wrote in message
news:uaiJVZ7MIHA.5224@.TK2MSFTNGP02.phx.gbl...
> how to find out if a TSQL table is being updated?
> for example if someone does an update to a table that takes a long time,
> is there anyway for me to check the status of that update process?
>

how to find out if a TSQL table is being updated?

how to find out if a TSQL table is being updated?
for example if someone does an update to a table that takes a long time, is
there anyway for me to check the status of that update process?
Hi
What do you mean by a long time? If there are a significant number of rows
being updated then you may want to "batch" the update so that only a specific
number are updated and use a loop until all is complete. This may reduce the
number or extent of the locks on the table and reduce contention. sp_lock
will show the locks.
You could then output the number of iterations, but unless you know the
total number of rows to be updated this may not be useful.
John
"DR" wrote:

> how to find out if a TSQL table is being updated?
> for example if someone does an update to a table that takes a long time, is
> there anyway for me to check the status of that update process?
>
>

how to find out if a TSQL table is being updated?

how to find out if a TSQL table is being updated?
for example if someone does an update to a table that takes a long time, is
there anyway for me to check the status of that update process?Hi
What do you mean by a long time? If there are a significant number of rows
being updated then you may want to "batch" the update so that only a specifi
c
number are updated and use a loop until all is complete. This may reduce the
number or extent of the locks on the table and reduce contention. sp_lock
will show the locks.
You could then output the number of iterations, but unless you know the
total number of rows to be updated this may not be useful.
John
"DR" wrote:

> how to find out if a TSQL table is being updated?
> for example if someone does an update to a table that takes a long time, i
s
> there anyway for me to check the status of that update process?
>
>