17GB of data. Database is 290GB. Why???

Question

17GB of data. Database is 290GB. Why???

Alan Naylor

SSC-Addicted

Points: 469
More actions
March 17, 2014 at 8:21 pm

#286818

I ran the below query and I got 17GB of data. Why is the database 290 GB.
DECLARE @TableName VARCHAR(100) --For storing values in the cursor
--Cursor to get the name of all user tables from the sysobjects listing
DECLARE tableCursor CURSOR FOR
select [name] from dbo.sysobjects where OBJECTPROPERTY(id, N'IsUserTable') = 1 FOR READ ONLY
--A procedure level temp table to store the results
CREATE TABLE #TempTable ( tableName varchar(100), numberofRows varchar(100), reservedSize varchar(50), dataSize varchar(50), indexSize varchar(50), unusedSize varchar(50) )
--Open the cursor
OPEN tableCursor
--Get the first table name from the cursor
FETCH NEXT FROM tableCursor INTO @TableName
--Loop until the cursor was not able to fetch
WHILE (@@Fetch_Status >= 0) BEGIN
--Dump the results of the sp_spaceused query to the temp table
INSERT #TempTable
EXEC sp_spaceused @TableName
--Get the next table name
FETCH NEXT FROM tableCursor INTO @TableName END
--Get rid of the cursor
CLOSE tableCursor
DEALLOCATE tableCursor
--Select all records so we can use the reults
SELECT * FROM #TempTable
order by 2
--Final cleanup!
DROP TABLE #TempTable
GO

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

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

free_mascot One Orange Chip Points: 27169 More actions · Answer 1

What is the datafile and logfile size?

---------------------------------------------------
"Thare are only 10 types of people in the world:
Those who understand binary, and those who don't."

masoudk1990 SSCommitted Points: 1651 More actions · Answer 2

Try it, in SQL Server Management Studio right click on your database and choose "tasks --> shrink --> Databases"

___________________________________
Computer Enterprise Masoud Keshavarz
I don't care about hell.
If I go there I've played enough Diablo to know how to fight my way out.

dbalmf SSCrazy Points: 2063 More actions · Answer 3

Before you shrink the database..

Shrinking is not really a recommended practice for a number of reasons, have a read of this blog post:

http://www.sqlservercentral.com/blogs/steve_jones/2010/09/14/common-sql-server-mistakes-_2D00_-shrinking-databases/

The data file does not shrink back if you delete a bunch of data which gives you the symptom you are seeing. If space is not an issue I would be tempted to leave it be and allow the data to grow within it.

Ta

David

============================================================
David

http://www.sqlservercentral.com/articles/Best+Practices/61537/[/url]

Suresh B. SSC-Insane Points: 22986 More actions · Answer 4

Alan Naylor (3/17/2014)
I ran the below query and I got 17GB of data. Why is the database 290 GB.

I think large tables are purged and that space is not yet released back.

Grant Fritchey SSC Guru Points: 398882 More actions · Answer 5

If this is a one-time shrink after a major data clean up, it should be fine. You just want to avoid a situation where you're growing then shrinking the database over and over again.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

MysteryJimbo SSC-Insane Points: 24203 More actions · Answer 6

dbalmf (3/18/2014)
The data file does not shrink back if you delete a bunch of data which gives you the symptom you are seeing. If space is not an issue I would be tempted to leave it be and allow the data to grow within it.

In some cases this would be acceptable but given its approx. 7% used it could be a VERY long time until the data grows if it does at all. There may now be archiving processes in place.

Grant Fritchey (3/18/2014)
If this is a one-time shrink after a major data clean up, it should be fine. You just want to avoid a situation where you're growing then shrinking the database over and over again.

+1

Followed by an index rebuild.

Greg Edwards-268690 SSC-Insane Points: 20680 More actions · Answer 7

MysteryJimbo (3/18/2014)
dbalmf (3/18/2014)
The data file does not shrink back if you delete a bunch of data which gives you the symptom you are seeing. If space is not an issue I would be tempted to leave it be and allow the data to grow within it.
In some cases this would be acceptable but given its approx. 7% used it could be a VERY long time until the data grows if it does at all. There may now be archiving processes in place.
Grant Fritchey (3/18/2014)
If this is a one-time shrink after a major data clean up, it should be fine. You just want to avoid a situation where you're growing then shrinking the database over and over again.
+1
Followed by an index rebuild.

+2

joshdbguy SSCertifiable Points: 7707 More actions · Answer 8

Shrink and Rebuild works great for one time operations but I'm curious as to what happened originally. Was there a one time operation that inserted a massive amount of a data and was cleaned up at some point? Is there a job or task that runs massive inserts and then deletes them. . . etc. I once discovered that with incorrect settings, there was a cleanup job that was running once every several months. The DB would go from 10 GB used to around 100 GB, the cleanup script would run and then it would go back to 10. However, no shrink was done, and that 100 GB amount remained.