June 25, 2007 at 2:34 pm
Hello,
I had a database that was 40 Gigs (.MDF file)... I deleted lots of records from this database (deleted more than 50%) and then performed DB Shrink which took 3 hours to run, but the database size dropped by 5 Gigs only...
Is this normal? After deleting all those records and tables, I thought that the size will drop to 15 G…
Thanks
June 25, 2007 at 3:36 pm
Hmmm...
Do you have a maintenance plan that cleans up this stuff and does reindexing and all that? I'm not 100% on this but my hunch is that if you deleted all the data but didn't re-index the tables a lot of the data that was delete is still floating around there...
I am just a developer but this is my initial thought...
Hope this helps!
Ben Sullins
bensullins.com
Beer is my primary key...
June 25, 2007 at 3:53 pm
I first deleted all the data, then re-indexed everything and finally did the shrink db...
June 25, 2007 at 3:58 pm
Have you ran DBCC UpdateUsage ?
A.J.
DBA with an attitude
June 25, 2007 at 4:07 pm
no I didnt
is this the correct statement:
DBCC UPDATEUSAGE (mydb)
Thanks
June 25, 2007 at 10:02 pm
hi,fyi ....
ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/tsqlref9/html/b8752ecc-db45-4e23-aee7-13b8bc3cbae2.htm
[font="Verdana"]- Deepak[/font]
June 25, 2007 at 11:43 pm
Check what is the space being used by the data and log file. Also do the shrinking by file rather than doing it at database level. If you say that all records has been removed the size should come down but it happens other wise i your case. So shrink each file and let us know.
Cheers,
Sugeshkumar Rajendran
SQL Server MVP
http://sugeshkr.blogspot.com
June 26, 2007 at 8:05 am
Hi Sugesh,
I shrinked the file.. This is the statement that I used when I did the shrinking:
USE DBNAME
DBCC SHRINKFILE (DBNAME_data)
But as I mentioned the size reduced by 5 gigs only.
I have't done DBCC UpdateUsage .. I will do that over the weekend and I will keep you guys posted
Thanks
June 26, 2007 at 11:28 pm
David,
DBCC Shrinkfile has to go with 2 parameters. As given below
DBCC SHRINKFILE(FILENAME or FILEID, TARGETFILESIZE)
eg:
use tempdb
dbcc shrinkfile(templog, 100)
This will shrink your tempdb database log file to 100MB. Get back in case of any clarifications
Cheers,
Sugeshkumar Rajendran
SQL Server MVP
http://sugeshkr.blogspot.com
June 27, 2007 at 8:04 am
Sugesh,
but this is for log file... my log file is 15 MB which is fine, my problem is the data file and not the log file...
Thanks
June 27, 2007 at 9:19 am
hi david,
ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/tsqlref9/html/e02b2318-bee9-4d84-a61f-2fddcf268c9f.htm fyi.......give both the parameters
[font="Verdana"]- Deepak[/font]
June 27, 2007 at 9:35 am
This query will show you how much unused space you have in each database file.
Use MyDatabase -- Show Size, Space Used, Unused Space, and Name of all database files select [FileSizeMB] = convert(numeric(10,2),round(a.size/128.,2)), [UsedSpaceMB] = convert(numeric(10,2),round(fileproperty( a.name,'SpaceUsed')/128.,2)) , [UnusedSpaceMB] = convert(numeric(10,2),round((a.size-fileproperty( a.name,'SpaceUsed'))/128.,2)) , [DBFileName] = a.name from sysfiles a
This script will do the file shrink for you.
Shrink DB File by Increment to Target Free Space
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=80355
June 28, 2007 at 7:28 am
Did you try running a backup first then shrinking?
June 29, 2007 at 1:07 am
Hi
Try by DBCC DBREINDEX every CLUSTER index (take care with users accessing these tables, it's not an online reindex) and then shrink the database several times. It worked for my some time ago.
June 29, 2007 at 9:56 am
hmm.. sure... I will be doing few things over the weekend.. I will keep you guys posted
Thank you
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply