Post reply

how to check if a database is still in use

  • 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

  • June 19, 2011 at 7:07 am

    #1340857

    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]

  • June 19, 2011 at 11:27 am

    #1340875

    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/

  • June 19, 2011 at 11:50 am

    #1340879

    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.

  • June 19, 2011 at 11:59 am

    #1340880

    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/

  • June 19, 2011 at 12:03 pm

    #1340882

    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

  • June 19, 2011 at 6:02 pm

    #1340923

    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

  • June 19, 2011 at 6:25 pm

    #1340925

    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.

  • June 19, 2011 at 6:29 pm

    #1340927

    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/

  • June 19, 2011 at 6:32 pm

    #1340928

    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);

  • 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]

  • June 19, 2011 at 6:38 pm

    #1340931

    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/

  • June 19, 2011 at 6:56 pm

    #1340933

    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

  • June 19, 2011 at 7:03 pm

    #1340935

    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/

  • June 19, 2011 at 7:04 pm

    #1340936

    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);

  • 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]

  • June 19, 2011 at 7:05 pm

    #1340937

    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);

  • 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]

    Viewing 15 posts - 1 through 15 (of 27 total)

    You must be logged in to reply to this topic. Login to reply

    Cookies on SQLServerCentral

    This website stores cookies on your computer.

    These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.

    To find out more about the cookies we use, see our Privacy Policy