Post reply

SQL Database Size 0 and Unallocated space in negative ..how to Free up?

  • May 30, 2014 at 12:39 pm

    #287069

    Hi everyone ,

    One of database size on sql 2000 showing available space 0.00MB and unallocated space -6030.04 MB.

    Any idea how to free up space.

    Is could be reason database holding locks when user trying to insert data?

    when user trying to access database from application they are not able to make change in DB through app. is this could be reason?

    Thanks

  • May 30, 2014 at 10:06 pm

    #1717989

    1. What recovery model is the database in?

    2. What type(s) of backups are you running?

    3. What are you doing for index maintenance?

    4. How big is TempDB?

    5. How big is the log file on your database?

    To fix the negative space available thing, try running the following in the database you're having problems with...

    DBCC UPDATEUSAGE (0);

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • May 31, 2014 at 4:15 pm

    #1718042

    This issue is caused if log back is not taken before tlog file get full. There are 2 ways to correct this issue. I am putting xxx please replace it with your db name.

    First way if you don't care about point of recovery. Please follow below steps. Execute below tsql:

    ALTER DATABASE xxx SET RECOVERY SIMPLE;

    Then find logical name of your log file by using below tsql:

    select * from sys.master_files

    (Take the name of your DB log file. I think it is 7th coloum in the result returned by above tsql). Then execute below tsql

    USE xxx;

    GO

    CHECKPOINT;

    GO

    CHECKPOINT; -- run twice to ensure file wrap-around

    GO

    DBCC SHRINKFILE(yourdb_log, 19000 MB);

    GO

    (Check space allocated for your log file and change above figure (19000 MB) accordingly)

    And you are done...

    Now Second way if you care about point of recovery. Please follow below steps.

    ALTER DATABASE xxx SET RECOVERY FULL;

    After that take a log backup

    BACKUP LOG xxx

    TO DISK = 'C:\xxx.TRN'

    GO

    Then

    Find logical name of log file of your database. Use below statement to get logical name. (7th column)

    select * from sys.master_files

    Then increase the log size

    USE [master];

    GO

    ALTER DATABASE xxx

    MODIFY FILE (NAME = yourdb_log, SIZE = 20000 MB, FILEGROWTH = 500MB);

    GO

    (Please set size according to disk space available and change above figure (20000 MB) accordingly)

    USE xxx;

    GO

    DBCC SHRINKFILE(yourdb_log, 19000 MB);

    GO

    (Check space allocated for your log file and change above figure (19000 MB) accordingly)

    And you are done...

    Thanks...

    Sanjiv Vinaik

  • June 1, 2014 at 10:19 am

    #1718068

    sanjivvinaik (5/31/2014)

    This issue is caused if log back is not taken before tlog file get full.

    Lack of log backups don't cause negative used space.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • June 2, 2014 at 12:29 pm

    #1718311

    Hmm... I was under impression that if log backup are taken in due interval (maybe 2 mins or so depending on application) before tlog gets full in that case database should will not go into negative size. Please correct me what else can cause this please.

    Thanks..

  • June 3, 2014 at 2:34 am

    #1718450

    The only way a DB can record 'negative' size or free space is when the page allocation data is wrong. Hence Jeff's recommendation.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • June 4, 2014 at 10:30 am

    #1718899

    Thanks Gail... logicinside22 please use Jeff's recommendation. If it fixes great else use other steps.

    Source: I fixed similar issue in SQL 2012 for DB participating in AG.

  • June 5, 2014 at 2:58 pm

    #1719469

    Thanks Jeff and Everyone for kind suggestions.

    here some more details.

    Full Backup - Every night

    Index Rebuild Every night

    T_Log - Every 3 hr between 5am to 8Pm

    Recovery -Full

    File Intial Size 10092 MB , Autogrowth 20MB , Unlimited

    It keep going negative again and again like end of every week.

  • June 6, 2014 at 12:43 am

    #1719546

    Jeff Moden (5/30/2014)

    To fix the negative space available thing, try running the following in the database you're having problems with...

    DBCC UPDATEUSAGE (0);

    Because there are bugs in SQL 2000's free space tracking, resulting in the 'negative' space.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

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

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