Post reply

Size of database is growing very fast

  • February 13, 2014 at 7:45 am

    #283706

    When I use this query, I see the biggest table is 81 MB, the database size is at 28 GB. Help is appreciated,

    IF object_id('tempdb..#TableSize') IS NOT NULL

    BEGIN

    DROP TABLE #TableSize

    END

    create table #TableSize (name varchar(150), rows int, reserved varchar(150)

    ,data varchar(150), index_size varchar(150), unused varchar(150))

    insert into #TableSize

    EXEC sp_MSforeachtable @command1='EXEC sp_spaceused ''?'''

    select name, cast(replace(data, ' KB','') as int)/1024 as TableDataSizeMB

    from #TableSize

    order by cast(replace(data, ' KB','') as int) desc

    drop table #TableSize

  • February 13, 2014 at 9:02 am

    #1688797

    my first guess is that you have a runaway log file, because the database is set to full recovery, and you are not doing LOG backups.

    select

    recovery_model

    from sys.databases

    where database_id = db_id()

    if you only ever do FULL backups, change the database to SIMPLE; otherwise you need to come up with a database backup and restoration plan, so you regularly backup the log.

    Lowell

    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • February 13, 2014 at 9:04 am

    #1688801

    I would add to Lowell's query with the following:SELECT recovery_model_desc, log_reuse_wait_desc

    FROM sys.databases

    WHERE database_id = DB_ID()


    Microsoft Certified Master - SQL Server 2008
    Follow me on twitter: @keith_tate

    Forum Etiquette: How to post data/code on a forum to get the best help[/url]

  • February 13, 2014 at 9:08 am

    #1688807

    sorry I should have mentioned that earlier, database is Simple

  • February 13, 2014 at 9:10 am

    #1688808

    result is simple and replication

  • February 13, 2014 at 9:13 am

    #1688812

    really? that is not what i'd be expecting.

    o you have a lot of tables that are HEAPS, that get a lot of updates? HEAP tables never release space unless you get a table lock on it, so you could get a boatload of space locked up that way.

    select

    object_name(object_id), *

    from sys.indexes

    where index_id = 0

    edit:

    replication? you didn't mention that previously, that's the spot to look at right away.

    Lowell

    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • February 13, 2014 at 9:14 am

    #1688814

    Have you checked for long running transactions? You could use sp_WhoIsActive, sys.dm_tran_active_transactions or DBCC OPENTRAN to find long running transactions.


    Microsoft Certified Master - SQL Server 2008
    Follow me on twitter: @keith_tate

    Forum Etiquette: How to post data/code on a forum to get the best help[/url]

  • February 13, 2014 at 9:17 am

    #1688819

    bubby (2/13/2014)

    result is simple and replication

    Are you running replication? If so, have you checked that it is functioning properly?


    Microsoft Certified Master - SQL Server 2008
    Follow me on twitter: @keith_tate

    Forum Etiquette: How to post data/code on a forum to get the best help[/url]

  • February 13, 2014 at 9:22 am

    #1688822

    My question here, if I rebooting this machine every night, shouldn't it clear all the locks and the space locked,

    This is the result I get,

    sysfiles18NULL00HEAP0100

    fnSecAccountActionList263671987NULL00HEAP0000

    fnTableDepends279672044NULL00HEAP0000

    Computers2034106287NULL00HEAP0100

  • February 13, 2014 at 9:25 am

    #1688824

    Replication is not enabled

  • February 13, 2014 at 9:52 am

    #1688839

    bubby (2/13/2014)

    Replication is not enabled

    I would look at the following thread: http://www.sqlservercentral.com/Forums/Topic695034-357-1.aspx


    Microsoft Certified Master - SQL Server 2008
    Follow me on twitter: @keith_tate

    Forum Etiquette: How to post data/code on a forum to get the best help[/url]

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

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