Dear All,
Our database log file in SQL 2000 is growing and filling the disk.
What are the option to reduce the log file size which is existing?
We tried the options :
DBCC Shrinkfile
SP_DBOPTION dbname , 'Truncate log on checkpoint', true.
However the file size remained same.
Are we making any mistake? Which is the best way to reduce the file size and
keep the size to a preset limit (without affecting the trasaction after
reaching the limit)
Regards
SathianHi
http://www.karaszi.com/SQLServer/info_dont_shrink.asp
"Sathian" <sathian.t@.in.bosch.com> wrote in message
news:e75ujo$sfo$1@.ns2.fe.internet.bosch.com...
> Dear All,
> Our database log file in SQL 2000 is growing and filling the disk.
> What are the option to reduce the log file size which is existing?
> We tried the options :
> DBCC Shrinkfile
> SP_DBOPTION dbname , 'Truncate log on checkpoint', true.
> However the file size remained same.
> Are we making any mistake? Which is the best way to reduce the file size
> and
> keep the size to a preset limit (without affecting the trasaction after
> reaching the limit)
> Regards
> Sathian
>|||take a backup and restore upto that point, truncate the log.
do this when users are offline if you can.
delete redundant objects and temp tables and shrink the log files. if that
doesn't work get more disk.
"Sathian" wrote:
> Dear All,
> Our database log file in SQL 2000 is growing and filling the disk.
> What are the option to reduce the log file size which is existing?
> We tried the options :
> DBCC Shrinkfile
> SP_DBOPTION dbname , 'Truncate log on checkpoint', true.
> However the file size remained same.
> Are we making any mistake? Which is the best way to reduce the file size a
nd
> keep the size to a preset limit (without affecting the trasaction after
> reaching the limit)
> Regards
> Sathian
>
>|||marcmc wrote:
> take a backup and restore upto that point, truncate the log.
> do this when users are offline if you can.
> delete redundant objects and temp tables and shrink the log files. if that
> doesn't work get more disk.
>
I don't really see any point in restoring anything. The problem seems
like you are running in FULL recovery mode and you've never backed up
your logfile.
If you are running on FULL recovery, you should make sure that you have
a recent backup of your database. Then you run a backup of your logfile.
Now you can run a DBCC SHRINKFILE(YourLogicalFileName, TargetSize) which
will try to shrink your logfile to the size you've specified.
Just keep in mind that if you have any open transactions, then you can't
shrink the file past these transactions.
If you don't care about your logfile backup, then you can also run the
backup log command with the NO_LOG option. This will only truncate the
log file and not create a log backup file. You can then afterwards run
the DBCC SHRINKFILE command.
Another good idea is also to look up "Truncating the transcation log" in
Books On Line.
Regards
Steen Schlüter Persson
Databaseadministrator / Systemadministrator|||Hi,
Here is every time working solution
1. Change recovery model into SIMPLE.
2. Truncate and shrink transaction log.
3. Resize log into required size.
Tomasz B.
"Sathian" wrote:
> Dear All,
> Our database log file in SQL 2000 is growing and filling the disk.
> What are the option to reduce the log file size which is existing?
> We tried the options :
> DBCC Shrinkfile
> SP_DBOPTION dbname , 'Truncate log on checkpoint', true.
> However the file size remained same.
> Are we making any mistake? Which is the best way to reduce the file size a
nd
> keep the size to a preset limit (without affecting the trasaction after
> reaching the limit)
> Regards
> Sathian
>
>|||"Tomasz Borawski" <TomaszBorawski@.discussions.microsoft.com> wrote in
message news:EFCEBE5D-1ADA-49BB-AD52-93ECD498C7B7@.microsoft.com...
> Hi,
> Here is every time working solution
> 1. Change recovery model into SIMPLE.
> 2. Truncate and shrink transaction log.
> 3. Resize log into required size.
You forgot the critical (assuming this is a production database or one they
care about otherwise) of "backup"
Once you've changed the recovery model to simple, you've negated your backup
string.
> Tomasz B.
> "Sathian" wrote:
>
and|||If this is production server, and recovery mode=full, I will
1. backup transaction_log dbname to disk='filename'
2. dbcc shrinkfile logical_db name
3. schedule to backup transaction_log depand on needed + recovery purpose
--soonyu
"Sathian" wrote:
> Dear All,
> Our database log file in SQL 2000 is growing and filling the disk.
> What are the option to reduce the log file size which is existing?
> We tried the options :
> DBCC Shrinkfile
> SP_DBOPTION dbname , 'Truncate log on checkpoint', true.
> However the file size remained same.
> Are we making any mistake? Which is the best way to reduce the file size a
nd
> keep the size to a preset limit (without affecting the trasaction after
> reaching the limit)
> Regards
> Sathian
>
>
Showing posts with label dear. Show all posts
Showing posts with label dear. Show all posts
Friday, March 23, 2012
Log File Size reduction
Dear All,
Our database log file in SQL 2000 is growing and filling the disk.
What are the option to reduce the log file size which is existing?
We tried the options :
DBCC Shrinkfile
SP_DBOPTION dbname , 'Truncate log on checkpoint', true.
However the file size remained same.
Are we making any mistake? Which is the best way to reduce the file size and
keep the size to a preset limit (without affecting the trasaction after
reaching the limit)
Regards
SathianHi
http://www.karaszi.com/SQLServer/info_dont_shrink.asp
"Sathian" <sathian.t@.in.bosch.com> wrote in message
news:e75ujo$sfo$1@.ns2.fe.internet.bosch.com...
> Dear All,
> Our database log file in SQL 2000 is growing and filling the disk.
> What are the option to reduce the log file size which is existing?
> We tried the options :
> DBCC Shrinkfile
> SP_DBOPTION dbname , 'Truncate log on checkpoint', true.
> However the file size remained same.
> Are we making any mistake? Which is the best way to reduce the file size
> and
> keep the size to a preset limit (without affecting the trasaction after
> reaching the limit)
> Regards
> Sathian
>|||take a backup and restore upto that point, truncate the log.
do this when users are offline if you can.
delete redundant objects and temp tables and shrink the log files. if that
doesn't work get more disk.
"Sathian" wrote:
> Dear All,
> Our database log file in SQL 2000 is growing and filling the disk.
> What are the option to reduce the log file size which is existing?
> We tried the options :
> DBCC Shrinkfile
> SP_DBOPTION dbname , 'Truncate log on checkpoint', true.
> However the file size remained same.
> Are we making any mistake? Which is the best way to reduce the file size and
> keep the size to a preset limit (without affecting the trasaction after
> reaching the limit)
> Regards
> Sathian
>
>|||marcmc wrote:
> take a backup and restore upto that point, truncate the log.
> do this when users are offline if you can.
> delete redundant objects and temp tables and shrink the log files. if that
> doesn't work get more disk.
>
I don't really see any point in restoring anything. The problem seems
like you are running in FULL recovery mode and you've never backed up
your logfile.
If you are running on FULL recovery, you should make sure that you have
a recent backup of your database. Then you run a backup of your logfile.
Now you can run a DBCC SHRINKFILE(YourLogicalFileName, TargetSize) which
will try to shrink your logfile to the size you've specified.
Just keep in mind that if you have any open transactions, then you can't
shrink the file past these transactions.
If you don't care about your logfile backup, then you can also run the
backup log command with the NO_LOG option. This will only truncate the
log file and not create a log backup file. You can then afterwards run
the DBCC SHRINKFILE command.
Another good idea is also to look up "Truncating the transcation log" in
Books On Line.
Regards
Steen Schlüter Persson
Databaseadministrator / Systemadministrator|||Hi,
Here is every time working solution
1. Change recovery model into SIMPLE.
2. Truncate and shrink transaction log.
3. Resize log into required size.
Tomasz B.
"Sathian" wrote:
> Dear All,
> Our database log file in SQL 2000 is growing and filling the disk.
> What are the option to reduce the log file size which is existing?
> We tried the options :
> DBCC Shrinkfile
> SP_DBOPTION dbname , 'Truncate log on checkpoint', true.
> However the file size remained same.
> Are we making any mistake? Which is the best way to reduce the file size and
> keep the size to a preset limit (without affecting the trasaction after
> reaching the limit)
> Regards
> Sathian
>
>|||"Tomasz Borawski" <TomaszBorawski@.discussions.microsoft.com> wrote in
message news:EFCEBE5D-1ADA-49BB-AD52-93ECD498C7B7@.microsoft.com...
> Hi,
> Here is every time working solution
> 1. Change recovery model into SIMPLE.
> 2. Truncate and shrink transaction log.
> 3. Resize log into required size.
You forgot the critical (assuming this is a production database or one they
care about otherwise) of "backup"
Once you've changed the recovery model to simple, you've negated your backup
string.
> Tomasz B.
> "Sathian" wrote:
> > Dear All,
> >
> > Our database log file in SQL 2000 is growing and filling the disk.
> >
> > What are the option to reduce the log file size which is existing?
> >
> > We tried the options :
> >
> > DBCC Shrinkfile
> > SP_DBOPTION dbname , 'Truncate log on checkpoint', true.
> >
> > However the file size remained same.
> >
> > Are we making any mistake? Which is the best way to reduce the file size
and
> > keep the size to a preset limit (without affecting the trasaction after
> > reaching the limit)
> >
> > Regards
> >
> > Sathian
> >
> >
> >|||If this is production server, and recovery mode=full, I will
1. backup transaction_log dbname to disk='filename'
2. dbcc shrinkfile logical_db name
3. schedule to backup transaction_log depand on needed + recovery purpose
--soonyu
"Sathian" wrote:
> Dear All,
> Our database log file in SQL 2000 is growing and filling the disk.
> What are the option to reduce the log file size which is existing?
> We tried the options :
> DBCC Shrinkfile
> SP_DBOPTION dbname , 'Truncate log on checkpoint', true.
> However the file size remained same.
> Are we making any mistake? Which is the best way to reduce the file size and
> keep the size to a preset limit (without affecting the trasaction after
> reaching the limit)
> Regards
> Sathian
>
>
Our database log file in SQL 2000 is growing and filling the disk.
What are the option to reduce the log file size which is existing?
We tried the options :
DBCC Shrinkfile
SP_DBOPTION dbname , 'Truncate log on checkpoint', true.
However the file size remained same.
Are we making any mistake? Which is the best way to reduce the file size and
keep the size to a preset limit (without affecting the trasaction after
reaching the limit)
Regards
SathianHi
http://www.karaszi.com/SQLServer/info_dont_shrink.asp
"Sathian" <sathian.t@.in.bosch.com> wrote in message
news:e75ujo$sfo$1@.ns2.fe.internet.bosch.com...
> Dear All,
> Our database log file in SQL 2000 is growing and filling the disk.
> What are the option to reduce the log file size which is existing?
> We tried the options :
> DBCC Shrinkfile
> SP_DBOPTION dbname , 'Truncate log on checkpoint', true.
> However the file size remained same.
> Are we making any mistake? Which is the best way to reduce the file size
> and
> keep the size to a preset limit (without affecting the trasaction after
> reaching the limit)
> Regards
> Sathian
>|||take a backup and restore upto that point, truncate the log.
do this when users are offline if you can.
delete redundant objects and temp tables and shrink the log files. if that
doesn't work get more disk.
"Sathian" wrote:
> Dear All,
> Our database log file in SQL 2000 is growing and filling the disk.
> What are the option to reduce the log file size which is existing?
> We tried the options :
> DBCC Shrinkfile
> SP_DBOPTION dbname , 'Truncate log on checkpoint', true.
> However the file size remained same.
> Are we making any mistake? Which is the best way to reduce the file size and
> keep the size to a preset limit (without affecting the trasaction after
> reaching the limit)
> Regards
> Sathian
>
>|||marcmc wrote:
> take a backup and restore upto that point, truncate the log.
> do this when users are offline if you can.
> delete redundant objects and temp tables and shrink the log files. if that
> doesn't work get more disk.
>
I don't really see any point in restoring anything. The problem seems
like you are running in FULL recovery mode and you've never backed up
your logfile.
If you are running on FULL recovery, you should make sure that you have
a recent backup of your database. Then you run a backup of your logfile.
Now you can run a DBCC SHRINKFILE(YourLogicalFileName, TargetSize) which
will try to shrink your logfile to the size you've specified.
Just keep in mind that if you have any open transactions, then you can't
shrink the file past these transactions.
If you don't care about your logfile backup, then you can also run the
backup log command with the NO_LOG option. This will only truncate the
log file and not create a log backup file. You can then afterwards run
the DBCC SHRINKFILE command.
Another good idea is also to look up "Truncating the transcation log" in
Books On Line.
Regards
Steen Schlüter Persson
Databaseadministrator / Systemadministrator|||Hi,
Here is every time working solution
1. Change recovery model into SIMPLE.
2. Truncate and shrink transaction log.
3. Resize log into required size.
Tomasz B.
"Sathian" wrote:
> Dear All,
> Our database log file in SQL 2000 is growing and filling the disk.
> What are the option to reduce the log file size which is existing?
> We tried the options :
> DBCC Shrinkfile
> SP_DBOPTION dbname , 'Truncate log on checkpoint', true.
> However the file size remained same.
> Are we making any mistake? Which is the best way to reduce the file size and
> keep the size to a preset limit (without affecting the trasaction after
> reaching the limit)
> Regards
> Sathian
>
>|||"Tomasz Borawski" <TomaszBorawski@.discussions.microsoft.com> wrote in
message news:EFCEBE5D-1ADA-49BB-AD52-93ECD498C7B7@.microsoft.com...
> Hi,
> Here is every time working solution
> 1. Change recovery model into SIMPLE.
> 2. Truncate and shrink transaction log.
> 3. Resize log into required size.
You forgot the critical (assuming this is a production database or one they
care about otherwise) of "backup"
Once you've changed the recovery model to simple, you've negated your backup
string.
> Tomasz B.
> "Sathian" wrote:
> > Dear All,
> >
> > Our database log file in SQL 2000 is growing and filling the disk.
> >
> > What are the option to reduce the log file size which is existing?
> >
> > We tried the options :
> >
> > DBCC Shrinkfile
> > SP_DBOPTION dbname , 'Truncate log on checkpoint', true.
> >
> > However the file size remained same.
> >
> > Are we making any mistake? Which is the best way to reduce the file size
and
> > keep the size to a preset limit (without affecting the trasaction after
> > reaching the limit)
> >
> > Regards
> >
> > Sathian
> >
> >
> >|||If this is production server, and recovery mode=full, I will
1. backup transaction_log dbname to disk='filename'
2. dbcc shrinkfile logical_db name
3. schedule to backup transaction_log depand on needed + recovery purpose
--soonyu
"Sathian" wrote:
> Dear All,
> Our database log file in SQL 2000 is growing and filling the disk.
> What are the option to reduce the log file size which is existing?
> We tried the options :
> DBCC Shrinkfile
> SP_DBOPTION dbname , 'Truncate log on checkpoint', true.
> However the file size remained same.
> Are we making any mistake? Which is the best way to reduce the file size and
> keep the size to a preset limit (without affecting the trasaction after
> reaching the limit)
> Regards
> Sathian
>
>
Log File Size (SQL 2000)
Dear All,
How to maintain the transaction log file size ?
When I should use Restrict filegrowth or Unrestricted filegrowth ?
How many MB shoud I setup for the log file size ?
Thanks.
Vensia
Hi
http://www.sql-server-performance.co...e_settings.asp
"Vensia" <vensia2000_nospam@.yahoo.com> wrote in message
news:eUNfuu1FGHA.752@.TK2MSFTNGP12.phx.gbl...
> Dear All,
> How to maintain the transaction log file size ?
> When I should use Restrict filegrowth or Unrestricted filegrowth ?
> How many MB shoud I setup for the log file size ?
> Thanks.
> Vensia
>
How to maintain the transaction log file size ?
When I should use Restrict filegrowth or Unrestricted filegrowth ?
How many MB shoud I setup for the log file size ?
Thanks.
Vensia
Hi
http://www.sql-server-performance.co...e_settings.asp
"Vensia" <vensia2000_nospam@.yahoo.com> wrote in message
news:eUNfuu1FGHA.752@.TK2MSFTNGP12.phx.gbl...
> Dear All,
> How to maintain the transaction log file size ?
> When I should use Restrict filegrowth or Unrestricted filegrowth ?
> How many MB shoud I setup for the log file size ?
> Thanks.
> Vensia
>
Log File Size (SQL 2000)
Dear All,
How to maintain the transaction log file size ?
When I should use Restrict filegrowth or Unrestricted filegrowth ?
How many MB shoud I setup for the log file size ?
Thanks.
VensiaHi
http://www.sql-server-performance.com/database_settings.asp
"Vensia" <vensia2000_nospam@.yahoo.com> wrote in message
news:eUNfuu1FGHA.752@.TK2MSFTNGP12.phx.gbl...
> Dear All,
> How to maintain the transaction log file size ?
> When I should use Restrict filegrowth or Unrestricted filegrowth ?
> How many MB shoud I setup for the log file size ?
> Thanks.
> Vensia
>
How to maintain the transaction log file size ?
When I should use Restrict filegrowth or Unrestricted filegrowth ?
How many MB shoud I setup for the log file size ?
Thanks.
VensiaHi
http://www.sql-server-performance.com/database_settings.asp
"Vensia" <vensia2000_nospam@.yahoo.com> wrote in message
news:eUNfuu1FGHA.752@.TK2MSFTNGP12.phx.gbl...
> Dear All,
> How to maintain the transaction log file size ?
> When I should use Restrict filegrowth or Unrestricted filegrowth ?
> How many MB shoud I setup for the log file size ?
> Thanks.
> Vensia
>
Log File Size (SQL 2000)
Dear All,
How to maintain the transaction log file size ?
When I should use Restrict filegrowth or Unrestricted filegrowth ?
How many MB shoud I setup for the log file size ?
Thanks.
VensiaHi
http://www.sql-server-performance.c...se_settings.asp
"Vensia" <vensia2000_nospam@.yahoo.com> wrote in message
news:eUNfuu1FGHA.752@.TK2MSFTNGP12.phx.gbl...
> Dear All,
> How to maintain the transaction log file size ?
> When I should use Restrict filegrowth or Unrestricted filegrowth ?
> How many MB shoud I setup for the log file size ?
> Thanks.
> Vensia
>
How to maintain the transaction log file size ?
When I should use Restrict filegrowth or Unrestricted filegrowth ?
How many MB shoud I setup for the log file size ?
Thanks.
VensiaHi
http://www.sql-server-performance.c...se_settings.asp
"Vensia" <vensia2000_nospam@.yahoo.com> wrote in message
news:eUNfuu1FGHA.752@.TK2MSFTNGP12.phx.gbl...
> Dear All,
> How to maintain the transaction log file size ?
> When I should use Restrict filegrowth or Unrestricted filegrowth ?
> How many MB shoud I setup for the log file size ?
> Thanks.
> Vensia
>
log file size
Dear Sir
I found that my database file (*.mdf) size is about
600 mb, but my log file (*.ldf) size is about 16 GB. My
harddisk has not enough space to place this file, if the
log continues to increase. I want to ask does any method
too reduced the size of the log file, becuase the log file
is not important for us'
Thank youIf you don't backup your transaction log regularly as part of your
recovery plan, you can have SQL Server automatically remove committed
data from the log by setting the recovery model to SIMPLE (SQL 2000) or
turn on the 'trunc. log on chkpt.' database option (SQL 7). Note that
your only recovery option in the SIMPLE recovery model is to restore
from full and differential backup.
For example:
SQL 2000:
ALTER DATABASE MyDatabase
SET RECOVERY SIMPLE
SQL 7:
EXEC sp_dboption 'MyDatabase', 'trunc. log on chkpt.', true
To reduce the size of your log, use DBCC SHRINKFILE. The example below
will shrink the log file to 200MB:
USE MyDatabase
DBCC SHRINKFILE('MyDatabase_Log', 200)
See the Books Online for details.
--
Hope this helps.
Dan Guzman
SQL Server MVP
--
SQL FAQ links (courtesy Neil Pike):
http://www.ntfaq.com/Articles/Index.cfm?DepartmentID=800
http://www.sqlserverfaq.com
http://www.mssqlserver.com/faq
--
"back1" <back1@.mail.hongkong.com> wrote in message
news:033f01c36aad$9ede6f90$a401280a@.phx.gbl...
> Dear Sir
> I found that my database file (*.mdf) size is about
> 600 mb, but my log file (*.ldf) size is about 16 GB. My
> harddisk has not enough space to place this file, if the
> log continues to increase. I want to ask does any method
> too reduced the size of the log file, becuase the log file
> is not important for us'
> Thank you|||Dear Sir
Thank you for your quickly reply!
I already use this statement "DBCC SHRINKFILE
('MyDatabase_Log', 200)" to shrink the log file, but it
has not any effect. What can i do now '
Thank you
>--Original Message--
>If you don't backup your transaction log regularly as
part of your
>recovery plan, you can have SQL Server automatically
remove committed
>data from the log by setting the recovery model to SIMPLE
(SQL 2000) or
>turn on the 'trunc. log on chkpt.' database option (SQL
7). Note that
>your only recovery option in the SIMPLE recovery model is
to restore
>from full and differential backup.
>For example:
>SQL 2000:
> ALTER DATABASE MyDatabase
> SET RECOVERY SIMPLE
>SQL 7:
> EXEC sp_dboption 'MyDatabase', 'trunc. log on
chkpt.', true
>To reduce the size of your log, use DBCC SHRINKFILE. The
example below
>will shrink the log file to 200MB:
> USE MyDatabase
> DBCC SHRINKFILE('MyDatabase_Log', 200)
>See the Books Online for details.
>--
>Hope this helps.
>Dan Guzman
>SQL Server MVP
>--
>SQL FAQ links (courtesy Neil Pike):
>http://www.ntfaq.com/Articles/Index.cfm?DepartmentID=800
>http://www.sqlserverfaq.com
>http://www.mssqlserver.com/faq
>--
>"back1" <back1@.mail.hongkong.com> wrote in message
>news:033f01c36aad$9ede6f90$a401280a@.phx.gbl...
>> Dear Sir
>> I found that my database file (*.mdf) size is about
>> 600 mb, but my log file (*.ldf) size is about 16 GB. My
>> harddisk has not enough space to place this file, if the
>> log continues to increase. I want to ask does any method
>> too reduced the size of the log file, becuase the log
file
>> is not important for us'
>> Thank you
>
>.
>
I found that my database file (*.mdf) size is about
600 mb, but my log file (*.ldf) size is about 16 GB. My
harddisk has not enough space to place this file, if the
log continues to increase. I want to ask does any method
too reduced the size of the log file, becuase the log file
is not important for us'
Thank youIf you don't backup your transaction log regularly as part of your
recovery plan, you can have SQL Server automatically remove committed
data from the log by setting the recovery model to SIMPLE (SQL 2000) or
turn on the 'trunc. log on chkpt.' database option (SQL 7). Note that
your only recovery option in the SIMPLE recovery model is to restore
from full and differential backup.
For example:
SQL 2000:
ALTER DATABASE MyDatabase
SET RECOVERY SIMPLE
SQL 7:
EXEC sp_dboption 'MyDatabase', 'trunc. log on chkpt.', true
To reduce the size of your log, use DBCC SHRINKFILE. The example below
will shrink the log file to 200MB:
USE MyDatabase
DBCC SHRINKFILE('MyDatabase_Log', 200)
See the Books Online for details.
--
Hope this helps.
Dan Guzman
SQL Server MVP
--
SQL FAQ links (courtesy Neil Pike):
http://www.ntfaq.com/Articles/Index.cfm?DepartmentID=800
http://www.sqlserverfaq.com
http://www.mssqlserver.com/faq
--
"back1" <back1@.mail.hongkong.com> wrote in message
news:033f01c36aad$9ede6f90$a401280a@.phx.gbl...
> Dear Sir
> I found that my database file (*.mdf) size is about
> 600 mb, but my log file (*.ldf) size is about 16 GB. My
> harddisk has not enough space to place this file, if the
> log continues to increase. I want to ask does any method
> too reduced the size of the log file, becuase the log file
> is not important for us'
> Thank you|||Dear Sir
Thank you for your quickly reply!
I already use this statement "DBCC SHRINKFILE
('MyDatabase_Log', 200)" to shrink the log file, but it
has not any effect. What can i do now '
Thank you
>--Original Message--
>If you don't backup your transaction log regularly as
part of your
>recovery plan, you can have SQL Server automatically
remove committed
>data from the log by setting the recovery model to SIMPLE
(SQL 2000) or
>turn on the 'trunc. log on chkpt.' database option (SQL
7). Note that
>your only recovery option in the SIMPLE recovery model is
to restore
>from full and differential backup.
>For example:
>SQL 2000:
> ALTER DATABASE MyDatabase
> SET RECOVERY SIMPLE
>SQL 7:
> EXEC sp_dboption 'MyDatabase', 'trunc. log on
chkpt.', true
>To reduce the size of your log, use DBCC SHRINKFILE. The
example below
>will shrink the log file to 200MB:
> USE MyDatabase
> DBCC SHRINKFILE('MyDatabase_Log', 200)
>See the Books Online for details.
>--
>Hope this helps.
>Dan Guzman
>SQL Server MVP
>--
>SQL FAQ links (courtesy Neil Pike):
>http://www.ntfaq.com/Articles/Index.cfm?DepartmentID=800
>http://www.sqlserverfaq.com
>http://www.mssqlserver.com/faq
>--
>"back1" <back1@.mail.hongkong.com> wrote in message
>news:033f01c36aad$9ede6f90$a401280a@.phx.gbl...
>> Dear Sir
>> I found that my database file (*.mdf) size is about
>> 600 mb, but my log file (*.ldf) size is about 16 GB. My
>> harddisk has not enough space to place this file, if the
>> log continues to increase. I want to ask does any method
>> too reduced the size of the log file, becuase the log
file
>> is not important for us'
>> Thank you
>
>.
>
Wednesday, March 21, 2012
Log file is not available
Dear sir
I have a database of around 116Gb and somehow I lost my
log file , now I am able to attach the database but not
able to do any transaction as I lost the log file.Pls
advice me how to add a log file with this database.
Regards
TriveniHi Triveni,
Check this out!
Restoring databases when only data file available
http://www.sqlservercentral.com/scripts/scriptdetails.asp?
scriptid=599
Regards
Thirumal Reddy M
www.sstil.com
>--Original Message--
>Dear sir
>I have a database of around 116Gb and somehow I lost my
>log file , now I am able to attach the database but not
>able to do any transaction as I lost the log file.Pls
>advice me how to add a log file with this database.
>Regards
>Triveni
>.
>|||Hi !!
Did not find anything on the below link. pls provide the
suggestion as soon as possible.
>--Original Message--
>Hi Triveni,
>Check this out!
>Restoring databases when only data file available
>http://www.sqlservercentral.com/scripts/scriptdetails.asp?
>scriptid=599
>Regards
>Thirumal Reddy M
>www.sstil.com
>>--Original Message--
>>Dear sir
>>I have a database of around 116Gb and somehow I lost my
>>log file , now I am able to attach the database but not
>>able to do any transaction as I lost the log file.Pls
>>advice me how to add a log file with this database.
>>Regards
>>Triveni
>>.
>.
>
I have a database of around 116Gb and somehow I lost my
log file , now I am able to attach the database but not
able to do any transaction as I lost the log file.Pls
advice me how to add a log file with this database.
Regards
TriveniHi Triveni,
Check this out!
Restoring databases when only data file available
http://www.sqlservercentral.com/scripts/scriptdetails.asp?
scriptid=599
Regards
Thirumal Reddy M
www.sstil.com
>--Original Message--
>Dear sir
>I have a database of around 116Gb and somehow I lost my
>log file , now I am able to attach the database but not
>able to do any transaction as I lost the log file.Pls
>advice me how to add a log file with this database.
>Regards
>Triveni
>.
>|||Hi !!
Did not find anything on the below link. pls provide the
suggestion as soon as possible.
>--Original Message--
>Hi Triveni,
>Check this out!
>Restoring databases when only data file available
>http://www.sqlservercentral.com/scripts/scriptdetails.asp?
>scriptid=599
>Regards
>Thirumal Reddy M
>www.sstil.com
>>--Original Message--
>>Dear sir
>>I have a database of around 116Gb and somehow I lost my
>>log file , now I am able to attach the database but not
>>able to do any transaction as I lost the log file.Pls
>>advice me how to add a log file with this database.
>>Regards
>>Triveni
>>.
>.
>
Monday, March 12, 2012
Log File Backup and Log Shipping
Dear All,
We currently back up our log file every 10 minutes.
We now have a requirement to implement log shipping. My question however is
will this effect out Log File Backup, such that together they will not work
correctly ?Log shipping will include ( and become, and replace ) your log backup.
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Patricia" <Patricia@.discussions.microsoft.com> wrote in message
news:1512FBDF-8A4B-4FF8-9092-5C2DBC377EC1@.microsoft.com...
> Dear All,
> We currently back up our log file every 10 minutes.
> We now have a requirement to implement log shipping. My question however
> is
> will this effect out Log File Backup, such that together they will not
> work
> correctly ?
>
>|||Thanks Wayne,
I guess we will not put it in then, we want to keep both a current copy and
point in time backup.
Thanks for your help
J
"Wayne Snyder" wrote:
> Log shipping will include ( and become, and replace ) your log backup.
> --
> Wayne Snyder, MCDBA, SQL Server MVP
> Mariner, Charlotte, NC
> www.mariner-usa.com
> (Please respond only to the newsgroups.)
> I support the Professional Association of SQL Server (PASS) and it's
> community of SQL Server professionals.
> www.sqlpass.org
> "Patricia" <Patricia@.discussions.microsoft.com> wrote in message
> news:1512FBDF-8A4B-4FF8-9092-5C2DBC377EC1@.microsoft.com...
>
>
We currently back up our log file every 10 minutes.
We now have a requirement to implement log shipping. My question however is
will this effect out Log File Backup, such that together they will not work
correctly ?Log shipping will include ( and become, and replace ) your log backup.
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Patricia" <Patricia@.discussions.microsoft.com> wrote in message
news:1512FBDF-8A4B-4FF8-9092-5C2DBC377EC1@.microsoft.com...
> Dear All,
> We currently back up our log file every 10 minutes.
> We now have a requirement to implement log shipping. My question however
> is
> will this effect out Log File Backup, such that together they will not
> work
> correctly ?
>
>|||Thanks Wayne,
I guess we will not put it in then, we want to keep both a current copy and
point in time backup.
Thanks for your help
J
"Wayne Snyder" wrote:
> Log shipping will include ( and become, and replace ) your log backup.
> --
> Wayne Snyder, MCDBA, SQL Server MVP
> Mariner, Charlotte, NC
> www.mariner-usa.com
> (Please respond only to the newsgroups.)
> I support the Professional Association of SQL Server (PASS) and it's
> community of SQL Server professionals.
> www.sqlpass.org
> "Patricia" <Patricia@.discussions.microsoft.com> wrote in message
> news:1512FBDF-8A4B-4FF8-9092-5C2DBC377EC1@.microsoft.com...
>
>
Subscribe to:
Posts (Atom)