Data Flow Task Problem: shrinkfile

Showing posts with label shrinkfile. Show all posts

Tuesday, March 27, 2012

Data File Does Not Shrink! (SQL2K)

Hi,
I have a database that its data file is almost 15GB. When I go to shrink
file page (EM), the page displays that the minimum file size can be 8GB but
after shrink, the size doesn't change at all! I tried both EM and DBCC
SHRINKFILE and the result is the same(the process takes 10 minutes)! It
might help if I say that the complete backup is about 9GB, but after restore
it's 15GB.
Any help would be greatly appreciated.
Leila
have you tried shrinking the files individually? To do this go to
shrink database, then click on the files... button and select the data
file you want to shrink.
Leila wrote:
> Hi,
> I have a database that its data file is almost 15GB. When I go to shrink
> file page (EM), the page displays that the minimum file size can be 8GB but
> after shrink, the size doesn't change at all! I tried both EM and DBCC
> SHRINKFILE and the result is the same(the process takes 10 minutes)! It
> might help if I say that the complete backup is about 9GB, but after restore
> it's 15GB.
> Any help would be greatly appreciated.
> Leila
|||Thanks,
I explained it exactly in my previous post!
"Someone Else" <someone_else_of_course@.hotmail.co.uk> wrote in message
news:1163509708.111473.245080@.b28g2000cwb.googlegr oups.com...
> have you tried shrinking the files individually? To do this go to
> shrink database, then click on the files... button and select the data
> file you want to shrink.
> Leila wrote:
>
|||you were less exact with the line "any help would be greatly
appreciated"
anyway, I was just trying to check the basics, people often confuse the
shrink database option and shrink file option.
things you could try (I don't know if they make a difference, but I
tend to clutch at straws when this happens to me and it usually works
in the end) would be clearing all connections to your db (not always an
option), checking to see what locks are held, if it's an option you
could even try detaching and re-attaching.
I believe the backup will restore with the file sizes it originally was
backed up with, so if you are restoring from a backup that had a lot of
free space when backed up, it will claim that space again upon restore.
Leila wrote:[vbcol=seagreen]
> Thanks,
> I explained it exactly in my previous post!
>
> "Someone Else" <someone_else_of_course@.hotmail.co.uk> wrote in message
> news:1163509708.111473.245080@.b28g2000cwb.googlegr oups.com...
|||Try:
dbcc shrinkfile(1,9000)
... explicitly specify the size (in MB)
"Leila" wrote:

> Hi,
> I have a database that its data file is almost 15GB. When I go to shrink
> file page (EM), the page displays that the minimum file size can be 8GB but
> after shrink, the size doesn't change at all! I tried both EM and DBCC
> SHRINKFILE and the result is the same(the process takes 10 minutes)! It
> might help if I say that the complete backup is about 9GB, but after restore
> it's 15GB.
> Any help would be greatly appreciated.
> Leila
>
>
|||Leila,
The situation you are reporting, is relacionated with the DB Transaction Log.
If you are not doing any log shipping, or transaction log backups, run the
Following:
Backup Log DatabaseName with truncate_only
This will truncate the inactive part of the Trn Log.
Best Regards,
Paulo Conde?a
"Leila" wrote:

> Hi,
> I have a database that its data file is almost 15GB. When I go to shrink
> file page (EM), the page displays that the minimum file size can be 8GB but
> after shrink, the size doesn't change at all! I tried both EM and DBCC
> SHRINKFILE and the result is the same(the process takes 10 minutes)! It
> might help if I say that the complete backup is about 9GB, but after restore
> it's 15GB.
> Any help would be greatly appreciated.
> Leila
>
>
|||Leila
Let's start with classic question , why would you want to shrink the file at
all?
Have you read this article?
http://www.karaszi.com/SQLServer/info_dont_shrink.asp
DBCC SHRINKFILE does not shrink a file past the size needed to store the
data. If you have 50 percent of the pagaes in a 10 MB data file are used ,a
DBCC SHRINKFILE statement with a target_size of 6 shrinks the file to only
5GB/MB not 6GB/MB
"Leila" <Leilas@.hotpop.com> wrote in message
news:uYimey%23BHHA.2328@.TK2MSFTNGP02.phx.gbl...
> Hi,
> I have a database that its data file is almost 15GB. When I go to shrink
> file page (EM), the page displays that the minimum file size can be 8GB
> but after shrink, the size doesn't change at all! I tried both EM and DBCC
> SHRINKFILE and the result is the same(the process takes 10 minutes)! It
> might help if I say that the complete backup is about 9GB, but after
> restore it's 15GB.
> Any help would be greatly appreciated.
> Leila
>
|||Thanks,
I exactly tried it, but no change occured!
"F_clef" <Fclef@.discussions.microsoft.com> wrote in message
news:A2C58C00-D254-496F-ABFC-B1C7DB76EBDF@.microsoft.com...[vbcol=seagreen]
> Try:
> dbcc shrinkfile(1,9000)
> ... explicitly specify the size (in MB)
> "Leila" wrote:
|||Thanks,
The log file almost always is 30MB! (Simple recovery model is being used)
"Paulo Condea" <PauloCondea@.discussions.microsoft.com> wrote in message
news:B2BF958E-CE71-4DD6-8570-DA5EB3EF47F0@.microsoft.com...[vbcol=seagreen]
> Leila,
> The situation you are reporting, is relacionated with the DB Transaction
> Log.
> If you are not doing any log shipping, or transaction log backups, run the
> Following:
> Backup Log DatabaseName with truncate_only
> This will truncate the inactive part of the Trn Log.
> Best Regards,
> Paulo Condea
> "Leila" wrote:
|||Thanks Uri,
Actually there was a big table in db (about 5GB). At the end of year, the
data of this table was moved to other db (kind of archive). The admin
dropped and recreated this table in first db. After some time, this table
became large again. sp_SpaceUsed shows that this table has 3GB reserved
space, 500MB is data and almost 2.5GB is unused! I'm curious to free this
2.5GB space.
I must mention that DBCC CHECKDB returns error for 4 pages near eachother.
But the ObjectID the it shows does not exists in sysobjects and Object_Name
function returns NULL for it!
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:edCoPQ$BHHA.3228@.TK2MSFTNGP03.phx.gbl...
> Leila
> Let's start with classic question , why would you want to shrink the file
> at all?
> Have you read this article?
> http://www.karaszi.com/SQLServer/info_dont_shrink.asp
>
> DBCC SHRINKFILE does not shrink a file past the size needed to store the
> data. If you have 50 percent of the pagaes in a 10 MB data file are used
> ,a DBCC SHRINKFILE statement with a target_size of 6 shrinks the file to
> only 5GB/MB not 6GB/MB
>
>
> "Leila" <Leilas@.hotpop.com> wrote in message
> news:uYimey%23BHHA.2328@.TK2MSFTNGP02.phx.gbl...
>

Data File Does Not Shrink! (SQL2K)

Hi,
I have a database that its data file is almost 15GB. When I go to shrink
file page (EM), the page displays that the minimum file size can be 8GB but
after shrink, the size doesn't change at all! I tried both EM and DBCC
SHRINKFILE and the result is the same(the process takes 10 minutes)! It
might help if I say that the complete backup is about 9GB, but after restore
it's 15GB.
Any help would be greatly appreciated.
Leilahave you tried shrinking the files individually? To do this go to
shrink database, then click on the files... button and select the data
file you want to shrink.
Leila wrote:
> Hi,
> I have a database that its data file is almost 15GB. When I go to shrink
> file page (EM), the page displays that the minimum file size can be 8GB bu
t
> after shrink, the size doesn't change at all! I tried both EM and DBCC
> SHRINKFILE and the result is the same(the process takes 10 minutes)! It
> might help if I say that the complete backup is about 9GB, but after resto
re
> it's 15GB.
> Any help would be greatly appreciated.
> Leila|||Thanks,
I explained it exactly in my previous post!
"Someone Else" <someone_else_of_course@.hotmail.co.uk> wrote in message
news:1163509708.111473.245080@.b28g2000cwb.googlegroups.com...
> have you tried shrinking the files individually? To do this go to
> shrink database, then click on the files... button and select the data
> file you want to shrink.
> Leila wrote:
>|||you were less exact with the line "any help would be greatly
appreciated"
anyway, I was just trying to check the basics, people often confuse the
shrink database option and shrink file option.
things you could try (I don't know if they make a difference, but I
tend to clutch at straws when this happens to me and it usually works
in the end) would be clearing all connections to your db (not always an
option), checking to see what locks are held, if it's an option you
could even try detaching and re-attaching.
I believe the backup will restore with the file sizes it originally was
backed up with, so if you are restoring from a backup that had a lot of
free space when backed up, it will claim that space again upon restore.
Leila wrote:[vbcol=seagreen]
> Thanks,
> I explained it exactly in my previous post!
>
> "Someone Else" <someone_else_of_course@.hotmail.co.uk> wrote in message
> news:1163509708.111473.245080@.b28g2000cwb.googlegroups.com...|||Try:
dbcc shrinkfile(1,9000)
... explicitly specify the size (in MB)
"Leila" wrote:

> Hi,
> I have a database that its data file is almost 15GB. When I go to shrink
> file page (EM), the page displays that the minimum file size can be 8GB bu
t
> after shrink, the size doesn't change at all! I tried both EM and DBCC
> SHRINKFILE and the result is the same(the process takes 10 minutes)! It
> might help if I say that the complete backup is about 9GB, but after resto
re
> it's 15GB.
> Any help would be greatly appreciated.
> Leila
>
>|||Leila,
The situation you are reporting, is relacionated with the DB Transaction Log
.
If you are not doing any log shipping, or transaction log backups, run the
Following:
Backup Log DatabaseName with truncate_only
This will truncate the inactive part of the Trn Log.
Best Regards,
Paulo Conde?a
"Leila" wrote:

> Hi,
> I have a database that its data file is almost 15GB. When I go to shrink
> file page (EM), the page displays that the minimum file size can be 8GB bu
t
> after shrink, the size doesn't change at all! I tried both EM and DBCC
> SHRINKFILE and the result is the same(the process takes 10 minutes)! It
> might help if I say that the complete backup is about 9GB, but after resto
re
> it's 15GB.
> Any help would be greatly appreciated.
> Leila
>
>|||Leila
Let's start with classic question , why would you want to shrink the file at
all?
Have you read this article?
http://www.karaszi.com/SQLServer/info_dont_shrink.asp
DBCC SHRINKFILE does not shrink a file past the size needed to store the
data. If you have 50 percent of the pagaes in a 10 MB data file are used ,a
DBCC SHRINKFILE statement with a target_size of 6 shrinks the file to only
5GB/MB not 6GB/MB
"Leila" <Leilas@.hotpop.com> wrote in message
news:uYimey%23BHHA.2328@.TK2MSFTNGP02.phx.gbl...
> Hi,
> I have a database that its data file is almost 15GB. When I go to shrink
> file page (EM), the page displays that the minimum file size can be 8GB
> but after shrink, the size doesn't change at all! I tried both EM and DBCC
> SHRINKFILE and the result is the same(the process takes 10 minutes)! It
> might help if I say that the complete backup is about 9GB, but after
> restore it's 15GB.
> Any help would be greatly appreciated.
> Leila
>|||Thanks,
I exactly tried it, but no change occured!
"F_clef" <Fclef@.discussions.microsoft.com> wrote in message
news:A2C58C00-D254-496F-ABFC-B1C7DB76EBDF@.microsoft.com...[vbcol=seagreen]
> Try:
> dbcc shrinkfile(1,9000)
> ... explicitly specify the size (in MB)
> "Leila" wrote:
>|||Thanks,
The log file almost always is 30MB! (Simple recovery model is being used)
"Paulo Condea" <PauloCondea@.discussions.microsoft.com> wrote in message
news:B2BF958E-CE71-4DD6-8570-DA5EB3EF47F0@.microsoft.com...[vbcol=seagreen]
> Leila,
> The situation you are reporting, is relacionated with the DB Transaction
> Log.
> If you are not doing any log shipping, or transaction log backups, run the
> Following:
> Backup Log DatabaseName with truncate_only
> This will truncate the inactive part of the Trn Log.
> Best Regards,
> Paulo Condea
> "Leila" wrote:
>|||Thanks Uri,
Actually there was a big table in db (about 5GB). At the end of year, the
data of this table was moved to other db (kind of archive). The admin
dropped and recreated this table in first db. After some time, this table
became large again. sp_SpaceUsed shows that this table has 3GB reserved
space, 500MB is data and almost 2.5GB is unused! I'm curious to free this
2.5GB space.
I must mention that DBCC CHECKDB returns error for 4 pages near eachother.
But the ObjectID the it shows does not exists in sysobjects and Object_Name
function returns NULL for it!
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:edCoPQ$BHHA.3228@.TK2MSFTNGP03.phx.gbl...
> Leila
> Let's start with classic question , why would you want to shrink the file
> at all?
> Have you read this article?
> http://www.karaszi.com/SQLServer/info_dont_shrink.asp
>
> DBCC SHRINKFILE does not shrink a file past the size needed to store the
> data. If you have 50 percent of the pagaes in a 10 MB data file are used
> ,a DBCC SHRINKFILE statement with a target_size of 6 shrinks the file to
> only 5GB/MB not 6GB/MB
>
>
> "Leila" <Leilas@.hotpop.com> wrote in message
> news:uYimey%23BHHA.2328@.TK2MSFTNGP02.phx.gbl...
>

Tuesday, February 14, 2012

cut the size of the log

I use this command but it doesnot work
cannot cut the size of the log to 2MB
DBCC SHRINKFILE(ABC_log,2)
what is the correct command actually pls?
actually i want to cut the size
because it is testing environment
"izumi" <test@.test.com> wrote in message
news:%23VPGv%23KXEHA.2408@.tk2msftngp13.phx.gbl...
> I use this command but it doesnot work
> cannot cut the size of the log to 2MB
> DBCC SHRINKFILE(ABC_log,2)
> what is the correct command actually pls?
>
|||If you are using a FULL or BULK LOGGED recovery model (SELECT
SERVERPROPERTYEX ('<db name>', 'RECOVERY), you can't shrink the log file
unless you backup the transaction log or truncate it. If you are using the
SIMPLE recovery model, you can't shrink it much if there are open
transactions that happen to occupy the end of the file.
Peter Yeoh
http://www.yohz.com
Need smaller SQL2K backup files? Try MiniSQLBackup
"izumi" <test@.test.com> wrote in message
news:%23VPGv%23KXEHA.2408@.tk2msftngp13.phx.gbl...
> I use this command but it doesnot work
> cannot cut the size of the log to 2MB
> DBCC SHRINKFILE(ABC_log,2)
> what is the correct command actually pls?
>
|||http://www.nigelrivett.net/Transacti...leGrows_1.html
"izumi" wrote:

> actually i want to cut the size
> because it is testing environment
> "izumi" <test@.test.com> wrote in message
> news:%23VPGv%23KXEHA.2408@.tk2msftngp13.phx.gbl...
>
>
|||finnaly I offline the database
go to physical path and delete the abc_log
then get back much more disk space
"Peter Yeoh" <nospam@.nospam.com> wrote in message
news:%23IsFtKMXEHA.3988@.tk2msftngp13.phx.gbl...
> If you are using a FULL or BULK LOGGED recovery model (SELECT
> SERVERPROPERTYEX ('<db name>', 'RECOVERY), you can't shrink the log file
> unless you backup the transaction log or truncate it. If you are using
the
> SIMPLE recovery model, you can't shrink it much if there are open
> transactions that happen to occupy the end of the file.
> Peter Yeoh
> http://www.yohz.com
> Need smaller SQL2K backup files? Try MiniSQLBackup
>
> "izumi" <test@.test.com> wrote in message
> news:%23VPGv%23KXEHA.2408@.tk2msftngp13.phx.gbl...
>
|||I hope you did a backup before. This is not a safe operation, and you should consider yourself lucky
if the database isn't corrupt after the operation.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"izumi" <test@.test.com> wrote in message news:e2z8mbMXEHA.644@.tk2msftngp13.phx.gbl...
> finnaly I offline the database
> go to physical path and delete the abc_log
> then get back much more disk space
> "Peter Yeoh" <nospam@.nospam.com> wrote in message
> news:%23IsFtKMXEHA.3988@.tk2msftngp13.phx.gbl...
> the
>
|||Yes i agree your point....
But this time it is testing environment...
so it is ok
next time, if it is live, i will backup
Thanks d><b
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:e9I4kyNXEHA.2940@.TK2MSFTNGP09.phx.gbl...
> I hope you did a backup before. This is not a safe operation, and you
should consider yourself lucky
> if the database isn't corrupt after the operation.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "izumi" <test@.test.com> wrote in message
news:e2z8mbMXEHA.644@.tk2msftngp13.phx.gbl...[vbcol=seagreen]
file[vbcol=seagreen]
using
>
|||Hi Izumi
What does 'does not work' mean? Do you get an error message? If so, what
error? Do you get any response at all? What does it say?
What version of SQL Server are you running?
Please give us more information.
HTH
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com
"izumi" <test@.test.com> wrote in message
news:#VPGv#KXEHA.2408@.tk2msftngp13.phx.gbl...
> I use this command but it doesnot work
> cannot cut the size of the log to 2MB
> DBCC SHRINKFILE(ABC_log,2)
> what is the correct command actually pls?
>
|||I also had a hard time with this problem. If it's a testing environment or you just don't care about being able to recover to a point in time, change the recovery model to Simple and then issue the following command in SQL Query Analyzer.
SHRINKFILE (logfilename_log, -1)
In the simple recovery model, a sequence of transaction logs is not being Maintained. All log records before the MinLSN can be truncated at any time, except while a BACKUP statement is being processed. The log file will shrink down to about 1mb.
"Kalen Delaney" wrote:

> Hi Izumi
> What does 'does not work' mean? Do you get an error message? If so, what
> error? Do you get any response at all? What does it say?
> What version of SQL Server are you running?
> Please give us more information.
> --
> HTH
> --
> Kalen Delaney
> SQL Server MVP
> www.SolidQualityLearning.com
>
> "izumi" <test@.test.com> wrote in message
> news:#VPGv#KXEHA.2408@.tk2msftngp13.phx.gbl...
>
>
|||I also had a hard time with this problem. If it's a testing environment or you just don't care about being able to recover to a point in time, change the recovery model to Simple and then issue the following command in SQL Query Analyzer.
SHRINKFILE (logfilename_log, -1)
In the simple recovery model, a sequence of transaction logs is not being Maintained. All log records before the MinLSN can be truncated at any time, except while a BACKUP statement is being processed. The log file will shrink down to about 1mb.
"Kalen Delaney" wrote:

> Hi Izumi
> What does 'does not work' mean? Do you get an error message? If so, what
> error? Do you get any response at all? What does it say?
> What version of SQL Server are you running?
> Please give us more information.
> --
> HTH
> --
> Kalen Delaney
> SQL Server MVP
> www.SolidQualityLearning.com
>
> "izumi" <test@.test.com> wrote in message
> news:#VPGv#KXEHA.2408@.tk2msftngp13.phx.gbl...
>
>

cut the size of the log

I use this command but it doesnot work
cannot cut the size of the log to 2MB
DBCC SHRINKFILE(ABC_log,2)
what is the correct command actually pls?actually i want to cut the size
because it is testing environment
"izumi" <test@.test.com> wrote in message
news:%23VPGv%23KXEHA.2408@.tk2msftngp13.phx.gbl...
> I use this command but it doesnot work
> cannot cut the size of the log to 2MB
> DBCC SHRINKFILE(ABC_log,2)
> what is the correct command actually pls?
>|||If you are using a FULL or BULK LOGGED recovery model (SELECT
SERVERPROPERTYEX ('<db name>', 'RECOVERY), you can't shrink the log file
unless you backup the transaction log or truncate it. If you are using the
SIMPLE recovery model, you can't shrink it much if there are open
transactions that happen to occupy the end of the file.
Peter Yeoh
http://www.yohz.com
Need smaller SQL2K backup files? Try MiniSQLBackup
"izumi" <test@.test.com> wrote in message
news:%23VPGv%23KXEHA.2408@.tk2msftngp13.phx.gbl...
> I use this command but it doesnot work
> cannot cut the size of the log to 2MB
> DBCC SHRINKFILE(ABC_log,2)
> what is the correct command actually pls?
>|||http://www.nigelrivett.net/TransactionLogFileGrows_1.html
"izumi" wrote:
> actually i want to cut the size
> because it is testing environment
> "izumi" <test@.test.com> wrote in message
> news:%23VPGv%23KXEHA.2408@.tk2msftngp13.phx.gbl...
> > I use this command but it doesnot work
> >
> > cannot cut the size of the log to 2MB
> >
> > DBCC SHRINKFILE(ABC_log,2)
> >
> > what is the correct command actually pls?
> >
> >
>
>|||finnaly I offline the database
go to physical path and delete the abc_log
then get back much more disk space
"Peter Yeoh" <nospam@.nospam.com> wrote in message
news:%23IsFtKMXEHA.3988@.tk2msftngp13.phx.gbl...
> If you are using a FULL or BULK LOGGED recovery model (SELECT
> SERVERPROPERTYEX ('<db name>', 'RECOVERY), you can't shrink the log file
> unless you backup the transaction log or truncate it. If you are using
the
> SIMPLE recovery model, you can't shrink it much if there are open
> transactions that happen to occupy the end of the file.
> Peter Yeoh
> http://www.yohz.com
> Need smaller SQL2K backup files? Try MiniSQLBackup
>
> "izumi" <test@.test.com> wrote in message
> news:%23VPGv%23KXEHA.2408@.tk2msftngp13.phx.gbl...
> > I use this command but it doesnot work
> >
> > cannot cut the size of the log to 2MB
> >
> > DBCC SHRINKFILE(ABC_log,2)
> >
> > what is the correct command actually pls?
> >
> >
>|||I hope you did a backup before. This is not a safe operation, and you should consider yourself lucky
if the database isn't corrupt after the operation.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"izumi" <test@.test.com> wrote in message news:e2z8mbMXEHA.644@.tk2msftngp13.phx.gbl...
> finnaly I offline the database
> go to physical path and delete the abc_log
> then get back much more disk space
> "Peter Yeoh" <nospam@.nospam.com> wrote in message
> news:%23IsFtKMXEHA.3988@.tk2msftngp13.phx.gbl...
> > If you are using a FULL or BULK LOGGED recovery model (SELECT
> > SERVERPROPERTYEX ('<db name>', 'RECOVERY), you can't shrink the log file
> > unless you backup the transaction log or truncate it. If you are using
> the
> > SIMPLE recovery model, you can't shrink it much if there are open
> > transactions that happen to occupy the end of the file.
> >
> > Peter Yeoh
> > http://www.yohz.com
> > Need smaller SQL2K backup files? Try MiniSQLBackup
> >
> >
> > "izumi" <test@.test.com> wrote in message
> > news:%23VPGv%23KXEHA.2408@.tk2msftngp13.phx.gbl...
> > > I use this command but it doesnot work
> > >
> > > cannot cut the size of the log to 2MB
> > >
> > > DBCC SHRINKFILE(ABC_log,2)
> > >
> > > what is the correct command actually pls?
> > >
> > >
> >
> >
>|||Yes i agree your point....
But this time it is testing environment...
so it is ok
next time, if it is live, i will backup
Thanks d><b
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:e9I4kyNXEHA.2940@.TK2MSFTNGP09.phx.gbl...
> I hope you did a backup before. This is not a safe operation, and you
should consider yourself lucky
> if the database isn't corrupt after the operation.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "izumi" <test@.test.com> wrote in message
news:e2z8mbMXEHA.644@.tk2msftngp13.phx.gbl...
> > finnaly I offline the database
> > go to physical path and delete the abc_log
> >
> > then get back much more disk space
> > "Peter Yeoh" <nospam@.nospam.com> wrote in message
> > news:%23IsFtKMXEHA.3988@.tk2msftngp13.phx.gbl...
> > > If you are using a FULL or BULK LOGGED recovery model (SELECT
> > > SERVERPROPERTYEX ('<db name>', 'RECOVERY), you can't shrink the log
file
> > > unless you backup the transaction log or truncate it. If you are
using
> > the
> > > SIMPLE recovery model, you can't shrink it much if there are open
> > > transactions that happen to occupy the end of the file.
> > >
> > > Peter Yeoh
> > > http://www.yohz.com
> > > Need smaller SQL2K backup files? Try MiniSQLBackup
> > >
> > >
> > > "izumi" <test@.test.com> wrote in message
> > > news:%23VPGv%23KXEHA.2408@.tk2msftngp13.phx.gbl...
> > > > I use this command but it doesnot work
> > > >
> > > > cannot cut the size of the log to 2MB
> > > >
> > > > DBCC SHRINKFILE(ABC_log,2)
> > > >
> > > > what is the correct command actually pls?
> > > >
> > > >
> > >
> > >
> >
> >
>|||Hi Izumi
What does 'does not work' mean? Do you get an error message? If so, what
error? Do you get any response at all? What does it say?
What version of SQL Server are you running?
Please give us more information.
--
HTH
--
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com
"izumi" <test@.test.com> wrote in message
news:#VPGv#KXEHA.2408@.tk2msftngp13.phx.gbl...
> I use this command but it doesnot work
> cannot cut the size of the log to 2MB
> DBCC SHRINKFILE(ABC_log,2)
> what is the correct command actually pls?
>|||it means the the size of the log is kept unchanged
"Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
news:O98aR8QXEHA.1684@.tk2msftngp13.phx.gbl...
> Hi Izumi
> What does 'does not work' mean? Do you get an error message? If so, what
> error? Do you get any response at all? What does it say?
> What version of SQL Server are you running?
> Please give us more information.
> --
> HTH
> --
> Kalen Delaney
> SQL Server MVP
> www.SolidQualityLearning.com
>
> "izumi" <test@.test.com> wrote in message
> news:#VPGv#KXEHA.2408@.tk2msftngp13.phx.gbl...
> > I use this command but it doesnot work
> >
> > cannot cut the size of the log to 2MB
> >
> > DBCC SHRINKFILE(ABC_log,2)
> >
> > what is the correct command actually pls?
> >
> >
>|||There are some links to articles regarding shrink of files for tlog at
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/
"izumi" <test@.test.com> wrote in message news:OeFMDCbXEHA.2940@.TK2MSFTNGP09.phx.gbl...
> it means the the size of the log is kept unchanged
> "Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
> news:O98aR8QXEHA.1684@.tk2msftngp13.phx.gbl...
> > Hi Izumi
> >
> > What does 'does not work' mean? Do you get an error message? If so, what
> > error? Do you get any response at all? What does it say?
> >
> > What version of SQL Server are you running?
> >
> > Please give us more information.
> >
> > --
> > HTH
> > --
> > Kalen Delaney
> > SQL Server MVP
> > www.SolidQualityLearning.com
> >
> >
> > "izumi" <test@.test.com> wrote in message
> > news:#VPGv#KXEHA.2408@.tk2msftngp13.phx.gbl...
> > > I use this command but it doesnot work
> > >
> > > cannot cut the size of the log to 2MB
> > >
> > > DBCC SHRINKFILE(ABC_log,2)
> > >
> > > what is the correct command actually pls?
> > >
> > >
> >
> >
>

Tuesday, March 27, 2012

Data File Does Not Shrink! (SQL2K)

Data File Does Not Shrink! (SQL2K)

Tuesday, February 14, 2012

cut the size of the log

cut the size of the log

cut the size of the log

Data Flow Task Problem

Blog Archive

About Me