Dbsize And Sysfiles

Jul 12, 2002

I have 50 SQL2k MSDE servers with 250 databases.
Need to know the 250 databases size on daily bases.

It looks dbsize is not in server.master.sysdatabases,
but in individual sysfiles.

Any idea about how to retrieve the dbsize in a better way?
-David

View 1 Replies


ADVERTISEMENT

Sysfiles

Apr 7, 2008

select * from sysdatabases
give the information for all DB's, where as
select * from sysfiles
gives the information only for current DB.

Is there a system table which contain the information for all the files on a server? Or Do I need to use SP_MSFOREACHDB?


------------------------
I think, therefore I am - Rene Descartes

View 2 Replies View Related

Select * From Sysfiles

Feb 21, 2008

Hi,

I am supposed to use 250 MB of space in SQL Server 2005 database

I was running out of space and thus deleted some rows from a table. To my surprise the db size increased. I then shrunk it to bring it back to what it was earlier.

When i deleted some 5000 rows, some space must have been released. Where did the space go and why did the db size increase after deleting the records?

I thought it might be log files..but my hosting provider tells me that db is set to Simple Recovery which does not utilize a Log File. So we cannot shrink it.


So i fired the command

'select * from sysfiles'

Results:
11251602880012820ASPNETDB_DAT_DATD:Program Files....dncASPNETDB.MDF
206312800128660ASPNETDB.MDF_logD:Program Files....dncASPNETDB_log.ldf
3025612800110486420dnc_log2D:Program Files....dncdnc_log2.ldf
What can I do now?thanks.

View 9 Replies View Related

SYSFILES Error With Dbname

Apr 4, 2008

I have databse called RM-A1 and a log of RM-A1.log

When i run this statement it errors.

select ceiling((size * 8192.0)/(1024.0 * 1024.0)),
case when status & 0x100000 = 0 then
convert(varchar,ceiling((growth * 8192)/(1024.0*1024.0)))
else convert (varchar, growth)
end + char(10)+char(13)
from RM-A1_log.dbo.sysfiles where fileid = '1'
--This works
select * from dbo.sysfiles

Im using a dynamic statement

set @cmd = N'select @ceil = ceiling((size * 8192.0)/(1024.0 * 1024.0)), @CNT = case when status & 0x100000 = 0 then
''MB '' + convert(varchar,ceiling((growth * 8192)/(1024.0*1024.0))) '
+ 'else ''PER '' + convert (varchar, growth) '
+ 'end' + char(10)+char(13)
+ 'from ' + @l_db_name + '.dbo.sysfiles where fileid = ''1'''

exec sp_executesql @cmd, @retType, @cnt OUTPUT, @ceil OUTPUT


I cannot determine why log name of '-' are erroring all other databases are working.

View 17 Replies View Related

Sysaltfiles And Sysfiles Inconsistant

Aug 2, 2007

I succesfully did a MSSQL "file" restore of production to a different node yesterday. But failed to apply any transaction logs, it complained that one of the files have not been restored. On further investigation I found that one of the files are missing in sysfiles, but the file is in sysaltfiles.

This SQL statement does not return the same number files.
SELECT * FROM <DB>..sysfilesSELECT * FROM master..sysaltfiles WHERE dbid= DB_ID('<DB>')

sp_helpdb '<DB>' gives the same result as sysfiles.

Any idea on how to fix this ?

Thanks
Tiaan

View 7 Replies View Related

[?]..sysfiles.size Question

Apr 22, 2008

Could someone explain how to convert this size to gigabytes? I look at it, and I can't figure out what size it is in. Maybe bits?

EXEC sp_MSForEachDB 'SELECT CONVERT(char(100), SERVERPROPERTY(''Servername'')) AS Server,
''?'' as DatabaseName,[?]..sysfiles.size, [?]..sysfiles.status, [?]..sysfiles.name, [?]..sysfiles.filename,convert(sysname,DatabasePropertyEx(''?'',''Status'')) as Status,
convert(sysname,DatabasePropertyEx(''?'',''Updateability'')) as Updateability,
convert(sysname,DatabasePropertyEx(''?'',''UserAccess'')) as User_Access,
convert(sysname,DatabasePropertyEx(''?'',''Recovery'')) as Recovery From [?]..sysfiles '

Any help is appreciated.
-Kyle

View 5 Replies View Related

SQL Server 2008 :: Running Sysfiles On A Database Bringing In Error

Apr 30, 2015

Whenever I'm running query against the below database alone, it throws me the error.

use [AdventureWorks_2005]
SELECT * FROM dbo.sysFILES
OR
SELECT * FROM [AdventureWorks_2005].[sys].[SYSFILES]

Error- >

Msg 208, Level 16, State 1, Line 2
Invalid object name 'dbo.sysFILES'

View 3 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved