I have recently changed the indexes ina large table in our database. I have
a lot of excess unused space ,approx 64GB, in the .MDF and want to give it
back to Windows 200 Server.
Our .MDF is not in the SQL DATA directory it is on another volume.
Is there a way to specify a path to another volume when using DBCC SHRINKFILE?
Right now I am forced to use DBCC SHRINKDATABASE and it is taking a long time.
TIA,
-BenDBCC SHRINKFILE doesn't care where the file is as it uses the logical
filename. There is no reason you need to use SHRINKDATABASE.
--
Andrew J. Kelly SQL MVP
"Ben" <Ben@.discussions.microsoft.com> wrote in message
news:33AB1ADE-1D27-45F0-A054-B6F4B5F88727@.microsoft.com...
>I have recently changed the indexes ina large table in our database. I
>have
> a lot of excess unused space ,approx 64GB, in the .MDF and want to give
> it
> back to Windows 200 Server.
> Our .MDF is not in the SQL DATA directory it is on another volume.
> Is there a way to specify a path to another volume when using DBCC
> SHRINKFILE?
> Right now I am forced to use DBCC SHRINKDATABASE and it is taking a long
> time.
> TIA,
> -Ben
Showing posts with label mdf. Show all posts
Showing posts with label mdf. Show all posts
Thursday, March 29, 2012
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
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
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
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
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
I have recently changed the indexes ina large table in our database. I have
a lot of excess unused space ,approx 64GB, in the .MDF and want to give it
back to Windows 200 Server.
Our .MDF is not in the SQL DATA directory it is on another volume.
Is there a way to specify a path to another volume when using DBCC SHRINKFILE?
Right now I am forced to use DBCC SHRINKDATABASE and it is taking a long time.
TIA,
-Ben
DBCC SHRINKFILE doesn't care where the file is as it uses the logical
filename. There is no reason you need to use SHRINKDATABASE.
Andrew J. Kelly SQL MVP
"Ben" <Ben@.discussions.microsoft.com> wrote in message
news:33AB1ADE-1D27-45F0-A054-B6F4B5F88727@.microsoft.com...
>I have recently changed the indexes ina large table in our database. I
>have
> a lot of excess unused space ,approx 64GB, in the .MDF and want to give
> it
> back to Windows 200 Server.
> Our .MDF is not in the SQL DATA directory it is on another volume.
> Is there a way to specify a path to another volume when using DBCC
> SHRINKFILE?
> Right now I am forced to use DBCC SHRINKDATABASE and it is taking a long
> time.
> TIA,
> -Ben
a lot of excess unused space ,approx 64GB, in the .MDF and want to give it
back to Windows 200 Server.
Our .MDF is not in the SQL DATA directory it is on another volume.
Is there a way to specify a path to another volume when using DBCC SHRINKFILE?
Right now I am forced to use DBCC SHRINKDATABASE and it is taking a long time.
TIA,
-Ben
DBCC SHRINKFILE doesn't care where the file is as it uses the logical
filename. There is no reason you need to use SHRINKDATABASE.
Andrew J. Kelly SQL MVP
"Ben" <Ben@.discussions.microsoft.com> wrote in message
news:33AB1ADE-1D27-45F0-A054-B6F4B5F88727@.microsoft.com...
>I have recently changed the indexes ina large table in our database. I
>have
> a lot of excess unused space ,approx 64GB, in the .MDF and want to give
> it
> back to Windows 200 Server.
> Our .MDF is not in the SQL DATA directory it is on another volume.
> Is there a way to specify a path to another volume when using DBCC
> SHRINKFILE?
> Right now I am forced to use DBCC SHRINKDATABASE and it is taking a long
> time.
> TIA,
> -Ben
DBCC SHRINKFILE
I have recently changed the indexes ina large table in our database. I have
a lot of excess unused space ,approx 64GB, in the .MDF and want to give it
back to Windows 200 Server.
Our .MDF is not in the SQL DATA directory it is on another volume.
Is there a way to specify a path to another volume when using DBCC SHRINKFIL
E?
Right now I am forced to use DBCC SHRINKDATABASE and it is taking a long tim
e.
TIA,
-BenDBCC SHRINKFILE doesn't care where the file is as it uses the logical
filename. There is no reason you need to use SHRINKDATABASE.
Andrew J. Kelly SQL MVP
"Ben" <Ben@.discussions.microsoft.com> wrote in message
news:33AB1ADE-1D27-45F0-A054-B6F4B5F88727@.microsoft.com...
>I have recently changed the indexes ina large table in our database. I
>have
> a lot of excess unused space ,approx 64GB, in the .MDF and want to give
> it
> back to Windows 200 Server.
> Our .MDF is not in the SQL DATA directory it is on another volume.
> Is there a way to specify a path to another volume when using DBCC
> SHRINKFILE?
> Right now I am forced to use DBCC SHRINKDATABASE and it is taking a long
> time.
> TIA,
> -Ben
a lot of excess unused space ,approx 64GB, in the .MDF and want to give it
back to Windows 200 Server.
Our .MDF is not in the SQL DATA directory it is on another volume.
Is there a way to specify a path to another volume when using DBCC SHRINKFIL
E?
Right now I am forced to use DBCC SHRINKDATABASE and it is taking a long tim
e.
TIA,
-BenDBCC SHRINKFILE doesn't care where the file is as it uses the logical
filename. There is no reason you need to use SHRINKDATABASE.
Andrew J. Kelly SQL MVP
"Ben" <Ben@.discussions.microsoft.com> wrote in message
news:33AB1ADE-1D27-45F0-A054-B6F4B5F88727@.microsoft.com...
>I have recently changed the indexes ina large table in our database. I
>have
> a lot of excess unused space ,approx 64GB, in the .MDF and want to give
> it
> back to Windows 200 Server.
> Our .MDF is not in the SQL DATA directory it is on another volume.
> Is there a way to specify a path to another volume when using DBCC
> SHRINKFILE?
> Right now I am forced to use DBCC SHRINKDATABASE and it is taking a long
> time.
> TIA,
> -Ben
Tuesday, March 27, 2012
DBCC Shrinkdatabase /shrinkfile not shrinking my database
I have 137GB of database (mdf). it is showing on database properties
that i still have about 63Gb available. If i go to EM-shrink file, i
can have a minimum of 65GB which i guess is my actual data. But when i
shrink it from there or from Query Analyzer, it still remains 137Gb.
I even tried doing a dbcc shrinkfile to 110GB, but still it didn't
work.
can anybody help pls.
TIA
JorizJoriz,
Just to clarify, this is the .mdf file and not the .ldf file that you're
trying to shrink correct? What are you specifying for the targetpercent in
the DBCC SHRINKFILE statement? Do you have exclusive access to the database
right now?
HTH
Jerry
<google@.joriz.is-a-geek.net> wrote in message
news:1129585565.049054.249070@.g44g2000cwa.googlegroups.com...
>
> I have 137GB of database (mdf). it is showing on database properties
> that i still have about 63Gb available. If i go to EM-shrink file, i
> can have a minimum of 65GB which i guess is my actual data. But when i
> shrink it from there or from Query Analyzer, it still remains 137Gb.
> I even tried doing a dbcc shrinkfile to 110GB, but still it didn't
> work.
> can anybody help pls.
> TIA
> Joriz
>|||Hi Joriz,
If u want to shrink the mdf file then ui have to run srink database
command.
But in most cases that doesnt work.
The process u are following works in ldf files.
If u want to optimise ur logfile on disk.
u have to run this command
backup log database name with truncate_only
dbcc shrinkfile(logical name , size)
HTH
from
Doller
Jerry Spivey wrote:
> Joriz,
> Just to clarify, this is the .mdf file and not the .ldf file that you're
> trying to shrink correct? What are you specifying for the targetpercent in
> the DBCC SHRINKFILE statement? Do you have exclusive access to the database
> right now?
> HTH
> Jerry
> <google@.joriz.is-a-geek.net> wrote in message
> news:1129585565.049054.249070@.g44g2000cwa.googlegroups.com...
> >
> >
> > I have 137GB of database (mdf). it is showing on database properties
> > that i still have about 63Gb available. If i go to EM-shrink file, i
> > can have a minimum of 65GB which i guess is my actual data. But when i
> > shrink it from there or from Query Analyzer, it still remains 137Gb.
> > I even tried doing a dbcc shrinkfile to 110GB, but still it didn't
> > work.
> >
> > can anybody help pls.
> >
> > TIA
> >
> > Joriz
> >|||doller wrote:
> Hi Joriz,
> If u want to shrink the mdf file then ui have to run srink database
> command.
> But in most cases that doesnt work.
> The process u are following works in ldf files.
> If u want to optimise ur logfile on disk.
> u have to run this command
> backup log database name with truncate_only
> dbcc shrinkfile(logical name , size)
> HTH
> from
> Doller
>
> Jerry Spivey wrote:
>> Joriz,
>> Just to clarify, this is the .mdf file and not the .ldf file that you're
>> trying to shrink correct? What are you specifying for the targetpercent in
>> the DBCC SHRINKFILE statement? Do you have exclusive access to the database
>> right now?
>> HTH
>> Jerry
>> <google@.joriz.is-a-geek.net> wrote in message
>> news:1129585565.049054.249070@.g44g2000cwa.googlegroups.com...
>> I have 137GB of database (mdf). it is showing on database properties
>> that i still have about 63Gb available. If i go to EM-shrink file, i
>> can have a minimum of 65GB which i guess is my actual data. But when i
>> shrink it from there or from Query Analyzer, it still remains 137Gb.
>> I even tried doing a dbcc shrinkfile to 110GB, but still it didn't
>> work.
>> can anybody help pls.
>> TIA
>> Joriz
>
Shrinking the database file doesn't require a SHRINK DATABASE - that can
be done with the DBCC SHRINKFILE command as well.
When you want to shrink your database file (.mdf) you can use the syntax
below :
USE YourDatabaseName
DBCC SHRINKFILE (LogicalDataFileName, TargetSize)
You can look up the command in Books On Line. If you need to get the
logical file names of your database, you can use sp_helpdb.
HTH
Regards
Steen
that i still have about 63Gb available. If i go to EM-shrink file, i
can have a minimum of 65GB which i guess is my actual data. But when i
shrink it from there or from Query Analyzer, it still remains 137Gb.
I even tried doing a dbcc shrinkfile to 110GB, but still it didn't
work.
can anybody help pls.
TIA
JorizJoriz,
Just to clarify, this is the .mdf file and not the .ldf file that you're
trying to shrink correct? What are you specifying for the targetpercent in
the DBCC SHRINKFILE statement? Do you have exclusive access to the database
right now?
HTH
Jerry
<google@.joriz.is-a-geek.net> wrote in message
news:1129585565.049054.249070@.g44g2000cwa.googlegroups.com...
>
> I have 137GB of database (mdf). it is showing on database properties
> that i still have about 63Gb available. If i go to EM-shrink file, i
> can have a minimum of 65GB which i guess is my actual data. But when i
> shrink it from there or from Query Analyzer, it still remains 137Gb.
> I even tried doing a dbcc shrinkfile to 110GB, but still it didn't
> work.
> can anybody help pls.
> TIA
> Joriz
>|||Hi Joriz,
If u want to shrink the mdf file then ui have to run srink database
command.
But in most cases that doesnt work.
The process u are following works in ldf files.
If u want to optimise ur logfile on disk.
u have to run this command
backup log database name with truncate_only
dbcc shrinkfile(logical name , size)
HTH
from
Doller
Jerry Spivey wrote:
> Joriz,
> Just to clarify, this is the .mdf file and not the .ldf file that you're
> trying to shrink correct? What are you specifying for the targetpercent in
> the DBCC SHRINKFILE statement? Do you have exclusive access to the database
> right now?
> HTH
> Jerry
> <google@.joriz.is-a-geek.net> wrote in message
> news:1129585565.049054.249070@.g44g2000cwa.googlegroups.com...
> >
> >
> > I have 137GB of database (mdf). it is showing on database properties
> > that i still have about 63Gb available. If i go to EM-shrink file, i
> > can have a minimum of 65GB which i guess is my actual data. But when i
> > shrink it from there or from Query Analyzer, it still remains 137Gb.
> > I even tried doing a dbcc shrinkfile to 110GB, but still it didn't
> > work.
> >
> > can anybody help pls.
> >
> > TIA
> >
> > Joriz
> >|||doller wrote:
> Hi Joriz,
> If u want to shrink the mdf file then ui have to run srink database
> command.
> But in most cases that doesnt work.
> The process u are following works in ldf files.
> If u want to optimise ur logfile on disk.
> u have to run this command
> backup log database name with truncate_only
> dbcc shrinkfile(logical name , size)
> HTH
> from
> Doller
>
> Jerry Spivey wrote:
>> Joriz,
>> Just to clarify, this is the .mdf file and not the .ldf file that you're
>> trying to shrink correct? What are you specifying for the targetpercent in
>> the DBCC SHRINKFILE statement? Do you have exclusive access to the database
>> right now?
>> HTH
>> Jerry
>> <google@.joriz.is-a-geek.net> wrote in message
>> news:1129585565.049054.249070@.g44g2000cwa.googlegroups.com...
>> I have 137GB of database (mdf). it is showing on database properties
>> that i still have about 63Gb available. If i go to EM-shrink file, i
>> can have a minimum of 65GB which i guess is my actual data. But when i
>> shrink it from there or from Query Analyzer, it still remains 137Gb.
>> I even tried doing a dbcc shrinkfile to 110GB, but still it didn't
>> work.
>> can anybody help pls.
>> TIA
>> Joriz
>
Shrinking the database file doesn't require a SHRINK DATABASE - that can
be done with the DBCC SHRINKFILE command as well.
When you want to shrink your database file (.mdf) you can use the syntax
below :
USE YourDatabaseName
DBCC SHRINKFILE (LogicalDataFileName, TargetSize)
You can look up the command in Books On Line. If you need to get the
logical file names of your database, you can use sp_helpdb.
HTH
Regards
Steen
DBCC Shrinkdatabase /shrinkfile not shrinking my database
I have 137GB of database (mdf). it is showing on database properties
that i still have about 63Gb available. If i go to EM-shrink file, i
can have a minimum of 65GB which i guess is my actual data. But when i
shrink it from there or from Query Analyzer, it still remains 137Gb.
I even tried doing a dbcc shrinkfile to 110GB, but still it didn't
work.
can anybody help pls.
TIA
JorizJoriz,
Just to clarify, this is the .mdf file and not the .ldf file that you're
trying to shrink correct? What are you specifying for the targetpercent in
the DBCC SHRINKFILE statement? Do you have exclusive access to the database
right now?
HTH
Jerry
<google@.joriz.is-a-geek.net> wrote in message
news:1129585565.049054.249070@.g44g2000cwa.googlegroups.com...
>
> I have 137GB of database (mdf). it is showing on database properties
> that i still have about 63Gb available. If i go to EM-shrink file, i
> can have a minimum of 65GB which i guess is my actual data. But when i
> shrink it from there or from Query Analyzer, it still remains 137Gb.
> I even tried doing a dbcc shrinkfile to 110GB, but still it didn't
> work.
> can anybody help pls.
> TIA
> Joriz
>
that i still have about 63Gb available. If i go to EM-shrink file, i
can have a minimum of 65GB which i guess is my actual data. But when i
shrink it from there or from Query Analyzer, it still remains 137Gb.
I even tried doing a dbcc shrinkfile to 110GB, but still it didn't
work.
can anybody help pls.
TIA
JorizJoriz,
Just to clarify, this is the .mdf file and not the .ldf file that you're
trying to shrink correct? What are you specifying for the targetpercent in
the DBCC SHRINKFILE statement? Do you have exclusive access to the database
right now?
HTH
Jerry
<google@.joriz.is-a-geek.net> wrote in message
news:1129585565.049054.249070@.g44g2000cwa.googlegroups.com...
>
> I have 137GB of database (mdf). it is showing on database properties
> that i still have about 63Gb available. If i go to EM-shrink file, i
> can have a minimum of 65GB which i guess is my actual data. But when i
> shrink it from there or from Query Analyzer, it still remains 137Gb.
> I even tried doing a dbcc shrinkfile to 110GB, but still it didn't
> work.
> can anybody help pls.
> TIA
> Joriz
>
DBCC Shrinkdatabase /shrinkfile not shrinking my database
I have 137GB of database (mdf). it is showing on database properties
that i still have about 63Gb available. If i go to EM-shrink file, i
can have a minimum of 65GB which i guess is my actual data. But when i
shrink it from there or from Query Analyzer, it still remains 137Gb.
I even tried doing a dbcc shrinkfile to 110GB, but still it didn't
work.
can anybody help pls.
TIA
Joriz
Joriz,
Just to clarify, this is the .mdf file and not the .ldf file that you're
trying to shrink correct? What are you specifying for the targetpercent in
the DBCC SHRINKFILE statement? Do you have exclusive access to the database
right now?
HTH
Jerry
<google@.joriz.is-a-geek.net> wrote in message
news:1129585565.049054.249070@.g44g2000cwa.googlegr oups.com...
>
> I have 137GB of database (mdf). it is showing on database properties
> that i still have about 63Gb available. If i go to EM-shrink file, i
> can have a minimum of 65GB which i guess is my actual data. But when i
> shrink it from there or from Query Analyzer, it still remains 137Gb.
> I even tried doing a dbcc shrinkfile to 110GB, but still it didn't
> work.
> can anybody help pls.
> TIA
> Joriz
>
that i still have about 63Gb available. If i go to EM-shrink file, i
can have a minimum of 65GB which i guess is my actual data. But when i
shrink it from there or from Query Analyzer, it still remains 137Gb.
I even tried doing a dbcc shrinkfile to 110GB, but still it didn't
work.
can anybody help pls.
TIA
Joriz
Joriz,
Just to clarify, this is the .mdf file and not the .ldf file that you're
trying to shrink correct? What are you specifying for the targetpercent in
the DBCC SHRINKFILE statement? Do you have exclusive access to the database
right now?
HTH
Jerry
<google@.joriz.is-a-geek.net> wrote in message
news:1129585565.049054.249070@.g44g2000cwa.googlegr oups.com...
>
> I have 137GB of database (mdf). it is showing on database properties
> that i still have about 63Gb available. If i go to EM-shrink file, i
> can have a minimum of 65GB which i guess is my actual data. But when i
> shrink it from there or from Query Analyzer, it still remains 137Gb.
> I even tried doing a dbcc shrinkfile to 110GB, but still it didn't
> work.
> can anybody help pls.
> TIA
> Joriz
>
Subscribe to:
Posts (Atom)