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
Moderator
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, This info might help: 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. Why would SQL need to reserve this much more space and how can I free up all the reserve space for these tables? Here is the utlity I ran: ————————————————— USE database1 GO SET NOCOUNT ON GO CREATE TABLE #SpaceUsed ( [name] varchar(255) , [rows] varchar(25) , [reserved] varchar(25) , [data] varchar(25) , [index_size] varchar(25) , [unused] varchar(25) ) GO DECLARE @tablename nvarchar(12<img src=’/community/emoticons/emotion-11.gif’ alt=’8)’ /> , @maxtablename nvarchar(12<img src=’/community/emoticons/emotion-11.gif’ alt=’8)’ /> , @cmd nvarchar(1000) SELECT @tablename = ” , @maxtablename = MAX(name) FROM sysobjects WHERE xtype=’u' WHILE @tablename < @maxtablename BEGIN SELECT @tablename = MIN(name) FROM sysobjects WHERE xtype=’u’ and name > @tablename SET @cmd=’exec sp_spaceused[‘[email protected]+’]' INSERT INTO #SpaceUsed EXEC sp_executesql @cmd END SET NOCOUNT OFF GO SELECT * FROM #SpaceUsed GO DROP TABLE #SpaceUSed GO

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
GO
SET NOCOUNT ON
GO
CREATE TABLE #SpaceUsed (
[name] varchar(255)
, [rows] varchar(25)
, [reserved] varchar(25)
, [data] varchar(25)
, [index_size] varchar(25)
, [unused] varchar(25)
)
GO
INSERT INTO #SpaceUsed exec sp_msforeachtable
@command1 = ‘exec sp_spaceused ”?”’ SET NOCOUNT OFF
GO
SELECT * FROM #SpaceUsed
GO

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
Moderator
http://www.SQL-Server-Performance.Com/forum
This posting is provided â€œAS ISâ€ with no rights for the sake of knowledge sharing.

]]>