3

I am running a SQL Server 2008 R2 SP2 DB. I have a DB that was running around 19 GB in size and three days ago grew to 34 for no good reason. We typically have about 100 MB growth in the DB every month or so, so this jump up by 15 GB is unexpected. At the same time I started seeing SQL Server eating up more and more RAM from the server. It's typical to see it run at around the 6-8 GB range but all of sudden its taking up to 24 GB of the 32 GB the server has. Again unexplained. I have rebuilt all the indexes, did a FILE shrink (not a DB and never a DB), did a full backup and nothing helped. I was forced to set MaxMemory limit at 16 GB to stabilize things but I have no clue what else I can do. The only change that was ever made over the last week was that a login name that was part of a Domain Controller (Admin account) was removed from SQL Server and replaced with a local Admin account.

I don't have performance states to share at the moment but wondering what could cause the sudden bloat in the DB and what can I look into or do about it? This DB has been running for 4 years without one single issue except for now. I am not a DBA so any help would be appreciated.

  • what is the overall size of the database and transaction log? what are the auto grow values of the files? what is the free space of the database and the transaction log? – KASQLDBA Jun 16 '15 at 19:49
  • Also, when it suddenly grew did u had any maintenance jobs running during that time? – KASQLDBA Jun 16 '15 at 19:50
  • DB is 34GB, TL is 1.2 GB, 10% for both. database_name database_size unallocated space 277_MAIN 34696.13 MB 0.00 MB reserved data index_size unused 40059032 KB 31294968 KB 7121816 KB 1642248 KB There are two maintenance jobs that are run: First is at midnight that deletes 2 day old backups Second that runs every 4 hours to Backup DB and Logs Again, these have been running for years. Also, we have a server at a satellite office that use replicates the DB Im having issues with and its running perfectly, low RAM, DB file size at 19GB, etc..Thanks. – Leo Jun 16 '15 at 19:59
  • sorry, last comment isnt clear so..database_size = 34696.13 unallocated space = 0.00MB – Leo Jun 16 '15 at 20:08
  • 1
    I would grab a backup before this happened, restore and compare the table sizes to the current. What applications use this database? Were they changed? stackoverflow.com/questions/7892334/… – Papa Jun 17 '15 at 3:36
1

I don't have answer but would like to clear few things that would help

DB is 34GB, TL is 1.2 GB, 10% for both

There was surely something happened in database it cannot grow on its own. The first thing you should do is change autogrowth setting you have set autogrowth in percentage(10 %) for both data and log files this IMO would have forced database to grow unnecessarily. You can refer to Autogrowth settings and how to configure it and configure optimum value for this preferably in MB.

It's typical to see it run at around the 6-8 GB range but all of sudden its taking up to 24 GB of the 32 GB the server has. Again unexplained.

What is current max server memory setting and please dont change it to lower value it will cause issues for you. Its normal for SQL server to utilize all memory if allowed to use via max server memory setting.

1

As Papa mentioned in his comment earlier if you have a backup of the database from before this happened restore it somehere else and then run a comparison of the table sizes before and after. This query will get you the row counts and physical size of all the tables and their indexes in the database:

CREATE TABLE #SizeInfo (Table_Name NVARCHAR(128)
                       ,Rows CHAR(11)      
                       ,Data_Size VARCHAR(18)
                       ,Data VARCHAR(18)
                       ,Index_size VARCHAR(18)
                       ,Unused VARCHAR(18))

EXEC       sp_MSForEachTable 'INSERT INTO #SizeInfo EXEC sp_spaceused ''?'' '

SELECT     Table_Name
          ,CONVERT(bigint,Rows) AS Row_Count
          ,CONVERT(bigint,LEFT(Data_Size,LEN(Data_Size)-3)) AS Table_Size_in_KB
          ,CONVERT(bigint,LEFT(Index_size,LEN(Index_size)-3)) AS Index_Size_in_KB
FROM       #SizeInfo 
ORDER BY   Row_Count DESC,Table_Size_in_KB DESC,Table_Name

DROP TABLE #SizeInfo

This should hopefully show you what has grown which then means you can start digging as to why it's grown!

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.