Post reply

Database size Increased..

  • March 20, 2010 at 8:41 am

    #216892

    Dear All

    I've problem with my sql server database size..actually we are a hotel co..and we've database since 8 years. and its DB backup size was only 10-12 gb almost..till nov 2009,since then.i'm then i m checking that its DB backup is daily growing like 350MB to 400MB..here i want to tell you that our operation or job schedule is same..nothing is increased..and now the backup size is 23GB..that is extremly big size for my server..so today i took INS backup ,,and it was only 10.5GB..

    any one have some idea..plz help...(SQL server 2000)

    Thanks N Regards,

  • March 20, 2010 at 8:53 am

    #1137179

    1. Has user activity increased?

    2. Have you created any new indexes as this is a sure sign when a db increases.

    3. What is your backup schedule? Do you backup and truncate the log?

  • March 20, 2010 at 9:01 am

    #1137181

    Thanks for prompt reply..

    1.No..its same.

    2.No. only .mdf is increased.

    3.Yes

  • March 20, 2010 at 10:01 am

    #1137197

    Is the mdf still large? By the end of your post it sounded like the backups had reduced back to normal.

    Has somebody been reindexing?

    Has somebody been creating copies of tables in the database and just leaving them out there?

    Jason...AKA CirqueDeSQLeil
    _______________________________________________
    I have given a name to my pain...MCM SQL Server, MVP
    SQL RNNR
    Posting Performance Based Questions - Gail Shaw[/url]
    Learn Extended Events

  • March 20, 2010 at 11:00 pm

    #1137308

    Yes mdf is still same..i mean large..

  • March 20, 2010 at 11:30 pm

    #1137310

    If you are certain that your volume of business has not changed in this database, check to see that nobody has copied a table into a "backup" table still in the production database.

    Verify that the tables that exist in the database belong in that database.

    You might also want to verify that nobody has changed any of the table schemas.

    Jason...AKA CirqueDeSQLeil
    _______________________________________________
    I have given a name to my pain...MCM SQL Server, MVP
    SQL RNNR
    Posting Performance Based Questions - Gail Shaw[/url]
    Learn Extended Events

  • March 20, 2010 at 11:50 pm

    #1137311

    I'll try....thanx

  • March 22, 2010 at 6:32 am

    #1137532

    If nothing else, you might want to start counting the records in the database to see if the database is really growing or not, and if so by how much.

    create table #temp(tablename varchar(100), reccount int)

    go

    sp_msforeachtable 'insert #temp select ''?'', count(*) from ?'

    select * from #temp

    order by reccount desc

    You can insert these records into another table in another database with a date field for comparison purposes and then look at the results over a period of time to see if you can spot trends in the growth.

    John.

  • March 22, 2010 at 7:50 am

    #1137598

    1) What exactly is an INS backup?

    2) What index maintenance is being done?

    3) run dbcc sqlperf(logspace) and report the size and percent used on the tlog of the relevant database.

    4) As others have pointed out users making copies of tables is a known and frequent cause of this type of problem. Just had an engagement where I found 21.5GB of such tables in a 300GB database!

    Best,
    Kevin G. Boles
    SQL Server Consultant
    SQL MVP 2007-2012
    TheSQLGuru on googles mail service

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

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