Avatar of Ross Turner
Ross Turner
Flag for United Kingdom of Great Britain and Northern Ireland asked on

MS SQL DB

Hi EE,

I've just done a purge on some tables in ms sql and i can see that the tables have become significantly smaller, but when running a script for the DB Size it still saying that it’s the same size as before.

 Is there some kinda of routine to run that rechecks the db size ?

Thank you

Ross
Microsoft SQL ServerDatabases

Avatar of undefined
Last Comment
Ross Turner

8/22/2022 - Mon
Robert Schutt

yes, you can use the command DBCC SHRINKDATABASE

http://msdn.microsoft.com/en-us/library/aa258287(v=sql.80).aspx 
Lee

Which version of SQL Server?
This is for SQL 2005 - http://msdn.microsoft.com/en-us/library/ms190488%28SQL.90%29.aspx
Ross Turner

ASKER
yeah i'm on Sql 2005
Your help has saved me hundreds of hours of internet surfing.
fblack61
ASKER CERTIFIED SOLUTION
Lee

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
VENKAT KOKULLA

Please Re-Index or Run DBCC CHECKDB on the DB, might this will helps.
Ross Turner

ASKER
it keeps saying that it's timeout when running the Shrinkfile on the whole db
Lee

How big is the database file? how much empty space is in it?
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
Ross Turner

ASKER
it's 70gb is there a specific way of finding out whitespace
Lowfatspread

for performance once a database allocates and uses space . it rarely releases it to the OS without direct intervention.

so just deleting table rows does not release space back to the file system. Indeed just deleting table rows does not necessarily free up internal database space for other tables. (rather the existing pages remain in use by the current table)

if you want to recover space then a combination of reoorganisation, re-indexing, (and as a last resort) shrinkfile/db should be performed.

Tell us what you actual situation is ... and we maybe able to suggest a correct way to proceed...

start with why you are concerned about a small database of 70GB size?
Anthony Perkins

And what is the size of the data file and what is the size of the Transaction Log file.
Experts Exchange has (a) saved my job multiple times, (b) saved me hours, days, and even weeks of work, and often (c) makes me look like a superhero! This place is MAGIC!
Walt Forbes
Ross Turner

ASKER
Basically the reason for wanting to trim  the fat is space really, the db gets replicated to a test and training version of itself in ms sql and well its a requirement for us to have these. However been where i am and saying lets fork out some money and get some more hard drives is a no no at the current moment in time.

Anyway,  i've attached some details of the DB below
db.bmp