define Hierarchy: servers

Showing posts with label servers. Show all posts

Friday, March 30, 2012

how to diff the data of same table of two sql servers

as subject.
any freeware or done at sql level?
thanks!What?
"Mullin Yu" <mullin_yu@.ctil.com> wrote in message
news:OREXwwFzEHA.1264@.TK2MSFTNGP12.phx.gbl...
> as subject.
> any freeware or done at sql level?
> thanks!
>|||say, i have a table Order at server A and B,
i want to diff the sql data at two servers.
"ChrisR" <chris@.noemail.com> wrote in message
news:OgXnH3FzEHA.1188@.tk2msftngp13.phx.gbl...
> What?
>
> "Mullin Yu" <mullin_yu@.ctil.com> wrote in message
> news:OREXwwFzEHA.1264@.TK2MSFTNGP12.phx.gbl...
>|||It's not free but it's real cheap. www.red-gate.com has a product called
data compare that will do what you ask.
Andrew J. Kelly SQL MVP
"Mullin Yu" <mullin_yu@.ctil.com> wrote in message
news:%23tl6F6FzEHA.1596@.TK2MSFTNGP10.phx.gbl...
> say, i have a table Order at server A and B,
> i want to diff the sql data at two servers.
> "ChrisR" <chris@.noemail.com> wrote in message
> news:OgXnH3FzEHA.1188@.tk2msftngp13.phx.gbl...
>|||I'm with Andrew, I vote Red Gate
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
"Mullin Yu" <mullin_yu@.ctil.com> wrote in message
news:OREXwwFzEHA.1264@.TK2MSFTNGP12.phx.gbl...
> as subject.
> any freeware or done at sql level?
> thanks!
>|||"Mullin Yu" <mullin_yu@.ctil.com> wrote in message
news:OREXwwFzEHA.1264@.TK2MSFTNGP12.phx.gbl...
> as subject.
> any freeware or done at sql level?
> thanks!
>
What about using a FULL OUTER JOIN and then pulling the values with NULL.
Those would be the difference records. The matches would be the matching
records.
Rick Sawtell
MCT, MCSD, MCDBA|||Have a look at www.dbghost.com - why bother with a product that doesn't
always work?
DB Ghost? provides you with a fully automated BUILD, COMPARISON and
SYNCHRONIZATION capability for your SQL Server databases and is the only
product on the market that ensures database integrity as DB Ghost? will bu
ild
your database directly from your source control system. No other product in
the world does this. No other product can build, compare and synchronize a
target database making it match the source scripts precisely, every single
time, not just sometimes, but every single time. Try and prove us wrong.
Something else that might grab your interest is that an incredible 94% of
our clients (94%!!!) previously purchased our competitors products and soon
found that in the real world, these products let them down time after time.
Don't make the same mistake - why would you buy from our competitors who, fo
r
similar money, can only offer you tools that don't build, and only compare
and sometimes synchronize...food for thought?
"ChrisR" wrote:

> What?
>
> "Mullin Yu" <mullin_yu@.ctil.com> wrote in message
> news:OREXwwFzEHA.1264@.TK2MSFTNGP12.phx.gbl...
>
>

how to diff the data of same table of two sql servers

as subject.
any freeware or done at sql level?
thanks!What?
"Mullin Yu" <mullin_yu@.ctil.com> wrote in message
news:OREXwwFzEHA.1264@.TK2MSFTNGP12.phx.gbl...
> as subject.
> any freeware or done at sql level?
> thanks!
>|||say, i have a table Order at server A and B,
i want to diff the sql data at two servers.
"ChrisR" <chris@.noemail.com> wrote in message
news:OgXnH3FzEHA.1188@.tk2msftngp13.phx.gbl...
> What?
>
> "Mullin Yu" <mullin_yu@.ctil.com> wrote in message
> news:OREXwwFzEHA.1264@.TK2MSFTNGP12.phx.gbl...
> > as subject.
> >
> > any freeware or done at sql level?
> >
> > thanks!
> >
> >
>|||It's not free but it's real cheap. www.red-gate.com has a product called
data compare that will do what you ask.
--
Andrew J. Kelly SQL MVP
"Mullin Yu" <mullin_yu@.ctil.com> wrote in message
news:%23tl6F6FzEHA.1596@.TK2MSFTNGP10.phx.gbl...
> say, i have a table Order at server A and B,
> i want to diff the sql data at two servers.
> "ChrisR" <chris@.noemail.com> wrote in message
> news:OgXnH3FzEHA.1188@.tk2msftngp13.phx.gbl...
>> What?
>>
>> "Mullin Yu" <mullin_yu@.ctil.com> wrote in message
>> news:OREXwwFzEHA.1264@.TK2MSFTNGP12.phx.gbl...
>> > as subject.
>> >
>> > any freeware or done at sql level?
>> >
>> > thanks!
>> >
>> >
>>
>|||I'm with Andrew, I vote Red Gate
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
"Mullin Yu" <mullin_yu@.ctil.com> wrote in message
news:OREXwwFzEHA.1264@.TK2MSFTNGP12.phx.gbl...
> as subject.
> any freeware or done at sql level?
> thanks!
>|||"Mullin Yu" <mullin_yu@.ctil.com> wrote in message
news:OREXwwFzEHA.1264@.TK2MSFTNGP12.phx.gbl...
> as subject.
> any freeware or done at sql level?
> thanks!
>
What about using a FULL OUTER JOIN and then pulling the values with NULL.
Those would be the difference records. The matches would be the matching
records.
Rick Sawtell
MCT, MCSD, MCDBA|||Have a look at www.dbghost.com - why bother with a product that doesn't
always work?
DB Ghostâ?¢ provides you with a fully automated BUILD, COMPARISON and
SYNCHRONIZATION capability for your SQL Server databases and is the only
product on the market that ensures database integrity as DB Ghostâ?¢ will build
your database directly from your source control system. No other product in
the world does this. No other product can build, compare and synchronize a
target database making it match the source scripts precisely, every single
time, not just sometimes, but every single time. Try and prove us wrong.
Something else that might grab your interest is that an incredible 94% of
our clients (94%!!!) previously purchased our competitors products and soon
found that in the real world, these products let them down time after time.
Don't make the same mistake - why would you buy from our competitors who, for
similar money, can only offer you tools that don't build, and only compare
and sometimes synchronize...food for thought?
"ChrisR" wrote:
> What?
>
> "Mullin Yu" <mullin_yu@.ctil.com> wrote in message
> news:OREXwwFzEHA.1264@.TK2MSFTNGP12.phx.gbl...
> > as subject.
> >
> > any freeware or done at sql level?
> >
> > thanks!
> >
> >
>
>

how to diff the data of same table of two sql servers

as subject.
any freeware or done at sql level?
thanks!
What?
"Mullin Yu" <mullin_yu@.ctil.com> wrote in message
news:OREXwwFzEHA.1264@.TK2MSFTNGP12.phx.gbl...
> as subject.
> any freeware or done at sql level?
> thanks!
>
|||say, i have a table Order at server A and B,
i want to diff the sql data at two servers.
"ChrisR" <chris@.noemail.com> wrote in message
news:OgXnH3FzEHA.1188@.tk2msftngp13.phx.gbl...
> What?
>
> "Mullin Yu" <mullin_yu@.ctil.com> wrote in message
> news:OREXwwFzEHA.1264@.TK2MSFTNGP12.phx.gbl...
>
|||It's not free but it's real cheap. www.red-gate.com has a product called
data compare that will do what you ask.
Andrew J. Kelly SQL MVP
"Mullin Yu" <mullin_yu@.ctil.com> wrote in message
news:%23tl6F6FzEHA.1596@.TK2MSFTNGP10.phx.gbl...
> say, i have a table Order at server A and B,
> i want to diff the sql data at two servers.
> "ChrisR" <chris@.noemail.com> wrote in message
> news:OgXnH3FzEHA.1188@.tk2msftngp13.phx.gbl...
>
|||I'm with Andrew, I vote Red Gate
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
"Mullin Yu" <mullin_yu@.ctil.com> wrote in message
news:OREXwwFzEHA.1264@.TK2MSFTNGP12.phx.gbl...
> as subject.
> any freeware or done at sql level?
> thanks!
>
|||"Mullin Yu" <mullin_yu@.ctil.com> wrote in message
news:OREXwwFzEHA.1264@.TK2MSFTNGP12.phx.gbl...
> as subject.
> any freeware or done at sql level?
> thanks!
>
What about using a FULL OUTER JOIN and then pulling the values with NULL.
Those would be the difference records. The matches would be the matching
records.
Rick Sawtell
MCT, MCSD, MCDBA
|||Have a look at www.dbghost.com - why bother with a product that doesn't
always work?
DB Ghost? provides you with a fully automated BUILD, COMPARISON and
SYNCHRONIZATION capability for your SQL Server databases and is the only
product on the market that ensures database integrity as DB Ghost? will build
your database directly from your source control system. No other product in
the world does this. No other product can build, compare and synchronize a
target database making it match the source scripts precisely, every single
time, not just sometimes, but every single time. Try and prove us wrong.
Something else that might grab your interest is that an incredible 94% of
our clients (94%!!!) previously purchased our competitors products and soon
found that in the real world, these products let them down time after time.
Don't make the same mistake - why would you buy from our competitors who, for
similar money, can only offer you tools that don't build, and only compare
and sometimes synchronize...food for thought?
"ChrisR" wrote:

> What?
>
> "Mullin Yu" <mullin_yu@.ctil.com> wrote in message
> news:OREXwwFzEHA.1264@.TK2MSFTNGP12.phx.gbl...
>
>

how to determine who has dba privileges?

for sql servers how can i determine who are the dbas?Hi,
Execute the below command:-
sp_helpsrvrolemember 'SYSADMIN'
Who ever comes under this result set will be able to do all the activities
inside that sql server instance.
For Database level DBA previlages, execute
sp_helprolemember 'db_owner'
Thanks
Hari
SQL Server MVP
"nlehrer" <nlehrer@.discussions.microsoft.com> wrote in message
news:CECA1001-EC8C-4EEC-866F-ECEB6B3C5C44@.microsoft.com...
> for sql servers how can i determine who are the dbas?|||thanks. where do i enter the command?
"Hari Prasad" wrote:

> Hi,
> Execute the below command:-
> sp_helpsrvrolemember 'SYSADMIN'
> Who ever comes under this result set will be able to do all the activities
> inside that sql server instance.
> For Database level DBA previlages, execute
> sp_helprolemember 'db_owner'
> Thanks
> Hari
> SQL Server MVP
>
>
> "nlehrer" <nlehrer@.discussions.microsoft.com> wrote in message
> news:CECA1001-EC8C-4EEC-866F-ECEB6B3C5C44@.microsoft.com...
>
>|||Hi,
Login to SQL Server using Query analyzer and enter your commands.
If it is MSDE then execute the commands from command prompt
OSQL -E
This will allow you to go to a SQL prompt, there u could type those
commands.
Thanks
Hari
SQL Server Mvp
"nlehrer" <nlehrer@.discussions.microsoft.com> wrote in message
news:9B6BA8B8-CFA5-4435-B6C1-9906B7DBD8D4@.microsoft.com...[vbcol=seagreen]
> thanks. where do i enter the command?
> "Hari Prasad" wrote:
>|||thank you.
"Hari Prasad" wrote:

> Hi,
> Login to SQL Server using Query analyzer and enter your commands.
>
> If it is MSDE then execute the commands from command prompt
> OSQL -E
> This will allow you to go to a SQL prompt, there u could type those
> commands.
> Thanks
> Hari
> SQL Server Mvp
> "nlehrer" <nlehrer@.discussions.microsoft.com> wrote in message
> news:9B6BA8B8-CFA5-4435-B6C1-9906B7DBD8D4@.microsoft.com...
>
>

Wednesday, March 28, 2012

How to determine the last access date of a DB?

(SQL Server 2000, SP3a)
Hello all!
In my company, we have several development servers that, over time, get cluttered with a
*lot* of "scratch" databases. Is there some inherent mechanism that will show the last
time a database was accessed? If not, is there a relatively simple SQL-based solution
that I could craft that could help track that?
Thanks for any help you can provide!
John PetersonYou have to do some server level settings for that.
SQL Server can log event information for logon attempts and you can view it
by reviewing the errorlog. By turning on the
auditing level of SQL Server.
follow these steps to enable auditing of all/successfull connections with
Enterprise Manager in SQL Server:
Expand a server group.
Right-click a server, and then click Properties.
On the Security tab, under Audit Level, click all/success etc(required
option).
You must stop and restart the server for this setting to take effect.
--
Vishal Parkar
vgparkar@.yahoo.co.in | vgparkar@.hotmail.com|||Login attempts won't tell you if a database has been used or
not. It will only tell you about server logins, not database
access. You would need to monitor this with profiler
going forward. I don't think there is any direct way to get
historical usage if you haven't been monitoring for such.
I suppose you could always drop the databases and then see
who calls complaining to determine if the databases are
being used. And you could use whatever criteria in
determining if you actually backup the databases before
dropping them. That could make for an interesting work day.
-Sue
On Thu, 8 Jul 2004 07:04:50 +0530, "Vishal Parkar"
<REMOVE_THIS_vgparkar@.yahoo.co.in> wrote:
>You have to do some server level settings for that.
>SQL Server can log event information for logon attempts and you can view it
>by reviewing the errorlog. By turning on the
>auditing level of SQL Server.
>follow these steps to enable auditing of all/successfull connections with
>Enterprise Manager in SQL Server:
>Expand a server group.
>Right-click a server, and then click Properties.
>On the Security tab, under Audit Level, click all/success etc(required
>option).
>You must stop and restart the server for this setting to take effect.|||Thanks guys! Yeah...if I could put a trigger or something on sysprocesses, I could
potentially log access to specific databases. I suppose I could have a "monitor" wake up
every X seconds and read the distinct databases that were in use -- but that seems kind of
heavy handed...
"Sue Hoegemeier" <Sue_H@.nomail.please> wrote in message
news:4kdpe010faeumj5c62626q6oo83iegva7m@.4ax.com...
> Login attempts won't tell you if a database has been used or
> not. It will only tell you about server logins, not database
> access. You would need to monitor this with profiler
> going forward. I don't think there is any direct way to get
> historical usage if you haven't been monitoring for such.
> I suppose you could always drop the databases and then see
> who calls complaining to determine if the databases are
> being used. And you could use whatever criteria in
> determining if you actually backup the databases before
> dropping them. That could make for an interesting work day.
> -Sue
> On Thu, 8 Jul 2004 07:04:50 +0530, "Vishal Parkar"
> <REMOVE_THIS_vgparkar@.yahoo.co.in> wrote:
> >You have to do some server level settings for that.
> >
> >SQL Server can log event information for logon attempts and you can view it
> >by reviewing the errorlog. By turning on the
> >auditing level of SQL Server.
> >
> >follow these steps to enable auditing of all/successfull connections with
> >Enterprise Manager in SQL Server:
> >
> >Expand a server group.
> >Right-click a server, and then click Properties.
> >On the Security tab, under Audit Level, click all/success etc(required
> >option).
> >
> >You must stop and restart the server for this setting to take effect.
>|||Triggers on system tables isn't supported though and
sysprocesses is only materialized when used so there
wouldn't even be something to put a trigger on.
-Sue
On Wed, 7 Jul 2004 21:52:56 -0700, "John Peterson"
<j0hnp@.comcast.net> wrote:
>Thanks guys! Yeah...if I could put a trigger or something on sysprocesses, I could
>potentially log access to specific databases. I suppose I could have a "monitor" wake up
>every X seconds and read the distinct databases that were in use -- but that seems kind of
>heavy handed...
>
>"Sue Hoegemeier" <Sue_H@.nomail.please> wrote in message
>news:4kdpe010faeumj5c62626q6oo83iegva7m@.4ax.com...
>> Login attempts won't tell you if a database has been used or
>> not. It will only tell you about server logins, not database
>> access. You would need to monitor this with profiler
>> going forward. I don't think there is any direct way to get
>> historical usage if you haven't been monitoring for such.
>> I suppose you could always drop the databases and then see
>> who calls complaining to determine if the databases are
>> being used. And you could use whatever criteria in
>> determining if you actually backup the databases before
>> dropping them. That could make for an interesting work day.
>> -Sue
>> On Thu, 8 Jul 2004 07:04:50 +0530, "Vishal Parkar"
>> <REMOVE_THIS_vgparkar@.yahoo.co.in> wrote:
>> >You have to do some server level settings for that.
>> >
>> >SQL Server can log event information for logon attempts and you can view it
>> >by reviewing the errorlog. By turning on the
>> >auditing level of SQL Server.
>> >
>> >follow these steps to enable auditing of all/successfull connections with
>> >Enterprise Manager in SQL Server:
>> >
>> >Expand a server group.
>> >Right-click a server, and then click Properties.
>> >On the Security tab, under Audit Level, click all/success etc(required
>> >option).
>> >
>> >You must stop and restart the server for this setting to take effect.
>|||Understood. How about reading database logs? Would *that* show any activity? How would
I even go about doing that?
"Sue Hoegemeier" <Sue_H@.nomail.please> wrote in message
news:q4eqe0htf3hbgg1hme377t5ro5gpcvs351@.4ax.com...
> Triggers on system tables isn't supported though and
> sysprocesses is only materialized when used so there
> wouldn't even be something to put a trigger on.
> -Sue
> On Wed, 7 Jul 2004 21:52:56 -0700, "John Peterson"
> <j0hnp@.comcast.net> wrote:
> >Thanks guys! Yeah...if I could put a trigger or something on sysprocesses, I could
> >potentially log access to specific databases. I suppose I could have a "monitor" wake
up
> >every X seconds and read the distinct databases that were in use -- but that seems kind
of
> >heavy handed...
> >
> >
> >"Sue Hoegemeier" <Sue_H@.nomail.please> wrote in message
> >news:4kdpe010faeumj5c62626q6oo83iegva7m@.4ax.com...
> >> Login attempts won't tell you if a database has been used or
> >> not. It will only tell you about server logins, not database
> >> access. You would need to monitor this with profiler
> >> going forward. I don't think there is any direct way to get
> >> historical usage if you haven't been monitoring for such.
> >> I suppose you could always drop the databases and then see
> >> who calls complaining to determine if the databases are
> >> being used. And you could use whatever criteria in
> >> determining if you actually backup the databases before
> >> dropping them. That could make for an interesting work day.
> >>
> >> -Sue
> >>
> >> On Thu, 8 Jul 2004 07:04:50 +0530, "Vishal Parkar"
> >> <REMOVE_THIS_vgparkar@.yahoo.co.in> wrote:
> >>
> >> >You have to do some server level settings for that.
> >> >
> >> >SQL Server can log event information for logon attempts and you can view it
> >> >by reviewing the errorlog. By turning on the
> >> >auditing level of SQL Server.
> >> >
> >> >follow these steps to enable auditing of all/successfull connections with
> >> >Enterprise Manager in SQL Server:
> >> >
> >> >Expand a server group.
> >> >Right-click a server, and then click Properties.
> >> >On the Security tab, under Audit Level, click all/success etc(required
> >> >option).
> >> >
> >> >You must stop and restart the server for this setting to take effect.
> >>
> >
>|||Well...I noodled on using DBCC LOG to try and see if any of that information might be
germane, but I don't think so (everything is "reset" on a restart of SQL Server).
Then I looked at sysobjects.refdate, but that didn't pan out either.
Is there *any* way to determine the date/time that a particular table was last affected by
an INSERT/UPDATE/DELETE?
There seems to be a similar question here:
http://www.experts-exchange.com/Databases/Microsoft_SQL_Server/Q_20684794.html
But I don't want to sign up just to find out I *can't* do this... ;-)
Thanks for any help you can provide! :-)
"John Peterson" <j0hnp@.comcast.net> wrote in message
news:%23Cd7QnPZEHA.2944@.TK2MSFTNGP11.phx.gbl...
> Understood. How about reading database logs? Would *that* show any activity? How
would
> I even go about doing that?
>
> "Sue Hoegemeier" <Sue_H@.nomail.please> wrote in message
> news:q4eqe0htf3hbgg1hme377t5ro5gpcvs351@.4ax.com...
> > Triggers on system tables isn't supported though and
> > sysprocesses is only materialized when used so there
> > wouldn't even be something to put a trigger on.
> >
> > -Sue
> >
> > On Wed, 7 Jul 2004 21:52:56 -0700, "John Peterson"
> > <j0hnp@.comcast.net> wrote:
> >
> > >Thanks guys! Yeah...if I could put a trigger or something on sysprocesses, I could
> > >potentially log access to specific databases. I suppose I could have a "monitor"
wake
> up
> > >every X seconds and read the distinct databases that were in use -- but that seems
kind
> of
> > >heavy handed...
> > >
> > >
> > >"Sue Hoegemeier" <Sue_H@.nomail.please> wrote in message
> > >news:4kdpe010faeumj5c62626q6oo83iegva7m@.4ax.com...
> > >> Login attempts won't tell you if a database has been used or
> > >> not. It will only tell you about server logins, not database
> > >> access. You would need to monitor this with profiler
> > >> going forward. I don't think there is any direct way to get
> > >> historical usage if you haven't been monitoring for such.
> > >> I suppose you could always drop the databases and then see
> > >> who calls complaining to determine if the databases are
> > >> being used. And you could use whatever criteria in
> > >> determining if you actually backup the databases before
> > >> dropping them. That could make for an interesting work day.
> > >>
> > >> -Sue
> > >>
> > >> On Thu, 8 Jul 2004 07:04:50 +0530, "Vishal Parkar"
> > >> <REMOVE_THIS_vgparkar@.yahoo.co.in> wrote:
> > >>
> > >> >You have to do some server level settings for that.
> > >> >
> > >> >SQL Server can log event information for logon attempts and you can view it
> > >> >by reviewing the errorlog. By turning on the
> > >> >auditing level of SQL Server.
> > >> >
> > >> >follow these steps to enable auditing of all/successfull connections with
> > >> >Enterprise Manager in SQL Server:
> > >> >
> > >> >Expand a server group.
> > >> >Right-click a server, and then click Properties.
> > >> >On the Security tab, under Audit Level, click all/success etc(required
> > >> >option).
> > >> >
> > >> >You must stop and restart the server for this setting to take effect.
> > >>
> > >
> >
>|||The SQL Server logs...not reliably. Unless the databases
have errors all the time that get logged. In terms of the
database log, I don't think you can't get timestamps using
dbcc loginfo or dbcc log. You can check object creation
dates in the database but that won't necessarily mean the
database isn't being used if no one is creating objects. And
even with the logs, if for some reason, they are only
selecting out of the database, that wouldn't do you any good
either.
If you want to read details of log files, you need to use
something like Log Explorer from lumigent -
www.lumigent.com
-Sue
On Thu, 8 Jul 2004 07:42:36 -0700, "John Peterson"
<j0hnp@.comcast.net> wrote:
>Understood. How about reading database logs? Would *that* show any activity? How would
>I even go about doing that?
>
>"Sue Hoegemeier" <Sue_H@.nomail.please> wrote in message
>news:q4eqe0htf3hbgg1hme377t5ro5gpcvs351@.4ax.com...
>> Triggers on system tables isn't supported though and
>> sysprocesses is only materialized when used so there
>> wouldn't even be something to put a trigger on.
>> -Sue
>> On Wed, 7 Jul 2004 21:52:56 -0700, "John Peterson"
>> <j0hnp@.comcast.net> wrote:
>> >Thanks guys! Yeah...if I could put a trigger or something on sysprocesses, I could
>> >potentially log access to specific databases. I suppose I could have a "monitor" wake
>up
>> >every X seconds and read the distinct databases that were in use -- but that seems kind
>of
>> >heavy handed...
>> >
>> >
>> >"Sue Hoegemeier" <Sue_H@.nomail.please> wrote in message
>> >news:4kdpe010faeumj5c62626q6oo83iegva7m@.4ax.com...
>> >> Login attempts won't tell you if a database has been used or
>> >> not. It will only tell you about server logins, not database
>> >> access. You would need to monitor this with profiler
>> >> going forward. I don't think there is any direct way to get
>> >> historical usage if you haven't been monitoring for such.
>> >> I suppose you could always drop the databases and then see
>> >> who calls complaining to determine if the databases are
>> >> being used. And you could use whatever criteria in
>> >> determining if you actually backup the databases before
>> >> dropping them. That could make for an interesting work day.
>> >>
>> >> -Sue
>> >>
>> >> On Thu, 8 Jul 2004 07:04:50 +0530, "Vishal Parkar"
>> >> <REMOVE_THIS_vgparkar@.yahoo.co.in> wrote:
>> >>
>> >> >You have to do some server level settings for that.
>> >> >
>> >> >SQL Server can log event information for logon attempts and you can view it
>> >> >by reviewing the errorlog. By turning on the
>> >> >auditing level of SQL Server.
>> >> >
>> >> >follow these steps to enable auditing of all/successfull connections with
>> >> >Enterprise Manager in SQL Server:
>> >> >
>> >> >Expand a server group.
>> >> >Right-click a server, and then click Properties.
>> >> >On the Security tab, under Audit Level, click all/success etc(required
>> >> >option).
>> >> >
>> >> >You must stop and restart the server for this setting to take effect.
>> >>
>> >
>sql

Monday, March 26, 2012

How to determine SQL version from a command line

Hi, I am trying to run a command line script against 50
SQL servers to determine the SQL ver and SP level
installed but when I run srvinfo -ns this returns way more
info then I require and does not include SP level. I have
run registry searches but this will only give the
installed version of SQL, it will not give me the latest
ver, i.e. if it has had an SP installed or not. Any help
would be great. ThanksYou can use OSQL from the command line to connect to SQL Server. Once
connected issue SELECT @.@.VERSION or use SERVERPROPERTY
Without using SQL you could always do a DIR and seach for sqlservr.exe, from
it's size and file data you should be able to work out which version.
--
HTH
Ryan Waight, MCDBA, MCSE
"Jonathon" <Jonathon_Taaffe@.hotmail.com> wrote in message
news:296d601c3919f$d3ea6f90$a601280a@.phx.gbl...
> Hi, I am trying to run a command line script against 50
> SQL servers to determine the SQL ver and SP level
> installed but when I run srvinfo -ns this returns way more
> info then I require and does not include SP level. I have
> run registry searches but this will only give the
> installed version of SQL, it will not give me the latest
> ver, i.e. if it has had an SP installed or not. Any help
> would be great. Thanks|||Jonathan,
Refer to following url:
http://support.microsoft.com/default.aspx?scid=kb;en-us;q321185
You can run these queries from command prompt, using osql utility by passing quries to -Q
parameter.
--
- Vishal|||You can run 'select @.@.version' with osql in dos.
>--Original Message--
>Hi, I am trying to run a command line script against 50
>SQL servers to determine the SQL ver and SP level
>installed but when I run srvinfo -ns this returns way
more
>info then I require and does not include SP level. I have
>run registry searches but this will only give the
>installed version of SQL, it will not give me the latest
>ver, i.e. if it has had an SP installed or not. Any help
>would be great. Thanks
>.
>|||In article <296d601c3919f$d3ea6f90$a601280a@.phx.gbl>, Jonathon
<Jonathon_Taaffe@.hotmail.com> writes
>Hi, I am trying to run a command line script against 50
>SQL servers to determine the SQL ver and SP level
>installed but when I run srvinfo -ns this returns way more
>info then I require and does not include SP level. I have
>run registry searches but this will only give the
>installed version of SQL, it will not give me the latest
>ver, i.e. if it has had an SP installed or not. Any help
>would be great. Thanks
If you are looking for any SQL Servers then you could try SQL Scan as
well-
http://www.microsoft.com/sql/downloads/securitytools.asp
I use a combination of methods to monitor what servers appear on the
network and in what state.
The registry will tell you which SP you are running, but it will not
tell you if there are any patches on top as well.
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSSQLServer\MSSQLServer\CurrentVers
ion]
The CurrentVersion value gives you the base version, e.g 8.00.194 = SQL
Server 2000 RTM.
The CSDVersion key will then tell you the service pack level, e.g.
8.00.761 = SP3a. Note this is actually quite useful because the TSQL
@.@.VERSION and similar will only give you 8.00.760, which means SP3 or
SP3a. However since I also have the latest security patch installed
@.@.VERSION says 8.00.818, so a combination is often better.
Darren Green (SQL Server MVP)
DTS - http://www.sqldts.com
PASS - the definitive, global community for SQL Server professionals
http://www.sqlpass.org|||I've just tried, it worked without problem.
From a cmd line :-
OSQL -Sservername -Q"select @.@.Version" -E
--
HTH
Ryan Waight, MCDBA, MCSE
"Ray Miao" <rmiao@.bloomberg.com> wrote in message
news:03d801c391b4$f2bded60$a401280a@.phx.gbl...
> You can run 'select @.@.version' with osql in dos.
> >--Original Message--
> >Hi, I am trying to run a command line script against 50
> >SQL servers to determine the SQL ver and SP level
> >installed but when I run srvinfo -ns this returns way
> more
> >info then I require and does not include SP level. I have
> >run registry searches but this will only give the
> >installed version of SQL, it will not give me the latest
> >ver, i.e. if it has had an SP installed or not. Any help
> >would be great. Thanks
> >.
> >

Friday, March 23, 2012

how to determine dbas?

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

Simon

Wednesday, March 21, 2012

How to detect suspect status out of 100 servers in network.

1) Suppose 100 servers...if one goes in suspect db..how can we check which d
atabase is suspect..
all 100 servers are scattered around the world...and we need to immediate th
e server went to suspect database...
Hows that possible...
2)log shipping..
can stand by server in non recovery mode..be used to handle and transactions
whether it is Read only or write and read...
Can we use tempdb in standby server for normal creation and writing in temp
tables..sanjaya
1)
IF (SELECT COUNT(*) FROM master..sysdatabases
WHERE name = @.dbname AND status & 256 = 256) != 1
BEGIN
PRINT 'The database is not in suspect mode.'
RETURN (1)
END
2)
Please refer to BOL
"sanjaya" <anonymous@.discussions.microsoft.com> wrote in message
news:D017C0EC-7C4D-42AE-AFF3-AEF62355A787@.microsoft.com...

quote:

> 1) Suppose 100 servers...if one goes in suspect db..how can we check which

database is suspect..

quote:

> all 100 servers are scattered around the world...and we need to immediate

the server went to suspect database...

quote:

> Hows that possible...
> 2)log shipping..
> can stand by server in non recovery mode..be used to handle and

transactions whether it is Read only or write and read...

quote:

> Can we use tempdb in standby server for normal creation and writing in

temp tables..

quote:

>
>

|||I scan and check the status of all my databases by
actaully querying them -- i.e. trying to run a simple
query. Then, I check the error code to determine what
state the database is in.
You can't make any change to a database in the standby
mode.
Linchi

quote:

>--Original Message--
>1) Suppose 100 servers...if one goes in suspect db..how

can we check which database is suspect..

quote:

>all 100 servers are scattered around the world...and we

need to immediate the server went to suspect database...

quote:

>Hows that possible...
>2)log shipping..
> can stand by server in non recovery mode..be used to

handle and transactions whether it is Read only or write
and read...

quote:

>Can we use tempdb in standby server for normal creation

and writing in temp tables..

quote:

>
>
>.
>

How to detect suspect status out of 100 servers in network.

1) Suppose 100 servers...if one goes in suspect db..how can we check which database is suspect.
all 100 servers are scattered around the world...and we need to immediate the server went to suspect database..
Hows that possible..
2)log shipping.
can stand by server in non recovery mode..be used to handle and transactions whether it is Read only or write and read..
Can we use tempdb in standby server for normal creation and writing in temp tables.sanjaya
1)
IF (SELECT COUNT(*) FROM master..sysdatabases
WHERE name = @.dbname AND status & 256 = 256) != 1
BEGIN
PRINT 'The database is not in suspect mode.'
RETURN (1)
END
2)
Please refer to BOL
"sanjaya" <anonymous@.discussions.microsoft.com> wrote in message
news:D017C0EC-7C4D-42AE-AFF3-AEF62355A787@.microsoft.com...
> 1) Suppose 100 servers...if one goes in suspect db..how can we check which
database is suspect..
> all 100 servers are scattered around the world...and we need to immediate
the server went to suspect database...
> Hows that possible...
> 2)log shipping..
> can stand by server in non recovery mode..be used to handle and
transactions whether it is Read only or write and read...
> Can we use tempdb in standby server for normal creation and writing in
temp tables..
>
>|||I scan and check the status of all my databases by
actaully querying them -- i.e. trying to run a simple
query. Then, I check the error code to determine what
state the database is in.
You can't make any change to a database in the standby
mode.
Linchi
>--Original Message--
>1) Suppose 100 servers...if one goes in suspect db..how
can we check which database is suspect..
>all 100 servers are scattered around the world...and we
need to immediate the server went to suspect database...
>Hows that possible...
>2)log shipping..
> can stand by server in non recovery mode..be used to
handle and transactions whether it is Read only or write
and read...
>Can we use tempdb in standby server for normal creation
and writing in temp tables..
>
>
>.
>

How to detect sql servers on the net

Hi
Is there a way to detect any instances of sql server on the network.
Like all the Microsoft configuration tools do...
If there exists one, is it also possible to use it within the .net
environment?
Regards
StephanStephan Zaubzer wrote:

> Hi
> Is there a way to detect any instances of sql server on the network.
> Like all the Microsoft configuration tools do...
> If there exists one, is it also possible to use it within the .net
> environment?
> Regards
> Stephan
Use OSQL -L|||I was rather looking for a way to use it within an application
In my application I need a config window where I specify to which server
to connect to...
And I wanna show all available servers in the config window...
amish wrote:
> Stephan Zaubzer wrote:
>
>
> Use OSQL -L
>|||Stephan Zaubzer <stephan.zaubzer@.schendl.at> wrote in
news:438F2929.5030501@.schendl.at:

> I was rather looking for a way to use it within an application
> In my application I need a config window where I specify to which
> server to connect to...
> And I wanna show all available servers in the config window...
>
You can do this in two ways;
1. You can ask for a DataSourceEnumerator from your provider (the method
name may not be exactly that, but you can find it). 2. Use SMO to
enumerate the servers on the network.
Niels
****************************************
**********
* Niels Berglund
* http://staff.develop.com/nielsb
* nielsb@.no-spam.develop.com
* "A First Look at SQL Server 2005 for Developers"
* http://www.awprofessional.com/title/0321180593
****************************************
**********|||... and to my knowledge, this is still not reliable since broadcasting is u
sed. So a server might be
in another domain, might not respond in time etc and hence will not show up.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Niels Berglund" <nielsb@.develop.com> wrote in message
news:Xns971FB26F1D379nielsbdevelopcom@.20
7.46.248.16...
> Stephan Zaubzer <stephan.zaubzer@.schendl.at> wrote in
> news:438F2929.5030501@.schendl.at:
>
> You can do this in two ways;
> 1. You can ask for a DataSourceEnumerator from your provider (the method
> name may not be exactly that, but you can find it). 2. Use SMO to
> enumerate the servers on the network.
> Niels
>
> --
> ****************************************
**********
> * Niels Berglund
> * http://staff.develop.com/nielsb
> * nielsb@.no-spam.develop.com
> * "A First Look at SQL Server 2005 for Developers"
> * http://www.awprofessional.com/title/0321180593
> ****************************************
**********|||"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote
in news:uCt#Ilx9FHA.2816@.tk2msftngp13.phx.gbl:

> ... and to my knowledge, this is still not reliable since broadcasting
> is used. So a server might be in another domain, might not respond in
> time etc and hence will not show up.
>
absolutely!!!! The methods I mentioned earlier are very brittle, and you
can not rely 100% on them. Also, as they use broadcast the methods are
sloooow.
Niels
****************************************
**********
* Niels Berglund
* http://staff.develop.com/nielsb
* nielsb@.no-spam.develop.com
* "A First Look at SQL Server 2005 for Developers"
* http://www.awprofessional.com/title/0321180593
****************************************
**********|||DataSourcEnumerator exists only in .net 2.0
Since I am working in VS.net 2003 and .net 1.1 I can't use it.
What exactly is SMO and how would I use it?
regards
Stephan
Niels Berglund wrote:
> Stephan Zaubzer <stephan.zaubzer@.schendl.at> wrote in
> news:438F2929.5030501@.schendl.at:
>
>
> You can do this in two ways;
> 1. You can ask for a DataSourceEnumerator from your provider (the method
> name may not be exactly that, but you can find it). 2. Use SMO to
> enumerate the servers on the network.
> Niels
>|||Stephan Zaubzer <stephan.zaubzer@.schendl.at> wrote in
news:uDd8Y019FHA.252@.TK2MSFTNGP15.phx.gbl:
> DataSourcEnumerator exists only in .net 2.0
> Since I am working in VS.net 2003 and .net 1.1 I can't use it.
> What exactly is SMO and how would I use it?
> regards
> Stephan
> Niels Berglund wrote:
[snip]
Sorry Stephan abour DataSourceEnum. I'm afraid that SMO is in that case
not any better either. It is the new management object hierarchy in SQL
2005.
Niels
****************************************
**********
* Niels Berglund
* http://staff.develop.com/nielsb
* nielsb@.no-spam.develop.com
* "A First Look at SQL Server 2005 for Developers"
* http://www.awprofessional.com/title/0321180593
****************************************
**********

Monday, March 19, 2012

How to detach replication dbs?

There are two databases on two web servers, db01 is on server01, db01_replica is on server02, db02 is on server02, db02_replica is on server01. db01 and db02 are both for one system. Each time after doing performance test, I have to recover databses. I copy the data files in a folder, try to use detach and attach to recover databases. But with two replication dbs, I don't know how to do it. The replication db should also be recovered.

Detach/attach is breaking the link between replication and these databases.

Depending on some specifics, there may be several ways to get what you want to do.

First, knowing what version and edition will tell us what tools we have to work with.

Off the top of my head, you could tear down your replication setup, detach/attach, and recreate the replication config. Not pretty, but you could automate it with scripts.

You can use backup/restore instead of detach/attach.

You could possibly create database snapshots of all the databases, and then to reset use RESTORE DATABASE <dbname> FROM DATABASE_SNAPSHOT=<snapshot name> to limit the amount of data being moved.

|||The situation is: my project is partitioned in two db servers, which all use SQL Server 2005. db01 is on server01 and has replication in server02, db02 is on server02 and has replication in server01. Each time there is a new build of the project, I have to do performance test for it. Unluckily, prepare for the performance test data need 2 days, each time do deployment for the new build, the db will be re-deployed, so all data will lose. I have keep the old build database and data, the most important thing is how to resotore data into new build. I ever used "bcp" to export and import data. But I don't know when replication will end up. So I think maybe detach/attach can help.

How to detach a database with replication db?

Monday, March 12, 2012

How to deploy the report on customer side

Hi there,
I am making some reports for some customers, I just wonder how they
deploy the reports - I don't know what their servers' name. Do they
need .NET on their server side so they can open my solution to change
deploy path and deploy method?
Thanks in advance.
HenryHenry,
See my reply to this thread:
http://groups.google.com/groups?hl=en&lr=&threadm=eQ4wHCDwEHA.2316%40TK2MSFTNGP15.phx.gbl&rnum=1&prev=/groups%3Fq%3D%2522Delivering%2BRS%2BReports%2Bto%2Ba%2Bcustomer%2522%26hl%3Den%26lr%3D%26selm%3DeQ4wHCDwEHA.2316%2540TK2MSFTNGP15.phx.gbl%26rnum%3D1
Basically, the easiest way to do this is to write a rss script which will be
executed on your client's Report Server box. Your script can take input
variables which you can use to pass settings, e.g. the computer name, etc.
--
Hope this helps.
---
Teo Lachev, MVP [SQL Server], MCSD, MCT
Author: "Microsoft Reporting Services in Action"
Publisher website: http://www.manning.com/lachev
Buy it from Amazon.com: http://shrinkster.com/eq
Home page and blog: http://www.prologika.com/
---
"Henry" <fanh@.tycoelectronics.com> wrote in message
news:d2a411ae.0411020706.535ffafe@.posting.google.com...
> Hi there,
> I am making some reports for some customers, I just wonder how they
> deploy the reports - I don't know what their servers' name. Do they
> need .NET on their server side so they can open my solution to change
> deploy path and deploy method?
> Thanks in advance.
> Henry

Wednesday, March 7, 2012

How to delete the log shipping on the sql server2000?

I have two servers installed sql server2000,i setuped log shipping on every
server!but now how to do delete log shipping?
--
Study everyday!Hi
Have you checked out the sp_delete_log_shipping... procedures including
sp_delete_log_shipping_monitor_info?
John
"lansehai-chen@.hotmail.com" wrote:

> I have two servers installed sql server2000,i setuped log shipping on ever
y
> server!but now how to do delete log shipping?
> --
> Study everyday!

How to delete the log shipping on the sql server2000?

I have two servers installed sql server2000,i setuped log shipping on every
server!but now how to do delete log shipping?
--
Study everyday!Hi
Have you checked out the sp_delete_log_shipping... procedures including
sp_delete_log_shipping_monitor_info?
John
"lansehai-chen@.hotmail.com" wrote:
> I have two servers installed sql server2000,i setuped log shipping on every
> server!but now how to do delete log shipping?
> --
> Study everyday!

Sunday, February 19, 2012

How To Defrag SQL Server 2000

Hi everyone, I am fairly new with SQL Server and need a little bit of help in regards to boosting my SQL servers performance. I have been advised that defraging SQL Server will definitely help solve my issue however I have never performed this function before. Can someone please help guide me through the necessary steps to execute this task. Thanks in advance.I have been advised that defraging SQL Server will definitely help solve my issue however I have never performed this function before.
Only one setting cannot solve your problem, you have to consider lots of things like server memory settings, disk space, indexes, query optimizing etc.

You can use database maintenance wizard from Enterprise Manager-> Tools-> Database maintenance planner.

You can fragment your indexes for better performance, syntax is given below.

DBCC INDEXDEFRAG
( { database_name | database_id | 0 }
, { table_name | table_id | 'view_name' | view_id }
, { index_name | index_id }
) [ WITH NO_INFOMSGS ]|||Only one setting cannot solve your problem, you have to consider lots of things like server memory settings, disk space, indexes, query optimizing etc.

You can use database maintenance wizard from Enterprise Manager-> Tools-> Database maintenance planner.

You can fragment your indexes for better performance, syntax is given below.

DBCC INDEXDEFRAG
( { database_name | database_id | 0 }
, { table_name | table_id | 'view_name' | view_id }
, { index_name | index_id }
) [ WITH NO_INFOMSGS ]
I am curious; is there any value in doing a backup/restore?

I have a daily scheduled run of Executive Software's Diskkeeper on all the servers. That keeps the files defragged on the file-system level, but of course doesn't reorder anything within the database.

As I understand it, the concept of Defragmenetation offers an optimization of physical aspects of the disk drive (rotations, head movements) and the software activities of piecing together the fragments. It follows that having all the bits of an index in order would have a similar effect (as you described above).

I guess in a database there's also a matter of eliminating all the holes left by prior deletes and of spreading indexes out more intelligently.

So then; I'm displaying a complete ignorance of "database layer fragmentation". Am I missing a lot of fundamentals in my thinking?

Question: Would it be a benifit to backup-then-restore a database?|||Backing up and restoring a database has no effect on fragmentation. Database fragmentation that is, the DBA's nerves will become highly fragmented if this sort of thing is implemented. In Oracle, you can export and import tables to remove fragmentation, which may be what you are thinking of. In SQL Server, a backup collects all pages that have data on them, and stashes them away. On a restore, the data pages are simply rewritten in place. without any moving of data around the pages.

Database fragmentation happens mainly with deletes, sometimes with updates, and somewhat less frequently with inserts (depending on your indexes).

Suppose you have a data page that originally has 20 entries (rows) in it. When you read in that page, you get 20 rows in memory. Suppose further that 19 of these rows are deleted. Now when you read in the same 8KB page, you only get 1 row of data. The space taken up by the rows that were there is not reclaimed automatically, and depending on insert/update activity and clustered index layout may not ever be reclaimed unless you rebuild the indexes. Rebuilding the indexes has the effect of re-arranging, or regenerating the entries packed closer together making read operations more efficient.

Here is a link to a decent paper about it. Note, users tend to not notice the difference, until tables get above 10,000 pages or so.
http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/ss2kidbp.mspx|||I am curious; is there any value in doing a backup/restore?

yes, there is some value|||Check this, a single web page consist of various subjects for SQL Server Maintenance.

http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/default.mspx

Friday, March 30, 2012

how to diff the data of same table of two sql servers

how to diff the data of same table of two sql servers

how to diff the data of same table of two sql servers

how to determine who has dba privileges?

Wednesday, March 28, 2012

How to determine the last access date of a DB?

How to determine the last access date of a DB?

How to determine the last access date of a DB?

Monday, March 26, 2012

How to determine SQL version from a command line

Friday, March 23, 2012

how to determine dbas?

Wednesday, March 21, 2012

How to detect suspect status out of 100 servers in network.

How to detect suspect status out of 100 servers in network.

How to detect sql servers on the net

Monday, March 19, 2012

How to detach replication dbs?

How to detach a database with replication db?

Monday, March 12, 2012

How to deploy the report on customer side

Wednesday, March 7, 2012

How to delete the log shipping on the sql server2000?

How to delete the log shipping on the sql server2000?

Sunday, February 19, 2012

How To Defrag SQL Server 2000

define Hierarchy

Blog Archive

About Me