Post reply

database size

  • February 28, 2011 at 1:56 am

    #144885

    Hi All,

    Could any one give me T-sql script for finding the database size for all databases which will be run on multiple servers.

    Thanks to All,

    rekha..

  • February 28, 2011 at 2:39 am

    #1292196

    gsuvarnarekha (2/28/2011)

    Hi All,

    Could any one give me T-sql script for finding the database size for all databases which will be run on multiple servers.

    Thanks to All,

    rekha..

    Creat a linked server.Try to write a loop st. and use the sysaltfiles or sh_helpdb.

    Better, If you have 2008 use the centralized server.

    Muthukkumaran Kaliyamoorthy
    https://www.sqlserverblogforum.com/

  • February 28, 2011 at 3:09 am

    #1292206

    Hi Muthukkumaran,

    Thanks for quick response...could you give me that script having loop for all databases which gives database name and database size.

    Thanks

    Rekha

  • February 28, 2011 at 3:38 am

    #1292209

    Plenty of ways to skin a cat...

    Have a play with sp_MSForeachdb and sys.database_files.

    Carlton.

  • February 28, 2011 at 3:51 am

    #1292212

    Hi,

    Actually i want to use the below script for giving the datails of database name and size for all databases..

    SELECT DB_NAME(database_id) AS DatabaseName,

    Name AS Logical_Name,

    Physical_Name, (size*8)/1024 SizeMB

    FROM sys.master_files

    so what my question is for executing the above query what previleges user want?

    Thanks

    Rekha

  • February 28, 2011 at 4:28 am

    #1292228

    Go google..

    The minimum permissions that are required to see the corresponding row are CREATE DATABASE, ALTER ANY DATABASE, or VIEW ANY DEFINITION.

    Muthukkumaran Kaliyamoorthy
    https://www.sqlserverblogforum.com/

  • February 28, 2011 at 4:31 am

    #1292229

    Hi,

    Is any problem for user having the public role?

    Thanks

    Rekha

  • February 28, 2011 at 5:35 am

    #1292241

    gsuvarnarekha (2/28/2011)

    Hi,

    Is any problem for user having the public role?

    Thanks

    Rekha

    Rekha

    Public role couldn't have permision to view that table.

    Give the grant VIEW ANY DEFINITION to public role.

    Muthukkumaran Kaliyamoorthy
    https://www.sqlserverblogforum.com/

  • February 28, 2011 at 7:18 am

    #1292299

    Thanks

  • February 28, 2011 at 8:31 pm

    #1292695

    muthukkumaran (2/28/2011)

    gsuvarnarekha (2/28/2011)

    Hi,

    Is any problem for user having the public role?

    Thanks

    Rekha

    Rekha

    Public role couldn't have permision to view that table.

    Give the grant VIEW ANY DEFINITION to public role.

    I don't know what the correct alternative answer might be but granting the public role to VIEW ANY DEFINITION just doesn't seem right to me.

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • February 28, 2011 at 11:05 pm

    #1292727

    Jeff you are right.

    I think the alternative way is create a sperate user for the application and give necessary rights to the user.

    Muthukkumaran Kaliyamoorthy
    https://www.sqlserverblogforum.com/

  • August 24, 2011 at 12:07 am

    #1372357

    Try this script

    create table #db

    (DBNAME varchar(50),

    Size float,

    remarks varchar(4))

    GO

    insert into #db

    exec sp_databases

    select DBNAME, Size/1024 as SizeMB from #db

    order by SizeMB desc

    drop table #db

  • August 24, 2011 at 5:22 am

    #1372470

    sp_helpdb

  • August 24, 2011 at 5:23 am

    #1372472

    sp_helpdb

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

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