Hi,
I'm trying to set up a subscriber for merge replication over https. The
initial snapshot file is about 50 Gigs and I'm wondering if its
possible to download & store this snapshot somewhere other than on my
database drive (I have size contraints). Any ideas or direction?
Thanks,
JC
Hi
Yes this feature is available for merge replication, there is some more
information here about alternate snapshot locations.
http://msdn.microsoft.com/library/de...limpl_3vcj.asp
Nabila Lacey
<jbzcooper@.gmail.com> wrote in message
news:1139947715.833574.258470@.g47g2000cwa.googlegr oups.com...
> Hi,
> I'm trying to set up a subscriber for merge replication over https. The
> initial snapshot file is about 50 Gigs and I'm wondering if its
> possible to download & store this snapshot somewhere other than on my
> database drive (I have size contraints). Any ideas or direction?
> Thanks,
> JC
>
|||As well as Nabila's advice, you might want to consider using winzip 9.0 or
winrar to speed up the data transfer. Compression is available in SQL Server
but is limited to the 2GB limitation of a CAB file.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||Thank you both. I suppose I should have been more clear. I have no
control over the publication itself and thus cannot specify an
alternate location on the publisher. I was told that the drive I was
replicating to needed at least 100GB free to house both the snapshot
and the database it would be loaded into. Is it possible for me to zip
and download the snapshot to my subscriber (assuming they will let me)
on an alternate drive and point my subscription to that?
I do appreciate the help,
Jeremiah
|||Jeremiah,
the alternative snapshot location I was referring to is basically a
subscriber setting. the method you propose is exactly what I have done in
the past for large publications.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
Showing posts with label file. Show all posts
Showing posts with label file. Show all posts
Thursday, March 29, 2012
Alternate snapshot for push subscribtion
Can I define an alternate snapshot file location for a push subscriber?
How?
Have a look at the agent profile parameters - http://msdn.microsoft.com/library/de...trib_2f09.asp.
EG for the distribution agent there is a property -AltSnapshotFolder
Regards,
Paul Ibison
|||right click on your publication, select publication properties, select
snapshot location. Then select on the Generate snapshots in the following
location, and enter a new snapshot location. Then restart your snapshot
agent, and your distribution agent.
"Humam" <anonymous@.discussions.microsoft.com> wrote in message
news:490BF15E-9BC5-4042-BEC4-A98383BBFF18@.microsoft.com...
> Can I define an alternate snapshot file location for a push subscriber?
> How?
>
|||That is right, but I need to transfer the snapshot files
to where the subscriber resides (on CD's).
How can I configure the push subscriber to use the
snapshot files located at the subscriber?
Thanks for your help.
>--Original Message--
>right click on your publication, select publication
properties, select
>snapshot location. Then select on the Generate snapshots
in the following
>location, and enter a new snapshot location. Then restart
your snapshot
>agent, and your distribution agent.
>"Humam" <anonymous@.discussions.microsoft.com> wrote in
message
>news:490BF15E-9BC5-4042-BEC4-A98383BBFF18@.microsoft.com...
push subscriber?
>
>.
>
|||Run your snapshot agent. Copy the snapshot path from repldata on down to
your subscriber.
Then when you pull your subscriber go through the prompts using the wizard
until you get to the Snapshot Delivery dialog. Select the Use snapshot files
from the following folder and point to the repldata folder you have copied
from your publisher. Then click on next and continue to build your pull
subscription.
"Humam" <anonymous@.discussions.microsoft.com> wrote in message
news:1621e01c41711$30106440$a301280a@.phx.gbl...
> That is right, but I need to transfer the snapshot files
> to where the subscriber resides (on CD's).
> How can I configure the push subscriber to use the
> snapshot files located at the subscriber?
> Thanks for your help.
> properties, select
> in the following
> your snapshot
> message
> push subscriber?
How?
Have a look at the agent profile parameters - http://msdn.microsoft.com/library/de...trib_2f09.asp.
EG for the distribution agent there is a property -AltSnapshotFolder
Regards,
Paul Ibison
|||right click on your publication, select publication properties, select
snapshot location. Then select on the Generate snapshots in the following
location, and enter a new snapshot location. Then restart your snapshot
agent, and your distribution agent.
"Humam" <anonymous@.discussions.microsoft.com> wrote in message
news:490BF15E-9BC5-4042-BEC4-A98383BBFF18@.microsoft.com...
> Can I define an alternate snapshot file location for a push subscriber?
> How?
>
|||That is right, but I need to transfer the snapshot files
to where the subscriber resides (on CD's).
How can I configure the push subscriber to use the
snapshot files located at the subscriber?
Thanks for your help.
>--Original Message--
>right click on your publication, select publication
properties, select
>snapshot location. Then select on the Generate snapshots
in the following
>location, and enter a new snapshot location. Then restart
your snapshot
>agent, and your distribution agent.
>"Humam" <anonymous@.discussions.microsoft.com> wrote in
message
>news:490BF15E-9BC5-4042-BEC4-A98383BBFF18@.microsoft.com...
push subscriber?
>
>.
>
|||Run your snapshot agent. Copy the snapshot path from repldata on down to
your subscriber.
Then when you pull your subscriber go through the prompts using the wizard
until you get to the Snapshot Delivery dialog. Select the Use snapshot files
from the following folder and point to the repldata folder you have copied
from your publisher. Then click on next and continue to build your pull
subscription.
"Humam" <anonymous@.discussions.microsoft.com> wrote in message
news:1621e01c41711$30106440$a301280a@.phx.gbl...
> That is right, but I need to transfer the snapshot files
> to where the subscriber resides (on CD's).
> How can I configure the push subscriber to use the
> snapshot files located at the subscriber?
> Thanks for your help.
> properties, select
> in the following
> your snapshot
> message
> push subscriber?
Alternate Key (from good 'ole ISAM file days)
In an ISAM file, you can have a primary key and alternate keys.
In a SQLServer database, you can have a primary key and foreign keys
attached to other tables.
Pardon my ignorance, but is it possible to identify a field in a table as an
alternate lookup? For example, empid is the primary and emplastname would be
an alternate.
Ed
You can set up additional indexes on your tables. Since your Primary Key is
most likely clustered, these additional indexes will have to be
non-clustered. A good starting point might be to look at which queries are
run the most, and which ones are taking the most time, and index the columns
used in the WHERE clauses of those queries.
"Ed" <Ed@.discussions.microsoft.com> wrote in message
news:0C0B6107-E67E-4847-BBB0-0DD247DEACC6@.microsoft.com...
> In an ISAM file, you can have a primary key and alternate keys.
> In a SQLServer database, you can have a primary key and foreign keys
> attached to other tables.
> Pardon my ignorance, but is it possible to identify a field in a table as
> an
> alternate lookup? For example, empid is the primary and emplastname would
> be
> an alternate.
> --
> Ed
|||In a relational database, the term alternate key implies unique values.
Unique constraints are usually defined on alternate keys.
It looks like what you want is an index. You can add an index on your
emplastname column to improve performance.
Hope this helps.
Dan Guzman
SQL Server MVP
"Ed" <Ed@.discussions.microsoft.com> wrote in message
news:0C0B6107-E67E-4847-BBB0-0DD247DEACC6@.microsoft.com...
> In an ISAM file, you can have a primary key and alternate keys.
> In a SQLServer database, you can have a primary key and foreign keys
> attached to other tables.
> Pardon my ignorance, but is it possible to identify a field in a table as
> an
> alternate lookup? For example, empid is the primary and emplastname would
> be
> an alternate.
> --
> Ed
In a SQLServer database, you can have a primary key and foreign keys
attached to other tables.
Pardon my ignorance, but is it possible to identify a field in a table as an
alternate lookup? For example, empid is the primary and emplastname would be
an alternate.
Ed
You can set up additional indexes on your tables. Since your Primary Key is
most likely clustered, these additional indexes will have to be
non-clustered. A good starting point might be to look at which queries are
run the most, and which ones are taking the most time, and index the columns
used in the WHERE clauses of those queries.
"Ed" <Ed@.discussions.microsoft.com> wrote in message
news:0C0B6107-E67E-4847-BBB0-0DD247DEACC6@.microsoft.com...
> In an ISAM file, you can have a primary key and alternate keys.
> In a SQLServer database, you can have a primary key and foreign keys
> attached to other tables.
> Pardon my ignorance, but is it possible to identify a field in a table as
> an
> alternate lookup? For example, empid is the primary and emplastname would
> be
> an alternate.
> --
> Ed
|||In a relational database, the term alternate key implies unique values.
Unique constraints are usually defined on alternate keys.
It looks like what you want is an index. You can add an index on your
emplastname column to improve performance.
Hope this helps.
Dan Guzman
SQL Server MVP
"Ed" <Ed@.discussions.microsoft.com> wrote in message
news:0C0B6107-E67E-4847-BBB0-0DD247DEACC6@.microsoft.com...
> In an ISAM file, you can have a primary key and alternate keys.
> In a SQLServer database, you can have a primary key and foreign keys
> attached to other tables.
> Pardon my ignorance, but is it possible to identify a field in a table as
> an
> alternate lookup? For example, empid is the primary and emplastname would
> be
> an alternate.
> --
> Ed
Alternate Key (from good 'ole ISAM file days)
In an ISAM file, you can have a primary key and alternate keys.
In a SQLServer database, you can have a primary key and foreign keys
attached to other tables.
Pardon my ignorance, but is it possible to identify a field in a table as an
alternate lookup? For example, empid is the primary and emplastname would b
e
an alternate.
--
EdYou can set up additional indexes on your tables. Since your Primary Key is
most likely clustered, these additional indexes will have to be
non-clustered. A good starting point might be to look at which queries are
run the most, and which ones are taking the most time, and index the columns
used in the WHERE clauses of those queries.
"Ed" <Ed@.discussions.microsoft.com> wrote in message
news:0C0B6107-E67E-4847-BBB0-0DD247DEACC6@.microsoft.com...
> In an ISAM file, you can have a primary key and alternate keys.
> In a SQLServer database, you can have a primary key and foreign keys
> attached to other tables.
> Pardon my ignorance, but is it possible to identify a field in a table as
> an
> alternate lookup? For example, empid is the primary and emplastname would
> be
> an alternate.
> --
> Ed|||In a relational database, the term alternate key implies unique values.
Unique constraints are usually defined on alternate keys.
It looks like what you want is an index. You can add an index on your
emplastname column to improve performance.
Hope this helps.
Dan Guzman
SQL Server MVP
"Ed" <Ed@.discussions.microsoft.com> wrote in message
news:0C0B6107-E67E-4847-BBB0-0DD247DEACC6@.microsoft.com...
> In an ISAM file, you can have a primary key and alternate keys.
> In a SQLServer database, you can have a primary key and foreign keys
> attached to other tables.
> Pardon my ignorance, but is it possible to identify a field in a table as
> an
> alternate lookup? For example, empid is the primary and emplastname would
> be
> an alternate.
> --
> Ed
In a SQLServer database, you can have a primary key and foreign keys
attached to other tables.
Pardon my ignorance, but is it possible to identify a field in a table as an
alternate lookup? For example, empid is the primary and emplastname would b
e
an alternate.
--
EdYou can set up additional indexes on your tables. Since your Primary Key is
most likely clustered, these additional indexes will have to be
non-clustered. A good starting point might be to look at which queries are
run the most, and which ones are taking the most time, and index the columns
used in the WHERE clauses of those queries.
"Ed" <Ed@.discussions.microsoft.com> wrote in message
news:0C0B6107-E67E-4847-BBB0-0DD247DEACC6@.microsoft.com...
> In an ISAM file, you can have a primary key and alternate keys.
> In a SQLServer database, you can have a primary key and foreign keys
> attached to other tables.
> Pardon my ignorance, but is it possible to identify a field in a table as
> an
> alternate lookup? For example, empid is the primary and emplastname would
> be
> an alternate.
> --
> Ed|||In a relational database, the term alternate key implies unique values.
Unique constraints are usually defined on alternate keys.
It looks like what you want is an index. You can add an index on your
emplastname column to improve performance.
Hope this helps.
Dan Guzman
SQL Server MVP
"Ed" <Ed@.discussions.microsoft.com> wrote in message
news:0C0B6107-E67E-4847-BBB0-0DD247DEACC6@.microsoft.com...
> In an ISAM file, you can have a primary key and alternate keys.
> In a SQLServer database, you can have a primary key and foreign keys
> attached to other tables.
> Pardon my ignorance, but is it possible to identify a field in a table as
> an
> alternate lookup? For example, empid is the primary and emplastname would
> be
> an alternate.
> --
> Ed
Tuesday, March 27, 2012
Alternate Key (from good 'ole ISAM file days)
In an ISAM file, you can have a primary key and alternate keys.
In a SQLServer database, you can have a primary key and foreign keys
attached to other tables.
Pardon my ignorance, but is it possible to identify a field in a table as an
alternate lookup? For example, empid is the primary and emplastname would be
an alternate.
--
EdYou can set up additional indexes on your tables. Since your Primary Key is
most likely clustered, these additional indexes will have to be
non-clustered. A good starting point might be to look at which queries are
run the most, and which ones are taking the most time, and index the columns
used in the WHERE clauses of those queries.
"Ed" <Ed@.discussions.microsoft.com> wrote in message
news:0C0B6107-E67E-4847-BBB0-0DD247DEACC6@.microsoft.com...
> In an ISAM file, you can have a primary key and alternate keys.
> In a SQLServer database, you can have a primary key and foreign keys
> attached to other tables.
> Pardon my ignorance, but is it possible to identify a field in a table as
> an
> alternate lookup? For example, empid is the primary and emplastname would
> be
> an alternate.
> --
> Ed|||In a relational database, the term alternate key implies unique values.
Unique constraints are usually defined on alternate keys.
It looks like what you want is an index. You can add an index on your
emplastname column to improve performance.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Ed" <Ed@.discussions.microsoft.com> wrote in message
news:0C0B6107-E67E-4847-BBB0-0DD247DEACC6@.microsoft.com...
> In an ISAM file, you can have a primary key and alternate keys.
> In a SQLServer database, you can have a primary key and foreign keys
> attached to other tables.
> Pardon my ignorance, but is it possible to identify a field in a table as
> an
> alternate lookup? For example, empid is the primary and emplastname would
> be
> an alternate.
> --
> Edsql
In a SQLServer database, you can have a primary key and foreign keys
attached to other tables.
Pardon my ignorance, but is it possible to identify a field in a table as an
alternate lookup? For example, empid is the primary and emplastname would be
an alternate.
--
EdYou can set up additional indexes on your tables. Since your Primary Key is
most likely clustered, these additional indexes will have to be
non-clustered. A good starting point might be to look at which queries are
run the most, and which ones are taking the most time, and index the columns
used in the WHERE clauses of those queries.
"Ed" <Ed@.discussions.microsoft.com> wrote in message
news:0C0B6107-E67E-4847-BBB0-0DD247DEACC6@.microsoft.com...
> In an ISAM file, you can have a primary key and alternate keys.
> In a SQLServer database, you can have a primary key and foreign keys
> attached to other tables.
> Pardon my ignorance, but is it possible to identify a field in a table as
> an
> alternate lookup? For example, empid is the primary and emplastname would
> be
> an alternate.
> --
> Ed|||In a relational database, the term alternate key implies unique values.
Unique constraints are usually defined on alternate keys.
It looks like what you want is an index. You can add an index on your
emplastname column to improve performance.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Ed" <Ed@.discussions.microsoft.com> wrote in message
news:0C0B6107-E67E-4847-BBB0-0DD247DEACC6@.microsoft.com...
> In an ISAM file, you can have a primary key and alternate keys.
> In a SQLServer database, you can have a primary key and foreign keys
> attached to other tables.
> Pardon my ignorance, but is it possible to identify a field in a table as
> an
> alternate lookup? For example, empid is the primary and emplastname would
> be
> an alternate.
> --
> Edsql
Sunday, March 25, 2012
Altering a connection manager dynamically via a variable
Within an SSIS Package, we are trying to change the connection string of an output file connection manager at runtime (used for package logging).
To do this, we have defined variables with package-level scope and set the connection manager connection string to this variable. The first step of the package is to set these variables. The second step begins the rest of the package operations (moving data). The package executes successfully, but the log file is never created/appended to.
When the package is debugged, I can verify that the variables are being set correctly in the script task and that the variable values are being passed to the connection manager data sources.
Any ideas why this isn’t working?
You shouldn't use script tasks to try and change connection manager connection strings. Use this technique: http://blogs.conchango.com/jamiethomson/archive/2006/03/11/3063.aspx
-Jamie
|||Use the mthods in Jamie link that is how I do mine and it works great.Thursday, March 8, 2012
Alter Database while "Suspect"
Hi
I got the following error
Error: 823, Severity: 24, State: 4
I/O error 33(The process cannot access the file because another process has locked a portion of the file.) detected during write at offset
0x0000000a796000 in file xxxxxxxxx.ndf'.
and the respective database could not be brought online - this was just due to a problem with a .ndf file containing only indexes...is there any way to connect to/alter a database while it is in this transitional state? (it would be no loss if i could just remove the file & its filegroup)
(i tried starting with -f -c, but no go)
thanks in advance
desCheck in your virus scan software to see if it ignores *.mdf, *.ndf and *.ldf. I am assuming this is on startup of the database? Say after recycling the SQL Server service, or rebooting the machine?
Also, you will want to do some diagnostics on the disk (check with hardware vendor), in order to make sure the disk has not suddenly gone bad.
Good luck, and let us know what happens.|||What had happened was the folder/drive I put the index file on was set to compress contents (accidentally)...I figure the windows compression had a hold on it...happened after rebooting the machine (I thought it took real long to build those indexes - now i know why!)
Disk seems fine though...
Thanks for advice
cheers
des
(what happened ultimately was db loss & a 12 hour snapshot delivery...eughh. wish i couldve just removed the index file somehow...)|||I thought I read it somewhere not to use compression on any files SQL touches, maybe with the exception of the error logs. You can lock out any interactive access to the data and log folders to prevent future mishaps.
I got the following error
Error: 823, Severity: 24, State: 4
I/O error 33(The process cannot access the file because another process has locked a portion of the file.) detected during write at offset
0x0000000a796000 in file xxxxxxxxx.ndf'.
and the respective database could not be brought online - this was just due to a problem with a .ndf file containing only indexes...is there any way to connect to/alter a database while it is in this transitional state? (it would be no loss if i could just remove the file & its filegroup)
(i tried starting with -f -c, but no go)
thanks in advance
desCheck in your virus scan software to see if it ignores *.mdf, *.ndf and *.ldf. I am assuming this is on startup of the database? Say after recycling the SQL Server service, or rebooting the machine?
Also, you will want to do some diagnostics on the disk (check with hardware vendor), in order to make sure the disk has not suddenly gone bad.
Good luck, and let us know what happens.|||What had happened was the folder/drive I put the index file on was set to compress contents (accidentally)...I figure the windows compression had a hold on it...happened after rebooting the machine (I thought it took real long to build those indexes - now i know why!)
Disk seems fine though...
Thanks for advice
cheers
des
(what happened ultimately was db loss & a 12 hour snapshot delivery...eughh. wish i couldve just removed the index file somehow...)|||I thought I read it somewhere not to use compression on any files SQL touches, maybe with the exception of the error logs. You can lock out any interactive access to the data and log folders to prevent future mishaps.
Wednesday, March 7, 2012
ALTER DATABASE MODIFY NAME but
Leave the file location/directory the heck alone!
How do I do this?
Just issuing:
ALTER DATABASE Old_Name MODIFY NAME = New_Name
moves the mdf and ldf files to a new, unwanted location (apparently the SQL Server default as it's under program files) with the new name.
Is this possible or do I have to issue the additional ALTER DATABASE MODIFY FILE statements for this?modify name does not move the file(s).
create database [test]
on(name=test,filename='c:\test.mdf')
log on(name=test_log,filename='c:\test.ldf')
go
alter database [test] modify name=newtest
go
select *
from [newtest]..sysfiles
go
drop database [newtest]
go
==result==
1 test c:\test.mdf
2 test_log c:\test.ldf|||
Hi,
ALTER DATABASE ... MODIFY FILE command modifies the names of data files of the related database.
Please check the following article for also a sample on changing logical file names of SQL databases http://www.kodyaz.com/articles/change-sql-server-database-file-names.aspx
Eralper
|||Guess you didn't read my full post. I'm aware of this command.|||did you try my demo script. do you get the expected result?|||
Yeah, turns out it's not that part of the script but rather the Copy Database wizard that is to blame.
(I've got a post in tools on it bu gtno replies yet.)
ALTER DATABASE (optimization job)
Hi all
I have a database (warehouse) with a data file 16GB with Recovery model FULL
And each week I do a night run for optimization with the options "Reorganize
data and index pages" and "Change free space per page percentage to 10%"
When this night run occurs, the transaction log on my database increases to
17GB
cause to recreation of indexes and so on.
I was wondering about the following, so I could solve the problem of 17GB
transaction logs. The disks are not cheap in an external sub-system with
mirrors and stripes.
1) Before the optimization job start backup the database
2) After the backup change the database recovery model to simple (so no log
will be recorded
3) Backup again database (now on simple mode) so transaction log will be
shrink
4) Run the optimization job
5) Change the database recovery model back to FULL
6) Backup again database (now in FULL mode)
That's the solution I have thought.and all that will be done by the night
run
How dangerous is to change the recovery model of the database before you run
a job' Is the risk high'
Is there an other way to perform the task, without having my transaction log
increased so match?
Thanks in advance
Dimitris Dimolas
Web Programmer
GreeceWhy do you want to shrink the file each week? SQL Server is designed to work
with pre-allocation of space. By
not shrinking, you don't have to do anything special at all!
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/
"Dimitris" <seeyou_gr@.hotmail.com> wrote in message news:uW1IwixPEHA.3216@.TK2MSFTNGP12.phx.g
bl...
> Hi all
> I have a database (warehouse) with a data file 16GB with Recovery model FU
LL
> And each week I do a night run for optimization with the options "Reorgani
ze
> data and index pages" and "Change free space per page percentage to 10%"
> When this night run occurs, the transaction log on my database increases t
o
> 17GB
> cause to recreation of indexes and so on.
> I was wondering about the following, so I could solve the problem of 17GB
> transaction logs. The disks are not cheap in an external sub-system with
> mirrors and stripes.
> 1) Before the optimization job start backup the database
> 2) After the backup change the database recovery model to simple (so no lo
g
> will be recorded
> 3) Backup again database (now on simple mode) so transaction log will be
> shrink
> 4) Run the optimization job
> 5) Change the database recovery model back to FULL
> 6) Backup again database (now in FULL mode)
>
> That's the solution I have thought.and all that will be done by the night
> run
>
> How dangerous is to change the recovery model of the database before you r
un
> a job' Is the risk high'
>
> Is there an other way to perform the task, without having my transaction l
og
> increased so match?
>
> Thanks in advance
>
> Dimitris Dimolas
> Web Programmer
> Greece
>|||In addition to the other responses, see the whitepaper at
http://www.microsoft.com/technet/pr...n/ss2kidbp.mspx
for index maintenance tips.
Hope this helps.
Dan Guzman
SQL Server MVP
"Dimitris" <seeyou_gr@.hotmail.com> wrote in message
news:uW1IwixPEHA.3216@.TK2MSFTNGP12.phx.gbl...
> Hi all
> I have a database (warehouse) with a data file 16GB with Recovery model
FULL
> And each week I do a night run for optimization with the options
"Reorganize
> data and index pages" and "Change free space per page percentage to 10%"
> When this night run occurs, the transaction log on my database increases
to
> 17GB
> cause to recreation of indexes and so on.
> I was wondering about the following, so I could solve the problem of 17GB
> transaction logs. The disks are not cheap in an external sub-system with
> mirrors and stripes.
> 1) Before the optimization job start backup the database
> 2) After the backup change the database recovery model to simple (so no
log
> will be recorded
> 3) Backup again database (now on simple mode) so transaction log will be
> shrink
> 4) Run the optimization job
> 5) Change the database recovery model back to FULL
> 6) Backup again database (now in FULL mode)
>
> That's the solution I have thought.and all that will be done by the night
> run
>
> How dangerous is to change the recovery model of the database before you
run
> a job' Is the risk high'
>
> Is there an other way to perform the task, without having my transaction
log
> increased so match?
>
> Thanks in advance
>
> Dimitris Dimolas
> Web Programmer
> Greece
>|||My transactions in a day are about 4gb
So why should i have a log of 17gb'
And the question isn't why.
But how i can done it
You can understand ofcource that if you have a log of 4 GB per day there is
no reason to have a log of 17GB when you r trying to optimize your database.
There is no need.
Thanks
"Dimitris" <seeyou_gr@.hotmail.com> wrote in message
news:uW1IwixPEHA.3216@.TK2MSFTNGP12.phx.gbl...
> Hi all
> I have a database (warehouse) with a data file 16GB with Recovery model
FULL
> And each week I do a night run for optimization with the options
"Reorganize
> data and index pages" and "Change free space per page percentage to 10%"
> When this night run occurs, the transaction log on my database increases
to
> 17GB
> cause to recreation of indexes and so on.
> I was wondering about the following, so I could solve the problem of 17GB
> transaction logs. The disks are not cheap in an external sub-system with
> mirrors and stripes.
> 1) Before the optimization job start backup the database
> 2) After the backup change the database recovery model to simple (so no
log
> will be recorded
> 3) Backup again database (now on simple mode) so transaction log will be
> shrink
> 4) Run the optimization job
> 5) Change the database recovery model back to FULL
> 6) Backup again database (now in FULL mode)
>
> That's the solution I have thought.and all that will be done by the night
> run
>
> How dangerous is to change the recovery model of the database before you
run
> a job' Is the risk high'
>
> Is there an other way to perform the task, without having my transaction
log
> increased so match?
>
> Thanks in advance
>
> Dimitris Dimolas
> Web Programmer
> Greece
>|||Here are a couple of things to consider:
It sounds like you are using the database maintenance plan to reindex,
is this right? If so, I would instead script out a job which will
only reindex those indexes which are fragmented. The maintenance plan
is actually running on all indexes, which will cause a large
transaction log. BOL has a nice sample in under DBCC ShowContig
examples. You can work with that sample to strategize at what level
of fragmentation you want to run the commands.
One other thing to consider, if you are using identity fields for
primary keys and clustered indexes, you are currently rebuilding these
with every run of the maintenance plan. Identity fields rarely become
fragmented, unless you have enables identity insert or if you have a
large amount of delete activity. Chances are that you can probably
get by without reindexing these very often. The script mentioned
above will make this determination.
Your plan to change the recovery model won't necessarily work. The
log will still grow large, as it will not clear the log until the
entire DBCC DBReindex (which the maintenance plan runs) completes. I
would be willing to bet that if you change the job to only address
those indexes which are in need of defragmentation, you probably won't
have a log issue. If this is not the case, and you would like to
backup the log intermittently throughout the process, you can choose
to run a DBCC IndexDefrag instead. Per BOL on IndexDefrag:
"In addition, the defragmentation is always fully logged, regardless
of the database recovery model setting (see ALTER DATABASE). The
defragmentation of a very fragmented index can generate more log than
even a fully logged index creation. The defragmentation, however, is
performed as a series of short transactions and thus does not require
a large log if log backups are taken frequently or if the recovery
model setting is SIMPLE. "
"Dimitris" <seeyou_gr@.hotmail.com> wrote in message news:<uW1IwixPEHA.3216@.TK2MSFTNGP12.phx.
gbl>...
> Hi all
> I have a database (warehouse) with a data file 16GB with Recovery model FU
LL
> And each week I do a night run for optimization with the options "Reorgani
ze
> data and index pages" and "Change free space per page percentage to 10%"
> When this night run occurs, the transaction log on my database increases t
o
> 17GB
> cause to recreation of indexes and so on.
> I was wondering about the following, so I could solve the problem of 17GB
> transaction logs. The disks are not cheap in an external sub-system with
> mirrors and stripes.
> 1) Before the optimization job start backup the database
> 2) After the backup change the database recovery model to simple (so no lo
g
> will be recorded
> 3) Backup again database (now on simple mode) so transaction log will be
> shrink
> 4) Run the optimization job
> 5) Change the database recovery model back to FULL
> 6) Backup again database (now in FULL mode)
>
> That's the solution I have thought.and all that will be done by the night
> run
>
> How dangerous is to change the recovery model of the database before you r
un
> a job' Is the risk high'
>
> Is there an other way to perform the task, without having my transaction l
og
> increased so match?
>
> Thanks in advance
>
> Dimitris Dimolas
> Web Programmer
> Greece|||> My transactions in a day are about 4gb
> So why should i have a log of 17gb'
Because the db is 16 GB and you reorg all indexes in the db. Assuming that y
ou have clustered index on all
tables, then you rebuild 16GB worth of data, and everything is logged. I thi
nk that the big question is
whether you perform regular log backups or not. If not, just run the db in s
imple recovery mode, and the
rebuilds are minimally logged. Then if the working space for a days work for
the log is 4GB, then you can just
keep the log the size it needs. Or shrink it, the article is just to explain
side effects of shrinking! If you
do run regular log backups, then you could do something like:
1. Backup log
2. Db to simple recovery
3. Do the index rebuilds
4. (Do the shrink)
5. Db to full recovery
6. Backup db
You now have a window in time where you cannot do point in time recovery. Th
is is (inclusive) from 2 to 6.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Dimitris" <seeyou_gr@.hotmail.com> wrote in message news:%23GPRtwzPEHA.3044@.TK2MSFTNGP10.phx
.gbl...
> My transactions in a day are about 4gb
> So why should i have a log of 17gb'
> And the question isn't why.
> But how i can done it
> You can understand ofcource that if you have a log of 4 GB per day there i
s
> no reason to have a log of 17GB when you r trying to optimize your databas
e.
> There is no need.
> Thanks
>
> "Dimitris" <seeyou_gr@.hotmail.com> wrote in message
> news:uW1IwixPEHA.3216@.TK2MSFTNGP12.phx.gbl...
> FULL
> "Reorganize
> to
> log
> run
> log
>|||Another option is to defrag using DBCC INDEXDEFRAG. Depending on the fragmen
tation, you might end up with less
in the log file. Also, I suggest that you don't defrag unless you have to (D
BCC SHOWCONTIG tell you the
fragmentation level). More info at:
http://www.microsoft.com/technet/pr...n/ss2kidbp.mspx
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message
news:ecf6z8zPEHA.904@.TK2MSFTNGP12.phx.gbl...
> Because the db is 16 GB and you reorg all indexes in the db. Assuming that
you have clustered index on all
> tables, then you rebuild 16GB worth of data, and everything is logged. I t
hink that the big question is
> whether you perform regular log backups or not. If not, just run the db in
simple recovery mode, and the
> rebuilds are minimally logged. Then if the working space for a days work for the l
og is 4GB, then you can
just
> keep the log the size it needs. Or shrink it, the article is just to explain side
effects of shrinking! If
you
> do run regular log backups, then you could do something like:
> 1. Backup log
> 2. Db to simple recovery
> 3. Do the index rebuilds
> 4. (Do the shrink)
> 5. Db to full recovery
> 6. Backup db
> You now have a window in time where you cannot do point in time recovery.
This is (inclusive) from 2 to 6.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Dimitris" <seeyou_gr@.hotmail.com> wrote in message news:%23GPRtwzPEHA.304
4@.TK2MSFTNGP10.phx.gbl...
>
I have a database (warehouse) with a data file 16GB with Recovery model FULL
And each week I do a night run for optimization with the options "Reorganize
data and index pages" and "Change free space per page percentage to 10%"
When this night run occurs, the transaction log on my database increases to
17GB
cause to recreation of indexes and so on.
I was wondering about the following, so I could solve the problem of 17GB
transaction logs. The disks are not cheap in an external sub-system with
mirrors and stripes.
1) Before the optimization job start backup the database
2) After the backup change the database recovery model to simple (so no log
will be recorded
3) Backup again database (now on simple mode) so transaction log will be
shrink
4) Run the optimization job
5) Change the database recovery model back to FULL
6) Backup again database (now in FULL mode)
That's the solution I have thought.and all that will be done by the night
run
How dangerous is to change the recovery model of the database before you run
a job' Is the risk high'
Is there an other way to perform the task, without having my transaction log
increased so match?
Thanks in advance
Dimitris Dimolas
Web Programmer
GreeceWhy do you want to shrink the file each week? SQL Server is designed to work
with pre-allocation of space. By
not shrinking, you don't have to do anything special at all!
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/
"Dimitris" <seeyou_gr@.hotmail.com> wrote in message news:uW1IwixPEHA.3216@.TK2MSFTNGP12.phx.g
bl...
> Hi all
> I have a database (warehouse) with a data file 16GB with Recovery model FU
LL
> And each week I do a night run for optimization with the options "Reorgani
ze
> data and index pages" and "Change free space per page percentage to 10%"
> When this night run occurs, the transaction log on my database increases t
o
> 17GB
> cause to recreation of indexes and so on.
> I was wondering about the following, so I could solve the problem of 17GB
> transaction logs. The disks are not cheap in an external sub-system with
> mirrors and stripes.
> 1) Before the optimization job start backup the database
> 2) After the backup change the database recovery model to simple (so no lo
g
> will be recorded
> 3) Backup again database (now on simple mode) so transaction log will be
> shrink
> 4) Run the optimization job
> 5) Change the database recovery model back to FULL
> 6) Backup again database (now in FULL mode)
>
> That's the solution I have thought.and all that will be done by the night
> run
>
> How dangerous is to change the recovery model of the database before you r
un
> a job' Is the risk high'
>
> Is there an other way to perform the task, without having my transaction l
og
> increased so match?
>
> Thanks in advance
>
> Dimitris Dimolas
> Web Programmer
> Greece
>|||In addition to the other responses, see the whitepaper at
http://www.microsoft.com/technet/pr...n/ss2kidbp.mspx
for index maintenance tips.
Hope this helps.
Dan Guzman
SQL Server MVP
"Dimitris" <seeyou_gr@.hotmail.com> wrote in message
news:uW1IwixPEHA.3216@.TK2MSFTNGP12.phx.gbl...
> Hi all
> I have a database (warehouse) with a data file 16GB with Recovery model
FULL
> And each week I do a night run for optimization with the options
"Reorganize
> data and index pages" and "Change free space per page percentage to 10%"
> When this night run occurs, the transaction log on my database increases
to
> 17GB
> cause to recreation of indexes and so on.
> I was wondering about the following, so I could solve the problem of 17GB
> transaction logs. The disks are not cheap in an external sub-system with
> mirrors and stripes.
> 1) Before the optimization job start backup the database
> 2) After the backup change the database recovery model to simple (so no
log
> will be recorded
> 3) Backup again database (now on simple mode) so transaction log will be
> shrink
> 4) Run the optimization job
> 5) Change the database recovery model back to FULL
> 6) Backup again database (now in FULL mode)
>
> That's the solution I have thought.and all that will be done by the night
> run
>
> How dangerous is to change the recovery model of the database before you
run
> a job' Is the risk high'
>
> Is there an other way to perform the task, without having my transaction
log
> increased so match?
>
> Thanks in advance
>
> Dimitris Dimolas
> Web Programmer
> Greece
>|||My transactions in a day are about 4gb
So why should i have a log of 17gb'
And the question isn't why.
But how i can done it
You can understand ofcource that if you have a log of 4 GB per day there is
no reason to have a log of 17GB when you r trying to optimize your database.
There is no need.
Thanks
"Dimitris" <seeyou_gr@.hotmail.com> wrote in message
news:uW1IwixPEHA.3216@.TK2MSFTNGP12.phx.gbl...
> Hi all
> I have a database (warehouse) with a data file 16GB with Recovery model
FULL
> And each week I do a night run for optimization with the options
"Reorganize
> data and index pages" and "Change free space per page percentage to 10%"
> When this night run occurs, the transaction log on my database increases
to
> 17GB
> cause to recreation of indexes and so on.
> I was wondering about the following, so I could solve the problem of 17GB
> transaction logs. The disks are not cheap in an external sub-system with
> mirrors and stripes.
> 1) Before the optimization job start backup the database
> 2) After the backup change the database recovery model to simple (so no
log
> will be recorded
> 3) Backup again database (now on simple mode) so transaction log will be
> shrink
> 4) Run the optimization job
> 5) Change the database recovery model back to FULL
> 6) Backup again database (now in FULL mode)
>
> That's the solution I have thought.and all that will be done by the night
> run
>
> How dangerous is to change the recovery model of the database before you
run
> a job' Is the risk high'
>
> Is there an other way to perform the task, without having my transaction
log
> increased so match?
>
> Thanks in advance
>
> Dimitris Dimolas
> Web Programmer
> Greece
>|||Here are a couple of things to consider:
It sounds like you are using the database maintenance plan to reindex,
is this right? If so, I would instead script out a job which will
only reindex those indexes which are fragmented. The maintenance plan
is actually running on all indexes, which will cause a large
transaction log. BOL has a nice sample in under DBCC ShowContig
examples. You can work with that sample to strategize at what level
of fragmentation you want to run the commands.
One other thing to consider, if you are using identity fields for
primary keys and clustered indexes, you are currently rebuilding these
with every run of the maintenance plan. Identity fields rarely become
fragmented, unless you have enables identity insert or if you have a
large amount of delete activity. Chances are that you can probably
get by without reindexing these very often. The script mentioned
above will make this determination.
Your plan to change the recovery model won't necessarily work. The
log will still grow large, as it will not clear the log until the
entire DBCC DBReindex (which the maintenance plan runs) completes. I
would be willing to bet that if you change the job to only address
those indexes which are in need of defragmentation, you probably won't
have a log issue. If this is not the case, and you would like to
backup the log intermittently throughout the process, you can choose
to run a DBCC IndexDefrag instead. Per BOL on IndexDefrag:
"In addition, the defragmentation is always fully logged, regardless
of the database recovery model setting (see ALTER DATABASE). The
defragmentation of a very fragmented index can generate more log than
even a fully logged index creation. The defragmentation, however, is
performed as a series of short transactions and thus does not require
a large log if log backups are taken frequently or if the recovery
model setting is SIMPLE. "
"Dimitris" <seeyou_gr@.hotmail.com> wrote in message news:<uW1IwixPEHA.3216@.TK2MSFTNGP12.phx.
gbl>...
> Hi all
> I have a database (warehouse) with a data file 16GB with Recovery model FU
LL
> And each week I do a night run for optimization with the options "Reorgani
ze
> data and index pages" and "Change free space per page percentage to 10%"
> When this night run occurs, the transaction log on my database increases t
o
> 17GB
> cause to recreation of indexes and so on.
> I was wondering about the following, so I could solve the problem of 17GB
> transaction logs. The disks are not cheap in an external sub-system with
> mirrors and stripes.
> 1) Before the optimization job start backup the database
> 2) After the backup change the database recovery model to simple (so no lo
g
> will be recorded
> 3) Backup again database (now on simple mode) so transaction log will be
> shrink
> 4) Run the optimization job
> 5) Change the database recovery model back to FULL
> 6) Backup again database (now in FULL mode)
>
> That's the solution I have thought.and all that will be done by the night
> run
>
> How dangerous is to change the recovery model of the database before you r
un
> a job' Is the risk high'
>
> Is there an other way to perform the task, without having my transaction l
og
> increased so match?
>
> Thanks in advance
>
> Dimitris Dimolas
> Web Programmer
> Greece|||> My transactions in a day are about 4gb
> So why should i have a log of 17gb'
Because the db is 16 GB and you reorg all indexes in the db. Assuming that y
ou have clustered index on all
tables, then you rebuild 16GB worth of data, and everything is logged. I thi
nk that the big question is
whether you perform regular log backups or not. If not, just run the db in s
imple recovery mode, and the
rebuilds are minimally logged. Then if the working space for a days work for
the log is 4GB, then you can just
keep the log the size it needs. Or shrink it, the article is just to explain
side effects of shrinking! If you
do run regular log backups, then you could do something like:
1. Backup log
2. Db to simple recovery
3. Do the index rebuilds
4. (Do the shrink)
5. Db to full recovery
6. Backup db
You now have a window in time where you cannot do point in time recovery. Th
is is (inclusive) from 2 to 6.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Dimitris" <seeyou_gr@.hotmail.com> wrote in message news:%23GPRtwzPEHA.3044@.TK2MSFTNGP10.phx
.gbl...
> My transactions in a day are about 4gb
> So why should i have a log of 17gb'
> And the question isn't why.
> But how i can done it
> You can understand ofcource that if you have a log of 4 GB per day there i
s
> no reason to have a log of 17GB when you r trying to optimize your databas
e.
> There is no need.
> Thanks
>
> "Dimitris" <seeyou_gr@.hotmail.com> wrote in message
> news:uW1IwixPEHA.3216@.TK2MSFTNGP12.phx.gbl...
> FULL
> "Reorganize
> to
> log
> run
> log
>|||Another option is to defrag using DBCC INDEXDEFRAG. Depending on the fragmen
tation, you might end up with less
in the log file. Also, I suggest that you don't defrag unless you have to (D
BCC SHOWCONTIG tell you the
fragmentation level). More info at:
http://www.microsoft.com/technet/pr...n/ss2kidbp.mspx
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message
news:ecf6z8zPEHA.904@.TK2MSFTNGP12.phx.gbl...
> Because the db is 16 GB and you reorg all indexes in the db. Assuming that
you have clustered index on all
> tables, then you rebuild 16GB worth of data, and everything is logged. I t
hink that the big question is
> whether you perform regular log backups or not. If not, just run the db in
simple recovery mode, and the
> rebuilds are minimally logged. Then if the working space for a days work for the l
og is 4GB, then you can
just
> keep the log the size it needs. Or shrink it, the article is just to explain side
effects of shrinking! If
you
> do run regular log backups, then you could do something like:
> 1. Backup log
> 2. Db to simple recovery
> 3. Do the index rebuilds
> 4. (Do the shrink)
> 5. Db to full recovery
> 6. Backup db
> You now have a window in time where you cannot do point in time recovery.
This is (inclusive) from 2 to 6.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Dimitris" <seeyou_gr@.hotmail.com> wrote in message news:%23GPRtwzPEHA.304
4@.TK2MSFTNGP10.phx.gbl...
>
ALTER DATABASE (optimization job)
Hi all
I have a database (warehouse) with a data file 16GB with Recovery model FULL
And each week I do a night run for optimization with the options "Reorganize
data and index pages" and "Change free space per page percentage to 10%"
When this night run occurs, the transaction log on my database increases to
17GB
cause to recreation of indexes and so on.
I was wondering about the following, so I could solve the problem of 17GB
transaction logs. The disks are not cheap in an external sub-system with
mirrors and stripes.
1) Before the optimization job start backup the database
2) After the backup change the database recovery model to simple (so no log
will be recorded
3) Backup again database (now on simple mode) so transaction log will be
shrink
4) Run the optimization job
5) Change the database recovery model back to FULL
6) Backup again database (now in FULL mode)
That's the solution I have thought.and all that will be done by the night
run
How dangerous is to change the recovery model of the database before you run
a job? Is the risk high?
Is there an other way to perform the task, without having my transaction log
increased so match?
Thanks in advance
Dimitris Dimolas
Web Programmer
Greece
Why do you want to shrink the file each week? SQL Server is designed to work with pre-allocation of space. By
not shrinking, you don't have to do anything special at all!
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/
"Dimitris" <seeyou_gr@.hotmail.com> wrote in message news:uW1IwixPEHA.3216@.TK2MSFTNGP12.phx.gbl...
> Hi all
> I have a database (warehouse) with a data file 16GB with Recovery model FULL
> And each week I do a night run for optimization with the options "Reorganize
> data and index pages" and "Change free space per page percentage to 10%"
> When this night run occurs, the transaction log on my database increases to
> 17GB
> cause to recreation of indexes and so on.
> I was wondering about the following, so I could solve the problem of 17GB
> transaction logs. The disks are not cheap in an external sub-system with
> mirrors and stripes.
> 1) Before the optimization job start backup the database
> 2) After the backup change the database recovery model to simple (so no log
> will be recorded
> 3) Backup again database (now on simple mode) so transaction log will be
> shrink
> 4) Run the optimization job
> 5) Change the database recovery model back to FULL
> 6) Backup again database (now in FULL mode)
>
> That's the solution I have thought.and all that will be done by the night
> run
>
> How dangerous is to change the recovery model of the database before you run
> a job? Is the risk high?
>
> Is there an other way to perform the task, without having my transaction log
> increased so match?
>
> Thanks in advance
>
> Dimitris Dimolas
> Web Programmer
> Greece
>
|||In addition to the other responses, see the whitepaper at
http://www.microsoft.com/technet/pro.../ss2kidbp.mspx
for index maintenance tips.
Hope this helps.
Dan Guzman
SQL Server MVP
"Dimitris" <seeyou_gr@.hotmail.com> wrote in message
news:uW1IwixPEHA.3216@.TK2MSFTNGP12.phx.gbl...
> Hi all
> I have a database (warehouse) with a data file 16GB with Recovery model
FULL
> And each week I do a night run for optimization with the options
"Reorganize
> data and index pages" and "Change free space per page percentage to 10%"
> When this night run occurs, the transaction log on my database increases
to
> 17GB
> cause to recreation of indexes and so on.
> I was wondering about the following, so I could solve the problem of 17GB
> transaction logs. The disks are not cheap in an external sub-system with
> mirrors and stripes.
> 1) Before the optimization job start backup the database
> 2) After the backup change the database recovery model to simple (so no
log
> will be recorded
> 3) Backup again database (now on simple mode) so transaction log will be
> shrink
> 4) Run the optimization job
> 5) Change the database recovery model back to FULL
> 6) Backup again database (now in FULL mode)
>
> That's the solution I have thought.and all that will be done by the night
> run
>
> How dangerous is to change the recovery model of the database before you
run
> a job? Is the risk high?
>
> Is there an other way to perform the task, without having my transaction
log
> increased so match?
>
> Thanks in advance
>
> Dimitris Dimolas
> Web Programmer
> Greece
>
|||My transactions in a day are about 4gb
So why should i have a log of 17gb?
And the question isn't why.
But how i can done it
You can understand ofcource that if you have a log of 4 GB per day there is
no reason to have a log of 17GB when you r trying to optimize your database.
There is no need.
Thanks
"Dimitris" <seeyou_gr@.hotmail.com> wrote in message
news:uW1IwixPEHA.3216@.TK2MSFTNGP12.phx.gbl...
> Hi all
> I have a database (warehouse) with a data file 16GB with Recovery model
FULL
> And each week I do a night run for optimization with the options
"Reorganize
> data and index pages" and "Change free space per page percentage to 10%"
> When this night run occurs, the transaction log on my database increases
to
> 17GB
> cause to recreation of indexes and so on.
> I was wondering about the following, so I could solve the problem of 17GB
> transaction logs. The disks are not cheap in an external sub-system with
> mirrors and stripes.
> 1) Before the optimization job start backup the database
> 2) After the backup change the database recovery model to simple (so no
log
> will be recorded
> 3) Backup again database (now on simple mode) so transaction log will be
> shrink
> 4) Run the optimization job
> 5) Change the database recovery model back to FULL
> 6) Backup again database (now in FULL mode)
>
> That's the solution I have thought.and all that will be done by the night
> run
>
> How dangerous is to change the recovery model of the database before you
run
> a job? Is the risk high?
>
> Is there an other way to perform the task, without having my transaction
log
> increased so match?
>
> Thanks in advance
>
> Dimitris Dimolas
> Web Programmer
> Greece
>
|||Here are a couple of things to consider:
It sounds like you are using the database maintenance plan to reindex,
is this right? If so, I would instead script out a job which will
only reindex those indexes which are fragmented. The maintenance plan
is actually running on all indexes, which will cause a large
transaction log. BOL has a nice sample in under DBCC ShowContig
examples. You can work with that sample to strategize at what level
of fragmentation you want to run the commands.
One other thing to consider, if you are using identity fields for
primary keys and clustered indexes, you are currently rebuilding these
with every run of the maintenance plan. Identity fields rarely become
fragmented, unless you have enables identity insert or if you have a
large amount of delete activity. Chances are that you can probably
get by without reindexing these very often. The script mentioned
above will make this determination.
Your plan to change the recovery model won't necessarily work. The
log will still grow large, as it will not clear the log until the
entire DBCC DBReindex (which the maintenance plan runs) completes. I
would be willing to bet that if you change the job to only address
those indexes which are in need of defragmentation, you probably won't
have a log issue. If this is not the case, and you would like to
backup the log intermittently throughout the process, you can choose
to run a DBCC IndexDefrag instead. Per BOL on IndexDefrag:
"In addition, the defragmentation is always fully logged, regardless
of the database recovery model setting (see ALTER DATABASE). The
defragmentation of a very fragmented index can generate more log than
even a fully logged index creation. The defragmentation, however, is
performed as a series of short transactions and thus does not require
a large log if log backups are taken frequently or if the recovery
model setting is SIMPLE. "
"Dimitris" <seeyou_gr@.hotmail.com> wrote in message news:<uW1IwixPEHA.3216@.TK2MSFTNGP12.phx.gbl>...
> Hi all
> I have a database (warehouse) with a data file 16GB with Recovery model FULL
> And each week I do a night run for optimization with the options "Reorganize
> data and index pages" and "Change free space per page percentage to 10%"
> When this night run occurs, the transaction log on my database increases to
> 17GB
> cause to recreation of indexes and so on.
> I was wondering about the following, so I could solve the problem of 17GB
> transaction logs. The disks are not cheap in an external sub-system with
> mirrors and stripes.
> 1) Before the optimization job start backup the database
> 2) After the backup change the database recovery model to simple (so no log
> will be recorded
> 3) Backup again database (now on simple mode) so transaction log will be
> shrink
> 4) Run the optimization job
> 5) Change the database recovery model back to FULL
> 6) Backup again database (now in FULL mode)
>
> That's the solution I have thought.and all that will be done by the night
> run
>
> How dangerous is to change the recovery model of the database before you run
> a job? Is the risk high?
>
> Is there an other way to perform the task, without having my transaction log
> increased so match?
>
> Thanks in advance
>
> Dimitris Dimolas
> Web Programmer
> Greece
|||> My transactions in a day are about 4gb
> So why should i have a log of 17gb?
Because the db is 16 GB and you reorg all indexes in the db. Assuming that you have clustered index on all
tables, then you rebuild 16GB worth of data, and everything is logged. I think that the big question is
whether you perform regular log backups or not. If not, just run the db in simple recovery mode, and the
rebuilds are minimally logged. Then if the working space for a days work for the log is 4GB, then you can just
keep the log the size it needs. Or shrink it, the article is just to explain side effects of shrinking! If you
do run regular log backups, then you could do something like:
1. Backup log
2. Db to simple recovery
3. Do the index rebuilds
4. (Do the shrink)
5. Db to full recovery
6. Backup db
You now have a window in time where you cannot do point in time recovery. This is (inclusive) from 2 to 6.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Dimitris" <seeyou_gr@.hotmail.com> wrote in message news:%23GPRtwzPEHA.3044@.TK2MSFTNGP10.phx.gbl...
> My transactions in a day are about 4gb
> So why should i have a log of 17gb?
> And the question isn't why.
> But how i can done it
> You can understand ofcource that if you have a log of 4 GB per day there is
> no reason to have a log of 17GB when you r trying to optimize your database.
> There is no need.
> Thanks
>
> "Dimitris" <seeyou_gr@.hotmail.com> wrote in message
> news:uW1IwixPEHA.3216@.TK2MSFTNGP12.phx.gbl...
> FULL
> "Reorganize
> to
> log
> run
> log
>
|||Another option is to defrag using DBCC INDEXDEFRAG. Depending on the fragmentation, you might end up with less
in the log file. Also, I suggest that you don't defrag unless you have to (DBCC SHOWCONTIG tell you the
fragmentation level). More info at:
http://www.microsoft.com/technet/pro.../ss2kidbp.mspx
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in message
news:ecf6z8zPEHA.904@.TK2MSFTNGP12.phx.gbl...
> Because the db is 16 GB and you reorg all indexes in the db. Assuming that you have clustered index on all
> tables, then you rebuild 16GB worth of data, and everything is logged. I think that the big question is
> whether you perform regular log backups or not. If not, just run the db in simple recovery mode, and the
> rebuilds are minimally logged. Then if the working space for a days work for the log is 4GB, then you can
just
> keep the log the size it needs. Or shrink it, the article is just to explain side effects of shrinking! If
you
> do run regular log backups, then you could do something like:
> 1. Backup log
> 2. Db to simple recovery
> 3. Do the index rebuilds
> 4. (Do the shrink)
> 5. Db to full recovery
> 6. Backup db
> You now have a window in time where you cannot do point in time recovery. This is (inclusive) from 2 to 6.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Dimitris" <seeyou_gr@.hotmail.com> wrote in message news:%23GPRtwzPEHA.3044@.TK2MSFTNGP10.phx.gbl...
>
I have a database (warehouse) with a data file 16GB with Recovery model FULL
And each week I do a night run for optimization with the options "Reorganize
data and index pages" and "Change free space per page percentage to 10%"
When this night run occurs, the transaction log on my database increases to
17GB
cause to recreation of indexes and so on.
I was wondering about the following, so I could solve the problem of 17GB
transaction logs. The disks are not cheap in an external sub-system with
mirrors and stripes.
1) Before the optimization job start backup the database
2) After the backup change the database recovery model to simple (so no log
will be recorded
3) Backup again database (now on simple mode) so transaction log will be
shrink
4) Run the optimization job
5) Change the database recovery model back to FULL
6) Backup again database (now in FULL mode)
That's the solution I have thought.and all that will be done by the night
run
How dangerous is to change the recovery model of the database before you run
a job? Is the risk high?
Is there an other way to perform the task, without having my transaction log
increased so match?
Thanks in advance
Dimitris Dimolas
Web Programmer
Greece
Why do you want to shrink the file each week? SQL Server is designed to work with pre-allocation of space. By
not shrinking, you don't have to do anything special at all!
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/
"Dimitris" <seeyou_gr@.hotmail.com> wrote in message news:uW1IwixPEHA.3216@.TK2MSFTNGP12.phx.gbl...
> Hi all
> I have a database (warehouse) with a data file 16GB with Recovery model FULL
> And each week I do a night run for optimization with the options "Reorganize
> data and index pages" and "Change free space per page percentage to 10%"
> When this night run occurs, the transaction log on my database increases to
> 17GB
> cause to recreation of indexes and so on.
> I was wondering about the following, so I could solve the problem of 17GB
> transaction logs. The disks are not cheap in an external sub-system with
> mirrors and stripes.
> 1) Before the optimization job start backup the database
> 2) After the backup change the database recovery model to simple (so no log
> will be recorded
> 3) Backup again database (now on simple mode) so transaction log will be
> shrink
> 4) Run the optimization job
> 5) Change the database recovery model back to FULL
> 6) Backup again database (now in FULL mode)
>
> That's the solution I have thought.and all that will be done by the night
> run
>
> How dangerous is to change the recovery model of the database before you run
> a job? Is the risk high?
>
> Is there an other way to perform the task, without having my transaction log
> increased so match?
>
> Thanks in advance
>
> Dimitris Dimolas
> Web Programmer
> Greece
>
|||In addition to the other responses, see the whitepaper at
http://www.microsoft.com/technet/pro.../ss2kidbp.mspx
for index maintenance tips.
Hope this helps.
Dan Guzman
SQL Server MVP
"Dimitris" <seeyou_gr@.hotmail.com> wrote in message
news:uW1IwixPEHA.3216@.TK2MSFTNGP12.phx.gbl...
> Hi all
> I have a database (warehouse) with a data file 16GB with Recovery model
FULL
> And each week I do a night run for optimization with the options
"Reorganize
> data and index pages" and "Change free space per page percentage to 10%"
> When this night run occurs, the transaction log on my database increases
to
> 17GB
> cause to recreation of indexes and so on.
> I was wondering about the following, so I could solve the problem of 17GB
> transaction logs. The disks are not cheap in an external sub-system with
> mirrors and stripes.
> 1) Before the optimization job start backup the database
> 2) After the backup change the database recovery model to simple (so no
log
> will be recorded
> 3) Backup again database (now on simple mode) so transaction log will be
> shrink
> 4) Run the optimization job
> 5) Change the database recovery model back to FULL
> 6) Backup again database (now in FULL mode)
>
> That's the solution I have thought.and all that will be done by the night
> run
>
> How dangerous is to change the recovery model of the database before you
run
> a job? Is the risk high?
>
> Is there an other way to perform the task, without having my transaction
log
> increased so match?
>
> Thanks in advance
>
> Dimitris Dimolas
> Web Programmer
> Greece
>
|||My transactions in a day are about 4gb
So why should i have a log of 17gb?
And the question isn't why.
But how i can done it
You can understand ofcource that if you have a log of 4 GB per day there is
no reason to have a log of 17GB when you r trying to optimize your database.
There is no need.
Thanks
"Dimitris" <seeyou_gr@.hotmail.com> wrote in message
news:uW1IwixPEHA.3216@.TK2MSFTNGP12.phx.gbl...
> Hi all
> I have a database (warehouse) with a data file 16GB with Recovery model
FULL
> And each week I do a night run for optimization with the options
"Reorganize
> data and index pages" and "Change free space per page percentage to 10%"
> When this night run occurs, the transaction log on my database increases
to
> 17GB
> cause to recreation of indexes and so on.
> I was wondering about the following, so I could solve the problem of 17GB
> transaction logs. The disks are not cheap in an external sub-system with
> mirrors and stripes.
> 1) Before the optimization job start backup the database
> 2) After the backup change the database recovery model to simple (so no
log
> will be recorded
> 3) Backup again database (now on simple mode) so transaction log will be
> shrink
> 4) Run the optimization job
> 5) Change the database recovery model back to FULL
> 6) Backup again database (now in FULL mode)
>
> That's the solution I have thought.and all that will be done by the night
> run
>
> How dangerous is to change the recovery model of the database before you
run
> a job? Is the risk high?
>
> Is there an other way to perform the task, without having my transaction
log
> increased so match?
>
> Thanks in advance
>
> Dimitris Dimolas
> Web Programmer
> Greece
>
|||Here are a couple of things to consider:
It sounds like you are using the database maintenance plan to reindex,
is this right? If so, I would instead script out a job which will
only reindex those indexes which are fragmented. The maintenance plan
is actually running on all indexes, which will cause a large
transaction log. BOL has a nice sample in under DBCC ShowContig
examples. You can work with that sample to strategize at what level
of fragmentation you want to run the commands.
One other thing to consider, if you are using identity fields for
primary keys and clustered indexes, you are currently rebuilding these
with every run of the maintenance plan. Identity fields rarely become
fragmented, unless you have enables identity insert or if you have a
large amount of delete activity. Chances are that you can probably
get by without reindexing these very often. The script mentioned
above will make this determination.
Your plan to change the recovery model won't necessarily work. The
log will still grow large, as it will not clear the log until the
entire DBCC DBReindex (which the maintenance plan runs) completes. I
would be willing to bet that if you change the job to only address
those indexes which are in need of defragmentation, you probably won't
have a log issue. If this is not the case, and you would like to
backup the log intermittently throughout the process, you can choose
to run a DBCC IndexDefrag instead. Per BOL on IndexDefrag:
"In addition, the defragmentation is always fully logged, regardless
of the database recovery model setting (see ALTER DATABASE). The
defragmentation of a very fragmented index can generate more log than
even a fully logged index creation. The defragmentation, however, is
performed as a series of short transactions and thus does not require
a large log if log backups are taken frequently or if the recovery
model setting is SIMPLE. "
"Dimitris" <seeyou_gr@.hotmail.com> wrote in message news:<uW1IwixPEHA.3216@.TK2MSFTNGP12.phx.gbl>...
> Hi all
> I have a database (warehouse) with a data file 16GB with Recovery model FULL
> And each week I do a night run for optimization with the options "Reorganize
> data and index pages" and "Change free space per page percentage to 10%"
> When this night run occurs, the transaction log on my database increases to
> 17GB
> cause to recreation of indexes and so on.
> I was wondering about the following, so I could solve the problem of 17GB
> transaction logs. The disks are not cheap in an external sub-system with
> mirrors and stripes.
> 1) Before the optimization job start backup the database
> 2) After the backup change the database recovery model to simple (so no log
> will be recorded
> 3) Backup again database (now on simple mode) so transaction log will be
> shrink
> 4) Run the optimization job
> 5) Change the database recovery model back to FULL
> 6) Backup again database (now in FULL mode)
>
> That's the solution I have thought.and all that will be done by the night
> run
>
> How dangerous is to change the recovery model of the database before you run
> a job? Is the risk high?
>
> Is there an other way to perform the task, without having my transaction log
> increased so match?
>
> Thanks in advance
>
> Dimitris Dimolas
> Web Programmer
> Greece
|||> My transactions in a day are about 4gb
> So why should i have a log of 17gb?
Because the db is 16 GB and you reorg all indexes in the db. Assuming that you have clustered index on all
tables, then you rebuild 16GB worth of data, and everything is logged. I think that the big question is
whether you perform regular log backups or not. If not, just run the db in simple recovery mode, and the
rebuilds are minimally logged. Then if the working space for a days work for the log is 4GB, then you can just
keep the log the size it needs. Or shrink it, the article is just to explain side effects of shrinking! If you
do run regular log backups, then you could do something like:
1. Backup log
2. Db to simple recovery
3. Do the index rebuilds
4. (Do the shrink)
5. Db to full recovery
6. Backup db
You now have a window in time where you cannot do point in time recovery. This is (inclusive) from 2 to 6.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Dimitris" <seeyou_gr@.hotmail.com> wrote in message news:%23GPRtwzPEHA.3044@.TK2MSFTNGP10.phx.gbl...
> My transactions in a day are about 4gb
> So why should i have a log of 17gb?
> And the question isn't why.
> But how i can done it
> You can understand ofcource that if you have a log of 4 GB per day there is
> no reason to have a log of 17GB when you r trying to optimize your database.
> There is no need.
> Thanks
>
> "Dimitris" <seeyou_gr@.hotmail.com> wrote in message
> news:uW1IwixPEHA.3216@.TK2MSFTNGP12.phx.gbl...
> FULL
> "Reorganize
> to
> log
> run
> log
>
|||Another option is to defrag using DBCC INDEXDEFRAG. Depending on the fragmentation, you might end up with less
in the log file. Also, I suggest that you don't defrag unless you have to (DBCC SHOWCONTIG tell you the
fragmentation level). More info at:
http://www.microsoft.com/technet/pro.../ss2kidbp.mspx
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in message
news:ecf6z8zPEHA.904@.TK2MSFTNGP12.phx.gbl...
> Because the db is 16 GB and you reorg all indexes in the db. Assuming that you have clustered index on all
> tables, then you rebuild 16GB worth of data, and everything is logged. I think that the big question is
> whether you perform regular log backups or not. If not, just run the db in simple recovery mode, and the
> rebuilds are minimally logged. Then if the working space for a days work for the log is 4GB, then you can
just
> keep the log the size it needs. Or shrink it, the article is just to explain side effects of shrinking! If
you
> do run regular log backups, then you could do something like:
> 1. Backup log
> 2. Db to simple recovery
> 3. Do the index rebuilds
> 4. (Do the shrink)
> 5. Db to full recovery
> 6. Backup db
> You now have a window in time where you cannot do point in time recovery. This is (inclusive) from 2 to 6.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Dimitris" <seeyou_gr@.hotmail.com> wrote in message news:%23GPRtwzPEHA.3044@.TK2MSFTNGP10.phx.gbl...
>
ALTER DATABASE (optimization job)
Hi all
I have a database (warehouse) with a data file 16GB with Recovery model FULL
And each week I do a night run for optimization with the options "Reorganize
data and index pages" and "Change free space per page percentage to 10%"
When this night run occurs, the transaction log on my database increases to
17GB
cause to recreation of indexes and so on.
I was wondering about the following, so I could solve the problem of 17GB
transaction logs. The disks are not cheap in an external sub-system with
mirrors and stripes.
1) Before the optimization job start backup the database
2) After the backup change the database recovery model to simple (so no log
will be recorded
3) Backup again database (now on simple mode) so transaction log will be
shrink
4) Run the optimization job
5) Change the database recovery model back to FULL
6) Backup again database (now in FULL mode)
That's the solution I have thought.and all that will be done by the night
run
How dangerous is to change the recovery model of the database before you run
a job? Is the risk high?
Is there an other way to perform the task, without having my transaction log
increased so match?
Thanks in advance
Dimitris Dimolas
Web Programmer
Greece
Why do you want to shrink the file each week? SQL Server is designed to work with pre-allocation of space. By
not shrinking, you don't have to do anything special at all!
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/
"Dimitris" <seeyou_gr@.hotmail.com> wrote in message news:uW1IwixPEHA.3216@.TK2MSFTNGP12.phx.gbl...
> Hi all
> I have a database (warehouse) with a data file 16GB with Recovery model FULL
> And each week I do a night run for optimization with the options "Reorganize
> data and index pages" and "Change free space per page percentage to 10%"
> When this night run occurs, the transaction log on my database increases to
> 17GB
> cause to recreation of indexes and so on.
> I was wondering about the following, so I could solve the problem of 17GB
> transaction logs. The disks are not cheap in an external sub-system with
> mirrors and stripes.
> 1) Before the optimization job start backup the database
> 2) After the backup change the database recovery model to simple (so no log
> will be recorded
> 3) Backup again database (now on simple mode) so transaction log will be
> shrink
> 4) Run the optimization job
> 5) Change the database recovery model back to FULL
> 6) Backup again database (now in FULL mode)
>
> That's the solution I have thought.and all that will be done by the night
> run
>
> How dangerous is to change the recovery model of the database before you run
> a job? Is the risk high?
>
> Is there an other way to perform the task, without having my transaction log
> increased so match?
>
> Thanks in advance
>
> Dimitris Dimolas
> Web Programmer
> Greece
>
|||In addition to the other responses, see the whitepaper at
http://www.microsoft.com/technet/pro.../ss2kidbp.mspx
for index maintenance tips.
Hope this helps.
Dan Guzman
SQL Server MVP
"Dimitris" <seeyou_gr@.hotmail.com> wrote in message
news:uW1IwixPEHA.3216@.TK2MSFTNGP12.phx.gbl...
> Hi all
> I have a database (warehouse) with a data file 16GB with Recovery model
FULL
> And each week I do a night run for optimization with the options
"Reorganize
> data and index pages" and "Change free space per page percentage to 10%"
> When this night run occurs, the transaction log on my database increases
to
> 17GB
> cause to recreation of indexes and so on.
> I was wondering about the following, so I could solve the problem of 17GB
> transaction logs. The disks are not cheap in an external sub-system with
> mirrors and stripes.
> 1) Before the optimization job start backup the database
> 2) After the backup change the database recovery model to simple (so no
log
> will be recorded
> 3) Backup again database (now on simple mode) so transaction log will be
> shrink
> 4) Run the optimization job
> 5) Change the database recovery model back to FULL
> 6) Backup again database (now in FULL mode)
>
> That's the solution I have thought.and all that will be done by the night
> run
>
> How dangerous is to change the recovery model of the database before you
run
> a job? Is the risk high?
>
> Is there an other way to perform the task, without having my transaction
log
> increased so match?
>
> Thanks in advance
>
> Dimitris Dimolas
> Web Programmer
> Greece
>
|||My transactions in a day are about 4gb
So why should i have a log of 17gb?
And the question isn't why.
But how i can done it
You can understand ofcource that if you have a log of 4 GB per day there is
no reason to have a log of 17GB when you r trying to optimize your database.
There is no need.
Thanks
"Dimitris" <seeyou_gr@.hotmail.com> wrote in message
news:uW1IwixPEHA.3216@.TK2MSFTNGP12.phx.gbl...
> Hi all
> I have a database (warehouse) with a data file 16GB with Recovery model
FULL
> And each week I do a night run for optimization with the options
"Reorganize
> data and index pages" and "Change free space per page percentage to 10%"
> When this night run occurs, the transaction log on my database increases
to
> 17GB
> cause to recreation of indexes and so on.
> I was wondering about the following, so I could solve the problem of 17GB
> transaction logs. The disks are not cheap in an external sub-system with
> mirrors and stripes.
> 1) Before the optimization job start backup the database
> 2) After the backup change the database recovery model to simple (so no
log
> will be recorded
> 3) Backup again database (now on simple mode) so transaction log will be
> shrink
> 4) Run the optimization job
> 5) Change the database recovery model back to FULL
> 6) Backup again database (now in FULL mode)
>
> That's the solution I have thought.and all that will be done by the night
> run
>
> How dangerous is to change the recovery model of the database before you
run
> a job? Is the risk high?
>
> Is there an other way to perform the task, without having my transaction
log
> increased so match?
>
> Thanks in advance
>
> Dimitris Dimolas
> Web Programmer
> Greece
>
|||Here are a couple of things to consider:
It sounds like you are using the database maintenance plan to reindex,
is this right? If so, I would instead script out a job which will
only reindex those indexes which are fragmented. The maintenance plan
is actually running on all indexes, which will cause a large
transaction log. BOL has a nice sample in under DBCC ShowContig
examples. You can work with that sample to strategize at what level
of fragmentation you want to run the commands.
One other thing to consider, if you are using identity fields for
primary keys and clustered indexes, you are currently rebuilding these
with every run of the maintenance plan. Identity fields rarely become
fragmented, unless you have enables identity insert or if you have a
large amount of delete activity. Chances are that you can probably
get by without reindexing these very often. The script mentioned
above will make this determination.
Your plan to change the recovery model won't necessarily work. The
log will still grow large, as it will not clear the log until the
entire DBCC DBReindex (which the maintenance plan runs) completes. I
would be willing to bet that if you change the job to only address
those indexes which are in need of defragmentation, you probably won't
have a log issue. If this is not the case, and you would like to
backup the log intermittently throughout the process, you can choose
to run a DBCC IndexDefrag instead. Per BOL on IndexDefrag:
"In addition, the defragmentation is always fully logged, regardless
of the database recovery model setting (see ALTER DATABASE). The
defragmentation of a very fragmented index can generate more log than
even a fully logged index creation. The defragmentation, however, is
performed as a series of short transactions and thus does not require
a large log if log backups are taken frequently or if the recovery
model setting is SIMPLE. "
"Dimitris" <seeyou_gr@.hotmail.com> wrote in message news:<uW1IwixPEHA.3216@.TK2MSFTNGP12.phx.gbl>...
> Hi all
> I have a database (warehouse) with a data file 16GB with Recovery model FULL
> And each week I do a night run for optimization with the options "Reorganize
> data and index pages" and "Change free space per page percentage to 10%"
> When this night run occurs, the transaction log on my database increases to
> 17GB
> cause to recreation of indexes and so on.
> I was wondering about the following, so I could solve the problem of 17GB
> transaction logs. The disks are not cheap in an external sub-system with
> mirrors and stripes.
> 1) Before the optimization job start backup the database
> 2) After the backup change the database recovery model to simple (so no log
> will be recorded
> 3) Backup again database (now on simple mode) so transaction log will be
> shrink
> 4) Run the optimization job
> 5) Change the database recovery model back to FULL
> 6) Backup again database (now in FULL mode)
>
> That's the solution I have thought.and all that will be done by the night
> run
>
> How dangerous is to change the recovery model of the database before you run
> a job? Is the risk high?
>
> Is there an other way to perform the task, without having my transaction log
> increased so match?
>
> Thanks in advance
>
> Dimitris Dimolas
> Web Programmer
> Greece
|||> My transactions in a day are about 4gb
> So why should i have a log of 17gb?
Because the db is 16 GB and you reorg all indexes in the db. Assuming that you have clustered index on all
tables, then you rebuild 16GB worth of data, and everything is logged. I think that the big question is
whether you perform regular log backups or not. If not, just run the db in simple recovery mode, and the
rebuilds are minimally logged. Then if the working space for a days work for the log is 4GB, then you can just
keep the log the size it needs. Or shrink it, the article is just to explain side effects of shrinking! If you
do run regular log backups, then you could do something like:
1. Backup log
2. Db to simple recovery
3. Do the index rebuilds
4. (Do the shrink)
5. Db to full recovery
6. Backup db
You now have a window in time where you cannot do point in time recovery. This is (inclusive) from 2 to 6.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Dimitris" <seeyou_gr@.hotmail.com> wrote in message news:%23GPRtwzPEHA.3044@.TK2MSFTNGP10.phx.gbl...
> My transactions in a day are about 4gb
> So why should i have a log of 17gb?
> And the question isn't why.
> But how i can done it
> You can understand ofcource that if you have a log of 4 GB per day there is
> no reason to have a log of 17GB when you r trying to optimize your database.
> There is no need.
> Thanks
>
> "Dimitris" <seeyou_gr@.hotmail.com> wrote in message
> news:uW1IwixPEHA.3216@.TK2MSFTNGP12.phx.gbl...
> FULL
> "Reorganize
> to
> log
> run
> log
>
|||Another option is to defrag using DBCC INDEXDEFRAG. Depending on the fragmentation, you might end up with less
in the log file. Also, I suggest that you don't defrag unless you have to (DBCC SHOWCONTIG tell you the
fragmentation level). More info at:
http://www.microsoft.com/technet/pro.../ss2kidbp.mspx
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in message
news:ecf6z8zPEHA.904@.TK2MSFTNGP12.phx.gbl...
> Because the db is 16 GB and you reorg all indexes in the db. Assuming that you have clustered index on all
> tables, then you rebuild 16GB worth of data, and everything is logged. I think that the big question is
> whether you perform regular log backups or not. If not, just run the db in simple recovery mode, and the
> rebuilds are minimally logged. Then if the working space for a days work for the log is 4GB, then you can
just
> keep the log the size it needs. Or shrink it, the article is just to explain side effects of shrinking! If
you
> do run regular log backups, then you could do something like:
> 1. Backup log
> 2. Db to simple recovery
> 3. Do the index rebuilds
> 4. (Do the shrink)
> 5. Db to full recovery
> 6. Backup db
> You now have a window in time where you cannot do point in time recovery. This is (inclusive) from 2 to 6.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Dimitris" <seeyou_gr@.hotmail.com> wrote in message news:%23GPRtwzPEHA.3044@.TK2MSFTNGP10.phx.gbl...
>
I have a database (warehouse) with a data file 16GB with Recovery model FULL
And each week I do a night run for optimization with the options "Reorganize
data and index pages" and "Change free space per page percentage to 10%"
When this night run occurs, the transaction log on my database increases to
17GB
cause to recreation of indexes and so on.
I was wondering about the following, so I could solve the problem of 17GB
transaction logs. The disks are not cheap in an external sub-system with
mirrors and stripes.
1) Before the optimization job start backup the database
2) After the backup change the database recovery model to simple (so no log
will be recorded
3) Backup again database (now on simple mode) so transaction log will be
shrink
4) Run the optimization job
5) Change the database recovery model back to FULL
6) Backup again database (now in FULL mode)
That's the solution I have thought.and all that will be done by the night
run
How dangerous is to change the recovery model of the database before you run
a job? Is the risk high?
Is there an other way to perform the task, without having my transaction log
increased so match?
Thanks in advance
Dimitris Dimolas
Web Programmer
Greece
Why do you want to shrink the file each week? SQL Server is designed to work with pre-allocation of space. By
not shrinking, you don't have to do anything special at all!
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/
"Dimitris" <seeyou_gr@.hotmail.com> wrote in message news:uW1IwixPEHA.3216@.TK2MSFTNGP12.phx.gbl...
> Hi all
> I have a database (warehouse) with a data file 16GB with Recovery model FULL
> And each week I do a night run for optimization with the options "Reorganize
> data and index pages" and "Change free space per page percentage to 10%"
> When this night run occurs, the transaction log on my database increases to
> 17GB
> cause to recreation of indexes and so on.
> I was wondering about the following, so I could solve the problem of 17GB
> transaction logs. The disks are not cheap in an external sub-system with
> mirrors and stripes.
> 1) Before the optimization job start backup the database
> 2) After the backup change the database recovery model to simple (so no log
> will be recorded
> 3) Backup again database (now on simple mode) so transaction log will be
> shrink
> 4) Run the optimization job
> 5) Change the database recovery model back to FULL
> 6) Backup again database (now in FULL mode)
>
> That's the solution I have thought.and all that will be done by the night
> run
>
> How dangerous is to change the recovery model of the database before you run
> a job? Is the risk high?
>
> Is there an other way to perform the task, without having my transaction log
> increased so match?
>
> Thanks in advance
>
> Dimitris Dimolas
> Web Programmer
> Greece
>
|||In addition to the other responses, see the whitepaper at
http://www.microsoft.com/technet/pro.../ss2kidbp.mspx
for index maintenance tips.
Hope this helps.
Dan Guzman
SQL Server MVP
"Dimitris" <seeyou_gr@.hotmail.com> wrote in message
news:uW1IwixPEHA.3216@.TK2MSFTNGP12.phx.gbl...
> Hi all
> I have a database (warehouse) with a data file 16GB with Recovery model
FULL
> And each week I do a night run for optimization with the options
"Reorganize
> data and index pages" and "Change free space per page percentage to 10%"
> When this night run occurs, the transaction log on my database increases
to
> 17GB
> cause to recreation of indexes and so on.
> I was wondering about the following, so I could solve the problem of 17GB
> transaction logs. The disks are not cheap in an external sub-system with
> mirrors and stripes.
> 1) Before the optimization job start backup the database
> 2) After the backup change the database recovery model to simple (so no
log
> will be recorded
> 3) Backup again database (now on simple mode) so transaction log will be
> shrink
> 4) Run the optimization job
> 5) Change the database recovery model back to FULL
> 6) Backup again database (now in FULL mode)
>
> That's the solution I have thought.and all that will be done by the night
> run
>
> How dangerous is to change the recovery model of the database before you
run
> a job? Is the risk high?
>
> Is there an other way to perform the task, without having my transaction
log
> increased so match?
>
> Thanks in advance
>
> Dimitris Dimolas
> Web Programmer
> Greece
>
|||My transactions in a day are about 4gb
So why should i have a log of 17gb?
And the question isn't why.
But how i can done it
You can understand ofcource that if you have a log of 4 GB per day there is
no reason to have a log of 17GB when you r trying to optimize your database.
There is no need.
Thanks
"Dimitris" <seeyou_gr@.hotmail.com> wrote in message
news:uW1IwixPEHA.3216@.TK2MSFTNGP12.phx.gbl...
> Hi all
> I have a database (warehouse) with a data file 16GB with Recovery model
FULL
> And each week I do a night run for optimization with the options
"Reorganize
> data and index pages" and "Change free space per page percentage to 10%"
> When this night run occurs, the transaction log on my database increases
to
> 17GB
> cause to recreation of indexes and so on.
> I was wondering about the following, so I could solve the problem of 17GB
> transaction logs. The disks are not cheap in an external sub-system with
> mirrors and stripes.
> 1) Before the optimization job start backup the database
> 2) After the backup change the database recovery model to simple (so no
log
> will be recorded
> 3) Backup again database (now on simple mode) so transaction log will be
> shrink
> 4) Run the optimization job
> 5) Change the database recovery model back to FULL
> 6) Backup again database (now in FULL mode)
>
> That's the solution I have thought.and all that will be done by the night
> run
>
> How dangerous is to change the recovery model of the database before you
run
> a job? Is the risk high?
>
> Is there an other way to perform the task, without having my transaction
log
> increased so match?
>
> Thanks in advance
>
> Dimitris Dimolas
> Web Programmer
> Greece
>
|||Here are a couple of things to consider:
It sounds like you are using the database maintenance plan to reindex,
is this right? If so, I would instead script out a job which will
only reindex those indexes which are fragmented. The maintenance plan
is actually running on all indexes, which will cause a large
transaction log. BOL has a nice sample in under DBCC ShowContig
examples. You can work with that sample to strategize at what level
of fragmentation you want to run the commands.
One other thing to consider, if you are using identity fields for
primary keys and clustered indexes, you are currently rebuilding these
with every run of the maintenance plan. Identity fields rarely become
fragmented, unless you have enables identity insert or if you have a
large amount of delete activity. Chances are that you can probably
get by without reindexing these very often. The script mentioned
above will make this determination.
Your plan to change the recovery model won't necessarily work. The
log will still grow large, as it will not clear the log until the
entire DBCC DBReindex (which the maintenance plan runs) completes. I
would be willing to bet that if you change the job to only address
those indexes which are in need of defragmentation, you probably won't
have a log issue. If this is not the case, and you would like to
backup the log intermittently throughout the process, you can choose
to run a DBCC IndexDefrag instead. Per BOL on IndexDefrag:
"In addition, the defragmentation is always fully logged, regardless
of the database recovery model setting (see ALTER DATABASE). The
defragmentation of a very fragmented index can generate more log than
even a fully logged index creation. The defragmentation, however, is
performed as a series of short transactions and thus does not require
a large log if log backups are taken frequently or if the recovery
model setting is SIMPLE. "
"Dimitris" <seeyou_gr@.hotmail.com> wrote in message news:<uW1IwixPEHA.3216@.TK2MSFTNGP12.phx.gbl>...
> Hi all
> I have a database (warehouse) with a data file 16GB with Recovery model FULL
> And each week I do a night run for optimization with the options "Reorganize
> data and index pages" and "Change free space per page percentage to 10%"
> When this night run occurs, the transaction log on my database increases to
> 17GB
> cause to recreation of indexes and so on.
> I was wondering about the following, so I could solve the problem of 17GB
> transaction logs. The disks are not cheap in an external sub-system with
> mirrors and stripes.
> 1) Before the optimization job start backup the database
> 2) After the backup change the database recovery model to simple (so no log
> will be recorded
> 3) Backup again database (now on simple mode) so transaction log will be
> shrink
> 4) Run the optimization job
> 5) Change the database recovery model back to FULL
> 6) Backup again database (now in FULL mode)
>
> That's the solution I have thought.and all that will be done by the night
> run
>
> How dangerous is to change the recovery model of the database before you run
> a job? Is the risk high?
>
> Is there an other way to perform the task, without having my transaction log
> increased so match?
>
> Thanks in advance
>
> Dimitris Dimolas
> Web Programmer
> Greece
|||> My transactions in a day are about 4gb
> So why should i have a log of 17gb?
Because the db is 16 GB and you reorg all indexes in the db. Assuming that you have clustered index on all
tables, then you rebuild 16GB worth of data, and everything is logged. I think that the big question is
whether you perform regular log backups or not. If not, just run the db in simple recovery mode, and the
rebuilds are minimally logged. Then if the working space for a days work for the log is 4GB, then you can just
keep the log the size it needs. Or shrink it, the article is just to explain side effects of shrinking! If you
do run regular log backups, then you could do something like:
1. Backup log
2. Db to simple recovery
3. Do the index rebuilds
4. (Do the shrink)
5. Db to full recovery
6. Backup db
You now have a window in time where you cannot do point in time recovery. This is (inclusive) from 2 to 6.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Dimitris" <seeyou_gr@.hotmail.com> wrote in message news:%23GPRtwzPEHA.3044@.TK2MSFTNGP10.phx.gbl...
> My transactions in a day are about 4gb
> So why should i have a log of 17gb?
> And the question isn't why.
> But how i can done it
> You can understand ofcource that if you have a log of 4 GB per day there is
> no reason to have a log of 17GB when you r trying to optimize your database.
> There is no need.
> Thanks
>
> "Dimitris" <seeyou_gr@.hotmail.com> wrote in message
> news:uW1IwixPEHA.3216@.TK2MSFTNGP12.phx.gbl...
> FULL
> "Reorganize
> to
> log
> run
> log
>
|||Another option is to defrag using DBCC INDEXDEFRAG. Depending on the fragmentation, you might end up with less
in the log file. Also, I suggest that you don't defrag unless you have to (DBCC SHOWCONTIG tell you the
fragmentation level). More info at:
http://www.microsoft.com/technet/pro.../ss2kidbp.mspx
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in message
news:ecf6z8zPEHA.904@.TK2MSFTNGP12.phx.gbl...
> Because the db is 16 GB and you reorg all indexes in the db. Assuming that you have clustered index on all
> tables, then you rebuild 16GB worth of data, and everything is logged. I think that the big question is
> whether you perform regular log backups or not. If not, just run the db in simple recovery mode, and the
> rebuilds are minimally logged. Then if the working space for a days work for the log is 4GB, then you can
just
> keep the log the size it needs. Or shrink it, the article is just to explain side effects of shrinking! If
you
> do run regular log backups, then you could do something like:
> 1. Backup log
> 2. Db to simple recovery
> 3. Do the index rebuilds
> 4. (Do the shrink)
> 5. Db to full recovery
> 6. Backup db
> You now have a window in time where you cannot do point in time recovery. This is (inclusive) from 2 to 6.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Dimitris" <seeyou_gr@.hotmail.com> wrote in message news:%23GPRtwzPEHA.3044@.TK2MSFTNGP10.phx.gbl...
>
ALTER DATABASE (optimization job)
Hi all
I have a database (warehouse) with a data file 16GB with Recovery model FULL
And each week I do a night run for optimization with the options "Reorganize
data and index pages" and "Change free space per page percentage to 10%"
When this night run occurs, the transaction log on my database increases to
17GB
cause to recreation of indexes and so on.
I was wondering about the following, so I could solve the problem of 17GB
transaction logs. The disks are not cheap in an external sub-system with
mirrors and stripes.
1) Before the optimization job start backup the database
2) After the backup change the database recovery model to simple (so no log
will be recorded
3) Backup again database (now on simple mode) so transaction log will be
shrink
4) Run the optimization job
5) Change the database recovery model back to FULL
6) Backup again database (now in FULL mode)
That's the solution I have thought.and all that will be done by the night
run
How dangerous is to change the recovery model of the database before you run
a job' Is the risk high'
Is there an other way to perform the task, without having my transaction log
increased so match?
Thanks in advance
Dimitris Dimolas
Web Programmer
GreeceWhy do you want to shrink the file each week? SQL Server is designed to work with pre-allocation of space. By
not shrinking, you don't have to do anything special at all!
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/
"Dimitris" <seeyou_gr@.hotmail.com> wrote in message news:uW1IwixPEHA.3216@.TK2MSFTNGP12.phx.gbl...
> Hi all
> I have a database (warehouse) with a data file 16GB with Recovery model FULL
> And each week I do a night run for optimization with the options "Reorganize
> data and index pages" and "Change free space per page percentage to 10%"
> When this night run occurs, the transaction log on my database increases to
> 17GB
> cause to recreation of indexes and so on.
> I was wondering about the following, so I could solve the problem of 17GB
> transaction logs. The disks are not cheap in an external sub-system with
> mirrors and stripes.
> 1) Before the optimization job start backup the database
> 2) After the backup change the database recovery model to simple (so no log
> will be recorded
> 3) Backup again database (now on simple mode) so transaction log will be
> shrink
> 4) Run the optimization job
> 5) Change the database recovery model back to FULL
> 6) Backup again database (now in FULL mode)
>
> That's the solution I have thought.and all that will be done by the night
> run
>
> How dangerous is to change the recovery model of the database before you run
> a job' Is the risk high'
>
> Is there an other way to perform the task, without having my transaction log
> increased so match?
>
> Thanks in advance
>
> Dimitris Dimolas
> Web Programmer
> Greece
>|||In addition to the other responses, see the whitepaper at
http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/ss2kidbp.mspx
for index maintenance tips.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Dimitris" <seeyou_gr@.hotmail.com> wrote in message
news:uW1IwixPEHA.3216@.TK2MSFTNGP12.phx.gbl...
> Hi all
> I have a database (warehouse) with a data file 16GB with Recovery model
FULL
> And each week I do a night run for optimization with the options
"Reorganize
> data and index pages" and "Change free space per page percentage to 10%"
> When this night run occurs, the transaction log on my database increases
to
> 17GB
> cause to recreation of indexes and so on.
> I was wondering about the following, so I could solve the problem of 17GB
> transaction logs. The disks are not cheap in an external sub-system with
> mirrors and stripes.
> 1) Before the optimization job start backup the database
> 2) After the backup change the database recovery model to simple (so no
log
> will be recorded
> 3) Backup again database (now on simple mode) so transaction log will be
> shrink
> 4) Run the optimization job
> 5) Change the database recovery model back to FULL
> 6) Backup again database (now in FULL mode)
>
> That's the solution I have thought.and all that will be done by the night
> run
>
> How dangerous is to change the recovery model of the database before you
run
> a job' Is the risk high'
>
> Is there an other way to perform the task, without having my transaction
log
> increased so match?
>
> Thanks in advance
>
> Dimitris Dimolas
> Web Programmer
> Greece
>|||My transactions in a day are about 4gb
So why should i have a log of 17gb'
And the question isn't why.
But how i can done it
You can understand ofcource that if you have a log of 4 GB per day there is
no reason to have a log of 17GB when you r trying to optimize your database.
There is no need.
Thanks
"Dimitris" <seeyou_gr@.hotmail.com> wrote in message
news:uW1IwixPEHA.3216@.TK2MSFTNGP12.phx.gbl...
> Hi all
> I have a database (warehouse) with a data file 16GB with Recovery model
FULL
> And each week I do a night run for optimization with the options
"Reorganize
> data and index pages" and "Change free space per page percentage to 10%"
> When this night run occurs, the transaction log on my database increases
to
> 17GB
> cause to recreation of indexes and so on.
> I was wondering about the following, so I could solve the problem of 17GB
> transaction logs. The disks are not cheap in an external sub-system with
> mirrors and stripes.
> 1) Before the optimization job start backup the database
> 2) After the backup change the database recovery model to simple (so no
log
> will be recorded
> 3) Backup again database (now on simple mode) so transaction log will be
> shrink
> 4) Run the optimization job
> 5) Change the database recovery model back to FULL
> 6) Backup again database (now in FULL mode)
>
> That's the solution I have thought.and all that will be done by the night
> run
>
> How dangerous is to change the recovery model of the database before you
run
> a job' Is the risk high'
>
> Is there an other way to perform the task, without having my transaction
log
> increased so match?
>
> Thanks in advance
>
> Dimitris Dimolas
> Web Programmer
> Greece
>|||Here are a couple of things to consider:
It sounds like you are using the database maintenance plan to reindex,
is this right? If so, I would instead script out a job which will
only reindex those indexes which are fragmented. The maintenance plan
is actually running on all indexes, which will cause a large
transaction log. BOL has a nice sample in under DBCC ShowContig
examples. You can work with that sample to strategize at what level
of fragmentation you want to run the commands.
One other thing to consider, if you are using identity fields for
primary keys and clustered indexes, you are currently rebuilding these
with every run of the maintenance plan. Identity fields rarely become
fragmented, unless you have enables identity insert or if you have a
large amount of delete activity. Chances are that you can probably
get by without reindexing these very often. The script mentioned
above will make this determination.
Your plan to change the recovery model won't necessarily work. The
log will still grow large, as it will not clear the log until the
entire DBCC DBReindex (which the maintenance plan runs) completes. I
would be willing to bet that if you change the job to only address
those indexes which are in need of defragmentation, you probably won't
have a log issue. If this is not the case, and you would like to
backup the log intermittently throughout the process, you can choose
to run a DBCC IndexDefrag instead. Per BOL on IndexDefrag:
"In addition, the defragmentation is always fully logged, regardless
of the database recovery model setting (see ALTER DATABASE). The
defragmentation of a very fragmented index can generate more log than
even a fully logged index creation. The defragmentation, however, is
performed as a series of short transactions and thus does not require
a large log if log backups are taken frequently or if the recovery
model setting is SIMPLE. "
"Dimitris" <seeyou_gr@.hotmail.com> wrote in message news:<uW1IwixPEHA.3216@.TK2MSFTNGP12.phx.gbl>...
> Hi all
> I have a database (warehouse) with a data file 16GB with Recovery model FULL
> And each week I do a night run for optimization with the options "Reorganize
> data and index pages" and "Change free space per page percentage to 10%"
> When this night run occurs, the transaction log on my database increases to
> 17GB
> cause to recreation of indexes and so on.
> I was wondering about the following, so I could solve the problem of 17GB
> transaction logs. The disks are not cheap in an external sub-system with
> mirrors and stripes.
> 1) Before the optimization job start backup the database
> 2) After the backup change the database recovery model to simple (so no log
> will be recorded
> 3) Backup again database (now on simple mode) so transaction log will be
> shrink
> 4) Run the optimization job
> 5) Change the database recovery model back to FULL
> 6) Backup again database (now in FULL mode)
>
> That's the solution I have thought.and all that will be done by the night
> run
>
> How dangerous is to change the recovery model of the database before you run
> a job' Is the risk high'
>
> Is there an other way to perform the task, without having my transaction log
> increased so match?
>
> Thanks in advance
>
> Dimitris Dimolas
> Web Programmer
> Greece|||> My transactions in a day are about 4gb
> So why should i have a log of 17gb'
Because the db is 16 GB and you reorg all indexes in the db. Assuming that you have clustered index on all
tables, then you rebuild 16GB worth of data, and everything is logged. I think that the big question is
whether you perform regular log backups or not. If not, just run the db in simple recovery mode, and the
rebuilds are minimally logged. Then if the working space for a days work for the log is 4GB, then you can just
keep the log the size it needs. Or shrink it, the article is just to explain side effects of shrinking! If you
do run regular log backups, then you could do something like:
1. Backup log
2. Db to simple recovery
3. Do the index rebuilds
4. (Do the shrink)
5. Db to full recovery
6. Backup db
You now have a window in time where you cannot do point in time recovery. This is (inclusive) from 2 to 6.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Dimitris" <seeyou_gr@.hotmail.com> wrote in message news:%23GPRtwzPEHA.3044@.TK2MSFTNGP10.phx.gbl...
> My transactions in a day are about 4gb
> So why should i have a log of 17gb'
> And the question isn't why.
> But how i can done it
> You can understand ofcource that if you have a log of 4 GB per day there is
> no reason to have a log of 17GB when you r trying to optimize your database.
> There is no need.
> Thanks
>
> "Dimitris" <seeyou_gr@.hotmail.com> wrote in message
> news:uW1IwixPEHA.3216@.TK2MSFTNGP12.phx.gbl...
> > Hi all
> >
> > I have a database (warehouse) with a data file 16GB with Recovery model
> FULL
> >
> > And each week I do a night run for optimization with the options
> "Reorganize
> > data and index pages" and "Change free space per page percentage to 10%"
> >
> > When this night run occurs, the transaction log on my database increases
> to
> > 17GB
> >
> > cause to recreation of indexes and so on.
> >
> > I was wondering about the following, so I could solve the problem of 17GB
> > transaction logs. The disks are not cheap in an external sub-system with
> > mirrors and stripes.
> >
> > 1) Before the optimization job start backup the database
> >
> > 2) After the backup change the database recovery model to simple (so no
> log
> > will be recorded
> >
> > 3) Backup again database (now on simple mode) so transaction log will be
> > shrink
> >
> > 4) Run the optimization job
> >
> > 5) Change the database recovery model back to FULL
> >
> > 6) Backup again database (now in FULL mode)
> >
> >
> >
> > That's the solution I have thought.and all that will be done by the night
> > run
> >
> >
> >
> > How dangerous is to change the recovery model of the database before you
> run
> > a job' Is the risk high'
> >
> >
> >
> > Is there an other way to perform the task, without having my transaction
> log
> > increased so match?
> >
> >
> >
> > Thanks in advance
> >
> >
> >
> > Dimitris Dimolas
> >
> > Web Programmer
> >
> > Greece
> >
> >
>|||Another option is to defrag using DBCC INDEXDEFRAG. Depending on the fragmentation, you might end up with less
in the log file. Also, I suggest that you don't defrag unless you have to (DBCC SHOWCONTIG tell you the
fragmentation level). More info at:
http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/ss2kidbp.mspx
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in message
news:ecf6z8zPEHA.904@.TK2MSFTNGP12.phx.gbl...
> > My transactions in a day are about 4gb
> > So why should i have a log of 17gb'
> Because the db is 16 GB and you reorg all indexes in the db. Assuming that you have clustered index on all
> tables, then you rebuild 16GB worth of data, and everything is logged. I think that the big question is
> whether you perform regular log backups or not. If not, just run the db in simple recovery mode, and the
> rebuilds are minimally logged. Then if the working space for a days work for the log is 4GB, then you can
just
> keep the log the size it needs. Or shrink it, the article is just to explain side effects of shrinking! If
you
> do run regular log backups, then you could do something like:
> 1. Backup log
> 2. Db to simple recovery
> 3. Do the index rebuilds
> 4. (Do the shrink)
> 5. Db to full recovery
> 6. Backup db
> You now have a window in time where you cannot do point in time recovery. This is (inclusive) from 2 to 6.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Dimitris" <seeyou_gr@.hotmail.com> wrote in message news:%23GPRtwzPEHA.3044@.TK2MSFTNGP10.phx.gbl...
> > My transactions in a day are about 4gb
> > So why should i have a log of 17gb'
> >
> > And the question isn't why.
> > But how i can done it
> >
> > You can understand ofcource that if you have a log of 4 GB per day there is
> > no reason to have a log of 17GB when you r trying to optimize your database.
> > There is no need.
> >
> > Thanks
> >
> >
> >
> > "Dimitris" <seeyou_gr@.hotmail.com> wrote in message
> > news:uW1IwixPEHA.3216@.TK2MSFTNGP12.phx.gbl...
> > > Hi all
> > >
> > > I have a database (warehouse) with a data file 16GB with Recovery model
> > FULL
> > >
> > > And each week I do a night run for optimization with the options
> > "Reorganize
> > > data and index pages" and "Change free space per page percentage to 10%"
> > >
> > > When this night run occurs, the transaction log on my database increases
> > to
> > > 17GB
> > >
> > > cause to recreation of indexes and so on.
> > >
> > > I was wondering about the following, so I could solve the problem of 17GB
> > > transaction logs. The disks are not cheap in an external sub-system with
> > > mirrors and stripes.
> > >
> > > 1) Before the optimization job start backup the database
> > >
> > > 2) After the backup change the database recovery model to simple (so no
> > log
> > > will be recorded
> > >
> > > 3) Backup again database (now on simple mode) so transaction log will be
> > > shrink
> > >
> > > 4) Run the optimization job
> > >
> > > 5) Change the database recovery model back to FULL
> > >
> > > 6) Backup again database (now in FULL mode)
> > >
> > >
> > >
> > > That's the solution I have thought.and all that will be done by the night
> > > run
> > >
> > >
> > >
> > > How dangerous is to change the recovery model of the database before you
> > run
> > > a job' Is the risk high'
> > >
> > >
> > >
> > > Is there an other way to perform the task, without having my transaction
> > log
> > > increased so match?
> > >
> > >
> > >
> > > Thanks in advance
> > >
> > >
> > >
> > > Dimitris Dimolas
> > >
> > > Web Programmer
> > >
> > > Greece
> > >
> > >
> >
> >
>
I have a database (warehouse) with a data file 16GB with Recovery model FULL
And each week I do a night run for optimization with the options "Reorganize
data and index pages" and "Change free space per page percentage to 10%"
When this night run occurs, the transaction log on my database increases to
17GB
cause to recreation of indexes and so on.
I was wondering about the following, so I could solve the problem of 17GB
transaction logs. The disks are not cheap in an external sub-system with
mirrors and stripes.
1) Before the optimization job start backup the database
2) After the backup change the database recovery model to simple (so no log
will be recorded
3) Backup again database (now on simple mode) so transaction log will be
shrink
4) Run the optimization job
5) Change the database recovery model back to FULL
6) Backup again database (now in FULL mode)
That's the solution I have thought.and all that will be done by the night
run
How dangerous is to change the recovery model of the database before you run
a job' Is the risk high'
Is there an other way to perform the task, without having my transaction log
increased so match?
Thanks in advance
Dimitris Dimolas
Web Programmer
GreeceWhy do you want to shrink the file each week? SQL Server is designed to work with pre-allocation of space. By
not shrinking, you don't have to do anything special at all!
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/
"Dimitris" <seeyou_gr@.hotmail.com> wrote in message news:uW1IwixPEHA.3216@.TK2MSFTNGP12.phx.gbl...
> Hi all
> I have a database (warehouse) with a data file 16GB with Recovery model FULL
> And each week I do a night run for optimization with the options "Reorganize
> data and index pages" and "Change free space per page percentage to 10%"
> When this night run occurs, the transaction log on my database increases to
> 17GB
> cause to recreation of indexes and so on.
> I was wondering about the following, so I could solve the problem of 17GB
> transaction logs. The disks are not cheap in an external sub-system with
> mirrors and stripes.
> 1) Before the optimization job start backup the database
> 2) After the backup change the database recovery model to simple (so no log
> will be recorded
> 3) Backup again database (now on simple mode) so transaction log will be
> shrink
> 4) Run the optimization job
> 5) Change the database recovery model back to FULL
> 6) Backup again database (now in FULL mode)
>
> That's the solution I have thought.and all that will be done by the night
> run
>
> How dangerous is to change the recovery model of the database before you run
> a job' Is the risk high'
>
> Is there an other way to perform the task, without having my transaction log
> increased so match?
>
> Thanks in advance
>
> Dimitris Dimolas
> Web Programmer
> Greece
>|||In addition to the other responses, see the whitepaper at
http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/ss2kidbp.mspx
for index maintenance tips.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Dimitris" <seeyou_gr@.hotmail.com> wrote in message
news:uW1IwixPEHA.3216@.TK2MSFTNGP12.phx.gbl...
> Hi all
> I have a database (warehouse) with a data file 16GB with Recovery model
FULL
> And each week I do a night run for optimization with the options
"Reorganize
> data and index pages" and "Change free space per page percentage to 10%"
> When this night run occurs, the transaction log on my database increases
to
> 17GB
> cause to recreation of indexes and so on.
> I was wondering about the following, so I could solve the problem of 17GB
> transaction logs. The disks are not cheap in an external sub-system with
> mirrors and stripes.
> 1) Before the optimization job start backup the database
> 2) After the backup change the database recovery model to simple (so no
log
> will be recorded
> 3) Backup again database (now on simple mode) so transaction log will be
> shrink
> 4) Run the optimization job
> 5) Change the database recovery model back to FULL
> 6) Backup again database (now in FULL mode)
>
> That's the solution I have thought.and all that will be done by the night
> run
>
> How dangerous is to change the recovery model of the database before you
run
> a job' Is the risk high'
>
> Is there an other way to perform the task, without having my transaction
log
> increased so match?
>
> Thanks in advance
>
> Dimitris Dimolas
> Web Programmer
> Greece
>|||My transactions in a day are about 4gb
So why should i have a log of 17gb'
And the question isn't why.
But how i can done it
You can understand ofcource that if you have a log of 4 GB per day there is
no reason to have a log of 17GB when you r trying to optimize your database.
There is no need.
Thanks
"Dimitris" <seeyou_gr@.hotmail.com> wrote in message
news:uW1IwixPEHA.3216@.TK2MSFTNGP12.phx.gbl...
> Hi all
> I have a database (warehouse) with a data file 16GB with Recovery model
FULL
> And each week I do a night run for optimization with the options
"Reorganize
> data and index pages" and "Change free space per page percentage to 10%"
> When this night run occurs, the transaction log on my database increases
to
> 17GB
> cause to recreation of indexes and so on.
> I was wondering about the following, so I could solve the problem of 17GB
> transaction logs. The disks are not cheap in an external sub-system with
> mirrors and stripes.
> 1) Before the optimization job start backup the database
> 2) After the backup change the database recovery model to simple (so no
log
> will be recorded
> 3) Backup again database (now on simple mode) so transaction log will be
> shrink
> 4) Run the optimization job
> 5) Change the database recovery model back to FULL
> 6) Backup again database (now in FULL mode)
>
> That's the solution I have thought.and all that will be done by the night
> run
>
> How dangerous is to change the recovery model of the database before you
run
> a job' Is the risk high'
>
> Is there an other way to perform the task, without having my transaction
log
> increased so match?
>
> Thanks in advance
>
> Dimitris Dimolas
> Web Programmer
> Greece
>|||Here are a couple of things to consider:
It sounds like you are using the database maintenance plan to reindex,
is this right? If so, I would instead script out a job which will
only reindex those indexes which are fragmented. The maintenance plan
is actually running on all indexes, which will cause a large
transaction log. BOL has a nice sample in under DBCC ShowContig
examples. You can work with that sample to strategize at what level
of fragmentation you want to run the commands.
One other thing to consider, if you are using identity fields for
primary keys and clustered indexes, you are currently rebuilding these
with every run of the maintenance plan. Identity fields rarely become
fragmented, unless you have enables identity insert or if you have a
large amount of delete activity. Chances are that you can probably
get by without reindexing these very often. The script mentioned
above will make this determination.
Your plan to change the recovery model won't necessarily work. The
log will still grow large, as it will not clear the log until the
entire DBCC DBReindex (which the maintenance plan runs) completes. I
would be willing to bet that if you change the job to only address
those indexes which are in need of defragmentation, you probably won't
have a log issue. If this is not the case, and you would like to
backup the log intermittently throughout the process, you can choose
to run a DBCC IndexDefrag instead. Per BOL on IndexDefrag:
"In addition, the defragmentation is always fully logged, regardless
of the database recovery model setting (see ALTER DATABASE). The
defragmentation of a very fragmented index can generate more log than
even a fully logged index creation. The defragmentation, however, is
performed as a series of short transactions and thus does not require
a large log if log backups are taken frequently or if the recovery
model setting is SIMPLE. "
"Dimitris" <seeyou_gr@.hotmail.com> wrote in message news:<uW1IwixPEHA.3216@.TK2MSFTNGP12.phx.gbl>...
> Hi all
> I have a database (warehouse) with a data file 16GB with Recovery model FULL
> And each week I do a night run for optimization with the options "Reorganize
> data and index pages" and "Change free space per page percentage to 10%"
> When this night run occurs, the transaction log on my database increases to
> 17GB
> cause to recreation of indexes and so on.
> I was wondering about the following, so I could solve the problem of 17GB
> transaction logs. The disks are not cheap in an external sub-system with
> mirrors and stripes.
> 1) Before the optimization job start backup the database
> 2) After the backup change the database recovery model to simple (so no log
> will be recorded
> 3) Backup again database (now on simple mode) so transaction log will be
> shrink
> 4) Run the optimization job
> 5) Change the database recovery model back to FULL
> 6) Backup again database (now in FULL mode)
>
> That's the solution I have thought.and all that will be done by the night
> run
>
> How dangerous is to change the recovery model of the database before you run
> a job' Is the risk high'
>
> Is there an other way to perform the task, without having my transaction log
> increased so match?
>
> Thanks in advance
>
> Dimitris Dimolas
> Web Programmer
> Greece|||> My transactions in a day are about 4gb
> So why should i have a log of 17gb'
Because the db is 16 GB and you reorg all indexes in the db. Assuming that you have clustered index on all
tables, then you rebuild 16GB worth of data, and everything is logged. I think that the big question is
whether you perform regular log backups or not. If not, just run the db in simple recovery mode, and the
rebuilds are minimally logged. Then if the working space for a days work for the log is 4GB, then you can just
keep the log the size it needs. Or shrink it, the article is just to explain side effects of shrinking! If you
do run regular log backups, then you could do something like:
1. Backup log
2. Db to simple recovery
3. Do the index rebuilds
4. (Do the shrink)
5. Db to full recovery
6. Backup db
You now have a window in time where you cannot do point in time recovery. This is (inclusive) from 2 to 6.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Dimitris" <seeyou_gr@.hotmail.com> wrote in message news:%23GPRtwzPEHA.3044@.TK2MSFTNGP10.phx.gbl...
> My transactions in a day are about 4gb
> So why should i have a log of 17gb'
> And the question isn't why.
> But how i can done it
> You can understand ofcource that if you have a log of 4 GB per day there is
> no reason to have a log of 17GB when you r trying to optimize your database.
> There is no need.
> Thanks
>
> "Dimitris" <seeyou_gr@.hotmail.com> wrote in message
> news:uW1IwixPEHA.3216@.TK2MSFTNGP12.phx.gbl...
> > Hi all
> >
> > I have a database (warehouse) with a data file 16GB with Recovery model
> FULL
> >
> > And each week I do a night run for optimization with the options
> "Reorganize
> > data and index pages" and "Change free space per page percentage to 10%"
> >
> > When this night run occurs, the transaction log on my database increases
> to
> > 17GB
> >
> > cause to recreation of indexes and so on.
> >
> > I was wondering about the following, so I could solve the problem of 17GB
> > transaction logs. The disks are not cheap in an external sub-system with
> > mirrors and stripes.
> >
> > 1) Before the optimization job start backup the database
> >
> > 2) After the backup change the database recovery model to simple (so no
> log
> > will be recorded
> >
> > 3) Backup again database (now on simple mode) so transaction log will be
> > shrink
> >
> > 4) Run the optimization job
> >
> > 5) Change the database recovery model back to FULL
> >
> > 6) Backup again database (now in FULL mode)
> >
> >
> >
> > That's the solution I have thought.and all that will be done by the night
> > run
> >
> >
> >
> > How dangerous is to change the recovery model of the database before you
> run
> > a job' Is the risk high'
> >
> >
> >
> > Is there an other way to perform the task, without having my transaction
> log
> > increased so match?
> >
> >
> >
> > Thanks in advance
> >
> >
> >
> > Dimitris Dimolas
> >
> > Web Programmer
> >
> > Greece
> >
> >
>|||Another option is to defrag using DBCC INDEXDEFRAG. Depending on the fragmentation, you might end up with less
in the log file. Also, I suggest that you don't defrag unless you have to (DBCC SHOWCONTIG tell you the
fragmentation level). More info at:
http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/ss2kidbp.mspx
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in message
news:ecf6z8zPEHA.904@.TK2MSFTNGP12.phx.gbl...
> > My transactions in a day are about 4gb
> > So why should i have a log of 17gb'
> Because the db is 16 GB and you reorg all indexes in the db. Assuming that you have clustered index on all
> tables, then you rebuild 16GB worth of data, and everything is logged. I think that the big question is
> whether you perform regular log backups or not. If not, just run the db in simple recovery mode, and the
> rebuilds are minimally logged. Then if the working space for a days work for the log is 4GB, then you can
just
> keep the log the size it needs. Or shrink it, the article is just to explain side effects of shrinking! If
you
> do run regular log backups, then you could do something like:
> 1. Backup log
> 2. Db to simple recovery
> 3. Do the index rebuilds
> 4. (Do the shrink)
> 5. Db to full recovery
> 6. Backup db
> You now have a window in time where you cannot do point in time recovery. This is (inclusive) from 2 to 6.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Dimitris" <seeyou_gr@.hotmail.com> wrote in message news:%23GPRtwzPEHA.3044@.TK2MSFTNGP10.phx.gbl...
> > My transactions in a day are about 4gb
> > So why should i have a log of 17gb'
> >
> > And the question isn't why.
> > But how i can done it
> >
> > You can understand ofcource that if you have a log of 4 GB per day there is
> > no reason to have a log of 17GB when you r trying to optimize your database.
> > There is no need.
> >
> > Thanks
> >
> >
> >
> > "Dimitris" <seeyou_gr@.hotmail.com> wrote in message
> > news:uW1IwixPEHA.3216@.TK2MSFTNGP12.phx.gbl...
> > > Hi all
> > >
> > > I have a database (warehouse) with a data file 16GB with Recovery model
> > FULL
> > >
> > > And each week I do a night run for optimization with the options
> > "Reorganize
> > > data and index pages" and "Change free space per page percentage to 10%"
> > >
> > > When this night run occurs, the transaction log on my database increases
> > to
> > > 17GB
> > >
> > > cause to recreation of indexes and so on.
> > >
> > > I was wondering about the following, so I could solve the problem of 17GB
> > > transaction logs. The disks are not cheap in an external sub-system with
> > > mirrors and stripes.
> > >
> > > 1) Before the optimization job start backup the database
> > >
> > > 2) After the backup change the database recovery model to simple (so no
> > log
> > > will be recorded
> > >
> > > 3) Backup again database (now on simple mode) so transaction log will be
> > > shrink
> > >
> > > 4) Run the optimization job
> > >
> > > 5) Change the database recovery model back to FULL
> > >
> > > 6) Backup again database (now in FULL mode)
> > >
> > >
> > >
> > > That's the solution I have thought.and all that will be done by the night
> > > run
> > >
> > >
> > >
> > > How dangerous is to change the recovery model of the database before you
> > run
> > > a job' Is the risk high'
> > >
> > >
> > >
> > > Is there an other way to perform the task, without having my transaction
> > log
> > > increased so match?
> > >
> > >
> > >
> > > Thanks in advance
> > >
> > >
> > >
> > > Dimitris Dimolas
> > >
> > > Web Programmer
> > >
> > > Greece
> > >
> > >
> >
> >
>
ALTER data type
I have created a table from a text file delimited by comma's. The date within the text file is YYYYMMDD and needs to be stored within a smalldatetime column. Unfortunatley whenever I import the data it errors out. If I import the data into a nvarchar column, it imports correctly and then allows me to change the datatype to smalldatetime(thus giving me the correct format). Is there a way I could run a command pre and post to the import?
ALTER TABLE PO MODIFY x_column nvarchar
IMPORT DATA
ALTER TABLE PO MODIFY x_column smalldatetime
hope this is clear enough, thanks for the helpalter table tblname alter column colname smalldatetime
You would be safer importing to a staging table then inserting from there.|||thanks for the quick response, worked like a charm
ALTER TABLE PO MODIFY x_column nvarchar
IMPORT DATA
ALTER TABLE PO MODIFY x_column smalldatetime
hope this is clear enough, thanks for the helpalter table tblname alter column colname smalldatetime
You would be safer importing to a staging table then inserting from there.|||thanks for the quick response, worked like a charm
Friday, February 24, 2012
Allowing users to truncate log file
I would like to allow a particular user to truncate a log file in a
stored procedure that the user runs every day. At this moment the only
personnel that can truncate the log file are personnel with sysadmin
rights. Is there any way to do this in sql server 2005 without
granting this user sysadmin rights (something we REALLY don't want to
do)? Thanks for all your help in advance.
Yes, this can be done with help of certificates. I have an article on my
web site that describes this in detail:
http://www.sommarskog.se/grantperm.html.
stored procedure that the user runs every day. At this moment the only
personnel that can truncate the log file are personnel with sysadmin
rights. Is there any way to do this in sql server 2005 without
granting this user sysadmin rights (something we REALLY don't want to
do)? Thanks for all your help in advance.
Dave C.hedgracer (d.christman@.sbcglobal.net) writes:
Quote:
Originally Posted by
I would like to allow a particular user to truncate a log file in a
stored procedure that the user runs every day. At this moment the only
personnel that can truncate the log file are personnel with sysadmin
rights. Is there any way to do this in sql server 2005 without
granting this user sysadmin rights (something we REALLY don't want to
do)? Thanks for all your help in advance.
Yes, this can be done with help of certificates. I have an article on my
web site that describes this in detail:
http://www.sommarskog.se/grantperm.html.
However, this not at all sound right to me, at least if the user would
truncate the log file every day. Truncating the log is something you
only do in exceptional cases when there is an emergency. Normally, you
either:
1) Run with full recovery and schedule regular full backups as well as
transaction log backups.
2) Run with simple recovery and schedule only full backups. The log
will be auto-truncated.
When you run with full recovery, you do so, because you want to be able
to recover the database to any given point in time. But if you truncate
the log, you lose that possibility. Which in fact is self-evident in
SQL 2005, where the only way to do this is to set the database into
simple recovery.
--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx
Sunday, February 19, 2012
allowing null values
I have a flat file that I'm reading from and loading my tables with. In that file I have a column that has numbers (2000,1999,1998 and so on) and the column that they are being loaded into is defined as an INT. The issue I'm running into is that the first 50 or so rows in the flat file is empty for this column so I'm getting an error message. If I put numbers in that column in the flat file it works, if i remove them it fails. How can I allow for NULL values for my INT column on the database table?
here is the error I'm getting:
[OLE DB Destination [182]] Error: There was an error with input column "SalesYear" (7259) on input "OLE DB Destination Input" (195). The column status returned was: "The value could not be converted because of a potential loss of data.".What is the format of the flat file?
CSV? Tab delimited? Fixed width?|||
I got it to work, I had to go into the flat file connection and make some changes there on the field. Once I did that it loads correctly.
|||
IGotyourdotnet wrote:
I got it to work, I had to go into the flat file connection and make some changes there on the field. Once I did that it loads correctly.
Can you share what kind of changes you made to perhaps help others down the road?
Thanks.|||
I just changed the data type of the column. So instead of creating a derived column for it, I just changed it in the flat file connection manager.
|||I have a similar problem.Could you tell me what datatype you used in Flat file connection manager and the data type used in databse?|||
I have a similar problem.Could you tell me what datatype you used in Flat file connection manager and the data type used in databse?
Try a character data type.
allowing null values
I have a flat file that I'm reading from and loading my tables with. In that file I have a column that has numbers (2000,1999,1998 and so on) and the column that they are being loaded into is defined as an INT. The issue I'm running into is that the first 50 or so rows in the flat file is empty for this column so I'm getting an error message. If I put numbers in that column in the flat file it works, if i remove them it fails. How can I allow for NULL values for my INT column on the database table?
here is the error I'm getting:
[OLE DB Destination [182]] Error: There was an error with input column "SalesYear" (7259) on input "OLE DB Destination Input" (195). The column status returned was: "The value could not be converted because of a potential loss of data.".What is the format of the flat file?
CSV? Tab delimited? Fixed width?|||
I got it to work, I had to go into the flat file connection and make some changes there on the field. Once I did that it loads correctly.
|||
I got it to work, I had to go into the flat file connection and make some changes there on the field. Once I did that it loads correctly.
Can you share what kind of changes you made to perhaps help others down the road?
Thanks.|||
I just changed the data type of the column. So instead of creating a derived column for it, I just changed it in the flat file connection manager.
|||I have a similar problem.Could you tell me what datatype you used in Flat file connection manager and the data type used in databse?|||
I have a similar problem.Could you tell me what datatype you used in Flat file connection manager and the data type used in databse?
Try a character data type.
Thursday, February 16, 2012
Allow null in a field in Flat File Source
How could I specify in either FF Connection manager or source that it shouldnt give any error and assume blank or no value as NULL ?
Thanks,
FahadYou can't. There is no such thing in a flat file. You'll have to bring in your empty field and then use a derived column to set it to NULL, if necessary.|||Can I change type in derived column ?
I am able to get the field in STR column, Now I wanna change the type of it. Can I ?|||
Fahad349 wrote:
Can I change type in derived column ?
I am able to get the field in STR column, Now I wanna change the type of it. Can I ?
You can do all of this in a derived column. You can't change the type of a column in the dataflow, but you can CAST it to a new type in a NEW column.|||Ok, Last thing is, the source feed contains spaces instead of no value between 2 commas, and I think this makes FF Source failed, what do you think ?|||Import as string, then you can TRIM() that field later in a derived column.|||
you can go to the properties of the Flat file source and set 'RetainNulls' to 'True'.
I think that solves your problem.
|||you can go to the properties of the Flat file source and set 'RetainNulls' to 'True'.
I think that solves your problem.
Except the OP stated that the value was blank or empty, not null (char(0)).
Allow null in a field in Flat File Source
How could I specify in either FF Connection manager or source that it shouldnt give any error and assume blank or no value as NULL ?
Thanks,
FahadYou can't. There is no such thing in a flat file. You'll have to bring in your empty field and then use a derived column to set it to NULL, if necessary.|||Can I change type in derived column ?
I am able to get the field in STR column, Now I wanna change the type of it. Can I ?|||
Can I change type in derived column ?
I am able to get the field in STR column, Now I wanna change the type of it. Can I ?
You can do all of this in a derived column. You can't change the type of a column in the dataflow, but you can CAST it to a new type in a NEW column.|||Ok, Last thing is, the source feed contains spaces instead of no value between 2 commas, and I think this makes FF Source failed, what do you think ?|||Import as string, then you can TRIM() that field later in a derived column.|||
you can go to the properties of the Flat file source and set 'RetainNulls' to 'True'.
I think that solves your problem.
|||you can go to the properties of the Flat file source and set 'RetainNulls' to 'True'.
I think that solves your problem.
Except the OP stated that the value was blank or empty, not null (char(0)).
Monday, February 13, 2012
Allocate space
Hi,
I want to allocate 20 GB for a new database, what is the
best way of do it'
I simply allocate a .mdf file of 20 GB or create 4 of 5
GB ? i just have one RAID with 140 GB,the storage is
shared with other hosts, i have the servers's H:\ drive
pointing the storage with 70 GB for my databases in my
instance.
Thanks a lot
Miguel CorreiaI think it doesn't make much of a difference with 1 file or 4 files on the
same Raid disk ...
--
HTH,
Vinod Kumar
MCSE, DBA, MCAD, MCSD
http://www.extremeexperts.com
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp
"Miguel" <cmlcorreia@.netcabo.pt> wrote in message
news:0ab001c3a398$b410eb30$a501280a@.phx.gbl...
> Hi,
> I want to allocate 20 GB for a new database, what is the
> best way of do it'
> I simply allocate a .mdf file of 20 GB or create 4 of 5
> GB ? i just have one RAID with 140 GB,the storage is
> shared with other hosts, i have the servers's H:\ drive
> pointing the storage with 70 GB for my databases in my
> instance.
> Thanks a lot
> Miguel Correia
I want to allocate 20 GB for a new database, what is the
best way of do it'
I simply allocate a .mdf file of 20 GB or create 4 of 5
GB ? i just have one RAID with 140 GB,the storage is
shared with other hosts, i have the servers's H:\ drive
pointing the storage with 70 GB for my databases in my
instance.
Thanks a lot
Miguel CorreiaI think it doesn't make much of a difference with 1 file or 4 files on the
same Raid disk ...
--
HTH,
Vinod Kumar
MCSE, DBA, MCAD, MCSD
http://www.extremeexperts.com
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp
"Miguel" <cmlcorreia@.netcabo.pt> wrote in message
news:0ab001c3a398$b410eb30$a501280a@.phx.gbl...
> Hi,
> I want to allocate 20 GB for a new database, what is the
> best way of do it'
> I simply allocate a .mdf file of 20 GB or create 4 of 5
> GB ? i just have one RAID with 140 GB,the storage is
> shared with other hosts, i have the servers's H:\ drive
> pointing the storage with 70 GB for my databases in my
> instance.
> Thanks a lot
> Miguel Correia
Subscribe to:
Posts (Atom)