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 log. Show all posts
Showing posts with label log. 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 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 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 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
Wednesday, March 28, 2012
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
>
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
>
Monday, March 26, 2012
how to setup the permission of share fold in log shipping ?
when I set up log shipping in SQL SERVER 2000
I got the following error:
'Unable to copy the initialization file to the secondary server '%s' '
I know this is permission question about the share fold to log file.
I want to know the following thing:
one is how to setup permisson about about the share fold to log file?
two is whether log shpping primary and the secondary db must be run
in domin mode or not?
thanks in advance!
1. if your logshipping jobs are owned by 'sa', the acct for sqlserver
service needs to have access to the shared.
2. no, they do not have to be running in a same domain. the key here is to
ensure each server can access the shared.
-oj
<lovexueer@.gmail.com> wrote in message
news:1143611950.475014.180370@.i39g2000cwa.googlegr oups.com...
> when I set up log shipping in SQL SERVER 2000
> I got the following error:
> 'Unable to copy the initialization file to the secondary server '%s' '
> I know this is permission question about the share fold to log file.
> I want to know the following thing:
> one is how to setup permisson about about the share fold to log file?
> two is whether log shpping primary and the secondary db must be run
> in domin mode or not?
>
> thanks in advance!
>
I got the following error:
'Unable to copy the initialization file to the secondary server '%s' '
I know this is permission question about the share fold to log file.
I want to know the following thing:
one is how to setup permisson about about the share fold to log file?
two is whether log shpping primary and the secondary db must be run
in domin mode or not?
thanks in advance!
1. if your logshipping jobs are owned by 'sa', the acct for sqlserver
service needs to have access to the shared.
2. no, they do not have to be running in a same domain. the key here is to
ensure each server can access the shared.
-oj
<lovexueer@.gmail.com> wrote in message
news:1143611950.475014.180370@.i39g2000cwa.googlegr oups.com...
> when I set up log shipping in SQL SERVER 2000
> I got the following error:
> 'Unable to copy the initialization file to the secondary server '%s' '
> I know this is permission question about the share fold to log file.
> I want to know the following thing:
> one is how to setup permisson about about the share fold to log file?
> two is whether log shpping primary and the secondary db must be run
> in domin mode or not?
>
> thanks in advance!
>
how to setup the permission of share fold in log shipping ?
when I set up log shipping in SQL SERVER 2000
I got the following error:
'Unable to copy the initialization file to the secondary server '%s' '
I know this is permission question about the share fold to log file.
I want to know the following thing:
one is how to setup permisson about about the share fold to log file?
two is whether log shpping primary and the secondary db must be run
in domin mode or not?
thanks in advance!1. if your logshipping jobs are owned by 'sa', the acct for sqlserver
service needs to have access to the shared.
2. no, they do not have to be running in a same domain. the key here is to
ensure each server can access the shared.
-oj
<lovexueer@.gmail.com> wrote in message
news:1143611950.475014.180370@.i39g2000cwa.googlegroups.com...
> when I set up log shipping in SQL SERVER 2000
> I got the following error:
> 'Unable to copy the initialization file to the secondary server '%s' '
> I know this is permission question about the share fold to log file.
> I want to know the following thing:
> one is how to setup permisson about about the share fold to log file?
> two is whether log shpping primary and the secondary db must be run
> in domin mode or not?
>
> thanks in advance!
>
I got the following error:
'Unable to copy the initialization file to the secondary server '%s' '
I know this is permission question about the share fold to log file.
I want to know the following thing:
one is how to setup permisson about about the share fold to log file?
two is whether log shpping primary and the secondary db must be run
in domin mode or not?
thanks in advance!1. if your logshipping jobs are owned by 'sa', the acct for sqlserver
service needs to have access to the shared.
2. no, they do not have to be running in a same domain. the key here is to
ensure each server can access the shared.
-oj
<lovexueer@.gmail.com> wrote in message
news:1143611950.475014.180370@.i39g2000cwa.googlegroups.com...
> when I set up log shipping in SQL SERVER 2000
> I got the following error:
> 'Unable to copy the initialization file to the secondary server '%s' '
> I know this is permission question about the share fold to log file.
> I want to know the following thing:
> one is how to setup permisson about about the share fold to log file?
> two is whether log shpping primary and the secondary db must be run
> in domin mode or not?
>
> thanks in advance!
>
how to setup the permission of share fold in log shipping ?
when I set up log shipping in SQL SERVER 2000
I got the following error:
'Unable to copy the initialization file to the secondary server '%s' '
I know this is permission question about the share fold to log file.
I want to know the following thing:
one is how to setup permisson about about the share fold to log file?
two is whether log shpping primary and the secondary db must be run
in domin mode or not?
thanks in advance!1. if your logshipping jobs are owned by 'sa', the acct for sqlserver
service needs to have access to the shared.
2. no, they do not have to be running in a same domain. the key here is to
ensure each server can access the shared.
--
-oj
<lovexueer@.gmail.com> wrote in message
news:1143611950.475014.180370@.i39g2000cwa.googlegroups.com...
> when I set up log shipping in SQL SERVER 2000
> I got the following error:
> 'Unable to copy the initialization file to the secondary server '%s' '
> I know this is permission question about the share fold to log file.
> I want to know the following thing:
> one is how to setup permisson about about the share fold to log file?
> two is whether log shpping primary and the secondary db must be run
> in domin mode or not?
>
> thanks in advance!
>|||sorry,I don't understand what you said to the first quesiton?
can you tell furthur detailed information about how to setup the
permisson of shared fold ?sql
I got the following error:
'Unable to copy the initialization file to the secondary server '%s' '
I know this is permission question about the share fold to log file.
I want to know the following thing:
one is how to setup permisson about about the share fold to log file?
two is whether log shpping primary and the secondary db must be run
in domin mode or not?
thanks in advance!1. if your logshipping jobs are owned by 'sa', the acct for sqlserver
service needs to have access to the shared.
2. no, they do not have to be running in a same domain. the key here is to
ensure each server can access the shared.
--
-oj
<lovexueer@.gmail.com> wrote in message
news:1143611950.475014.180370@.i39g2000cwa.googlegroups.com...
> when I set up log shipping in SQL SERVER 2000
> I got the following error:
> 'Unable to copy the initialization file to the secondary server '%s' '
> I know this is permission question about the share fold to log file.
> I want to know the following thing:
> one is how to setup permisson about about the share fold to log file?
> two is whether log shpping primary and the secondary db must be run
> in domin mode or not?
>
> thanks in advance!
>|||sorry,I don't understand what you said to the first quesiton?
can you tell furthur detailed information about how to setup the
permisson of shared fold ?sql
Friday, March 23, 2012
How to setup LockTime value?
When I set the deadlock timeout value by "set @.@.lock_timeout", if I log off
from the server, the value will be set to the default value "-1".
How to set up the value, which can be applied where the server start up?
Thanks.
AlistairLock timeout can not be set that way... sorry...( and do not remember any
Trace flags which allow it to be set for all users either.)
Wayne Snyder, MCDBA, SQL Server MVP
Computer Education Services Corporation (CESC), Charlotte, NC
www.computeredservices.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
"Alistair" <anonymous@.discussions.microsoft.com> wrote in message
news:6D258EAB-7A0F-44D6-AB4A-DFEB1D7EE74E@.microsoft.com...
off from the server, the value will be set to the default value "-1".
from the server, the value will be set to the default value "-1".
How to set up the value, which can be applied where the server start up?
Thanks.
AlistairLock timeout can not be set that way... sorry...( and do not remember any
Trace flags which allow it to be set for all users either.)
Wayne Snyder, MCDBA, SQL Server MVP
Computer Education Services Corporation (CESC), Charlotte, NC
www.computeredservices.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
"Alistair" <anonymous@.discussions.microsoft.com> wrote in message
news:6D258EAB-7A0F-44D6-AB4A-DFEB1D7EE74E@.microsoft.com...
quote:
> When I set the deadlock timeout value by "set @.@.lock_timeout", if I log
off from the server, the value will be set to the default value "-1".
quote:sql
> How to set up the value, which can be applied where the server start up?
> Thanks.
> Alistair
How to setup LockTime value?
When I set the deadlock timeout value by "set @.@.lock_timeout", if I log off from the server, the value will be set to the default value "-1".
How to set up the value, which can be applied where the server start up?
Thanks.
AlistairLock timeout can not be set that way... sorry...( and do not remember any
Trace flags which allow it to be set for all users either.)
--
Wayne Snyder, MCDBA, SQL Server MVP
Computer Education Services Corporation (CESC), Charlotte, NC
www.computeredservices.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
"Alistair" <anonymous@.discussions.microsoft.com> wrote in message
news:6D258EAB-7A0F-44D6-AB4A-DFEB1D7EE74E@.microsoft.com...
> When I set the deadlock timeout value by "set @.@.lock_timeout", if I log
off from the server, the value will be set to the default value "-1".
> How to set up the value, which can be applied where the server start up?
> Thanks.
> Alistair
How to set up the value, which can be applied where the server start up?
Thanks.
AlistairLock timeout can not be set that way... sorry...( and do not remember any
Trace flags which allow it to be set for all users either.)
--
Wayne Snyder, MCDBA, SQL Server MVP
Computer Education Services Corporation (CESC), Charlotte, NC
www.computeredservices.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
"Alistair" <anonymous@.discussions.microsoft.com> wrote in message
news:6D258EAB-7A0F-44D6-AB4A-DFEB1D7EE74E@.microsoft.com...
> When I set the deadlock timeout value by "set @.@.lock_timeout", if I log
off from the server, the value will be set to the default value "-1".
> How to set up the value, which can be applied where the server start up?
> Thanks.
> Alistair
Wednesday, March 21, 2012
How to setup a script to automatically monitor log files
Is there any way to setup a script that can monitor sql
server 2000 log files automatically by using something
like dbcc sqlperf (logspace) go.
Any help will be appreacited.
Thanks,
Aboki.Look at performance condition alerts in BOL. You can define an alert on =a percentage full level on whichever log file you want, then take some =action, like backup log when it happens.
Mike John
"Aboki" <waco361@.hotmail.com> wrote in message =news:01df01c34d68$c7984ad0$a501280a@.phx.gbl...
> > Is there any way to setup a script that can monitor sql > server 2000 log files automatically by using something > like dbcc sqlperf (logspace) go.
> > Any help will be appreacited.
> > Thanks,
> Aboki.sql
server 2000 log files automatically by using something
like dbcc sqlperf (logspace) go.
Any help will be appreacited.
Thanks,
Aboki.Look at performance condition alerts in BOL. You can define an alert on =a percentage full level on whichever log file you want, then take some =action, like backup log when it happens.
Mike John
"Aboki" <waco361@.hotmail.com> wrote in message =news:01df01c34d68$c7984ad0$a501280a@.phx.gbl...
> > Is there any way to setup a script that can monitor sql > server 2000 log files automatically by using something > like dbcc sqlperf (logspace) go.
> > Any help will be appreacited.
> > Thanks,
> Aboki.sql
Subscribe to:
Posts (Atom)