how to check if a database is still in use

Question

Post reply

how to check if a database is still in use

sqlfriend

SSC Guru

Points: 52781
More actions
June 18, 2011 at 11:46 pm

#389772

Is there a way that can tell what database are not used in for example for months on our servers?
I see there are some draft atabases on the server, so I'm not sure if they still need to be there, of course on way to check with the people who orignially created them, but he is no longer here. So can I tell from the date modified for the mdf or ldf files to guess how long it hasn't been used?
I did check it but it all shows today's date. so I wonder where else can I check if the database has been in use or not?
Thanks
Forum Etiquette: How to post Reporting Services problems

[/url]

Forum Etiquette: How to post data/code on a forum to get the best help - by Jeff Moden
[/url]

How to Post Performance Problems - by Gail Shaw
[/url]

Ron McCullough SSC Guru Points: 63877 More actions · Answer 1

I would suggest that you read these two items.

http://sqlserver-training.com/sql-script-to-find-out-when-database-was-last-accessed/-

http://dinesql.blogspot.com/2010/05/when-was-my-sql-server-database-last.html

Also pay particular attention to:

Note: – This query uses DMV which hold the information from the point where sql services were started. Do not use this query if your server is restarted recently.

If everything seems to be going well, you have obviously overlooked something.

Ron

Please help us, help you -before posting a question please read[/url]
Before posting a performance problem please read[/url]

Welsh Corgi SSC Guru Points: 116520 More actions · Answer 2

The following script can be used to detemine if a Database is currently in use.

USE Master

DECLARE @dbid INT

SELECT @dbid = dbid FROM sys.sysdatabases WHERE name = ”

IF EXISTS (SELECT spid FROM sys.sysprocesses WHERE dbid = @dbid)

BEGIN

RAISERROR (‘The restore cannot be done’, 16, 1)

SELECT ‘These processes are blocking the restore from occurring’ AS Note, spid, last_batch, status, hostname, loginame FROM sys.sysprocesses WHERE dbid = @dbid

END

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

JamesMorrison Hall of Fame Points: 3213 More actions · Answer 3

Make the database read only and see if anyone complains. 🙂

Or take it offline completely and see if anyone notices.

Seriously, if you are really asking the question, it cannot be a mission critical database with high visibility in the company.

If it is a rarely used database, odds are that the one person using it will contact you when they notice something has changed.

Welsh Corgi SSC Guru Points: 116520 More actions · Answer 4

Personally I would find out of the Database is in use and by who and go from there?

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Orlando Colamatteo SSC Guru Points: 182279 More actions · Answer 5

james.morrison 19355 (6/19/2011)
Make the database read only and see if anyone complains. 🙂
Or take it offline completely and see if anyone notices.
Seriously, if you are really asking the question, it cannot be a mission critical database with high visibility in the company.
If it is a rarely used database, odds are that the one person using it will contact you when they notice something has changed.

Sorry, but this is poor advice.

The original poster is obviously being conscientious and wants to be proactive before taking a database offline.

I agree with Welsh Corgi. Do your due diligence and go from there. An email to "the world" asking about the database is also a good move just to cover all bases.

There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato

sqlfriend SSC Guru Points: 52781 More actions · Answer 6

Thanks, this is a sharepoint database called something like ProjectServerTest_Draft. it also have database like ProjectServer_Draft, and a list all the databases for sharepoints.

And the server has not enough disk space issue.

So I'm trying to figure out if some unnecessary database should be removed. People who set up this has left the company, thought if there is some metadata I can query for example, no data modified in last several months, if so, I can take a further to try to make it read only etc.

But it seems there is nothing of metadata in database provide this.

Thanks

sqlfriend SSC Guru Points: 52781 More actions · Answer 7

bitbucket provided the two links are helpful.

The first one is broken link, the second one I tried, it turns out it all accessed today.

So I think I may check more into this.

Welsh Corgi SSC Guru Points: 116520 More actions · Answer 8

sqlfriends (6/19/2011)
So I think I may check more into this.

I believe that you were provided an adequate solution to your problem so I'm not sure what additional checking is required?

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

mister.magoo SSC-Forever Points: 47174 More actions · Answer 9

I am not an expert on this..., but please try this code - again be warned that it seems to only be good for data since the last engine startup.

It will give you a list of [Transaction Name] and their earliest [Begin Time], allowing you to make a judgement about usage...

EXECUTE sp_MSforeachdb '

USE ? ;

SELECT DB_NAME(),

MIN([d].[Begin Time]) AS [Begin Time],

[d].[Transaction Name]

FROM ::fn_dblog (NULL, NULL) AS [d]

WHERE [d].[Begin Time] IS NOT NULL

GROUP BY [d].[Transaction Name]

UNION ALL

SELECT DB_NAME(),

NULL,

NULL

ORDER BY [Begin Time] DESC;'

I realise this may be bad advice and I may be corrected, but it *seems* useful.

MM

select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);

Welsh Corgi SSC Guru Points: 116520 More actions · Answer 10

You can also use SP_who, sp_who2 or sp_who3 (requires download) and you can quickly identify all Connections to each database, the Login and the Application, etc.

Try it.

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

sqlfriend SSC Guru Points: 52781 More actions · Answer 11

mister.magoo provided the script does help a lot in some degree.

For the server I'm talking about we haven't restarted for a while.

and the script does provide some good information.

For the database I suspected that may not be in use I got the following result by run the script specific to the database:

(No column name)Begin TimeTransaction Name

ProjectServerTest_Draft2011/06/19 17:13:34:760CREATE STATISTICS

ProjectServerTest_Draft2011/06/19 17:13:34:747SpaceAlloc

ProjectServerTest_Draft2011/06/19 17:13:34:637SplitPage

ProjectServerTest_Draft2011/06/19 17:13:34:150UpdateQPStats

ProjectServerTest_Draft2011/06/19 01:46:45:130Backup:CommitDifferentialBase

ProjectServerTest_DraftNULLNULL

I can see there are some information for backup which is done for every database, also space Alloc, I didn't see any data related access information.

For the other database the I think it on production use I run the script too, and I got:

(No column name)Begin TimeTransaction Name

ProjectServer_Published2011/06/19 01:39:26:007user_transaction

ProjectServer_Published2011/06/19 01:38:31:037Backup:CommitDifferentialBase

ProjectServer_PublishedNULLNULL

So I think the script helps in some degree, but I still cannot decide if the former database is in use or not.

Thanks

Welsh Corgi SSC Guru Points: 116520 More actions · Answer 12

sqlfriends (6/19/2011)
So I think the script helps in some degree, but I still cannot decide if the former database is in use or not.

Have you tried what I suggested?

You could execute the code provided and move on to the next task. :hehe:

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

mister.magoo SSC-Forever Points: 47174 More actions · Answer 13

Actually, I think the backup will stop that being useful - sorry - I did say I wasn't an expert - I think I will step aside on this one :hehe:

MM

select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);

mister.magoo SSC-Forever Points: 47174 More actions · Answer 14

Welsh Corgi (6/19/2011)
sqlfriends (6/19/2011)
So I think the script helps in some degree, but I still cannot decide if the former database is in use or not.
Have you tried what I suggested?
You could execute the code provided and move on to the next task. :hehe:

As long as the database is in use when you run it....or am I wrong about that?

MM

select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);