How to release Table unused space

Hi all
I am having some disk space issue, and I notice that I have some tables with
a lot of GB of unused space.
Is it a way to claim and release that unused space?
To give you a better picture, I have just one table with below information
Rows = 131977895
Reserved =146270344 KB
Index_Size = 2767616 KB
Data = 70372568 KB
Unused = 73130160 KB <-- here is my concernd almost 70 GB unused !!!
This table is reindexed every sunday (yesterday was the last reindex) and it
does not look to have fragmentation issues, when I ran a DBCC Showcontig I
get these results:
DBCC SHOWCONTIG scanning 'Sales' table...
Table: 'Sales' (1228791685); index ID: 1, database ID: 16
TABLE level scan performed.
- Pages Scanned........................: 8796978
- Extents Scanned.......................: 1101956
- Extent Switches.......................: 1101956
- Avg. Pages per Extent..................: 8.0
- Scan Density [Best Count:Actual Count]......: 99.79% [1099623:1101957]
- Logical Scan Fragmentation ..............: 0.04%
- Extent Scan Fragmentation ...............: 9.80%
- Avg. Bytes Free per Page................: 647.0
- Avg. Page Density (full)................: 92.01%
DBCC execution completed. If DBCC printed error messages, contact your
system administrator.
Could someone give me an idea on how to release the 70 GB of unused space?
Thanks a lot
JuanDo you have Text or image columns? Did you drop any recently?
--
Andrew J. Kelly SQL MVP
"Juan" <Juan@.discussions.microsoft.com> wrote in message
news:A6F92E41-49D3-4D64-9123-63FE8740E18E@.microsoft.com...
> Hi all
> I am having some disk space issue, and I notice that I have some tables
> with
> a lot of GB of unused space.
> Is it a way to claim and release that unused space?
> To give you a better picture, I have just one table with below information
> Rows = 131977895
> Reserved =146270344 KB
> Index_Size = 2767616 KB
> Data = 70372568 KB
> Unused = 73130160 KB <-- here is my concernd almost 70 GB unused !!!
>
> This table is reindexed every sunday (yesterday was the last reindex) and
> it
> does not look to have fragmentation issues, when I ran a DBCC Showcontig I
> get these results:
> DBCC SHOWCONTIG scanning 'Sales' table...
> Table: 'Sales' (1228791685); index ID: 1, database ID: 16
> TABLE level scan performed.
> - Pages Scanned........................: 8796978
> - Extents Scanned.......................: 1101956
> - Extent Switches.......................: 1101956
> - Avg. Pages per Extent..................: 8.0
> - Scan Density [Best Count:Actual Count]......: 99.79% [1099623:1101957]
> - Logical Scan Fragmentation ..............: 0.04%
> - Extent Scan Fragmentation ...............: 9.80%
> - Avg. Bytes Free per Page................: 647.0
> - Avg. Page Density (full)................: 92.01%
> DBCC execution completed. If DBCC printed error messages, contact your
> system administrator.
>
> Could someone give me an idea on how to release the 70 GB of unused space?
> Thanks a lot
> Juan
>|||Juan,
Your numbers don't match.
The first data you mention is 132 million rows, and 50% unused space.
But then the DBCC SHOWCONTIG output reports 1,228 millions rows and 10%
unused space.
Gert-Jan
Juan wrote:
> Hi all
> I am having some disk space issue, and I notice that I have some tables with
> a lot of GB of unused space.
> Is it a way to claim and release that unused space?
> To give you a better picture, I have just one table with below information
> Rows = 131977895
> Reserved =146270344 KB
> Index_Size = 2767616 KB
> Data = 70372568 KB
> Unused = 73130160 KB <-- here is my concernd almost 70 GB unused !!!
> This table is reindexed every sunday (yesterday was the last reindex) and it
> does not look to have fragmentation issues, when I ran a DBCC Showcontig I
> get these results:
> DBCC SHOWCONTIG scanning 'Sales' table...
> Table: 'Sales' (1228791685); index ID: 1, database ID: 16
> TABLE level scan performed.
> - Pages Scanned........................: 8796978
> - Extents Scanned.......................: 1101956
> - Extent Switches.......................: 1101956
> - Avg. Pages per Extent..................: 8.0
> - Scan Density [Best Count:Actual Count]......: 99.79% [1099623:1101957]
> - Logical Scan Fragmentation ..............: 0.04%
> - Extent Scan Fragmentation ...............: 9.80%
> - Avg. Bytes Free per Page................: 647.0
> - Avg. Page Density (full)................: 92.01%
> DBCC execution completed. If DBCC printed error messages, contact your
> system administrator.
> Could someone give me an idea on how to release the 70 GB of unused space?
> Thanks a lot
> Juan|||Did you get your numbers below from sp_spaceused? You cannot rely on this
for accurate information. DBCC updateusage will help.
Note that from showcontig output your pages are mostly full --> not much
unused space.
--
TheSQLGuru
President
Indicium Resources, Inc.
"Juan" <Juan@.discussions.microsoft.com> wrote in message
news:A6F92E41-49D3-4D64-9123-63FE8740E18E@.microsoft.com...
> Hi all
> I am having some disk space issue, and I notice that I have some tables
> with
> a lot of GB of unused space.
> Is it a way to claim and release that unused space?
> To give you a better picture, I have just one table with below information
> Rows = 131977895
> Reserved =146270344 KB
> Index_Size = 2767616 KB
> Data = 70372568 KB
> Unused = 73130160 KB <-- here is my concernd almost 70 GB unused !!!
>
> This table is reindexed every sunday (yesterday was the last reindex) and
> it
> does not look to have fragmentation issues, when I ran a DBCC Showcontig I
> get these results:
> DBCC SHOWCONTIG scanning 'Sales' table...
> Table: 'Sales' (1228791685); index ID: 1, database ID: 16
> TABLE level scan performed.
> - Pages Scanned........................: 8796978
> - Extents Scanned.......................: 1101956
> - Extent Switches.......................: 1101956
> - Avg. Pages per Extent..................: 8.0
> - Scan Density [Best Count:Actual Count]......: 99.79% [1099623:1101957]
> - Logical Scan Fragmentation ..............: 0.04%
> - Extent Scan Fragmentation ...............: 9.80%
> - Avg. Bytes Free per Page................: 647.0
> - Avg. Page Density (full)................: 92.01%
> DBCC execution completed. If DBCC printed error messages, contact your
> system administrator.
>
> Could someone give me an idea on how to release the 70 GB of unused space?
> Thanks a lot
> Juan
>|||Ey, you are right, I ran a DBCC updateusage and it fix the numbers, I am
getting now just 300 KB of unused space, this makes sense now.
Thanks a lot, I'll keep on mind to run a DBCC UpdateUsage next time I check
the sizes of my tables.
Juan
"TheSQLGuru" wrote:
> Did you get your numbers below from sp_spaceused? You cannot rely on this
> for accurate information. DBCC updateusage will help.
> Note that from showcontig output your pages are mostly full --> not much
> unused space.
> --
> TheSQLGuru
> President
> Indicium Resources, Inc.
> "Juan" <Juan@.discussions.microsoft.com> wrote in message
> news:A6F92E41-49D3-4D64-9123-63FE8740E18E@.microsoft.com...
> > Hi all
> >
> > I am having some disk space issue, and I notice that I have some tables
> > with
> > a lot of GB of unused space.
> >
> > Is it a way to claim and release that unused space?
> >
> > To give you a better picture, I have just one table with below information
> > Rows = 131977895
> > Reserved =146270344 KB
> > Index_Size = 2767616 KB
> > Data = 70372568 KB
> > Unused = 73130160 KB <-- here is my concernd almost 70 GB unused !!!
> >
> >
> > This table is reindexed every sunday (yesterday was the last reindex) and
> > it
> > does not look to have fragmentation issues, when I ran a DBCC Showcontig I
> > get these results:
> > DBCC SHOWCONTIG scanning 'Sales' table...
> > Table: 'Sales' (1228791685); index ID: 1, database ID: 16
> > TABLE level scan performed.
> > - Pages Scanned........................: 8796978
> > - Extents Scanned.......................: 1101956
> > - Extent Switches.......................: 1101956
> > - Avg. Pages per Extent..................: 8.0
> > - Scan Density [Best Count:Actual Count]......: 99.79% [1099623:1101957]
> > - Logical Scan Fragmentation ..............: 0.04%
> > - Extent Scan Fragmentation ...............: 9.80%
> > - Avg. Bytes Free per Page................: 647.0
> > - Avg. Page Density (full)................: 92.01%
> > DBCC execution completed. If DBCC printed error messages, contact your
> > system administrator.
> >
> >
> > Could someone give me an idea on how to release the 70 GB of unused space?
> >
> > Thanks a lot
> >
> > Juan
> >
>
>

How to release Table unused space

Hi all
I am having some disk space issue, and I notice that I have some tables with
a lot of GB of unused space.
Is it a way to claim and release that unused space?
To give you a better picture, I have just one table with below information
Rows = 131977895
Reserved =146270344 KB
Index_Size = 2767616 KB
Data = 70372568 KB
Unused = 73130160 KB <-- here is my concernd almost 70 GB unused !!!
This table is reindexed every sunday (yesterday was the last reindex) and it
does not look to have fragmentation issues, when I ran a DBCC Showcontig I
get these results:
DBCC SHOWCONTIG scanning 'Sales' table...
Table: 'Sales' (1228791685); index ID: 1, database ID: 16
TABLE level scan performed.
- Pages Scanned........................: 8796978
- Extents Scanned.......................: 1101956
- Extent Switches.......................: 1101956
- Avg. Pages per Extent..................: 8.0
- Scan Density [Best Count:Actual Count]......: 99.79% [1099623:1101957]
- Logical Scan Fragmentation ..............: 0.04%
- Extent Scan Fragmentation ...............: 9.80%
- Avg. Bytes Free per Page................: 647.0
- Avg. Page Density (full)................: 92.01%
DBCC execution completed. If DBCC printed error messages, contact your
system administrator.
Could someone give me an idea on how to release the 70 GB of unused space?
Thanks a lot
Juan
Do you have Text or image columns? Did you drop any recently?
Andrew J. Kelly SQL MVP
"Juan" <Juan@.discussions.microsoft.com> wrote in message
news:A6F92E41-49D3-4D64-9123-63FE8740E18E@.microsoft.com...
> Hi all
> I am having some disk space issue, and I notice that I have some tables
> with
> a lot of GB of unused space.
> Is it a way to claim and release that unused space?
> To give you a better picture, I have just one table with below information
> Rows = 131977895
> Reserved =146270344 KB
> Index_Size = 2767616 KB
> Data = 70372568 KB
> Unused = 73130160 KB <-- here is my concernd almost 70 GB unused !!!
>
> This table is reindexed every sunday (yesterday was the last reindex) and
> it
> does not look to have fragmentation issues, when I ran a DBCC Showcontig I
> get these results:
> DBCC SHOWCONTIG scanning 'Sales' table...
> Table: 'Sales' (1228791685); index ID: 1, database ID: 16
> TABLE level scan performed.
> - Pages Scanned........................: 8796978
> - Extents Scanned.......................: 1101956
> - Extent Switches.......................: 1101956
> - Avg. Pages per Extent..................: 8.0
> - Scan Density [Best Count:Actual Count]......: 99.79% [1099623:1101957]
> - Logical Scan Fragmentation ..............: 0.04%
> - Extent Scan Fragmentation ...............: 9.80%
> - Avg. Bytes Free per Page................: 647.0
> - Avg. Page Density (full)................: 92.01%
> DBCC execution completed. If DBCC printed error messages, contact your
> system administrator.
>
> Could someone give me an idea on how to release the 70 GB of unused space?
> Thanks a lot
> Juan
>
|||Juan,
Your numbers don't match.
The first data you mention is 132 million rows, and 50% unused space.
But then the DBCC SHOWCONTIG output reports 1,228 millions rows and 10%
unused space.
Gert-Jan
Juan wrote:
> Hi all
> I am having some disk space issue, and I notice that I have some tables with
> a lot of GB of unused space.
> Is it a way to claim and release that unused space?
> To give you a better picture, I have just one table with below information
> Rows = 131977895
> Reserved =146270344 KB
> Index_Size = 2767616 KB
> Data = 70372568 KB
> Unused = 73130160 KB <-- here is my concernd almost 70 GB unused !!!
> This table is reindexed every sunday (yesterday was the last reindex) and it
> does not look to have fragmentation issues, when I ran a DBCC Showcontig I
> get these results:
> DBCC SHOWCONTIG scanning 'Sales' table...
> Table: 'Sales' (1228791685); index ID: 1, database ID: 16
> TABLE level scan performed.
> - Pages Scanned........................: 8796978
> - Extents Scanned.......................: 1101956
> - Extent Switches.......................: 1101956
> - Avg. Pages per Extent..................: 8.0
> - Scan Density [Best Count:Actual Count]......: 99.79% [1099623:1101957]
> - Logical Scan Fragmentation ..............: 0.04%
> - Extent Scan Fragmentation ...............: 9.80%
> - Avg. Bytes Free per Page................: 647.0
> - Avg. Page Density (full)................: 92.01%
> DBCC execution completed. If DBCC printed error messages, contact your
> system administrator.
> Could someone give me an idea on how to release the 70 GB of unused space?
> Thanks a lot
> Juan
|||Did you get your numbers below from sp_spaceused? You cannot rely on this
for accurate information. DBCC updateusage will help.
Note that from showcontig output your pages are mostly full --> not much
unused space.
TheSQLGuru
President
Indicium Resources, Inc.
"Juan" <Juan@.discussions.microsoft.com> wrote in message
news:A6F92E41-49D3-4D64-9123-63FE8740E18E@.microsoft.com...
> Hi all
> I am having some disk space issue, and I notice that I have some tables
> with
> a lot of GB of unused space.
> Is it a way to claim and release that unused space?
> To give you a better picture, I have just one table with below information
> Rows = 131977895
> Reserved =146270344 KB
> Index_Size = 2767616 KB
> Data = 70372568 KB
> Unused = 73130160 KB <-- here is my concernd almost 70 GB unused !!!
>
> This table is reindexed every sunday (yesterday was the last reindex) and
> it
> does not look to have fragmentation issues, when I ran a DBCC Showcontig I
> get these results:
> DBCC SHOWCONTIG scanning 'Sales' table...
> Table: 'Sales' (1228791685); index ID: 1, database ID: 16
> TABLE level scan performed.
> - Pages Scanned........................: 8796978
> - Extents Scanned.......................: 1101956
> - Extent Switches.......................: 1101956
> - Avg. Pages per Extent..................: 8.0
> - Scan Density [Best Count:Actual Count]......: 99.79% [1099623:1101957]
> - Logical Scan Fragmentation ..............: 0.04%
> - Extent Scan Fragmentation ...............: 9.80%
> - Avg. Bytes Free per Page................: 647.0
> - Avg. Page Density (full)................: 92.01%
> DBCC execution completed. If DBCC printed error messages, contact your
> system administrator.
>
> Could someone give me an idea on how to release the 70 GB of unused space?
> Thanks a lot
> Juan
>
|||Ey, you are right, I ran a DBCC updateusage and it fix the numbers, I am
getting now just 300 KB of unused space, this makes sense now.
Thanks a lot, I'll keep on mind to run a DBCC UpdateUsage next time I check
the sizes of my tables.
Juan
"TheSQLGuru" wrote:

> Did you get your numbers below from sp_spaceused? You cannot rely on this
> for accurate information. DBCC updateusage will help.
> Note that from showcontig output your pages are mostly full --> not much
> unused space.
> --
> TheSQLGuru
> President
> Indicium Resources, Inc.
> "Juan" <Juan@.discussions.microsoft.com> wrote in message
> news:A6F92E41-49D3-4D64-9123-63FE8740E18E@.microsoft.com...
>
>

How to release Table unused space

Hi all
I am having some disk space issue, and I notice that I have some tables with
a lot of GB of unused space.
Is it a way to claim and release that unused space?
To give you a better picture, I have just one table with below information
Rows = 131977895
Reserved =146270344 KB
Index_Size = 2767616 KB
Data = 70372568 KB
Unused = 73130160 KB <-- here is my concernd almost 70 GB unused !!!
This table is reindexed every sunday (yesterday was the last reindex) and it
does not look to have fragmentation issues, when I ran a DBCC Showcontig I
get these results:
DBCC SHOWCONTIG scanning 'Sales' table...
Table: 'Sales' (1228791685); index ID: 1, database ID: 16
TABLE level scan performed.
- Pages Scanned........................: 8796978
- Extents Scanned.......................: 1101956
- Extent Switches.......................: 1101956
- Avg. Pages per Extent..................: 8.0
- Scan Density [Best Count:Actual Count]......: 99.79% [1099623:110
1957]
- Logical Scan Fragmentation ..............: 0.04%
- Extent Scan Fragmentation ...............: 9.80%
- Avg. Bytes Free per Page................: 647.0
- Avg. Page Density (full)................: 92.01%
DBCC execution completed. If DBCC printed error messages, contact your
system administrator.
Could someone give me an idea on how to release the 70 GB of unused space?
Thanks a lot
JuanDo you have Text or image columns? Did you drop any recently?
Andrew J. Kelly SQL MVP
"Juan" <Juan@.discussions.microsoft.com> wrote in message
news:A6F92E41-49D3-4D64-9123-63FE8740E18E@.microsoft.com...
> Hi all
> I am having some disk space issue, and I notice that I have some tables
> with
> a lot of GB of unused space.
> Is it a way to claim and release that unused space?
> To give you a better picture, I have just one table with below information
> Rows = 131977895
> Reserved =146270344 KB
> Index_Size = 2767616 KB
> Data = 70372568 KB
> Unused = 73130160 KB <-- here is my concernd almost 70 GB unused !!!
>
> This table is reindexed every sunday (yesterday was the last reindex) and
> it
> does not look to have fragmentation issues, when I ran a DBCC Showcontig I
> get these results:
> DBCC SHOWCONTIG scanning 'Sales' table...
> Table: 'Sales' (1228791685); index ID: 1, database ID: 16
> TABLE level scan performed.
> - Pages Scanned........................: 8796978
> - Extents Scanned.......................: 1101956
> - Extent Switches.......................: 1101956
> - Avg. Pages per Extent..................: 8.0
> - Scan Density [Best Count:Actual Count]......: 99.79% [1099623:1
101957]
> - Logical Scan Fragmentation ..............: 0.04%
> - Extent Scan Fragmentation ...............: 9.80%
> - Avg. Bytes Free per Page................: 647.0
> - Avg. Page Density (full)................: 92.01%
> DBCC execution completed. If DBCC printed error messages, contact your
> system administrator.
>
> Could someone give me an idea on how to release the 70 GB of unused space?
> Thanks a lot
> Juan
>|||Juan,
Your numbers don't match.
The first data you mention is 132 million rows, and 50% unused space.
But then the DBCC SHOWCONTIG output reports 1,228 millions rows and 10%
unused space.
Gert-Jan
Juan wrote:
> Hi all
> I am having some disk space issue, and I notice that I have some tables wi
th
> a lot of GB of unused space.
> Is it a way to claim and release that unused space?
> To give you a better picture, I have just one table with below information
> Rows = 131977895
> Reserved =146270344 KB
> Index_Size = 2767616 KB
> Data = 70372568 KB
> Unused = 73130160 KB <-- here is my concernd almost 70 GB unused !!!
> This table is reindexed every sunday (yesterday was the last reindex) and
it
> does not look to have fragmentation issues, when I ran a DBCC Showcontig I
> get these results:
> DBCC SHOWCONTIG scanning 'Sales' table...
> Table: 'Sales' (1228791685); index ID: 1, database ID: 16
> TABLE level scan performed.
> - Pages Scanned........................: 8796978
> - Extents Scanned.......................: 1101956
> - Extent Switches.......................: 1101956
> - Avg. Pages per Extent..................: 8.0
> - Scan Density [Best Count:Actual Count]......: 99.79% [1099623:1
101957]
> - Logical Scan Fragmentation ..............: 0.04%
> - Extent Scan Fragmentation ...............: 9.80%
> - Avg. Bytes Free per Page................: 647.0
> - Avg. Page Density (full)................: 92.01%
> DBCC execution completed. If DBCC printed error messages, contact your
> system administrator.
> Could someone give me an idea on how to release the 70 GB of unused space?
> Thanks a lot
> Juan|||Did you get your numbers below from sp_spaceused? You cannot rely on this
for accurate information. DBCC updateusage will help.
Note that from showcontig output your pages are mostly full --> not much
unused space.
TheSQLGuru
President
Indicium Resources, Inc.
"Juan" <Juan@.discussions.microsoft.com> wrote in message
news:A6F92E41-49D3-4D64-9123-63FE8740E18E@.microsoft.com...
> Hi all
> I am having some disk space issue, and I notice that I have some tables
> with
> a lot of GB of unused space.
> Is it a way to claim and release that unused space?
> To give you a better picture, I have just one table with below information
> Rows = 131977895
> Reserved =146270344 KB
> Index_Size = 2767616 KB
> Data = 70372568 KB
> Unused = 73130160 KB <-- here is my concernd almost 70 GB unused !!!
>
> This table is reindexed every sunday (yesterday was the last reindex) and
> it
> does not look to have fragmentation issues, when I ran a DBCC Showcontig I
> get these results:
> DBCC SHOWCONTIG scanning 'Sales' table...
> Table: 'Sales' (1228791685); index ID: 1, database ID: 16
> TABLE level scan performed.
> - Pages Scanned........................: 8796978
> - Extents Scanned.......................: 1101956
> - Extent Switches.......................: 1101956
> - Avg. Pages per Extent..................: 8.0
> - Scan Density [Best Count:Actual Count]......: 99.79% [1099623:1
101957]
> - Logical Scan Fragmentation ..............: 0.04%
> - Extent Scan Fragmentation ...............: 9.80%
> - Avg. Bytes Free per Page................: 647.0
> - Avg. Page Density (full)................: 92.01%
> DBCC execution completed. If DBCC printed error messages, contact your
> system administrator.
>
> Could someone give me an idea on how to release the 70 GB of unused space?
> Thanks a lot
> Juan
>|||Ey, you are right, I ran a DBCC updateusage and it fix the numbers, I am
getting now just 300 KB of unused space, this makes sense now.
Thanks a lot, I'll keep on mind to run a DBCC UpdateUsage next time I check
the sizes of my tables.
Juan
"TheSQLGuru" wrote:

> Did you get your numbers below from sp_spaceused? You cannot rely on this
> for accurate information. DBCC updateusage will help.
> Note that from showcontig output your pages are mostly full --> not much
> unused space.
> --
> TheSQLGuru
> President
> Indicium Resources, Inc.
> "Juan" <Juan@.discussions.microsoft.com> wrote in message
> news:A6F92E41-49D3-4D64-9123-63FE8740E18E@.microsoft.com...
>
>

How to release large unused/allocated space

Hi,

I have 6 filegroups that contain over 30 gigs of data. I manage the transaction log space efficiently by using a script and 'shrink database'. But, the allocated unused space of the data files are very large, over 30 gigs.

The configuration is standard for all files:

- Automatically grow file by percent

- Maximum file size - Unrestricted

How do I release the unused space?

Thanks,

- gshaf

Hi Gshaf,

You can issue the DBCC SHRINKDATABASE statement with TRUNCATEONLY. This will shrink the database the maximum amount possible, and release the space back to windows. If you want to control how much space in the db left for growth, you'll need to issue DBCC SHRINKFILE and specify the amount (MB) of space you want left:

DBCC SHRINKFILE(MyDataFileNameHere, 15000)

The above command will attempt to shrink the datafile "MyDataFileNameHere" to 15GB.

Cheers,

Rob

sql

How to release large unused/allocated space

Hi,

I have 6 filegroups that contain over 30 gigs of data. I manage the transaction log space efficiently by using a script and 'shrink database'. But, the allocated unused space of the data files are very large, over 30 gigs.

The configuration is standard for all files:

- Automatically grow file by percent

- Maximum file size - Unrestricted

How do I release the unused space?

Thanks,

- gshaf

Hi Gshaf,

You can issue the DBCC SHRINKDATABASE statement with TRUNCATEONLY. This will shrink the database the maximum amount possible, and release the space back to windows. If you want to control how much space in the db left for growth, you'll need to issue DBCC SHRINKFILE and specify the amount (MB) of space you want left:

DBCC SHRINKFILE(MyDataFileNameHere, 15000)

The above command will attempt to shrink the datafile "MyDataFileNameHere" to 15GB.

Cheers,

Rob

How to reduce white space when object is hidden?

The report is made of one list containing 2 tables, one below each other.
Depending on the report parameters, the bottom table is made visible or
hidden.
When that table is hidden at run time, RS still reserves the same amount of
white space that the table takes in the report design layout.
This causes RS to render an extra white page at the end of the report where
it would display the table. Since the table is hidden, nothing is displayed
in this extra white page.
Does anyone know a workaround that would prevent the white page at the end
of the report?Hi Pat,
Thanks for your post.
From your descriptions, I understood the report will reserve some the
space for that hidden table when there is two tables in the Report. If I
have misunderstood your concern, please feel free to point it out.
I have tested on my side but it seems RS won't reserve the place for hidden
objects. Would you please show me a sample with AdventureWorks, with which
I could reproduce it on my side?
Sincerely yours,
Michael Cheng
Microsoft Online Partner Support
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
=====================================================
This posting is provided "AS IS" with no warranties, and confers no rights.|||Did you try the scenario where both table are contained in one list?
"Michael Cheng [MSFT]" wrote:
> Hi Pat,
> Thanks for your post.
> From your descriptions, I understood the report will reserve some the
> space for that hidden table when there is two tables in the Report. If I
> have misunderstood your concern, please feel free to point it out.
> I have tested on my side but it seems RS won't reserve the place for hidden
> objects. Would you please show me a sample with AdventureWorks, with which
> I could reproduce it on my side?
>
> Sincerely yours,
> Michael Cheng
> Microsoft Online Partner Support
> When responding to posts, please "Reply to Group" via your newsreader so
> that others may learn and benefit from your issue.
> =====================================================> This posting is provided "AS IS" with no warranties, and confers no rights.
>|||Hi Pat,
Thanks for your patience.
If you set the visibility like this:
<Visibility>
<Hidden>true</Hidden>
</Visibility>
This means that the table is always hidden. The space is preserved for
always hidden items. If you changed visibility according to Expression, it
will not reduce the white space.
However, I have tested the scenario that both table are contained in one
list as you described. It will do leave the blank and I am afraid currently
we cannot eliminate this behavior.
Sincerely yours,
Michael Cheng
Microsoft Online Partner Support
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
=====================================================
This posting is provided "AS IS" with no warranties, and confers no rights.

How to reduce the unallocated space of a database?

I tried DBCC SHRINKDATABASE, however, can not reduce the unallocated space. Please help!Press rite click on a database u want n then goto all tasks>shrink database

press files button
select database file u want to shrink
then select shrink file to (type used space)
press ok button|||You can also set the database to auto_shrink. Enterprise manager: right-click on your database, choose properties and then the options tabl. You'll see just below the middle on the right the option auto_shrink.

How to reduce the data space allocated ?

Hi All,
I don't know how to change data space allocated ?
My SQL data have 10GB, but the "space allocated" was allocated 50GB.
The usage of data storage is 79%. I want to restore 20GB space from
the "space allocated". How to do that ?
DBCC SHRINKDATABASE
<http://msdn.microsoft.com/library/en..._dbcc_3pd1.asp>
or
DBCC SHRINKFILE
<http://msdn.microsoft.com/library/en..._dbcc_8b51.asp>
but you should be careful with these because if you're shrinking the
data file(s) these commands will 1) potentially create a stack of
transaction log records (and therefore blow out your transaction log
file size if you're using FULL or BULK-LOGGED recovery model) and 2)
cause inefficiencies if the file(s) that you shrink will need to grow again.
Here is some more info about it from a couple of SQL experts (Aaron
Bertrand & Tibor Karaszi) for your research:
How do I reclaim space in SQL Server?
<http://www.aspfaq.com/show.asp?id=2471>
Why you want to be restrictive with shrink of data files
<http://www.karaszi.com/SQLServer/info_dont_shrink.asp>
Hope this helps.
*mike hodgson*
blog: http://sqlnerd.blogspot.com
kelvinweb@.gmail.com wrote:

>Hi All,
>I don't know how to change data space allocated ?
>My SQL data have 10GB, but the "space allocated" was allocated 50GB.
>The usage of data storage is 79%. I want to restore 20GB space from
>the "space allocated". How to do that ?
>
>
sql

How to reduce the data space allocated ?

Hi All,
I don't know how to change data space allocated ?
My SQL data have 10GB, but the "space allocated" was allocated 50GB.
The usage of data storage is 79%. I want to restore 20GB space from
the "space allocated". How to do that ?DBCC SHRINKDATABASE
<http://msdn.microsoft.com/library/e...s_dbcc_3pd1.asp>
or
DBCC SHRINKFILE
<http://msdn.microsoft.com/library/e...s_dbcc_8b51.asp>
but you should be careful with these because if you're shrinking the
data file(s) these commands will 1) potentially create a stack of
transaction log records (and therefore blow out your transaction log
file size if you're using FULL or BULK-LOGGED recovery model) and 2)
cause inefficiencies if the file(s) that you shrink will need to grow again.
Here is some more info about it from a couple of SQL experts (Aaron
Bertrand & Tibor Karaszi) for your research:
How do I reclaim space in SQL Server?
<http://www.aspfaq.com/show.asp?id=2471>
Why you want to be restrictive with shrink of data files
<http://www.karaszi.com/SQLServer/info_dont_shrink.asp>
Hope this helps.
*mike hodgson*
blog: http://sqlnerd.blogspot.com
kelvinweb@.gmail.com wrote:

>Hi All,
>I don't know how to change data space allocated ?
>My SQL data have 10GB, but the "space allocated" was allocated 50GB.
>The usage of data storage is 79%. I want to restore 20GB space from
>the "space allocated". How to do that ?
>
>

How to reduce the data space allocated ?

Hi All,
I don't know how to change data space allocated ?
My SQL data have 10GB, but the "space allocated" was allocated 50GB.
The usage of data storage is 79%. I want to restore 20GB space from
the "space allocated". How to do that ?This is a multi-part message in MIME format.
--040706030801020607030300
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
DBCC SHRINKDATABASE
<http://msdn.microsoft.com/library/en-us/tsqlref/ts_dbcc_3pd1.asp>
or
DBCC SHRINKFILE
<http://msdn.microsoft.com/library/en-us/tsqlref/ts_dbcc_8b51.asp>
but you should be careful with these because if you're shrinking the
data file(s) these commands will 1) potentially create a stack of
transaction log records (and therefore blow out your transaction log
file size if you're using FULL or BULK-LOGGED recovery model) and 2)
cause inefficiencies if the file(s) that you shrink will need to grow again.
Here is some more info about it from a couple of SQL experts (Aaron
Bertrand & Tibor Karaszi) for your research:
How do I reclaim space in SQL Server?
<http://www.aspfaq.com/show.asp?id=2471>
Why you want to be restrictive with shrink of data files
<http://www.karaszi.com/SQLServer/info_dont_shrink.asp>
Hope this helps.
--
*mike hodgson*
blog: http://sqlnerd.blogspot.com
kelvinweb@.gmail.com wrote:
>Hi All,
>I don't know how to change data space allocated ?
>My SQL data have 10GB, but the "space allocated" was allocated 50GB.
>The usage of data storage is 79%. I want to restore 20GB space from
>the "space allocated". How to do that ?
>
>
--040706030801020607030300
Content-Type: text/html; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
<meta content="text/html;charset=ISO-8859-1" http-equiv="Content-Type">
</head>
<body bgcolor="#ffffff" text="#000000">
<tt><a
href="http://links.10026.com/?link=DBCC">http://msdn.microsoft.com/library/en-us/tsqlref/ts_dbcc_3pd1.asp">DBCC
SHRINKDATABASE</a><br>
or<br>
<a
href="http://links.10026.com/?link=DBCC">http://msdn.microsoft.com/library/en-us/tsqlref/ts_dbcc_8b51.asp">DBCC
SHRINKFILE</a><br>
<br>
but you should be careful with these because if you're shrinking the
data file(s) these commands will 1) potentially create a stack of
transaction log records (and therefore blow out your transaction log
file size if you're using FULL or BULK-LOGGED recovery model) and 2)
cause inefficiencies if the file(s) that you shrink will need to grow
again.<br>
<br>
Here is some more info about it from a couple of SQL experts (Aaron
Bertrand & Tibor Karaszi) for your research:<br>
<a href="http://links.10026.com/?link=How">http://www.aspfaq.com/show.asp?id=2471">How do I reclaim space
in SQL Server?</a><br>
<a href="http://links.10026.com/?link=Why">http://www.karaszi.com/SQLServer/info_dont_shrink.asp">Why you
want to be restrictive with shrink of data files</a><br>
<br>
Hope this helps.<br>
</tt>
<div class="moz-signature">
<title></title>
<meta http-equiv="Content-Type" content="text/html; ">
<p><span lang="en-au"><font face="Tahoma" size="2">--<br>
</font></span> <b><span lang="en-au"><font face="Tahoma" size="2">mike
hodgson</font></span></b><span lang="en-au"><br>
<font face="Tahoma" size="2">blog:</font><font face="Tahoma" size="2"> <a
href="http://links.10026.com/?link=http://sqlnerd.blogspot.com</a></font></span>">http://sqlnerd.blogspot.com">http://sqlnerd.blogspot.com</a></font></span>
</p>
</div>
<br>
<br>
<a class="moz-txt-link-abbreviated" href="http://links.10026.com/?link=mailto:kelvinweb@.gmail.com">kelvinweb@.gmail.com</a> wrote:
<blockquote
cite="mid1128560354.037374.148950@.o13g2000cwo.googlegroups.com"
type="cite">
<pre wrap="">Hi All,
I don't know how to change data space allocated ?
My SQL data have 10GB, but the "space allocated" was allocated 50GB.
The usage of data storage is 79%. I want to restore 20GB space from
the "space allocated". How to do that ?
</pre>
</blockquote>
</body>
</html>
--040706030801020607030300--

How to reduce database size/ getting back unused space to OS

Hi All,
I have got Central site SMS database whose size is 27 GB. But when I browse it through SQl Enterprise manager, I could see there that only 12GB space is used and rest of 15 GB space is free. I have already tried shrinking the database but could not succed
ed. The SQL command which I have used is:
DBCC shrinkdatabse (SMS_DB, Truncateonly)
Could anybody suggest how to get space released to the operating system from the databse which is unused.
Thanks in advance
Sheetu
First, run sp_helpfile to get the file sizes.
If your data file is what's taking up the space, I usually like to do the
following things...
USE SMS_DB
GO
sp_msforeachtable 'DBCC DBREINDEX(''?'', 100)' --if you don't mind the
tables being locked... otherwise:
sp_msforeachtable 'DBCC INDEXDEFRAG(0, ''?'', 1)' --This will defrag all of
the clustered indexes only
--Then, shrink the file(s):
DBCC SHRINKFILE(1, 1, truncateonly)
...
If it's your log file(s)...
You can either backup the log, or if you don't mind losing it, run:
BACKUP LOG SMS_DB WITH TRUNCATE_ONLY
Then, you can run:
USE SMS_DB
GO
DBCC SHRINKFILE(2, emptyfile) --assuming the log is fileid 2
"Sheetu" <Sheetu@.discussions.microsoft.com> wrote in message
news:892F1961-304B-4574-B87D-97FB2414DF54@.microsoft.com...
> Hi All,
> I have got Central site SMS database whose size is 27 GB. But when I
browse it through SQl Enterprise manager, I could see there that only 12GB
space is used and rest of 15 GB space is free. I have already tried
shrinking the database but could not succeded. The SQL command which I have
used is:
> DBCC shrinkdatabse (SMS_DB, Truncateonly)
> Could anybody suggest how to get space released to the operating system
from the databse which is unused.
> Thanks in advance
> Sheetu
>
|||Sheetu,
You can use DBCC SHRINKDATABASE or SHRINKFILE to shrink the physical files.
To actually get the space back you need to avoid the TruncateOnly which can
only truncate back to the last extent in use in the file. So, 1 extent in
use down at the 26.95 GB mark will limit the shrinkage.
Shrinking is a heavy process, since pages and extents must be moved to free
up the end of the files so that they can be returned to the Operating
System. This should be done rarely and only if the space is truly unneeded
in the database. (Otherwise it will all get reexpaned into with the
overhead involved in that.)
Russell Fields
"Sheetu" <Sheetu@.discussions.microsoft.com> wrote in message
news:892F1961-304B-4574-B87D-97FB2414DF54@.microsoft.com...
> Hi All,
> I have got Central site SMS database whose size is 27 GB. But when I
browse it through SQl Enterprise manager, I could see there that only 12GB
space is used and rest of 15 GB space is free. I have already tried
shrinking the database but could not succeded. The SQL command which I have
used is:
> DBCC shrinkdatabse (SMS_DB, Truncateonly)
> Could anybody suggest how to get space released to the operating system
from the databse which is unused.
> Thanks in advance
> Sheetu
>
|||> sp_msforeachtable 'DBCC INDEXDEFRAG(0, ''?'', 1)' --This will defrag all
of
> the clustered indexes only
Ah, you must have indexes on all of your tables. ;-)
I maintain systems where this is not true (and I do not have the power to
change it), so I build this based on a query for tables that have indexes.
Then I don't get a bunch of Msg 7999 errors polluting my output. ;-)
A
|||Adam,
Thanks for your remedy.
I am afraid to go ahead as it will rebuild the indexes as well as defragging all the clusttered. Is there any risk to do so or any other way to do.
Sheetu
"Adam Machanic" wrote:

> First, run sp_helpfile to get the file sizes.
> If your data file is what's taking up the space, I usually like to do the
> following things...
> USE SMS_DB
> GO
> sp_msforeachtable 'DBCC DBREINDEX(''?'', 100)' --if you don't mind the
> tables being locked... otherwise:
> sp_msforeachtable 'DBCC INDEXDEFRAG(0, ''?'', 1)' --This will defrag all of
> the clustered indexes only
> --Then, shrink the file(s):
> DBCC SHRINKFILE(1, 1, truncateonly)
>
> ...
> If it's your log file(s)...
> You can either backup the log, or if you don't mind losing it, run:
> BACKUP LOG SMS_DB WITH TRUNCATE_ONLY
> Then, you can run:
> USE SMS_DB
> GO
> DBCC SHRINKFILE(2, emptyfile) --assuming the log is fileid 2
>
> "Sheetu" <Sheetu@.discussions.microsoft.com> wrote in message
> news:892F1961-304B-4574-B87D-97FB2414DF54@.microsoft.com...
> browse it through SQl Enterprise manager, I could see there that only 12GB
> space is used and rest of 15 GB space is free. I have already tried
> shrinking the database but could not succeded. The SQL command which I have
> used is:
> from the databse which is unused.
>
>
|||"Sheetu" <Sheetu@.discussions.microsoft.com> wrote in message
news:C4C33EA0-863A-4D88-B9FB-A777890648FE@.microsoft.com...
> Adam,
> Thanks for your remedy.
> I am afraid to go ahead as it will rebuild the indexes as well as
defragging all the clusttered. Is there any risk to do so or any other way
to do.
>
First of all, you shouldn't do both. Rebuilding the indexes will defrag
them. Second, you need to know that the rebuild will lock the tables,
whereas the defrag will allow queries to keep happening. So if you're going
to be doing this during business hours while users are actively querying (or
if you have a database with 24/7 uptime), you need to use defrag only.
That's the only risk I can think of. I have never seen data corruption
or anything of that nature as a result of index upkeep.
|||"Aaron [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:u20kiKEYEHA.1048@.tk2msftngp13.phx.gbl...[vbcol=seagreen]
all
> of
> Ah, you must have indexes on all of your tables. ;-)
I'm working on it, but unfortunately the previous person working on the
databases apparently didn't like indexes (or those pesky constraint things!)

> I maintain systems where this is not true (and I do not have the power to
> change it), so I build this based on a query for tables that have indexes.
> Then I don't get a bunch of Msg 7999 errors polluting my output. ;-)
I've been meaning to write a query like that but for the moment I just
live with the error messages... I figured the OP could live with them too ;)
Do you mind posting your query?
... By the way, this is the second time I've had to post this message; I
seem to be dropping a lot of posts in these newsgroups recently. Any ideas
on how to diagnose and/or fix the problem?

How to reduce database size/ getting back unused space to OS

Hi All,
I have got Central site SMS database whose size is 27 GB. But when I browse
it through SQl Enterprise manager, I could see there that only 12GB space is
used and rest of 15 GB space is free. I have already tried shrinking the da
tabase but could not succed
ed. The SQL command which I have used is:
DBCC shrinkdatabse (SMS_DB, Truncateonly)
Could anybody suggest how to get space released to the operating system from
the databse which is unused.
Thanks in advance
SheetuFirst, run sp_helpfile to get the file sizes.
If your data file is what's taking up the space, I usually like to do the
following things...
USE SMS_DB
GO
sp_msforeachtable 'DBCC DBREINDEX(''?'', 100)' --if you don't mind the
tables being locked... otherwise:
sp_msforeachtable 'DBCC INDEXDEFRAG(0, ''?'', 1)' --This will defrag all of
the clustered indexes only
--Then, shrink the file(s):
DBCC SHRINKFILE(1, 1, truncateonly)
...
If it's your log file(s)...
You can either backup the log, or if you don't mind losing it, run:
BACKUP LOG SMS_DB WITH TRUNCATE_ONLY
Then, you can run:
USE SMS_DB
GO
DBCC SHRINKFILE(2, emptyfile) --assuming the log is fileid 2
"Sheetu" <Sheetu@.discussions.microsoft.com> wrote in message
news:892F1961-304B-4574-B87D-97FB2414DF54@.microsoft.com...
> Hi All,
> I have got Central site SMS database whose size is 27 GB. But when I
browse it through SQl Enterprise manager, I could see there that only 12GB
space is used and rest of 15 GB space is free. I have already tried
shrinking the database but could not succeded. The SQL command which I have
used is:
> DBCC shrinkdatabse (SMS_DB, Truncateonly)
> Could anybody suggest how to get space released to the operating system
from the databse which is unused.
> Thanks in advance
> Sheetu
>|||Sheetu,
You can use DBCC SHRINKDATABASE or SHRINKFILE to shrink the physical files.
To actually get the space back you need to avoid the TruncateOnly which can
only truncate back to the last extent in use in the file. So, 1 extent in
use down at the 26.95 GB mark will limit the shrinkage.
Shrinking is a heavy process, since pages and extents must be moved to free
up the end of the files so that they can be returned to the Operating
System. This should be done rarely and only if the space is truly unneeded
in the database. (Otherwise it will all get reexpaned into with the
overhead involved in that.)
Russell Fields
"Sheetu" <Sheetu@.discussions.microsoft.com> wrote in message
news:892F1961-304B-4574-B87D-97FB2414DF54@.microsoft.com...
> Hi All,
> I have got Central site SMS database whose size is 27 GB. But when I
browse it through SQl Enterprise manager, I could see there that only 12GB
space is used and rest of 15 GB space is free. I have already tried
shrinking the database but could not succeded. The SQL command which I have
used is:
> DBCC shrinkdatabse (SMS_DB, Truncateonly)
> Could anybody suggest how to get space released to the operating system
from the databse which is unused.
> Thanks in advance
> Sheetu
>|||> sp_msforeachtable 'DBCC INDEXDEFRAG(0, ''?'', 1)' --This will defrag all
of
> the clustered indexes only
Ah, you must have indexes on all of your tables. ;-)
I maintain systems where this is not true (and I do not have the power to
change it), so I build this based on a query for tables that have indexes.
Then I don't get a bunch of Msg 7999 errors polluting my output. ;-)
A|||Adam,
Thanks for your remedy.
I am afraid to go ahead as it will rebuild the indexes as well as defragging
all the clusttered. Is there any risk to do so or any other way to do.
Sheetu
"Adam Machanic" wrote:

> First, run sp_helpfile to get the file sizes.
> If your data file is what's taking up the space, I usually like to do the
> following things...
> USE SMS_DB
> GO
> sp_msforeachtable 'DBCC DBREINDEX(''?'', 100)' --if you don't mind the
> tables being locked... otherwise:
> sp_msforeachtable 'DBCC INDEXDEFRAG(0, ''?'', 1)' --This will defrag all
of
> the clustered indexes only
> --Then, shrink the file(s):
> DBCC SHRINKFILE(1, 1, truncateonly)
>
> ...
> If it's your log file(s)...
> You can either backup the log, or if you don't mind losing it, run:
> BACKUP LOG SMS_DB WITH TRUNCATE_ONLY
> Then, you can run:
> USE SMS_DB
> GO
> DBCC SHRINKFILE(2, emptyfile) --assuming the log is fileid 2
>
> "Sheetu" <Sheetu@.discussions.microsoft.com> wrote in message
> news:892F1961-304B-4574-B87D-97FB2414DF54@.microsoft.com...
> browse it through SQl Enterprise manager, I could see there that only 12GB
> space is used and rest of 15 GB space is free. I have already tried
> shrinking the database but could not succeded. The SQL command which I hav
e
> used is:
> from the databse which is unused.
>
>|||"Sheetu" <Sheetu@.discussions.microsoft.com> wrote in message
news:C4C33EA0-863A-4D88-B9FB-A777890648FE@.microsoft.com...
> Adam,
> Thanks for your remedy.
> I am afraid to go ahead as it will rebuild the indexes as well as
defragging all the clusttered. Is there any risk to do so or any other way
to do.
>
First of all, you shouldn't do both. Rebuilding the indexes will defrag
them. Second, you need to know that the rebuild will lock the tables,
whereas the defrag will allow queries to keep happening. So if you're going
to be doing this during business hours while users are actively querying (or
if you have a database with 24/7 uptime), you need to use defrag only.
That's the only risk I can think of. I have never seen data corruption
or anything of that nature as a result of index upkeep.|||"Aaron [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:u20kiKEYEHA.1048@.tk2msftngp13.phx.gbl...
all[vbcol=seagreen]
> of
> Ah, you must have indexes on all of your tables. ;-)
I'm working on it, but unfortunately the previous person working on the
databases apparently didn't like indexes (or those pesky constraint things!)

> I maintain systems where this is not true (and I do not have the power to
> change it), so I build this based on a query for tables that have indexes.
> Then I don't get a bunch of Msg 7999 errors polluting my output. ;-)
I've been meaning to write a query like that but for the moment I just
live with the error messages... I figured the OP could live with them too ;)
Do you mind posting your query?
... By the way, this is the second time I've had to post this message; I
seem to be dropping a lot of posts in these newsgroups recently. Any ideas
on how to diagnose and/or fix the problem?

How to reduce database size/ getting back unused space to OS

Hi All,
I have got Central site SMS database whose size is 27 GB. But when I browse it through SQl Enterprise manager, I could see there that only 12GB space is used and rest of 15 GB space is free. I have already tried shrinking the database but could not succeded. The SQL command which I have used is:
DBCC shrinkdatabse (SMS_DB, Truncateonly)
Could anybody suggest how to get space released to the operating system from the databse which is unused.
Thanks in advance
:) SheetuFirst, run sp_helpfile to get the file sizes.
If your data file is what's taking up the space, I usually like to do the
following things...
USE SMS_DB
GO
sp_msforeachtable 'DBCC DBREINDEX(''?'', 100)' --if you don't mind the
tables being locked... otherwise:
sp_msforeachtable 'DBCC INDEXDEFRAG(0, ''?'', 1)' --This will defrag all of
the clustered indexes only
--Then, shrink the file(s):
DBCC SHRINKFILE(1, 1, truncateonly)
...
If it's your log file(s)...
You can either backup the log, or if you don't mind losing it, run:
BACKUP LOG SMS_DB WITH TRUNCATE_ONLY
Then, you can run:
USE SMS_DB
GO
DBCC SHRINKFILE(2, emptyfile) --assuming the log is fileid 2
"Sheetu" <Sheetu@.discussions.microsoft.com> wrote in message
news:892F1961-304B-4574-B87D-97FB2414DF54@.microsoft.com...
> Hi All,
> I have got Central site SMS database whose size is 27 GB. But when I
browse it through SQl Enterprise manager, I could see there that only 12GB
space is used and rest of 15 GB space is free. I have already tried
shrinking the database but could not succeded. The SQL command which I have
used is:
> DBCC shrinkdatabse (SMS_DB, Truncateonly)
> Could anybody suggest how to get space released to the operating system
from the databse which is unused.
> Thanks in advance
> :) Sheetu
>|||Sheetu,
You can use DBCC SHRINKDATABASE or SHRINKFILE to shrink the physical files.
To actually get the space back you need to avoid the TruncateOnly which can
only truncate back to the last extent in use in the file. So, 1 extent in
use down at the 26.95 GB mark will limit the shrinkage.
Shrinking is a heavy process, since pages and extents must be moved to free
up the end of the files so that they can be returned to the Operating
System. This should be done rarely and only if the space is truly unneeded
in the database. (Otherwise it will all get reexpaned into with the
overhead involved in that.)
Russell Fields
"Sheetu" <Sheetu@.discussions.microsoft.com> wrote in message
news:892F1961-304B-4574-B87D-97FB2414DF54@.microsoft.com...
> Hi All,
> I have got Central site SMS database whose size is 27 GB. But when I
browse it through SQl Enterprise manager, I could see there that only 12GB
space is used and rest of 15 GB space is free. I have already tried
shrinking the database but could not succeded. The SQL command which I have
used is:
> DBCC shrinkdatabse (SMS_DB, Truncateonly)
> Could anybody suggest how to get space released to the operating system
from the databse which is unused.
> Thanks in advance
> :) Sheetu
>|||> sp_msforeachtable 'DBCC INDEXDEFRAG(0, ''?'', 1)' --This will defrag all
of
> the clustered indexes only
Ah, you must have indexes on all of your tables. ;-)
I maintain systems where this is not true (and I do not have the power to
change it), so I build this based on a query for tables that have indexes.
Then I don't get a bunch of Msg 7999 errors polluting my output. ;-)
A|||Adam,
Thanks for your remedy.
I am afraid to go ahead as it will rebuild the indexes as well as defragging all the clusttered. Is there any risk to do so or any other way to do.
:) Sheetu
"Adam Machanic" wrote:
> First, run sp_helpfile to get the file sizes.
> If your data file is what's taking up the space, I usually like to do the
> following things...
> USE SMS_DB
> GO
> sp_msforeachtable 'DBCC DBREINDEX(''?'', 100)' --if you don't mind the
> tables being locked... otherwise:
> sp_msforeachtable 'DBCC INDEXDEFRAG(0, ''?'', 1)' --This will defrag all of
> the clustered indexes only
> --Then, shrink the file(s):
> DBCC SHRINKFILE(1, 1, truncateonly)
>
> ...
> If it's your log file(s)...
> You can either backup the log, or if you don't mind losing it, run:
> BACKUP LOG SMS_DB WITH TRUNCATE_ONLY
> Then, you can run:
> USE SMS_DB
> GO
> DBCC SHRINKFILE(2, emptyfile) --assuming the log is fileid 2
>
> "Sheetu" <Sheetu@.discussions.microsoft.com> wrote in message
> news:892F1961-304B-4574-B87D-97FB2414DF54@.microsoft.com...
> > Hi All,
> > I have got Central site SMS database whose size is 27 GB. But when I
> browse it through SQl Enterprise manager, I could see there that only 12GB
> space is used and rest of 15 GB space is free. I have already tried
> shrinking the database but could not succeded. The SQL command which I have
> used is:
> > DBCC shrinkdatabse (SMS_DB, Truncateonly)
> >
> > Could anybody suggest how to get space released to the operating system
> from the databse which is unused.
> >
> > Thanks in advance
> >
> > :) Sheetu
> >
>
>|||"Sheetu" <Sheetu@.discussions.microsoft.com> wrote in message
news:C4C33EA0-863A-4D88-B9FB-A777890648FE@.microsoft.com...
> Adam,
> Thanks for your remedy.
> I am afraid to go ahead as it will rebuild the indexes as well as
defragging all the clusttered. Is there any risk to do so or any other way
to do.
>
First of all, you shouldn't do both. Rebuilding the indexes will defrag
them. Second, you need to know that the rebuild will lock the tables,
whereas the defrag will allow queries to keep happening. So if you're going
to be doing this during business hours while users are actively querying (or
if you have a database with 24/7 uptime), you need to use defrag only.
That's the only risk I can think of. I have never seen data corruption
or anything of that nature as a result of index upkeep.|||"Aaron [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:u20kiKEYEHA.1048@.tk2msftngp13.phx.gbl...
> > sp_msforeachtable 'DBCC INDEXDEFRAG(0, ''?'', 1)' --This will defrag
all
> of
> > the clustered indexes only
> Ah, you must have indexes on all of your tables. ;-)
I'm working on it, but unfortunately the previous person working on the
databases apparently didn't like indexes (or those pesky constraint things!)
> I maintain systems where this is not true (and I do not have the power to
> change it), so I build this based on a query for tables that have indexes.
> Then I don't get a bunch of Msg 7999 errors polluting my output. ;-)
I've been meaning to write a query like that but for the moment I just
live with the error messages... I figured the OP could live with them too ;)
Do you mind posting your query?
... By the way, this is the second time I've had to post this message; I
seem to be dropping a lot of posts in these newsgroups recently. Any ideas
on how to diagnose and/or fix the problem?

How to reclaim space in columns changed from nvarchar to varchar

Hi,

This is probably an easy question for someone so any help would be
appreciated.

I have changed the columns in a table that where nvarchar to the same
size of type varchar so halve the space needed for them.

I have done this a) becuase this is never going to be an international
application, b) we are running out of space and c) there are 100
million rows.

I have done this with the alter table statement which seems to work but
the space used in the database hasn't altered.

I'm presuming that the way the records are structured within the table
there is just now more space free inbetween each page?

Is there a way or re-shrinking just an individual table and free up
some of the space in there or am i missing the point somewhere?

Thanks in advance,

IanYou can reorg the table by rebuilding the table's clustered index.

--
Hope this helps.

Dan Guzman
SQL Server MVP

"wriggs" <ian.w@.btinternet.com> wrote in message
news:1116844882.254733.254670@.g47g2000cwa.googlegr oups.com...
> Hi,
> This is probably an easy question for someone so any help would be
> appreciated.
> I have changed the columns in a table that where nvarchar to the same
> size of type varchar so halve the space needed for them.
> I have done this a) becuase this is never going to be an international
> application, b) we are running out of space and c) there are 100
> million rows.
> I have done this with the alter table statement which seems to work but
> the space used in the database hasn't altered.
> I'm presuming that the way the records are structured within the table
> there is just now more space free inbetween each page?
> Is there a way or re-shrinking just an individual table and free up
> some of the space in there or am i missing the point somewhere?
> Thanks in advance,
> Ian|||The table doesn't have any clustered index. Any other ideas?

Dan Guzman wrote:
> You can reorg the table by rebuilding the table's clustered index.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "wriggs" <ian.w@.btinternet.com> wrote in message
> news:1116844882.254733.254670@.g47g2000cwa.googlegr oups.com...
> > Hi,
> > This is probably an easy question for someone so any help would be
> > appreciated.
> > I have changed the columns in a table that where nvarchar to the
same
> > size of type varchar so halve the space needed for them.
> > I have done this a) becuase this is never going to be an
international
> > application, b) we are running out of space and c) there are 100
> > million rows.
> > I have done this with the alter table statement which seems to work
but
> > the space used in the database hasn't altered.
> > I'm presuming that the way the records are structured within the
table
> > there is just now more space free inbetween each page?
> > Is there a way or re-shrinking just an individual table and free up
> > some of the space in there or am i missing the point somewhere?
> > Thanks in advance,
> > Ian|||wriggs (ian.w@.btinternet.com) writes:
> The table doesn't have any clustered index. Any other ideas?

Is that a conscious decision?

Having a clustered index on a table is usually good practice. Not the
least, because you then can run DBCC DBREINDEX to handle fragmentation.

If nothing else, you could create a clustered index on the table, and
then drop it.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Thanks for the advice Eland,

Not my decision, I've only just taken over the day to day running of
the box on a contract basis.

I agree totally with what you said, so I may have to try and at least
add a clustered index to the table and try what you said.

Ian,

How to reclaim DB space?

I have a sql2000 database at almost 25gigs. When i checked the
properties, it says 87% is unused space. When i went into the shrink
menu, database file looks reasonable(around 2gigs), but log file is
17gigs with 2gigs used. So, almost 15gigs free space. How do i reclaim
that space and return it to the NT file system? I also did a backup of
full database and also re-backup the log files. Also tried all kinds
of shrinking switches like truncating, rearranging etc...shrink to
3gigs...it comes back with successful but size remains around 17gigs.
what gives?
thank you.If you don't do regular log backups, set the database to simple recovery mod
e. As for shrinking of
the physical files, see the links at about the middle of this article:
http://www.karaszi.com/SQLServer/info_dont_shrink.asp
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"slycat" <slycat@.consultant.com> wrote in message
news:dec83278.0410281333.57bca24c@.posting.google.com...
>I have a sql2000 database at almost 25gigs. When i checked the
> properties, it says 87% is unused space. When i went into the shrink
> menu, database file looks reasonable(around 2gigs), but log file is
> 17gigs with 2gigs used. So, almost 15gigs free space. How do i reclaim
> that space and return it to the NT file system? I also did a backup of
> full database and also re-backup the log files. Also tried all kinds
> of shrinking switches like truncating, rearranging etc...shrink to
> 3gigs...it comes back with successful but size remains around 17gigs.
> what gives?
> thank you.|||Slycat,
Have you tried DBCC SHRINKFILE on the log file?
Ron
--
Ron Talmage
SQL Server MVP
"slycat" <slycat@.consultant.com> wrote in message
news:dec83278.0410281333.57bca24c@.posting.google.com...
> I have a sql2000 database at almost 25gigs. When i checked the
> properties, it says 87% is unused space. When i went into the shrink
> menu, database file looks reasonable(around 2gigs), but log file is
> 17gigs with 2gigs used. So, almost 15gigs free space. How do i reclaim
> that space and return it to the NT file system? I also did a backup of
> full database and also re-backup the log files. Also tried all kinds
> of shrinking switches like truncating, rearranging etc...shrink to
> 3gigs...it comes back with successful but size remains around 17gigs.
> what gives?
> thank you.|||please issue a checkpoint command before trying to shrinkfile
"Ron Talmage" wrote:

> Slycat,
> Have you tried DBCC SHRINKFILE on the log file?
> Ron
> --
> Ron Talmage
> SQL Server MVP
> "slycat" <slycat@.consultant.com> wrote in message
> news:dec83278.0410281333.57bca24c@.posting.google.com...
>
>|||Sorry for being a noob but what does that do and how do i do it?
newbie <newbie@.discussions.microsoft.com> wrote in message news:<7D798618-FA75-4245-8D44-FC7
FD79C9AC6@.microsoft.com>...
> please issue a checkpoint command before trying to shrinkfile
>

How to reclaim DB space?

Hi
Change the recovery model to simple (found by right
clicking on the database, then going to the options tab).
Then run the following:
backup log <database_name> with truncate_only
go
dbcc shrinkfile (<logical_log_file>, 100)
Note: to get the <ligical_log_file>, run sp_helpfile.
important: This will not cause you to lose any data, BUT
you will not be able to restore using your tx-log backups
anymore (because you just truncated it!). So, make sure
you do a full SQL backup on this database before giving my
script a go.
You can then change the recovery model back to bulk-logged
or Full if you so require.
Enjoy
JonoI tried this and it works fine. Thanks a lot!
"Jono" <anonymous@.discussions.microsoft.com> wrote in message news:<1d8701c4bd4d$01954df0$a6
01280a@.phx.gbl>...
> Hi
> Change the recovery model to simple (found by right
> clicking on the database, then going to the options tab).
> Then run the following:
> backup log <database_name> with truncate_only
> go
> dbcc shrinkfile (<logical_log_file>, 100)
> Note: to get the <ligical_log_file>, run sp_helpfile.
> important: This will not cause you to lose any data, BUT
> you will not be able to restore using your tx-log backups
> anymore (because you just truncated it!). So, make sure
> you do a full SQL backup on this database before giving my
> script a go.
> You can then change the recovery model back to bulk-logged
> or Full if you so require.
> Enjoy
> Jono

How to reclaim DB space?

Hi
Change the recovery model to simple (found by right
clicking on the database, then going to the options tab).
Then run the following:
backup log <database_name> with truncate_only
go
dbcc shrinkfile (<logical_log_file>, 100)
Note: to get the <ligical_log_file>, run sp_helpfile.
important: This will not cause you to lose any data, BUT
you will not be able to restore using your tx-log backups
anymore (because you just truncated it!). So, make sure
you do a full SQL backup on this database before giving my
script a go.
You can then change the recovery model back to bulk-logged
or Full if you so require.
Enjoy
Jono
I tried this and it works fine. Thanks a lot!
"Jono" <anonymous@.discussions.microsoft.com> wrote in message news:<1d8701c4bd4d$01954df0$a601280a@.phx.gbl>...
> Hi
> Change the recovery model to simple (found by right
> clicking on the database, then going to the options tab).
> Then run the following:
> backup log <database_name> with truncate_only
> go
> dbcc shrinkfile (<logical_log_file>, 100)
> Note: to get the <ligical_log_file>, run sp_helpfile.
> important: This will not cause you to lose any data, BUT
> you will not be able to restore using your tx-log backups
> anymore (because you just truncated it!). So, make sure
> you do a full SQL backup on this database before giving my
> script a go.
> You can then change the recovery model back to bulk-logged
> or Full if you so require.
> Enjoy
> Jono

How to reclaim DB space?

I have a sql2000 database at almost 25gigs. When i checked the
properties, it says 87% is unused space. When i went into the shrink
menu, database file looks reasonable(around 2gigs), but log file is
17gigs with 2gigs used. So, almost 15gigs free space. How do i reclaim
that space and return it to the NT file system? I also did a backup of
full database and also re-backup the log files. Also tried all kinds
of shrinking switches like truncating, rearranging etc...shrink to
3gigs...it comes back with successful but size remains around 17gigs.
what gives?
thank you.
If you don't do regular log backups, set the database to simple recovery mode. As for shrinking of
the physical files, see the links at about the middle of this article:
http://www.karaszi.com/SQLServer/info_dont_shrink.asp
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"slycat" <slycat@.consultant.com> wrote in message
news:dec83278.0410281333.57bca24c@.posting.google.c om...
>I have a sql2000 database at almost 25gigs. When i checked the
> properties, it says 87% is unused space. When i went into the shrink
> menu, database file looks reasonable(around 2gigs), but log file is
> 17gigs with 2gigs used. So, almost 15gigs free space. How do i reclaim
> that space and return it to the NT file system? I also did a backup of
> full database and also re-backup the log files. Also tried all kinds
> of shrinking switches like truncating, rearranging etc...shrink to
> 3gigs...it comes back with successful but size remains around 17gigs.
> what gives?
> thank you.
|||Slycat,
Have you tried DBCC SHRINKFILE on the log file?
Ron
Ron Talmage
SQL Server MVP
"slycat" <slycat@.consultant.com> wrote in message
news:dec83278.0410281333.57bca24c@.posting.google.c om...
> I have a sql2000 database at almost 25gigs. When i checked the
> properties, it says 87% is unused space. When i went into the shrink
> menu, database file looks reasonable(around 2gigs), but log file is
> 17gigs with 2gigs used. So, almost 15gigs free space. How do i reclaim
> that space and return it to the NT file system? I also did a backup of
> full database and also re-backup the log files. Also tried all kinds
> of shrinking switches like truncating, rearranging etc...shrink to
> 3gigs...it comes back with successful but size remains around 17gigs.
> what gives?
> thank you.
|||please issue a checkpoint command before trying to shrinkfile
"Ron Talmage" wrote:

> Slycat,
> Have you tried DBCC SHRINKFILE on the log file?
> Ron
> --
> Ron Talmage
> SQL Server MVP
> "slycat" <slycat@.consultant.com> wrote in message
> news:dec83278.0410281333.57bca24c@.posting.google.c om...
>
>
|||Sorry for being a noob but what does that do and how do i do it?
newbie <newbie@.discussions.microsoft.com> wrote in message news:<7D798618-FA75-4245-8D44-FC7FD79C9AC6@.microsoft.com>...
> please issue a checkpoint command before trying to shrinkfile
>

How to reclaim DB space?

I have a sql2000 database at almost 25gigs. When i checked the
properties, it says 87% is unused space. When i went into the shrink
menu, database file looks reasonable(around 2gigs), but log file is
17gigs with 2gigs used. So, almost 15gigs free space. How do i reclaim
that space and return it to the NT file system? I also did a backup of
full database and also re-backup the log files. Also tried all kinds
of shrinking switches like truncating, rearranging etc...shrink to
3gigs...it comes back with successful but size remains around 17gigs.
what gives?
thank you.If you don't do regular log backups, set the database to simple recovery mode. As for shrinking of
the physical files, see the links at about the middle of this article:
http://www.karaszi.com/SQLServer/info_dont_shrink.asp
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"slycat" <slycat@.consultant.com> wrote in message
news:dec83278.0410281333.57bca24c@.posting.google.com...
>I have a sql2000 database at almost 25gigs. When i checked the
> properties, it says 87% is unused space. When i went into the shrink
> menu, database file looks reasonable(around 2gigs), but log file is
> 17gigs with 2gigs used. So, almost 15gigs free space. How do i reclaim
> that space and return it to the NT file system? I also did a backup of
> full database and also re-backup the log files. Also tried all kinds
> of shrinking switches like truncating, rearranging etc...shrink to
> 3gigs...it comes back with successful but size remains around 17gigs.
> what gives?
> thank you.|||Slycat,
Have you tried DBCC SHRINKFILE on the log file?
Ron
--
Ron Talmage
SQL Server MVP
"slycat" <slycat@.consultant.com> wrote in message
news:dec83278.0410281333.57bca24c@.posting.google.com...
> I have a sql2000 database at almost 25gigs. When i checked the
> properties, it says 87% is unused space. When i went into the shrink
> menu, database file looks reasonable(around 2gigs), but log file is
> 17gigs with 2gigs used. So, almost 15gigs free space. How do i reclaim
> that space and return it to the NT file system? I also did a backup of
> full database and also re-backup the log files. Also tried all kinds
> of shrinking switches like truncating, rearranging etc...shrink to
> 3gigs...it comes back with successful but size remains around 17gigs.
> what gives?
> thank you.|||please issue a checkpoint command before trying to shrinkfile
"Ron Talmage" wrote:
> Slycat,
> Have you tried DBCC SHRINKFILE on the log file?
> Ron
> --
> Ron Talmage
> SQL Server MVP
> "slycat" <slycat@.consultant.com> wrote in message
> news:dec83278.0410281333.57bca24c@.posting.google.com...
> > I have a sql2000 database at almost 25gigs. When i checked the
> > properties, it says 87% is unused space. When i went into the shrink
> > menu, database file looks reasonable(around 2gigs), but log file is
> > 17gigs with 2gigs used. So, almost 15gigs free space. How do i reclaim
> > that space and return it to the NT file system? I also did a backup of
> > full database and also re-backup the log files. Also tried all kinds
> > of shrinking switches like truncating, rearranging etc...shrink to
> > 3gigs...it comes back with successful but size remains around 17gigs.
> > what gives?
> >
> > thank you.
>
>|||Hi
Change the recovery model to simple (found by right
clicking on the database, then going to the options tab).
Then run the following:
backup log <database_name> with truncate_only
go
dbcc shrinkfile (<logical_log_file>, 100)
Note: to get the <ligical_log_file>, run sp_helpfile.
important: This will not cause you to lose any data, BUT
you will not be able to restore using your tx-log backups
anymore (because you just truncated it!). So, make sure
you do a full SQL backup on this database before giving my
script a go.
You can then change the recovery model back to bulk-logged
or Full if you so require.
Enjoy
Jono|||Sorry for being a noob but what does that do and how do i do it?
newbie <newbie@.discussions.microsoft.com> wrote in message news:<7D798618-FA75-4245-8D44-FC7FD79C9AC6@.microsoft.com>...
> please issue a checkpoint command before trying to shrinkfile
>|||I tried this and it works fine. Thanks a lot!
"Jono" <anonymous@.discussions.microsoft.com> wrote in message news:<1d8701c4bd4d$01954df0$a601280a@.phx.gbl>...
> Hi
> Change the recovery model to simple (found by right
> clicking on the database, then going to the options tab).
> Then run the following:
> backup log <database_name> with truncate_only
> go
> dbcc shrinkfile (<logical_log_file>, 100)
> Note: to get the <ligical_log_file>, run sp_helpfile.
> important: This will not cause you to lose any data, BUT
> you will not be able to restore using your tx-log backups
> anymore (because you just truncated it!). So, make sure
> you do a full SQL backup on this database before giving my
> script a go.
> You can then change the recovery model back to bulk-logged
> or Full if you so require.
> Enjoy
> Jono