No longer need default instance of SQL Server

Currently I have a server that has two instances of SQL Server that are
both heavily used. We are moving the databases that are on the default
instance of this server to a new server. Since the old server will
only need one instance (the current named instance) of SQL Server is
there a good way to remove the default instance and make the named
instance the default? If this change is possible will the name of the
current named instance need to be changed? Will all of the programs
currently accessing the named instance need to change their connection
string?AJ (ablanchard@.mnlife.com) writes:
> Currently I have a server that has two instances of SQL Server that are
> both heavily used. We are moving the databases that are on the default
> instance of this server to a new server. Since the old server will
> only need one instance (the current named instance) of SQL Server is
> there a good way to remove the default instance and make the named
> instance the default? If this change is possible will the name of the
> current named instance need to be changed? Will all of the programs
> currently accessing the named instance need to change their connection
> string?

You could uninstall the default instance and keep the named instance
as it is. To be honest, I have not actually tried it, but I am quite
convinced that it is possible.

I don't see the point with making the named instance the default
instance, not the least if you would have to go and change
connection strings.

And I don't think there is a simple way to rename an instance. Probably
the easiest method would be to detach the databases, and attach them
to the existing default instance. If you really want to have them a
default instance, that is.

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

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Thanks for your suggestion. I guess I just thought there had to be a
default instance. In a test environment I created a default and a
named instance and then removed the default instance and everthing
seemed to be fine. It seems this will be my solution.

AJ

No Local instance with SQLServer2005 Dev. Ed.

(New to SQL Server Installs)

I installed SQL Server 2005 Developer Edition on a WinXP-SP2
workstation and can see some enterprise databases on the network just
fine.

My problem is I cannot register a local database on the local drive for
testing/education purposes.
(I accepted all of the defaults on the install and thought all was
well.)

There are a couple of small databases that are in development in our
group and I need to work on when offline from the network.

Short of uninstalling the whole product and doing it over, what steps
can I take to remedy this so I will be able to work with DB's locally?

Thanks.My problem is I cannot register a local database on the local drive for

Quote:

Originally Posted by

testing/education purposes.

I'm not sure what you mean by 'register' a local database. A database can
be created with CREATE DATABASE (including attach) or RESTORE.

Quote:

Originally Posted by

There are a couple of small databases that are in development in our
group and I need to work on when offline from the network.

I suggest you create a backup of the desired databases and then restore.
You can use SQL Server Management Studio for this.

--
Hope this helps.

Dan Guzman
SQL Server MVP

"RLN" <rlntemp-newsgroup@.yahoo.comwrote in message
news:1161270087.986650.261700@.b28g2000cwb.googlegr oups.com...

Quote:

Originally Posted by

(New to SQL Server Installs)
>
I installed SQL Server 2005 Developer Edition on a WinXP-SP2
workstation and can see some enterprise databases on the network just
fine.
>
My problem is I cannot register a local database on the local drive for
testing/education purposes.
(I accepted all of the defaults on the install and thought all was
well.)
>
There are a couple of small databases that are in development in our
group and I need to work on when offline from the network.
>
Short of uninstalling the whole product and doing it over, what steps
can I take to remedy this so I will be able to work with DB's locally?
>
Thanks.
>

|||Dan,
Please accept my 2 apologies; first for a late reply, and second if I
am using SQL-Server terms incorrectly.

On a 2nd test box (WinXP-SP2) I have Sql Server 2005 Developer Edition
installed.

What works:
-When I launch Mgmt Studio, I am able to connect to an enterprise SQL
Server box successfully on our network.
-In Mgmt Studio, when I click on "View/Registered Servers", I can see
"W4082107/test". This is my test box. I am under the impression that
"W4082107/test" is a "local" SQL Server where I can copy sample
databases off of the network and compose and work with copies of
Northwind, etc.

What does not work:
-I am not able to have a local instance of SQL server working on my
local drive to do some practice work with SQL Server.

I'll take a look on MSDN, TechNet and the MSKB for a page (maybe a
'step-by-step') that references setting up a local instance of SQL
Server when it is working with the Enterprise servers just fine. I
thought I could install SQLServer Express to get my local databases to
work, but MS says the Developer Edition and Express cannot exist on the
same box.

I hope this clarifies my original request.

Dan Guzman wrote:

Quote:

Originally Posted by

Quote:

Originally Posted by

My problem is I cannot register a local database on the local drive for
testing/education purposes.

>
I'm not sure what you mean by 'register' a local database. A database can
be created with CREATE DATABASE (including attach) or RESTORE.
>

Quote:

Originally Posted by

There are a couple of small databases that are in development in our
group and I need to work on when offline from the network.

>
I suggest you create a backup of the desired databases and then restore.
You can use SQL Server Management Studio for this.
>
--
Hope this helps.
>
Dan Guzman
SQL Server MVP
>
"RLN" <rlntemp-newsgroup@.yahoo.comwrote in message
news:1161270087.986650.261700@.b28g2000cwb.googlegr oups.com...

Quote:

Originally Posted by

(New to SQL Server Installs)

I installed SQL Server 2005 Developer Edition on a WinXP-SP2
workstation and can see some enterprise databases on the network just
fine.

My problem is I cannot register a local database on the local drive for
testing/education purposes.
(I accepted all of the defaults on the install and thought all was
well.)

There are a couple of small databases that are in development in our
group and I need to work on when offline from the network.

Short of uninstalling the whole product and doing it over, what steps
can I take to remedy this so I will be able to work with DB's locally?

Thanks.

|||RLN (rlntemp-newsgroup@.yahoo.com) writes:

Quote:

Originally Posted by

On a 2nd test box (WinXP-SP2) I have Sql Server 2005 Developer Edition
installed.
>
What works:
-When I launch Mgmt Studio, I am able to connect to an enterprise SQL
Server box successfully on our network.
-In Mgmt Studio, when I click on "View/Registered Servers", I can see
"W4082107/test". This is my test box. I am under the impression that
"W4082107/test" is a "local" SQL Server where I can copy sample
databases off of the network and compose and work with copies of
Northwind, etc.

Yes and no. You can backup a database on the big SQL Server, and
restore the database on your machine. But this presumes that there
is a network share somewhere, but both you and SQL Server can access.

You can also use the Copy Database Wizard to copy a database with the
SMO method, which copies the database object by object. But CDW is
very buggy, and may not copy everything.

Quote:

Originally Posted by

What does not work:
-I am not able to have a local instance of SQL server working on my
local drive to do some practice work with SQL Server.

In what way does it not work? Since you provide no information, I
will ask a couple of basic questions:

* You are certain that you installed the Database Engine and not just
only the tools?
* If so, have you ensured that SQL Server is running? Check this in the
SQL Configuration Manager.
* If it is running, do you connect to it properly. I assume that W4082107
is your workstation. The /test should probably be \test, if you have
installed SQL 2005 as a named instance. You can view the instance
names in the Configuration Manager.

Quote:

Originally Posted by

I'll take a look on MSDN, TechNet and the MSKB for a page (maybe a
'step-by-step') that references setting up a local instance of SQL
Server when it is working with the Enterprise servers just fine. I
thought I could install SQLServer Express to get my local databases to
work, but MS says the Developer Edition and Express cannot exist on the
same box.

That is not correct. It's perfectly possible to have instance of both
Developer and Expres Edition on the same box. What is not possible is
to install SQL Server Management Studio Express if you have the big
Mgmt Studio installed and vice versa.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspxsql

No Items in EM

I have a SQL 2000 Server that is still functioning however within
Enterprise Manager there are "no items" listed under databases. Nothing
in management or replication either.
Hi
This is databases not being listed and not servers? If it is actually the
latter this may be occuring
http://support.microsoft.com/default.aspx?scid=kb;en-us;323280. If it is
databases have you checked what EM from a different machine is reporting?
Have you tried re-registering the server again?
John
"J1C" wrote:

> I have a SQL 2000 Server that is still functioning however within
> Enterprise Manager there are "no items" listed under databases. Nothing
> in management or replication either.
>
|||If you right-click on the databases folder and click "New Database", do
you get an arithmetic overflow error?
|||I am having this same problem, and yes i do get the arithmetic overflow
error. Any ideas?
Thanks in advance.
"JG" wrote:

> If you right-click on the databases folder and click "New Database", do
> you get an arithmetic overflow error?
>

No Items in EM

I have a SQL 2000 Server that is still functioning however within
Enterprise Manager there are "no items" listed under databases. Nothing
in management or replication either.Hi
This is databases not being listed and not servers? If it is actually the
latter this may be occuring
http://support.microsoft.com/default.aspx?scid=kb;en-us;323280. If it is
databases have you checked what EM from a different machine is reporting?
Have you tried re-registering the server again?
John
"J1C" wrote:
> I have a SQL 2000 Server that is still functioning however within
> Enterprise Manager there are "no items" listed under databases. Nothing
> in management or replication either.
>|||If you right-click on the databases folder and click "New Database", do
you get an arithmetic overflow error?|||I am having this same problem, and yes i do get the arithmetic overflow
error. Any ideas?
Thanks in advance.
"JG" wrote:
> If you right-click on the databases folder and click "New Database", do
> you get an arithmetic overflow error?
>

No Items in EM

I have a SQL 2000 Server that is still functioning however within
Enterprise Manager there are "no items" listed under databases. Nothing
in management or replication either.Hi
This is databases not being listed and not servers? If it is actually the
latter this may be occuring
http://support.microsoft.com/defaul...b;en-us;323280. If it is
databases have you checked what EM from a different machine is reporting?
Have you tried re-registering the server again?
John
"J1C" wrote:

> I have a SQL 2000 Server that is still functioning however within
> Enterprise Manager there are "no items" listed under databases. Nothing
> in management or replication either.
>|||If you right-click on the databases folder and click "New Database", do
you get an arithmetic overflow error?|||I am having this same problem, and yes i do get the arithmetic overflow
error. Any ideas?
Thanks in advance.
"JG" wrote:

> If you right-click on the databases folder and click "New Database", do
> you get an arithmetic overflow error?
>

No entries show up in SQL Server Profiler trace

Hi,
I have inherited a SQL Server 2000 machine with 59 databases on it,
several of which I suspect are no longer used.
I am setting up a SQL Profiler trace to show me which databases are
still being accessed, and by which machine/user.
There are 4 other SQL Servers in use here, too, running SQL Server 6.5,
7, 2000 and 2005.
The problem I'm encountering is this:
I start the Profiler trace on SQL Server machine A
>From my local machine's Query Analyzer, I then query a table in one of
the suspect databases on machine A, via a linked server entry in my
local SQL Server instance
None of this query activity against the database is logged in Profiler
on machine A
What am I missing?
Thanks in advance-
Jason
I figured it out. I am now monitoring the event class "Security
Audit->Audit Object Persmission Event", and bringing back the data
columns ObjectName, LoginName, and Hostname to know what's being looked
at, by whom, and from what machine.
JasonReber@.gmail.com wrote:
> Hi,
> I have inherited a SQL Server 2000 machine with 59 databases on it,
> several of which I suspect are no longer used.
> I am setting up a SQL Profiler trace to show me which databases are
> still being accessed, and by which machine/user.
> There are 4 other SQL Servers in use here, too, running SQL Server 6.5,
> 7, 2000 and 2005.
> The problem I'm encountering is this:
> I start the Profiler trace on SQL Server machine A
> the suspect databases on machine A, via a linked server entry in my
> local SQL Server instance
> None of this query activity against the database is logged in Profiler
> on machine A
> What am I missing?
> Thanks in advance-
> Jason

No entries show up in SQL Server Profiler trace

Hi,
I have inherited a SQL Server 2000 machine with 59 databases on it,
several of which I suspect are no longer used.
I am setting up a SQL Profiler trace to show me which databases are
still being accessed, and by which machine/user.
There are 4 other SQL Servers in use here, too, running SQL Server 6.5,
7, 2000 and 2005.
The problem I'm encountering is this:
I start the Profiler trace on SQL Server machine A
>From my local machine's Query Analyzer, I then query a table in one of
the suspect databases on machine A, via a linked server entry in my
local SQL Server instance
None of this query activity against the database is logged in Profiler
on machine A
What am I missing?
Thanks in advance-
JasonI figured it out. I am now monitoring the event class "Security
Audit->Audit Object Persmission Event", and bringing back the data
columns ObjectName, LoginName, and Hostname to know what's being looked
at, by whom, and from what machine.
JasonReber@.gmail.com wrote:
> Hi,
> I have inherited a SQL Server 2000 machine with 59 databases on it,
> several of which I suspect are no longer used.
> I am setting up a SQL Profiler trace to show me which databases are
> still being accessed, and by which machine/user.
> There are 4 other SQL Servers in use here, too, running SQL Server 6.5,
> 7, 2000 and 2005.
> The problem I'm encountering is this:
> I start the Profiler trace on SQL Server machine A
> the suspect databases on machine A, via a linked server entry in my
> local SQL Server instance
> None of this query activity against the database is logged in Profiler
> on machine A
> What am I missing?
> Thanks in advance-
> Jason

No entries show up in SQL Server Profiler trace

Hi,
I have inherited a SQL Server 2000 machine with 59 databases on it,
several of which I suspect are no longer used.
I am setting up a SQL Profiler trace to show me which databases are
still being accessed, and by which machine/user.
There are 4 other SQL Servers in use here, too, running SQL Server 6.5,
7, 2000 and 2005.
The problem I'm encountering is this:
I start the Profiler trace on SQL Server machine A
>From my local machine's Query Analyzer, I then query a table in one of
the suspect databases on machine A, via a linked server entry in my
local SQL Server instance
None of this query activity against the database is logged in Profiler
on machine A
What am I missing?
Thanks in advance-
JasonI figured it out. I am now monitoring the event class "Security
Audit->Audit Object Persmission Event", and bringing back the data
columns ObjectName, LoginName, and Hostname to know what's being looked
at, by whom, and from what machine.
JasonReber@.gmail.com wrote:
> Hi,
> I have inherited a SQL Server 2000 machine with 59 databases on it,
> several of which I suspect are no longer used.
> I am setting up a SQL Profiler trace to show me which databases are
> still being accessed, and by which machine/user.
> There are 4 other SQL Servers in use here, too, running SQL Server 6.5,
> 7, 2000 and 2005.
> The problem I'm encountering is this:
> I start the Profiler trace on SQL Server machine A
> >From my local machine's Query Analyzer, I then query a table in one of
> the suspect databases on machine A, via a linked server entry in my
> local SQL Server instance
> None of this query activity against the database is logged in Profiler
> on machine A
> What am I missing?
> Thanks in advance-
> Jason

No dbo for a database - but there are db_owner users

I have a number of user databases who have lost their owner. That is, displaying properties for the database says the owner is unknown.

I assume that any user for such a database that's assigned to the db_owner role can admin everything in this database? Which means that the lack of a dbo doesn't cause any other problem than making it impossible to run a sp_helpdb for the database, or...?

Of course I'd like to make someone the owner, but it seems difficult to make an already existing database user the owner, without dropping the user and re-creating it.Methinks you need sp_changedbowner (http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_sp_ca-cz_30s2.asp), which makes the fix trivial anywhere you can execute a stored procedure (such as in Query Analyzer).

-PatP|||Yes, but it doesn't work if the user to be the new owner is already a user in that database. Seems like I need to drop the user and re-create it?|||Yep. Otherwise, SQL Server would have to make a choice about what permissions to grant that user. Especially if that user were dropped from db_owners. The user should see no difference, unless they have created objects under their own username.

No Databases in Enterprise Manager

SQL - 2000 running on Win2k Server
I am unable to view my databases in enterprise manager but can see them in Query Analyzer/ODBC. I had deleted a bunch of unused databases last night and after reboot this morning Enterprise manager will not display any of my databases. What have I done?
rwjohn1@.sbcglobal.net
Posted using Wimdows.net NntpNews Component -
Post Made from http://www.SqlJunkies.com/newsgroups Our newsgroup engine supports Post Alerts, Ratings, and Searching.
Try deleting the registered server and then register it
again in Enterprise Manager.
In Enterprise Manager, right click on the server, select
Delete SQL Server Registration. Then right click on the
server group and select new server registration to register
the server again.
-Sue
On Thu, 28 Oct 2004 12:04:36 -0700, SqlJunkies User
<User@.-NOSPAM-SqlJunkies.com> wrote:

>SQL - 2000 running on Win2k Server
>I am unable to view my databases in enterprise manager but can see them in Query Analyzer/ODBC. I had deleted a bunch of unused databases last night and after reboot this morning Enterprise manager will not display any of my databases. What have I done
?
>rwjohn1@.sbcglobal.net
>--
>Posted using Wimdows.net NntpNews Component -
>Post Made from http://www.SqlJunkies.com/newsgroups Our newsgroup engine supports Post Alerts, Ratings, and Searching.

No Databases in Enterprise Manager

SQL - 2000 running on Win2k Server
I am unable to view my databases in enterprise manager but can see them in Q
uery Analyzer/ODBC. I had deleted a bunch of unused databases last night an
d after reboot this morning Enterprise manager will not display any of my da
tabases. What have I done?
rwjohn1@.sbcglobal.net
Posted using Wimdows.net NntpNews Component -
Post Made from http://www.SqlJunkies.com/newsgroups Our newsgroup engine sup
ports Post Alerts, Ratings, and Searching.Try deleting the registered server and then register it
again in Enterprise Manager.
In Enterprise Manager, right click on the server, select
Delete SQL Server Registration. Then right click on the
server group and select new server registration to register
the server again.
-Sue
On Thu, 28 Oct 2004 12:04:36 -0700, SqlJunkies User
<User@.-NOSPAM-SqlJunkies.com> wrote:

>SQL - 2000 running on Win2k Server
>I am unable to view my databases in enterprise manager but can see them in Query An
alyzer/ODBC. I had deleted a bunch of unused databases last night and after reboot
this morning Enterprise manager will not display any of my databases. What have I d
one
?
>rwjohn1@.sbcglobal.net
>--
>Posted using Wimdows.net NntpNews Component -
>Post Made from http://www.SqlJunkies.com/newsgroups Our newsgroup engine supports P
ost Alerts, Ratings, and Searching.

No compatibilty level 90 option after upgrade from SQL 2000 to 2005

I did an in place upgrade of my SQL 2000 server to SQL 2005.

Now all the system and user databases are set to compatibilty mode 80 and in the options tab there is no compatibilty mode 90 option.

How can I set the databasbes to compatibility mode 90?

Thanks!

Stange thing, did you try using the TSQL command for that ?

sp_dbcmptlevel [ [ @.dbname = ] name ]
[ , [ @.new_cmptlevel = ] version ]
90 = SQL Server 2005

HTH, Jens Suessmeyer.

http://www.sqlserver2005.de

Nightly Maintenance Backup Plan fails -- no trace of why

We have been running a backup plan (created using the sql 2005 Maintenance wizard) for some time. It failed last night but all databases seemed to be backed up.

I looked at the ERRORLOG in <mssql2005>\mssql.1\log as well as

"Servname DB Backup Plan_20070118184358.txt"

but found no errors. Where else can I look to see what went wrong?

TIA,

barkingdog

You can also check the logs of the Sql Server Agent job corresponding to the maintenance plan.

Thanks,

Kuntal

|||

>>> check the logs of the Sql Server Agent job

Where are these located? (Are you referring to SQLAgent.OUT? It had nothing of interest.)

Barkingdog

|||

In Sql 2005, for every maintenance plan an Sql Agent job is created by the same name. You can view its logs as Sql Server Agent -> Jobs -> select the job & right click to View History.

Hope that helps

Kuntal

|||

What was the error under the scheduled job for this maintenance plan?

Also try to run thsi manually from SSMS to see the execution, also ensure the SQLAgent account has required privileges to run this job. ALso check what changes have been since last time when this job was running succesfsully.

Next step after Access?

I've been an Access user for years designing and running desktop database
app's. My work, however, has taken the size of my Access databases to half a
million records and 1.5GB. Access completely crawls at this level.
Furthermore, I need to perform more statistical analysis that Access seems to
be designed for. I feel like I'm needing to take the next step in database
usage. Unfortunately, there don't seem to be any other "desktop" database
app's that are significantly more capable than Access. Thus, I'm guessing I
need to bite the bullet and begrudgingly buy and learn the server /
enterprise database software. What would be the best one to ease into for a
MS Access user like me? Would it be MS SQL Server Personal, for instance?
What's the learning curve like for an Access user? And finally, will it run
on a desktop computer running Windows XP?
Bill_S wrote:
> I've been an Access user for years designing and running desktop
> database app's. My work, however, has taken the size of my Access
> databases to half a million records and 1.5GB. Access completely
> crawls at this level. Furthermore, I need to perform more statistical
> analysis that Access seems to be designed for. I feel like I'm
> needing to take the next step in database usage. Unfortunately,
> there don't seem to be any other "desktop" database app's that are
> significantly more capable than Access. Thus, I'm guessing I need to
> bite the bullet and begrudgingly buy and learn the server /
> enterprise database software. What would be the best one to ease
> into for a MS Access user like me? Would it be MS SQL Server
> Personal, for instance? What's the learning curve like for an Access
> user? And finally, will it run on a desktop computer running Windows
> XP?
Personal Edition only exists as an add-on for Standard/EE/and Developer.
You can download the MS Data Engine (MSDE) - which is essentially a
stripped down version of SQL Server 2000 without any client tools and a
2GB limit on database size. It may not take you very far, given you're
already at 1.5GB, but at least it's free. You can download the MS Web
Data Administrator to help manage the database.
If this is a development project, you can purchase Developer Edition
from MS for about $50. It's obviously not licensed for use in
production.
You'll need to read about SQL Server to get started. There are number of
good web sites (sqlserverperformance.com, SQLServerCentral.com, etc).
Get a book as well - I prefer Kalen Delaney's Inside SQL Server 2000 -
MS Press.
David Gugick
Imceda Software
www.imceda.com

Next step after Access?

I've been an Access user for years designing and running desktop database
app's. My work, however, has taken the size of my Access databases to half
a
million records and 1.5GB. Access completely crawls at this level.
Furthermore, I need to perform more statistical analysis that Access seems t
o
be designed for. I feel like I'm needing to take the next step in database
usage. Unfortunately, there don't seem to be any other "desktop" database
app's that are significantly more capable than Access. Thus, I'm guessing I
need to bite the bullet and begrudgingly buy and learn the server /
enterprise database software. What would be the best one to ease into for a
MS Access user like me? Would it be MS SQL Server Personal, for instance?
What's the learning curve like for an Access user? And finally, will it run
on a desktop computer running Windows XP?Bill_S wrote:
> I've been an Access user for years designing and running desktop
> database app's. My work, however, has taken the size of my Access
> databases to half a million records and 1.5GB. Access completely
> crawls at this level. Furthermore, I need to perform more statistical
> analysis that Access seems to be designed for. I feel like I'm
> needing to take the next step in database usage. Unfortunately,
> there don't seem to be any other "desktop" database app's that are
> significantly more capable than Access. Thus, I'm guessing I need to
> bite the bullet and begrudgingly buy and learn the server /
> enterprise database software. What would be the best one to ease
> into for a MS Access user like me? Would it be MS SQL Server
> Personal, for instance? What's the learning curve like for an Access
> user? And finally, will it run on a desktop computer running Windows
> XP?
Personal Edition only exists as an add-on for Standard/EE/and Developer.
You can download the MS Data Engine (MSDE) - which is essentially a
stripped down version of SQL Server 2000 without any client tools and a
2GB limit on database size. It may not take you very far, given you're
already at 1.5GB, but at least it's free. You can download the MS Web
Data Administrator to help manage the database.
If this is a development project, you can purchase Developer Edition
from MS for about $50. It's obviously not licensed for use in
production.
You'll need to read about SQL Server to get started. There are number of
good web sites (sqlserverperformance.com, SQLServerCentral.com, etc).
Get a book as well - I prefer Kalen Delaney's Inside SQL Server 2000 -
MS Press.
David Gugick
Imceda Software
www.imceda.com

news server for comp.databases.ms-sqlserver

I know the news server i used to setup for the newsgroup im posting is
news.microsoft.com
Whats the news server to post to comp.databases.ms-sqlserver ?
ThanksYou can use any newsserver which let you post messages through and
lists the above mentioned one. For a list of available newsserver just
search in google.
e.g. here: http://www.cab.u-szeged.hu/linux/mi...ws-servers.html
HTH, Jens Suessmeyer.

Newbie-How to tell when Database last used

I Inherited a SQL 2000 Server with some databases I think are no longer used.
I'm not sure on some of them what apps may be using them. How can I tell
when a database or any of its tables were last used? I don't really care
what the specific transactions were, just when it was touched. There are
database maint jobs backing up the databases. Does this imply that the
datestamps of the files change when they are backed up, and therefore not a
good indication of when they were last used?
Thanks.
"Coop" <Coop@.discussions.microsoft.com> wrote in message
news:33770A94-B003-400A-88F6-9DE689625A91@.microsoft.com...
>I Inherited a SQL 2000 Server with some databases I think are no longer
>used.
> I'm not sure on some of them what apps may be using them. How can I tell
> when a database or any of its tables were last used? I don't really care
> what the specific transactions were, just when it was touched. There are
> database maint jobs backing up the databases. Does this imply that the
> datestamps of the files change when they are backed up, and therefore not
> a
> good indication of when they were last used?
> Thanks.
There's no easy way to do this.
The timestamp on the file for example only changes when the database is
opened or the file size is changed.
You can setup a profiler to watch the database.
Or simply detach it and see who squawks first.
Greg Moore
SQL Server DBA Consulting Remote and Onsite available!
Email: sql (at) greenms.com http://www.greenms.com/sqlserver.html
|||Greg D. Moore (Strider) wrote:
> "Coop" <Coop@.discussions.microsoft.com> wrote in message
> news:33770A94-B003-400A-88F6-9DE689625A91@.microsoft.com...
> There's no easy way to do this.
> The timestamp on the file for example only changes when the database is
> opened or the file size is changed.
> You can setup a profiler to watch the database.
> Or simply detach it and see who squawks first.
>
Taking the DB offline may be simplier...

Newbie-How to tell when Database last used

I Inherited a SQL 2000 Server with some databases I think are no longer used.
I'm not sure on some of them what apps may be using them. How can I tell
when a database or any of its tables were last used? I don't really care
what the specific transactions were, just when it was touched. There are
database maint jobs backing up the databases. Does this imply that the
datestamps of the files change when they are backed up, and therefore not a
good indication of when they were last used?
Thanks."Coop" <Coop@.discussions.microsoft.com> wrote in message
news:33770A94-B003-400A-88F6-9DE689625A91@.microsoft.com...
>I Inherited a SQL 2000 Server with some databases I think are no longer
>used.
> I'm not sure on some of them what apps may be using them. How can I tell
> when a database or any of its tables were last used? I don't really care
> what the specific transactions were, just when it was touched. There are
> database maint jobs backing up the databases. Does this imply that the
> datestamps of the files change when they are backed up, and therefore not
> a
> good indication of when they were last used?
> Thanks.
There's no easy way to do this.
The timestamp on the file for example only changes when the database is
opened or the file size is changed.
You can setup a profiler to watch the database.
Or simply detach it and see who squawks first.
Greg Moore
SQL Server DBA Consulting Remote and Onsite available!
Email: sql (at) greenms.com http://www.greenms.com/sqlserver.html|||Greg D. Moore (Strider) wrote:
> "Coop" <Coop@.discussions.microsoft.com> wrote in message
> news:33770A94-B003-400A-88F6-9DE689625A91@.microsoft.com...
>> I Inherited a SQL 2000 Server with some databases I think are no longer
>> used.
>> I'm not sure on some of them what apps may be using them. How can I tell
>> when a database or any of its tables were last used? I don't really care
>> what the specific transactions were, just when it was touched. There are
>> database maint jobs backing up the databases. Does this imply that the
>> datestamps of the files change when they are backed up, and therefore not
>> a
>> good indication of when they were last used?
>> Thanks.
> There's no easy way to do this.
> The timestamp on the file for example only changes when the database is
> opened or the file size is changed.
> You can setup a profiler to watch the database.
> Or simply detach it and see who squawks first.
>
Taking the DB offline may be simplier...

Newbie-How to tell when Database last used

I Inherited a SQL 2000 Server with some databases I think are no longer used
.
I'm not sure on some of them what apps may be using them. How can I tell
when a database or any of its tables were last used? I don't really care
what the specific transactions were, just when it was touched. There are
database maint jobs backing up the databases. Does this imply that the
datestamps of the files change when they are backed up, and therefore not a
good indication of when they were last used?
Thanks."Coop" <Coop@.discussions.microsoft.com> wrote in message
news:33770A94-B003-400A-88F6-9DE689625A91@.microsoft.com...
>I Inherited a SQL 2000 Server with some databases I think are no longer
>used.
> I'm not sure on some of them what apps may be using them. How can I tell
> when a database or any of its tables were last used? I don't really care
> what the specific transactions were, just when it was touched. There are
> database maint jobs backing up the databases. Does this imply that the
> datestamps of the files change when they are backed up, and therefore not
> a
> good indication of when they were last used?
> Thanks.
There's no easy way to do this.
The timestamp on the file for example only changes when the database is
opened or the file size is changed.
You can setup a profiler to watch the database.
Or simply detach it and see who squawks first.
Greg Moore
SQL Server DBA Consulting Remote and Onsite available!
Email: sql (at) greenms.com http://www.greenms.com/sqlserver.html|||Greg D. Moore (Strider) wrote:
> "Coop" <Coop@.discussions.microsoft.com> wrote in message
> news:33770A94-B003-400A-88F6-9DE689625A91@.microsoft.com...
> There's no easy way to do this.
> The timestamp on the file for example only changes when the database is
> opened or the file size is changed.
> You can setup a profiler to watch the database.
> Or simply detach it and see who squawks first.
>
Taking the DB offline may be simplier...

newbie: viewing data in SQL Server 7.0

I am embarassed even to ask this question here, but I truly don't know.
I can't find a way to graphically view the databases on my server. It is
running Back Office SBS 4.5, which includes SQL Server 7.0. I didn't set up
the server, someone else did, but I do have the installation disks.
I don't know much about SQL Server, but I've had some experience with mySQL
(which I've used phpMyAdmin to view/edit).
I've looked around for applications in the start menu, but nothing is shown
relating to SQL Server. Can anyone please help?
Thanks for the help.
SarahIf the SQL Server administrative tools have been installed, look for an Ente
rprise Manager icon in the Programs\Microsoft SQL Server folder.
If they haven't, rerun the installation and add the admin tools. You will a
lso have to reapply any service packs and hotfixes.
Sincerely,
Anthony Thomas
--
"Sarah S" <psusarah@.ncx.com> wrote in message news:%23bIsDGqzEHA.2192@.TK2M
SFTNGP14.phx.gbl...
I am embarassed even to ask this question here, but I truly don't know.
I can't find a way to graphically view the databases on my server. It is
running Back Office SBS 4.5, which includes SQL Server 7.0. I didn't set u
p
the server, someone else did, but I do have the installation disks.
I don't know much about SQL Server, but I've had some experience with mySQ
L
(which I've used phpMyAdmin to view/edit).
I've looked around for applications in the start menu, but nothing is show
n
relating to SQL Server. Can anyone please help?
Thanks for the help.
Sarah|||Thanks! Does this mean to re-run the OS installation? I'm assuming it does.
No SQL tools were installed originally.
"AnthonyThomas" <Anthony.Thomas@.CommerceBank.com> wrote in message news:%23O
5w9q0zEHA.3120@.TK2MSFTNGP12.phx.gbl...
If the SQL Server administrative tools have been installed, look for an Ente
rprise Manager icon in the Programs\Microsoft SQL Server folder.
If they haven't, rerun the installation and add the admin tools. You will a
lso have to reapply any service packs and hotfixes.
Sincerely,
Anthony Thomas
--
"Sarah S" <psusarah@.ncx.com> wrote in message news:%23bIsDGqzEHA.2192@.TK2MSF
TNGP14.phx.gbl...
I am embarassed even to ask this question here, but I truly don't know.
I can't find a way to graphically view the databases on my server. It is
running Back Office SBS 4.5, which includes SQL Server 7.0. I didn't set up
the server, someone else did, but I do have the installation disks.
I don't know much about SQL Server, but I've had some experience with mysql
(which I've used phpMyAdmin to view/edit).
I've looked around for applications in the start menu, but nothing is shown
relating to SQL Server. Can anyone please help?
Thanks for the help.
Sarah