ALTER TABLE In cursor

Hi, All
I have a cursor which sets the value of @.tablename to a user table and
another cursor which set the @.contraint to a valid contraint name.
Why does the statement below produce a incorrect syntax error
alter table @.tablename drop constraint @.constraint
If I substitute the variables with actual values it works fine
Please help
You can't use parameters in an ALTER TABLE statement. Use:
EXEC ('alter table ' + @.tablename + ' drop constraint ' + @.constraint)
Jacco Schalkwijk
SQL Server MVP
"Antony" <Antony@.discussions.microsoft.com> wrote in message
news:89619AFE-048B-4002-AEF4-E7D0A1EA527D@.microsoft.com...
> Hi, All
> I have a cursor which sets the value of @.tablename to a user table and
> another cursor which set the @.contraint to a valid contraint name.
> Why does the statement below produce a incorrect syntax error
> alter table @.tablename drop constraint @.constraint
> If I substitute the variables with actual values it works fine
> Please help

Alter table

Hi to all!
I have a silly problem, but I'm not able to solve it!
I know how to add a field in a table via Enterprise manager and I know also via ALTER TABLE command.
...But the problem is: I want to add a field NOT in the last position, but on the middle (for istance)
So, this is possible via Enterprise Manager but with the ALTER TABLE command? How can I add a field without put it in the last position?
...Thanks a lot!
Sergio

p.s.: Sorry for my poor english..I hope you understand my question!Do it in enterprise manager->design table, click on the little scroll with briefcase icon (thirs on the tool bar) and copy the generated SQL code from there, and exit design without saving.|||Originally posted by HanafiH
Do it in enterprise manager->design table, click on the little scroll with briefcase icon (thirs on the tool bar) and copy the generated SQL code from there, and exit design without saving.

THANKS A LOT! I SOLVED MY PROBLEM!..

I never take care about icons .........bur from NOW I will consider IT!
Once again thank you!
Sergio

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...
>

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...
>

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...
>

Alter coulmn (Replication applied)

Hi All
I dont know how to modify the table schema (e.g. Alter Column) without
removing the applied replication. Would anyone please tell me the solution?
Thanks alot,
Douglas
Douglas,
With SQL2000 you can add/drop columns in a replicated table using
sp_repladdcolumn and sp_repldropcolumn but there is no facility to alter
them. One way to accomplish this would be to unsubscribe, unpublish and then
do the alter table...alter column followed by republishing and
resubscribing. One more way would be to add a new column with the required
format, update the new column with data from the column which was meant to
be altered and then drop it, all without unpublishing. I havent tried the
latter option but a quick guess on the amount of work to be done, I think it
will be a pain.
Dinesh
SQL Server MVP
--
SQL Server FAQ at
http://www.tkdinesh.com
"douglas" <douglaswong@.hotmail.com> wrote in message
news:#tcohAGREHA.2932@.TK2MSFTNGP10.phx.gbl...
> Hi All
> I dont know how to modify the table schema (e.g. Alter Column) without
> removing the applied replication. Would anyone please tell me the
solution?
> Thanks alot,
> Douglas
>

Alter coulmn (Replication applied)

Hi All
I dont know how to modify the table schema (e.g. Alter Column) without
removing the applied replication. Would anyone please tell me the solution?
Thanks alot,
DouglasDouglas,
With SQL2000 you can add/drop columns in a replicated table using
sp_repladdcolumn and sp_repldropcolumn but there is no facility to alter
them. One way to accomplish this would be to unsubscribe, unpublish and then
do the alter table...alter column followed by republishing and
resubscribing. One more way would be to add a new column with the required
format, update the new column with data from the column which was meant to
be altered and then drop it, all without unpublishing. I havent tried the
latter option but a quick guess on the amount of work to be done, I think it
will be a pain.
Dinesh
SQL Server MVP
--
--
SQL Server FAQ at
http://www.tkdinesh.com
"douglas" <douglaswong@.hotmail.com> wrote in message
news:#tcohAGREHA.2932@.TK2MSFTNGP10.phx.gbl...
> Hi All
> I dont know how to modify the table schema (e.g. Alter Column) without
> removing the applied replication. Would anyone please tell me the
solution?
> Thanks alot,
> Douglas
>