Results 1 to 8 of 8
  1. #1
    Join Date
    Feb 2009
    Posts
    18

    Unanswered: MSSQL: Shrinking of database

    hello
    Please can i have some advice on how to go about shrinking a databsae that i have. firstly i shrink the log file and it goes through but it shrinks the log file to a much much smaller size than i stipulated. Secondly i try shrinking the data file and it seems to go through as it says query executed successfully and there are no errors returned but it does not shrink the data file at all. Please can you help, thanks a million.

  2. #2
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11
    How are you shrinking the files? With the GUI or with DBCC commands in Management Studio?

  3. #3
    Join Date
    Feb 2009
    Posts
    18
    i used dbcc shrinkfile on query analyzer

  4. #4
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11
    The second parameter in DBCC SHRINKFILE is size in MB. While I have seen DBCC SHRINKFILE shrink files to about the size specified due to logical log files, or usage, I have never seen it shrink something much smaller than specified.

    The datafile may not be shrinking, because it could be full.

  5. #5
    Join Date
    Feb 2009
    Posts
    18
    Thanks, how can i check if it is full and what can i do to shrink the data file?

  6. #6
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11
    use the FILEPROPERTY function;
    Code:
    select fileproperty(name, 'SpaceUsed')/128 as "Used MB",
          size/128 as "File Size MB",
          max_size/128 as "Max Size MB"
    from sys.database_files

  7. #7
    Join Date
    Feb 2009
    Posts
    18
    i tried that but i got this error
    "Server: Msg 208, Level 16, State 1, Line 1
    Invalid object name 'sys.database_files' "

    this (SELECT FILEPROPERTY('Altiris', 'SpaceUsed') / 128) executed successfully and the results does tell me that the file is full. So what can i do know to shrink this database? Thank you very much

  8. #8
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    The SQL was for 2005, you must be 2000.

    You need to empty the database a bit to shrink it.
    Options:
    Get appropriate disks for your data.
    Delete some indexes.
    Look for inappropriate datatypes (e.g. numbers between 1 and 10 stored in an INT column - these coule be TINYINT)
    Archive off and delete some data.
    Up the fillfactor of indexes
    Testimonial:
    pootle flump
    ur codings are working excelent.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •