4 Replies - 789 Views - Last Post: 24 June 2016 - 01:00 PM

#1 Riskinit  Icon User is offline

  • D.I.C Head

Reputation: 5
  • View blog
  • Posts: 174
  • Joined: 14-January 10

Is there a process for checking a SQL Server can be decommissioned?

Posted 20 June 2016 - 03:02 PM

I have a very old server running Microsoft Windows 2000 // 5.00.2195 // Service Pack 4.
It states it is running a server using Microsoft SQL Server 2000.

Several years ago the C: and D: drives filled up and a lot of things started failing. A few years after that we removed two of the servers linked to it completely. The only two things on the server's domain are the server and a client.

IP: 197.0.0.2 (client)
IP: 197.0.0.104 (server)

In the Enterprise Manager there are 3 databases. Is there a way to easily tell if they are being constantly used and which tables are being used?

Thanks for any help.

Is This A Good Question/Topic? 0
  • +

Replies To: Is there a process for checking a SQL Server can be decommissioned?

#2 maceysoftware  Icon User is offline

  • D.I.C Lover
  • member icon

Reputation: 348
  • View blog
  • Posts: 1,491
  • Joined: 07-September 13

Re: Is there a process for checking a SQL Server can be decommissioned?

Posted 20 June 2016 - 03:07 PM

You could use SP_WHO2 to see what is currently being being ran?

However I don't know of a way to find out which tables are constantly being used?
Was This Post Helpful? 0
  • +
  • -

#3 ybadragon  Icon User is offline

  • Home Owner
  • member icon

Reputation: 571
  • View blog
  • Posts: 2,647
  • Joined: 11-May 12

Re: Is there a process for checking a SQL Server can be decommissioned?

Posted 21 June 2016 - 06:56 AM

You could run a profiler on it over a certain period of time. Or you could use the built in system reporting/monitoring by right clicking the server in ssms then choosing Reports/Activity Monitor Respectively.
Was This Post Helpful? 1
  • +
  • -

#4 rgfirefly24  Icon User is offline

  • D.I.C Lover
  • member icon


Reputation: 446
  • View blog
  • Posts: 2,177
  • Joined: 07-April 08

Re: Is there a process for checking a SQL Server can be decommissioned?

Posted 24 June 2016 - 07:22 AM

If you know how to interpret the Transaction logs you can get what tables are actively being used from that. Check out the function: fn_dblog. There should be a column that will tell you the exact table names that are having commands run against them. You would run that function against each of the databases you want to look at.
Was This Post Helpful? 0
  • +
  • -

#5 Riskinit  Icon User is offline

  • D.I.C Head

Reputation: 5
  • View blog
  • Posts: 174
  • Joined: 14-January 10

Re: Is there a process for checking a SQL Server can be decommissioned?

Posted 24 June 2016 - 01:00 PM

So the update to this story is this:

After I posted on the 20th I went out and looked at the server a bit longer. After not Googling anything too useful with Windows Server 2000 I just went and added in the following code to each stored procedure.

INSERT INTO sps_being_called db_name, db_sp, db_timestamp
VALUES 'STR_DB_NAME', 'STR_STORED_PROC_NAME', CURRENT_TIMESTAMP;



And right off the bat I get 3 stored procedures running constantly so some type of messaging going on. After 1 night of running I ended up needing about 9 out of 300 stored procedures.

But low and behold something bad happens. One of our networking cards loses it's routing table in the night (first time in over 10 years). So I am called in at 3am to come undo all my changes even though it doesn't make any sense to me. So I take out all my changes and we also reload the routing table from a backup and everything is fine but I'm still not sure it was my fault. Anyways, suddenly people several levels higher than me are calling in and wondering why this is happening. How can a network card lose it's routing table? I still don't have a good answer for this and at this point I'm too afraid to try anything else.

I escaped this one and got my answer. The server is in use so we will need to see about getting a virtual machine and someone more familiar with such things than myself.
Was This Post Helpful? 0
  • +
  • -

Page 1 of 1