none
My database became too big in size RRS feed

  • Question

  • I have a database build on SQL 2000 but after inserting some data in it noticed that the data base becomes too big >>> what is the problem?
    Sunday, September 26, 2010 3:13 PM

Answers

  • Hi,

    So here to conclude everything.

    1) Run SP_HELPDB using this command

             EXEC SP_HELPDB N'<DBNAME>'

       Check DB_SIZE from first output and from second output check whether Database File or the Log File is taking more space.

    2) If your Log File is taking more space then run below command

             DBCC SQLPERF(LOGSPACE)

       Check output for databases which is in question and see how much space is free for Log Space. if you see that   

       actual utilisation of file is less and free space is more that means log file is using more space.

    3) As recommended earlier by other SQL folks i.e. try to set Transaction log file backup and schedule it at regular intervals.

    4) If you see that Database file size is more then go to SQL Server Management Studio.

        Right click on Database click on TASKS and then SHRINK and then DATABASE option

        Check how much Free space is available. If you see there is good amount of space free then you can try

        SHRINK. But take into consideration that USING SHRINK REGULARLY IS NOT RECOMMENDED BY MICROSOFT.

    Now coming to a million $ question, why this much space got occurpied in database file. Need to check whether some Data Load/Extraction task got executed on this database. If yes then you might end up in this situation pretty often.

    HTH

     

     


    Regards Gursethi Blog: http://ms-gursethi.blogspot.com/ Please mark "Propose As Answer" if my answer helped..... :)
    • Marked as answer by Dr.X1987 Thursday, September 30, 2010 4:33 PM
    Monday, September 27, 2010 5:57 AM

All replies

  • How much is your database?

    What kind of data do you have in your DB? Did you check fragmentation on indexes? Maybe your indexes are fragmented, then you need to rebuild or reorganize them. This will shrink your DB. Check the backup logs as well. It could be a lot things that are involved.

    Start with checking Indexes. Then maybe you need to take look at your design as well.


    Best Wishes, Arbi --- Please vote if you find this posting was helpful or Mark it as answered.
    Monday, September 27, 2010 4:15 AM
  • Hi Hasan,

      How did you check the database size ..Did you verify the data file and log file or directly you have checked the database size .. ?

    What type of data and how much data you have entered ..  if you have entered huge amount of data, then obviusly, database size will grow .. but if it the difference between the amount of data that you entered and the database growth is huge, then you verify the log file growth and also as suggested before, the fragmentation ...


    -- Reddy Balaji C. ##Mark as answer if it solves the issue
    Monday, September 27, 2010 4:34 AM
  • Most likely the transaction log has grown huge. Scheduling regular transaction log backups will help you overcome this issue. What is the output of sp_helpdb for that database?


    Pradeep Adiga
    My blog: http://www.sqldbadiaries.com

    Recent posts on my blog
    Monday, September 27, 2010 5:08 AM
  • As others have already said you probably have a database with FULL recovery mode and had never performed BACKUP LOG file..... Is that true?
    Best Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/
    Monday, September 27, 2010 5:41 AM
    Moderator
  • Hi,

    So here to conclude everything.

    1) Run SP_HELPDB using this command

             EXEC SP_HELPDB N'<DBNAME>'

       Check DB_SIZE from first output and from second output check whether Database File or the Log File is taking more space.

    2) If your Log File is taking more space then run below command

             DBCC SQLPERF(LOGSPACE)

       Check output for databases which is in question and see how much space is free for Log Space. if you see that   

       actual utilisation of file is less and free space is more that means log file is using more space.

    3) As recommended earlier by other SQL folks i.e. try to set Transaction log file backup and schedule it at regular intervals.

    4) If you see that Database file size is more then go to SQL Server Management Studio.

        Right click on Database click on TASKS and then SHRINK and then DATABASE option

        Check how much Free space is available. If you see there is good amount of space free then you can try

        SHRINK. But take into consideration that USING SHRINK REGULARLY IS NOT RECOMMENDED BY MICROSOFT.

    Now coming to a million $ question, why this much space got occurpied in database file. Need to check whether some Data Load/Extraction task got executed on this database. If yes then you might end up in this situation pretty often.

    HTH

     

     


    Regards Gursethi Blog: http://ms-gursethi.blogspot.com/ Please mark "Propose As Answer" if my answer helped..... :)
    • Marked as answer by Dr.X1987 Thursday, September 30, 2010 4:33 PM
    Monday, September 27, 2010 5:57 AM