Post reply

want to know unstricted database of sql server

  • April 17, 2009 at 7:56 am

    #133181

    Hi,

    I have a no of sql servers for which i need to know the databases which are not restricted.

    If anyone could please help me in that....if you have any script which will feach those database.

    Thanks in advance.

  • April 17, 2009 at 8:04 am

    #979686

    DKG,

    What do you mean by Restricted? Are you referring to the Database State/Restrict Access options?

  • April 17, 2009 at 8:12 am

    #979691

    Take a look in SYS.Databases , everything you need to know is in there.


    Shamless self promotion - read my blog http://sirsql.net

  • April 17, 2009 at 10:46 am

    #979827

    sorry this information is not found in sysdatabases.

    in the sysdatabases you can only found:

    name, dbid, sid, mode, status, status2, crdate, reserved, category, cmptlevel, filename, version

    Mark - Unrestricted means data and log file size restricted file growth/unrestricted file growth.

    I want to know the databases in my sql server which are set on unrestricted file growth.

    thanks,

    DKG

  • April 17, 2009 at 10:57 am

    #979837

    There are properties for databases you can query. Are you looking for this in T-SQL or SSMS? In SSMS, you can get the properties by right clicking a database.

    For T-SQL, sys.database_files has info

    http://msdn.microsoft.com/en-us/library/ms174397.aspx

  • April 17, 2009 at 10:59 am

    #979841

    Sorry, your initial post indicated you wanted user access information, not filegrowth data


    Shamless self promotion - read my blog http://sirsql.net

  • April 17, 2009 at 11:30 am

    #979867

    select d.name as DatabaseName, mf.name as FileLogicalName, mf.physical_name, type_desc, max_size

    from sys.master_files mf inner join sys.databases d on d.database_id = mf.database_id

    Any files with a max size of -1 are set to unlimited growth.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • April 20, 2009 at 2:06 pm

    #981033

    Thanks Gail for the help,

    This script giving result for all the databases, and in sql 2005 if the data base file is unrestricted it set its value to 2XXXXXXXMB and make it restrict. Its a known bug in sql 2005. the script doesnt fetch that file with -1.

    I want a script which will fetch only those DBs set to unrestrict growth, so that i can execute on server and can get the result in one go. I need to check 100+ servers.

    Thanks,

  • April 20, 2009 at 7:37 pm

    #981149

    Modify the query then:

    select d.name as DatabaseName, mf.name

    as FileLogicalName, mf.physical_name, type_desc, max_size

    from sys.master_files mf

    inner join sys.databases d on d.database_id = mf.database_id

    where max_size in (-1,268435456)

  • April 21, 2009 at 2:21 am

    #981245

    Bear in mind that the -1/2TB 'bug' only applies to log files. Data files with unrestricted growth do show a -1 there.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • April 21, 2009 at 3:24 am

    #981265

    Yes you are right Gail, the bug is for log file only.

    Thanks MANU its working fine.

  • April 21, 2009 at 4:26 am

    #981288

    Technically it's not exactly a bug. The max allowable size for a transaction log file is 2 TB, so unrestricted or not, a log file cannot grow larger than that.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • April 21, 2009 at 5:57 am

    #981343

    Thanks Gail for this information.

    Gail , MANU this scirpt doesn't work for sql 2000

    select d.name as DatabaseName, mf.name

    as FileLogicalName, mf.physical_name, type_desc, max_size

    from sys.master_files mf

    inner join sys.databases d on d.database_id = mf.database_id

    where max_size in (-1,268435456)

  • April 21, 2009 at 6:35 am

    #981359

    DKG (4/21/2009)

    Gail , MANU this scirpt doesn't work for sql 2000

    No, it won't. The question's in the 2005 forums, hence the query posted uses 2005 features.

    If this is for SQL 2000, the tables you'll need are sysdatabases and sysaltfiles. The column names will all be different too, shouldn't be too hard to change the 2005 query to use the 2000 tables.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

Viewing 14 posts - 1 through 13 (of 13 total)

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