InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comWed, 22 May 2013 04:18:48 GMT20http://www.sqlservercentral.com/Forums/Topic1397540-1550-1.aspxThere is a script here to help you with that.[url]http://jasonbrimhall.info/2012/07/17/a-trio-of-eachdb/[/url]Fri, 21 Dec 2012 01:26:31 GMTSQLRNNRhttp://www.sqlservercentral.com/Forums/Topic1397540-1550-1.aspxHello Team,I am using below query to get size_mb and unused space in databaseIs there any way to get instance level or report ?Query:select a.FILEID, [FILE_SIZE_MB] = convert(decimal(12,2),round(a.size/128.000,2)), [SPACE_USED_MB] = convert(decimal(12,2),round(fileproperty(a.name,'SpaceUsed')/128.000,2)), [FREE_SPACE_MB] = convert(decimal(12,2),round((a.size-fileproperty(a.name,'SpaceUsed'))/128.000,2)) , NAME = left(a.NAME,15), FILENAME = left(a.FILENAME,30)from dbo.sysfiles aResult---------Field id FILE_SIZE_MB Space_used_mb free_space_mb name filename1 4.00 2.94 1.06 master C:\Program Files (x86)\Microso2 1.25 0.64 0.61 mastlog C:\Program Files (x86)\MicrosoKindly help me Thanks in advanceFri, 21 Dec 2012 01:13:02 GMTRK2012http://www.sqlservercentral.com/Forums/Topic1397540-1550-1.aspxFor database log file space stats,you can run " dbcc sqlperf ".[code="sql"]dbcc sqlperf('LOGSPACE')[/code]Wed, 19 Dec 2012 21:43:42 GMTjyuan68http://www.sqlservercentral.com/Forums/Topic1397540-1550-1.aspxdon't remove the whole thing, just remove the spaces.the string of characters is special and they are removed from posts hence why I need to put spaces in so that you can see the full script '& # x 0 D ;''&#x 0D;' Just remove the space between the x and the 0Tue, 18 Dec 2012 23:38:53 GMTanthony.greenhttp://www.sqlservercentral.com/Forums/Topic1397540-1550-1.aspx[quote][b]RK2012 (12/18/2012)[/b][hr]Can you please give me modified script[/quote][code="sql"]DECLARE @SQL NVARCHAR(MAX) SELECT @SQL = REPLACE( CAST( ( SELECT 'USE ' + QUOTENAME(name) +';' + CHAR(13) + CHAR(10) + 'SELECT ' + CHAR(13) + CHAR(10) + 'DatabaseName = DB_NAME(), ' + CHAR(13) + CHAR(10) + 'a.FILEID, ' + CHAR(13) + CHAR(10) + '[FILE_SIZE_MB] = CONVERT(DECIMAL(12, 2), ROUND(a.size / 128.000, 2)), ' + CHAR(13) + CHAR(10) + '[SPACE_USED_MB] = CONVERT(DECIMAL(12, 2), ROUND(fileproperty(a.NAME, ' + CHAR(39) + 'SpaceUsed' + CHAR(39) +') / 128.000, 2)), ' + CHAR(13) + CHAR(10) + '[FREE_SPACE_MB] = CONVERT(DECIMAL(12, 2), ROUND((a.size - fileproperty(a.NAME, ' + CHAR(39) + 'SpaceUsed' + CHAR(39) +')) / 128.000, 2)), ' + CHAR(13) + CHAR(10) + 'a.NAME, a.FILENAME ' + CHAR(13) + CHAR(10) + 'FROM dbo.sysfiles a;' + CHAR(13) + CHAR(10) FROM sys.databases FOR XML PATH('') ) AS NVARCHAR(MAX) ), '
',CHAR(13) + CHAR(10) ) print @SQL EXECUTE sp_executesql @SQL[/code]Tue, 18 Dec 2012 23:17:18 GMTBhuvneshhttp://www.sqlservercentral.com/Forums/Topic1397540-1550-1.aspxThanks anthony I have removed '& # x 0 D ;' in script it is not working Can you please give me modified scriptTue, 18 Dec 2012 21:29:43 GMTRK2012http://www.sqlservercentral.com/Forums/Topic1397540-1550-1.aspx[code="sql"] DECLARE @SQL NVARCHAR(MAX) SELECT @SQL = REPLACE( CAST( ( SELECT 'USE ' + QUOTENAME(name) +';' + CHAR(13) + CHAR(10) + 'SELECT ' + CHAR(13) + CHAR(10) + 'DatabaseName = DB_NAME(), ' + CHAR(13) + CHAR(10) + 'a.FILEID, ' + CHAR(13) + CHAR(10) + '[FILE_SIZE_MB] = CONVERT(DECIMAL(12, 2), ROUND(a.size / 128.000, 2)), ' + CHAR(13) + CHAR(10) + '[SPACE_USED_MB] = CONVERT(DECIMAL(12, 2), ROUND(fileproperty(a.NAME, ' + CHAR(39) + 'SpaceUsed' + CHAR(39) +') / 128.000, 2)), ' + CHAR(13) + CHAR(10) + '[FREE_SPACE_MB] = CONVERT(DECIMAL(12, 2), ROUND((a.size - fileproperty(a.NAME, ' + CHAR(39) + 'SpaceUsed' + CHAR(39) +')) / 128.000, 2)), ' + CHAR(13) + CHAR(10) + 'a.NAME, a.FILENAME ' + CHAR(13) + CHAR(10) + 'FROM dbo.sysfiles a;' + CHAR(13) + CHAR(10) FROM sys.databases FOR XML PATH('') ) AS NVARCHAR(MAX) ), '& # x 0 D ;',CHAR(13) + CHAR(10) ) --SELECT @SQL EXECUTE sp_executesql @SQL[/code]This loops through all DB's pulling out the free space, used space, total space per file.Just remove the spaces between '& # x 0 D ;'Tue, 18 Dec 2012 05:45:11 GMTanthony.greenhttp://www.sqlservercentral.com/Forums/Topic1397540-1550-1.aspxYou can see this in Management Studio.1. Click on "Databases" in Object Explorer2. Select menu item "View -> Object Explorer Details" - a table is displayed showing all databases and few properties, including Size (MB)3. Right click on one of the column headings and make sure "Space Available (KB)" is checked. You can uncheck other columns if you don't need themVoilaTue, 18 Dec 2012 05:42:17 GMTRichard Fryarhttp://www.sqlservercentral.com/Forums/Topic1397540-1550-1.aspxGoogle it sp_spaceused.Mon, 17 Dec 2012 22:40:22 GMTSQL Showhttp://www.sqlservercentral.com/Forums/Topic1397540-1550-1.aspxHello Team,How to find allocated space and used space for all databases in sqlserver instance ?Is there any query.Thanks in advanceMon, 17 Dec 2012 22:32:10 GMTRK2012