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?
Showing posts with label transaction. Show all posts
Showing posts with label transaction. Show all posts
Friday, March 30, 2012
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 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 21, 2012
How to set upload only from sql mobile?
Hi,
I am using SQL 2005 in merge-replication with sql mobile. Can anyone tell me
a way to set the transaction for upload from pocket pc only?
Thanks,
Luke
You can set the ExchangeType to 1 and this will do uploadOnly.
You can set this either at the client side or as a profile parameter.
Hope that helps
--Mahesh
[ This posting is provided "as is" with no warranties and confers no
rights. ]
"luke" <talktomyfriend@.hotmail.com> wrote in message
news:edxyEGUEHHA.4280@.TK2MSFTNGP04.phx.gbl...
> Hi,
> I am using SQL 2005 in merge-replication with sql mobile. Can anyone tell
> me a way to set the transaction for upload from pocket pc only?
> Thanks,
> Luke
>
I am using SQL 2005 in merge-replication with sql mobile. Can anyone tell me
a way to set the transaction for upload from pocket pc only?
Thanks,
Luke
You can set the ExchangeType to 1 and this will do uploadOnly.
You can set this either at the client side or as a profile parameter.
Hope that helps
--Mahesh
[ This posting is provided "as is" with no warranties and confers no
rights. ]
"luke" <talktomyfriend@.hotmail.com> wrote in message
news:edxyEGUEHHA.4280@.TK2MSFTNGP04.phx.gbl...
> Hi,
> I am using SQL 2005 in merge-replication with sql mobile. Can anyone tell
> me a way to set the transaction for upload from pocket pc only?
> Thanks,
> Luke
>
How to set up the transaction log file properties?
Hello Guys,
I have question concerning the log file size:
I have a DB that receives a massive amount of data every two days and
several Transaction (USP) and delete records are usually done on it.
During these operations, the log size goes up until 32 Mo.
the size of the .mdf is 9Mo and .ldf is 14Mo
The DB is set to FULL and a backup DB and LOG is performed.
I set up the autoshink as well. is it required?
Why the log file increase so much and how to set up correctly the
transaction log file properties?
Thanks on advance.
Inaina
> I set up the autoshink as well. is it required?
No, disable this option
> Why the log file increase so much and how to set up correctly the
> transaction log file properties?
Why do you concern about that the log file is growing? That means that SQL
Server needs more virtual log files to deal with data. Make sure that you
have a free space enough for log file
http://www.karaszi.com/SQLServer/info_dont_shrink.asp
"ina" <roberta.inalbon@.gmail.com> wrote in message
news:1169025984.073903.91700@.v45g2000cwv.googlegroups.com...
> Hello Guys,
> I have question concerning the log file size:
> I have a DB that receives a massive amount of data every two days and
> several Transaction (USP) and delete records are usually done on it.
> During these operations, the log size goes up until 32 Mo.
> the size of the .mdf is 9Mo and .ldf is 14Mo
> The DB is set to FULL and a backup DB and LOG is performed.
> I set up the autoshink as well. is it required?
> Why the log file increase so much and how to set up correctly the
> transaction log file properties?
>
> Thanks on advance.
> Ina
>|||On 17.01.2007 10:26, ina wrote:
> I have a DB that receives a massive amount of data every two days and
> several Transaction (USP) and delete records are usually done on it.
> During these operations, the log size goes up until 32 Mo.
What unit is that? Megabytes? If so this is really a small DB you
should not worry about sizes in that area at all.
> the size of the .mdf is 9Mo and .ldf is 14Mo
> The DB is set to FULL and a backup DB and LOG is performed.
> I set up the autoshink as well. is it required?
As Uri pointed out, rather switch this off since you only waste
performance. The log will grow and shrink anyway so you can leave it at
the largest size.
> Why the log file increase so much and how to set up correctly the
> transaction log file properties?
Because apparently you DB needs the space (you mention "massive inserts").
Kind regards
robert|||Thanks :)
mdf: 9 MO (Mega Octets)
ldf : 14 MO
yes it is small DB but before, as I did not know about backup LOG and
shrink log file, I had for the same ldf file a size of 13 Go, and this
is big.
Really thank you because I solved this problem and everything works
file now.
These information helped me a bunch. :)
Ina
Robert Klemme wrote:
> On 17.01.2007 10:26, ina wrote:
> > I have a DB that receives a massive amount of data every two days and
> > several Transaction (USP) and delete records are usually done on it.
> >
> > During these operations, the log size goes up until 32 Mo.
> What unit is that? Megabytes? If so this is really a small DB you
> should not worry about sizes in that area at all.
> > the size of the .mdf is 9Mo and .ldf is 14Mo
> >
> > The DB is set to FULL and a backup DB and LOG is performed.
> >
> > I set up the autoshink as well. is it required?
> As Uri pointed out, rather switch this off since you only waste
> performance. The log will grow and shrink anyway so you can leave it at
> the largest size.
> > Why the log file increase so much and how to set up correctly the
> > transaction log file properties?
> Because apparently you DB needs the space (you mention "massive inserts").
> Kind regards
> robert
I have question concerning the log file size:
I have a DB that receives a massive amount of data every two days and
several Transaction (USP) and delete records are usually done on it.
During these operations, the log size goes up until 32 Mo.
the size of the .mdf is 9Mo and .ldf is 14Mo
The DB is set to FULL and a backup DB and LOG is performed.
I set up the autoshink as well. is it required?
Why the log file increase so much and how to set up correctly the
transaction log file properties?
Thanks on advance.
Inaina
> I set up the autoshink as well. is it required?
No, disable this option
> Why the log file increase so much and how to set up correctly the
> transaction log file properties?
Why do you concern about that the log file is growing? That means that SQL
Server needs more virtual log files to deal with data. Make sure that you
have a free space enough for log file
http://www.karaszi.com/SQLServer/info_dont_shrink.asp
"ina" <roberta.inalbon@.gmail.com> wrote in message
news:1169025984.073903.91700@.v45g2000cwv.googlegroups.com...
> Hello Guys,
> I have question concerning the log file size:
> I have a DB that receives a massive amount of data every two days and
> several Transaction (USP) and delete records are usually done on it.
> During these operations, the log size goes up until 32 Mo.
> the size of the .mdf is 9Mo and .ldf is 14Mo
> The DB is set to FULL and a backup DB and LOG is performed.
> I set up the autoshink as well. is it required?
> Why the log file increase so much and how to set up correctly the
> transaction log file properties?
>
> Thanks on advance.
> Ina
>|||On 17.01.2007 10:26, ina wrote:
> I have a DB that receives a massive amount of data every two days and
> several Transaction (USP) and delete records are usually done on it.
> During these operations, the log size goes up until 32 Mo.
What unit is that? Megabytes? If so this is really a small DB you
should not worry about sizes in that area at all.
> the size of the .mdf is 9Mo and .ldf is 14Mo
> The DB is set to FULL and a backup DB and LOG is performed.
> I set up the autoshink as well. is it required?
As Uri pointed out, rather switch this off since you only waste
performance. The log will grow and shrink anyway so you can leave it at
the largest size.
> Why the log file increase so much and how to set up correctly the
> transaction log file properties?
Because apparently you DB needs the space (you mention "massive inserts").
Kind regards
robert|||Thanks :)
mdf: 9 MO (Mega Octets)
ldf : 14 MO
yes it is small DB but before, as I did not know about backup LOG and
shrink log file, I had for the same ldf file a size of 13 Go, and this
is big.
Really thank you because I solved this problem and everything works
file now.
These information helped me a bunch. :)
Ina
Robert Klemme wrote:
> On 17.01.2007 10:26, ina wrote:
> > I have a DB that receives a massive amount of data every two days and
> > several Transaction (USP) and delete records are usually done on it.
> >
> > During these operations, the log size goes up until 32 Mo.
> What unit is that? Megabytes? If so this is really a small DB you
> should not worry about sizes in that area at all.
> > the size of the .mdf is 9Mo and .ldf is 14Mo
> >
> > The DB is set to FULL and a backup DB and LOG is performed.
> >
> > I set up the autoshink as well. is it required?
> As Uri pointed out, rather switch this off since you only waste
> performance. The log will grow and shrink anyway so you can leave it at
> the largest size.
> > Why the log file increase so much and how to set up correctly the
> > transaction log file properties?
> Because apparently you DB needs the space (you mention "massive inserts").
> Kind regards
> robert
How to set up the transaction log file properties?
Hello Guys,
I have question concerning the log file size:
I have a DB that receives a massive amount of data every two days and
several Transaction (USP) and delete records are usually done on it.
During these operations, the log size goes up until 32 Mo.
the size of the .mdf is 9Mo and .ldf is 14Mo
The DB is set to FULL and a backup DB and LOG is performed.
I set up the autoshink as well. is it required?
Why the log file increase so much and how to set up correctly the
transaction log file properties?
Thanks on advance.
Ina
ina
> I set up the autoshink as well. is it required?
No, disable this option
> Why the log file increase so much and how to set up correctly the
> transaction log file properties?
Why do you concern about that the log file is growing? That means that SQL
Server needs more virtual log files to deal with data. Make sure that you
have a free space enough for log file
http://www.karaszi.com/SQLServer/info_dont_shrink.asp
"ina" <roberta.inalbon@.gmail.com> wrote in message
news:1169025984.073903.91700@.v45g2000cwv.googlegro ups.com...
> Hello Guys,
> I have question concerning the log file size:
> I have a DB that receives a massive amount of data every two days and
> several Transaction (USP) and delete records are usually done on it.
> During these operations, the log size goes up until 32 Mo.
> the size of the .mdf is 9Mo and .ldf is 14Mo
> The DB is set to FULL and a backup DB and LOG is performed.
> I set up the autoshink as well. is it required?
> Why the log file increase so much and how to set up correctly the
> transaction log file properties?
>
> Thanks on advance.
> Ina
>
|||Thanks
mdf: 9 MO (Mega Octets)
ldf : 14 MO
yes it is small DB but before, as I did not know about backup LOG and
shrink log file, I had for the same ldf file a size of 13 Go, and this
is big.
Really thank you because I solved this problem and everything works
file now.
These information helped me a bunch.
Ina
Robert Klemme wrote:
> On 17.01.2007 10:26, ina wrote:
> What unit is that? Megabytes? If so this is really a small DB you
> should not worry about sizes in that area at all.
>
> As Uri pointed out, rather switch this off since you only waste
> performance. The log will grow and shrink anyway so you can leave it at
> the largest size.
>
> Because apparently you DB needs the space (you mention "massive inserts").
> Kind regards
> robert
sql
I have question concerning the log file size:
I have a DB that receives a massive amount of data every two days and
several Transaction (USP) and delete records are usually done on it.
During these operations, the log size goes up until 32 Mo.
the size of the .mdf is 9Mo and .ldf is 14Mo
The DB is set to FULL and a backup DB and LOG is performed.
I set up the autoshink as well. is it required?
Why the log file increase so much and how to set up correctly the
transaction log file properties?
Thanks on advance.
Ina
ina
> I set up the autoshink as well. is it required?
No, disable this option
> Why the log file increase so much and how to set up correctly the
> transaction log file properties?
Why do you concern about that the log file is growing? That means that SQL
Server needs more virtual log files to deal with data. Make sure that you
have a free space enough for log file
http://www.karaszi.com/SQLServer/info_dont_shrink.asp
"ina" <roberta.inalbon@.gmail.com> wrote in message
news:1169025984.073903.91700@.v45g2000cwv.googlegro ups.com...
> Hello Guys,
> I have question concerning the log file size:
> I have a DB that receives a massive amount of data every two days and
> several Transaction (USP) and delete records are usually done on it.
> During these operations, the log size goes up until 32 Mo.
> the size of the .mdf is 9Mo and .ldf is 14Mo
> The DB is set to FULL and a backup DB and LOG is performed.
> I set up the autoshink as well. is it required?
> Why the log file increase so much and how to set up correctly the
> transaction log file properties?
>
> Thanks on advance.
> Ina
>
|||Thanks
mdf: 9 MO (Mega Octets)
ldf : 14 MO
yes it is small DB but before, as I did not know about backup LOG and
shrink log file, I had for the same ldf file a size of 13 Go, and this
is big.
Really thank you because I solved this problem and everything works
file now.
These information helped me a bunch.
Ina
Robert Klemme wrote:
> On 17.01.2007 10:26, ina wrote:
> What unit is that? Megabytes? If so this is really a small DB you
> should not worry about sizes in that area at all.
>
> As Uri pointed out, rather switch this off since you only waste
> performance. The log will grow and shrink anyway so you can leave it at
> the largest size.
>
> Because apparently you DB needs the space (you mention "massive inserts").
> Kind regards
> robert
sql
How to set up the transaction log file properties?
Hello Guys,
I have question concerning the log file size:
I have a DB that receives a massive amount of data every two days and
several Transaction (USP) and delete records are usually done on it.
During these operations, the log size goes up until 32 Mo.
the size of the .mdf is 9Mo and .ldf is 14Mo
The DB is set to FULL and a backup DB and LOG is performed.
I set up the autoshink as well. is it required?
Why the log file increase so much and how to set up correctly the
transaction log file properties?
Thanks on advance.
Inaina
> I set up the autoshink as well. is it required?
No, disable this option
> Why the log file increase so much and how to set up correctly the
> transaction log file properties?
Why do you concern about that the log file is growing? That means that SQL
Server needs more virtual log files to deal with data. Make sure that you
have a free space enough for log file
http://www.karaszi.com/SQLServer/info_dont_shrink.asp
"ina" <roberta.inalbon@.gmail.com> wrote in message
news:1169025984.073903.91700@.v45g2000cwv.googlegroups.com...
> Hello Guys,
> I have question concerning the log file size:
> I have a DB that receives a massive amount of data every two days and
> several Transaction (USP) and delete records are usually done on it.
> During these operations, the log size goes up until 32 Mo.
> the size of the .mdf is 9Mo and .ldf is 14Mo
> The DB is set to FULL and a backup DB and LOG is performed.
> I set up the autoshink as well. is it required?
> Why the log file increase so much and how to set up correctly the
> transaction log file properties?
>
> Thanks on advance.
> Ina
>|||On 17.01.2007 10:26, ina wrote:
> I have a DB that receives a massive amount of data every two days and
> several Transaction (USP) and delete records are usually done on it.
> During these operations, the log size goes up until 32 Mo.
What unit is that? Megabytes? If so this is really a small DB you
should not worry about sizes in that area at all.
> the size of the .mdf is 9Mo and .ldf is 14Mo
> The DB is set to FULL and a backup DB and LOG is performed.
> I set up the autoshink as well. is it required?
As Uri pointed out, rather switch this off since you only waste
performance. The log will grow and shrink anyway so you can leave it at
the largest size.
> Why the log file increase so much and how to set up correctly the
> transaction log file properties?
Because apparently you DB needs the space (you mention "massive inserts").
Kind regards
robert|||Thanks
mdf: 9 MO (Mega Octets)
ldf : 14 MO
yes it is small DB but before, as I did not know about backup LOG and
shrink log file, I had for the same ldf file a size of 13 Go, and this
is big.
Really thank you because I solved this problem and everything works
file now.
These information helped me a bunch.
Ina
Robert Klemme wrote:
> On 17.01.2007 10:26, ina wrote:
> What unit is that? Megabytes? If so this is really a small DB you
> should not worry about sizes in that area at all.
>
> As Uri pointed out, rather switch this off since you only waste
> performance. The log will grow and shrink anyway so you can leave it at
> the largest size.
>
> Because apparently you DB needs the space (you mention "massive inserts").
> Kind regards
> robert
I have question concerning the log file size:
I have a DB that receives a massive amount of data every two days and
several Transaction (USP) and delete records are usually done on it.
During these operations, the log size goes up until 32 Mo.
the size of the .mdf is 9Mo and .ldf is 14Mo
The DB is set to FULL and a backup DB and LOG is performed.
I set up the autoshink as well. is it required?
Why the log file increase so much and how to set up correctly the
transaction log file properties?
Thanks on advance.
Inaina
> I set up the autoshink as well. is it required?
No, disable this option
> Why the log file increase so much and how to set up correctly the
> transaction log file properties?
Why do you concern about that the log file is growing? That means that SQL
Server needs more virtual log files to deal with data. Make sure that you
have a free space enough for log file
http://www.karaszi.com/SQLServer/info_dont_shrink.asp
"ina" <roberta.inalbon@.gmail.com> wrote in message
news:1169025984.073903.91700@.v45g2000cwv.googlegroups.com...
> Hello Guys,
> I have question concerning the log file size:
> I have a DB that receives a massive amount of data every two days and
> several Transaction (USP) and delete records are usually done on it.
> During these operations, the log size goes up until 32 Mo.
> the size of the .mdf is 9Mo and .ldf is 14Mo
> The DB is set to FULL and a backup DB and LOG is performed.
> I set up the autoshink as well. is it required?
> Why the log file increase so much and how to set up correctly the
> transaction log file properties?
>
> Thanks on advance.
> Ina
>|||On 17.01.2007 10:26, ina wrote:
> I have a DB that receives a massive amount of data every two days and
> several Transaction (USP) and delete records are usually done on it.
> During these operations, the log size goes up until 32 Mo.
What unit is that? Megabytes? If so this is really a small DB you
should not worry about sizes in that area at all.
> the size of the .mdf is 9Mo and .ldf is 14Mo
> The DB is set to FULL and a backup DB and LOG is performed.
> I set up the autoshink as well. is it required?
As Uri pointed out, rather switch this off since you only waste
performance. The log will grow and shrink anyway so you can leave it at
the largest size.
> Why the log file increase so much and how to set up correctly the
> transaction log file properties?
Because apparently you DB needs the space (you mention "massive inserts").
Kind regards
robert|||Thanks
mdf: 9 MO (Mega Octets)
ldf : 14 MO
yes it is small DB but before, as I did not know about backup LOG and
shrink log file, I had for the same ldf file a size of 13 Go, and this
is big.
Really thank you because I solved this problem and everything works
file now.
These information helped me a bunch.
Ina
Robert Klemme wrote:
> On 17.01.2007 10:26, ina wrote:
> What unit is that? Megabytes? If so this is really a small DB you
> should not worry about sizes in that area at all.
>
> As Uri pointed out, rather switch this off since you only waste
> performance. The log will grow and shrink anyway so you can leave it at
> the largest size.
>
> Because apparently you DB needs the space (you mention "massive inserts").
> Kind regards
> robert
Monday, March 19, 2012
How to set transactions timeout?
Is there a way for a client to tell a server to rollback a transaction after a certain time elapses?
The scenario: I execute an update statement in a RepeatableRead transaction, but the client may lose network connectivity before the Commit is issued. If this occurs after ExecuteNonQuery, then the process on the sql server is holding a large amount of locks, and the server takes several minutes before it releases those locks.
Meanwhile, other transactions are attempting to run, but are getting selected as the deadlock victims because they are waiting on resources that the disconnected client had locked. This really backs things up.
I have found some settings that can be done on the server side that will decrease the time it takes for a transaction WAITING on resources to timeout. I.E. this just makes those that are being blocked timeout faster. But it is the blockING process that I want rolledback sooner. It doesn't get selected as the deadlock victim because it has all the resources it needs. It is not waiting on any resources, but instead the server is simply waiting for another query or a commit, which it never gets because the client lost network connectivity. The command timeout doesn't seem to have an affect, because the command itself completes.
I tested this by having a client display a modal dialog box just before the commit statement, and then I unplugged the network cable. I then used activity monitor on the server to view the locks being held. I refreshed it several times, and I believe at least 5 minutes pasted before the locks were released.
In the production environment, users will be losing connectivity very often. I'm sure someone will suggest a different architecture where the database logic is server side, and data is sent to/from clients in another manner so that this situation can be handled by the server side app. Right now that is not an option. I am running the database in 2000, but sql server 2005 with 2000 compatibility mode is also possible.
Thanks in advance.
If you are using distributed transactions, the timeout can be configured via Control Panel->Administrative Tools->Component Services.
See also http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=772404&SiteID=1
|||I'm not using distributed transactions unless that's the default for ADO.NET, but I'll read up on them and see if maybe that's what I should use. Thanks.How to set transactions timeout?
Is there a way for a client to tell a server to rollback a transaction after a certain time elapses?
The scenario: I execute an update statement in a RepeatableRead transaction, but the client may lose network connectivity before the Commit is issued. If this occurs after ExecuteNonQuery, then the process on the sql server is holding a large amount of locks, and the server takes several minutes before it releases those locks.
Meanwhile, other transactions are attempting to run, but are getting selected as the deadlock victims because they are waiting on resources that the disconnected client had locked. This really backs things up.
I have found some settings that can be done on the server side that will decrease the time it takes for a transaction WAITING on resources to timeout. I.E. this just makes those that are being blocked timeout faster. But it is the blockING process that I want rolledback sooner. It doesn't get selected as the deadlock victim because it has all the resources it needs. It is not waiting on any resources, but instead the server is simply waiting for another query or a commit, which it never gets because the client lost network connectivity. The command timeout doesn't seem to have an affect, because the command itself completes.
I tested this by having a client display a modal dialog box just before the commit statement, and then I unplugged the network cable. I then used activity monitor on the server to view the locks being held. I refreshed it several times, and I believe at least 5 minutes pasted before the locks were released.
In the production environment, users will be losing connectivity very often. I'm sure someone will suggest a different architecture where the database logic is server side, and data is sent to/from clients in another manner so that this situation can be handled by the server side app. Right now that is not an option. I am running the database in 2000, but sql server 2005 with 2000 compatibility mode is also possible.
Thanks in advance.
If you are using distributed transactions, the timeout can be configured via Control Panel->Administrative Tools->Component Services.
See also http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=772404&SiteID=1
|||I'm not using distributed transactions unless that's the default for ADO.NET, but I'll read up on them and see if maybe that's what I should use. Thanks.
Subscribe to:
Posts (Atom)