How to remove unused columns and eliminate warnings

I have hundreds of these I think mainly due to the way the union all task works. Any ideas on ways to eliminate these?Select the connections in the editor and hit the delete button.
K|||

I guess I wasn't clear. Those columns are used earlier on in the data flow so I can't delete them from the connection. However I get incessant warnings about them hanging around later in the data flow.

|||Chris,

could you give us more details? How did those columns get generated?

That might give us a better idea if you can get rid of them.

Thanks,|||Just an idea... As far as I understand SSIS columns are "recreated" behind an asynchronous transform...

I.e. when you have a source with 2 fields (A and B), a derieved column task which calulates C by A+B, a sort by C passing A and B through it and a destination where you write A and C in a file you should get a warning that B is not used and can be removed... That's because B is passed through an asynchronous transform so it's a new column. And in fact, it's not used behind the sort...

At least that's how I understand SSIS...sql

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 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 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?

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