DB growth?? | SQL Server Performance Forums

SQL Server Performance Forum – Threads Archive

DB growth??

My DB overnight went from 15GB to 24GB and now things are running at a snail’s pace. I tried sp_updatestats and shrink DB to no avail. Does anyone know how I can find out why my DB grew so much and how I can find out what tables possibly have much more data? I suspect some tables doubled or more, but not sure wha tthe # of rows was before. Thanks.
Did you run reindex?, because that grow database ( space to grow ). Luis Martin
SQL-Server-Performance.com All postings are provided “AS IS” with no warranties for accuracy.
Luis, No I didn’t run any utility. All I did was run my regular nightly incremental refresh update and from the logs, it does not seem like any more rows were inserted than should have been. Now I am having huge performance issues with reports taking way too long to run. I just can’t seem to figure out how my DB can grow 8-10GB overnight???
Luis,<br /><br />This info might help:<br /><br />I ran a utility that gives me a listing of all the DB tables, # of rows in each table, the reserved space and the data size in KB for each table. I am noticing that in a few tables, the reserved is over 1 GB for each of these table larger than the data size. <br /><br />Why would SQL need to reserve this much more space and how can I free up all the reserve space for these tables?<br /><br />Here is the utlity I ran:<br /><br />—————————————————<br />USE database1<br />GO<br />SET NOCOUNT ON<br />GO<br />CREATE TABLE #SpaceUsed (<br /> [name] varchar(255)<br />, [rows] varchar(25)<br />, [reserved] varchar(25)<br />, [data] varchar(25)<br />, [index_size] varchar(25)<br />, [unused] varchar(25)<br />)<br />GO<br />DECLARE @tablename nvarchar(12<img src=’/community/emoticons/emotion-11.gif’ alt=’8)’ /><br />, @maxtablename nvarchar(12<img src=’/community/emoticons/emotion-11.gif’ alt=’8)’ /><br />, @cmd nvarchar(1000) <br /> SELECT @tablename = ”<br />, @maxtablename = MAX(name) <br /> FROM sysobjects <br /> WHERE xtype=’u'<br />WHILE @tablename &lt; @maxtablename <br />BEGIN<br />SELECT @tablename = MIN(name) <br /> FROM sysobjects <br /> WHERE xtype=’u’ and name &gt; @tablename<br /> <br /> SET @cmd=’exec sp_spaceused[‘[email protected]+’]'<br />INSERT INTO #SpaceUsed EXEC sp_executesql @cmd<br />END<br />SET NOCOUNT OFF<br />GO<br />SELECT * FROM #SpaceUsed<br />GO<br />DROP TABLE #SpaceUSed<br />GO<br />
Hi, Reserved is the amount of total space reserved for a table. This includes the space used by data and index. So, it is natural for this number to be bigger than data size. The formula is Reserved = Data + Index + Unused So, if your Unused space is more, then it is possible that you are wasting space.
And here’s another (rather simple) way of acheiving the same thing as your above script. This uses an undocumneted procedure sp_msforeachtable. USE database1
[name] varchar(255)
, [rows] varchar(25)
, [reserved] varchar(25)
, [data] varchar(25)
, [index_size] varchar(25)
, [unused] varchar(25)
INSERT INTO #SpaceUsed exec sp_msforeachtable
@command1 = ‘exec sp_spaceused ”?”’ SET NOCOUNT OFF
SELECT * FROM #SpaceUsed
I have 6.3GB of UNUSED space in my DB. Does anyone know how I can clear this out or how this got this way? Thanks.
You can clear this by shrinking the database using dbcc shrinkdatabase. And the reason for this overnight growth could be a maintenance operation (such as reindex).
To all, thanks for the input here. All is back to normal today, the DB is down to the right size, 14GB. I think what happened was as follows, please let me know if I am not on the wrong track. On Sunday night, I ran a DW update ETL process using Cognos DecisionStream that took 9 hours to run, but normally takes only 2 hours. It was the next morning that I saw the DB have tons of issues with slow response time against these tables updated and them grow to huge proportions. Last night, the process ran in its normal 2 hours, and this morning the DB is back to normal. After a shrinkDB command, all is well. What could happen to DB tables in this 9 hour process to make them grow so much and make reports running against them take 4 times as long? What is SQL server writing to the tables, other than data that could cause this data retrieval performance degredation? Thanks.
Check the Transaction log size between these processess and ensure it is placed on correct disk for better I/O performance. Satya SKJ
This posting is provided “AS IS” with no rights for the sake of knowledge sharing.