Friday, March 30, 2012

release database unused space

Hi,
I have deleted 100K records from a table in a database. I ran the DBCC shrin
kfile & DBCC shrinkdatabase to have the database release the space. I see no
reduction in the database size.
I have run the following;
DBCC SHRINKFILE (Virten1_dat, 100) -- Data File Name
GO
DBCC SHRINKFILE (Virten3_dat, 100) -- Log File Name
GO
BACKUP LOG VIRTEN WITH TRUNCATE_ONLY
GO
DBCC SHRICKDATABASE (VIRTEN, 20)
GO
Backup database VIRTEN to disk = 'F:\DBBkp\VIRTEN.dmp'
GO
Please can anyone tell me, where I am making a mistake, or provide me with a
nother method to release the space.
Many thanks.
DanielPerhaps you need to defrag the indexes first? (DBCC DBREINDEX or DBCC INDEXD
EFRAG.)
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Daniel" <danieljantony@.hotmail.com> wrote in message
news:D364CB21-9A8C-4D7A-942B-0C2038BA0675@.microsoft.com...
> Hi,
> I have deleted 100K records from a table in a database. I ran the DBCC shrinkfile
& DBCC shrinkdatabase to
have the database release the space. I see no reduction in the database size.">
> I have run the following;
> DBCC SHRINKFILE (Virten1_dat, 100) -- Data File Name
> GO
> DBCC SHRINKFILE (Virten3_dat, 100) -- Log File Name
> GO
> BACKUP LOG VIRTEN WITH TRUNCATE_ONLY
> GO
> DBCC SHRICKDATABASE (VIRTEN, 20)
> GO
> Backup database VIRTEN to disk = 'F:\DBBkp\VIRTEN.dmp'
> GO
> Please can anyone tell me, where I am making a mistake, or provide me with another
method to release the
space.
> Many thanks.
> Daniel|||What Tibor is alluding to is that fact that just because you deleted a bunch
of records, it doesn't mean that pages have been freed. If the pattern of
your deletes is such that only one record per page is deleted, no pages will
be deallocated and so no space can be reclaimed by shrink.
You can compact such pages using DBCC DBREINDEX or DBCC INDEXDEFRAG (if you
have a clustered index). There's no such mechanism for heaps except bcp
out/in.
You should also be aware that if you shrink the database it will most likely
grow again if there is any insert/update activity on it.
Regards.
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:evs41PvTEHA.3336@.TK2MSFTNGP10.phx.gbl...
> Perhaps you need to defrag the indexes first? (DBCC DBREINDEX or DBCC
INDEXDEFRAG.)
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Daniel" <danieljantony@.hotmail.com> wrote in message
> news:D364CB21-9A8C-4D7A-942B-0C2038BA0675@.microsoft.com...
shrinkfile & DBCC shrinkdatabase to[vbcol=seagreen]
> have the database release the space. I see no reduction in the database
size.
with another method to release the[vbcol=seagreen]
> space.
>sql

No comments:

Post a Comment