Post reply

Database Size

  • March 14, 2011 at 7:33 am

    #389039

    Dear All,

    How to find the database size?

    Will it shows the correct value i.e db size when executing sp_helpdb,?

    Please advise.

    Thanks and Regards,

    Ravichandra.

  • March 14, 2011 at 7:39 am

    #1298316

    Can you run sp_helpdb 'database_name'

    M&M

  • March 14, 2011 at 7:59 am

    #1298330

    You can right click the DB in SSMS and go to properties. That is how i find it, or are you trying to use T-SQL only? Then you can query either sysfiles or sys.database_files.

  • September 20, 2015 at 6:43 pm

    #1827970

    Hi there. Sorry for ignoramus question but I"ve jumped into here to try and help a customer out. I am a Splunk admin and not a sql guy but our customer wants me to query his sql databases and report on database and logfile size if possible. Splunk has a db connector into which I can put any query but the account I have only has datareader access to the DBs and so most of the queries I can find will not work. I found exec sp_spaceused but I have to specify every database before that with use "db_name". I saw the 'usealldatabses' (from memory, could be wrong) "secret" command but that doesn't work for me.

    Any clues as to how I might achieve this with this level of access would be great OR what's the lowest level of access I can get this sort of infomration.

    Thanks very much.

  • September 20, 2015 at 10:31 pm

    #1827977

    Exercise your Google-Fu.

    Here[/url]'s one.

    If you google it, you'll find more answers than you can shake a stick at.

  • September 20, 2015 at 10:54 pm

    #1827978

    Don't worry I've certainly been google fooing and I found that one but I get the following when I run it: command="dbxquery", Incorrect syntax near '—'.

    And I get:

    SQL Error (4145):Incorrect syntax near '—'

    An expression of non-boolean type specified in a context where a condition is expected, near 'need'.

    If i try to run it against the database server using Heidi sql.

    Also, the bottom of this page: https://msdn.microsoft.com/en-IN/library/ms186782.aspx lists the permissions required to access sys.master_files which is why i was asking if there was a method to obtain this, or something similar to it, using lesser user rights.

  • September 20, 2015 at 11:10 pm

    #1827979

    "If i try to run it against the database server using Heidi sql."

    Is this a SQL Server database?

    If you're using SQL Server, I would look here... just because it's a great resource:

    http://www.sqlauthority.com

    Yes, it's an indirect answer, but Pinal Dave's website is well worth checking out if you are learning SQL Server.

  • September 20, 2015 at 11:18 pm

    #1827981

    I'm really not going to be able to spread myself across into sql, it's just this one question I thought I'd try and put out there.

    Thanks.

  • September 20, 2015 at 11:30 pm

    #1827982

    what database engine are you writing against? HeidiSQL?

  • September 20, 2015 at 11:51 pm

    #1827984

    No it's an MS SQL Server, I installed HEIDI just so I could run some queries against the sql servers so I could take the Splunk DB connector out of the equation as it's very slow. I don't have access to sql server studio or anything else.

    Sorry I should have made it clear. I have to MS SQL servers that I doin't have access to. I have another windows server that runs Splunk along with Splunk's database connector. I connect from there to the sql dbs with an account I"ve been given that is datareader level access. With that I can query the databases fine but I can't get system type info such as the database and log sizes. To help with testing I installed Heidi on the Splunk server so that I could more quickly test queries agains the sql servers.

    Thanks.

  • September 22, 2015 at 12:29 pm

    #1828533

    The following will fail for databases within which your account is not a db_datareader.

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

    create table #results

    (

    [server] sysname,

    [database] sysname,

    type_desc nvarchar(60),

    [physical_name] nvarchar(260),

    [size_MB] int

    )

    declare @dbname sysname, @sql nvarchar(max)

    declare IH8ProlificCursorUses

    cursor local for select name

    from sys.databases

    open IH8ProlificCursorUses

    while 1=1

    begin

    fetch next from IH8ProlificCursorUses into @dbname

    if @@fetch_status <> 0 break

    set @sql = 'use ' + quotename(@dbname) + ';

    insert #results

    select

    @@servername,

    db_name(),

    type_desc,

    physical_name,

    cast(round(size/128.,0) as int)

    from sys.database_files'

    exec(@sql)

    end

    close IH8ProlificCursorUses

    deallocate IH8ProlificCursorUses

    go

    select * from #results

    go

    drop table #results

  • September 22, 2015 at 12:44 pm

    #1828538

    SoHelpMeCodd (9/22/2015)

    The following will fail for databases within which your account is not a db_datareader. ...

    That's normal. You cannot query a database if you do not have sufficient rights.

    Use an account that has the required rights.


    Alvin Ramard
    Memphis PASS Chapter[/url]

    All my SSC forum answers come with a money back guarantee. If you didn't like the answer then I'll gladly refund what you paid for it.

    For best practices on asking questions, please read the following article: Forum Etiquette: How to post data/code on a forum to get the best help[/url]

Viewing 12 posts - 1 through 11 (of 11 total)

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