DBCC SHRINKFILE ('filename' , EMPTYFILE) not working

I created a file (size = 1 GB, filegrowth=1 GB) but I forgot to add it to the
correct filegroup (it defaulted to the primary).
I am trying to use DBCC shrinkfile with the emptyfile option so that I can
then go and do a 'alter database remove file'. I start the process and with
sp_who2 active it shows the command DbccFilesCompact, but it just sits there
and spins forever.
I see cpu and diskio cycles escalating but nothing ever happens.
is there a step before this that i am forgetting?
setup is: x64 sql 2005, sp1 (windwos 2003 ee x64), db in simple recovery
model.
gracias,
Robert
i should state a few more facts.
It is a data file (not log), and the whole DB 1.8 TB. The file is only 100GB.
I have tried to backup just that filegroup (only 1 file in it) and do a
emptyfile right afterwards but no luck...
"sql411@.nospam.com" wrote:

> I created a file (size = 1 GB, filegrowth=1 GB) but I forgot to add it to the
> correct filegroup (it defaulted to the primary).
> I am trying to use DBCC shrinkfile with the emptyfile option so that I can
> then go and do a 'alter database remove file'. I start the process and with
> sp_who2 active it shows the command DbccFilesCompact, but it just sits there
> and spins forever.
> I see cpu and diskio cycles escalating but nothing ever happens.
> is there a step before this that i am forgetting?
> setup is: x64 sql 2005, sp1 (windwos 2003 ee x64), db in simple recovery
> model.
> gracias,
> Robert
sql

DBCC SHRINKFILE ('filename' , EMPTYFILE) not working

I created a file (size = 1 GB, filegrowth=1 GB) but I forgot to add it to th
e
correct filegroup (it defaulted to the primary).
I am trying to use DBCC shrinkfile with the emptyfile option so that I can
then go and do a 'alter database remove file'. I start the process and with
sp_who2 active it shows the command DbccFilesCompact, but it just sits there
and spins forever.
I see cpu and diskio cycles escalating but nothing ever happens.
is there a step before this that i am forgetting?
setup is: x64 sql 2005, sp1 (windwos 2003 ee x64), db in simple recovery
model.
gracias,
Roberti should state a few more facts.
It is a data file (not log), and the whole DB 1.8 TB. The file is only 100G
B.
I have tried to backup just that filegroup (only 1 file in it) and do a
emptyfile right afterwards but no luck...
"sql411@.nospam.com" wrote:

> I created a file (size = 1 GB, filegrowth=1 GB) but I forgot to add it to
the
> correct filegroup (it defaulted to the primary).
> I am trying to use DBCC shrinkfile with the emptyfile option so that I can
> then go and do a 'alter database remove file'. I start the process and wi
th
> sp_who2 active it shows the command DbccFilesCompact, but it just sits the
re
> and spins forever.
> I see cpu and diskio cycles escalating but nothing ever happens.
> is there a step before this that i am forgetting?
> setup is: x64 sql 2005, sp1 (windwos 2003 ee x64), db in simple recovery
> model.
> gracias,
> Robert

DBCC Shrinkfile

I ran a dbcc shrinkfile after deleting data in order to take thte db down in size and it has been running for 2 days. Can I canel the command? Will the file be partially shrunk? Last year when we did this I ran a re-index command. Will I have to run a bakup or truncate log command? I have been trying to get the client to upgrade from SQL 7 but they just have not done it yet.follow up question. If I cancel how long will it take to finish the cancel operation?

DBCC Shrinkfile

Hi,
Can someone tell me of some the reasons why DBCC Shrinkfile does not shrink
the size of the log file in my SQL Server 2000 server? After backing up the
log file, I tried to shrink the physical size of the log file to 2000 MB but
it gave me an error (which I can't remember) even though the actual used size
(not phyical) was like 1500 MB. I tried again with a larger number, 5000 MB
and this time it worked.
TIA.When you have problems shrinking, you should check for open
transactions using dbcc opentran
You also need to keep in mind that an active portion of the
log can't be impacted. So if you have an active portion of
the log at the end, you won't be able to shrink. You can
check this using dbcc loginfo(YourDatabase). A status of 2
indicates it's active.
Make sure you aren't needlessly shrinking. It should be more
of an ad hoc process - after some out of control process
blows out the size of the log type of thing.
You can find more information in the following article and a
more thorough explanation of what you see with dbcc loginfo:
http://www.karaszi.com/SQLServer/info_dont_shrink.asp
-Sue
On Wed, 11 Oct 2006 14:19:02 -0700, Rob
<Rob@.discussions.microsoft.com> wrote:
>Hi,
>Can someone tell me of some the reasons why DBCC Shrinkfile does not shrink
>the size of the log file in my SQL Server 2000 server? After backing up the
>log file, I tried to shrink the physical size of the log file to 2000 MB but
>it gave me an error (which I can't remember) even though the actual used size
>(not phyical) was like 1500 MB. I tried again with a larger number, 5000 MB
>and this time it worked.
>TIA.

DBCC SHRINKFILE

A few days ago, I made some structure changes to 5 tables and added
some indexes to them.
This caused the mdf file size to increase from 82GB to 109GB. Because
our development and training boxes do not have enough disk space, I
tried to shrink the file so that the database can fit into the
development box when I do a restore.
I created a job to run the following at 3AM:
DBCC SHRINKFILE (N'Nu_Data', 85000)
At 6AM, the job crashed with the following error.
Transaction (Process ID 66) was deadlocked on lock resources with
another process and has been chosen as the deadlock victim. Rerun the
transaction.
Our database is running transactional replication. Do I need to pause
the replication and set the database in single user mode before
executing the DBCC SHRINKFILE (N'N_Data', 85000)?
alter database Nu
set single_user with ROLLBACK IMMEDIATE
go
DBCC SHRINKFILE (N'Nu_Data', 85000)
go
alter database Nu
set multi_user
goHi
Execute DBCC SHRINKFILE when there is no active transacton in progress for that database. Check this by executing
DBCC OPENTRAN(DBNAME). It can be a replication process or it can can be a normal user connecting to the server and using some DML.
So execute the SHRINKFILE command when there is no activity in the database. Preferably do not schedule this as a job, rather do it manually if it is a production serve
Thank
Har
MCDB
-- KC wrote: --
A few days ago, I made some structure changes to 5 tables and adde
some indexes to them
This caused the mdf file size to increase from 82GB to 109GB. Becaus
our development and training boxes do not have enough disk space,
tried to shrink the file so that the database can fit into th
development box when I do a restore
I created a job to run the following at 3AM
DBCC SHRINKFILE (N'Nu_Data', 85000
At 6AM, the job crashed with the following error
Transaction (Process ID 66) was deadlocked on lock resources wit
another process and has been chosen as the deadlock victim. Rerun th
transaction
Our database is running transactional replication. Do I need to paus
the replication and set the database in single user mode befor
executing the DBCC SHRINKFILE (N'N_Data', 85000)
alter database N
set single_user with ROLLBACK IMMEDIATE
g
DBCC SHRINKFILE (N'Nu_Data', 85000
g
alter database N
set multi_use
g

DBCC SHRINKFILE

A few days ago, I made some structure changes to 5 tables and added
some indexes to them.
This caused the mdf file size to increase from 82GB to 109GB. Because
our development and training boxes do not have enough disk space, I
tried to shrink the file so that the database can fit into the
development box when I do a restore.
I created a job to run the following at 3AM:
DBCC SHRINKFILE (N'Nu_Data', 85000)
At 6AM, the job crashed with the following error.
Transaction (Process ID 66) was deadlocked on lock resources with
another process and has been chosen as the deadlock victim. Rerun the
transaction.
Our database is running transactional replication. Do I need to pause
the replication and set the database in single user mode before
executing the DBCC SHRINKFILE (N'N_Data', 85000)?
alter database Nu
set single_user with ROLLBACK IMMEDIATE
go
DBCC SHRINKFILE (N'Nu_Data', 85000)
go
alter database Nu
set multi_user
goHi,
Execute DBCC SHRINKFILE when there is no active transacton in progress for t
hat database. Check this by executing
DBCC OPENTRAN(DBNAME). It can be a replication process or it can can be a no
rmal user connecting to the server and using some DML.
So execute the SHRINKFILE command when there is no activity in the database.
Preferably do not schedule this as a job, rather do it manually if it is a
production server
Thanks
Hari
MCDBA
-- KC wrote: --
A few days ago, I made some structure changes to 5 tables and added
some indexes to them.
This caused the mdf file size to increase from 82GB to 109GB. Because
our development and training boxes do not have enough disk space, I
tried to shrink the file so that the database can fit into the
development box when I do a restore.
I created a job to run the following at 3AM:
DBCC SHRINKFILE (N'Nu_Data', 85000)
At 6AM, the job crashed with the following error.
Transaction (Process ID 66) was deadlocked on lock resources with
another process and has been chosen as the deadlock victim. Rerun the
transaction.
Our database is running transactional replication. Do I need to pause
the replication and set the database in single user mode before
executing the DBCC SHRINKFILE (N'N_Data', 85000)?
alter database Nu
set single_user with ROLLBACK IMMEDIATE
go
DBCC SHRINKFILE (N'Nu_Data', 85000)
go
alter database Nu
set multi_user
go

DBCC Shrinkfile

Hi,
Can someone tell me of some the reasons why DBCC Shrinkfile does not shrink
the size of the log file in my SQL Server 2000 server? After backing up the
log file, I tried to shrink the physical size of the log file to 2000 MB but
it gave me an error (which I can't remember) even though the actual used size
(not phyical) was like 1500 MB. I tried again with a larger number, 5000 MB
and this time it worked.
TIA.
When you have problems shrinking, you should check for open
transactions using dbcc opentran
You also need to keep in mind that an active portion of the
log can't be impacted. So if you have an active portion of
the log at the end, you won't be able to shrink. You can
check this using dbcc loginfo(YourDatabase). A status of 2
indicates it's active.
Make sure you aren't needlessly shrinking. It should be more
of an ad hoc process - after some out of control process
blows out the size of the log type of thing.
You can find more information in the following article and a
more thorough explanation of what you see with dbcc loginfo:
http://www.karaszi.com/SQLServer/info_dont_shrink.asp
-Sue
On Wed, 11 Oct 2006 14:19:02 -0700, Rob
<Rob@.discussions.microsoft.com> wrote:

>Hi,
>Can someone tell me of some the reasons why DBCC Shrinkfile does not shrink
>the size of the log file in my SQL Server 2000 server? After backing up the
>log file, I tried to shrink the physical size of the log file to 2000 MB but
>it gave me an error (which I can't remember) even though the actual used size
>(not phyical) was like 1500 MB. I tried again with a larger number, 5000 MB
>and this time it worked.
>TIA.

DBCC SHRINKFILE

A few days ago, I made some structure changes to 5 tables and added
some indexes to them.
This caused the mdf file size to increase from 82GB to 109GB. Because
our development and training boxes do not have enough disk space, I
tried to shrink the file so that the database can fit into the
development box when I do a restore.
I created a job to run the following at 3AM:
DBCC SHRINKFILE (N'Nu_Data', 85000)
At 6AM, the job crashed with the following error.
Transaction (Process ID 66) was deadlocked on lock resources with
another process and has been chosen as the deadlock victim. Rerun the
transaction.
Our database is running transactional replication. Do I need to pause
the replication and set the database in single user mode before
executing the DBCC SHRINKFILE (N'N_Data', 85000)?
alter database Nu
set single_user with ROLLBACK IMMEDIATE
go
DBCC SHRINKFILE (N'Nu_Data', 85000)
go
alter database Nu
set multi_user
go
Hi,
Execute DBCC SHRINKFILE when there is no active transacton in progress for that database. Check this by executing
DBCC OPENTRAN(DBNAME). It can be a replication process or it can can be a normal user connecting to the server and using some DML.
So execute the SHRINKFILE command when there is no activity in the database. Preferably do not schedule this as a job, rather do it manually if it is a production server
Thanks
Hari
MCDBA
-- KC wrote: --
A few days ago, I made some structure changes to 5 tables and added
some indexes to them.
This caused the mdf file size to increase from 82GB to 109GB. Because
our development and training boxes do not have enough disk space, I
tried to shrink the file so that the database can fit into the
development box when I do a restore.
I created a job to run the following at 3AM:
DBCC SHRINKFILE (N'Nu_Data', 85000)
At 6AM, the job crashed with the following error.
Transaction (Process ID 66) was deadlocked on lock resources with
another process and has been chosen as the deadlock victim. Rerun the
transaction.
Our database is running transactional replication. Do I need to pause
the replication and set the database in single user mode before
executing the DBCC SHRINKFILE (N'N_Data', 85000)?
alter database Nu
set single_user with ROLLBACK IMMEDIATE
go
DBCC SHRINKFILE (N'Nu_Data', 85000)
go
alter database Nu
set multi_user
go
sql

DBCC Shrinkfile

Hi,
Can someone tell me of some the reasons why DBCC Shrinkfile does not shrink
the size of the log file in my SQL Server 2000 server? After backing up the
log file, I tried to shrink the physical size of the log file to 2000 MB but
it gave me an error (which I can't remember) even though the actual used siz
e
(not phyical) was like 1500 MB. I tried again with a larger number, 5000 MB
and this time it worked.
TIA.When you have problems shrinking, you should check for open
transactions using dbcc opentran
You also need to keep in mind that an active portion of the
log can't be impacted. So if you have an active portion of
the log at the end, you won't be able to shrink. You can
check this using dbcc loginfo(YourDatabase). A status of 2
indicates it's active.
Make sure you aren't needlessly shrinking. It should be more
of an ad hoc process - after some out of control process
blows out the size of the log type of thing.
You can find more information in the following article and a
more thorough explanation of what you see with dbcc loginfo:
http://www.karaszi.com/SQLServer/info_dont_shrink.asp
-Sue
On Wed, 11 Oct 2006 14:19:02 -0700, Rob
<Rob@.discussions.microsoft.com> wrote:

>Hi,
>Can someone tell me of some the reasons why DBCC Shrinkfile does not shrink
>the size of the log file in my SQL Server 2000 server? After backing up the
>log file, I tried to shrink the physical size of the log file to 2000 MB bu
t
>it gave me an error (which I can't remember) even though the actual used si
ze
>(not phyical) was like 1500 MB. I tried again with a larger number, 5000 MB
>and this time it worked.
>TIA.

DBCC SHRINKDATABASE Question

We need to shrink one of our production databases - SQL Server 2000,
service pack 4. The size of the database is currently 26GB in size,
with about 40-50% free space.
We have concerns about the performance impact of running the DBCC
SHRINKDATABASE statement.
I would like to hear other people's experience with running this
command.
My understanding is that it will NOT take the database offline, that
is, users can still query the database - but is it going to create a
serious performance problem?
Also, approximately how long would a shrink of this size take?
In addition to these questions, I need general recommendations about
shrinking the database, caveats, etc.
Thank you!!
See:
http://support.microsoft.com/default...b;en-us;272318 --not sure if
this is data or log file
and
http://www.karaszi.com/sqlserver/info_dont_shrink.asp
PS - You cannot shink a db smaller than it's orriginal creation size using
DBCC SHRINKDATABASE - must use DBCC SHRINKFILE.
HTH
Jerry
<tootsuite@.gmail.com> wrote in message
news:1129658373.884310.50430@.g14g2000cwa.googlegro ups.com...
> We need to shrink one of our production databases - SQL Server 2000,
> service pack 4. The size of the database is currently 26GB in size,
> with about 40-50% free space.
> We have concerns about the performance impact of running the DBCC
> SHRINKDATABASE statement.
> I would like to hear other people's experience with running this
> command.
> My understanding is that it will NOT take the database offline, that
> is, users can still query the database - but is it going to create a
> serious performance problem?
> Also, approximately how long would a shrink of this size take?
> In addition to these questions, I need general recommendations about
> shrinking the database, caveats, etc.
> Thank you!!
>

DBCC SHRINKDATABASE Question

We need to shrink one of our production databases - SQL Server 2000,
service pack 4. The size of the database is currently 26GB in size,
with about 40-50% free space.
We have concerns about the performance impact of running the DBCC
SHRINKDATABASE statement.
I would like to hear other people's experience with running this
command.
My understanding is that it will NOT take the database offline, that
is, users can still query the database - but is it going to create a
serious performance problem?
Also, approximately how long would a shrink of this size take?
In addition to these questions, I need general recommendations about
shrinking the database, caveats, etc.
Thank you!!See:
http://support.microsoft.com/defaul...kb;en-us;272318 --not sure if
this is data or log file
and
http://www.karaszi.com/sqlserver/info_dont_shrink.asp
PS - You cannot shink a db smaller than it's orriginal creation size using
DBCC SHRINKDATABASE - must use DBCC SHRINKFILE.
HTH
Jerry
<tootsuite@.gmail.com> wrote in message
news:1129658373.884310.50430@.g14g2000cwa.googlegroups.com...
> We need to shrink one of our production databases - SQL Server 2000,
> service pack 4. The size of the database is currently 26GB in size,
> with about 40-50% free space.
> We have concerns about the performance impact of running the DBCC
> SHRINKDATABASE statement.
> I would like to hear other people's experience with running this
> command.
> My understanding is that it will NOT take the database offline, that
> is, users can still query the database - but is it going to create a
> serious performance problem?
> Also, approximately how long would a shrink of this size take?
> In addition to these questions, I need general recommendations about
> shrinking the database, caveats, etc.
> Thank you!!
>

DBCC SHRINKDATABASE Question

We need to shrink one of our production databases - SQL Server 2000,
service pack 4. The size of the database is currently 26GB in size,
with about 40-50% free space.
We have concerns about the performance impact of running the DBCC
SHRINKDATABASE statement.
I would like to hear other people's experience with running this
command.
My understanding is that it will NOT take the database offline, that
is, users can still query the database - but is it going to create a
serious performance problem?
Also, approximately how long would a shrink of this size take?
In addition to these questions, I need general recommendations about
shrinking the database, caveats, etc.
Thank you!!See:
http://support.microsoft.com/default.aspx?scid=kb;en-us;272318 --not sure if
this is data or log file
and
http://www.karaszi.com/sqlserver/info_dont_shrink.asp
PS - You cannot shink a db smaller than it's orriginal creation size using
DBCC SHRINKDATABASE - must use DBCC SHRINKFILE.
HTH
Jerry
<tootsuite@.gmail.com> wrote in message
news:1129658373.884310.50430@.g14g2000cwa.googlegroups.com...
> We need to shrink one of our production databases - SQL Server 2000,
> service pack 4. The size of the database is currently 26GB in size,
> with about 40-50% free space.
> We have concerns about the performance impact of running the DBCC
> SHRINKDATABASE statement.
> I would like to hear other people's experience with running this
> command.
> My understanding is that it will NOT take the database offline, that
> is, users can still query the database - but is it going to create a
> serious performance problem?
> Also, approximately how long would a shrink of this size take?
> In addition to these questions, I need general recommendations about
> shrinking the database, caveats, etc.
> Thank you!!
>sql

DBCC SHRINKDATABASE

In my company, we use this command to shink databse, but it was not like our
expectation after it was done because the size was reduced less than
expectation. Could someone tell me if there are some factor to impact this
operation? What can I do to shrink much more size of database. Thanks.
Jerry Mu
See DBCC SHRINKFILE, section 'The File Does Not Shrink' on BOL.
Run the query listed there to see if sufficient free space is available
SELECT name ,size/128.0 - CAST(FILEPROPERTY(name, 'SpaceUsed') AS int)/128.0
AS AvailableSpaceInMB
FROM sys.database_files;
Hope this helps,
Ben Nevarez
Senior Database Administrator
"Iter" wrote:

> In my company, we use this command to shink databse, but it was not like our
> expectation after it was done because the size was reduced less than
> expectation. Could someone tell me if there are some factor to impact this
> operation? What can I do to shrink much more size of database. Thanks.
> Jerry Mu
>
|||Also, see the following article:
http://www.karaszi.com/SQLServer/info_dont_shrink.asp
Ekrem ?nsoy
"Iter" <Iter@.discussions.microsoft.com> wrote in message
news:75F13788-9781-4DE1-8BEF-AA39A6977CEB@.microsoft.com...
> In my company, we use this command to shink databse, but it was not like
> our
> expectation after it was done because the size was reduced less than
> expectation. Could someone tell me if there are some factor to impact this
> operation? What can I do to shrink much more size of database. Thanks.
> Jerry Mu
>
|||Are you sure there is free space in the database? What is your goal in
shrinking the database? Reclaiming disk space temporarily? Unless your
database is large only because of a very unusual data load, and that data is
now gone, I would not allocate that disk space to something else just yet.
If your database is large because of normal activity, then shrinking it will
only mean that later it has to grow again, and you do not want this to be an
unexpected event that happens during peak activity, because you will have a
lot of unhappy users. Better off leaving the file large...
"Iter" <Iter@.discussions.microsoft.com> wrote in message
news:75F13788-9781-4DE1-8BEF-AA39A6977CEB@.microsoft.com...
> In my company, we use this command to shink databse, but it was not like
> our
> expectation after it was done because the size was reduced less than
> expectation. Could someone tell me if there are some factor to impact this
> operation? What can I do to shrink much more size of database. Thanks.
> Jerry Mu
>
|||Also I second the recommendation to read Tibor's article, posted by Ekrem.
It echoes my suggestions but in much more detail.
A
"Iter" <Iter@.discussions.microsoft.com> wrote in message
news:75F13788-9781-4DE1-8BEF-AA39A6977CEB@.microsoft.com...
> In my company, we use this command to shink databse, but it was not like
> our
> expectation after it was done because the size was reduced less than
> expectation. Could someone tell me if there are some factor to impact this
> operation? What can I do to shrink much more size of database. Thanks.
> Jerry Mu
>

DBCC SHRINKDATABASE

In my company, we use this command to shink databse, but it was not like our
expectation after it was done because the size was reduced less than
expectation. Could someone tell me if there are some factor to impact this
operation? What can I do to shrink much more size of database. Thanks.
Jerry MuSee DBCC SHRINKFILE, section 'The File Does Not Shrink' on BOL.
Run the query listed there to see if sufficient free space is available
SELECT name ,size/128.0 - CAST(FILEPROPERTY(name, 'SpaceUsed') AS int)/128.0
AS AvailableSpaceInMB
FROM sys.database_files;
Hope this helps,
Ben Nevarez
Senior Database Administrator
"Iter" wrote:
> In my company, we use this command to shink databse, but it was not like our
> expectation after it was done because the size was reduced less than
> expectation. Could someone tell me if there are some factor to impact this
> operation? What can I do to shrink much more size of database. Thanks.
> Jerry Mu
>|||Also, see the following article:
http://www.karaszi.com/SQLServer/info_dont_shrink.asp
--
Ekrem Ã?nsoy
"Iter" <Iter@.discussions.microsoft.com> wrote in message
news:75F13788-9781-4DE1-8BEF-AA39A6977CEB@.microsoft.com...
> In my company, we use this command to shink databse, but it was not like
> our
> expectation after it was done because the size was reduced less than
> expectation. Could someone tell me if there are some factor to impact this
> operation? What can I do to shrink much more size of database. Thanks.
> Jerry Mu
>|||Are you sure there is free space in the database? What is your goal in
shrinking the database? Reclaiming disk space temporarily? Unless your
database is large only because of a very unusual data load, and that data is
now gone, I would not allocate that disk space to something else just yet.
If your database is large because of normal activity, then shrinking it will
only mean that later it has to grow again, and you do not want this to be an
unexpected event that happens during peak activity, because you will have a
lot of unhappy users. Better off leaving the file large...
"Iter" <Iter@.discussions.microsoft.com> wrote in message
news:75F13788-9781-4DE1-8BEF-AA39A6977CEB@.microsoft.com...
> In my company, we use this command to shink databse, but it was not like
> our
> expectation after it was done because the size was reduced less than
> expectation. Could someone tell me if there are some factor to impact this
> operation? What can I do to shrink much more size of database. Thanks.
> Jerry Mu
>|||Also I second the recommendation to read Tibor's article, posted by Ekrem.
It echoes my suggestions but in much more detail.
A
"Iter" <Iter@.discussions.microsoft.com> wrote in message
news:75F13788-9781-4DE1-8BEF-AA39A6977CEB@.microsoft.com...
> In my company, we use this command to shink databse, but it was not like
> our
> expectation after it was done because the size was reduced less than
> expectation. Could someone tell me if there are some factor to impact this
> operation? What can I do to shrink much more size of database. Thanks.
> Jerry Mu
>sql

dbcc reports different size than shown in database properties

In sql 2k, I've been looking at size and autogrowth, and some issues have
come up.
In my database properties, general tab, I see size 9118mb, space avail
1372mb. However, when I run
dbcc showfilestats
I get an allocated size of 7808mb (TotalExtents*64/1024.00), and free space
of 189mb ((TotalExtents-UsedExtents)*24/1024). Why this huge discrepancy?
Perhaps the log is counted in the size, but surely that should not affect
space available.
Jeremy
Try running DBCC UPDATEUSAGE and see if that helps.
Andrew J. Kelly SQL MVP
"JeremyGrand" <jeremy@.ninprodata.com> wrote in message
news:eUDnqux7GHA.1560@.TK2MSFTNGP04.phx.gbl...
> In sql 2k, I've been looking at size and autogrowth, and some issues have
> come up.
> In my database properties, general tab, I see size 9118mb, space avail
> 1372mb. However, when I run
> dbcc showfilestats
> I get an allocated size of 7808mb (TotalExtents*64/1024.00), and free
> space of 189mb ((TotalExtents-UsedExtents)*24/1024). Why this huge
> discrepancy? Perhaps the log is counted in the size, but surely that
> should not affect space available.
> Jeremy
>

dbcc reports different size than shown in database properties

In sql 2k, I've been looking at size and autogrowth, and some issues have
come up.
In my database properties, general tab, I see size 9118mb, space avail
1372mb. However, when I run
dbcc showfilestats
I get an allocated size of 7808mb (TotalExtents*64/1024.00), and free space
of 189mb ((TotalExtents-UsedExtents)*24/1024). Why this huge discrepancy?
Perhaps the log is counted in the size, but surely that should not affect
space available.
JeremyTry running DBCC UPDATEUSAGE and see if that helps.
--
Andrew J. Kelly SQL MVP
"JeremyGrand" <jeremy@.ninprodata.com> wrote in message
news:eUDnqux7GHA.1560@.TK2MSFTNGP04.phx.gbl...
> In sql 2k, I've been looking at size and autogrowth, and some issues have
> come up.
> In my database properties, general tab, I see size 9118mb, space avail
> 1372mb. However, when I run
> dbcc showfilestats
> I get an allocated size of 7808mb (TotalExtents*64/1024.00), and free
> space of 189mb ((TotalExtents-UsedExtents)*24/1024). Why this huge
> discrepancy? Perhaps the log is counted in the size, but surely that
> should not affect space available.
> Jeremy
>

dbcc reports different size than shown in database properties

In sql 2k, I've been looking at size and autogrowth, and some issues have
come up.
In my database properties, general tab, I see size 9118mb, space avail
1372mb. However, when I run
dbcc showfilestats
I get an allocated size of 7808mb (TotalExtents*64/1024.00), and free space
of 189mb ((TotalExtents-UsedExtents)*24/1024). Why this huge discrepancy?
Perhaps the log is counted in the size, but surely that should not affect
space available.
JeremyTry running DBCC UPDATEUSAGE and see if that helps.
Andrew J. Kelly SQL MVP
"JeremyGrand" <jeremy@.ninprodata.com> wrote in message
news:eUDnqux7GHA.1560@.TK2MSFTNGP04.phx.gbl...
> In sql 2k, I've been looking at size and autogrowth, and some issues have
> come up.
> In my database properties, general tab, I see size 9118mb, space avail
> 1372mb. However, when I run
> dbcc showfilestats
> I get an allocated size of 7808mb (TotalExtents*64/1024.00), and free
> space of 189mb ((TotalExtents-UsedExtents)*24/1024). Why this huge
> discrepancy? Perhaps the log is counted in the size, but surely that
> should not affect space available.
> Jeremy
>

dbcc proccache units

what are the units for proc cache size? i'm assuming they're in kb and
want to make sure. thanks,
arthurIt seems to be pages.
HTH
--
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com
"arthur" <alangham@.gmail.com> wrote in message
news:1106239956.487457.76280@.z14g2000cwz.googlegroups.com...
> what are the units for proc cache size? i'm assuming they're in kb and
> want to make sure. thanks,
> arthur
>|||how'd you figure that out? i'm trying to reconcile the performance
monitor's sqlserver:cache manager procedure plans w/ the results from
dbcc proccache. basically what i eventually need to find out is how
much space there is total in the procedure cache.
thanks,
arthur|||You can also run DBCC MEMORYSTATUS.
When I do that, I get the same value for Procedure Cache, Total Pages as I
do for Proc Cache Size in DBCC PROCCACHE. Since I get the same number, I
figure it's the same unit.
HTH
--
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com
"arthur" <alangham@.gmail.com> wrote in message
news:1106249027.345721.114590@.f14g2000cwb.googlegroups.com...
> how'd you figure that out? i'm trying to reconcile the performance
> monitor's sqlserver:cache manager procedure plans w/ the results from
> dbcc proccache. basically what i eventually need to find out is how
> much space there is total in the procedure cache.
> thanks,
> arthur
>sql

dbcc proccache units

what are the units for proc cache size? i'm assuming they're in kb and
want to make sure. thanks,
arthur
It seems to be pages.
HTH
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com
"arthur" <alangham@.gmail.com> wrote in message
news:1106239956.487457.76280@.z14g2000cwz.googlegro ups.com...
> what are the units for proc cache size? i'm assuming they're in kb and
> want to make sure. thanks,
> arthur
>
|||how'd you figure that out? i'm trying to reconcile the performance
monitor's sqlserver:cache manager procedure plans w/ the results from
dbcc proccache. basically what i eventually need to find out is how
much space there is total in the procedure cache.
thanks,
arthur
|||You can also run DBCC MEMORYSTATUS.
When I do that, I get the same value for Procedure Cache, Total Pages as I
do for Proc Cache Size in DBCC PROCCACHE. Since I get the same number, I
figure it's the same unit.
HTH
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com
"arthur" <alangham@.gmail.com> wrote in message
news:1106249027.345721.114590@.f14g2000cwb.googlegr oups.com...
> how'd you figure that out? i'm trying to reconcile the performance
> monitor's sqlserver:cache manager procedure plans w/ the results from
> dbcc proccache. basically what i eventually need to find out is how
> much space there is total in the procedure cache.
> thanks,
> arthur
>

dbcc proccache units

what are the units for proc cache size? i'm assuming they're in kb and
want to make sure. thanks,
arthurIt seems to be pages.
--
HTH
--
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com
"arthur" <alangham@.gmail.com> wrote in message
news:1106239956.487457.76280@.z14g2000cwz.googlegroups.com...
> what are the units for proc cache size? i'm assuming they're in kb and
> want to make sure. thanks,
> arthur
>|||how'd you figure that out? i'm trying to reconcile the performance
monitor's sqlserver:cache manager procedure plans w/ the results from
dbcc proccache. basically what i eventually need to find out is how
much space there is total in the procedure cache.
thanks,
arthur|||You can also run DBCC MEMORYSTATUS.
When I do that, I get the same value for Procedure Cache, Total Pages as I
do for Proc Cache Size in DBCC PROCCACHE. Since I get the same number, I
figure it's the same unit.
--
HTH
--
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com
"arthur" <alangham@.gmail.com> wrote in message
news:1106249027.345721.114590@.f14g2000cwb.googlegroups.com...
> how'd you figure that out? i'm trying to reconcile the performance
> monitor's sqlserver:cache manager procedure plans w/ the results from
> dbcc proccache. basically what i eventually need to find out is how
> much space there is total in the procedure cache.
> thanks,
> arthur
>