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