SQL 2005 DB Space Allocation
in [Server Configuration]
|
From: RG on 23 Jul 2010 00:01 I have a db which originally occupied 80 gig. I have deleted 80% of the rows in the db. After, I ran reindex and defrag. However, the db space usage is at 89%. I expected that the usage should be at 15% at most. What do I need to do to compact the db pages? Thanks in advance
From: Erland Sommarskog on 23 Jul 2010 09:10 RG (nobody(a)nowhere.com) writes: > I have a db which originally occupied 80 gig. I have deleted 80% of the > rows in the db. After, I ran reindex and defrag. However, the db space > usage is at 89%. I expected that the usage should be at 15% at most. > What do I need to do to compact the db pages? It is unclear how you arrived at these numbers where your estimate of 15% comes from. But now you apparently have a 16 GB database, with only 10% free space. Depending how quickly the database fills up, there may be reason to add a few more GB to get some breathing space. -- Erland Sommarskog, SQL Server MVP, esquel(a)sommarskog.se Links for SQL Server Books Online: SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx SQL 2000: http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
From: Dan on 23 Jul 2010 11:01 "RG" <nobody(a)nowhere.com> wrote in message news:B0BDFEA5-DAFD-430F-A80B-F54261EE09A9(a)microsoft.com... > I have a db which originally occupied 80 gig. I have deleted 80% of the > rows in the db. After, I ran reindex and defrag. However, the db space > usage is at 89%. I expected that the usage should be at 15% at most. > What do I need to do to compact the db pages? > > Thanks in advance What's the recovery mode set to? Is most of the space usage from the log? If you don't have Simple recovery mode you will need to take a log backup to clear down the logged deletion. -- Dan
From: RG on 23 Jul 2010 11:05 I used .. DBCC showfilestats shows.. Fileid FileGroup TotalExtents UsedExtents Name FileName ----------- ----------- -------------------- -------------------- -------------------------------------------------------------------------------------------------------------------------------- ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- 1 1 1230584 1086526 WH_Data C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\wh.mdf "Erland Sommarskog" wrote: > RG (nobody(a)nowhere.com) writes: > > > I have a db which originally occupied 80 gig. I have deleted 80% of the > > rows in the db. After, I ran reindex and defrag. However, the db space > > usage is at 89%. I expected that the usage should be at 15% at most. > > What do I need to do to compact the db pages? > > It is unclear how you arrived at these numbers where your estimate of 15% > comes from. But now you apparently have a 16 GB database, with only 10% > free space. Depending how quickly the database fills up, there may be > reason to add a few more GB to get some breathing space. > > -- > Erland Sommarskog, SQL Server MVP, esquel(a)sommarskog.se > > Links for SQL Server Books Online: > SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx > SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx > SQL 2000: http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx > > . >
From: Dan on 23 Jul 2010 11:14
Ignore my other reply about the log, as I can see you're looking at only the mdf. Do you have a clustered index? What is your page density set to? Dan "RG" <RG(a)discussions.microsoft.com> wrote in message news:A201210A-9A74-448A-8483-C1E1E382F73B(a)microsoft.com... > I used .. > > DBCC showfilestats > > shows.. > > Fileid FileGroup TotalExtents UsedExtents Name > > FileName > ----------- ----------- -------------------- -------------------- > -------------------------------------------------------------------------------------------------------------------------------- > ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- > 1 1 1230584 1086526 WH_Data > > C:\Program Files\Microsoft SQL > Server\MSSQL.1\MSSQL\Data\wh.mdf > > > "Erland Sommarskog" wrote: > >> RG (nobody(a)nowhere.com) writes: >> >> > I have a db which originally occupied 80 gig. I have deleted 80% of >> > the >> > rows in the db. After, I ran reindex and defrag. However, the db >> > space >> > usage is at 89%. I expected that the usage should be at 15% at most. >> > What do I need to do to compact the db pages? >> >> It is unclear how you arrived at these numbers where your estimate of 15% >> comes from. But now you apparently have a 16 GB database, with only 10% >> free space. Depending how quickly the database fills up, there may be >> reason to add a few more GB to get some breathing space. >> >> -- >> Erland Sommarskog, SQL Server MVP, esquel(a)sommarskog.se >> >> Links for SQL Server Books Online: >> SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx >> SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx >> SQL 2000: >> http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx >> >> . >> |