Can't shrink a database

Hi there,
I am running SQL2005 SP1 Standard.
I have a database that was originally a SQL2K db that was rolled up to 2K5.
Everything was fine with it until I truncated a few tables and dropped a
large column & index on a few tables. Since then the following symptoms are
present:
1) The size of the datafile and log file are about 2.5x the size of the
actual data
2) The DB properties ALWAYS shows "Space Available" as "0.00 MB"
3) The shrink dialog with the file type set to "Log" shows avaialble free
space to be 98% but shrinking it does not release any of the free space.
I have tried backing up the database, deleting it and restoring it but the
inflated log file still exists. I've also tried creating a new blank DB
(versus letting the restore create it) and restoring into that but the
problem persists.
Does anyone have any ideas on how to shrink this database down?
Thanks,
--
-Steven1) Is it the data or the log file which is "too big"?
2) Read about DBCC UPDATEUSAGE
3) See 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/
"Steven Berkovitz" <mbcdev@.community.nospam> wrote in message
news:F4B6BA40-9143-4D52-BC8F-3FB9CFBEB133@.microsoft.com...
> Hi there,
> I am running SQL2005 SP1 Standard.
> I have a database that was originally a SQL2K db that was rolled up to 2K5.
> Everything was fine with it until I truncated a few tables and dropped a
> large column & index on a few tables. Since then the following symptoms are
> present:
> 1) The size of the datafile and log file are about 2.5x the size of the
> actual data
> 2) The DB properties ALWAYS shows "Space Available" as "0.00 MB"
> 3) The shrink dialog with the file type set to "Log" shows avaialble free
> space to be 98% but shrinking it does not release any of the free space.
> I have tried backing up the database, deleting it and restoring it but the
> inflated log file still exists. I've also tried creating a new blank DB
> (versus letting the restore create it) and restoring into that but the
> problem persists.
> Does anyone have any ideas on how to shrink this database down?
> Thanks,
> --
> -Steven|||Hi Steven,
I would like to know the issue status and whether or not you need further
research.
You can try setting the recovery mode to simple, backing up your database
completely, truncating the log with BACKUP LOG ... WITH TRUNCATE ONLY, and
then use DBCC SHRINKFILE to shrink the log file.
Have a great day!
Charles Wang
Microsoft Online Community Support

Can't shrink a database

Hi there,
I am running SQL2005 SP1 Standard.
I have a database that was originally a SQL2K db that was rolled up to 2K5.
Everything was fine with it until I truncated a few tables and dropped a
large column & index on a few tables. Since then the following symptoms are
present:
1) The size of the datafile and log file are about 2.5x the size of the
actual data
2) The DB properties ALWAYS shows "Space Available" as "0.00 MB"
3) The shrink dialog with the file type set to "Log" shows avaialble free
space to be 98% but shrinking it does not release any of the free space.
I have tried backing up the database, deleting it and restoring it but the
inflated log file still exists. I've also tried creating a new blank DB
(versus letting the restore create it) and restoring into that but the
problem persists.
Does anyone have any ideas on how to shrink this database down?
Thanks,
--
-Steven1) Is it the data or the log file which is "too big"?
2) Read about DBCC UPDATEUSAGE
3) See 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/
"Steven Berkovitz" <mbcdev@.community.nospam> wrote in message
news:F4B6BA40-9143-4D52-BC8F-3FB9CFBEB133@.microsoft.com...
> Hi there,
> I am running SQL2005 SP1 Standard.
> I have a database that was originally a SQL2K db that was rolled up to 2K5
.
> Everything was fine with it until I truncated a few tables and dropped a
> large column & index on a few tables. Since then the following symptoms a
re
> present:
> 1) The size of the datafile and log file are about 2.5x the size of the
> actual data
> 2) The DB properties ALWAYS shows "Space Available" as "0.00 MB"
> 3) The shrink dialog with the file type set to "Log" shows avaialble free
> space to be 98% but shrinking it does not release any of the free space.
> I have tried backing up the database, deleting it and restoring it but the
> inflated log file still exists. I've also tried creating a new blank DB
> (versus letting the restore create it) and restoring into that but the
> problem persists.
> Does anyone have any ideas on how to shrink this database down?
> Thanks,
> --
> -Steven|||Hi Steven,
I would like to know the issue status and whether or not you need further
research.
You can try setting the recovery mode to simple, backing up your database
completely, truncating the log with BACKUP LOG ... WITH TRUNCATE ONLY, and
then use DBCC SHRINKFILE to shrink the log file.
Have a great day!
Charles Wang
Microsoft Online Community Supportsql

Can't see Mapped Drives in Database Maintenance tool!

I have SQL2000 & SQL2005 running on Windows 2003 servers.
I am using the Database Maintenance tool to backup selected databases. I
realized the only drives I can save these backups to are the local drives. We
don't have enough space on these local drives, so is there anyway to see the
network mapped drives for this procedure?
Thanks,
Ted
Don't use a mapped drive. Use the UNC pathing instead. You need to ensure
the account SQL Server is running under has permissions to read and write to
this share first.
http://support.microsoft.com/kb/555128
Andrew J. Kelly SQL MVP
"Infomax" <Infomax@.discussions.microsoft.com> wrote in message
news:EBB765BE-0A41-456C-9615-C6AB069AAADB@.microsoft.com...
>I have SQL2000 & SQL2005 running on Windows 2003 servers.
> I am using the Database Maintenance tool to backup selected databases. I
> realized the only drives I can save these backups to are the local drives.
> We
> don't have enough space on these local drives, so is there anyway to see
> the
> network mapped drives for this procedure?
> Thanks,
> Ted

cant load DB files from SQL2005 Beta2

I was using SQL 2005 Beta2 in my lab boxes, which I have now upgraded
to the RTM release of SQL Server 2005, however it wont let me load the
DB files from databases I created on the Beta version...I am in bad
shape because I no longer have any Beta2 installations or
media....how can I load the DB files into the RTM version' help!!TechMasters wrote:
> I was using SQL 2005 Beta2 in my lab boxes, which I have now upgraded
> to the RTM release of SQL Server 2005, however it wont let me load the
> DB files from databases I created on the Beta version...I am in bad
> shape because I no longer have any Beta2 installations or
> media....how can I load the DB files into the RTM version' help!!
What do you mean by "can't load"? What are you trying to do and which
error messages do you get?
Regards
Steen|||On Thu, 18 May 2006 10:19:56 +0200, "Steen Persson (DK)"
<spe@.REMOVEdatea.dk> wrote:
>TechMasters wrote:
>> I was using SQL 2005 Beta2 in my lab boxes, which I have now upgraded
>> to the RTM release of SQL Server 2005, however it wont let me load the
>> DB files from databases I created on the Beta version...I am in bad
>> shape because I no longer have any Beta2 installations or
>> media....how can I load the DB files into the RTM version' help!!
>What do you mean by "can't load"? What are you trying to do and which
>error messages do you get?
>Regards
>Steen
I meant when I go to attach the database files (mdf file) whcih where
created in the beta version I get the following
--
Database 'Report' cannot be upgraded because its non-release version
(587) is not supported by this version of SQL Server. You cannot open
a database that is incompatible with this version of sqlservr.exe. You
must re-create the database.
Could not open new database 'Report'. CREATE DATABASE is aborted.
(Microsoft SQL Server, Error: 950)|||On Thu, 18 May 2006 10:19:56 +0200, "Steen Persson (DK)"
<spe@.REMOVEdatea.dk> wrote:
>TechMasters wrote:
>> I was using SQL 2005 Beta2 in my lab boxes, which I have now upgraded
>> to the RTM release of SQL Server 2005, however it wont let me load the
>> DB files from databases I created on the Beta version...I am in bad
>> shape because I no longer have any Beta2 installations or
>> media....how can I load the DB files into the RTM version' help!!
>What do you mean by "can't load"? What are you trying to do and which
>error messages do you get?
>Regards
>Steen
What I mean is I get the error below when I try to attach the database
files (.mdf) which were created in the Beta version
--
Database 'Report' cannot be upgraded because its non-release version
(587) is not supported by this version of SQL Server. You cannot open
a database that is incompatible with this version of sqlservr.exe. You
must re-create the database.
Could not open new database 'Report'. CREATE DATABASE is aborted.
(Microsoft SQL Server, Error: 950)|||I have the same problem. Is there a way to get the beta2 mdf's loaded
into the RTM version?
Gans
TechMasters wrote:
> On Thu, 18 May 2006 10:19:56 +0200, "Steen Persson (DK)"
> <spe@.REMOVEdatea.dk> wrote:
> >TechMasters wrote:
> >> I was using SQL 2005 Beta2 in my lab boxes, which I have now upgraded
> >> to the RTM release of SQL Server 2005, however it wont let me load the
> >> DB files from databases I created on the Beta version...I am in bad
> >> shape because I no longer have any Beta2 installations or
> >> media....how can I load the DB files into the RTM version' help!!
> >
> >What do you mean by "can't load"? What are you trying to do and which
> >error messages do you get?
> >
> >Regards
> >Steen
> What I mean is I get the error below when I try to attach the database
> files (.mdf) which were created in the Beta version
> --
> Database 'Report' cannot be upgraded because its non-release version
> (587) is not supported by this version of SQL Server. You cannot open
> a database that is incompatible with this version of sqlservr.exe. You
> must re-create the database.
> Could not open new database 'Report'. CREATE DATABASE is aborted.
> (Microsoft SQL Server, Error: 950)

cant load DB files from SQL2005 Beta2

I was using SQL 2005 Beta2 in my lab boxes, which I have now upgraded
to the RTM release of SQL Server 2005, however it wont let me load the
DB files from databases I created on the Beta version...I am in bad
shape because I no longer have any Beta2 installations or
media....how can I load the DB files into the RTM version' help!!TechMasters wrote:
> I was using SQL 2005 Beta2 in my lab boxes, which I have now upgraded
> to the RTM release of SQL Server 2005, however it wont let me load the
> DB files from databases I created on the Beta version...I am in bad
> shape because I no longer have any Beta2 installations or
> media....how can I load the DB files into the RTM version' help!!
What do you mean by "can't load"? What are you trying to do and which
error messages do you get?
Regards
Steen|||On Thu, 18 May 2006 10:19:56 +0200, "Steen Persson (DK)"
<spe@.REMOVEdatea.dk> wrote:

>TechMasters wrote:
>What do you mean by "can't load"? What are you trying to do and which
>error messages do you get?
>Regards
>Steen
I meant when I go to attach the database files (mdf file) whcih where
created in the beta version I get the following
Database 'Report' cannot be upgraded because its non-release version
(587) is not supported by this version of SQL Server. You cannot open
a database that is incompatible with this version of sqlservr.exe. You
must re-create the database.
Could not open new database 'Report'. CREATE DATABASE is aborted.
(Microsoft SQL Server, Error: 950)|||On Thu, 18 May 2006 10:19:56 +0200, "Steen Persson (DK)"
<spe@.REMOVEdatea.dk> wrote:

>TechMasters wrote:
>What do you mean by "can't load"? What are you trying to do and which
>error messages do you get?
>Regards
>Steen
What I mean is I get the error below when I try to attach the database
files (.mdf) which were created in the Beta version
Database 'Report' cannot be upgraded because its non-release version
(587) is not supported by this version of SQL Server. You cannot open
a database that is incompatible with this version of sqlservr.exe. You
must re-create the database.
Could not open new database 'Report'. CREATE DATABASE is aborted.
(Microsoft SQL Server, Error: 950)|||I have the same problem. Is there a way to get the beta2 mdf's loaded
into the RTM version?
Gans
TechMasters wrote:
> On Thu, 18 May 2006 10:19:56 +0200, "Steen Persson (DK)"
> <spe@.REMOVEdatea.dk> wrote:
>
> What I mean is I get the error below when I try to attach the database
> files (.mdf) which were created in the Beta version
> --
> Database 'Report' cannot be upgraded because its non-release version
> (587) is not supported by this version of SQL Server. You cannot open
> a database that is incompatible with this version of sqlservr.exe. You
> must re-create the database.
> Could not open new database 'Report'. CREATE DATABASE is aborted.
> (Microsoft SQL Server, Error: 950)|||I have the same problem. Is there a way to get the beta2 mdf's loaded
into the RTM version?
Gans
TechMasters wrote:
> On Thu, 18 May 2006 10:19:56 +0200, "Steen Persson (DK)"
> <spe@.REMOVEdatea.dk> wrote:
>
> What I mean is I get the error below when I try to attach the database
> files (.mdf) which were created in the Beta version
> --
> Database 'Report' cannot be upgraded because its non-release version
> (587) is not supported by this version of SQL Server. You cannot open
> a database that is incompatible with this version of sqlservr.exe. You
> must re-create the database.
> Could not open new database 'Report'. CREATE DATABASE is aborted.
> (Microsoft SQL Server, Error: 950)