I'm assuming that the .ldf file houses all the transaction logs, well mine is getting quite large (like 6 times the size of my .mdf) and I'm wondering if there is a way to purge logs back to a specific date. Can anyone give me some direction? ThanksYou can't clear the log selectively... Its unfortunately an "all or nothing" proposition. Check out the How To (http://msdn.microsoft.com/library/default.asp?url=/library/en-us/howtosql/ht_7_backpc_4915.asp) from BOL.
-PatP|||Your log files grows so large because there are action queries against your database that modify large volumes of data. Assuming your Recovery Mode is Simple, the smaller your transactions in terms of volume of data affected, the less chances are that your log would grow. If the recovery mode is Full or Bulk-Logged, the growth also depends on the frequency of transaction log or full database backups.
Showing posts with label size. Show all posts
Showing posts with label size. Show all posts
Wednesday, March 21, 2012
Monday, March 12, 2012
Pull Merge Replication - Expand Column Size
I need to increase the size of a column in a table which is part of merge
replication.
Here's the approach I believe I need to take:
1) Use sp_repladdcolumn to create a new temporary column and use the
schema_script to populate my new column
with the values from my old column.
2) Use sp_repldropcolumn to drop the old column
3) Use sp_repladdcolumn to create a new column using the "old column" name
and use the
schema_script to populate the new column with the values.
4) Use sp_repldropcolumn to drop the temporary column created in step 1.
Questions:
1) Do I need do these steps on both the publisher and subscriber?
2) Million Dollar Question - Will this work without having to reinitialize
all my subscribers?
Thanks for all your help!
Tina
1) just publisher
2) yes
Rgds,
Paul Ibison
[vbcol=seagreen]
|||The sp_repladdcolumn system stored procedure adds the dummy column but it
doesn't run the script I set for the schema_change_script parameter.
Is there something I'm missing?
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:e2SwX4IsEHA.4008@.TK2MSFTNGP14.phx.gbl...
> 1) just publisher
> 2) yes
> Rgds,
> Paul Ibison
>
>
|||I have found in testing that this approach will NOT work when using a Push
ExchangeType ( 1 ). Replication makes the schema change on the subscriber
but the values in the column are lost.
"Tina Smith" <tb.smith@.earthlink.net> wrote in message
news:%23d9WM8HsEHA.1752@.TK2MSFTNGP14.phx.gbl...
> I need to increase the size of a column in a table which is part of merge
> replication.
> Here's the approach I believe I need to take:
> 1) Use sp_repladdcolumn to create a new temporary column and use the
> schema_script to populate my new column
> with the values from my old column.
> 2) Use sp_repldropcolumn to drop the old column
> 3) Use sp_repladdcolumn to create a new column using the "old column" name
> and use the
> schema_script to populate the new column with the values.
> 4) Use sp_repldropcolumn to drop the temporary column created in step 1.
> --
> Questions:
> 1) Do I need do these steps on both the publisher and subscriber?
> 2) Million Dollar Question - Will this work without having to reinitialize
> all my subscribers?
> Thanks for all your help!
> Tina
>
|||use sp_repladdcolumn to add a dummy column.
Then update the the value of the dummy column with the value of the column
whose data type you want to change. Do this on the publisher.
Then use sp_repldropcolumn to drop the column which you wish to modify.
Then use sp_repladdcolumn to add the column back in with the correct
datatype.
Then update the value of the new column with the value of the dummy column.
Do this on the publisher.
Then drop the dummy column using sp_repldropcolumn.
Do all the work on the publisher, you shouldn't have to do anything on the
subscriber. You do not have to reinitialize all of your subscribers.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
"Tina Smith" <tb.smith@.earthlink.net> wrote in message
news:%23d9WM8HsEHA.1752@.TK2MSFTNGP14.phx.gbl...
> I need to increase the size of a column in a table which is part of merge
> replication.
> Here's the approach I believe I need to take:
> 1) Use sp_repladdcolumn to create a new temporary column and use the
> schema_script to populate my new column
> with the values from my old column.
> 2) Use sp_repldropcolumn to drop the old column
> 3) Use sp_repladdcolumn to create a new column using the "old column" name
> and use the
> schema_script to populate the new column with the values.
> 4) Use sp_repldropcolumn to drop the temporary column created in step 1.
> --
> Questions:
> 1) Do I need do these steps on both the publisher and subscriber?
> 2) Million Dollar Question - Will this work without having to reinitialize
> all my subscribers?
> Thanks for all your help!
> Tina
>
|||Tina,
what is in the @.schema_change_script that you are
sending? This parameter shouldn't be nesessary for merge.
If it is a script to populate the column, try a simple
update statement instead (on the publisher).[vbcol=seagreen]
|||It's a script to populate the column with the values from the old column.
update CustomerContact
Set dummy = State
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:037e01c4b106$83c0b060$a401280a@.phx.gbl...
> Tina,
> what is in the @.schema_change_script that you are
> sending? This parameter shouldn't be nesessary for merge.
> If it is a script to populate the column, try a simple
> update statement instead (on the publisher).
>
|||I went with the following steps since I couldn't get the schema_script
parameter to work.
All went well on the Publisher, I now have a varchar(3) state column with
the data. I then ran the agent on the subscriber and ended up with a
varchar(3) state column BUT with no data. My subscriber is a PUSH (
exchangetype (1 ) ) for this publication so I don't see how it will work.
sp_repladdcolumn @.source_object = 'CustomerContact'
, @.column = 'Dummy'
, @.typetext = 'varchar(3)'
, @.publication_to_add = 'SAM_HomeOffice'
GO
update CustomerContact
set dummy = state
GO
sp_repldropcolumn @.source_object = 'CustomerContact'
, @.column = 'state'
GO
sp_repladdcolumn @.source_object = 'CustomerContact'
, @.column = 'State'
, @.typetext = 'varchar(3)'
, @.publication_to_add = 'SAM_HomeOffice'
GO
update CustomerContact
set state = dummy
GO
sp_repldropcolumn @.source_object = 'CustomerContact' , @.column = 'dummy'
"Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
news:eko7LbMsEHA.1032@.TK2MSFTNGP10.phx.gbl...
> use sp_repladdcolumn to add a dummy column.
> Then update the the value of the dummy column with the value of the column
> whose data type you want to change. Do this on the publisher.
> Then use sp_repldropcolumn to drop the column which you wish to modify.
> Then use sp_repladdcolumn to add the column back in with the correct
> datatype.
> Then update the value of the new column with the value of the dummy
column.[vbcol=seagreen]
> Do this on the publisher.
> Then drop the dummy column using sp_repldropcolumn.
> Do all the work on the publisher, you shouldn't have to do anything on the
> subscriber. You do not have to reinitialize all of your subscribers.
> --
> Hilary Cotter
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
>
> "Tina Smith" <tb.smith@.earthlink.net> wrote in message
> news:%23d9WM8HsEHA.1752@.TK2MSFTNGP14.phx.gbl...
merge[vbcol=seagreen]
name[vbcol=seagreen]
reinitialize
>
|||Pls try a simple update statement (on the publisher)
after the sp_repladdcolumn statement and miss out the
@.schema_change_script parameter.
TIA,
Paul Ibison
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||Sorry Tina,
ignore my last message as in your more recent post you
mentioned the -EXCHANGETYPE forcing uploading. Please try
putting the update statement in sp_addscriptexec as a
step after adding the column.
Rgds,
Paul Ibison
replication.
Here's the approach I believe I need to take:
1) Use sp_repladdcolumn to create a new temporary column and use the
schema_script to populate my new column
with the values from my old column.
2) Use sp_repldropcolumn to drop the old column
3) Use sp_repladdcolumn to create a new column using the "old column" name
and use the
schema_script to populate the new column with the values.
4) Use sp_repldropcolumn to drop the temporary column created in step 1.
Questions:
1) Do I need do these steps on both the publisher and subscriber?
2) Million Dollar Question - Will this work without having to reinitialize
all my subscribers?
Thanks for all your help!
Tina
1) just publisher
2) yes
Rgds,
Paul Ibison
[vbcol=seagreen]
|||The sp_repladdcolumn system stored procedure adds the dummy column but it
doesn't run the script I set for the schema_change_script parameter.
Is there something I'm missing?
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:e2SwX4IsEHA.4008@.TK2MSFTNGP14.phx.gbl...
> 1) just publisher
> 2) yes
> Rgds,
> Paul Ibison
>
>
|||I have found in testing that this approach will NOT work when using a Push
ExchangeType ( 1 ). Replication makes the schema change on the subscriber
but the values in the column are lost.
"Tina Smith" <tb.smith@.earthlink.net> wrote in message
news:%23d9WM8HsEHA.1752@.TK2MSFTNGP14.phx.gbl...
> I need to increase the size of a column in a table which is part of merge
> replication.
> Here's the approach I believe I need to take:
> 1) Use sp_repladdcolumn to create a new temporary column and use the
> schema_script to populate my new column
> with the values from my old column.
> 2) Use sp_repldropcolumn to drop the old column
> 3) Use sp_repladdcolumn to create a new column using the "old column" name
> and use the
> schema_script to populate the new column with the values.
> 4) Use sp_repldropcolumn to drop the temporary column created in step 1.
> --
> Questions:
> 1) Do I need do these steps on both the publisher and subscriber?
> 2) Million Dollar Question - Will this work without having to reinitialize
> all my subscribers?
> Thanks for all your help!
> Tina
>
|||use sp_repladdcolumn to add a dummy column.
Then update the the value of the dummy column with the value of the column
whose data type you want to change. Do this on the publisher.
Then use sp_repldropcolumn to drop the column which you wish to modify.
Then use sp_repladdcolumn to add the column back in with the correct
datatype.
Then update the value of the new column with the value of the dummy column.
Do this on the publisher.
Then drop the dummy column using sp_repldropcolumn.
Do all the work on the publisher, you shouldn't have to do anything on the
subscriber. You do not have to reinitialize all of your subscribers.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
"Tina Smith" <tb.smith@.earthlink.net> wrote in message
news:%23d9WM8HsEHA.1752@.TK2MSFTNGP14.phx.gbl...
> I need to increase the size of a column in a table which is part of merge
> replication.
> Here's the approach I believe I need to take:
> 1) Use sp_repladdcolumn to create a new temporary column and use the
> schema_script to populate my new column
> with the values from my old column.
> 2) Use sp_repldropcolumn to drop the old column
> 3) Use sp_repladdcolumn to create a new column using the "old column" name
> and use the
> schema_script to populate the new column with the values.
> 4) Use sp_repldropcolumn to drop the temporary column created in step 1.
> --
> Questions:
> 1) Do I need do these steps on both the publisher and subscriber?
> 2) Million Dollar Question - Will this work without having to reinitialize
> all my subscribers?
> Thanks for all your help!
> Tina
>
|||Tina,
what is in the @.schema_change_script that you are
sending? This parameter shouldn't be nesessary for merge.
If it is a script to populate the column, try a simple
update statement instead (on the publisher).[vbcol=seagreen]
|||It's a script to populate the column with the values from the old column.
update CustomerContact
Set dummy = State
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:037e01c4b106$83c0b060$a401280a@.phx.gbl...
> Tina,
> what is in the @.schema_change_script that you are
> sending? This parameter shouldn't be nesessary for merge.
> If it is a script to populate the column, try a simple
> update statement instead (on the publisher).
>
|||I went with the following steps since I couldn't get the schema_script
parameter to work.
All went well on the Publisher, I now have a varchar(3) state column with
the data. I then ran the agent on the subscriber and ended up with a
varchar(3) state column BUT with no data. My subscriber is a PUSH (
exchangetype (1 ) ) for this publication so I don't see how it will work.
sp_repladdcolumn @.source_object = 'CustomerContact'
, @.column = 'Dummy'
, @.typetext = 'varchar(3)'
, @.publication_to_add = 'SAM_HomeOffice'
GO
update CustomerContact
set dummy = state
GO
sp_repldropcolumn @.source_object = 'CustomerContact'
, @.column = 'state'
GO
sp_repladdcolumn @.source_object = 'CustomerContact'
, @.column = 'State'
, @.typetext = 'varchar(3)'
, @.publication_to_add = 'SAM_HomeOffice'
GO
update CustomerContact
set state = dummy
GO
sp_repldropcolumn @.source_object = 'CustomerContact' , @.column = 'dummy'
"Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
news:eko7LbMsEHA.1032@.TK2MSFTNGP10.phx.gbl...
> use sp_repladdcolumn to add a dummy column.
> Then update the the value of the dummy column with the value of the column
> whose data type you want to change. Do this on the publisher.
> Then use sp_repldropcolumn to drop the column which you wish to modify.
> Then use sp_repladdcolumn to add the column back in with the correct
> datatype.
> Then update the value of the new column with the value of the dummy
column.[vbcol=seagreen]
> Do this on the publisher.
> Then drop the dummy column using sp_repldropcolumn.
> Do all the work on the publisher, you shouldn't have to do anything on the
> subscriber. You do not have to reinitialize all of your subscribers.
> --
> Hilary Cotter
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
>
> "Tina Smith" <tb.smith@.earthlink.net> wrote in message
> news:%23d9WM8HsEHA.1752@.TK2MSFTNGP14.phx.gbl...
merge[vbcol=seagreen]
name[vbcol=seagreen]
reinitialize
>
|||Pls try a simple update statement (on the publisher)
after the sp_repladdcolumn statement and miss out the
@.schema_change_script parameter.
TIA,
Paul Ibison
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||Sorry Tina,
ignore my last message as in your more recent post you
mentioned the -EXCHANGETYPE forcing uploading. Please try
putting the update statement in sp_addscriptexec as a
step after adding the column.
Rgds,
Paul Ibison
PULL huge table from SQL Server Problem
Hey Guys
I have meet the same problem, too.
I create a table with 1,380,000 rows data,
the db real size about 114 MB.
The primary key size is nchar(6).
When I use RDA pull, I found that the primary
key in the PDA disappear. So, It took a long time
to get query response.
But when I delete some rows to 680,000 rows of data.
After I pull, The primary key can pull from the SQL Server.
PS: I didn't change any code. Just delete some rows.
Is that SQL-Mobile's bug?
PS: 1.Database and Temp Database limitation both are 384MB
2.If I use query analyzer to add primary key it works! so strange!!
3.Pull process return "S_OK".
4.After Pull process finished, the db connection still alive. It seems not like
time out problem.
5.Local Connection String:"Data Source='%s\\%s';SSCE
atabase Password='%s';SSCE:Encrypt Database='true';SSCE:Max Database Size=384;SSCE:Temp File Max size=384;SSCE:Temp File Directory=%s"
We do have tests that do PULL of 200 MB data and almost 10 Lakh rows. Is that possible for you to give the schema and few more details of your environment so that we can try reproducing and find the root cause.
Thanks,
Laxmi
Wednesday, March 7, 2012
publisher and subscriber database differ in size (newbie question)
"Ib Schrader" <ibschrader@.gmail.com> wrote in message
news:ea1chVrVIHA.1184@.TK2MSFTNGP04.phx.gbl...
> Hi there
> However when I look on the disk the 2 databases differ greatly in size,
> the one on the publisher is 450 Mb and the subscriber is 260 Mb.
Perhaps the larger database has a lot of free space and the smaller one
doesn't, or the transaction log on the subscriber is smaller as it isn't
handling the client transactions directly. Have a look at each database's
properties in Management Studio. Check the amount of free space on the
general tab, and check the size of the data files and transaction logs in
the Files tab.
"Ib Schrader" <ibschrader@.gmail.com> wrote in message
news:OkNC2vrVIHA.5160@.TK2MSFTNGP05.phx.gbl...
> Thanks for your reply.
> Should data size + available space (in the Enterprise Manager) equal size
> on disk?
I think Data Size is the actual size on disk inclusing the free space, but
I'm not sure.
I do remember there is a system stored procedure to give you all the details
about either the data file or log file, but I can't for the life of me
remember what it is.
news:ea1chVrVIHA.1184@.TK2MSFTNGP04.phx.gbl...
> Hi there
> However when I look on the disk the 2 databases differ greatly in size,
> the one on the publisher is 450 Mb and the subscriber is 260 Mb.
Perhaps the larger database has a lot of free space and the smaller one
doesn't, or the transaction log on the subscriber is smaller as it isn't
handling the client transactions directly. Have a look at each database's
properties in Management Studio. Check the amount of free space on the
general tab, and check the size of the data files and transaction logs in
the Files tab.
"Ib Schrader" <ibschrader@.gmail.com> wrote in message
news:OkNC2vrVIHA.5160@.TK2MSFTNGP05.phx.gbl...
> Thanks for your reply.
> Should data size + available space (in the Enterprise Manager) equal size
> on disk?
I think Data Size is the actual size on disk inclusing the free space, but
I'm not sure.
I do remember there is a system stored procedure to give you all the details
about either the data file or log file, but I can't for the life of me
remember what it is.
publisher and subscriber database differ in size (newbie question)
Hi there
I have 2 SQL servers. One of them is set up as publisher and the other as
subscriber. I have a database set up for replication so the changes gets
copied to the subscriber. I wish to use the subscriber in the event that the
publisher becomes unavaible (blue screen, hardware error, whatever)
However when I look on the disk the 2 databases differ greatly in size, the
one on the publisher is 450 Mb and the subscriber is 260 Mb.
Aren't these 2 supposed to be exactly the same size? It seems as if the
transaction log gets flushed weekly when I run a full backup of the
database, but the subscriber database is still consistently smaller.
Is this normal behaviour?
Thanks in advance for any input
Ib"Ib Schrader" <ibschrader@.gmail.com> wrote in message
news:ea1chVrVIHA.1184@.TK2MSFTNGP04.phx.gbl...
> Hi there
> However when I look on the disk the 2 databases differ greatly in size,
> the one on the publisher is 450 Mb and the subscriber is 260 Mb.
Perhaps the larger database has a lot of free space and the smaller one
doesn't, or the transaction log on the subscriber is smaller as it isn't
handling the client transactions directly. Have a look at each database's
properties in Management Studio. Check the amount of free space on the
general tab, and check the size of the data files and transaction logs in
the Files tab.|||Thanks for your reply.
Should data size + available space (in the Enterprise Manager) equal size on
disk?
I am running SQL 2000 by the way, if that makes a difference.
"Leon Mayne" <leon@.rmv_me.mvps.org> wrote in message
news:53744401-9495-4913-B1BF-A19AA3850C58@.microsoft.com...
> "Ib Schrader" <ibschrader@.gmail.com> wrote in message
> news:ea1chVrVIHA.1184@.TK2MSFTNGP04.phx.gbl...
>> Hi there
>> However when I look on the disk the 2 databases differ greatly in size,
>> the one on the publisher is 450 Mb and the subscriber is 260 Mb.
> Perhaps the larger database has a lot of free space and the smaller one
> doesn't, or the transaction log on the subscriber is smaller as it isn't
> handling the client transactions directly. Have a look at each database's
> properties in Management Studio. Check the amount of free space on the
> general tab, and check the size of the data files and transaction logs in
> the Files tab.|||"Ib Schrader" <ibschrader@.gmail.com> wrote in message
news:OkNC2vrVIHA.5160@.TK2MSFTNGP05.phx.gbl...
> Thanks for your reply.
> Should data size + available space (in the Enterprise Manager) equal size
> on disk?
I think Data Size is the actual size on disk inclusing the free space, but
I'm not sure.
I do remember there is a system stored procedure to give you all the details
about either the data file or log file, but I can't for the life of me
remember what it is.
I have 2 SQL servers. One of them is set up as publisher and the other as
subscriber. I have a database set up for replication so the changes gets
copied to the subscriber. I wish to use the subscriber in the event that the
publisher becomes unavaible (blue screen, hardware error, whatever)
However when I look on the disk the 2 databases differ greatly in size, the
one on the publisher is 450 Mb and the subscriber is 260 Mb.
Aren't these 2 supposed to be exactly the same size? It seems as if the
transaction log gets flushed weekly when I run a full backup of the
database, but the subscriber database is still consistently smaller.
Is this normal behaviour?
Thanks in advance for any input
Ib"Ib Schrader" <ibschrader@.gmail.com> wrote in message
news:ea1chVrVIHA.1184@.TK2MSFTNGP04.phx.gbl...
> Hi there
> However when I look on the disk the 2 databases differ greatly in size,
> the one on the publisher is 450 Mb and the subscriber is 260 Mb.
Perhaps the larger database has a lot of free space and the smaller one
doesn't, or the transaction log on the subscriber is smaller as it isn't
handling the client transactions directly. Have a look at each database's
properties in Management Studio. Check the amount of free space on the
general tab, and check the size of the data files and transaction logs in
the Files tab.|||Thanks for your reply.
Should data size + available space (in the Enterprise Manager) equal size on
disk?
I am running SQL 2000 by the way, if that makes a difference.
"Leon Mayne" <leon@.rmv_me.mvps.org> wrote in message
news:53744401-9495-4913-B1BF-A19AA3850C58@.microsoft.com...
> "Ib Schrader" <ibschrader@.gmail.com> wrote in message
> news:ea1chVrVIHA.1184@.TK2MSFTNGP04.phx.gbl...
>> Hi there
>> However when I look on the disk the 2 databases differ greatly in size,
>> the one on the publisher is 450 Mb and the subscriber is 260 Mb.
> Perhaps the larger database has a lot of free space and the smaller one
> doesn't, or the transaction log on the subscriber is smaller as it isn't
> handling the client transactions directly. Have a look at each database's
> properties in Management Studio. Check the amount of free space on the
> general tab, and check the size of the data files and transaction logs in
> the Files tab.|||"Ib Schrader" <ibschrader@.gmail.com> wrote in message
news:OkNC2vrVIHA.5160@.TK2MSFTNGP05.phx.gbl...
> Thanks for your reply.
> Should data size + available space (in the Enterprise Manager) equal size
> on disk?
I think Data Size is the actual size on disk inclusing the free space, but
I'm not sure.
I do remember there is a system stored procedure to give you all the details
about either the data file or log file, but I can't for the life of me
remember what it is.
Subscribe to:
Comments (Atom)