Shrink Log file does not seems to make the file size any smaller.
The file size remains the even after shrinking it a few time.
The GUI seems ez to use but I may be doing it wrongly.
How do I shink the Log file?
"TBoon" <allblacks15@.hotmail.com> wrote in message
news:esbf$KHIHHA.3676@.TK2MSFTNGP03.phx.gbl...
> Shrink Log file does not seems to make the file size any smaller.
> The file size remains the even after shrinking it a few time.
> The GUI seems ez to use but I may be doing it wrongly.
> How do I shink the Log file?
>
You can't shrink it if it's full of transactions. Perform a log backup to
copy the transactions out to a backup device, then you should be able to
shrink the transaction log. Then set up a maintence plan to perform
periodic transaction log backups to control the growth of the log file in
the future.
David
Showing posts with label shrink. Show all posts
Showing posts with label shrink. Show all posts
Friday, March 30, 2012
how to shrink Trx Log
Shrink Log file does not seems to make the file size any smaller.
The file size remains the even after shrinking it a few time.
The GUI seems ez to use but I may be doing it wrongly.
How do I shink the Log file?"TBoon" <allblacks15@.hotmail.com> wrote in message
news:esbf$KHIHHA.3676@.TK2MSFTNGP03.phx.gbl...
> Shrink Log file does not seems to make the file size any smaller.
> The file size remains the even after shrinking it a few time.
> The GUI seems ez to use but I may be doing it wrongly.
> How do I shink the Log file?
>
You can't shrink it if it's full of transactions. Perform a log backup to
copy the transactions out to a backup device, then you should be able to
shrink the transaction log. Then set up a maintence plan to perform
periodic transaction log backups to control the growth of the log file in
the future.
David|||... and some details at http://www.karaszi.com/SQLServer/in...ver/default.asp
http://www.solidqualitylearning.com/
"David Browne" <davidbaxterbrowne no potted meat@.hotmail.com> wrote in messa
ge
news:Ouy%239MHIHHA.924@.TK2MSFTNGP02.phx.gbl...
>
> "TBoon" <allblacks15@.hotmail.com> wrote in message news:esbf$KHIHHA.3676@.T
K2MSFTNGP03.phx.gbl...
> You can't shrink it if it's full of transactions. Perform a log backup to
copy the transactions
> out to a backup device, then you should be able to shrink the transaction
log. Then set up a
> maintence plan to perform periodic transaction log backups to control the
growth of the log file
> in the future.
> David
The file size remains the even after shrinking it a few time.
The GUI seems ez to use but I may be doing it wrongly.
How do I shink the Log file?"TBoon" <allblacks15@.hotmail.com> wrote in message
news:esbf$KHIHHA.3676@.TK2MSFTNGP03.phx.gbl...
> Shrink Log file does not seems to make the file size any smaller.
> The file size remains the even after shrinking it a few time.
> The GUI seems ez to use but I may be doing it wrongly.
> How do I shink the Log file?
>
You can't shrink it if it's full of transactions. Perform a log backup to
copy the transactions out to a backup device, then you should be able to
shrink the transaction log. Then set up a maintence plan to perform
periodic transaction log backups to control the growth of the log file in
the future.
David|||... and some details at http://www.karaszi.com/SQLServer/in...ver/default.asp
http://www.solidqualitylearning.com/
"David Browne" <davidbaxterbrowne no potted meat@.hotmail.com> wrote in messa
ge
news:Ouy%239MHIHHA.924@.TK2MSFTNGP02.phx.gbl...
>
> "TBoon" <allblacks15@.hotmail.com> wrote in message news:esbf$KHIHHA.3676@.T
K2MSFTNGP03.phx.gbl...
> You can't shrink it if it's full of transactions. Perform a log backup to
copy the transactions
> out to a backup device, then you should be able to shrink the transaction
log. Then set up a
> maintence plan to perform periodic transaction log backups to control the
growth of the log file
> in the future.
> David
how to shrink Trx Log
Shrink Log file does not seems to make the file size any smaller.
The file size remains the even after shrinking it a few time.
The GUI seems ez to use but I may be doing it wrongly.
How do I shink the Log file?"TBoon" <allblacks15@.hotmail.com> wrote in message
news:esbf$KHIHHA.3676@.TK2MSFTNGP03.phx.gbl...
> Shrink Log file does not seems to make the file size any smaller.
> The file size remains the even after shrinking it a few time.
> The GUI seems ez to use but I may be doing it wrongly.
> How do I shink the Log file?
>
You can't shrink it if it's full of transactions. Perform a log backup to
copy the transactions out to a backup device, then you should be able to
shrink the transaction log. Then set up a maintence plan to perform
periodic transaction log backups to control the growth of the log file in
the future.
David|||... and some details 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/
"David Browne" <davidbaxterbrowne no potted meat@.hotmail.com> wrote in message
news:Ouy%239MHIHHA.924@.TK2MSFTNGP02.phx.gbl...
>
> "TBoon" <allblacks15@.hotmail.com> wrote in message news:esbf$KHIHHA.3676@.TK2MSFTNGP03.phx.gbl...
>> Shrink Log file does not seems to make the file size any smaller.
>> The file size remains the even after shrinking it a few time.
>> The GUI seems ez to use but I may be doing it wrongly.
>> How do I shink the Log file?
> You can't shrink it if it's full of transactions. Perform a log backup to copy the transactions
> out to a backup device, then you should be able to shrink the transaction log. Then set up a
> maintence plan to perform periodic transaction log backups to control the growth of the log file
> in the future.
> David
The file size remains the even after shrinking it a few time.
The GUI seems ez to use but I may be doing it wrongly.
How do I shink the Log file?"TBoon" <allblacks15@.hotmail.com> wrote in message
news:esbf$KHIHHA.3676@.TK2MSFTNGP03.phx.gbl...
> Shrink Log file does not seems to make the file size any smaller.
> The file size remains the even after shrinking it a few time.
> The GUI seems ez to use but I may be doing it wrongly.
> How do I shink the Log file?
>
You can't shrink it if it's full of transactions. Perform a log backup to
copy the transactions out to a backup device, then you should be able to
shrink the transaction log. Then set up a maintence plan to perform
periodic transaction log backups to control the growth of the log file in
the future.
David|||... and some details 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/
"David Browne" <davidbaxterbrowne no potted meat@.hotmail.com> wrote in message
news:Ouy%239MHIHHA.924@.TK2MSFTNGP02.phx.gbl...
>
> "TBoon" <allblacks15@.hotmail.com> wrote in message news:esbf$KHIHHA.3676@.TK2MSFTNGP03.phx.gbl...
>> Shrink Log file does not seems to make the file size any smaller.
>> The file size remains the even after shrinking it a few time.
>> The GUI seems ez to use but I may be doing it wrongly.
>> How do I shink the Log file?
> You can't shrink it if it's full of transactions. Perform a log backup to copy the transactions
> out to a backup device, then you should be able to shrink the transaction log. Then set up a
> maintence plan to perform periodic transaction log backups to control the growth of the log file
> in the future.
> David
How to shrink transaction log
I have an application which is using SQL 2000 as
database. However, the transaction log is getting larger
and larger. I wouldn't reduce the size. The database
size is 300MB w/ used space 278MB and the transaction log
size is 6803MB w/ used space 290MB.
Is there any idea how to reduce the size of transaction
log?Quoted from Vishal :
You have various options to keep transaction log size manageable:
1.Keep taking transaction log backups at particular intervals this will help
you keep optimal transaction log size. When SQL Server finishes backing up
the transaction log, it automatically truncates the inactive portion of the
transaction log.
2.Keep transaction size as small as possible.
Refer to following urls
http://www.support.microsoft.com/?id=256650 INF: How to Shrink the SQL
Server 7.0 Tran Log
http://www.support.microsoft.com/?id=317375 Log File Grows too big
http://www.support.microsoft.com/?id=110139 Log file filling up
http://www.mssqlserver.com/faq/logs-shrinklog.asp Shrink File
http://www.support.microsoft.com/?id=315512 Considerations for Autogrow
and AutoShrink
http://www.support.microsoft.com/?id=272318 INF: Shrinking Log in SQL
Server 2000 with DBCC SHRINKFILE
http://www.support.microsoft.com/?id=317375 Log File Grows too big
http://www.support.microsoft.com/?id=110139 Log file filling up
http://www.mssqlserver.com/faq/logs-shrinklog.asp Shrink File
http://www.support.microsoft.com/?id=315512 Considerations for Autogrow
and AutoShrink
HTH, Jens Süßmeyer.
"Ling" <Ling.Ngai@.hk.standardchartered.com> schrieb im Newsbeitrag
news:07c001c37b78$e8e54f60$a101280a@.phx.gbl...
> I have an application which is using SQL 2000 as
> database. However, the transaction log is getting larger
> and larger. I wouldn't reduce the size. The database
> size is 300MB w/ used space 278MB and the transaction log
> size is 6803MB w/ used space 290MB.
> Is there any idea how to reduce the size of transaction
> log?|||I refer you to a useful post by Andrew kelly
Either change to Simple Mode or issue regular log backups. If your are
issuing timely log backups and it still continues to grow you most likely
have an open transaction stopping the truncation. Use DBCC OPENTRAN to see.
http://www.support.microsoft.com/?id=317375 Log File Grows too big
http://www.support.microsoft.com/?id=110139 Log file filling up
http://www.mssqlserver.com/faq/logs-shrinklog.asp Shrink File
http://www.support.microsoft.com/?id=315512 Considerations for Autogrow
and AutoShrink
http://www.support.microsoft.com/?id=256650 INF: How to Shrink the SQL
Server 7.0 Tran Log
http://www.support.microsoft.com/?id=272318 INF: Shrinking Log in SQL
Server 2000 with DBCC SHRINKFILE
--
Allan Mitchell (Microsoft SQL Server MVP)
MCSE,MCDBA
www.SQLDTS.com
I support PASS - the definitive, global community
for SQL Server professionals - http://www.sqlpass.org
"Ling" <Ling.Ngai@.hk.standardchartered.com> wrote in message
news:07c001c37b78$e8e54f60$a101280a@.phx.gbl...
> I have an application which is using SQL 2000 as
> database. However, the transaction log is getting larger
> and larger. I wouldn't reduce the size. The database
> size is 300MB w/ used space 278MB and the transaction log
> size is 6803MB w/ used space 290MB.
> Is there any idea how to reduce the size of transaction
> log?|||Try using this code.
You will need to change the <logname> and <databasename> with your
transaction log name and the database name. <Desired_final_size_in_MB> is
optional parameter. This script works great and I use it regularly.
DBCC SHRINKFILE (<logname> , <DESIRED_FINAL_SIZE_IN_MB>,TRUNCATEONLY )
go
BACKUP LOG <databasename> WITH TRUNCATE_ONLY
go
drop table mytable
go
CREATE TABLE MyTable (MyField VARCHAR(10), PK INT )
INSERT Mytable (PK) VALUES (1)
GO
SET NOCOUNT ON
DECLARE @.Index INT
SELECT @.Index = 0
WHILE (@.Index < 20000)
BEGIN
UPDATE MyTable
SET MyField = MyField
WHERE PK = 1 /* Some criteria to restrict to one row. */
SELECT @.Index = @.Index + 1
END
SET NOCOUNT OFF
go
DBCC SHRINKFILE ( <logname> ,<DESIRED_FINAL_SIZE_IN_MB>, truncateonly )
go
BACKUP LOG < databasename > WITH TRUNCATE_ONLY
go
"Ling" <Ling.Ngai@.hk.standardchartered.com> wrote in message
news:07c001c37b78$e8e54f60$a101280a@.phx.gbl...
> I have an application which is using SQL 2000 as
> database. However, the transaction log is getting larger
> and larger. I wouldn't reduce the size. The database
> size is 300MB w/ used space 278MB and the transaction log
> size is 6803MB w/ used space 290MB.
> Is there any idea how to reduce the size of transaction
> log?
database. However, the transaction log is getting larger
and larger. I wouldn't reduce the size. The database
size is 300MB w/ used space 278MB and the transaction log
size is 6803MB w/ used space 290MB.
Is there any idea how to reduce the size of transaction
log?Quoted from Vishal :
You have various options to keep transaction log size manageable:
1.Keep taking transaction log backups at particular intervals this will help
you keep optimal transaction log size. When SQL Server finishes backing up
the transaction log, it automatically truncates the inactive portion of the
transaction log.
2.Keep transaction size as small as possible.
Refer to following urls
http://www.support.microsoft.com/?id=256650 INF: How to Shrink the SQL
Server 7.0 Tran Log
http://www.support.microsoft.com/?id=317375 Log File Grows too big
http://www.support.microsoft.com/?id=110139 Log file filling up
http://www.mssqlserver.com/faq/logs-shrinklog.asp Shrink File
http://www.support.microsoft.com/?id=315512 Considerations for Autogrow
and AutoShrink
http://www.support.microsoft.com/?id=272318 INF: Shrinking Log in SQL
Server 2000 with DBCC SHRINKFILE
http://www.support.microsoft.com/?id=317375 Log File Grows too big
http://www.support.microsoft.com/?id=110139 Log file filling up
http://www.mssqlserver.com/faq/logs-shrinklog.asp Shrink File
http://www.support.microsoft.com/?id=315512 Considerations for Autogrow
and AutoShrink
HTH, Jens Süßmeyer.
"Ling" <Ling.Ngai@.hk.standardchartered.com> schrieb im Newsbeitrag
news:07c001c37b78$e8e54f60$a101280a@.phx.gbl...
> I have an application which is using SQL 2000 as
> database. However, the transaction log is getting larger
> and larger. I wouldn't reduce the size. The database
> size is 300MB w/ used space 278MB and the transaction log
> size is 6803MB w/ used space 290MB.
> Is there any idea how to reduce the size of transaction
> log?|||I refer you to a useful post by Andrew kelly
Either change to Simple Mode or issue regular log backups. If your are
issuing timely log backups and it still continues to grow you most likely
have an open transaction stopping the truncation. Use DBCC OPENTRAN to see.
http://www.support.microsoft.com/?id=317375 Log File Grows too big
http://www.support.microsoft.com/?id=110139 Log file filling up
http://www.mssqlserver.com/faq/logs-shrinklog.asp Shrink File
http://www.support.microsoft.com/?id=315512 Considerations for Autogrow
and AutoShrink
http://www.support.microsoft.com/?id=256650 INF: How to Shrink the SQL
Server 7.0 Tran Log
http://www.support.microsoft.com/?id=272318 INF: Shrinking Log in SQL
Server 2000 with DBCC SHRINKFILE
--
Allan Mitchell (Microsoft SQL Server MVP)
MCSE,MCDBA
www.SQLDTS.com
I support PASS - the definitive, global community
for SQL Server professionals - http://www.sqlpass.org
"Ling" <Ling.Ngai@.hk.standardchartered.com> wrote in message
news:07c001c37b78$e8e54f60$a101280a@.phx.gbl...
> I have an application which is using SQL 2000 as
> database. However, the transaction log is getting larger
> and larger. I wouldn't reduce the size. The database
> size is 300MB w/ used space 278MB and the transaction log
> size is 6803MB w/ used space 290MB.
> Is there any idea how to reduce the size of transaction
> log?|||Try using this code.
You will need to change the <logname> and <databasename> with your
transaction log name and the database name. <Desired_final_size_in_MB> is
optional parameter. This script works great and I use it regularly.
DBCC SHRINKFILE (<logname> , <DESIRED_FINAL_SIZE_IN_MB>,TRUNCATEONLY )
go
BACKUP LOG <databasename> WITH TRUNCATE_ONLY
go
drop table mytable
go
CREATE TABLE MyTable (MyField VARCHAR(10), PK INT )
INSERT Mytable (PK) VALUES (1)
GO
SET NOCOUNT ON
DECLARE @.Index INT
SELECT @.Index = 0
WHILE (@.Index < 20000)
BEGIN
UPDATE MyTable
SET MyField = MyField
WHERE PK = 1 /* Some criteria to restrict to one row. */
SELECT @.Index = @.Index + 1
END
SET NOCOUNT OFF
go
DBCC SHRINKFILE ( <logname> ,<DESIRED_FINAL_SIZE_IN_MB>, truncateonly )
go
BACKUP LOG < databasename > WITH TRUNCATE_ONLY
go
"Ling" <Ling.Ngai@.hk.standardchartered.com> wrote in message
news:07c001c37b78$e8e54f60$a101280a@.phx.gbl...
> I have an application which is using SQL 2000 as
> database. However, the transaction log is getting larger
> and larger. I wouldn't reduce the size. The database
> size is 300MB w/ used space 278MB and the transaction log
> size is 6803MB w/ used space 290MB.
> Is there any idea how to reduce the size of transaction
> log?
how to shrink tran-logs of databases
hi,
work on sql server 2000.
I have serveral dbs that are either 'full' or 'simple',
their tran-logs have lots of empty space in it due to one-
time big processes, hence, I want to shrink their physical
size down.
I used the below query on several dbs, some log size got
shrinked, some didn't shrink at all. how come? I didn't
perform a backup log operation for any of the db before I
run this query:
use db1
DBCC SHRINKFILE (db1_log1_logical_name)
many thanks.
JJ
HI,
You need to perform a backup log if the database recovery model is FULL or
BULK_LOGGED. Otherwise the inactive trasnaction logs will not be removed and
though your shrinkfile command will shrink. in case if you donot need the
trasnactions you could truncate the logs
backup log <dbname> with truncate_only
go
DBCC SHRINKFILE (db1_log1_logical_name,truncateonly)
Now execute the below command to see log file size and usage.
DBCC SQLPERF(LOGSPACE)
Thanks
Hari
MCDBA
"JJ Wang" <anonymous@.discussions.microsoft.com> wrote in message
news:28e4a01c46551$c5a68f00$a601280a@.phx.gbl...
> hi,
> work on sql server 2000.
> I have serveral dbs that are either 'full' or 'simple',
> their tran-logs have lots of empty space in it due to one-
> time big processes, hence, I want to shrink their physical
> size down.
> I used the below query on several dbs, some log size got
> shrinked, some didn't shrink at all. how come? I didn't
> perform a backup log operation for any of the db before I
> run this query:
> use db1
> DBCC SHRINKFILE (db1_log1_logical_name)
> many thanks.
> JJ
|||hey Hari, it's you again! thanks for all the help. I
think you are definately right.
one more question, I also want to shrink this db's data
file which is total 21 gig, with over 9 gig free. I want
to shrink off those 9 gigs. I started by shrinking down
25 mg only, it took 11 minutes! my God, for 9 gig, it
will take 66 hrs then!
how come so slow?
thanks
JJ
>--Original Message--
>HI,
>You need to perform a backup log if the database recovery
model is FULL or
>BULK_LOGGED. Otherwise the inactive trasnaction logs will
not be removed and
>though your shrinkfile command will shrink. in case if
you donot need the
>trasnactions you could truncate the logs
>backup log <dbname> with truncate_only
>go
> DBCC SHRINKFILE (db1_log1_logical_name,truncateonly)
>Now execute the below command to see log file size and
usage.
>DBCC SQLPERF(LOGSPACE)
>Thanks
>Hari
>MCDBA
>
>"JJ Wang" <anonymous@.discussions.microsoft.com> wrote in
message[vbcol=seagreen]
>news:28e4a01c46551$c5a68f00$a601280a@.phx.gbl...
one-[vbcol=seagreen]
physical[vbcol=seagreen]
I
>
>.
>
|||JJ,
9GB of free spacein a 21GB DB is not too much. You need free space for a
database to operate properly anyway for things such as reindexing and you
always want room to add more data without growing. In any case the reason
it takes so long is that it shrinks from the end of the file and must move
any data that is near the end to a free spot at the beginning. This process
is resource intensive and can take a while depending on your hardware and if
the log files are on their own raid array or not. 11 minutes for only 25MB
does seem a little excessive but if your disks are poorly configured it's
not out of the question. Shrinking the file will also fragment your data
files and you may want to reindex afterwards. But if you shrink it too much
that will cause it to grow again<g>.
Andrew J. Kelly SQL MVP
"JJ Wang" <anonymous@.discussions.microsoft.com> wrote in message
news:2a18601c4655a$809f44c0$a301280a@.phx.gbl...[vbcol=seagreen]
> hey Hari, it's you again! thanks for all the help. I
> think you are definately right.
> one more question, I also want to shrink this db's data
> file which is total 21 gig, with over 9 gig free. I want
> to shrink off those 9 gigs. I started by shrinking down
> 25 mg only, it took 11 minutes! my God, for 9 gig, it
> will take 66 hrs then!
> how come so slow?
> thanks
> JJ
> model is FULL or
> not be removed and
> you donot need the
> usage.
> message
> one-
> physical
> I
|||Hi,
Add on to Andrews detailed description, please shrink the MDF file in Single
user mode and during reindexing make the database recovery model to
BULK_LOGGED.
Alter database <dbname> set single_user with rollback immediate
go
dbcc shrinkfile('logical_mdf_name',size_in_mb)
go
Now reindex
go
-- Multi user
Alter database <dbname> set multi_user
Thanks
Hari
MCDBA
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:#e9ZYzVZEHA.212@.TK2MSFTNGP12.phx.gbl...
> JJ,
> 9GB of free spacein a 21GB DB is not too much. You need free space for a
> database to operate properly anyway for things such as reindexing and you
> always want room to add more data without growing. In any case the reason
> it takes so long is that it shrinks from the end of the file and must move
> any data that is near the end to a free spot at the beginning. This
process
> is resource intensive and can take a while depending on your hardware and
if
> the log files are on their own raid array or not. 11 minutes for only 25MB
> does seem a little excessive but if your disks are poorly configured it's
> not out of the question. Shrinking the file will also fragment your data
> files and you may want to reindex afterwards. But if you shrink it too
much
> that will cause it to grow again<g>.
> --
> Andrew J. Kelly SQL MVP
>
> "JJ Wang" <anonymous@.discussions.microsoft.com> wrote in message
> news:2a18601c4655a$809f44c0$a301280a@.phx.gbl...
>
|||Don't EVER leave the transaction log on 'full' recovery - it will absolutely eat your disks. ALWAYS have the default for ALL databases to be on 'simple'.
"JJ Wang" wrote:
> hi,
> work on sql server 2000.
> I have serveral dbs that are either 'full' or 'simple',
> their tran-logs have lots of empty space in it due to one-
> time big processes, hence, I want to shrink their physical
> size down.
> I used the below query on several dbs, some log size got
> shrinked, some didn't shrink at all. how come? I didn't
> perform a backup log operation for any of the db before I
> run this query:
> use db1
> DBCC SHRINKFILE (db1_log1_logical_name)
> many thanks.
> JJ
>
|||That is absolutely wrong advise. The reason it eats up your disks is that
you are not doing regular log backups to keep it in check. If you need to
have the ability to recover up to the minute there is no choice but to use
Full recovery mode.
Andrew J. Kelly SQL MVP
"Beeeeeeeeeeeeves" <Beeeeeeeeeeeeves@.discussions.microsoft.com> wrote in
message news:EF5BCB8B-0C14-4728-A2B3-D3E8907D7171@.microsoft.com...
> Don't EVER leave the transaction log on 'full' recovery - it will
absolutely eat your disks. ALWAYS have the default for ALL databases to be
on 'simple'.[vbcol=seagreen]
> "JJ Wang" wrote:
|||hi Andrew,
you sure always have good advices. The reason I want to
shrink this files was that we had a one time temperaroy
data expansion, this db's size should stay pretty constant
because we have a history archive db for it. and this is
just one of the files taht we need to shrink, there are
more files that have more unneeded space.
we have tran-log disk (1,0) seperate from our data disk
(raid 5). what do you mean by 'if the disk is poorly
designed'? how to judge that?
also, you mentioned: 'it takes so long is that it shrinks
from the end of the file and must move any data that is
near the end to a free spot at the beginning. ', is there
an option that we don't move the data around when
shrinking?
many thanks.
JJ
>--Original Message--
>JJ,
>9GB of free spacein a 21GB DB is not too much. You need
free space for a
>database to operate properly anyway for things such as
reindexing and you
>always want room to add more data without growing. In
any case the reason
>it takes so long is that it shrinks from the end of the
file and must move
>any data that is near the end to a free spot at the
beginning. This process
>is resource intensive and can take a while depending on
your hardware and if
>the log files are on their own raid array or not. 11
minutes for only 25MB
>does seem a little excessive but if your disks are poorly
configured it's
>not out of the question. Shrinking the file will also
fragment your data
>files and you may want to reindex afterwards. But if you
shrink it too much
>that will cause it to grow again<g>.
>--
>Andrew J. Kelly SQL MVP
>
>"JJ Wang" <anonymous@.discussions.microsoft.com> wrote in
message[vbcol=seagreen]
>news:2a18601c4655a$809f44c0$a301280a@.phx.gbl...
want[vbcol=seagreen]
recovery[vbcol=seagreen]
will[vbcol=seagreen]
in[vbcol=seagreen]
or 'simple',[vbcol=seagreen]
got[vbcol=seagreen]
didn't[vbcol=seagreen]
before
>
>.
>
|||thanks Andrew.
backup the log doesn't shrink its physical size though.
is the best way to shrink a log is to do it right after
the backup log job (maybe as the next step in the backup
log job)?
JJ
>--Original Message--
>That is absolutely wrong advise. The reason it eats up
your disks is that
>you are not doing regular log backups to keep it in
check. If you need to
>have the ability to recover up to the minute there is no
choice but to use
>Full recovery mode.
>--
>Andrew J. Kelly SQL MVP
>
>"Beeeeeeeeeeeeves"
<Beeeeeeeeeeeeves@.discussions.microsoft.com> wrote in
>message news:EF5BCB8B-0C14-4728-A2B3-
D3E8907D7171@.microsoft.com...[vbcol=seagreen]
recovery - it will
>absolutely eat your disks. ALWAYS have the default for
ALL databases to be[vbcol=seagreen]
>on 'simple'.
or 'simple',[vbcol=seagreen]
one-[vbcol=seagreen]
physical[vbcol=seagreen]
got[vbcol=seagreen]
didn't[vbcol=seagreen]
before I
>
>.
>
|||I totally agree with Andrew. but thanks anyway. :-)
JJ
>--Original Message--
>Don't EVER leave the transaction log on 'full' recovery -
it will absolutely eat your disks. ALWAYS have the default
for ALL databases to be on 'simple'.[vbcol=seagreen]
>"JJ Wang" wrote:
one-[vbcol=seagreen]
physical[vbcol=seagreen]
got[vbcol=seagreen]
didn't[vbcol=seagreen]
I
>.
>
sql
work on sql server 2000.
I have serveral dbs that are either 'full' or 'simple',
their tran-logs have lots of empty space in it due to one-
time big processes, hence, I want to shrink their physical
size down.
I used the below query on several dbs, some log size got
shrinked, some didn't shrink at all. how come? I didn't
perform a backup log operation for any of the db before I
run this query:
use db1
DBCC SHRINKFILE (db1_log1_logical_name)
many thanks.
JJ
HI,
You need to perform a backup log if the database recovery model is FULL or
BULK_LOGGED. Otherwise the inactive trasnaction logs will not be removed and
though your shrinkfile command will shrink. in case if you donot need the
trasnactions you could truncate the logs
backup log <dbname> with truncate_only
go
DBCC SHRINKFILE (db1_log1_logical_name,truncateonly)
Now execute the below command to see log file size and usage.
DBCC SQLPERF(LOGSPACE)
Thanks
Hari
MCDBA
"JJ Wang" <anonymous@.discussions.microsoft.com> wrote in message
news:28e4a01c46551$c5a68f00$a601280a@.phx.gbl...
> hi,
> work on sql server 2000.
> I have serveral dbs that are either 'full' or 'simple',
> their tran-logs have lots of empty space in it due to one-
> time big processes, hence, I want to shrink their physical
> size down.
> I used the below query on several dbs, some log size got
> shrinked, some didn't shrink at all. how come? I didn't
> perform a backup log operation for any of the db before I
> run this query:
> use db1
> DBCC SHRINKFILE (db1_log1_logical_name)
> many thanks.
> JJ
|||hey Hari, it's you again! thanks for all the help. I
think you are definately right.
one more question, I also want to shrink this db's data
file which is total 21 gig, with over 9 gig free. I want
to shrink off those 9 gigs. I started by shrinking down
25 mg only, it took 11 minutes! my God, for 9 gig, it
will take 66 hrs then!
how come so slow?
thanks
JJ
>--Original Message--
>HI,
>You need to perform a backup log if the database recovery
model is FULL or
>BULK_LOGGED. Otherwise the inactive trasnaction logs will
not be removed and
>though your shrinkfile command will shrink. in case if
you donot need the
>trasnactions you could truncate the logs
>backup log <dbname> with truncate_only
>go
> DBCC SHRINKFILE (db1_log1_logical_name,truncateonly)
>Now execute the below command to see log file size and
usage.
>DBCC SQLPERF(LOGSPACE)
>Thanks
>Hari
>MCDBA
>
>"JJ Wang" <anonymous@.discussions.microsoft.com> wrote in
message[vbcol=seagreen]
>news:28e4a01c46551$c5a68f00$a601280a@.phx.gbl...
one-[vbcol=seagreen]
physical[vbcol=seagreen]
I
>
>.
>
|||JJ,
9GB of free spacein a 21GB DB is not too much. You need free space for a
database to operate properly anyway for things such as reindexing and you
always want room to add more data without growing. In any case the reason
it takes so long is that it shrinks from the end of the file and must move
any data that is near the end to a free spot at the beginning. This process
is resource intensive and can take a while depending on your hardware and if
the log files are on their own raid array or not. 11 minutes for only 25MB
does seem a little excessive but if your disks are poorly configured it's
not out of the question. Shrinking the file will also fragment your data
files and you may want to reindex afterwards. But if you shrink it too much
that will cause it to grow again<g>.
Andrew J. Kelly SQL MVP
"JJ Wang" <anonymous@.discussions.microsoft.com> wrote in message
news:2a18601c4655a$809f44c0$a301280a@.phx.gbl...[vbcol=seagreen]
> hey Hari, it's you again! thanks for all the help. I
> think you are definately right.
> one more question, I also want to shrink this db's data
> file which is total 21 gig, with over 9 gig free. I want
> to shrink off those 9 gigs. I started by shrinking down
> 25 mg only, it took 11 minutes! my God, for 9 gig, it
> will take 66 hrs then!
> how come so slow?
> thanks
> JJ
> model is FULL or
> not be removed and
> you donot need the
> usage.
> message
> one-
> physical
> I
|||Hi,
Add on to Andrews detailed description, please shrink the MDF file in Single
user mode and during reindexing make the database recovery model to
BULK_LOGGED.
Alter database <dbname> set single_user with rollback immediate
go
dbcc shrinkfile('logical_mdf_name',size_in_mb)
go
Now reindex
go
-- Multi user
Alter database <dbname> set multi_user
Thanks
Hari
MCDBA
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:#e9ZYzVZEHA.212@.TK2MSFTNGP12.phx.gbl...
> JJ,
> 9GB of free spacein a 21GB DB is not too much. You need free space for a
> database to operate properly anyway for things such as reindexing and you
> always want room to add more data without growing. In any case the reason
> it takes so long is that it shrinks from the end of the file and must move
> any data that is near the end to a free spot at the beginning. This
process
> is resource intensive and can take a while depending on your hardware and
if
> the log files are on their own raid array or not. 11 minutes for only 25MB
> does seem a little excessive but if your disks are poorly configured it's
> not out of the question. Shrinking the file will also fragment your data
> files and you may want to reindex afterwards. But if you shrink it too
much
> that will cause it to grow again<g>.
> --
> Andrew J. Kelly SQL MVP
>
> "JJ Wang" <anonymous@.discussions.microsoft.com> wrote in message
> news:2a18601c4655a$809f44c0$a301280a@.phx.gbl...
>
|||Don't EVER leave the transaction log on 'full' recovery - it will absolutely eat your disks. ALWAYS have the default for ALL databases to be on 'simple'.
"JJ Wang" wrote:
> hi,
> work on sql server 2000.
> I have serveral dbs that are either 'full' or 'simple',
> their tran-logs have lots of empty space in it due to one-
> time big processes, hence, I want to shrink their physical
> size down.
> I used the below query on several dbs, some log size got
> shrinked, some didn't shrink at all. how come? I didn't
> perform a backup log operation for any of the db before I
> run this query:
> use db1
> DBCC SHRINKFILE (db1_log1_logical_name)
> many thanks.
> JJ
>
|||That is absolutely wrong advise. The reason it eats up your disks is that
you are not doing regular log backups to keep it in check. If you need to
have the ability to recover up to the minute there is no choice but to use
Full recovery mode.
Andrew J. Kelly SQL MVP
"Beeeeeeeeeeeeves" <Beeeeeeeeeeeeves@.discussions.microsoft.com> wrote in
message news:EF5BCB8B-0C14-4728-A2B3-D3E8907D7171@.microsoft.com...
> Don't EVER leave the transaction log on 'full' recovery - it will
absolutely eat your disks. ALWAYS have the default for ALL databases to be
on 'simple'.[vbcol=seagreen]
> "JJ Wang" wrote:
|||hi Andrew,
you sure always have good advices. The reason I want to
shrink this files was that we had a one time temperaroy
data expansion, this db's size should stay pretty constant
because we have a history archive db for it. and this is
just one of the files taht we need to shrink, there are
more files that have more unneeded space.
we have tran-log disk (1,0) seperate from our data disk
(raid 5). what do you mean by 'if the disk is poorly
designed'? how to judge that?
also, you mentioned: 'it takes so long is that it shrinks
from the end of the file and must move any data that is
near the end to a free spot at the beginning. ', is there
an option that we don't move the data around when
shrinking?
many thanks.
JJ
>--Original Message--
>JJ,
>9GB of free spacein a 21GB DB is not too much. You need
free space for a
>database to operate properly anyway for things such as
reindexing and you
>always want room to add more data without growing. In
any case the reason
>it takes so long is that it shrinks from the end of the
file and must move
>any data that is near the end to a free spot at the
beginning. This process
>is resource intensive and can take a while depending on
your hardware and if
>the log files are on their own raid array or not. 11
minutes for only 25MB
>does seem a little excessive but if your disks are poorly
configured it's
>not out of the question. Shrinking the file will also
fragment your data
>files and you may want to reindex afterwards. But if you
shrink it too much
>that will cause it to grow again<g>.
>--
>Andrew J. Kelly SQL MVP
>
>"JJ Wang" <anonymous@.discussions.microsoft.com> wrote in
message[vbcol=seagreen]
>news:2a18601c4655a$809f44c0$a301280a@.phx.gbl...
want[vbcol=seagreen]
recovery[vbcol=seagreen]
will[vbcol=seagreen]
in[vbcol=seagreen]
or 'simple',[vbcol=seagreen]
got[vbcol=seagreen]
didn't[vbcol=seagreen]
before
>
>.
>
|||thanks Andrew.
backup the log doesn't shrink its physical size though.
is the best way to shrink a log is to do it right after
the backup log job (maybe as the next step in the backup
log job)?
JJ
>--Original Message--
>That is absolutely wrong advise. The reason it eats up
your disks is that
>you are not doing regular log backups to keep it in
check. If you need to
>have the ability to recover up to the minute there is no
choice but to use
>Full recovery mode.
>--
>Andrew J. Kelly SQL MVP
>
>"Beeeeeeeeeeeeves"
<Beeeeeeeeeeeeves@.discussions.microsoft.com> wrote in
>message news:EF5BCB8B-0C14-4728-A2B3-
D3E8907D7171@.microsoft.com...[vbcol=seagreen]
recovery - it will
>absolutely eat your disks. ALWAYS have the default for
ALL databases to be[vbcol=seagreen]
>on 'simple'.
or 'simple',[vbcol=seagreen]
one-[vbcol=seagreen]
physical[vbcol=seagreen]
got[vbcol=seagreen]
didn't[vbcol=seagreen]
before I
>
>.
>
|||I totally agree with Andrew. but thanks anyway. :-)
JJ
>--Original Message--
>Don't EVER leave the transaction log on 'full' recovery -
it will absolutely eat your disks. ALWAYS have the default
for ALL databases to be on 'simple'.[vbcol=seagreen]
>"JJ Wang" wrote:
one-[vbcol=seagreen]
physical[vbcol=seagreen]
got[vbcol=seagreen]
didn't[vbcol=seagreen]
I
>.
>
sql
how to shrink the size of tempdb other than reboot the server
Hi,
I work with sql server 2000.
Wondering what's the best practice to shrink the size of
tempdb other than reboot the server. I don't want to
interrupt productions db running on the server.
JJtry dbcc shrinkdatabase, dbcc shrinkfile
Richard
"JJ Wang" <jwang@.leapwireless.com> wrote in message
news:019401c39283$bc0aeeb0$a401280a@.phx.gbl...
> Hi,
> I work with sql server 2000.
> Wondering what's the best practice to shrink the size of
> tempdb other than reboot the server. I don't want to
> interrupt productions db running on the server.
> JJ|||Howdy
You can reduce the tempdb size, BUT, if the tempdb was created with
( say ) 1000 MB initial size, 1000 MB is the smallest you can
shrink it to.
Cheers
SG
Posted via http://dbforums.com
I work with sql server 2000.
Wondering what's the best practice to shrink the size of
tempdb other than reboot the server. I don't want to
interrupt productions db running on the server.
JJtry dbcc shrinkdatabase, dbcc shrinkfile
Richard
"JJ Wang" <jwang@.leapwireless.com> wrote in message
news:019401c39283$bc0aeeb0$a401280a@.phx.gbl...
> Hi,
> I work with sql server 2000.
> Wondering what's the best practice to shrink the size of
> tempdb other than reboot the server. I don't want to
> interrupt productions db running on the server.
> JJ|||Howdy
You can reduce the tempdb size, BUT, if the tempdb was created with
( say ) 1000 MB initial size, 1000 MB is the smallest you can
shrink it to.
Cheers
SG
Posted via http://dbforums.com
how to shrink the logfile
hi,
My log file is of more than 2gb,
i want to shrink the size of this , without taking my server offline.
i've tried
DBCC SHRINKFILE ( 2, EMPTYFILE )
still no use.
This is the info of my database
Status=ONLINE, Updateability=READ_WRITE, UserAccess=MULTI_USER,
Recovery=FULL, Version=539, Collation=SQL_Latin1_General_CP1_CI_AS,
SQLSortOrder=52, IsTornPageDetectionEnabled, IsAutoCreateStatistics,
IsAutoUpdateStatistics, IsFullTextEnabled
thanks in advance
regards
Lara
Hi
Check out
http://msdn.microsoft.com/library/de...r_da2_1uzr.asp
John
"Lara" wrote:
> hi,
> My log file is of more than 2gb,
> i want to shrink the size of this , without taking my server offline.
> i've tried
> DBCC SHRINKFILE ( 2, EMPTYFILE )
> still no use.
> This is the info of my database
> Status=ONLINE, Updateability=READ_WRITE, UserAccess=MULTI_USER,
> Recovery=FULL, Version=539, Collation=SQL_Latin1_General_CP1_CI_AS,
> SQLSortOrder=52, IsTornPageDetectionEnabled, IsAutoCreateStatistics,
> IsAutoUpdateStatistics, IsFullTextEnabled
>
> thanks in advance
> regards
> Lara
>
>
|||Hi,
How to reduce the Transaction log size
1. Backup the transaction log (Use BACKUP Log dbname to
disk='c:\backup\dbname.tr1' or use enterprise manager)
or (Since you done have the hard disk space truncate the trasaction log)
backup log <dbname> with truncate_only
3. Shrink the transaction log file.
DBCC SHRINKFILE('logical_transactioXn_log file name','truncateonly')
Have a look into the below link.
Shrinking the Transaction Log in SQL Server 2000 with DBCC
http://www.support.microsoft.com/?id=272318
Thanks
Hari
SQL Server MVP
"Lara" <lara@.hotmail.com> wrote in message
news:%23uQQ0KKqFHA.3404@.TK2MSFTNGP11.phx.gbl...
> hi,
> My log file is of more than 2gb,
> i want to shrink the size of this , without taking my server offline.
> i've tried
> DBCC SHRINKFILE ( 2, EMPTYFILE )
> still no use.
> This is the info of my database
> Status=ONLINE, Updateability=READ_WRITE, UserAccess=MULTI_USER,
> Recovery=FULL, Version=539, Collation=SQL_Latin1_General_CP1_CI_AS,
> SQLSortOrder=52, IsTornPageDetectionEnabled, IsAutoCreateStatistics,
> IsAutoUpdateStatistics, IsFullTextEnabled
>
> thanks in advance
> regards
> Lara
>
My log file is of more than 2gb,
i want to shrink the size of this , without taking my server offline.
i've tried
DBCC SHRINKFILE ( 2, EMPTYFILE )
still no use.
This is the info of my database
Status=ONLINE, Updateability=READ_WRITE, UserAccess=MULTI_USER,
Recovery=FULL, Version=539, Collation=SQL_Latin1_General_CP1_CI_AS,
SQLSortOrder=52, IsTornPageDetectionEnabled, IsAutoCreateStatistics,
IsAutoUpdateStatistics, IsFullTextEnabled
thanks in advance
regards
Lara
Hi
Check out
http://msdn.microsoft.com/library/de...r_da2_1uzr.asp
John
"Lara" wrote:
> hi,
> My log file is of more than 2gb,
> i want to shrink the size of this , without taking my server offline.
> i've tried
> DBCC SHRINKFILE ( 2, EMPTYFILE )
> still no use.
> This is the info of my database
> Status=ONLINE, Updateability=READ_WRITE, UserAccess=MULTI_USER,
> Recovery=FULL, Version=539, Collation=SQL_Latin1_General_CP1_CI_AS,
> SQLSortOrder=52, IsTornPageDetectionEnabled, IsAutoCreateStatistics,
> IsAutoUpdateStatistics, IsFullTextEnabled
>
> thanks in advance
> regards
> Lara
>
>
|||Hi,
How to reduce the Transaction log size
1. Backup the transaction log (Use BACKUP Log dbname to
disk='c:\backup\dbname.tr1' or use enterprise manager)
or (Since you done have the hard disk space truncate the trasaction log)
backup log <dbname> with truncate_only
3. Shrink the transaction log file.
DBCC SHRINKFILE('logical_transactioXn_log file name','truncateonly')
Have a look into the below link.
Shrinking the Transaction Log in SQL Server 2000 with DBCC
http://www.support.microsoft.com/?id=272318
Thanks
Hari
SQL Server MVP
"Lara" <lara@.hotmail.com> wrote in message
news:%23uQQ0KKqFHA.3404@.TK2MSFTNGP11.phx.gbl...
> hi,
> My log file is of more than 2gb,
> i want to shrink the size of this , without taking my server offline.
> i've tried
> DBCC SHRINKFILE ( 2, EMPTYFILE )
> still no use.
> This is the info of my database
> Status=ONLINE, Updateability=READ_WRITE, UserAccess=MULTI_USER,
> Recovery=FULL, Version=539, Collation=SQL_Latin1_General_CP1_CI_AS,
> SQLSortOrder=52, IsTornPageDetectionEnabled, IsAutoCreateStatistics,
> IsAutoUpdateStatistics, IsFullTextEnabled
>
> thanks in advance
> regards
> Lara
>
how to shrink the logfile
hi,
My log file is of more than 2gb,
i want to shrink the size of this , without taking my server offline.
i've tried
DBCC SHRINKFILE ( 2, EMPTYFILE )
still no use.
This is the info of my database
Status=ONLINE, Updateability=READ_WRITE, UserAccess=MULTI_USER,
Recovery=FULL, Version=539, Collation=SQL_Latin1_General_CP1_CI_AS,
SQLSortOrder=52, IsTornPageDetectionEnabled, IsAutoCreateStatistics,
IsAutoUpdateStatistics, IsFullTextEnabled
thanks in advance
regards
LaraHi
Check out
http://msdn.microsoft.com/library/d...r />
_1uzr.asp
John
"Lara" wrote:
> hi,
> My log file is of more than 2gb,
> i want to shrink the size of this , without taking my server offline.
> i've tried
> DBCC SHRINKFILE ( 2, EMPTYFILE )
> still no use.
> This is the info of my database
> Status=ONLINE, Updateability=READ_WRITE, UserAccess=MULTI_USER,
> Recovery=FULL, Version=539, Collation=SQL_Latin1_General_CP1_CI_AS,
> SQLSortOrder=52, IsTornPageDetectionEnabled, IsAutoCreateStatistics,
> IsAutoUpdateStatistics, IsFullTextEnabled
>
> thanks in advance
> regards
> Lara
>
>|||Hi,
How to reduce the Transaction log size
1. Backup the transaction log (Use BACKUP Log dbname to
disk='c:\backup\dbname.tr1' or use enterprise manager)
or (Since you done have the hard disk space truncate the trasaction log)
backup log <dbname> with truncate_only
3. Shrink the transaction log file.
DBCC SHRINKFILE('logical_transactio_n_log file name','truncateonly')
Have a look into the below link.
Shrinking the Transaction Log in SQL Server 2000 with DBCC
http://www.support.microsoft.com/?id=272318
Thanks
Hari
SQL Server MVP
"Lara" <lara@.hotmail.com> wrote in message
news:%23uQQ0KKqFHA.3404@.TK2MSFTNGP11.phx.gbl...
> hi,
> My log file is of more than 2gb,
> i want to shrink the size of this , without taking my server offline.
> i've tried
> DBCC SHRINKFILE ( 2, EMPTYFILE )
> still no use.
> This is the info of my database
> Status=ONLINE, Updateability=READ_WRITE, UserAccess=MULTI_USER,
> Recovery=FULL, Version=539, Collation=SQL_Latin1_General_CP1_CI_AS,
> SQLSortOrder=52, IsTornPageDetectionEnabled, IsAutoCreateStatistics,
> IsAutoUpdateStatistics, IsFullTextEnabled
>
> thanks in advance
> regards
> Lara
>
My log file is of more than 2gb,
i want to shrink the size of this , without taking my server offline.
i've tried
DBCC SHRINKFILE ( 2, EMPTYFILE )
still no use.
This is the info of my database
Status=ONLINE, Updateability=READ_WRITE, UserAccess=MULTI_USER,
Recovery=FULL, Version=539, Collation=SQL_Latin1_General_CP1_CI_AS,
SQLSortOrder=52, IsTornPageDetectionEnabled, IsAutoCreateStatistics,
IsAutoUpdateStatistics, IsFullTextEnabled
thanks in advance
regards
LaraHi
Check out
http://msdn.microsoft.com/library/d...r />
_1uzr.asp
John
"Lara" wrote:
> hi,
> My log file is of more than 2gb,
> i want to shrink the size of this , without taking my server offline.
> i've tried
> DBCC SHRINKFILE ( 2, EMPTYFILE )
> still no use.
> This is the info of my database
> Status=ONLINE, Updateability=READ_WRITE, UserAccess=MULTI_USER,
> Recovery=FULL, Version=539, Collation=SQL_Latin1_General_CP1_CI_AS,
> SQLSortOrder=52, IsTornPageDetectionEnabled, IsAutoCreateStatistics,
> IsAutoUpdateStatistics, IsFullTextEnabled
>
> thanks in advance
> regards
> Lara
>
>|||Hi,
How to reduce the Transaction log size
1. Backup the transaction log (Use BACKUP Log dbname to
disk='c:\backup\dbname.tr1' or use enterprise manager)
or (Since you done have the hard disk space truncate the trasaction log)
backup log <dbname> with truncate_only
3. Shrink the transaction log file.
DBCC SHRINKFILE('logical_transactio_n_log file name','truncateonly')
Have a look into the below link.
Shrinking the Transaction Log in SQL Server 2000 with DBCC
http://www.support.microsoft.com/?id=272318
Thanks
Hari
SQL Server MVP
"Lara" <lara@.hotmail.com> wrote in message
news:%23uQQ0KKqFHA.3404@.TK2MSFTNGP11.phx.gbl...
> hi,
> My log file is of more than 2gb,
> i want to shrink the size of this , without taking my server offline.
> i've tried
> DBCC SHRINKFILE ( 2, EMPTYFILE )
> still no use.
> This is the info of my database
> Status=ONLINE, Updateability=READ_WRITE, UserAccess=MULTI_USER,
> Recovery=FULL, Version=539, Collation=SQL_Latin1_General_CP1_CI_AS,
> SQLSortOrder=52, IsTornPageDetectionEnabled, IsAutoCreateStatistics,
> IsAutoUpdateStatistics, IsFullTextEnabled
>
> thanks in advance
> regards
> Lara
>
how to shrink the logfile
hi,
My log file is of more than 2gb,
i want to shrink the size of this , without taking my server offline.
i've tried
DBCC SHRINKFILE ( 2, EMPTYFILE )
still no use.
This is the info of my database
Status=ONLINE, Updateability=READ_WRITE, UserAccess=MULTI_USER,
Recovery=FULL, Version=539, Collation=SQL_Latin1_General_CP1_CI_AS,
SQLSortOrder=52, IsTornPageDetectionEnabled, IsAutoCreateStatistics,
IsAutoUpdateStatistics, IsFullTextEnabled
thanks in advance
regards
LaraHi
Check out
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/architec/8_ar_da2_1uzr.asp
John
"Lara" wrote:
> hi,
> My log file is of more than 2gb,
> i want to shrink the size of this , without taking my server offline.
> i've tried
> DBCC SHRINKFILE ( 2, EMPTYFILE )
> still no use.
> This is the info of my database
> Status=ONLINE, Updateability=READ_WRITE, UserAccess=MULTI_USER,
> Recovery=FULL, Version=539, Collation=SQL_Latin1_General_CP1_CI_AS,
> SQLSortOrder=52, IsTornPageDetectionEnabled, IsAutoCreateStatistics,
> IsAutoUpdateStatistics, IsFullTextEnabled
>
> thanks in advance
> regards
> Lara
>
>|||Hi,
How to reduce the Transaction log size
1. Backup the transaction log (Use BACKUP Log dbname to
disk='c:\backup\dbname.tr1' or use enterprise manager)
or (Since you done have the hard disk space truncate the trasaction log)
backup log <dbname> with truncate_only
3. Shrink the transaction log file.
DBCC SHRINKFILE('logical_transaction_log file name','truncateonly')
Have a look into the below link.
Shrinking the Transaction Log in SQL Server 2000 with DBCC
http://www.support.microsoft.com/?id=272318
Thanks
Hari
SQL Server MVP
"Lara" <lara@.hotmail.com> wrote in message
news:%23uQQ0KKqFHA.3404@.TK2MSFTNGP11.phx.gbl...
> hi,
> My log file is of more than 2gb,
> i want to shrink the size of this , without taking my server offline.
> i've tried
> DBCC SHRINKFILE ( 2, EMPTYFILE )
> still no use.
> This is the info of my database
> Status=ONLINE, Updateability=READ_WRITE, UserAccess=MULTI_USER,
> Recovery=FULL, Version=539, Collation=SQL_Latin1_General_CP1_CI_AS,
> SQLSortOrder=52, IsTornPageDetectionEnabled, IsAutoCreateStatistics,
> IsAutoUpdateStatistics, IsFullTextEnabled
>
> thanks in advance
> regards
> Lara
>
My log file is of more than 2gb,
i want to shrink the size of this , without taking my server offline.
i've tried
DBCC SHRINKFILE ( 2, EMPTYFILE )
still no use.
This is the info of my database
Status=ONLINE, Updateability=READ_WRITE, UserAccess=MULTI_USER,
Recovery=FULL, Version=539, Collation=SQL_Latin1_General_CP1_CI_AS,
SQLSortOrder=52, IsTornPageDetectionEnabled, IsAutoCreateStatistics,
IsAutoUpdateStatistics, IsFullTextEnabled
thanks in advance
regards
LaraHi
Check out
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/architec/8_ar_da2_1uzr.asp
John
"Lara" wrote:
> hi,
> My log file is of more than 2gb,
> i want to shrink the size of this , without taking my server offline.
> i've tried
> DBCC SHRINKFILE ( 2, EMPTYFILE )
> still no use.
> This is the info of my database
> Status=ONLINE, Updateability=READ_WRITE, UserAccess=MULTI_USER,
> Recovery=FULL, Version=539, Collation=SQL_Latin1_General_CP1_CI_AS,
> SQLSortOrder=52, IsTornPageDetectionEnabled, IsAutoCreateStatistics,
> IsAutoUpdateStatistics, IsFullTextEnabled
>
> thanks in advance
> regards
> Lara
>
>|||Hi,
How to reduce the Transaction log size
1. Backup the transaction log (Use BACKUP Log dbname to
disk='c:\backup\dbname.tr1' or use enterprise manager)
or (Since you done have the hard disk space truncate the trasaction log)
backup log <dbname> with truncate_only
3. Shrink the transaction log file.
DBCC SHRINKFILE('logical_transaction_log file name','truncateonly')
Have a look into the below link.
Shrinking the Transaction Log in SQL Server 2000 with DBCC
http://www.support.microsoft.com/?id=272318
Thanks
Hari
SQL Server MVP
"Lara" <lara@.hotmail.com> wrote in message
news:%23uQQ0KKqFHA.3404@.TK2MSFTNGP11.phx.gbl...
> hi,
> My log file is of more than 2gb,
> i want to shrink the size of this , without taking my server offline.
> i've tried
> DBCC SHRINKFILE ( 2, EMPTYFILE )
> still no use.
> This is the info of my database
> Status=ONLINE, Updateability=READ_WRITE, UserAccess=MULTI_USER,
> Recovery=FULL, Version=539, Collation=SQL_Latin1_General_CP1_CI_AS,
> SQLSortOrder=52, IsTornPageDetectionEnabled, IsAutoCreateStatistics,
> IsAutoUpdateStatistics, IsFullTextEnabled
>
> thanks in advance
> regards
> Lara
>
How to Shrink SQL Server database
First of all thanks for taking interest in this thread.
I have database in Microsoft SQL Server and its size is appx. 427 GB. It consists of data from 2006. Everyday I expire data of 2-3 days and then try to shrink database as much as possible.
But it frees only 2-3 MB after 7-8 hours process. And in that database there some files which do not shrink even I put one of them on shrinking for whole day.
And as per company policy, I am only allowed to expire data which are 15 months older.
I am facing very much problem as my daily import and publish activity some times got failed for that reason.
If anyone knows the solution please tell me the solution.The simple answer would be don't shrink it. Shrinking db files is really not a good idea especially on a regular basis.
Moving thread to proper forum.|||Then what should I do?|||Then what should I do?Don't shrink the file :)
Are there any pressing needs for why you are shrinking? Is this an active database with ongoing modifications?|||Yes, I have 2 databases. One is currently in use and occupies 202 GB and the other, older one, which is not in use but have to keep data up to 15 month older, occupies appx. 427 GB.
I have 9 drives of 100 GB and there is restriction that each drive must have 10% free space. As daily input to new database done, it grows rapidly but older one doesn't frees occupied space even after expiring data older then 15 months.
Thus, my application which does daily import and publish activity get failed.
That's why I shrink the database.
Please tell me the solution for that.|||Buy more disks. I am serious - why fit your database to your server not the other way round?
10% free space is rather low too. You can't even do a proper physical defrag AFAIK.
Check out the BoL entry for shrinking databases and files. Paul Randal (SQL Server enginge lead at MS) ensured that some of the disadvantages of this approach made it into the manual. If you try googling you will find hundreds of examples of people wnating to do what you want to do and being advised not to do it.|||Thanks for serious advice! But this is not my pc or my company's pc but it is my company's clients server pc.
And in near future they have no plan to add any drive as there is much total free space. But problem is that on some drive it has 50 GB free space and on some drives only 12 GB.
I also tried to move datafiles but some files are more than 50 GB so cant move to other drive.
So still any serious advice?|||Listen to the Flump. The Flump speaks Wisdom.
Do.
Not.
Shrink.
Database.
I wish Microsoft had not even included database shrinking as an option.
Tell.
Your.
Client.
To.
Buy.
More.
Disk.
Space.|||I *love* useless advice.
Listen to Chintan. *He* needs to shrink the database-and nobody will tell him how. Doesn't matter whether it's a good idea or not. He's been told to shrink the database. He has two options-shrink the database or leave his job. Your advice boils down to option 2: leave his job because they're telling him to do something that *you* don't think is a good idea.
I'm not arguing whether or not shrinking the database is a good idea or not, but I am arguing that ignoring Chintan's problem is a *bad* idea. For him, at least.
Chintan: If I could help you, I would-but I'm faced with the same problem & the same lack of useful advice.|||I *love* useless advice.
Listen to Chintan. *He* needs to shrink the database-and nobody will tell him how. Doesn't matter whether it's a good idea or not. He's been told to shrink the database. He has two options-shrink the database or leave his job. Your advice boils down to option 2: leave his job because they're telling him to do something that *you* don't think is a good idea.
I'm not arguing whether or not shrinking the database is a good idea or not, but I am arguing that ignoring Chintan's problem is a *bad* idea. For him, at least.
Chintan: If I could help you, I would-but I'm faced with the same problem & the same lack of useful advice.
It's NOT useless advice. It is sound, practical advice.
The only thing I would add to it would go something like this:
What you face is not a technical issue. It is a business issue. Business people respond better to technical problems when they are presented in business terms. This usually means presenting an issue and couching it in terms of cost versus benefit.
I would approach the problem in this fashion:
John [or my manager's name],
I am technically unable to shrink the database because of limitations in MS SQL Server. There are several ways to deal with this issue:
1. We can do nothing. Eventually the application will fail because there is insufficient storage. This will cost the business no money, but the downside is that the application will no longer function and users will not have access to the most recent data.
2. We can add disk space to the existing server. This would involve purchasing XX disks at YY cost per disk for a total of ZZZ dollars. I do/do not have experience installing these disks and so there is/is not additional cost associated with bringing in a technician to do the installation.
3. We can selectively eliminate more data than what is being purged now. Instead of C years' retention, we can instead choose B years' retention. This will/will not put us in violation of selected audit rules/business requirements. Once more data has been eliminated, it may be possible for me to shrink the database effectively.
My recommendation would be to implement Option X above.
I look forward to working through this issue with you.
Regards,
Don B. Azalea
My advice:
1. Never approach your manager with a business problem without having at least two solutions that you have thought through.
2. Always include the option to do nothing. It at least rephrases the problem in terms that a manager can comprehend.
3. Learn to speak in terms of Return on Investment. It will make you a more well-rounded (and hence valuable) person in your operation.
4. Never talk down to your boss/supervisor. They make decisions that affect your paycheck. Speak to them in terms that they can understand, but never assume that they are ignorant. Try to step out of your world on occasion and see things from their point of view. It can be very enlightening.
Good luck.
By the way, I agree with Pootle and Blindman here: shrinking the db is NOT the way to go.
Regards,
hmscott
Edited for clarity.|||Pootle Flump...nice sig!!!
:D
hmscott|||Chintan: If I could help you, I would-but I'm faced with the same problem & the same lack of useful advice.Hint: It's not the advice that is useless...|||sometimes, late at night, when the wind is still and the water on the pond is a smooth black mirror, you can hear the Flump calling out, wailing a sad song of how you shouldn't shrink databases.
who will listen?|||If the advice doesn't solve the problem, then it's useless-IMO. The problem isn't whether or not to shrink the database-that's already been decided (and not at my level). If I disagree with that decision I can quit or be fired.
The problem is *how* to shrink the database. Your advice doesn't solve that, so it's useless. JMHO.|||Should have addressed hmscott's reply, too. That was a nice reply & would be useful in other situations. Doesn't work here as the decision has already been made. Stating that I'm unable to shrink the database would work if I actually were unable to shrink it-but that's not the case. Both my boss & his boss know that the database can be shrunk so that approach simply tells them that *I* can't shrink it-and that's likely to get me fired. (Don't know about where you work but being unable to do something you were hired to do is grounds for dismissal here.)|||part of my job is to provide managers, users and developers with what i consider the best practices and the best options when it comes to all matters database. if i do not do that, then I am failing them by not providing them the best solution possible.
i understand that money might not always be there for the hardware I desire or require, but it is my job to point out the need. from what i understand you can mitigate the effects of the shrink with a little index defrag or or reindex. however it seems part of the problem is the time the shrink takes and the process will be even longer if you add this step in.
so thems be the options mr. manager. buy me some disks, or i am going to bog down my server by doing some expensive shrinking and defragging. do DBAs get fired? I never recall seeing it happen. half decent ones seem to hard to find. I have certainly behaved in such a way i would have fired myself once or twice.|||so thems be the options mr. manager. buy me some disks, or i am going to bog down my server by doing some expensive shrinking and defragging. do DBAs get fired? I never recall seeing it happen. half decent ones seem to hard to find. I have certainly behaved in such a way i would have fired myself once or twice.
:D
I have never done such a thing. never
Well, maybe once...there was a time when I dropped the entire schema for our data warehouse...but that's a tale for a different day.
Regards,
hmscott|||Should have addressed hmscott's reply, too. That was a nice reply & would be useful in other situations. Doesn't work here as the decision has already been made. Stating that I'm unable to shrink the database would work if I actually were unable to shrink it-but that's not the case. Both my boss & his boss know that the database can be shrunk so that approach simply tells them that *I* can't shrink it-and that's likely to get me fired. (Don't know about where you work but being unable to do something you were hired to do is grounds for dismissal here.)
Don't know what your situation is, so I can't address it directly.
Conditions under which I can understand and recommend that a database be shrunk:
1. You've purged a "significant" amount of data from the data files (through truncate or delete or drop). You do not expect to re-add this data anytime soon.
NB: "Significant" is subjective here, it can be any value, but I would generally look for anything greater than 25%.
2. You've had an issue with a log file filling up and extending beyond your intended size limit.
Some ways of shrinking your database...
Instead of using DBCC SHRINK DATABASE, consider using DBCC SHRINKFILE. This can be less expensive in terms of I/O on the disk.
You might also consider deleting some indexes in an attempt to trade off performance for space.
You might consider moving some data to another server, making a linked server and accessing the moved data through a linked server query.
On a side note, if you think that you might lose your job on account of this one issue, then your job is already lost. I do not mean this to be facetious or rude. You must have sufficient confidence in your skills and trust in your boss to be able to admit to ignorance of a particular issue and be able to get the help you need to fix it. If that confidence isn't there or the trust isn't there, then it's only a matter of time before your boss will find someone who has the skills or in whom he can trust.
Just my .02. It's worth what you paid for it.
Regards,
hmscott|||Well, I agree with your comments about the job. Long term either I'll be gone or my boss will, but that doesn't mean I want to (actually, can afford to) leave right now.
I appreciate your advice & will look into SHRINKFILE. Somehow overlooked that while I was checking on SHRINK DATABASE.
Thrasy, essentially those options were presented to my manager-who responded that buying more disks was out. He knows the database can be shrunk but it's up to me to actually do it and all the advice I've found so far is "don't do it". Even though I agree with it, it's still useless because I can't follow it & still keep my job.|||No problem. FWIW, it appears that I will soon be moving on though not by my choice nor by my manager's choice.
Long story, but it sucks 'cause I like it here.
Regards,
hmscott|||Calvin, obviously your boss does not respect your opinion or advice.
And after reading your posts in this thread, equally obvious to me is WHY he doesn't respect your advice.
Pootle gave good advice. The fact that your boss refuses to listen to the advice of some of the best DBAs in the world, coupled with your labeling that advice as "useless", makes me think that you must be a pretty good fit for your current position of employment. I can see why you are so keen on keeping it.
Basically, if your boss refuses to listen to you that is not Pootle's problem, and it is not my problem, and it is not SQL Server's problem. Who's left?|||Guys! Guys! Guys!
Lots of reply, thanks for that especially Calvin-c, flump and hmscott.
I got confused after reading all replies.
My actual problem is:
-> How to more effectively shrink SQL database?
-> Is there any alternate way through which I can get free spaces after expiring?
As I told there is appx. 270 GB free space available but in scattered form means in one drive there is available free space is 40 GB while in other 12 GB. And sizes of datafiles are very big so I can't move them from one drive to another.
So I don't think that I have any option rather that going for shrinking database however it is not preferable.
And as there is lots of free space, so I can't demand for more disk space in any condition.
So still any serious advice?|||Do you know how much data you actually delete? You know it is more than a few MB yes? Also what commands are you executing?
Just in case I forget to mention later - you need to reindex after a shrink too as you will have fragmented your indexes and the database will run poorly for many reporting applications.|||Also - what version? Is this 2000 or a database that has been upgraded to 2005? If so please run DBCC UPDATEUSAGE|||I use SQL 2000. And everyday first I expire data oldest 2-3 days data using min (timestamp) query.
Appx. 3 GB of data per day, however it depends on the log file for that day but on the average it shows reduced size of that database up to 6 to 9 GB.
In expiring, I dont face any problem but after then when I shrink database. There are total 36 tables and 24 tables get shrunken successfully but 12 tables do not shrink and those tables use much of the memory. And at the end it frees hardly 3-4 MB.
If you know how to shrink those 12 tables then most probably my problem will be solved.|||What code are you running to get the used and available space for your database? And what code do you run to shrink it?|||I recently had to shrink a 50 gig database after major clean up (previous company was ...). It went from 50 to 1 when I was done, but I had to re-run the shrink probably 100 times over a weekend before it was done. Also, I was able to do it before going live with the db.
I also would switch back and forth between shrinkfile and shrinkdatabase. Sometimes it would free up nothing, and then every 10 times or so it would give me back a couple gigs.|||I use expiredata command from DOS to expire database for specified date. However this command is only available through application I use.
And after then I shrink the tables one by one from SQL Server Enterprise Manager -> shrink database.
I have also tried shrinkdatabase and shrinktable but they dont work.|||This is what I use but I only need to deal with 2 GB database since I have my clients running MSDE Rel A. I don't know how long it will take on a 250 GB database.
HTH
backup log MyDataBase with truncate_only
dbcc loginfo
go
dbcc shrinkfile (MyDataBase_log);
dbcc loginfo
go
dbcc shrinkfile (MyDataBase_DATA);
dbcc loginfo
go|||I use expiredata command from DOS to expire database for specified date. However this command is only available through application I use.
Presumably that just deletes data from tables?
And after then I shrink the tables one by one from SQL Server Enterprise Manager -> shrink database.
I have also tried shrinkdatabase and shrinktable but they dont work.
Well - you don't shrink tables you shrink files\ databases.
Did you run updateusage?
What do you get if you run this:
USE my_db
go
CREATE TABLE #helpfile
(
name sysname
, fileid smallint
, filename nvarchar(256)
, filegroup sysname NULL
, size nvarchar(18)
, maxsize nvarchar(18)
, growth nvarchar(18)
, usage varchar(9)
)
INSERT INTO #helpfile
EXEC dbo.sp_helpfile
SELECT *
, file_size_mb - space_used_mb AS spare_space
FROM (
SELECT name
, usage
, CAST(SUBSTRING(size, 1, CHARINDEX(' ', size) - 1) AS INT) / 1024 AS file_size_mb
, CAST(fileproperty(name,'SpaceUsed') * (8/ 1024.0) AS INT) AS space_used_mb
FROM #helpfile
) AS der_t
DROP TABLE #helpfile|||It shows all datafiles and spare space. The total space is 24813 MB. And there are 4 files which have spare space more than 5000 MB. So if I manage with there files, my problem will automatically be solved.
But main problem is that as I earlier said; when I shrink these files it hardly frees 1-2 MB after whole day process.|||Can you confirm you ran updateusage before running that script?|||What is updatemanage? I don't know what it is? Will you please tell me what it is and how will it be useful to me?|||Sorry, I mean updateusage. Will you please tell me what it is?|||Check the BoL entry for details. The short answer is that usage stats are not necessarily accurate in SS 2K - databases. Fixed for SS2005. updateusage updates the stats to ensure you are looking at accurate info.
USE my_db
GO
DBCC UPDATEUSAGE (0)Once complete then rerun the code I supplied. We can then be assured the results are correct.|||Check the BoL entry for details. The short answer is that usage stats are not necessarily accurate in SS 2K - databases. Fixed for SS2005. updateusage updates the stats to ensure you are looking at accurate info.
USE my_db
GO
DBCC UPDATEUSAGE (0)Once complete then rerun the code I supplied. We can then be assured the results are correct.|||I executed both codes but no change in result. It shows the same as before.|||I executed both codes but no change in result. It shows the same as before.Good stuff.
I forgot about sysfiles (that's what comes of using 2005 for 6 months)...
How do you get on with the below? Substitute file_name with the name of the first file (not database) you want to shrink.
USE my_db
GO
SELECT name,size
FROM sysfiles
GO
DBCC shrinkfile (file_name, 0)
GO
SELECT name,size
FROM sysfiles
GO
Some other questions - is this database we are shrinking no longer used for writing data to? Do you run dbreindex or indexdefrag? Do you back up this database?|||I find this threaad amusing
What do you mean you "expire" data?
Do mean you delete data?
Then if that's what you are doing, then why do you keep around the other database that is "no longer in use"
And I haven't read anywhere in this thread that says why shrinking the database is not a good idea.
The reason is, that space is allocated, and when you delete the data, the space becomes available, and you don't incur the overhead of shrinking, AND growth.
And how come nobody asked about the transaction log size?|||I *love* useless advice.
Chintan: If I could help you, I would-but I'm faced with the same problem & the same lack of useful advice.
This is priceless
Would you like fries with that advice?|||And I haven't read anywhere in this thread that says why shrinking the database is not a good idea.You are kidding right? The first two pages pretty well talk about nothing else.|||You are kidding right? The first two pages pretty well talk about nothing else.
Where?
I just reread it. Nowhere does anyone say that it's just gonna grow again anyway, so why incurr the overhead?
Want tto post a quote where it discusses that?|||if you can't shrink the data, and...
you can't add new disk space, and...
you cannot persuade people of another course of action, then...
basically you are stuffed with a capital F
then Id examine the data that you are archiving... does the full 427Mb db need to be where it is, does it really need to be so close to production (ie could it be archived off in a better mechanism). how often do they access data that is 15 months old., can it be filleted to remove much of the redundancy, or just to keep the data that is actually required
id want to seriously look at table design.. are there columns that are poorly defined in terms of size, are there redundant columns/tables that can go
are there tables or rows that can be dropped within the archive.
but that is not a short term fix, and right now I guess short term fix is what is required. The most immediate short term fix is bigger disk.|||This is a common myth, that you can shrink databases without removing data. It is NOT a zip file, it does not compress.
No amount of sitting on the suitcase will allow it to buckle any easier.
What DBCC SHRINKFILE actually does, is reassign excess space allocated at the end of the file and moves it to the transaction log and doesn't really reduce the size at all until the database is backed up and the transaction logs dumped and then not really at all if you have your database set to allocate 10% growth over your current database size.
You can site this article from Microsoft explaining what "shrinking" the database actually does: DBCC SHRINKFILE (http://msdn2.microsoft.com/en-us/library/aa258824(SQL.80).aspx)
I am going to assume that you ARE backing up the database and dumping the transaction logs when you do so.
Bits of data are not compressible. You can reorganize it, and label it in a way that allows it to be more streamlined and smaller, but those are programming/coding changes to the database itself. You can do performance tuning on your database, and implement some best practices if possible, but you still can not physically reduce the size of a bit of data.
If the requirement is that you MUST keep 15 months of data, then you must have enough space to store 15 months of data. If your company has grown in any way since the inception of the database, then your database will also grow accordingly.
That's what we've been trying to explain. Shrink is a misnomer. Think of it as a full glass of water in a plastic cup. You can put your hand over the top and suck all the air out of the little space above the water. But it does not reduce the water, and as soon as your hand (the 10% growfile function) comes off the top of the glass, you still have a container that is the same size and still has the same amount of water.|||Stop that, Mo.
Damn.
You're supposed to say "Refer to the BOL for details of the SHRINKFILE process". ;)
And you forget that this thread is being posted to by primarily men. Your analogy should refer to a glass of beer to be most effective.
Other than that, good description!|||Only rednecks serve beer in plastic cups! =P|||What? Glasswear at a kegger? That's just askin' for cracked skulls later on in the evening.
Besides, we like to drink our beer from a mason jar.|||fyi, here's a script courtesy of MVJ over on sqlteam that might be of use to you:
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=80355|||Want tto post a quote where it discusses that?Sure:
http://www.dbforums.com/showpost.php?p=6283790&postcount=8489 ;)
We pointed out several reasons why he should avoid doing this if at all possible however we didn't make your point because of this little nugget:
Yes, I have 2 databases. One is currently in use and occupies 202 GB and the other, older one, which is not in use but have to keep data up to 15 month older, occupies appx. 427 GB.|||Liebling - I've read the BoL 2005 entry (which has more detail than B0L 2000 on this) and Inside SQL Server 2005 and that is not my understanding of shrinkfile.|||I haven't read the 2005 entry, but my assumption was that if they were not willing to spring for hard drive space (really cheap) then they were probably also still on 2000. In fact, I think he stated he was on SQL 2000 (http://www.dbforums.com/showpost.php?p=6282845&postcount=25). You can't refer to SQL 2005 BOL's because they aren't the same beast. Similar, but a lot of recoding and changes went into 2005.
In my experience, and according to every SQL 2000 reference to SHRINKFILE I've read thus far, that is exactly what it is doing.|||Ok - reread the 2000 entry. It is, as far as I can tell, the same as the 2005 entry except the 2005 entry is more thorough.
What DBCC SHRINKFILE actually does, is reassign excess space allocated at the end of the file and moves it to the transaction log and doesn't really reduce the size at all until the database is backed up and the transaction logs dumped and then not really at all if you have your database set to allocate 10% growth over your current database size. I don't get that at all - my understanding and what it says in BoL is that pages are moved from the end of the file and allocated to the beginning of the file (depending on various stuff, including the amount of free space indicated when running the dbcc command, size of model and min size of the db). There is no mention of moving stuff from one file to another except when emptyfile is specified.
Ultimately the mechanism behind shrinkfile is (as I see it) something like indexdefrag. It simply moves pages within the file around, just for a different purpose and using a different algorithm. Nothing within pages changes so the total used space remains constant.|||Right, it moves the pages that are not complete around a bit. Most of those pages are actually placeholders created by the grow function from my understanding. But yes, much like defrag it still doesn't really change the size of the container just the places the data is stored. It never shrinks the data.
It doesn't actually say that it moves anything to the transaction log file, but run it and see how much your TL grows. It's writing a refrence to where it was to where it is going in my understanding.|||Right, it moves the pages that are not complete around a bit. It moves any pages that fall in the part of the file to be "emptied" around irrespective of their contents. It just sees them as 8kb blocks. (TMK of course).
It doesn't actually say that it moves anything to the transaction log file, but run it and see how much your TL grows. It's writing a refrence to where it was to where it is going in my understanding.Way ahead of you :) Wrote the below this lunch break.
SET NOCOUNT ON
GO
USE master
go
CREATE DATABASE pf_test
GO
USE pf_test
GO
ALTER DATABASE pf_test SET RECOVERY SIMPLE
GO
CREATE TABLE dbo.size_info
(
order_col TINYINT IDENTITY(1, 1)
, stage VARCHAR(20)
, name NVARCHAR(15)
, size_mb DECIMAL(8, 5)
, free_space_mb DECIMAL(8, 5)
)
GO
INSERT INTO dbo.size_info
SELECT 'Initial', name, size * (8/1024.0), (size * (8/1024.0)) - fileproperty(name,'SpaceUsed') * (8/ 1024.0)
FROM dbo.sysfiles
ORDER BY name
CREATE TABLE dbo.dummy_table
(
number INT
, dummy_data VARCHAR(7990)
)
GO
INSERT INTO dbo.dummy_table
SELECT number, REPLICATE('a', number)
FROM admin.dbo.numbers
WHERE number BETWEEN 1 AND 7990
GO
INSERT INTO dbo.size_info
SELECT 'Full', name, size * (8/1024.0), (size * (8/1024.0)) - fileproperty(name,'SpaceUsed') * (8/ 1024.0)
FROM dbo.sysfiles
ORDER BY name
GO
DELETE
FROM dbo.dummy_table
GO
INSERT INTO dbo.size_info
SELECT 'Post Delete', name, size * (8/1024.0), (size * (8/1024.0)) - fileproperty(name,'SpaceUsed') * (8/ 1024.0)
FROM dbo.sysfiles
ORDER BY name
GO
DBCC SHRINKFILE (pf_test, 0) WITH NO_INFOMSGS
GO
INSERT INTO dbo.size_info
SELECT 'Data Shrunk', name, size * (8/1024.0), (size * (8/1024.0)) - fileproperty(name,'SpaceUsed') * (8/ 1024.0)
FROM dbo.sysfiles
ORDER BY name
GO
DBCC SHRINKFILE (pf_test_log, 0) WITH NO_INFOMSGS
GO
INSERT INTO dbo.size_info
SELECT 'Log Shrunk', name, size * (8/1024.0), (size * (8/1024.0)) - fileproperty(name,'SpaceUsed') * (8/ 1024.0)
FROM dbo.sysfiles
ORDER BY name
GO
SELECT *
FROM dbo.size_info
ORDER BY order_col
GO
USE master
go
DROP DATABASE pf_test
GO
----
--take 2
USE master
go
CREATE DATABASE pf_test
GO
USE pf_test
GO
ALTER DATABASE pf_test SET RECOVERY FULL
GO
CREATE TABLE dbo.size_info
(
order_col TINYINT IDENTITY(1, 1)
, stage VARCHAR(20)
, name NVARCHAR(15)
, size_mb DECIMAL(8, 5)
, free_space_mb DECIMAL(8, 5)
)
GO
INSERT INTO dbo.size_info
SELECT 'Initial', name, size * (8/1024.0), (size * (8/1024.0)) - fileproperty(name,'SpaceUsed') * (8/ 1024.0)
FROM dbo.sysfiles
ORDER BY name
CREATE TABLE dbo.dummy_table
(
number INT
, dummy_data VARCHAR(7990)
)
GO
INSERT INTO dbo.dummy_table
SELECT number, REPLICATE('a', number)
FROM admin.dbo.numbers
WHERE number BETWEEN 1 AND 7990
GO
INSERT INTO dbo.size_info
SELECT 'Full', name, size * (8/1024.0), (size * (8/1024.0)) - fileproperty(name,'SpaceUsed') * (8/ 1024.0)
FROM dbo.sysfiles
ORDER BY name
GO
DELETE
FROM dbo.dummy_table
GO
INSERT INTO dbo.size_info
SELECT 'Post Delete', name, size * (8/1024.0), (size * (8/1024.0)) - fileproperty(name,'SpaceUsed') * (8/ 1024.0)
FROM dbo.sysfiles
ORDER BY name
GO
DBCC SHRINKFILE (pf_test, 0) WITH NO_INFOMSGS
GO
INSERT INTO dbo.size_info
SELECT 'Data Shrunk', name, size * (8/1024.0), (size * (8/1024.0)) - fileproperty(name,'SpaceUsed') * (8/ 1024.0)
FROM dbo.sysfiles
ORDER BY name
GO
DBCC SHRINKFILE (pf_test_log, 0) WITH NO_INFOMSGS
GO
INSERT INTO dbo.size_info
SELECT 'Log Shrunk', name, size * (8/1024.0), (size * (8/1024.0)) - fileproperty(name,'SpaceUsed') * (8/ 1024.0)
FROM dbo.sysfiles
ORDER BY name
GO
SELECT *
FROM dbo.size_info
ORDER BY order_col
GO
USE master
go
DROP DATABASE pf_test
GO Produces (depends on your model settings of course):
order_col stage name size_mb free_space_mb
--- ------- ----- ------------ ------------
1 Initial pf_test 2.18736 0.81245
2 Initial pf_test_log 0.54684 0.27342
3 Full pf_test 42.18480 0.74995
4 Full pf_test_log 42.93475 7.91356
5 Post Delete pf_test 42.18480 27.62323
6 Post Delete pf_test_log 111.74285 42.98162
7 Data Shrunk pf_test 2.18736 0.81245
8 Data Shrunk pf_test_log 111.74285 101.15759
9 Log Shrunk pf_test 2.18736 0.81245
10 Log Shrunk pf_test_log 0.54684 0.31248
order_col stage name size_mb free_space_mb
--- ------- ----- ------------ ------------
1 Initial pf_test 2.18736 0.81245
2 Initial pf_test_log 0.54684 0.28123
3 Full pf_test 42.18480 0.74995
4 Full pf_test_log 42.93475 7.91356
5 Post Delete pf_test 42.18480 27.62323
6 Post Delete pf_test_log 111.74285 42.98944
7 Data Shrunk pf_test 2.18736 0.81245
8 Data Shrunk pf_test_log 111.74285 101.16540
9 Log Shrunk pf_test 2.18736 0.81245
10 Log Shrunk pf_test_log 0.54684 0.31248|||Once again I would be grateful if someone could enlighten me on copying & pasting from SSMS to this flipping page whilst retaining the formatting. Ideally not mocking me this time.|||Once again I would be grateful if someone could enlighten me on copying & pasting from SSMS to this flipping page whilst retaining the formatting. Ideally not mocking me this time.
I'm betting it's a browser issue. I use firefox and I've never been able to preserve the colorization of code either. I assume that's what you are referring to? probably it only works with IE.|||I am an IEer. Works fine on SQLTeam not here. And it was the same at my old job and now this one, both QA and SSMS. Poor Poots :o|||Good stuff.
I forgot about sysfiles (that's what comes of using 2005 for 6 months)...
How do you get on with the below? Substitute file_name with the name of the first file (not database) you want to shrink.
USE my_db
GO
SELECT name,size
FROM sysfiles
GO
DBCC shrinkfile (file_name, 0)
GO
SELECT name,size
FROM sysfiles
GO
Some other questions - is this database we are shrinking no longer used for writing data to? Do you run dbreindex or indexdefrag? Do you back up this database?
I use above code for 2 days and for around 20 hrs. but it hardly frees 4-5 MBs. So it is also not working properly.
And this database is not used for writing only used for report viewing. As having less memory, we dont hv backup of this database.
No, I dont run dbreindex or indexdefrag as it is for faster retrival of data but we dont face any problem of retrival.|||What about back ups?|||What about back ups?
As I told in post no.58 that we dont hv backup as having less free space.|||As I told in post no.58 that we dont hv backup as having less free space.
Well, that has got to be as good a reason not to buy more disk space....
If you go to the Access Forum, I'm sure they'll have an answer for you|||Access forum? But I am using SQL. then how can they know my problem?|||Access forum? But I am using SQL. then how can they know my problem?Don't even try to make sense out of Brett's coment... It makes sense in a larger context (based on many different threads), but its just outright bizzare in the context of just this one thread.
Brett's having one of those mornings... Or maybe having three or four of those mornings, all in one day!
-PatP|||1. You don't have any backups, and it sounds like a VLDB...how quaint
2. You don't try the code in the link from Kristen...how clever
3. You ignore all the advice from everyone here in the SQL Server forum
I just thought maybe all of the sophisticated programmers in the Access forum might have the answer you are looking for
Nomad|||you'd have a lot more free space if you just dropped the database.
you could just tell your boss that it got irreversibly corrupted, and since you don't have a backup, that should be fine, right?
I mean, they probably expect to lose the data entirely one day through some mishap, since you don't have backups.
read: maybe you should think about creating a backup.|||A brief synopsis for those just now joining our story:
1st Skydiver: How can I shrink my parachute?
Instructor: Don't shrink your parachute.
1st Skydiver: I don't have enough room in my pack for it.
Instructor: Get a bigger pack.
2nd Skydiver: Try cutting a big wedge out of one side.
Instructor: Don't shrink your parachute. It is as big as it needs to be.
1st Skydiver: My pack has plenty of space. I can still fit a sandwich in it for lunch.
2nd Skydiver: ...or you could just cut a large hole in the center. That would probably work too.
Instructor: Better make sure your reserve parachute is working before you do that.
1st Skydiver: I don't have a reserve chute. I told you, I don't have enough space in my pack.
Instructor: Geronimo!|||Dude: They have company policies about data retention but you don't have a backup? That has GOT to take the cake.|||For SQL Server files which won't shrink, try changing the backup method to Full, then shrink, then change it to simple, then shrink. It works!! I guarantee it. I had the same problem with SQL db's not shrinking and discovered this hiccup by Microsoft.|||For SQL Server files which won't shrink, try changing the backup method to Full, then shrink, then change it to simple, then shrink. It works!! I guarantee it. I had the same problem with SQL db's not shrinking and discovered this hiccup by Microsoft.
I dont know what is full backup. BTW, i told that we dont take backup of that older database. If u had the same problem, how u solved it? And I am not allowed to take backup of that database.|||Read posts 61 (http://www.dbforums.com/showpost.php?p=6284483&postcount=61) and 66 (http://www.dbforums.com/showpost.php?p=6284560&postcount=66) :)|||Wow, this was fun to catch up on after a week on the beach, thanks guys! It's a relief to know that the good threads have staying power.
After n posts saying "I can't do anything you guys suggest, but I still need you to tell me how to fix my issue", if the reader/contributor can resist the urge to throw up his/her hands in frustration, and if he/she can distance him/herself emotionally, a thread becomes almost a source of glee.
This is especially true when a parachuting analogy can be successfully applied.|||Where d'ya go? Stop by the corral and fill us insql
I have database in Microsoft SQL Server and its size is appx. 427 GB. It consists of data from 2006. Everyday I expire data of 2-3 days and then try to shrink database as much as possible.
But it frees only 2-3 MB after 7-8 hours process. And in that database there some files which do not shrink even I put one of them on shrinking for whole day.
And as per company policy, I am only allowed to expire data which are 15 months older.
I am facing very much problem as my daily import and publish activity some times got failed for that reason.
If anyone knows the solution please tell me the solution.The simple answer would be don't shrink it. Shrinking db files is really not a good idea especially on a regular basis.
Moving thread to proper forum.|||Then what should I do?|||Then what should I do?Don't shrink the file :)
Are there any pressing needs for why you are shrinking? Is this an active database with ongoing modifications?|||Yes, I have 2 databases. One is currently in use and occupies 202 GB and the other, older one, which is not in use but have to keep data up to 15 month older, occupies appx. 427 GB.
I have 9 drives of 100 GB and there is restriction that each drive must have 10% free space. As daily input to new database done, it grows rapidly but older one doesn't frees occupied space even after expiring data older then 15 months.
Thus, my application which does daily import and publish activity get failed.
That's why I shrink the database.
Please tell me the solution for that.|||Buy more disks. I am serious - why fit your database to your server not the other way round?
10% free space is rather low too. You can't even do a proper physical defrag AFAIK.
Check out the BoL entry for shrinking databases and files. Paul Randal (SQL Server enginge lead at MS) ensured that some of the disadvantages of this approach made it into the manual. If you try googling you will find hundreds of examples of people wnating to do what you want to do and being advised not to do it.|||Thanks for serious advice! But this is not my pc or my company's pc but it is my company's clients server pc.
And in near future they have no plan to add any drive as there is much total free space. But problem is that on some drive it has 50 GB free space and on some drives only 12 GB.
I also tried to move datafiles but some files are more than 50 GB so cant move to other drive.
So still any serious advice?|||Listen to the Flump. The Flump speaks Wisdom.
Do.
Not.
Shrink.
Database.
I wish Microsoft had not even included database shrinking as an option.
Tell.
Your.
Client.
To.
Buy.
More.
Disk.
Space.|||I *love* useless advice.
Listen to Chintan. *He* needs to shrink the database-and nobody will tell him how. Doesn't matter whether it's a good idea or not. He's been told to shrink the database. He has two options-shrink the database or leave his job. Your advice boils down to option 2: leave his job because they're telling him to do something that *you* don't think is a good idea.
I'm not arguing whether or not shrinking the database is a good idea or not, but I am arguing that ignoring Chintan's problem is a *bad* idea. For him, at least.
Chintan: If I could help you, I would-but I'm faced with the same problem & the same lack of useful advice.|||I *love* useless advice.
Listen to Chintan. *He* needs to shrink the database-and nobody will tell him how. Doesn't matter whether it's a good idea or not. He's been told to shrink the database. He has two options-shrink the database or leave his job. Your advice boils down to option 2: leave his job because they're telling him to do something that *you* don't think is a good idea.
I'm not arguing whether or not shrinking the database is a good idea or not, but I am arguing that ignoring Chintan's problem is a *bad* idea. For him, at least.
Chintan: If I could help you, I would-but I'm faced with the same problem & the same lack of useful advice.
It's NOT useless advice. It is sound, practical advice.
The only thing I would add to it would go something like this:
What you face is not a technical issue. It is a business issue. Business people respond better to technical problems when they are presented in business terms. This usually means presenting an issue and couching it in terms of cost versus benefit.
I would approach the problem in this fashion:
John [or my manager's name],
I am technically unable to shrink the database because of limitations in MS SQL Server. There are several ways to deal with this issue:
1. We can do nothing. Eventually the application will fail because there is insufficient storage. This will cost the business no money, but the downside is that the application will no longer function and users will not have access to the most recent data.
2. We can add disk space to the existing server. This would involve purchasing XX disks at YY cost per disk for a total of ZZZ dollars. I do/do not have experience installing these disks and so there is/is not additional cost associated with bringing in a technician to do the installation.
3. We can selectively eliminate more data than what is being purged now. Instead of C years' retention, we can instead choose B years' retention. This will/will not put us in violation of selected audit rules/business requirements. Once more data has been eliminated, it may be possible for me to shrink the database effectively.
My recommendation would be to implement Option X above.
I look forward to working through this issue with you.
Regards,
Don B. Azalea
My advice:
1. Never approach your manager with a business problem without having at least two solutions that you have thought through.
2. Always include the option to do nothing. It at least rephrases the problem in terms that a manager can comprehend.
3. Learn to speak in terms of Return on Investment. It will make you a more well-rounded (and hence valuable) person in your operation.
4. Never talk down to your boss/supervisor. They make decisions that affect your paycheck. Speak to them in terms that they can understand, but never assume that they are ignorant. Try to step out of your world on occasion and see things from their point of view. It can be very enlightening.
Good luck.
By the way, I agree with Pootle and Blindman here: shrinking the db is NOT the way to go.
Regards,
hmscott
Edited for clarity.|||Pootle Flump...nice sig!!!
:D
hmscott|||Chintan: If I could help you, I would-but I'm faced with the same problem & the same lack of useful advice.Hint: It's not the advice that is useless...|||sometimes, late at night, when the wind is still and the water on the pond is a smooth black mirror, you can hear the Flump calling out, wailing a sad song of how you shouldn't shrink databases.
who will listen?|||If the advice doesn't solve the problem, then it's useless-IMO. The problem isn't whether or not to shrink the database-that's already been decided (and not at my level). If I disagree with that decision I can quit or be fired.
The problem is *how* to shrink the database. Your advice doesn't solve that, so it's useless. JMHO.|||Should have addressed hmscott's reply, too. That was a nice reply & would be useful in other situations. Doesn't work here as the decision has already been made. Stating that I'm unable to shrink the database would work if I actually were unable to shrink it-but that's not the case. Both my boss & his boss know that the database can be shrunk so that approach simply tells them that *I* can't shrink it-and that's likely to get me fired. (Don't know about where you work but being unable to do something you were hired to do is grounds for dismissal here.)|||part of my job is to provide managers, users and developers with what i consider the best practices and the best options when it comes to all matters database. if i do not do that, then I am failing them by not providing them the best solution possible.
i understand that money might not always be there for the hardware I desire or require, but it is my job to point out the need. from what i understand you can mitigate the effects of the shrink with a little index defrag or or reindex. however it seems part of the problem is the time the shrink takes and the process will be even longer if you add this step in.
so thems be the options mr. manager. buy me some disks, or i am going to bog down my server by doing some expensive shrinking and defragging. do DBAs get fired? I never recall seeing it happen. half decent ones seem to hard to find. I have certainly behaved in such a way i would have fired myself once or twice.|||so thems be the options mr. manager. buy me some disks, or i am going to bog down my server by doing some expensive shrinking and defragging. do DBAs get fired? I never recall seeing it happen. half decent ones seem to hard to find. I have certainly behaved in such a way i would have fired myself once or twice.
:D
I have never done such a thing. never
Well, maybe once...there was a time when I dropped the entire schema for our data warehouse...but that's a tale for a different day.
Regards,
hmscott|||Should have addressed hmscott's reply, too. That was a nice reply & would be useful in other situations. Doesn't work here as the decision has already been made. Stating that I'm unable to shrink the database would work if I actually were unable to shrink it-but that's not the case. Both my boss & his boss know that the database can be shrunk so that approach simply tells them that *I* can't shrink it-and that's likely to get me fired. (Don't know about where you work but being unable to do something you were hired to do is grounds for dismissal here.)
Don't know what your situation is, so I can't address it directly.
Conditions under which I can understand and recommend that a database be shrunk:
1. You've purged a "significant" amount of data from the data files (through truncate or delete or drop). You do not expect to re-add this data anytime soon.
NB: "Significant" is subjective here, it can be any value, but I would generally look for anything greater than 25%.
2. You've had an issue with a log file filling up and extending beyond your intended size limit.
Some ways of shrinking your database...
Instead of using DBCC SHRINK DATABASE, consider using DBCC SHRINKFILE. This can be less expensive in terms of I/O on the disk.
You might also consider deleting some indexes in an attempt to trade off performance for space.
You might consider moving some data to another server, making a linked server and accessing the moved data through a linked server query.
On a side note, if you think that you might lose your job on account of this one issue, then your job is already lost. I do not mean this to be facetious or rude. You must have sufficient confidence in your skills and trust in your boss to be able to admit to ignorance of a particular issue and be able to get the help you need to fix it. If that confidence isn't there or the trust isn't there, then it's only a matter of time before your boss will find someone who has the skills or in whom he can trust.
Just my .02. It's worth what you paid for it.
Regards,
hmscott|||Well, I agree with your comments about the job. Long term either I'll be gone or my boss will, but that doesn't mean I want to (actually, can afford to) leave right now.
I appreciate your advice & will look into SHRINKFILE. Somehow overlooked that while I was checking on SHRINK DATABASE.
Thrasy, essentially those options were presented to my manager-who responded that buying more disks was out. He knows the database can be shrunk but it's up to me to actually do it and all the advice I've found so far is "don't do it". Even though I agree with it, it's still useless because I can't follow it & still keep my job.|||No problem. FWIW, it appears that I will soon be moving on though not by my choice nor by my manager's choice.
Long story, but it sucks 'cause I like it here.
Regards,
hmscott|||Calvin, obviously your boss does not respect your opinion or advice.
And after reading your posts in this thread, equally obvious to me is WHY he doesn't respect your advice.
Pootle gave good advice. The fact that your boss refuses to listen to the advice of some of the best DBAs in the world, coupled with your labeling that advice as "useless", makes me think that you must be a pretty good fit for your current position of employment. I can see why you are so keen on keeping it.
Basically, if your boss refuses to listen to you that is not Pootle's problem, and it is not my problem, and it is not SQL Server's problem. Who's left?|||Guys! Guys! Guys!
Lots of reply, thanks for that especially Calvin-c, flump and hmscott.
I got confused after reading all replies.
My actual problem is:
-> How to more effectively shrink SQL database?
-> Is there any alternate way through which I can get free spaces after expiring?
As I told there is appx. 270 GB free space available but in scattered form means in one drive there is available free space is 40 GB while in other 12 GB. And sizes of datafiles are very big so I can't move them from one drive to another.
So I don't think that I have any option rather that going for shrinking database however it is not preferable.
And as there is lots of free space, so I can't demand for more disk space in any condition.
So still any serious advice?|||Do you know how much data you actually delete? You know it is more than a few MB yes? Also what commands are you executing?
Just in case I forget to mention later - you need to reindex after a shrink too as you will have fragmented your indexes and the database will run poorly for many reporting applications.|||Also - what version? Is this 2000 or a database that has been upgraded to 2005? If so please run DBCC UPDATEUSAGE|||I use SQL 2000. And everyday first I expire data oldest 2-3 days data using min (timestamp) query.
Appx. 3 GB of data per day, however it depends on the log file for that day but on the average it shows reduced size of that database up to 6 to 9 GB.
In expiring, I dont face any problem but after then when I shrink database. There are total 36 tables and 24 tables get shrunken successfully but 12 tables do not shrink and those tables use much of the memory. And at the end it frees hardly 3-4 MB.
If you know how to shrink those 12 tables then most probably my problem will be solved.|||What code are you running to get the used and available space for your database? And what code do you run to shrink it?|||I recently had to shrink a 50 gig database after major clean up (previous company was ...). It went from 50 to 1 when I was done, but I had to re-run the shrink probably 100 times over a weekend before it was done. Also, I was able to do it before going live with the db.
I also would switch back and forth between shrinkfile and shrinkdatabase. Sometimes it would free up nothing, and then every 10 times or so it would give me back a couple gigs.|||I use expiredata command from DOS to expire database for specified date. However this command is only available through application I use.
And after then I shrink the tables one by one from SQL Server Enterprise Manager -> shrink database.
I have also tried shrinkdatabase and shrinktable but they dont work.|||This is what I use but I only need to deal with 2 GB database since I have my clients running MSDE Rel A. I don't know how long it will take on a 250 GB database.
HTH
backup log MyDataBase with truncate_only
dbcc loginfo
go
dbcc shrinkfile (MyDataBase_log);
dbcc loginfo
go
dbcc shrinkfile (MyDataBase_DATA);
dbcc loginfo
go|||I use expiredata command from DOS to expire database for specified date. However this command is only available through application I use.
Presumably that just deletes data from tables?
And after then I shrink the tables one by one from SQL Server Enterprise Manager -> shrink database.
I have also tried shrinkdatabase and shrinktable but they dont work.
Well - you don't shrink tables you shrink files\ databases.
Did you run updateusage?
What do you get if you run this:
USE my_db
go
CREATE TABLE #helpfile
(
name sysname
, fileid smallint
, filename nvarchar(256)
, filegroup sysname NULL
, size nvarchar(18)
, maxsize nvarchar(18)
, growth nvarchar(18)
, usage varchar(9)
)
INSERT INTO #helpfile
EXEC dbo.sp_helpfile
SELECT *
, file_size_mb - space_used_mb AS spare_space
FROM (
SELECT name
, usage
, CAST(SUBSTRING(size, 1, CHARINDEX(' ', size) - 1) AS INT) / 1024 AS file_size_mb
, CAST(fileproperty(name,'SpaceUsed') * (8/ 1024.0) AS INT) AS space_used_mb
FROM #helpfile
) AS der_t
DROP TABLE #helpfile|||It shows all datafiles and spare space. The total space is 24813 MB. And there are 4 files which have spare space more than 5000 MB. So if I manage with there files, my problem will automatically be solved.
But main problem is that as I earlier said; when I shrink these files it hardly frees 1-2 MB after whole day process.|||Can you confirm you ran updateusage before running that script?|||What is updatemanage? I don't know what it is? Will you please tell me what it is and how will it be useful to me?|||Sorry, I mean updateusage. Will you please tell me what it is?|||Check the BoL entry for details. The short answer is that usage stats are not necessarily accurate in SS 2K - databases. Fixed for SS2005. updateusage updates the stats to ensure you are looking at accurate info.
USE my_db
GO
DBCC UPDATEUSAGE (0)Once complete then rerun the code I supplied. We can then be assured the results are correct.|||Check the BoL entry for details. The short answer is that usage stats are not necessarily accurate in SS 2K - databases. Fixed for SS2005. updateusage updates the stats to ensure you are looking at accurate info.
USE my_db
GO
DBCC UPDATEUSAGE (0)Once complete then rerun the code I supplied. We can then be assured the results are correct.|||I executed both codes but no change in result. It shows the same as before.|||I executed both codes but no change in result. It shows the same as before.Good stuff.
I forgot about sysfiles (that's what comes of using 2005 for 6 months)...
How do you get on with the below? Substitute file_name with the name of the first file (not database) you want to shrink.
USE my_db
GO
SELECT name,size
FROM sysfiles
GO
DBCC shrinkfile (file_name, 0)
GO
SELECT name,size
FROM sysfiles
GO
Some other questions - is this database we are shrinking no longer used for writing data to? Do you run dbreindex or indexdefrag? Do you back up this database?|||I find this threaad amusing
What do you mean you "expire" data?
Do mean you delete data?
Then if that's what you are doing, then why do you keep around the other database that is "no longer in use"
And I haven't read anywhere in this thread that says why shrinking the database is not a good idea.
The reason is, that space is allocated, and when you delete the data, the space becomes available, and you don't incur the overhead of shrinking, AND growth.
And how come nobody asked about the transaction log size?|||I *love* useless advice.
Chintan: If I could help you, I would-but I'm faced with the same problem & the same lack of useful advice.
This is priceless
Would you like fries with that advice?|||And I haven't read anywhere in this thread that says why shrinking the database is not a good idea.You are kidding right? The first two pages pretty well talk about nothing else.|||You are kidding right? The first two pages pretty well talk about nothing else.
Where?
I just reread it. Nowhere does anyone say that it's just gonna grow again anyway, so why incurr the overhead?
Want tto post a quote where it discusses that?|||if you can't shrink the data, and...
you can't add new disk space, and...
you cannot persuade people of another course of action, then...
basically you are stuffed with a capital F
then Id examine the data that you are archiving... does the full 427Mb db need to be where it is, does it really need to be so close to production (ie could it be archived off in a better mechanism). how often do they access data that is 15 months old., can it be filleted to remove much of the redundancy, or just to keep the data that is actually required
id want to seriously look at table design.. are there columns that are poorly defined in terms of size, are there redundant columns/tables that can go
are there tables or rows that can be dropped within the archive.
but that is not a short term fix, and right now I guess short term fix is what is required. The most immediate short term fix is bigger disk.|||This is a common myth, that you can shrink databases without removing data. It is NOT a zip file, it does not compress.
No amount of sitting on the suitcase will allow it to buckle any easier.
What DBCC SHRINKFILE actually does, is reassign excess space allocated at the end of the file and moves it to the transaction log and doesn't really reduce the size at all until the database is backed up and the transaction logs dumped and then not really at all if you have your database set to allocate 10% growth over your current database size.
You can site this article from Microsoft explaining what "shrinking" the database actually does: DBCC SHRINKFILE (http://msdn2.microsoft.com/en-us/library/aa258824(SQL.80).aspx)
I am going to assume that you ARE backing up the database and dumping the transaction logs when you do so.
Bits of data are not compressible. You can reorganize it, and label it in a way that allows it to be more streamlined and smaller, but those are programming/coding changes to the database itself. You can do performance tuning on your database, and implement some best practices if possible, but you still can not physically reduce the size of a bit of data.
If the requirement is that you MUST keep 15 months of data, then you must have enough space to store 15 months of data. If your company has grown in any way since the inception of the database, then your database will also grow accordingly.
That's what we've been trying to explain. Shrink is a misnomer. Think of it as a full glass of water in a plastic cup. You can put your hand over the top and suck all the air out of the little space above the water. But it does not reduce the water, and as soon as your hand (the 10% growfile function) comes off the top of the glass, you still have a container that is the same size and still has the same amount of water.|||Stop that, Mo.
Damn.
You're supposed to say "Refer to the BOL for details of the SHRINKFILE process". ;)
And you forget that this thread is being posted to by primarily men. Your analogy should refer to a glass of beer to be most effective.
Other than that, good description!|||Only rednecks serve beer in plastic cups! =P|||What? Glasswear at a kegger? That's just askin' for cracked skulls later on in the evening.
Besides, we like to drink our beer from a mason jar.|||fyi, here's a script courtesy of MVJ over on sqlteam that might be of use to you:
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=80355|||Want tto post a quote where it discusses that?Sure:
http://www.dbforums.com/showpost.php?p=6283790&postcount=8489 ;)
We pointed out several reasons why he should avoid doing this if at all possible however we didn't make your point because of this little nugget:
Yes, I have 2 databases. One is currently in use and occupies 202 GB and the other, older one, which is not in use but have to keep data up to 15 month older, occupies appx. 427 GB.|||Liebling - I've read the BoL 2005 entry (which has more detail than B0L 2000 on this) and Inside SQL Server 2005 and that is not my understanding of shrinkfile.|||I haven't read the 2005 entry, but my assumption was that if they were not willing to spring for hard drive space (really cheap) then they were probably also still on 2000. In fact, I think he stated he was on SQL 2000 (http://www.dbforums.com/showpost.php?p=6282845&postcount=25). You can't refer to SQL 2005 BOL's because they aren't the same beast. Similar, but a lot of recoding and changes went into 2005.
In my experience, and according to every SQL 2000 reference to SHRINKFILE I've read thus far, that is exactly what it is doing.|||Ok - reread the 2000 entry. It is, as far as I can tell, the same as the 2005 entry except the 2005 entry is more thorough.
What DBCC SHRINKFILE actually does, is reassign excess space allocated at the end of the file and moves it to the transaction log and doesn't really reduce the size at all until the database is backed up and the transaction logs dumped and then not really at all if you have your database set to allocate 10% growth over your current database size. I don't get that at all - my understanding and what it says in BoL is that pages are moved from the end of the file and allocated to the beginning of the file (depending on various stuff, including the amount of free space indicated when running the dbcc command, size of model and min size of the db). There is no mention of moving stuff from one file to another except when emptyfile is specified.
Ultimately the mechanism behind shrinkfile is (as I see it) something like indexdefrag. It simply moves pages within the file around, just for a different purpose and using a different algorithm. Nothing within pages changes so the total used space remains constant.|||Right, it moves the pages that are not complete around a bit. Most of those pages are actually placeholders created by the grow function from my understanding. But yes, much like defrag it still doesn't really change the size of the container just the places the data is stored. It never shrinks the data.
It doesn't actually say that it moves anything to the transaction log file, but run it and see how much your TL grows. It's writing a refrence to where it was to where it is going in my understanding.|||Right, it moves the pages that are not complete around a bit. It moves any pages that fall in the part of the file to be "emptied" around irrespective of their contents. It just sees them as 8kb blocks. (TMK of course).
It doesn't actually say that it moves anything to the transaction log file, but run it and see how much your TL grows. It's writing a refrence to where it was to where it is going in my understanding.Way ahead of you :) Wrote the below this lunch break.
SET NOCOUNT ON
GO
USE master
go
CREATE DATABASE pf_test
GO
USE pf_test
GO
ALTER DATABASE pf_test SET RECOVERY SIMPLE
GO
CREATE TABLE dbo.size_info
(
order_col TINYINT IDENTITY(1, 1)
, stage VARCHAR(20)
, name NVARCHAR(15)
, size_mb DECIMAL(8, 5)
, free_space_mb DECIMAL(8, 5)
)
GO
INSERT INTO dbo.size_info
SELECT 'Initial', name, size * (8/1024.0), (size * (8/1024.0)) - fileproperty(name,'SpaceUsed') * (8/ 1024.0)
FROM dbo.sysfiles
ORDER BY name
CREATE TABLE dbo.dummy_table
(
number INT
, dummy_data VARCHAR(7990)
)
GO
INSERT INTO dbo.dummy_table
SELECT number, REPLICATE('a', number)
FROM admin.dbo.numbers
WHERE number BETWEEN 1 AND 7990
GO
INSERT INTO dbo.size_info
SELECT 'Full', name, size * (8/1024.0), (size * (8/1024.0)) - fileproperty(name,'SpaceUsed') * (8/ 1024.0)
FROM dbo.sysfiles
ORDER BY name
GO
DELETE
FROM dbo.dummy_table
GO
INSERT INTO dbo.size_info
SELECT 'Post Delete', name, size * (8/1024.0), (size * (8/1024.0)) - fileproperty(name,'SpaceUsed') * (8/ 1024.0)
FROM dbo.sysfiles
ORDER BY name
GO
DBCC SHRINKFILE (pf_test, 0) WITH NO_INFOMSGS
GO
INSERT INTO dbo.size_info
SELECT 'Data Shrunk', name, size * (8/1024.0), (size * (8/1024.0)) - fileproperty(name,'SpaceUsed') * (8/ 1024.0)
FROM dbo.sysfiles
ORDER BY name
GO
DBCC SHRINKFILE (pf_test_log, 0) WITH NO_INFOMSGS
GO
INSERT INTO dbo.size_info
SELECT 'Log Shrunk', name, size * (8/1024.0), (size * (8/1024.0)) - fileproperty(name,'SpaceUsed') * (8/ 1024.0)
FROM dbo.sysfiles
ORDER BY name
GO
SELECT *
FROM dbo.size_info
ORDER BY order_col
GO
USE master
go
DROP DATABASE pf_test
GO
----
--take 2
USE master
go
CREATE DATABASE pf_test
GO
USE pf_test
GO
ALTER DATABASE pf_test SET RECOVERY FULL
GO
CREATE TABLE dbo.size_info
(
order_col TINYINT IDENTITY(1, 1)
, stage VARCHAR(20)
, name NVARCHAR(15)
, size_mb DECIMAL(8, 5)
, free_space_mb DECIMAL(8, 5)
)
GO
INSERT INTO dbo.size_info
SELECT 'Initial', name, size * (8/1024.0), (size * (8/1024.0)) - fileproperty(name,'SpaceUsed') * (8/ 1024.0)
FROM dbo.sysfiles
ORDER BY name
CREATE TABLE dbo.dummy_table
(
number INT
, dummy_data VARCHAR(7990)
)
GO
INSERT INTO dbo.dummy_table
SELECT number, REPLICATE('a', number)
FROM admin.dbo.numbers
WHERE number BETWEEN 1 AND 7990
GO
INSERT INTO dbo.size_info
SELECT 'Full', name, size * (8/1024.0), (size * (8/1024.0)) - fileproperty(name,'SpaceUsed') * (8/ 1024.0)
FROM dbo.sysfiles
ORDER BY name
GO
DELETE
FROM dbo.dummy_table
GO
INSERT INTO dbo.size_info
SELECT 'Post Delete', name, size * (8/1024.0), (size * (8/1024.0)) - fileproperty(name,'SpaceUsed') * (8/ 1024.0)
FROM dbo.sysfiles
ORDER BY name
GO
DBCC SHRINKFILE (pf_test, 0) WITH NO_INFOMSGS
GO
INSERT INTO dbo.size_info
SELECT 'Data Shrunk', name, size * (8/1024.0), (size * (8/1024.0)) - fileproperty(name,'SpaceUsed') * (8/ 1024.0)
FROM dbo.sysfiles
ORDER BY name
GO
DBCC SHRINKFILE (pf_test_log, 0) WITH NO_INFOMSGS
GO
INSERT INTO dbo.size_info
SELECT 'Log Shrunk', name, size * (8/1024.0), (size * (8/1024.0)) - fileproperty(name,'SpaceUsed') * (8/ 1024.0)
FROM dbo.sysfiles
ORDER BY name
GO
SELECT *
FROM dbo.size_info
ORDER BY order_col
GO
USE master
go
DROP DATABASE pf_test
GO Produces (depends on your model settings of course):
order_col stage name size_mb free_space_mb
--- ------- ----- ------------ ------------
1 Initial pf_test 2.18736 0.81245
2 Initial pf_test_log 0.54684 0.27342
3 Full pf_test 42.18480 0.74995
4 Full pf_test_log 42.93475 7.91356
5 Post Delete pf_test 42.18480 27.62323
6 Post Delete pf_test_log 111.74285 42.98162
7 Data Shrunk pf_test 2.18736 0.81245
8 Data Shrunk pf_test_log 111.74285 101.15759
9 Log Shrunk pf_test 2.18736 0.81245
10 Log Shrunk pf_test_log 0.54684 0.31248
order_col stage name size_mb free_space_mb
--- ------- ----- ------------ ------------
1 Initial pf_test 2.18736 0.81245
2 Initial pf_test_log 0.54684 0.28123
3 Full pf_test 42.18480 0.74995
4 Full pf_test_log 42.93475 7.91356
5 Post Delete pf_test 42.18480 27.62323
6 Post Delete pf_test_log 111.74285 42.98944
7 Data Shrunk pf_test 2.18736 0.81245
8 Data Shrunk pf_test_log 111.74285 101.16540
9 Log Shrunk pf_test 2.18736 0.81245
10 Log Shrunk pf_test_log 0.54684 0.31248|||Once again I would be grateful if someone could enlighten me on copying & pasting from SSMS to this flipping page whilst retaining the formatting. Ideally not mocking me this time.|||Once again I would be grateful if someone could enlighten me on copying & pasting from SSMS to this flipping page whilst retaining the formatting. Ideally not mocking me this time.
I'm betting it's a browser issue. I use firefox and I've never been able to preserve the colorization of code either. I assume that's what you are referring to? probably it only works with IE.|||I am an IEer. Works fine on SQLTeam not here. And it was the same at my old job and now this one, both QA and SSMS. Poor Poots :o|||Good stuff.
I forgot about sysfiles (that's what comes of using 2005 for 6 months)...
How do you get on with the below? Substitute file_name with the name of the first file (not database) you want to shrink.
USE my_db
GO
SELECT name,size
FROM sysfiles
GO
DBCC shrinkfile (file_name, 0)
GO
SELECT name,size
FROM sysfiles
GO
Some other questions - is this database we are shrinking no longer used for writing data to? Do you run dbreindex or indexdefrag? Do you back up this database?
I use above code for 2 days and for around 20 hrs. but it hardly frees 4-5 MBs. So it is also not working properly.
And this database is not used for writing only used for report viewing. As having less memory, we dont hv backup of this database.
No, I dont run dbreindex or indexdefrag as it is for faster retrival of data but we dont face any problem of retrival.|||What about back ups?|||What about back ups?
As I told in post no.58 that we dont hv backup as having less free space.|||As I told in post no.58 that we dont hv backup as having less free space.
Well, that has got to be as good a reason not to buy more disk space....
If you go to the Access Forum, I'm sure they'll have an answer for you|||Access forum? But I am using SQL. then how can they know my problem?|||Access forum? But I am using SQL. then how can they know my problem?Don't even try to make sense out of Brett's coment... It makes sense in a larger context (based on many different threads), but its just outright bizzare in the context of just this one thread.
Brett's having one of those mornings... Or maybe having three or four of those mornings, all in one day!
-PatP|||1. You don't have any backups, and it sounds like a VLDB...how quaint
2. You don't try the code in the link from Kristen...how clever
3. You ignore all the advice from everyone here in the SQL Server forum
I just thought maybe all of the sophisticated programmers in the Access forum might have the answer you are looking for
Nomad|||you'd have a lot more free space if you just dropped the database.
you could just tell your boss that it got irreversibly corrupted, and since you don't have a backup, that should be fine, right?
I mean, they probably expect to lose the data entirely one day through some mishap, since you don't have backups.
read: maybe you should think about creating a backup.|||A brief synopsis for those just now joining our story:
1st Skydiver: How can I shrink my parachute?
Instructor: Don't shrink your parachute.
1st Skydiver: I don't have enough room in my pack for it.
Instructor: Get a bigger pack.
2nd Skydiver: Try cutting a big wedge out of one side.
Instructor: Don't shrink your parachute. It is as big as it needs to be.
1st Skydiver: My pack has plenty of space. I can still fit a sandwich in it for lunch.
2nd Skydiver: ...or you could just cut a large hole in the center. That would probably work too.
Instructor: Better make sure your reserve parachute is working before you do that.
1st Skydiver: I don't have a reserve chute. I told you, I don't have enough space in my pack.
Instructor: Geronimo!|||Dude: They have company policies about data retention but you don't have a backup? That has GOT to take the cake.|||For SQL Server files which won't shrink, try changing the backup method to Full, then shrink, then change it to simple, then shrink. It works!! I guarantee it. I had the same problem with SQL db's not shrinking and discovered this hiccup by Microsoft.|||For SQL Server files which won't shrink, try changing the backup method to Full, then shrink, then change it to simple, then shrink. It works!! I guarantee it. I had the same problem with SQL db's not shrinking and discovered this hiccup by Microsoft.
I dont know what is full backup. BTW, i told that we dont take backup of that older database. If u had the same problem, how u solved it? And I am not allowed to take backup of that database.|||Read posts 61 (http://www.dbforums.com/showpost.php?p=6284483&postcount=61) and 66 (http://www.dbforums.com/showpost.php?p=6284560&postcount=66) :)|||Wow, this was fun to catch up on after a week on the beach, thanks guys! It's a relief to know that the good threads have staying power.
After n posts saying "I can't do anything you guys suggest, but I still need you to tell me how to fix my issue", if the reader/contributor can resist the urge to throw up his/her hands in frustration, and if he/she can distance him/herself emotionally, a thread becomes almost a source of glee.
This is especially true when a parachuting analogy can be successfully applied.|||Where d'ya go? Stop by the corral and fill us insql
How to shrink my database
hello
i know how to do shrinking DBCC SHRINKDATABASE( XXX,10).
But my question is how we know minumum shrinking Database value through stroed procedure.
please help me i need to shrink Database.
thanx
kiran
Kiran,
With DBCC SHRINKDATABASE, you specify the percentage of free space to leave after the shrink. i.e. : in your example,"10" will leave about 10 % free space after the shrink.
DBCC SHRINKFILE allows you to shrink a specific file within the database (i.e. : the transaction log or one of the data files). If the value you specify for target size is less than the minimum size, it will shrink the file to the minimum possible, therefore, if you specify "1", then it will shrink to the minimum possible.
Examples ;
DBCC SHRINKDATABASE (northwind, 10) -- Leaves about 10 % free space after the shrink
DBCC SHRINKFILE (datafile, 5) -- Shrinks "datafile" to 5 MB (or the minimum possible if more than 5 MB used)
Rod.
|||thanx for you response rod.
but i have some small question can u we put defaulut "1" every time
like DBCC SHRINKDATABASE (northwind, 1) ?
regards
kiran
|||I wouldn't do that if I were you. If your autogrow property is not enabled, you end up with a non-functional database in no time.How to shrink log file.
Hi, I have a database in SQL 2000 server whose
Transaction log file size is over 5GB but dats file size
just 300mb. (x.mdf = 300mb and x.ldf =5gb)That data base
is not frequently used and I wanted to shrink the size to
save some disk space.In the properties of this log
file, 'unrestricted growth' is enabled.
Could some one help me to do it.
Thanks and regards, JohnHi,
Have a look into the below article,
INF: Shrinking the Transaction Log in SQL Server 2000 with DBCC SHRINKFILE
http://support.microsoft.com/defaul...kb;en-us;272318
http://www.support.microsoft.com/?id=315512
Thanks
Hari
MCDBA
"John" <justforu@.freesurf.ch> wrote in message
news:47c701c3ff6a$6e7a9750$a301280a@.phx.gbl...
> Hi, I have a database in SQL 2000 server whose
> Transaction log file size is over 5GB but dats file size
> just 300mb. (x.mdf = 300mb and x.ldf =5gb)That data base
> is not frequently used and I wanted to shrink the size to
> save some disk space.In the properties of this log
> file, 'unrestricted growth' is enabled.
> Could some one help me to do it.
> Thanks and regards, John|||backup transaction log
and then run dbcc shrinkfile(x_log, TRUNCATEONLY)
The truncateonly causes the unused space to be released to the Operating
System (Check oout BOL for dbcc shrinkfile)
Olu Adedeji
"John" <justforu@.freesurf.ch> wrote in message
news:47c701c3ff6a$6e7a9750$a301280a@.phx.gbl...
> Hi, I have a database in SQL 2000 server whose
> Transaction log file size is over 5GB but dats file size
> just 300mb. (x.mdf = 300mb and x.ldf =5gb)That data base
> is not frequently used and I wanted to shrink the size to
> save some disk space.In the properties of this log
> file, 'unrestricted growth' is enabled.
> Could some one help me to do it.
> Thanks and regards, John
Transaction log file size is over 5GB but dats file size
just 300mb. (x.mdf = 300mb and x.ldf =5gb)That data base
is not frequently used and I wanted to shrink the size to
save some disk space.In the properties of this log
file, 'unrestricted growth' is enabled.
Could some one help me to do it.
Thanks and regards, JohnHi,
Have a look into the below article,
INF: Shrinking the Transaction Log in SQL Server 2000 with DBCC SHRINKFILE
http://support.microsoft.com/defaul...kb;en-us;272318
http://www.support.microsoft.com/?id=315512
Thanks
Hari
MCDBA
"John" <justforu@.freesurf.ch> wrote in message
news:47c701c3ff6a$6e7a9750$a301280a@.phx.gbl...
> Hi, I have a database in SQL 2000 server whose
> Transaction log file size is over 5GB but dats file size
> just 300mb. (x.mdf = 300mb and x.ldf =5gb)That data base
> is not frequently used and I wanted to shrink the size to
> save some disk space.In the properties of this log
> file, 'unrestricted growth' is enabled.
> Could some one help me to do it.
> Thanks and regards, John|||backup transaction log
and then run dbcc shrinkfile(x_log, TRUNCATEONLY)
The truncateonly causes the unused space to be released to the Operating
System (Check oout BOL for dbcc shrinkfile)
Olu Adedeji
"John" <justforu@.freesurf.ch> wrote in message
news:47c701c3ff6a$6e7a9750$a301280a@.phx.gbl...
> Hi, I have a database in SQL 2000 server whose
> Transaction log file size is over 5GB but dats file size
> just 300mb. (x.mdf = 300mb and x.ldf =5gb)That data base
> is not frequently used and I wanted to shrink the size to
> save some disk space.In the properties of this log
> file, 'unrestricted growth' is enabled.
> Could some one help me to do it.
> Thanks and regards, John
How to shrink log file.
Hi, I have a database in SQL 2000 server whose
Transaction log file size is over 5GB but dats file size
just 300mb. (x.mdf = 300mb and x.ldf =5gb)That data base
is not frequently used and I wanted to shrink the size to
save some disk space.In the properties of this log
file, 'unrestricted growth' is enabled.
Could some one help me to do it.
Thanks and regards, JohnHi,
Have a look into the below article,
INF: Shrinking the Transaction Log in SQL Server 2000 with DBCC SHRINKFILE
http://support.microsoft.com/default.aspx?scid=kb;en-us;272318
http://www.support.microsoft.com/?id=315512
Thanks
Hari
MCDBA
"John" <justforu@.freesurf.ch> wrote in message
news:47c701c3ff6a$6e7a9750$a301280a@.phx.gbl...
> Hi, I have a database in SQL 2000 server whose
> Transaction log file size is over 5GB but dats file size
> just 300mb. (x.mdf = 300mb and x.ldf =5gb)That data base
> is not frequently used and I wanted to shrink the size to
> save some disk space.In the properties of this log
> file, 'unrestricted growth' is enabled.
> Could some one help me to do it.
> Thanks and regards, John|||backup transaction log
and then run dbcc shrinkfile(x_log, TRUNCATEONLY)
The truncateonly causes the unused space to be released to the Operating
System (Check oout BOL for dbcc shrinkfile)
--
Olu Adedeji
"John" <justforu@.freesurf.ch> wrote in message
news:47c701c3ff6a$6e7a9750$a301280a@.phx.gbl...
> Hi, I have a database in SQL 2000 server whose
> Transaction log file size is over 5GB but dats file size
> just 300mb. (x.mdf = 300mb and x.ldf =5gb)That data base
> is not frequently used and I wanted to shrink the size to
> save some disk space.In the properties of this log
> file, 'unrestricted growth' is enabled.
> Could some one help me to do it.
> Thanks and regards, John|||Yes it worked. Thanks a lot.
>--Original Message--
>Hi, I have a database in SQL 2000 server whose
>Transaction log file size is over 5GB but dats file size
>just 300mb. (x.mdf = 300mb and x.ldf =5gb)That data base
>is not frequently used and I wanted to shrink the size
to
>save some disk space.In the properties of this log
>file, 'unrestricted growth' is enabled.
>Could some one help me to do it.
>Thanks and regards, John
>.
>|||>>Hi, I have a database in SQL 2000 server whose
>>Transaction log file size is over 20GB but mdf file size
>>just 4GB. That data base
>>is frequently used and I wanted to shrink the size
>to
>>save some disk space.Moreover I want to delete the old
transactions of this particular log file. pls guide me how
to do this.
Regards
Triveni|||Hi!!
I have a database of around 116GB and somehow I lost the
> log file now if I want to do any transaction, it simply
> says log file is not available as I lost the log
> file.Kindly help me to get rid of this problem
any help would be highly appreciated.
>
>--Original Message--
>Yes it worked. Thanks a lot.
>>--Original Message--
>>Hi, I have a database in SQL 2000 server whose
>>Transaction log file size is over 5GB but dats file size
>>just 300mb. (x.mdf = 300mb and x.ldf =5gb)That data base
>>is not frequently used and I wanted to shrink the size
>to
>>save some disk space.In the properties of this log
>>file, 'unrestricted growth' is enabled.
>>Could some one help me to do it.
>>Thanks and regards, John
>>.
>.
>
Transaction log file size is over 5GB but dats file size
just 300mb. (x.mdf = 300mb and x.ldf =5gb)That data base
is not frequently used and I wanted to shrink the size to
save some disk space.In the properties of this log
file, 'unrestricted growth' is enabled.
Could some one help me to do it.
Thanks and regards, JohnHi,
Have a look into the below article,
INF: Shrinking the Transaction Log in SQL Server 2000 with DBCC SHRINKFILE
http://support.microsoft.com/default.aspx?scid=kb;en-us;272318
http://www.support.microsoft.com/?id=315512
Thanks
Hari
MCDBA
"John" <justforu@.freesurf.ch> wrote in message
news:47c701c3ff6a$6e7a9750$a301280a@.phx.gbl...
> Hi, I have a database in SQL 2000 server whose
> Transaction log file size is over 5GB but dats file size
> just 300mb. (x.mdf = 300mb and x.ldf =5gb)That data base
> is not frequently used and I wanted to shrink the size to
> save some disk space.In the properties of this log
> file, 'unrestricted growth' is enabled.
> Could some one help me to do it.
> Thanks and regards, John|||backup transaction log
and then run dbcc shrinkfile(x_log, TRUNCATEONLY)
The truncateonly causes the unused space to be released to the Operating
System (Check oout BOL for dbcc shrinkfile)
--
Olu Adedeji
"John" <justforu@.freesurf.ch> wrote in message
news:47c701c3ff6a$6e7a9750$a301280a@.phx.gbl...
> Hi, I have a database in SQL 2000 server whose
> Transaction log file size is over 5GB but dats file size
> just 300mb. (x.mdf = 300mb and x.ldf =5gb)That data base
> is not frequently used and I wanted to shrink the size to
> save some disk space.In the properties of this log
> file, 'unrestricted growth' is enabled.
> Could some one help me to do it.
> Thanks and regards, John|||Yes it worked. Thanks a lot.
>--Original Message--
>Hi, I have a database in SQL 2000 server whose
>Transaction log file size is over 5GB but dats file size
>just 300mb. (x.mdf = 300mb and x.ldf =5gb)That data base
>is not frequently used and I wanted to shrink the size
to
>save some disk space.In the properties of this log
>file, 'unrestricted growth' is enabled.
>Could some one help me to do it.
>Thanks and regards, John
>.
>|||>>Hi, I have a database in SQL 2000 server whose
>>Transaction log file size is over 20GB but mdf file size
>>just 4GB. That data base
>>is frequently used and I wanted to shrink the size
>to
>>save some disk space.Moreover I want to delete the old
transactions of this particular log file. pls guide me how
to do this.
Regards
Triveni|||Hi!!
I have a database of around 116GB and somehow I lost the
> log file now if I want to do any transaction, it simply
> says log file is not available as I lost the log
> file.Kindly help me to get rid of this problem
any help would be highly appreciated.
>
>--Original Message--
>Yes it worked. Thanks a lot.
>>--Original Message--
>>Hi, I have a database in SQL 2000 server whose
>>Transaction log file size is over 5GB but dats file size
>>just 300mb. (x.mdf = 300mb and x.ldf =5gb)That data base
>>is not frequently used and I wanted to shrink the size
>to
>>save some disk space.In the properties of this log
>>file, 'unrestricted growth' is enabled.
>>Could some one help me to do it.
>>Thanks and regards, John
>>.
>.
>
How to shrink log file manually
Hello !
Is there any t-sql statement to shrink log file?
--
S.JCheck out DBCC SHRINKFILE. Also, about in the middle for below article, you find some links
regarding shrinking of log files:
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/
"MS" <fastaccess@.hotmail.com> wrote in message news:Ouue84ChEHA.1356@.TK2MSFTNGP09.phx.gbl...
> Hello !
> Is there any t-sql statement to shrink log file?
> --
> S.J
>
Is there any t-sql statement to shrink log file?
--
S.JCheck out DBCC SHRINKFILE. Also, about in the middle for below article, you find some links
regarding shrinking of log files:
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/
"MS" <fastaccess@.hotmail.com> wrote in message news:Ouue84ChEHA.1356@.TK2MSFTNGP09.phx.gbl...
> Hello !
> Is there any t-sql statement to shrink log file?
> --
> S.J
>
How to shrink log file manually
Hello !
Is there any t-sql statement to shrink log file?
S.J
Check out DBCC SHRINKFILE. Also, about in the middle for below article, you find some links
regarding shrinking of log files:
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/
"MS" <fastaccess@.hotmail.com> wrote in message news:Ouue84ChEHA.1356@.TK2MSFTNGP09.phx.gbl...
> Hello !
> Is there any t-sql statement to shrink log file?
> --
> S.J
>
sql
Is there any t-sql statement to shrink log file?
S.J
Check out DBCC SHRINKFILE. Also, about in the middle for below article, you find some links
regarding shrinking of log files:
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/
"MS" <fastaccess@.hotmail.com> wrote in message news:Ouue84ChEHA.1356@.TK2MSFTNGP09.phx.gbl...
> Hello !
> Is there any t-sql statement to shrink log file?
> --
> S.J
>
sql
How to shrink log file manually
Hello !
Is there any t-sql statement to shrink log file?
S.JCheck out DBCC SHRINKFILE. Also, about in the middle for below article, you
find some links
regarding shrinking of log files:
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/
"MS" <fastaccess@.hotmail.com> wrote in message news:Ouue84ChEHA.1356@.TK2MSFTNGP09.phx.gbl...
> Hello !
> Is there any t-sql statement to shrink log file?
> --
> S.J
>
Is there any t-sql statement to shrink log file?
S.JCheck out DBCC SHRINKFILE. Also, about in the middle for below article, you
find some links
regarding shrinking of log files:
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/
"MS" <fastaccess@.hotmail.com> wrote in message news:Ouue84ChEHA.1356@.TK2MSFTNGP09.phx.gbl...
> Hello !
> Is there any t-sql statement to shrink log file?
> --
> S.J
>
How to shrink db
We have a SQL 2005 server. There is a db which isabout 20g, but about 80%
of it is empty. How can I shrink the data file not using the GUI?Hello,
Take a look into DBCC SHRINKFILE and DBCC SHRINKDB in books online.
DBCC SHRINKFILE - Allows you to shrink the database file level
DBCC SHRINKDB - Shrinks the database [both data and log files]
Thanks
Hari
"news.microsoft.com" <Xeon@.donotemailme.com> wrote in message
news:eYuc6nzFHHA.3616@.TK2MSFTNGP06.phx.gbl...
> We have a SQL 2005 server. There is a db which isabout 20g, but about 80%
> of it is empty. How can I shrink the data file not using the GUI?
>|||Is it normal for DBCC SHRINKFILE to take a long time? I have 20G DB which
is 80% empty space.
"Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
news:eqW6%23g0FHHA.3976@.TK2MSFTNGP05.phx.gbl...
> Hello,
> Take a look into DBCC SHRINKFILE and DBCC SHRINKDB in books online.
> DBCC SHRINKFILE - Allows you to shrink the database file level
> DBCC SHRINKDB - Shrinks the database [both data and log files]
> Thanks
> Hari
> "news.microsoft.com" <Xeon@.donotemailme.com> wrote in message
> news:eYuc6nzFHHA.3616@.TK2MSFTNGP06.phx.gbl...
>> We have a SQL 2005 server. There is a db which isabout 20g, but about
>> 80% of it is empty. How can I shrink the data file not using the GUI?
>>
>|||Hello,
It is faster. I will recommend to shrink in smaller size. First shrink to 15
GB then to 10 GB and finally to 5 GB.
Thanks
Hari
"news.microsoft.com" <Xeon@.donotemailme.com> wrote in message
news:ec76tC1FHHA.1784@.TK2MSFTNGP06.phx.gbl...
> Is it normal for DBCC SHRINKFILE to take a long time? I have 20G DB which
> is 80% empty space.
> "Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
> news:eqW6%23g0FHHA.3976@.TK2MSFTNGP05.phx.gbl...
>> Hello,
>> Take a look into DBCC SHRINKFILE and DBCC SHRINKDB in books online.
>> DBCC SHRINKFILE - Allows you to shrink the database file level
>> DBCC SHRINKDB - Shrinks the database [both data and log files]
>> Thanks
>> Hari
>> "news.microsoft.com" <Xeon@.donotemailme.com> wrote in message
>> news:eYuc6nzFHHA.3616@.TK2MSFTNGP06.phx.gbl...
>> We have a SQL 2005 server. There is a db which isabout 20g, but about
>> 80% of it is empty. How can I shrink the data file not using the GUI?
>>
>>
>
of it is empty. How can I shrink the data file not using the GUI?Hello,
Take a look into DBCC SHRINKFILE and DBCC SHRINKDB in books online.
DBCC SHRINKFILE - Allows you to shrink the database file level
DBCC SHRINKDB - Shrinks the database [both data and log files]
Thanks
Hari
"news.microsoft.com" <Xeon@.donotemailme.com> wrote in message
news:eYuc6nzFHHA.3616@.TK2MSFTNGP06.phx.gbl...
> We have a SQL 2005 server. There is a db which isabout 20g, but about 80%
> of it is empty. How can I shrink the data file not using the GUI?
>|||Is it normal for DBCC SHRINKFILE to take a long time? I have 20G DB which
is 80% empty space.
"Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
news:eqW6%23g0FHHA.3976@.TK2MSFTNGP05.phx.gbl...
> Hello,
> Take a look into DBCC SHRINKFILE and DBCC SHRINKDB in books online.
> DBCC SHRINKFILE - Allows you to shrink the database file level
> DBCC SHRINKDB - Shrinks the database [both data and log files]
> Thanks
> Hari
> "news.microsoft.com" <Xeon@.donotemailme.com> wrote in message
> news:eYuc6nzFHHA.3616@.TK2MSFTNGP06.phx.gbl...
>> We have a SQL 2005 server. There is a db which isabout 20g, but about
>> 80% of it is empty. How can I shrink the data file not using the GUI?
>>
>|||Hello,
It is faster. I will recommend to shrink in smaller size. First shrink to 15
GB then to 10 GB and finally to 5 GB.
Thanks
Hari
"news.microsoft.com" <Xeon@.donotemailme.com> wrote in message
news:ec76tC1FHHA.1784@.TK2MSFTNGP06.phx.gbl...
> Is it normal for DBCC SHRINKFILE to take a long time? I have 20G DB which
> is 80% empty space.
> "Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
> news:eqW6%23g0FHHA.3976@.TK2MSFTNGP05.phx.gbl...
>> Hello,
>> Take a look into DBCC SHRINKFILE and DBCC SHRINKDB in books online.
>> DBCC SHRINKFILE - Allows you to shrink the database file level
>> DBCC SHRINKDB - Shrinks the database [both data and log files]
>> Thanks
>> Hari
>> "news.microsoft.com" <Xeon@.donotemailme.com> wrote in message
>> news:eYuc6nzFHHA.3616@.TK2MSFTNGP06.phx.gbl...
>> We have a SQL 2005 server. There is a db which isabout 20g, but about
>> 80% of it is empty. How can I shrink the data file not using the GUI?
>>
>>
>
How to shrink db
We have a SQL 2005 server. There is a db which isabout 20g, but about 80%
of it is empty. How can I shrink the data file not using the GUI?
Hello,
Take a look into DBCC SHRINKFILE and DBCC SHRINKDB in books online.
DBCC SHRINKFILE - Allows you to shrink the database file level
DBCC SHRINKDB - Shrinks the database [both data and log files]
Thanks
Hari
"news.microsoft.com" <Xeon@.donotemailme.com> wrote in message
news:eYuc6nzFHHA.3616@.TK2MSFTNGP06.phx.gbl...
> We have a SQL 2005 server. There is a db which isabout 20g, but about 80%
> of it is empty. How can I shrink the data file not using the GUI?
>
|||Is it normal for DBCC SHRINKFILE to take a long time? I have 20G DB which
is 80% empty space.
"Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
news:eqW6%23g0FHHA.3976@.TK2MSFTNGP05.phx.gbl...
> Hello,
> Take a look into DBCC SHRINKFILE and DBCC SHRINKDB in books online.
> DBCC SHRINKFILE - Allows you to shrink the database file level
> DBCC SHRINKDB - Shrinks the database [both data and log files]
> Thanks
> Hari
> "news.microsoft.com" <Xeon@.donotemailme.com> wrote in message
> news:eYuc6nzFHHA.3616@.TK2MSFTNGP06.phx.gbl...
>
|||Hello,
It is faster. I will recommend to shrink in smaller size. First shrink to 15
GB then to 10 GB and finally to 5 GB.
Thanks
Hari
"news.microsoft.com" <Xeon@.donotemailme.com> wrote in message
news:ec76tC1FHHA.1784@.TK2MSFTNGP06.phx.gbl...
> Is it normal for DBCC SHRINKFILE to take a long time? I have 20G DB which
> is 80% empty space.
> "Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
> news:eqW6%23g0FHHA.3976@.TK2MSFTNGP05.phx.gbl...
>
of it is empty. How can I shrink the data file not using the GUI?
Hello,
Take a look into DBCC SHRINKFILE and DBCC SHRINKDB in books online.
DBCC SHRINKFILE - Allows you to shrink the database file level
DBCC SHRINKDB - Shrinks the database [both data and log files]
Thanks
Hari
"news.microsoft.com" <Xeon@.donotemailme.com> wrote in message
news:eYuc6nzFHHA.3616@.TK2MSFTNGP06.phx.gbl...
> We have a SQL 2005 server. There is a db which isabout 20g, but about 80%
> of it is empty. How can I shrink the data file not using the GUI?
>
|||Is it normal for DBCC SHRINKFILE to take a long time? I have 20G DB which
is 80% empty space.
"Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
news:eqW6%23g0FHHA.3976@.TK2MSFTNGP05.phx.gbl...
> Hello,
> Take a look into DBCC SHRINKFILE and DBCC SHRINKDB in books online.
> DBCC SHRINKFILE - Allows you to shrink the database file level
> DBCC SHRINKDB - Shrinks the database [both data and log files]
> Thanks
> Hari
> "news.microsoft.com" <Xeon@.donotemailme.com> wrote in message
> news:eYuc6nzFHHA.3616@.TK2MSFTNGP06.phx.gbl...
>
|||Hello,
It is faster. I will recommend to shrink in smaller size. First shrink to 15
GB then to 10 GB and finally to 5 GB.
Thanks
Hari
"news.microsoft.com" <Xeon@.donotemailme.com> wrote in message
news:ec76tC1FHHA.1784@.TK2MSFTNGP06.phx.gbl...
> Is it normal for DBCC SHRINKFILE to take a long time? I have 20G DB which
> is 80% empty space.
> "Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
> news:eqW6%23g0FHHA.3976@.TK2MSFTNGP05.phx.gbl...
>
How to shrink db
We have a SQL 2005 server. There is a db which isabout 20g, but about 80%
of it is empty. How can I shrink the data file not using the GUI?Hello,
Take a look into DBCC SHRINKFILE and DBCC SHRINKDB in books online.
DBCC SHRINKFILE - Allows you to shrink the database file level
DBCC SHRINKDB - Shrinks the database [both data and log files]
Thanks
Hari
"news.microsoft.com" <Xeon@.donotemailme.com> wrote in message
news:eYuc6nzFHHA.3616@.TK2MSFTNGP06.phx.gbl...
> We have a SQL 2005 server. There is a db which isabout 20g, but about 80%
> of it is empty. How can I shrink the data file not using the GUI?
>|||Is it normal for DBCC SHRINKFILE to take a long time? I have 20G DB which
is 80% empty space.
"Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
news:eqW6%23g0FHHA.3976@.TK2MSFTNGP05.phx.gbl...
> Hello,
> Take a look into DBCC SHRINKFILE and DBCC SHRINKDB in books online.
> DBCC SHRINKFILE - Allows you to shrink the database file level
> DBCC SHRINKDB - Shrinks the database [both data and log files]
> Thanks
> Hari
> "news.microsoft.com" <Xeon@.donotemailme.com> wrote in message
> news:eYuc6nzFHHA.3616@.TK2MSFTNGP06.phx.gbl...
>|||Hello,
It is faster. I will recommend to shrink in smaller size. First shrink to 15
GB then to 10 GB and finally to 5 GB.
Thanks
Hari
"news.microsoft.com" <Xeon@.donotemailme.com> wrote in message
news:ec76tC1FHHA.1784@.TK2MSFTNGP06.phx.gbl...
> Is it normal for DBCC SHRINKFILE to take a long time? I have 20G DB which
> is 80% empty space.
> "Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
> news:eqW6%23g0FHHA.3976@.TK2MSFTNGP05.phx.gbl...
>
of it is empty. How can I shrink the data file not using the GUI?Hello,
Take a look into DBCC SHRINKFILE and DBCC SHRINKDB in books online.
DBCC SHRINKFILE - Allows you to shrink the database file level
DBCC SHRINKDB - Shrinks the database [both data and log files]
Thanks
Hari
"news.microsoft.com" <Xeon@.donotemailme.com> wrote in message
news:eYuc6nzFHHA.3616@.TK2MSFTNGP06.phx.gbl...
> We have a SQL 2005 server. There is a db which isabout 20g, but about 80%
> of it is empty. How can I shrink the data file not using the GUI?
>|||Is it normal for DBCC SHRINKFILE to take a long time? I have 20G DB which
is 80% empty space.
"Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
news:eqW6%23g0FHHA.3976@.TK2MSFTNGP05.phx.gbl...
> Hello,
> Take a look into DBCC SHRINKFILE and DBCC SHRINKDB in books online.
> DBCC SHRINKFILE - Allows you to shrink the database file level
> DBCC SHRINKDB - Shrinks the database [both data and log files]
> Thanks
> Hari
> "news.microsoft.com" <Xeon@.donotemailme.com> wrote in message
> news:eYuc6nzFHHA.3616@.TK2MSFTNGP06.phx.gbl...
>|||Hello,
It is faster. I will recommend to shrink in smaller size. First shrink to 15
GB then to 10 GB and finally to 5 GB.
Thanks
Hari
"news.microsoft.com" <Xeon@.donotemailme.com> wrote in message
news:ec76tC1FHHA.1784@.TK2MSFTNGP06.phx.gbl...
> Is it normal for DBCC SHRINKFILE to take a long time? I have 20G DB which
> is 80% empty space.
> "Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
> news:eqW6%23g0FHHA.3976@.TK2MSFTNGP05.phx.gbl...
>
How to shrink a tranlog which wont shrink
If you have problems shrinking a transaction log no matter what commands you issue, here's a way to shrink the tranlog:
1. Right click on the properties of the database and go to the options tab.
2. Change the recovery mode to simple.
3. Right click again on the database - go to all tasks - shrink database.
4. Shrink the database.
5. Change the recovery mode back to what you had it as.
I found this out by trial and error as I could not find any documentation on it and no matter what I tried, I could not get the transaction log to shrink.If your database is in FULL recovery mode this little trick wil break your backup chain!!! You will not be able to do a point-in-time restore (which is the reason you're having your database on FULL) to a moment after you set the db to SIMPLE.
When you do this, make a full backup inmediately afterwards.|||An alternate method is to take a tran log backup after the initial attempt to shrink the file. This should wrap the active portion of the log back to the beginning of the file. So, if you have a runaway transaction log:
1) Backup the log
2) Backup the log again (yes again)
3) dbcc shrinkfile.|||If after doing all the backing up and dbcc shrinkfile STILL doesn't work try
dump tran DATABASENAME with no_log
If this doesn't help then your log file might be corrupt. Not being able to shrink the log file past a certain point might be a sign of a corrupt log file.
Good luck,
Hope this helps|||Giving credit where credit is due, I found this code by Andrew Zanevsky about 5 years ago. It was originally for SQL 7 and has been modified for SQL 2K. I can only remember a few times where it has not done the job.
1st ... execute the code
2nd ... change to the db in which to shrink the log and execute sp_force_shrink_log
use master
go
if object_id( 'sp_force_shrink_log' ) is not null drop proc sp_force_shrink_log
go
create proc sp_force_shrink_log
------------------------
-- Purpose: Shrink transaction log of the current database in SQL Server 7.0.
-- Author: Andrew Zanevsky, AZ Databases, Inc., 12/25/1999, v5 - 08/18/2000
-- zanevsky@.azdatabases.com
------------------------
@.target_percent tinyint = 0,
@.target_size_MB int = 10,
@.max_iterations int = 1000,
@.backup_log_opt nvarchar(1000) = 'with truncate_only'
as
set nocount on
declare @.db sysname,
@.last_row int,
@.log_size decimal(15,2),
@.unused1 decimal(15,2),
@.unused decimal(15,2),
@.shrinkable decimal(15,2),
@.iteration int,
@.file_max int,
@.file int,
@.fileid varchar(5)
select @.db = db_name(),
@.iteration = 0
/*
FileId FileSize StartOffset FSeqNo Status Parity CreateLSN
---- ------- ------- ---- ---- -- --------
2 1245184 8192 925963 0 128 0
*/
create table #loginfo (
id int identity,
FileId int,
FileSize numeric(22,0),
StartOffset numeric(22,0),
FSeqNo int,
Status int,
Parity smallint,
CreateLSN varchar(32)
)
create unique clustered index loginfo_FSeqNo on #loginfo ( FSeqNo, StartOffset )
create table #logfiles ( id int identity(1,1), fileid varchar(5) not null )
insert #logfiles ( fileid ) select convert( varchar, fileid ) from sysfiles where status & 0x40 = 0x40
select @.file_max = @.@.rowcount
if object_id( 'table_to_force_shrink_log' ) is null
exec( 'create table table_to_force_shrink_log ( x nchar(3000) not null )' )
insert #loginfo ( FileId, FileSize, StartOffset, FSeqNo, Status, Parity, CreateLSN ) exec ( 'dbcc loginfo' )
select @.last_row = @.@.rowcount
select @.log_size = sum( FileSize ) / 1048576.00,
@.unused = sum( case when Status = 0 then FileSize else 0 end ) / 1048576.00,
@.shrinkable = sum( case when id < @.last_row - 1 and Status = 0 then FileSize else 0 end ) / 1048576.00
from #loginfo
select @.unused1 = @.unused -- save for later
select 'iteration' = @.iteration,
'log size, MB' = @.log_size,
'unused log, MB' = @.unused,
'shrinkable log, MB' = @.shrinkable,
'shrinkable %' = convert( decimal(6,2), @.shrinkable * 100 / @.log_size )
while @.shrinkable * 100 / @.log_size > @.target_percent
and @.shrinkable > @.target_size_MB
and @.iteration < @.max_iterations begin
select @.iteration = @.iteration + 1 -- this is just a precaution
exec( 'insert table_to_force_shrink_log select name from sysobjects
delete table_to_force_shrink_log')
select @.file = 0
while @.file < @.file_max begin
select @.file = @.file + 1
select @.fileid = fileid from #logfiles where id = @.file
exec( 'dbcc shrinkfile( ' + @.fileid + ' )' )
end
exec( 'backup log [' + @.db + '] ' + @.backup_log_opt )
truncate table #loginfo
insert #loginfo ( FileId, FileSize, StartOffset, FSeqNo, Status, Parity, CreateLSN ) exec ( 'dbcc loginfo' )
select @.last_row = @.@.rowcount
select @.log_size = sum( FileSize ) / 1048576.00,
@.unused = sum( case when Status = 0 then FileSize else 0 end ) / 1048576.00,
@.shrinkable = sum( case when id < @.last_row - 1 and Status = 0 then FileSize else 0 end ) / 1048576.00
from #loginfo
select 'iteration' = @.iteration,
'log size, MB' = @.log_size,
'unused log, MB' = @.unused,
'shrinkable log, MB' = @.shrinkable,
'shrinkable %' = convert( decimal(6,2), @.shrinkable * 100 / @.log_size )
end
if @.unused1 < @.unused
select 'After ' + convert( varchar, @.iteration ) +
' iterations the unused portion of the log has grown from ' +
convert( varchar, @.unused1 ) + ' MB to ' +
convert( varchar, @.unused ) + ' MB.'
union all
select 'Since the remaining unused portion is larger than 10 MB,' where @.unused > 10
union all
select 'you may try running this procedure again with a higher number of iterations.' where @.unused > 10
union all
select 'Sometimes the log would not shrink to a size smaller than several Megabytes.' where @.unused <= 10
else
select 'It took ' + convert( varchar, @.iteration ) +
' iterations to shrink the unused portion of the log from ' +
convert( varchar, @.unused1 ) + ' MB to ' +
convert( varchar, @.unused ) + ' MB'
exec( 'drop table table_to_force_shrink_log' )
gosql
1. Right click on the properties of the database and go to the options tab.
2. Change the recovery mode to simple.
3. Right click again on the database - go to all tasks - shrink database.
4. Shrink the database.
5. Change the recovery mode back to what you had it as.
I found this out by trial and error as I could not find any documentation on it and no matter what I tried, I could not get the transaction log to shrink.If your database is in FULL recovery mode this little trick wil break your backup chain!!! You will not be able to do a point-in-time restore (which is the reason you're having your database on FULL) to a moment after you set the db to SIMPLE.
When you do this, make a full backup inmediately afterwards.|||An alternate method is to take a tran log backup after the initial attempt to shrink the file. This should wrap the active portion of the log back to the beginning of the file. So, if you have a runaway transaction log:
1) Backup the log
2) Backup the log again (yes again)
3) dbcc shrinkfile.|||If after doing all the backing up and dbcc shrinkfile STILL doesn't work try
dump tran DATABASENAME with no_log
If this doesn't help then your log file might be corrupt. Not being able to shrink the log file past a certain point might be a sign of a corrupt log file.
Good luck,
Hope this helps|||Giving credit where credit is due, I found this code by Andrew Zanevsky about 5 years ago. It was originally for SQL 7 and has been modified for SQL 2K. I can only remember a few times where it has not done the job.
1st ... execute the code
2nd ... change to the db in which to shrink the log and execute sp_force_shrink_log
use master
go
if object_id( 'sp_force_shrink_log' ) is not null drop proc sp_force_shrink_log
go
create proc sp_force_shrink_log
------------------------
-- Purpose: Shrink transaction log of the current database in SQL Server 7.0.
-- Author: Andrew Zanevsky, AZ Databases, Inc., 12/25/1999, v5 - 08/18/2000
-- zanevsky@.azdatabases.com
------------------------
@.target_percent tinyint = 0,
@.target_size_MB int = 10,
@.max_iterations int = 1000,
@.backup_log_opt nvarchar(1000) = 'with truncate_only'
as
set nocount on
declare @.db sysname,
@.last_row int,
@.log_size decimal(15,2),
@.unused1 decimal(15,2),
@.unused decimal(15,2),
@.shrinkable decimal(15,2),
@.iteration int,
@.file_max int,
@.file int,
@.fileid varchar(5)
select @.db = db_name(),
@.iteration = 0
/*
FileId FileSize StartOffset FSeqNo Status Parity CreateLSN
---- ------- ------- ---- ---- -- --------
2 1245184 8192 925963 0 128 0
*/
create table #loginfo (
id int identity,
FileId int,
FileSize numeric(22,0),
StartOffset numeric(22,0),
FSeqNo int,
Status int,
Parity smallint,
CreateLSN varchar(32)
)
create unique clustered index loginfo_FSeqNo on #loginfo ( FSeqNo, StartOffset )
create table #logfiles ( id int identity(1,1), fileid varchar(5) not null )
insert #logfiles ( fileid ) select convert( varchar, fileid ) from sysfiles where status & 0x40 = 0x40
select @.file_max = @.@.rowcount
if object_id( 'table_to_force_shrink_log' ) is null
exec( 'create table table_to_force_shrink_log ( x nchar(3000) not null )' )
insert #loginfo ( FileId, FileSize, StartOffset, FSeqNo, Status, Parity, CreateLSN ) exec ( 'dbcc loginfo' )
select @.last_row = @.@.rowcount
select @.log_size = sum( FileSize ) / 1048576.00,
@.unused = sum( case when Status = 0 then FileSize else 0 end ) / 1048576.00,
@.shrinkable = sum( case when id < @.last_row - 1 and Status = 0 then FileSize else 0 end ) / 1048576.00
from #loginfo
select @.unused1 = @.unused -- save for later
select 'iteration' = @.iteration,
'log size, MB' = @.log_size,
'unused log, MB' = @.unused,
'shrinkable log, MB' = @.shrinkable,
'shrinkable %' = convert( decimal(6,2), @.shrinkable * 100 / @.log_size )
while @.shrinkable * 100 / @.log_size > @.target_percent
and @.shrinkable > @.target_size_MB
and @.iteration < @.max_iterations begin
select @.iteration = @.iteration + 1 -- this is just a precaution
exec( 'insert table_to_force_shrink_log select name from sysobjects
delete table_to_force_shrink_log')
select @.file = 0
while @.file < @.file_max begin
select @.file = @.file + 1
select @.fileid = fileid from #logfiles where id = @.file
exec( 'dbcc shrinkfile( ' + @.fileid + ' )' )
end
exec( 'backup log [' + @.db + '] ' + @.backup_log_opt )
truncate table #loginfo
insert #loginfo ( FileId, FileSize, StartOffset, FSeqNo, Status, Parity, CreateLSN ) exec ( 'dbcc loginfo' )
select @.last_row = @.@.rowcount
select @.log_size = sum( FileSize ) / 1048576.00,
@.unused = sum( case when Status = 0 then FileSize else 0 end ) / 1048576.00,
@.shrinkable = sum( case when id < @.last_row - 1 and Status = 0 then FileSize else 0 end ) / 1048576.00
from #loginfo
select 'iteration' = @.iteration,
'log size, MB' = @.log_size,
'unused log, MB' = @.unused,
'shrinkable log, MB' = @.shrinkable,
'shrinkable %' = convert( decimal(6,2), @.shrinkable * 100 / @.log_size )
end
if @.unused1 < @.unused
select 'After ' + convert( varchar, @.iteration ) +
' iterations the unused portion of the log has grown from ' +
convert( varchar, @.unused1 ) + ' MB to ' +
convert( varchar, @.unused ) + ' MB.'
union all
select 'Since the remaining unused portion is larger than 10 MB,' where @.unused > 10
union all
select 'you may try running this procedure again with a higher number of iterations.' where @.unused > 10
union all
select 'Sometimes the log would not shrink to a size smaller than several Megabytes.' where @.unused <= 10
else
select 'It took ' + convert( varchar, @.iteration ) +
' iterations to shrink the unused portion of the log from ' +
convert( varchar, @.unused1 ) + ' MB to ' +
convert( varchar, @.unused ) + ' MB'
exec( 'drop table table_to_force_shrink_log' )
gosql
Subscribe to:
Posts (Atom)