List All databases by Size and Available space SQL SERVER 2008 R2 -


i'm sysadmin no means dba person, , such i'd ask help.

i have 2 questions. first, how query sql server return list of databases (without default ones master etc..) , list db size , available space (preferably in gb):

see picture

what need know if "space available" affects .bak file size of respective db.

hoping quick response. in advance.

david sankovsky.

right, after tweaking , rtfm-ing came this:

if exists(     select 1     tempdb.dbo.sysobjects     id = object_id('tempdb.dbo.#dbobj') ) drop table #dbobj  create table #dbobj (       [db] sysname     , reservedpages int     , usedpages int     , pages int )  exec sys.sp_msforeachdb '     use [?]      insert #dbobj     (           [db]         , reservedpages         , usedpages         , pages     )     select           db_name()         , pg.reservedpages         , pg.usedpages         , pg.pages     (         select               reservedpages = sum(a.total_pages)             , usedpages = sum(a.used_pages)             , pages = sum(                 case                     when it.internal_type in (202, 204, 207, 211, 212, 213, 214, 215, 216, 221, 222) 0                     when a.[type] != 1 , p.index_id < 2 a.used_pages                     when p.index_id < 2 a.data_pages else 0                 end               )         sys.partitions p         join sys.allocation_units on p.[partition_id] = a.container_id         left join sys.internal_tables on p.[object_id] = it.[object_id]     ) pg'  select       d.name     , total_size_mb = data.row_size_mb + data.log_size_mb     , data.log_size_mb     , data.row_size_mb     , unused_size_mb = cast((do.reservedpages - do.usedpages) * 8. / 1024 decimal(10,2))     , unallocated_space_gb =         cast(case when data.row_size >= do.reservedpages             (data.row_size - do.reservedpages) * 8. / 1024 /1024             else 0         end decimal(10,2)) (     select           df2.database_id         , log_size_mb = cast(df2.log_size * 8. / 1024 decimal(10,2))         , row_size_mb = cast(df2.row_size * 8. / 1024 decimal(10,2))         , df2.log_size         , df2.row_size     (         select               df.database_id             , log_size = sum(case when df.type_desc = 'log' df.size end)             , row_size = sum(case when df.type_desc = 'rows' df.size end)         sys.master_files df         group df.database_id     ) df2 ) data join sys.databases d on data.database_id = d.database_id join #dbobj on do.db = d.name order data.row_size_mb + data.log_size_mb desc

that seems job done. feel free share , everyone. sql link


Comments

Post a Comment

Popular posts from this blog

javascript - Google App Script ContentService downloadAsFile not working -

i have web app developed using google app script htmlservice , html form, populating excel sheet in google drive using spreadsheetapp . , 1 section calling contentservice download data excel file. function doget(e) { // read excel sheet //getappfile(); // render application html template return htmlservice.createtemplatefromfile('index').evaluate() .settitle('go smart') .setsandboxmode(htmlservice.sandboxmode.iframe); } function downloaddoublequatecsvfile() { var sheetid = propertiesservice.getscriptproperties().getproperty('sheetid'); var ss = spreadsheetapp.openbyid(sheetid).getactivesheet(); //var ss = spreadsheetapp.getactivespreadsheet().getactivesheet(); var maxcolumn = ss.getlastcolumn(); var maxrow = ss.getlastrow(); var data = ss.getrange(1, 1, maxrow, maxcolumn).getvalues(); if (data.length > 1) { var csv = ""; (var row = 0; row < data.length; row++) { (var c...
Read more

javascript - Function overwritting -

is there way overwrite function or event handling in function? using addeventlistener, can have many event handlers on element want var test=document.getelementbyid("div"); test.addeventlistener("click",function(){alert("im first");},false); test.addeventlistener("click",function(){alert("im second");},false); // alerts "im first" , after "im second" but if want overwrite function/event example based on client width example this function one() { alert("first"); } function two() { alert("second"); } window.onresize = function() { var test = document.getelementbyid("div"); if (window.innerwidth > 500) { test.onclick = 1 } else { test.onclick = two; } } is possible in javascript? in case use effective little known approach. addeventlistener can accept object property handleevent event handler. in case it's easy overwr...
Read more

php - Find a regex to take part of Email -

i have following emails need extract part from: bestellnummer xxx: 1 von xxx, 1er pack ------------- anfang der nachricht ------------- foo bar baz foo bar baz // <<<<< need text here ------------- ende der nachricht ------------- ------------- anfang der nachricht ------------- foo bar baz foo bar baz ------------- ende der nachricht ------------- there 0 unlimited occurences of ------------- anfang der nachricht ------------- ------------- ende der nachricht ------------- and i'm able extract first part regex: $re = "/------------- .*? -------------.?(.*?).?------------- .*? -------------/s"; but, i'm quite new on learning regex, i'm pretty sure there must better regex extract part (foo bar baz foo bar baz) of text between ------------- anfang der nachricht ------------- ------------- ende der nachricht ------------- as can in different languages, i'm using .? to match between hyphens. i...
Read more