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.

Colin 't Hart
  • 9,455
  • 15
  • 36
  • 44
Leo
  • 31
  • 1

2 Answers2

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.

Shanky
  • 19,148
  • 4
  • 37
  • 58
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!

motosubatsu
  • 181
  • 6