tag:blogger.com,1999:blog-1619266374223964354.post3099609665960870175..comments2024-03-17T08:06:29.007-07:00Comments on SQL and SQL only: Finding Space Used,Space left on Data and Log filesNagaraj Venkatesanhttp://www.blogger.com/profile/17134038987463298279noreply@blogger.comBlogger6125tag:blogger.com,1999:blog-1619266374223964354.post-53403741754860322252012-10-19T20:00:50.611-07:002012-10-19T20:00:50.611-07:00sp_msforeachdb does make it faster. But its undcou...sp_msforeachdb does make it faster. But its undcoumented and hence take extra care while using the sameNagaraj Venkatesanhttps://www.blogger.com/profile/17134038987463298279noreply@blogger.comtag:blogger.com,1999:blog-1619266374223964354.post-7785756922971963372012-10-18T10:45:25.562-07:002012-10-18T10:45:25.562-07:00Great example for me to get started with. But I el...Great example for me to get started with. But I eliminated the cursor and this script executes with half the processing time.<br /><br />CREATE TABLE #db_file_info (<br /> [Database_Name] SYSNAME NOT NULL,<br /> [File_ID] SMALLINT NOT NULL,<br /> [File_Type] VARCHAR(10) NOT NULL,<br /> [File_Name] SYSNAME NOT NULL,<br /> [File_Path] VARCHAR(500) NOT NULL,<br /> [File_Size_in_MB] INT NOT NULL,<br /> [Space_Used_in_MB] INT NOT NULL,<br /> [Space_Left_in_MB] INT NOT NULL)<br /> <br /> Insert into #DB_FILE_INFO<br /> EXEC sp_MSforeachdb ' use [?];<br /> Select db_name(),fileid,case when groupid = 0 then ''log file'' else ''data file'' end,<br /> name,filename, <br /><br /> [file_size] = <br /> convert(int,round((sysfiles.size*1.000)/128.000,0)),<br /> [space_used] =<br /> convert(int,round(fileproperty(sysfiles.name,''SpaceUsed'')/128.000,0)),<br /> [space_left] =<br /> convert(int,round((sysfiles.size-fileproperty(sysfiles.name,''SpaceUsed''))/128.000,0))<br /> from<br /> dbo.sysfiles;' <br /> <br /> <br />SELECT *<br />FROM #db_file_info<br /><br />DROP TABLE #db_file_infoAnonymousnoreply@blogger.comtag:blogger.com,1999:blog-1619266374223964354.post-64265911471180348962012-08-24T06:45:22.750-07:002012-08-24T06:45:22.750-07:00This is great, many thanks for posting this. It...This is great, many thanks for posting this. It's exactly what I've been looking for! :-DAnonymousnoreply@blogger.comtag:blogger.com,1999:blog-1619266374223964354.post-39218005596484312962011-08-05T07:43:06.337-07:002011-08-05T07:43:06.337-07:00wonderful! Exactly what I was looking for. Thank y...wonderful! Exactly what I was looking for. Thank you!Anonymousnoreply@blogger.comtag:blogger.com,1999:blog-1619266374223964354.post-72891958017370919122011-05-04T04:03:55.160-07:002011-05-04T04:03:55.160-07:00Thanks a lot. Script was useful, i replaced set @@...Thanks a lot. Script was useful, i replaced set @@rowcount option with cursor as i need output only for particular database.vijayasekarhttps://www.blogger.com/profile/13156246614007008837noreply@blogger.comtag:blogger.com,1999:blog-1619266374223964354.post-68902347870872661282011-02-10T08:44:28.501-08:002011-02-10T08:44:28.501-08:00Venkatesan,
Firstly, good article... Contratulatio...Venkatesan,<br />Firstly, good article... Contratulations!<br />There is a bug in the line "SET @rowcnt = @@ROWCOUNT". The field DATABASE_ID from SYS.DATABASES has ascend number and if any database is deleted from instance won´t be a sequential number (1,2,3,4,5,8,9).<br />In this case we can replace line "SET @rowcnt = @@ROWCOUNT" to "SELECT @rowcnt=MAX (A.DATABASE_ID) FROM SYS.DATABASES A".<br />The max number loops will be correct.<br /><br />Kind Regards,<br />Rafael<br />rafa4321@terra.com.brAnonymousnoreply@blogger.com