none
Database is getting too big RRS feed

  • Question

  • We're currently running into some performance problems since our database is getting too big. Due to db size is increasing application cannot handle load and getting hang sometimes.

    Database size: 16421 mb Log size: 505.0625 mb Total size: 16926.0625 mb

    Recovery model: Simple Disk: C drive with 32.33 percent free space Datafile: Autogrowth is on. Growth value: 128 in units of 8 kb pages. Logfile: Will grow to a max size of 2 TB. Growth value: 10 %. Autogrow setting for db: 1024 kb.


    sunnybabu

    Thursday, January 17, 2019 5:36 AM

All replies

  • 16 GB is not that huge a size

    What performance issue are you facing?

    How do you know that its because of db size?


    Please Mark This As Answer if it solved your issue
    Please Vote This As Helpful if it helps to solve your issue
    Visakh
    ----------------------------
    My Wiki User Page
    My MSDN Page
    My Personal Blog
    My Facebook Page

    Thursday, January 17, 2019 5:58 AM
  • Application slowness or hanging or stops suddenly.

    Database is growing. We have only 2.8 gb free space.

    Also, i rebuild or reorganize fragmented indexes and update stats for every 3 days manually. No maintainance jobs except full backup job. 

    So i thought as db size is growing it may be causing the issue as application not able to handle the load.


    sunnybabu

    Thursday, January 17, 2019 6:17 AM
  • What are all the possible ways to reduce the db size or resolving this issue ?

    Thanks,


    sunnybabu

    Thursday, January 17, 2019 6:43 AM
  • We're currently running into some performance problems since our database is getting too big. Due to db size is increasing application cannot handle load and getting hang sometimes.

    Database size: 16421 mb Log size: 505.0625 mb Total size: 16926.0625 mb

    Recovery model: Simple Disk: C drive with 32.33 percent free space Datafile: Autogrowth is on. Growth value: 128 in units of 8 kb pages. Logfile: Will grow to a max size of 2 TB. Growth value: 10 %. Autogrow setting for db: 1024 kb.

    16gb is pretty small by modern standards.

    But why are you running out of disk space?  Can't you add another disk, if you've really filled up your C: drive?  Or can you move the database to another drive that you already have!

    Also it matters how much RAM memory you have on your computer, as the database gets larger you need more RAM to keep the performance snappy.

    But even with lots of disk space and lots of RAM you can find the application running slowly or hanging, but that indicates database and/or application bugs, not performance.

    I'd guess the average SQL Server database is probably 50gb these days, lots of 500gb databases run just fine, and even 5tb databases can run well, on larger servers and with a little extra care.

    Josh

    ps - SQL Server standards generally recommend you have the data files on one drive, log file on another, and tempdb is best on yet another.  Though that's with old-fashioned HDD drives, with SSD I guess they can all be on one and run pretty well.  So there are lots of things you can do.  But since you say you're running in simple recovery mode, it sounds like you're not doing a commercial production-quality system or running on a big server, so just add what you need.
    Thursday, January 17, 2019 6:52 AM
  • Again, 16 GB is not huge, it's small. SQL Server can easily handle database with hundret of TB and more.

    Performance issues are often caused by bad database and/or application design. You have to analyze app & SQL code to get the root cause.


    Olaf Helper

    [ Blog] [ Xing] [ MVP]

    Thursday, January 17, 2019 6:53 AM
    Moderator
  • Hi,

    I think the database size is not that big, and may be it the not the reason for the performance issues.

    You need to do performance tuning for your server and database to know the bottleneck of the performance and then based on that you can take the action.

    You can consider many scenarios one of it may be the database indexing, auto growth option of your database, locking and many other even related to the server.

    I recommend to go through bellow article to do performance tuning for may be one day or even a week and then do the analysis.

    DB and Server Performance analysis

    I hope this is helpful


    Please Mark it as Answered if it answered your question
    OR mark it as Helpful if it help you to solve your problem
    Elmozamil Elamir Hamid


    MCSE Data Platform MCITP: SQL Server 2008 Administration/Development
    MCSA: SQL Server 2012/2014
    MCTS: SQL Server Administration/Development
    MyBlog

    Thursday, January 17, 2019 7:04 AM
  • Does it resolve the issue with below things:

    Changing recovery model to full

    Deleting all unnecarry backups and scheduling Daily full backups and every 20 mins log backups maintainance jobs

    Seperate drive for data and log files

    Seperate drive for Tempdb

    Adding more tempfiles

    Thanks,



    sunnybabu

    Thursday, January 17, 2019 8:00 AM
  • Does it resolve the issue with below things:

    Changing recovery model to full

    Deleting all unnecarry backups and scheduling Daily full backups and every 20 mins log backups maintainance jobs

    Seperate drive for data and log files

    Seperate drive for Tempdb

    Adding more tempfiles

    Thanks,



    sunnybabu

    This will not solve the issue it may enhance performance if it is related to IO (separate drive options). But the main thing is to audit the server and the database(s) to know the root cause of the issue.

    I remember one of IT people always increase the resources of the server (RAM, CPU, etc.) and the performance still bad, and after I checked the server the issue is the edition of the SQL server (they used express edition which only consume only specific RAM size and CPU). I mentioned this only to show you the importance of the auditing instead of jumping to conclusion.

    I hope this is helpful


    Please Mark it as Answered if it answered your question
    OR mark it as Helpful if it help you to solve your problem
    Elmozamil Elamir Hamid


    MCSE Data Platform MCITP: SQL Server 2008 Administration/Development
    MCSA: SQL Server 2012/2014
    MCTS: SQL Server Administration/Development
    MyBlog

    Thursday, January 17, 2019 8:58 AM
  • What are all the possible ways to reduce the db size or resolving this issue ?

    Thanks,


    sunnybabu

    Your root cause analysis is not correct

    You need to analyze where performance issues are happening

    Application slowness can be due to lots of locking happening etc too

    So you need to analyze query behind for the cases where it responds slowly

    Another thing can be poorly written code causing lots of unwanted tempdb usage and increasing IO considerably

    So first start analyzing in this direction and see

    Also you've specified you've only 2,8 GB left. Do you mean your db server doesnt have enough hard disk space? If yes, add some space to drive as well which holds db files


    Please Mark This As Answer if it solved your issue
    Please Vote This As Helpful if it helps to solve your issue
    Visakh
    ----------------------------
    My Wiki User Page
    My MSDN Page
    My Personal Blog
    My Facebook Page

    Thursday, January 17, 2019 9:08 AM
  • Windows is not activated !!!!

    And it is a production server.

    Thanks,


    sunnybabu

    Thursday, January 17, 2019 11:06 AM
  • Windows is not activated !!!!

    And it is a production server.


    ???? Not clear what you trying to tell us??

    Olaf Helper

    [ Blog] [ Xing] [ MVP]

    Friday, January 18, 2019 6:41 AM
    Moderator
  • Actually, Production Windows Server OS is Not Geniuine. We cannot use SQL Server Licenced Software on a Fake OS, definetly there will be Issues. There is Pure issue on the Windows Server. Windows is Not Activated. I thought this is causing the issue. 

    Also tempdb is taking loads on server and there is no dedicated drive for tempdb.

    Analyzed all reports and found that we have good RAM and Drive space and no top transactions by age. Also, no much long executing queries..even if there are slow queries, execution counts are 2 or 3.

    Thanks a lot for all your replies. Any other suggestions or help would be highly appreciated.

    Thanks,


    sunnybabu

    Friday, January 18, 2019 6:50 AM
  • Thank you for the sharing.
    Saturday, January 19, 2019 7:28 AM