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

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

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

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
>

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
>

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_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 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

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
>>.
>.
>

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
>

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

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
>

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?
>>
>>
>

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...
>

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...
>

how to shrink a file fast

I had a 100GB database and i truncated some few tables and now the database
used is around 20GB.I ran the dbcc shrinkfile to shrink the file to around
40GB and its over 1 hr now and its still running . It is running and not
being blocked. Is there any way to make this run faster ?
Using SQL 2K
Hassan,
If you use TRUNCATEONLY clause with the DBCC command, it will free up
the space but won't move any data around. The drawback is that you might
not meet your shrink target. Worth a try anyway.
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602m.html
Hassan wrote:
> I had a 100GB database and i truncated some few tables and now the database
> used is around 20GB.I ran the dbcc shrinkfile to shrink the file to around
> 40GB and its over 1 hr now and its still running . It is running and not
> being blocked. Is there any way to make this run faster ?
> Using SQL 2K
>

how to shrink a file fast

I had a 100GB database and i truncated some few tables and now the database
used is around 20GB.I ran the dbcc shrinkfile to shrink the file to around
40GB and its over 1 hr now and its still running . It is running and not
being blocked. Is there any way to make this run faster ?
Using SQL 2KHassan,
If you use TRUNCATEONLY clause with the DBCC command, it will free up
the space but won't move any data around. The drawback is that you might
not meet your shrink target. Worth a try anyway.
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602m.html
Hassan wrote:
> I had a 100GB database and i truncated some few tables and now the databas
e
> used is around 20GB.I ran the dbcc shrinkfile to shrink the file to around
> 40GB and its over 1 hr now and its still running . It is running and not
> being blocked. Is there any way to make this run faster ?
> Using SQL 2K
>sql

how to shrink a file fast

I had a 100GB database and i truncated some few tables and now the database
used is around 20GB.I ran the dbcc shrinkfile to shrink the file to around
40GB and its over 1 hr now and its still running . It is running and not
being blocked. Is there any way to make this run faster ?
Using SQL 2KHassan,
If you use TRUNCATEONLY clause with the DBCC command, it will free up
the space but won't move any data around. The drawback is that you might
not meet your shrink target. Worth a try anyway.
--
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602m.html
Hassan wrote:
> I had a 100GB database and i truncated some few tables and now the database
> used is around 20GB.I ran the dbcc shrinkfile to shrink the file to around
> 40GB and its over 1 hr now and its still running . It is running and not
> being blocked. Is there any way to make this run faster ?
> Using SQL 2K
>

How to shrink 38 gig .ldf file

I have Disk Xtender 2000 which was made by OTG Software , Legato and now EMC. I have an NT 4.0 PC with Microsoft SQL 2000. I have a drive space problem and need to shrink a 38 gig .ldf file called OTG03.ldf I also have a 2 gig .mdf file called OTG03.mdf How can I shrink this .ldf file. I'm not a DBA so being specific is greatly appreciated.I got this from a download from a while back. It says 7.0 in the header, but has been modified for SQL 2000. It hads worked for me many, many times.

1. Create the proc in QA
2. Change to the database in which you want to shrink the log file and then execute the proc.

** WARNING **
Use at your own risk ... no warranty applies ... test it first on a database you can afford to trash and restore (the old liability lawyerese)


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' )
go|||Mike,

Backing up your database should shrink your transaction log for you but if you would like to force a shrink on just the ldf file you can use the following:

DBCC SHRINKFILE (OTG03.ldf)

Hope this helps.|||Mike,

Just a clarification, you should replace "OTG03.ldf" with the sql file name. You can find this by right clicking on the database going to properties and on the "Transaction Log" tab.|||I tried DBCC SHRINKFILE (OTG03Log) and got a syntax error.
and also I tried DBCC SHRINKFILE (OTG03.ldf) and got a syntax error.|||I tried DBCC SHRINKFILE (OTG03Log) and got a syntax error.
and also I tried DBCC SHRINKFILE (OTG03.ldf) and got a syntax error.

Use exec sp_helpfile in the database to find the correct value to use with
DBCC SHRINKFILE (?)

Tim S|||Mike,

DBCC SHRINKFILE (OTG03Log) should do the trick for you. Make sure you are running it on your OTG03 database.

Try this:

USE OTG03
GO
DBCC SHRINKFILE (OTG03Log)
GO|||Well ..
If you have no need for the transaction log then you can set the recovery mode to simple and use the following commands
------------
backup log dbname with no_log
go
use OTG03
go
DBCC SHRINKFILE (OTG03Log)
GO

------------

This should do the trick ...
Or for another option ... open the EM ... right click on database name ... select all tasks ... shrink database and then explicitly shrink the log file from there ...

In case you get any error .. revert back with the error text ...

how to shrik log file

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
Larabackup the log first
--
Jose de Jesus Jr. Mcp,Mcdba
Data Architect
Sykes Asia (Manila philippines)
MCP #2324787
"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
>
>|||Lara,
If you go All Tasks->Shrink Database and then there choose between data file
or log file. Just a faster option(if we're talking about the log file)
"Jose G. de Jesus Jr MCP, MCDBA" wrote:
> backup the log first
> --
>
> Jose de Jesus Jr. Mcp,Mcdba
> Data Architect
> Sykes Asia (Manila philippines)
> MCP #2324787
>
> "Lara" wrote:
>|||http://www.aspfaq.com/2471
"Lara" <lara@.hotmail.com> wrote in message
news:OIYF6KKqFHA.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
>|||Have a look at
http://www.karaszi.com/sqlserver/info_dont_shrink.asp
Roji. P. Thomas
Net Asset Management
http://toponewithties.blogspot.com
"Lara" <lara@.hotmail.com> wrote in message
news:OIYF6KKqFHA.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
>|||You can backup the log with the no_log option. For example:
BACKUP LOG [DATABASE NAME] WITH NO_LOG
DBCC SHRINKFILE ([DATABASE NAME], 2)
The active portion of the log cannot be trucated. Keep this in mind, if you
are attemping to trucate in the middle of the day with open transactions or
are attempting to truncate the log during a very large nightly transaction.
If you frequently fill up a large log between trucates, then you may not
want to shrink it down to the absolute minimal size. Keeping the log file
large prevents the need for it to be extended and improves performance.
"Lara" <lara@.hotmail.com> wrote in message
news:OIYF6KKqFHA.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 show progress percent

I have a script file which do the replication and in the script there is a lot of table need to be addad as article.
So while use osql execute the script there would be cost a lot of time. What I want to do is show a dialog which could show the progress percent and the file name which is now being addad as article. when i use osql I just want whenever I execute a stat
ement the osql or the script could return a number!
hwo can i use Transact-SQL to do this?
Thanks a lot
Out of the box for standard initialization this is not possible. If you want
to monitor the standard process you could poll the relevant history table -
MSmerge_history for merge and MSdistribution_history for transactional and
snapshot and filter these results. If you are implementing your own script
to create the tables at the subscriber you could maintain your own counter
and increment it each time a table is added.
HTH,
Paul Ibison
|||the replication ActiveX controls have this functionality. Unfortunately you have to use the status event which is not accessible from a script.
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
"Paul Ibison" wrote:

> Out of the box for standard initialization this is not possible. If you want
> to monitor the standard process you could poll the relevant history table -
> MSmerge_history for merge and MSdistribution_history for transactional and
> snapshot and filter these results. If you are implementing your own script
> to create the tables at the subscriber you could maintain your own counter
> and increment it each time a table is added.
> HTH,
> Paul Ibison
>
>
|||I could maintain my own counter and increment it but how can i return the counter to the outside when executed each one statement ?
In fact, now I want to build a setup program with InstallShield Profession and during the install, a replication should be done. The database which need to be replicated is very big. So I want to show a dialog which show the progress precent to improve th
e setup UI. It is not complex to show a dialog in InstallShield script. but I can not return the counter in the MSSQL script to the InstallShield script, when using the osql utility( like osql ... -i script file name ).
Could you like to give any suggestion?
Thanks a lot
"Paul Ibison" wrote:

> Out of the box for standard initialization this is not possible. If you want
> to monitor the standard process you could poll the relevant history table -
> MSmerge_history for merge and MSdistribution_history for transactional and
> snapshot and filter these results. If you are implementing your own script
> to create the tables at the subscriber you could maintain your own counter
> and increment it each time a table is added.
> HTH,
> Paul Ibison
>
>
|||Lowiq,
you have a few choices. You can have many scripts and get a count by virtue
of the number of scripts processed. If InstallShield is multi-threaded
(don't know offhand), you can set off the osql script asynchronously. The
script would increment a counter in a table each time it adds a table,
populates a table etc. Your main execution thread would poll this counter
table to see the level of progress. Alternatively you could poll the
relevant history table although this would require a bit of complex
filtering. BTW nosync initializations can be a little restrictive as far as
future modifications are concerned.
HTH,
Paul Ibison
"lowiq" <lowiq@.discussions.microsoft.com> wrote in message
news:331E585D-28A2-473A-8B6D-EA38D2A841BA@.microsoft.com...
> I could maintain my own counter and increment it but how can i return the
counter to the outside when executed each one statement ?
> In fact, now I want to build a setup program with InstallShield Profession
and during the install, a replication should be done. The database which
need to be replicated is very big. So I want to show a dialog which show the
progress precent to improve the setup UI. It is not complex to show a dialog
in InstallShield script. but I can not return the counter in the MSSQL
script to the InstallShield script, when using the osql utility( like osql
... -i script file name ).[vbcol=seagreen]
> Could you like to give any suggestion?
> Thanks a lot
> "Paul Ibison" wrote:
want[vbcol=seagreen]
table -[vbcol=seagreen]
and[vbcol=seagreen]
script[vbcol=seagreen]
counter[vbcol=seagreen]