Error when shrinking data file in SQL 2005

I've just upgraded my production server from 2K to 2K5, and I've got one
database that is way too big (almost 5 GB with 88% free space). I have no
problems shrinking the transaction log, but when I try to shrink the data
file, I get the following message:
"A severe error occurred on the current command. The results, if any,
should be discarded. File ID 1 of database ID 8 cannot be shrunk as it is
either being shrunk by another process or is empty. (Microsoft SQL Server,
Error: 0)"
At one point, I had autoshrink turned on for the database to handle the
transaction log, but this has never been a problem for the data file. Durin
g
my upgrade process, I detatched this database and copied it over to my test
server, where I am still having no problems. Help!!Hi
I assume you are using DBCC SHRINKFILE? Have you tried doing a DBCC CHECKDB?
John
"Gary" wrote:

> I've just upgraded my production server from 2K to 2K5, and I've got one
> database that is way too big (almost 5 GB with 88% free space). I have no
> problems shrinking the transaction log, but when I try to shrink the data
> file, I get the following message:
> "A severe error occurred on the current command. The results, if any,
> should be discarded. File ID 1 of database ID 8 cannot be shrunk as it is
> either being shrunk by another process or is empty. (Microsoft SQL Server,
> Error: 0)"
> At one point, I had autoshrink turned on for the database to handle the
> transaction log, but this has never been a problem for the data file. Dur
ing
> my upgrade process, I detatched this database and copied it over to my tes
t
> server, where I am still having no problems. Help!!
>|||"John Bell" wrote:
[vbcol=seagreen]
> I assume you are using DBCC SHRINKFILE? Have you tried doing a DBCC >CHECKDB?[/vbc
ol]
John,
I ended up getting that problem solved (by detaching and reattaching the
database and then taking it offline and rebooting the server), but I've got
another somewhat related problem. In a maintenance plan I was doing a
rebuild on the indexes for the database. That maintenance plan never
completed (it hung up), and now my data file size is about 4 GB (it was abou
t
500 MB) with basically no free space. I've run DBCC CHECKDB along with abou
t
everything else I can think of, and I can't find any way to get that file
size back down.
Thanks!|||Hi Gary
Is this the data file or log file? If it is the log file you can use BACKUP
LOG ... WITH TRUNCATE_ONLY and then DBCC SHRINKFILE.
John
"Gary" wrote:

> "John Bell" wrote:
>
> John,
> I ended up getting that problem solved (by detaching and reattaching the
> database and then taking it offline and rebooting the server), but I've go
t
> another somewhat related problem. In a maintenance plan I was doing a
> rebuild on the indexes for the database. That maintenance plan never
> completed (it hung up), and now my data file size is about 4 GB (it was ab
out
> 500 MB) with basically no free space. I've run DBCC CHECKDB along with ab
out
> everything else I can think of, and I can't find any way to get that file
> size back down.
> Thanks!
>|||John,
No, it's the data file. I've tried to manually rebuild/reorganize
individual indexes, and that's not really working, either.
Gary
[vbcol=seagreen]
> Hi Gary
> Is this the data file or log file? If it is the log file you can use BACKU
P
> LOG ... WITH TRUNCATE_ONLY and then DBCC SHRINKFILE.
> John
>
> "Gary" wrote:
>|||Hi
You can use sp_spaceused to see what is being used by the file, and DBCC
SHRINKFILE will shrink it. In general it is not a good idea to contunually
expand/shrink the files.
John
"Gary" wrote:
[vbcol=seagreen]
> John,
> No, it's the data file. I've tried to manually rebuild/reorganize
> individual indexes, and that's not really working, either.
> Gary
>|||i came accross this problem too. what did was to restart the service
and executed dbcc shrinkfile again. that time it succeeded.|||Hi,
I got the same error,
When i moved all of user tables and indexes out of primary FG and to a new
FG, my Primary is left with around 17GB of which less than a 1GB is actually
used, and when I ran shrink file on the only file on that FG, it took ages.
then I stoped it (in 2000 it would have taken max 2-3 hours, our SAN is not
that bad :-) ). when I ran it again I get the error described.
when I back up the db and restore it, then I can shrink the file.
this is a db in simle mode, dev environment. worried what happens when we go
to production with this.
error:
File ID 1 of database ID X cannot be shrunk as it is either being shrunk by
another process or is empty.
Msg 0, Level 11, State 0, Line 0
A severe error occurred on the current command. The results, if any, should
be discarded.
CTS DBA
"XNMB" wrote:

> i came accross this problem too. what did was to restart the service
> and executed dbcc shrinkfile again. that time it succeeded.
>|||I tried detach and re-attach the database fie and run DBCC SHRINKFILE
But I wouldn't recommend shrinking. Here's an article by the great Tibor
Karaszi on database shrinking
http://www.karaszi.com/SQLServer/info_dont_shrink.asp
"CTS_DBA" <CTSDBA@.discussions.microsoft.com> wrote in message
news:630320F3-22A4-4D33-A245-54D14D4784FD@.microsoft.com...[vbcol=seagreen]
> Hi,
> I got the same error,
> When i moved all of user tables and indexes out of primary FG and to a new
> FG, my Primary is left with around 17GB of which less than a 1GB is
> actually
> used, and when I ran shrink file on the only file on that FG, it took
> ages.
> then I stoped it (in 2000 it would have taken max 2-3 hours, our SAN is
> not
> that bad :-) ). when I ran it again I get the error described.
> when I back up the db and restore it, then I can shrink the file.
> this is a db in simle mode, dev environment. worried what happens when we
> go
> to production with this.
> error:
> File ID 1 of database ID X cannot be shrunk as it is either being shrunk
> by
> another process or is empty.
> Msg 0, Level 11, State 0, Line 0
> A severe error occurred on the current command. The results, if any,
> should
> be discarded.
> --
> CTS DBA
>
> "XNMB" wrote:
>|||hi,
thanks for the reply.
I know what you mean, but what I don't understand is why shrinkfile wouldn't
work when there's so much free space in the file.
over 8GB
if the original database shrunk in SQL 2000 it works perfectly.
Thanks
--
CTS DBA
"bass_player [SBS-MVP]" wrote:

> I tried detach and re-attach the database fie and run DBCC SHRINKFILE
> But I wouldn't recommend shrinking. Here's an article by the great Tibor
> Karaszi on database shrinking
> http://www.karaszi.com/SQLServer/info_dont_shrink.asp
> "CTS_DBA" <CTSDBA@.discussions.microsoft.com> wrote in message
> news:630320F3-22A4-4D33-A245-54D14D4784FD@.microsoft.com...
>
>

Error when cast Varchar to decimal

I'm writing a script with an insert statment to insert records from one
database to another but I have to change the type first. How can I find out
more about what can be causing this or is it something wrong with my syntax?
The field [patient_weight] is a varchar(256) stripped of all spaces left and
right.
Thanks, Alpha
cast([patient_weight] as decimal(10,2))
What result do you get here?
SELECT COUNT(*) FROM [source table name]
WHERE ISNUMERIC(patient_weight) = 0
Please post DDL, sample data and desired results.
See http://www.aspfaq.com/5006 for info.
"Alpha" <Alpha@.discussions.microsoft.com> wrote in message
news:D0914609-50C8-4480-A15D-302752BECA86@.microsoft.com...
> I'm writing a script with an insert statment to insert records from one
> database to another but I have to change the type first. How can I find
out
> more about what can be causing this or is it something wrong with my
syntax?
> The field [patient_weight] is a varchar(256) stripped of all spaces left
and
> right.
> Thanks, Alpha
> cast([patient_weight] as decimal(10,2))
>
|||I got 4. I looked at the table and they're all '0'(zero) except these 4.
"Aaron [SQL Server MVP]" wrote:

> What result do you get here?
> SELECT COUNT(*) FROM [source table name]
> WHERE ISNUMERIC(patient_weight) = 0
> --
> Please post DDL, sample data and desired results.
> See http://www.aspfaq.com/5006 for info.
>
>
> "Alpha" <Alpha@.discussions.microsoft.com> wrote in message
> news:D0914609-50C8-4480-A15D-302752BECA86@.microsoft.com...
> out
> syntax?
> and
>
>
|||Well, you need to fix those 4 rows.
SELECT * FROM Source_Table_Name
WHERE ISNUMERIC(patient_weight) = 0
If they look like 0's, maybe they're o's, or maybe they're zero's with
non-printable characters attached (e.g. carriage return).
UPDATE Source_Table_Name SET patient_weight = '0'
WHERE ISNUMERIC(patient_weight) = 0
Please post DDL, sample data and desired results.
See http://www.aspfaq.com/5006 for info.
"Alpha" <Alpha@.discussions.microsoft.com> wrote in message
news:5B264C8D-9F07-4A20-8066-93256AD0D975@.microsoft.com...[vbcol=seagreen]
> I got 4. I looked at the table and they're all '0'(zero) except these 4.
> "Aaron [SQL Server MVP]" wrote:
one[vbcol=seagreen]
find[vbcol=seagreen]
left[vbcol=seagreen]
|||Thank you that works but I don't understadn what happend here. So these 4
rows has a '0'(zero) char in it with CR. So how does this query able to
select it? ISNUMERIC cast it to 0 (zero in numerica) right? I don't
understand.
Thanks,
Alpha
> SELECT * FROM Source_Table_Name
> WHERE ISNUMERIC(patient_weight) = 0
"Aaron [SQL Server MVP]" wrote:

> Well, you need to fix those 4 rows.
> SELECT * FROM Source_Table_Name
> WHERE ISNUMERIC(patient_weight) = 0
> If they look like 0's, maybe they're o's, or maybe they're zero's with
> non-printable characters attached (e.g. carriage return).
> UPDATE Source_Table_Name SET patient_weight = '0'
> WHERE ISNUMERIC(patient_weight) = 0
> --
> Please post DDL, sample data and desired results.
> See http://www.aspfaq.com/5006 for info.
>
>
> "Alpha" <Alpha@.discussions.microsoft.com> wrote in message
> news:5B264C8D-9F07-4A20-8066-93256AD0D975@.microsoft.com...
> one
> find
> left
>
>
|||> Thank you that works but I don't understadn what happend here. So these 4
> rows has a '0'(zero) char in it with CR. So how does this query able to
> select it? ISNUMERIC cast it to 0 (zero in numerica) right?
No, ISNUMERIC(expression) returns either 1 or 0. If the number is numeric*,
it returns 1, otherwise it returns 0. Something about the way you decided
to store numeric data in a character data type led to problems in these 4
rows (which could have been predicted at the outset, and prevented by
choosing the correct data type).
* ISNUMERIC() is not perfect. See http://www.aspfaq.com/2390
|||Oh, got it. Thank you very much and have a nice day.
Alpha
"Aaron [SQL Server MVP]" wrote:

> No, ISNUMERIC(expression) returns either 1 or 0. If the number is numeric*,
> it returns 1, otherwise it returns 0. Something about the way you decided
> to store numeric data in a character data type led to problems in these 4
> rows (which could have been predicted at the outset, and prevented by
> choosing the correct data type).
> * ISNUMERIC() is not perfect. See http://www.aspfaq.com/2390
>
>

Error when cast Varchar to decimal

I'm writing a script with an insert statment to insert records from one
database to another but I have to change the type first. How can I find out
more about what can be causing this or is it something wrong with my syntax?
The field [patient_weight] is a varchar(256) stripped of all spaces left
and
right.
Thanks, Alpha
cast([patient_weight] as decimal(10,2))What result do you get here?
SELECT COUNT(*) FROM [source table name]
WHERE ISNUMERIC(patient_weight) = 0
Please post DDL, sample data and desired results.
See http://www.aspfaq.com/5006 for info.
"Alpha" <Alpha@.discussions.microsoft.com> wrote in message
news:D0914609-50C8-4480-A15D-302752BECA86@.microsoft.com...
> I'm writing a script with an insert statment to insert records from one
> database to another but I have to change the type first. How can I find
out
> more about what can be causing this or is it something wrong with my
syntax?
> The field [patient_weight] is a varchar(256) stripped of all spaces left[/vbco
l]
and[vbcol=seagreen]
> right.
> Thanks, Alpha
> cast([patient_weight] as decimal(10,2))
>|||I got 4. I looked at the table and they're all '0'(zero) except these 4.
"Aaron [SQL Server MVP]" wrote:

> What result do you get here?
> SELECT COUNT(*) FROM [source table name]
> WHERE ISNUMERIC(patient_weight) = 0
> --
> Please post DDL, sample data and desired results.
> See http://www.aspfaq.com/5006 for info.
>
>
> "Alpha" <Alpha@.discussions.microsoft.com> wrote in message
> news:D0914609-50C8-4480-A15D-302752BECA86@.microsoft.com...
> out
> syntax?
> and
>
>|||Well, you need to fix those 4 rows.
SELECT * FROM Source_Table_Name
WHERE ISNUMERIC(patient_weight) = 0
If they look like 0's, maybe they're o's, or maybe they're zero's with
non-printable characters attached (e.g. carriage return).
UPDATE Source_Table_Name SET patient_weight = '0'
WHERE ISNUMERIC(patient_weight) = 0
Please post DDL, sample data and desired results.
See http://www.aspfaq.com/5006 for info.
"Alpha" <Alpha@.discussions.microsoft.com> wrote in message
news:5B264C8D-9F07-4A20-8066-93256AD0D975@.microsoft.com...[vbcol=seagreen]
> I got 4. I looked at the table and they're all '0'(zero) except these 4.
> "Aaron [SQL Server MVP]" wrote:
>
one[vbcol=seagreen]
find[vbcol=seagreen]
left[vbcol=seagreen]|||Thank you that works but I don't understadn what happend here. So these 4
rows has a '0'(zero) char in it with CR. So how does this query able to
select it? ISNUMERIC cast it to 0 (zero in numerica) right? I don't
understand.
Thanks,
Alpha
> SELECT * FROM Source_Table_Name
> WHERE ISNUMERIC(patient_weight) = 0
"Aaron [SQL Server MVP]" wrote:

> Well, you need to fix those 4 rows.
> SELECT * FROM Source_Table_Name
> WHERE ISNUMERIC(patient_weight) = 0
> If they look like 0's, maybe they're o's, or maybe they're zero's with
> non-printable characters attached (e.g. carriage return).
> UPDATE Source_Table_Name SET patient_weight = '0'
> WHERE ISNUMERIC(patient_weight) = 0
> --
> Please post DDL, sample data and desired results.
> See http://www.aspfaq.com/5006 for info.
>
>
> "Alpha" <Alpha@.discussions.microsoft.com> wrote in message
> news:5B264C8D-9F07-4A20-8066-93256AD0D975@.microsoft.com...
> one
> find
> left
>
>|||> Thank you that works but I don't understadn what happend here. So these 4
> rows has a '0'(zero) char in it with CR. So how does this query able to
> select it? ISNUMERIC cast it to 0 (zero in numerica) right?
No, ISNUMERIC(expression) returns either 1 or 0. If the number is numeric*,
it returns 1, otherwise it returns 0. Something about the way you decided
to store numeric data in a character data type led to problems in these 4
rows (which could have been predicted at the outset, and prevented by
choosing the correct data type).
* ISNUMERIC() is not perfect. See http://www.aspfaq.com/2390|||Oh, got it. Thank you very much and have a nice day.
Alpha
"Aaron [SQL Server MVP]" wrote:

> No, ISNUMERIC(expression) returns either 1 or 0. If the number is numeric
*,
> it returns 1, otherwise it returns 0. Something about the way you decided
> to store numeric data in a character data type led to problems in these 4
> rows (which could have been predicted at the outset, and prevented by
> choosing the correct data type).
> * ISNUMERIC() is not perfect. See http://www.aspfaq.com/2390
>
>