Post reply

script

  • August 31, 2009 at 6:22 am

    #210143

    how can we find the datafiles location, total disk space and database size in gb of all databases in sql2000, i ve a script which is giving error when i run in 2000.

    replies are appreciated

  • August 31, 2009 at 6:47 am

    #1046835

    http://www.sqlservercentral.com/scripts/Maintenance+and+Management/30922/

    What is the error?

  • August 31, 2009 at 7:33 am

    #1046865

    this is theh query when i executed in sql 2000 SELECT name, physical_name AS physical_location

    FROM sys.master_files

    showing sys.master_files are not exist

    any one sort it out .

    thanks

  • August 31, 2009 at 8:05 am

    #1046880

    this is theh query when i executed in sql 2000 SELECT name, physical_name AS physical_location

    FROM sys.master_files

    showing sys.master_files are not exist

    any one sort it out .

    thanks

  • August 31, 2009 at 8:39 am

    #1046889

    this is theh query when i executed in sql 2000 SELECT name, physical_name AS physical_location

    FROM sys.master_files

    showing sys.master_files are not exist

    any one sort it out .

    thanks

  • August 31, 2009 at 9:21 am

    #1046921

    You're trying to run 2005 code in 2000. You might be better off posting in the 2000 forum since many here in the 2005 forum no longer have 2000.

    Maybe you want something like exec sp_MSforeachdb 'Select ''?'' as DB_Name, size, filename from ?.dbo.Sysfiles'

  • August 31, 2009 at 3:28 pm

    #1047187

    Try this

    --This script pulls all data and log files

    --associated with any user db's

    BEGIN

    CREATE TABLE #FILEINFO

    (DatabaseName VARCHAR(100),

    PhysicalFileName NVARCHAR(520),

    FileSizeMB INT,

    Growth VARCHAR(100))

    DECLARE @command VARCHAR(5000)

    SELECT @command = 'Use [' + '?' + '] select ' + '''' + '?' + '''' + ' AS DatabaseName,

    sysfiles.filename AS PhysicalFileName,

    CAST(sysfiles.size/128.0 AS int) AS FileSize,

    CASE

    WHEN status & 0x100000 = 0

    THEN convert(varchar,ceiling((growth * 8192.0)/(1024.0*1024.0))) + '' MB''

    ELSE STR(growth) + '' %''

    END growth

    FROM dbo.sysfiles'

    INSERT #FILEINFO EXEC sp_MSForEachDB @command

    SELECT * FROM #FILEINFO

    order by DatabaseName, PhysicalFileName

    DROP TABLE #FILEINFO

    END

    GO

    I've submitted it to the scripts

  • August 31, 2009 at 3:52 pm

    #1047196

    This script gets the file information for every database on a server, and inserts it into temp table that is queried multiple ways to give various levels of analysis of file space usage.

    Get Server Database File Information

    http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=89058

  • August 31, 2009 at 4:15 pm

    #1047208

    Michael Valentine Jones (8/31/2009)

    This script gets the file information for every database on a server, and inserts it into temp table that is queried multiple ways to give various levels of analysis of file space usage.

    Get Server Database File Information

    http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=89058

    That is a very Nice script - thx

    Jason...AKA CirqueDeSQLeil
    _______________________________________________
    I have given a name to my pain...MCM SQL Server, MVP
    SQL RNNR
    Posting Performance Based Questions - Gail Shaw[/url]
    Learn Extended Events

  • August 31, 2009 at 10:52 pm

    #1047306

    you should use sysaltfiles instead of sys.master_files in sql server 2000 to get the details you want.

Viewing 10 posts - 1 through 9 (of 9 total)

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