March 17, 2014 at 8:21 pm
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
March 17, 2014 at 10:40 pm
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."
March 17, 2014 at 11:51 pm
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.
March 18, 2014 at 3:10 am
Before you shrink the database..
Shrinking is not really a recommended practice for a number of reasons, have a read of this blog post:
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
March 18, 2014 at 3:18 am
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.
March 18, 2014 at 4:08 am
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 RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software
March 18, 2014 at 5:08 am
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.
March 19, 2014 at 10:34 am
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
March 19, 2014 at 12:47 pm
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.
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply