Hi Group,
I'm looking for an alternate solution to delete rows from a temporary table.
Now, when the users run reports, the data belonging to each user is stored
in a temporary table, having an userid attached to each row.
Before starting a new report, the program issues a delete command like:
delete tmptable where usr = 123 to prepare the table for the new report.
This table grows very large, it can have 1.5..2 million records per user.
The problem I'm having is that the delete operation times out.
And also the log file grows very fast.
I changed the timeout to 10 minutes - values above this seem unreasonable
long to me...
I checked the TRUNCATE TABLE command - it works fast, it doesn't write info
to the log - but it doesn't have a where clause... so it would wipe out
information belonging to other users.
The temporary table is not bound in any FK references.
It has a clustered index built on the usrid field.
Is there any way of deleting records using DELETE command, but without
writing info to the log ? I mean, I know for sure this data is not so
important as to be logged when deleted...
Please help !
Thank you for any suggestion !
Andrei.All DELETE statements are logged and there is no way around that. The
"temp" table that you mention is actually a permanent table. Have you
considered going with an actual temp table - one whose name begins with#?
That would allow you to truncate the entire table (unlogged) without
affecting other users.
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Toronto, ON Canada
.
"Andrei" <andrei.toma@.era-environmental.com> wrote in message
news:O5FmFwWmGHA.4816@.TK2MSFTNGP03.phx.gbl...
Hi Group,
I'm looking for an alternate solution to delete rows from a temporary table.
Now, when the users run reports, the data belonging to each user is stored
in a temporary table, having an userid attached to each row.
Before starting a new report, the program issues a delete command like:
delete tmptable where usr = 123 to prepare the table for the new report.
This table grows very large, it can have 1.5..2 million records per user.
The problem I'm having is that the delete operation times out.
And also the log file grows very fast.
I changed the timeout to 10 minutes - values above this seem unreasonable
long to me...
I checked the TRUNCATE TABLE command - it works fast, it doesn't write info
to the log - but it doesn't have a where clause... so it would wipe out
information belonging to other users.
The temporary table is not bound in any FK references.
It has a clustered index built on the usrid field.
Is there any way of deleting records using DELETE command, but without
writing info to the log ? I mean, I know for sure this data is not so
important as to be logged when deleted...
Please help !
Thank you for any suggestion !
Andrei.|||Hi Tom and thanks for the fast answer !
You mentioned correctly that this is actually a permanent table - it's only
temporary from the point of view of the reporting action... we're so used to
call them temporary that it went out like this.
The problem is that I'm further using the "temporary" table in the report
generation and the report queries are based on this table... I don't see a
way of using different #tmp tables, belonging to different users, in the
same report...
Any ideas ?
Thank you !
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
news:uIlKRzWmGHA.3300@.TK2MSFTNGP05.phx.gbl...
> All DELETE statements are logged and there is no way around that. The
> "temp" table that you mention is actually a permanent table. Have you
> considered going with an actual temp table - one whose name begins with#?
> That would allow you to truncate the entire table (unlogged) without
> affecting other users.
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Toronto, ON Canada
> .
> "Andrei" <andrei.toma@.era-environmental.com> wrote in message
> news:O5FmFwWmGHA.4816@.TK2MSFTNGP03.phx.gbl...
> Hi Group,
> I'm looking for an alternate solution to delete rows from a temporary
> table.
> Now, when the users run reports, the data belonging to each user is stored
> in a temporary table, having an userid attached to each row.
> Before starting a new report, the program issues a delete command like:
> delete tmptable where usr = 123 to prepare the table for the new report.
> This table grows very large, it can have 1.5..2 million records per user.
> The problem I'm having is that the delete operation times out.
> And also the log file grows very fast.
> I changed the timeout to 10 minutes - values above this seem unreasonable
> long to me...
> I checked the TRUNCATE TABLE command - it works fast, it doesn't write
> info
> to the log - but it doesn't have a where clause... so it would wipe out
> information belonging to other users.
> The temporary table is not bound in any FK references.
> It has a clustered index built on the usrid field.
> Is there any way of deleting records using DELETE command, but without
> writing info to the log ? I mean, I know for sure this data is not so
> important as to be logged when deleted...
> Please help !
> Thank you for any suggestion !
> Andrei.
>
>|||That's a toughie. The only other suggestion is to delete in chunks, say
10,000 rows at a time.
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Toronto, ON Canada
.
"Andrei" <andrei.toma@.era-environmental.com> wrote in message
news:uaBp1%23WmGHA.2120@.TK2MSFTNGP05.phx.gbl...
Hi Tom and thanks for the fast answer !
You mentioned correctly that this is actually a permanent table - it's only
temporary from the point of view of the reporting action... we're so used to
call them temporary that it went out like this.
The problem is that I'm further using the "temporary" table in the report
generation and the report queries are based on this table... I don't see a
way of using different #tmp tables, belonging to different users, in the
same report...
Any ideas ?
Thank you !
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
news:uIlKRzWmGHA.3300@.TK2MSFTNGP05.phx.gbl...
> All DELETE statements are logged and there is no way around that. The
> "temp" table that you mention is actually a permanent table. Have you
> considered going with an actual temp table - one whose name begins with#?
> That would allow you to truncate the entire table (unlogged) without
> affecting other users.
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Toronto, ON Canada
> .
> "Andrei" <andrei.toma@.era-environmental.com> wrote in message
> news:O5FmFwWmGHA.4816@.TK2MSFTNGP03.phx.gbl...
> Hi Group,
> I'm looking for an alternate solution to delete rows from a temporary
> table.
> Now, when the users run reports, the data belonging to each user is stored
> in a temporary table, having an userid attached to each row.
> Before starting a new report, the program issues a delete command like:
> delete tmptable where usr = 123 to prepare the table for the new report.
> This table grows very large, it can have 1.5..2 million records per user.
> The problem I'm having is that the delete operation times out.
> And also the log file grows very fast.
> I changed the timeout to 10 minutes - values above this seem unreasonable
> long to me...
> I checked the TRUNCATE TABLE command - it works fast, it doesn't write
> info
> to the log - but it doesn't have a where clause... so it would wipe out
> information belonging to other users.
> The temporary table is not bound in any FK references.
> It has a clustered index built on the usrid field.
> Is there any way of deleting records using DELETE command, but without
> writing info to the log ? I mean, I know for sure this data is not so
> important as to be logged when deleted...
> Please help !
> Thank you for any suggestion !
> Andrei.
>
>|||I'm sure this is not a good solution, but you could change your database
recovery to simple.
--
If you are looking for SQL Server examples check out my Website at
http://www.geocities.com/sqlserverexamples
"Andrei" wrote:
> Hi Group,
> I'm looking for an alternate solution to delete rows from a temporary tabl
e.
> Now, when the users run reports, the data belonging to each user is stored
> in a temporary table, having an userid attached to each row.
> Before starting a new report, the program issues a delete command like:
> delete tmptable where usr = 123 to prepare the table for the new report.
> This table grows very large, it can have 1.5..2 million records per user.
> The problem I'm having is that the delete operation times out.
> And also the log file grows very fast.
> I changed the timeout to 10 minutes - values above this seem unreasonable
> long to me...
> I checked the TRUNCATE TABLE command - it works fast, it doesn't write inf
o
> to the log - but it doesn't have a where clause... so it would wipe out
> information belonging to other users.
> The temporary table is not bound in any FK references.
> It has a clustered index built on the usrid field.
> Is there any way of deleting records using DELETE command, but without
> writing info to the log ? I mean, I know for sure this data is not so
> important as to be logged when deleted...
> Please help !
> Thank you for any suggestion !
> Andrei.
>
>
>|||Alternatively you could put the table in a database by itself and then set
that database to simple recover mode.
--
If you are looking for SQL Server examples check out my Website at
http://www.geocities.com/sqlserverexamples
"Greg Larsen" wrote:
> I'm sure this is not a good solution, but you could change your database
> recovery to simple.
> --
> If you are looking for SQL Server examples check out my Website at
> http://www.geocities.com/sqlserverexamples
>
> "Andrei" wrote:
>|||Deletes are still logged in simple mode.
This solution depends on what the OP wants. If he wants the operation to be
as fast as a truncate, simple mode won't help. If he wants to make sure the
log doesn't fill up during the delete, simple won't help.
--
HTH
Kalen Delaney, SQL Server MVP
"Greg Larsen" <gregalarsen@.removeit.msn.com> wrote in message
news:45C04641-98F9-4E3C-BD5F-FFF19610F15D@.microsoft.com...
> Alternatively you could put the table in a database by itself and then set
> that database to simple recover mode.
> --
> If you are looking for SQL Server examples check out my Website at
> http://www.geocities.com/sqlserverexamples
>
> "Greg Larsen" wrote:
>|||Thank you, Tom, I'm going to try that.
Still leaves me with a big log file :(
I guess I'll have to clear it more often...
Andrei.
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
news:%23YLmjBXmGHA.4076@.TK2MSFTNGP03.phx.gbl...
> That's a toughie. The only other suggestion is to delete in chunks, say
> 10,000 rows at a time.
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Toronto, ON Canada
> .
> "Andrei" <andrei.toma@.era-environmental.com> wrote in message
> news:uaBp1%23WmGHA.2120@.TK2MSFTNGP05.phx.gbl...
> Hi Tom and thanks for the fast answer !
> You mentioned correctly that this is actually a permanent table - it's
> only
> temporary from the point of view of the reporting action... we're so used
> to
> call them temporary that it went out like this.
> The problem is that I'm further using the "temporary" table in the report
> generation and the report queries are based on this table... I don't see a
> way of using different #tmp tables, belonging to different users, in the
> same report...
> Any ideas ?
> Thank you !
>
>
>
> "Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
> news:uIlKRzWmGHA.3300@.TK2MSFTNGP05.phx.gbl...
>|||Ouch! That's a very unwieldly design issue.
Based upon your reply, it may be that your only viable option is DELETE
(which is logged).
Arnie Rowland, YACE*
"To be successful, your heart must accompany your knowledge."
*Yet Another certification Exam
"Andrei" <andrei.toma@.era-environmental.com> wrote in message
news:uaBp1%23WmGHA.2120@.TK2MSFTNGP05.phx.gbl...
> Hi Tom and thanks for the fast answer !
> You mentioned correctly that this is actually a permanent table - it's
> only temporary from the point of view of the reporting action... we're so
> used to call them temporary that it went out like this.
> The problem is that I'm further using the "temporary" table in the report
> generation and the report queries are based on this table... I don't see a
> way of using different #tmp tables, belonging to different users, in the
> same report...
> Any ideas ?
> Thank you !
>
>
>
> "Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
> news:uIlKRzWmGHA.3300@.TK2MSFTNGP05.phx.gbl...
>|||Thank you, Greg and Kalen.
I need to get both of them - don't we all ? :) - less delete time and
smaller log size but in this specific order...
Thank you !
"Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
news:ORvK%23LXmGHA.464@.TK2MSFTNGP05.phx.gbl...
> Deletes are still logged in simple mode.
> This solution depends on what the OP wants. If he wants the operation to
> be as fast as a truncate, simple mode won't help. If he wants to make sure
> the log doesn't fill up during the delete, simple won't help.
> --
> HTH
> Kalen Delaney, SQL Server MVP
>
> "Greg Larsen" <gregalarsen@.removeit.msn.com> wrote in message
> news:45C04641-98F9-4E3C-BD5F-FFF19610F15D@.microsoft.com...
>
Showing posts with label run. Show all posts
Showing posts with label run. Show all posts
Thursday, March 29, 2012
Tuesday, March 27, 2012
Altering a stored procedure
I am modifying a stored procedure to perform a check before updating the
table. I wanted to run tnis if else stmt to see if there is a better way to
perform the task.
The original sp generates a account number for a customer and updates the
table with the new account number.
I want to add a IF stmt to check if the account number already exists before
generating the account number, Print a message that the account # already
exsits and it will keep checking until a new account# appears.
The code:
IF (SELECT @.accountnumber <> @.accountnumberinput) FROM tablekeys
WHERE keyname = 'accountnumber')
BEGIN
UPDATE tablekeys SET @.accountnumberinput = currentvalue = (currentvalue /
2) +
((currentvalue % 2 + ((currentvalue / 8) % 2)) % 2) * POWER(2, 30)
WHERE keyname = 'accountnumber'
SELECT @.accountnumber = db.rcutil_inttobasex(@.accountnumberinput,
'0123456789BCDFGHJKLMNPQRSTVWXYZ')
END
ELSE
PRINT 'Account Number already exists, trying again'Am Mon, 5 Jun 2006 09:03:02 -0700 schrieb SAM:
> I am modifying a stored procedure to perform a check before updating the
> table. I wanted to run tnis if else stmt to see if there is a better way t
o
> perform the task.
> The original sp generates a account number for a customer and updates the
> table with the new account number.
> I want to add a IF stmt to check if the account number already exists befo
re
> generating the account number, Print a message that the account # already
> exsits and it will keep checking until a new account# appears.
>
This is not possible with a stored procedure because a stored proc is not
made for interactive communication. The stored proc can only send back
something (a value, a result set, an error message) to the calling
application, the rest must be done by the application and not by the stored
procedure.
So you can do the check and if it fails you can send back the message to
the calling application, then the calling application shows the message to
the user, the user enters a new account number and the application calls
the stored proc again with the new account number and so on...
By the way, your IF looks wrong for me, is this working? I think not.
For example, if i want to check if the @.newaccountnumber exists in the
accounttable, i would write the statement this way:
IF exists(select * from accounttable where accountnumber =
@.newaccountnumber) begin
raiserror('account number exists',16,1)
return -1
end
...
But if i am wrong, forget this sample :-))
bye, Helmut|||The user doesn't enter the account number. The stored procedure generates th
e
account number and assigns it the customer or user by updating the table.
I was just displaying a message but it is not necessary. I just wanted to
perform a check within the procedure to check the account prior to generatin
g
the new account number.
Therefore, there is nothing entered by the user or application for
interaction with the sp.
Would I still use your sample?
"Helmut Woess" wrote:
> Am Mon, 5 Jun 2006 09:03:02 -0700 schrieb SAM:
>
> This is not possible with a stored procedure because a stored proc is not
> made for interactive communication. The stored proc can only send back
> something (a value, a result set, an error message) to the calling
> application, the rest must be done by the application and not by the store
d
> procedure.
> So you can do the check and if it fails you can send back the message to
> the calling application, then the calling application shows the message to
> the user, the user enters a new account number and the application calls
> the stored proc again with the new account number and so on...
> By the way, your IF looks wrong for me, is this working? I think not.
> For example, if i want to check if the @.newaccountnumber exists in the
> accounttable, i would write the statement this way:
> IF exists(select * from accounttable where accountnumber =
> @.newaccountnumber) begin
> raiserror('account number exists',16,1)
> return -1
> end
> ...
> But if i am wrong, forget this sample :-))
> bye, Helmut
>|||Am Mon, 5 Jun 2006 09:55:01 -0700 schrieb SAM:
> The user doesn't enter the account number. The stored procedure generates
the
> account number and assigns it the customer or user by updating the table.
> I was just displaying a message but it is not necessary. I just wanted to
> perform a check within the procedure to check the account prior to generat
ing
> the new account number.
> Therefore, there is nothing entered by the user or application for
> interaction with the sp.
> Would I still use your sample?
Hm, okay, sorry, my english is not the best, propably i missunderstand you.
And i cannot find out, why there is @.accountnumberinput, if nothing is
entered by user or application. So i don't know how you will generate a
unique accountnumber if the first generated number is not unique..?
I would need more input because i don't understand your question :-(
bye, Helmut|||It is grapping that value from another table.
When a new user is added via the Web UI, a new account number generated and
added to the table along with the customer information that was entered by
the user. The account number is not entered by the user, the system assigns
this number via the store procedure.
Currently, when a new user is added under an exisitng account, that user
shares the same account number. We do not want this to happen. We want each
user, rather with the same company or under the same account name to have
their own account number.
Therefore, I wanted to alter the existing stored procedure to add a check
clause. If the new user that is being added and the system tries to assigned
an existing account number to the new user, I want a flag or check clause to
not assigned the user the same acct # but generated a new one and assigned i
t
to the new user.
I hope that makes more sense.
Actually, I think I need to perform this check in another stored procedure
that is creating the account information. I will post that code in a few
minutes. Thanks
"Helmut Woess" wrote:
> Am Mon, 5 Jun 2006 09:55:01 -0700 schrieb SAM:
>
> Hm, okay, sorry, my english is not the best, propably i missunderstand you
.
> And i cannot find out, why there is @.accountnumberinput, if nothing is
> entered by user or application. So i don't know how you will generate a
> unique accountnumber if the first generated number is not unique..?
> I would need more input because i don't understand your question :-(
> bye, Helmut
>sql
table. I wanted to run tnis if else stmt to see if there is a better way to
perform the task.
The original sp generates a account number for a customer and updates the
table with the new account number.
I want to add a IF stmt to check if the account number already exists before
generating the account number, Print a message that the account # already
exsits and it will keep checking until a new account# appears.
The code:
IF (SELECT @.accountnumber <> @.accountnumberinput) FROM tablekeys
WHERE keyname = 'accountnumber')
BEGIN
UPDATE tablekeys SET @.accountnumberinput = currentvalue = (currentvalue /
2) +
((currentvalue % 2 + ((currentvalue / 8) % 2)) % 2) * POWER(2, 30)
WHERE keyname = 'accountnumber'
SELECT @.accountnumber = db.rcutil_inttobasex(@.accountnumberinput,
'0123456789BCDFGHJKLMNPQRSTVWXYZ')
END
ELSE
PRINT 'Account Number already exists, trying again'Am Mon, 5 Jun 2006 09:03:02 -0700 schrieb SAM:
> I am modifying a stored procedure to perform a check before updating the
> table. I wanted to run tnis if else stmt to see if there is a better way t
o
> perform the task.
> The original sp generates a account number for a customer and updates the
> table with the new account number.
> I want to add a IF stmt to check if the account number already exists befo
re
> generating the account number, Print a message that the account # already
> exsits and it will keep checking until a new account# appears.
>
This is not possible with a stored procedure because a stored proc is not
made for interactive communication. The stored proc can only send back
something (a value, a result set, an error message) to the calling
application, the rest must be done by the application and not by the stored
procedure.
So you can do the check and if it fails you can send back the message to
the calling application, then the calling application shows the message to
the user, the user enters a new account number and the application calls
the stored proc again with the new account number and so on...
By the way, your IF looks wrong for me, is this working? I think not.
For example, if i want to check if the @.newaccountnumber exists in the
accounttable, i would write the statement this way:
IF exists(select * from accounttable where accountnumber =
@.newaccountnumber) begin
raiserror('account number exists',16,1)
return -1
end
...
But if i am wrong, forget this sample :-))
bye, Helmut|||The user doesn't enter the account number. The stored procedure generates th
e
account number and assigns it the customer or user by updating the table.
I was just displaying a message but it is not necessary. I just wanted to
perform a check within the procedure to check the account prior to generatin
g
the new account number.
Therefore, there is nothing entered by the user or application for
interaction with the sp.
Would I still use your sample?
"Helmut Woess" wrote:
> Am Mon, 5 Jun 2006 09:03:02 -0700 schrieb SAM:
>
> This is not possible with a stored procedure because a stored proc is not
> made for interactive communication. The stored proc can only send back
> something (a value, a result set, an error message) to the calling
> application, the rest must be done by the application and not by the store
d
> procedure.
> So you can do the check and if it fails you can send back the message to
> the calling application, then the calling application shows the message to
> the user, the user enters a new account number and the application calls
> the stored proc again with the new account number and so on...
> By the way, your IF looks wrong for me, is this working? I think not.
> For example, if i want to check if the @.newaccountnumber exists in the
> accounttable, i would write the statement this way:
> IF exists(select * from accounttable where accountnumber =
> @.newaccountnumber) begin
> raiserror('account number exists',16,1)
> return -1
> end
> ...
> But if i am wrong, forget this sample :-))
> bye, Helmut
>|||Am Mon, 5 Jun 2006 09:55:01 -0700 schrieb SAM:
> The user doesn't enter the account number. The stored procedure generates
the
> account number and assigns it the customer or user by updating the table.
> I was just displaying a message but it is not necessary. I just wanted to
> perform a check within the procedure to check the account prior to generat
ing
> the new account number.
> Therefore, there is nothing entered by the user or application for
> interaction with the sp.
> Would I still use your sample?
Hm, okay, sorry, my english is not the best, propably i missunderstand you.
And i cannot find out, why there is @.accountnumberinput, if nothing is
entered by user or application. So i don't know how you will generate a
unique accountnumber if the first generated number is not unique..?
I would need more input because i don't understand your question :-(
bye, Helmut|||It is grapping that value from another table.
When a new user is added via the Web UI, a new account number generated and
added to the table along with the customer information that was entered by
the user. The account number is not entered by the user, the system assigns
this number via the store procedure.
Currently, when a new user is added under an exisitng account, that user
shares the same account number. We do not want this to happen. We want each
user, rather with the same company or under the same account name to have
their own account number.
Therefore, I wanted to alter the existing stored procedure to add a check
clause. If the new user that is being added and the system tries to assigned
an existing account number to the new user, I want a flag or check clause to
not assigned the user the same acct # but generated a new one and assigned i
t
to the new user.
I hope that makes more sense.
Actually, I think I need to perform this check in another stored procedure
that is creating the account information. I will post that code in a few
minutes. Thanks
"Helmut Woess" wrote:
> Am Mon, 5 Jun 2006 09:55:01 -0700 schrieb SAM:
>
> Hm, okay, sorry, my english is not the best, propably i missunderstand you
.
> And i cannot find out, why there is @.accountnumberinput, if nothing is
> entered by user or application. So i don't know how you will generate a
> unique accountnumber if the first generated number is not unique..?
> I would need more input because i don't understand your question :-(
> bye, Helmut
>sql
Sunday, March 25, 2012
Altering a column on a replicated table
Tom,
nice that someone read it
If you're running these commands as a script, you'll need
a GO after each command, otherwise you can just run them
individually one-by-one.
Rgds,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
OK, that worked and I saw the queue reader agent process the commands and
the distribution agent's last action taken now reads "The initial snapshot
for article 'RequistionDetail' is not yet available'.
Now what do I need to do to generate a new snapshot of this one article in
order to get these changes to propogate over to the my subscriber?
Thanks-Tom
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:0f4b01c51511$e4181ce0$a401280a@.phx.gbl...
> Tom,
> nice that someone read it
> If you're running these commands as a script, you'll need
> a GO after each command, otherwise you can just run them
> individually one-by-one.
> Rgds,
> Paul Ibison SQL Server MVP, www.replicationanswers.com
> (recommended sql server 2000 replication book:
> http://www.nwsu.com/0974973602p.html)
>
|||Tom,
why are you using , @.force_reinit_subscription = 1?
Ordinarily this is left out and there is no invalidation
of the snapshot - the changes are propagated as a result
of the sp_repl... commands using hte existing replication
framework. If you want to snapshot the table then have a
look at the other method in the article where you drop
the subscription to the article then drop the article.
Rgds,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
nice that someone read it
If you're running these commands as a script, you'll need
a GO after each command, otherwise you can just run them
individually one-by-one.
Rgds,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
OK, that worked and I saw the queue reader agent process the commands and
the distribution agent's last action taken now reads "The initial snapshot
for article 'RequistionDetail' is not yet available'.
Now what do I need to do to generate a new snapshot of this one article in
order to get these changes to propogate over to the my subscriber?
Thanks-Tom
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:0f4b01c51511$e4181ce0$a401280a@.phx.gbl...
> Tom,
> nice that someone read it
> If you're running these commands as a script, you'll need
> a GO after each command, otherwise you can just run them
> individually one-by-one.
> Rgds,
> Paul Ibison SQL Server MVP, www.replicationanswers.com
> (recommended sql server 2000 replication book:
> http://www.nwsu.com/0974973602p.html)
>
|||Tom,
why are you using , @.force_reinit_subscription = 1?
Ordinarily this is left out and there is no invalidation
of the snapshot - the changes are propagated as a result
of the sp_repl... commands using hte existing replication
framework. If you want to snapshot the table then have a
look at the other method in the article where you drop
the subscription to the article then drop the article.
Rgds,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
Thursday, March 22, 2012
alter table set default value for money type column
I run the sql like the following
Alter table ItemStone add ISPurPrice money default 0
then when I select the itemStone table, I find the field ISPurPrice is still
Null, not 0, why?
I'm using SQL Server ver 8.0 (2000)
Thx!!
Kei,
Use the WITH VALUES option in your statement, or (probably better)
declare your new column as NOT NULL. Here are the choices:
Alter table ItemStone add ISPurPrice money NOT NULL default 0
Alter table ItemStone add ISPurPrice money default 0 WITH VALUES
From Books Online, topic ALTER TABLE:
WITH VALUES
Specifies that the value given in DEFAULT constant_expression is stored
in a new column added to existing rows. WITH VALUES can be specified
only when DEFAULT is specified in an ADD column clause. If the added
column allows null values and WITH VALUES is specified, the default
value is stored in the new column added to existing rows. If WITH VALUES
is not specified for columns that allow nulls, the value NULL is stored
in the new column in existing rows. If the new column does not allow
nulls, the default value is stored in new rows regardless of whether
WITH VALUES is specified.
Steve Kass
Drew University
kei wrote:
>I run the sql like the following
>Alter table ItemStone add ISPurPrice money default 0
>then when I select the itemStone table, I find the field ISPurPrice is still
>Null, not 0, why?
>I'm using SQL Server ver 8.0 (2000)
>Thx!!
>
Alter table ItemStone add ISPurPrice money default 0
then when I select the itemStone table, I find the field ISPurPrice is still
Null, not 0, why?
I'm using SQL Server ver 8.0 (2000)
Thx!!
Kei,
Use the WITH VALUES option in your statement, or (probably better)
declare your new column as NOT NULL. Here are the choices:
Alter table ItemStone add ISPurPrice money NOT NULL default 0
Alter table ItemStone add ISPurPrice money default 0 WITH VALUES
From Books Online, topic ALTER TABLE:
WITH VALUES
Specifies that the value given in DEFAULT constant_expression is stored
in a new column added to existing rows. WITH VALUES can be specified
only when DEFAULT is specified in an ADD column clause. If the added
column allows null values and WITH VALUES is specified, the default
value is stored in the new column added to existing rows. If WITH VALUES
is not specified for columns that allow nulls, the value NULL is stored
in the new column in existing rows. If the new column does not allow
nulls, the default value is stored in new rows regardless of whether
WITH VALUES is specified.
Steve Kass
Drew University
kei wrote:
>I run the sql like the following
>Alter table ItemStone add ISPurPrice money default 0
>then when I select the itemStone table, I find the field ISPurPrice is still
>Null, not 0, why?
>I'm using SQL Server ver 8.0 (2000)
>Thx!!
>
Tuesday, March 20, 2012
alter table nocheck constraint still some dependencies
Hi.
I'm getting errors like this when I try to run an upgrade script I'm trying to
write/test:
altering labels to length 60
Server: Msg 5074, Level 16, State 4, Line 5
The object 'ALTPART_ANNOT_ANNOTID_FK' is dependent on column 'label'.
I used this to bracket my script:
sp_msforeachtable "ALTER TABLE ? NOCHECK CONSTRAINT all"
go
sp_msforeachtable "ALTER TABLE ? DISABLE TRIGGER all"
go
/* updates here */
sp_msforeachtable @.command1="print '?'",
@.command2="ALTER TABLE ? CHECK CONSTRAINT all"
go
sp_msforeachtable @.command1="print '?'",
@.command2="ALTER TABLE ? ENABLE TRIGGER all"
go
I guess the alter table nocheck constraint isn't disabling the fk's
completely?
Is there a way around this, or do I manually have to do the constraint
dropping/recreating?
Thanks
Jeff KishOn Wed, 17 May 2006 10:25:25 -0400, Jeff Kish wrote:
>Hi.
>I'm getting errors like this when I try to run an upgrade script I'm trying to
>write/test:
>altering labels to length 60
>Server: Msg 5074, Level 16, State 4, Line 5
>The object 'ALTPART_ANNOT_ANNOTID_FK' is dependent on column 'label'.
>I used this to bracket my script:
>sp_msforeachtable "ALTER TABLE ? NOCHECK CONSTRAINT all"
>go
>sp_msforeachtable "ALTER TABLE ? DISABLE TRIGGER all"
>go
>/* updates here */
>
>sp_msforeachtable @.command1="print '?'",
>@.command2="ALTER TABLE ? CHECK CONSTRAINT all"
>go
>sp_msforeachtable @.command1="print '?'",
>@.command2="ALTER TABLE ? ENABLE TRIGGER all"
>go
>I guess the alter table nocheck constraint isn't disabling the fk's
>completely?
Hi Jeff,
ALTER TABLE xxx NOCHECK CONSTRAINT yyy is intended to (temporarily)
disable the checking of the constraint. The constraint is not removed
from the metadata. That means that you still can't perform any
modifications that would invalidate the constraint. (Coonsider what
would happpen if you change the datatype of a column on one end of a
FOREIGN KEY constraint but not on the other end and then try to
re-anable the constraint...)
>Is there a way around this, or do I manually have to do the constraint
>dropping/recreating?
If you google for it, you might be able to find scripts to generate the
code to drop and recreate constraints. I've never used any such code, so
I can't comment on the reliability.
--
Hugo Kornelis, SQL Server MVP|||Jeff Kish (jeff.kish@.mro.com) writes:
> I'm getting errors like this when I try to run an upgrade script I'm
> trying to write/test:
> altering labels to length 60
> Server: Msg 5074, Level 16, State 4, Line 5
> The object 'ALTPART_ANNOT_ANNOTID_FK' is dependent on column 'label'.
> I used this to bracket my script:
> sp_msforeachtable "ALTER TABLE ? NOCHECK CONSTRAINT all"
> go
> sp_msforeachtable "ALTER TABLE ? DISABLE TRIGGER all"
> go
Since you did not include the actual code that implements the change,
I will have to guess. My guess is that you change the length of a
PK column that is referenced by an FK.
If that is the case, you indeed have to drop the FK, as an FK must
always be of the same data type as the key it refers to. SQL Server
cannot know that you are altering both columns, so it only sees that
you are breaking the rule.
> sp_msforeachtable @.command1="print '?'",
> @.command2="ALTER TABLE ? CHECK CONSTRAINT all"
When you reenable constraints, you should use this quirky syntax:
@.command2="ALTER TABLE ? WITH CHEC CHECK CONSTRAINT all"
This forces SQL Server to re-check the constraints. While this take
much longer time, it also means that the optimizer can trust these
constraints and take them in regard when computing a query plan. In
some situations this can have drastic effects on the performance
of the application.
--
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|||<snip>
>Since you did not include the actual code that implements the change,
>I will have to guess. My guess is that you change the length of a
>PK column that is referenced by an FK.
>If that is the case, you indeed have to drop the FK, as an FK must
>always be of the same data type as the key it refers to. SQL Server
>cannot know that you are altering both columns, so it only sees that
>you are breaking the rule.
>> sp_msforeachtable @.command1="print '?'",
>> @.command2="ALTER TABLE ? CHECK CONSTRAINT all"
>When you reenable constraints, you should use this quirky syntax:
> @.command2="ALTER TABLE ? WITH CHEC CHECK CONSTRAINT all"
>This forces SQL Server to re-check the constraints. While this take
>much longer time, it also means that the optimizer can trust these
>constraints and take them in regard when computing a query plan. In
>some situations this can have drastic effects on the performance
>of the application.
Thanks to both of you, not only for the quick accurate explanation, but also
the reenable recommendation.
I guess I got kind of spoiled by Oracle (I hope that isn't a dirty word here),
but I was able to get things to work better by dropping then re-creating the
constraints.
Yes, I was changing the length of one of the columns in the primary key .
I took some of Erland's other advice I saw elsewhere, and decided not to rely
on any automated tools, and just sat down and grunted through manually
figuring out and implementing the scripts.
regards,
Jeff Kishsql
Alter table is greyed out
Hi,
I am trying to run the "script alter table to" but it is greyed out for any table in any database.
I am member of the builtint\administrator and sysadmin server role.
How can I fix this ?
CE
This is a normal behavior and Alter To /Execute To is not applicable to Table... it is only applicable to objects like sp/function/views
Madhu
|||I should have thought of that.
Thanks
CE
ALTER table command
Hi,
I'm trying to run the ALTER TABLE command using a dynamic string for the
table, like so:
DECLARE @.TableName CHAR
SET @.TableName = 'Customers'
ALTER TABLE @.TableName
ADD ...blah
Is this possible? We know this works:
ALTER TABLE Customers ADD ...blah
It looks like I need a way to convert the CHAR value to a literal or perhaps
even a table ID?
Thanks in advance,
PaulYou would need to use dynamic query...
e.g.
exec('alter table '+@.tb+' add blah')
--
-oj
RAC v2.2 & QALite!
http://www.rac4sql.net
"Paul Sampson" <psampson@.uecomm.com.au> wrote in message
news:1061964209.790366@.proxy.uecomm.net.au...
> Hi,
> I'm trying to run the ALTER TABLE command using a dynamic string for the
> table, like so:
> DECLARE @.TableName CHAR
> SET @.TableName = 'Customers'
> ALTER TABLE @.TableName
> ADD ...blah
> Is this possible? We know this works:
> ALTER TABLE Customers ADD ...blah
> It looks like I need a way to convert the CHAR value to a literal or
perhaps
> even a table ID?
> Thanks in advance,
> Paul|||"Paul Sampson" <psampson@.uecomm.com.au> wrote in message news:<1061964209.790366@.proxy.uecomm.net.au>...
> Hi,
> I'm trying to run the ALTER TABLE command using a dynamic string for the
> table, like so:
> DECLARE @.TableName CHAR
> SET @.TableName = 'Customers'
> ALTER TABLE @.TableName
> ADD ...blah
> Is this possible? We know this works:
> ALTER TABLE Customers ADD ...blah
> It looks like I need a way to convert the CHAR value to a literal or perhaps
> even a table ID?
> Thanks in advance,
> Paul
You can use dynamic SQL:
declare @.tablename sysname
set @.tablename = 'Customers'
exec('alter table dbo.' + @.tablename + ' add ...')
See here for more information on dynamic SQL:
http://www.algonet.se/~sommar/dynamic_sql.html
By the way, if you declare a variable as CHAR without a length, it
will default to CHAR(1). For object names, sysname is a better choice.
Simon|||Thanks Simon - a common suggestion and one that I'll be sure to remember.
"Simon Hayes" <sql@.hayes.ch> wrote in message
news:60cd0137.0308270337.2bbb79e4@.posting.google.c om...
> "Paul Sampson" <psampson@.uecomm.com.au> wrote in message
news:<1061964209.790366@.proxy.uecomm.net.au>...
> > Hi,
> > I'm trying to run the ALTER TABLE command using a dynamic string for the
> > table, like so:
> > DECLARE @.TableName CHAR
> > SET @.TableName = 'Customers'
> > ALTER TABLE @.TableName
> > ADD ...blah
> > Is this possible? We know this works:
> > ALTER TABLE Customers ADD ...blah
> > It looks like I need a way to convert the CHAR value to a literal or
perhaps
> > even a table ID?
> > Thanks in advance,
> > Paul
> You can use dynamic SQL:
> declare @.tablename sysname
> set @.tablename = 'Customers'
> exec('alter table dbo.' + @.tablename + ' add ...')
> See here for more information on dynamic SQL:
> http://www.algonet.se/~sommar/dynamic_sql.html
> By the way, if you declare a variable as CHAR without a length, it
> will default to CHAR(1). For object names, sysname is a better choice.
> Simon
Monday, March 19, 2012
ALTER TABLE ... IDENTITY question....
Hi,
I am trying to programatically change the seed of an existing IDENTITY
column (Copy_ID). When I run the following command I get the error:
Server: Msg 156, Level 15, State 1, Line 1
Incorrect syntax near the keyword 'IDENTITY'.
ALTER TABLE Copy ALTER COLUMN Copy_ID Int IDENTITY (1,1);
Where am I going wrong?
Thanks in advance,
StuCheck out DBCC CHECKIDENT in the BOL.
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
.
"Stu" <s.lock@.cergis.com> wrote in message
news:uGBp9R1RGHA.5500@.TK2MSFTNGP12.phx.gbl...
Hi,
I am trying to programatically change the seed of an existing IDENTITY
column (Copy_ID). When I run the following command I get the error:
Server: Msg 156, Level 15, State 1, Line 1
Incorrect syntax near the keyword 'IDENTITY'.
ALTER TABLE Copy ALTER COLUMN Copy_ID Int IDENTITY (1,1);
Where am I going wrong?
Thanks in advance,
Stu
I am trying to programatically change the seed of an existing IDENTITY
column (Copy_ID). When I run the following command I get the error:
Server: Msg 156, Level 15, State 1, Line 1
Incorrect syntax near the keyword 'IDENTITY'.
ALTER TABLE Copy ALTER COLUMN Copy_ID Int IDENTITY (1,1);
Where am I going wrong?
Thanks in advance,
StuCheck out DBCC CHECKIDENT in the BOL.
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
.
"Stu" <s.lock@.cergis.com> wrote in message
news:uGBp9R1RGHA.5500@.TK2MSFTNGP12.phx.gbl...
Hi,
I am trying to programatically change the seed of an existing IDENTITY
column (Copy_ID). When I run the following command I get the error:
Server: Msg 156, Level 15, State 1, Line 1
Incorrect syntax near the keyword 'IDENTITY'.
ALTER TABLE Copy ALTER COLUMN Copy_ID Int IDENTITY (1,1);
Where am I going wrong?
Thanks in advance,
Stu
Alter Table - Change Column Datatype
Hi,
I want to change the datatype of an existing column from char to
varbinary. When I run the "Alter Table" statement, I get the
following error message -
Disallowed implicit conversion from data type char to data type
varbinary, table 'test.dbo.testalter', column 'col1'. Use the CONVERT
function to run this query.
Can the CONVERT function be used as part of an alter table/alter
column? Is there another way besides renaming the table and creating
a new one?
Thanks,
BruceOn 19 Apr 2004 11:29:46 -0700, Bruce wrote:
>Hi,
>I want to change the datatype of an existing column from char to
>varbinary. When I run the "Alter Table" statement, I get the
>following error message -
>Disallowed implicit conversion from data type char to data type
>varbinary, table 'test.dbo.testalter', column 'col1'. Use the CONVERT
>function to run this query.
>Can the CONVERT function be used as part of an alter table/alter
>column? Is there another way besides renaming the table and creating
>a new one?
>Thanks,
>Bruce
Yes, there is another way: rename not the whole table, but just the
column, then create a new one:
EXEC sp_rename 'test.dbo.testalter.col1' 'col1old', COLUMN
go
ALTER TABLE test.dbo.testalter
ADD col1 varbinary(321) NULL
-- If it has to be NOT NULL, change this to read
-- ADD col1 varbinary(321) NOT NULL DEFAULT 0
go
UPDATE test.dbo.testalter
SET col1 = CAST(col1old AS varbinary(321))
go
ALTER TABLE test.dbo.testalter
DROP COLUMN col1old
go
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)
Sunday, March 11, 2012
Alter service <SVC1> (Add contract <CONTRACT>) does not really work, need help
Hi,
Sure you can run
The service contract bindings of the initiator service (SVC1) are irelevant. Is the target service (SVC2) that has to be bound to a specific contract. Try altering SVC2.|||Create Service SVC1 ON QUEUE QUEUE1 (CONTRACT1);
alter service SVC1 (add contract CONTRACT2);
But You can only send message using contract1, when you try to send msg using contract2, it always say can not find CONTRACT "contract2".
The following query shows the contract is there.
select s.*,c.* from sys.service_contract_usages U
inner join sys.services S on S.service_id = U.service_id
inner join sys.service_contracts C on U.service_contract_id=C.service_contract_id
where S.name='SVC1'
declare @.lMsg xml
declare @.ConversationHandle uniqueidentifier
set @.lMsg = '<test>testing</test>'
Begin Transaction
Begin Dialog @.ConversationHandle
From Service SVC1
To Service 'SVC2'
On Contract contract1
WITH Encryption=off;
SEND
ON CONVERSATION @.ConversationHandle
Message Type [type1]
Commit
The above works, but the following will not work.
declare @.lMsg xml
declare @.ConversationHandle uniqueidentifier
set @.lMsg = '<test>testing</test>'
Begin Transaction
Begin Dialog @.ConversationHandle
From Service SVC1
To Service 'SVC2'
On Contract CONTRACT2
WITH Encryption=off;
SEND
ON CONVERSATION @.ConversationHandle
Message Type [type2]
(@.lMsg)
Commit
Any idea ?
Thanks!
I mean everywhere SVC2. it is a typo when I posted the msg, my bad.
But still the alter service is still not working, any idea ?
|||Works fine for me:
Code Snippet
use tempdb
go
create message type mt1 validation = none;
create message type mt2 validation = none;
create contract sc1 (mt1 sent by any);
create contract sc2 (mt2 sent by any);
create queue q1;
create queue q2;
create service svc1 on queue q1;
create service svc2 on queue q2 ([sc1]);
alter service svc2 (add contract [sc2]);
go
declare @.h uniqueidentifier;
begin dialog conversation @.h
from service svc1
to service 'svc2', 'current database'
on contract [sc2]
with encryption = off;
send on conversation @.h message type [mt2];
waitfor (receive message_type_name, service_contract_name, * from q2);
go
message_type_name service_contract_name status priority queuing_order conversation_group_id conversation_handle message_sequence_number service_name service_id service_contract_name service_contract_id message_type_name message_type_id validation message_body
-- -- -- -- -- -- -- -- - -- - -
mt2 sc2 1 0 0 DDCD3F76-9C50-DC11-B57C-00188B111155 DECD3F76-9C50-DC11-B57C-00188B111155 0 svc2 65537 sc2 65537 mt2 65537 N NULL
(1 row(s) affected)
|||I am using distributed environment, It did not work when I tried.
I will re-test it, and update you.
|||It works, My bad, because I have different contract names, which caused the failure. Sorry for the late response.Alter service <SVC1> (Add contract <CONTRACT>) does not really work, need help
Hi,
Sure you can run
The service contract bindings of the initiator service (SVC1) are irelevant. Is the target service (SVC2) that has to be bound to a specific contract. Try altering SVC2.|||Create Service SVC1 ON QUEUE QUEUE1 (CONTRACT1);
alter service SVC1 (add contract CONTRACT2);
But You can only send message using contract1, when you try to send msg using contract2, it always say can not find CONTRACT "contract2".
The following query shows the contract is there.
select s.*,c.* from sys.service_contract_usages U
inner join sys.services S on S.service_id = U.service_id
inner join sys.service_contracts C on U.service_contract_id=C.service_contract_id
where S.name='SVC1'
declare @.lMsg xml
declare @.ConversationHandle uniqueidentifier
set @.lMsg = '<test>testing</test>'
Begin Transaction
Begin Dialog @.ConversationHandle
From Service SVC1
To Service 'SVC2'
On Contract contract1
WITH Encryption=off;
SEND
ON CONVERSATION @.ConversationHandle
Message Type [type1]
Commit
The above works, but the following will not work.
declare @.lMsg xml
declare @.ConversationHandle uniqueidentifier
set @.lMsg = '<test>testing</test>'
Begin Transaction
Begin Dialog @.ConversationHandle
From Service SVC1
To Service 'SVC2'
On Contract CONTRACT2
WITH Encryption=off;
SEND
ON CONVERSATION @.ConversationHandle
Message Type [type2]
(@.lMsg)
Commit
Any idea ?
Thanks!
I mean everywhere SVC2. it is a typo when I posted the msg, my bad.
But still the alter service is still not working, any idea ?
|||Works fine for me:
Code Snippet
use tempdb
go
create message type mt1 validation = none;
create message type mt2 validation = none;
create contract sc1 (mt1 sent by any);
create contract sc2 (mt2 sent by any);
create queue q1;
create queue q2;
create service svc1 on queue q1;
create service svc2 on queue q2 ([sc1]);
alter service svc2 (add contract [sc2]);
go
declare @.h uniqueidentifier;
begin dialog conversation @.h
from service svc1
to service 'svc2', 'current database'
on contract [sc2]
with encryption = off;
send on conversation @.h message type [mt2];
waitfor (receive message_type_name, service_contract_name, * from q2);
go
message_type_name service_contract_name status priority queuing_order conversation_group_id conversation_handle message_sequence_number service_name service_id service_contract_name service_contract_id message_type_name message_type_id validation message_body
-- -- -- -- -- -- -- -- - -- - -
mt2 sc2 1 0 0 DDCD3F76-9C50-DC11-B57C-00188B111155 DECD3F76-9C50-DC11-B57C-00188B111155 0 svc2 65537 sc2 65537 mt2 65537 N NULL
(1 row(s) affected)
|||I am using distributed environment, It did not work when I tried.
I will re-test it, and update you.
|||It works, My bad, because I have different contract names, which caused the failure. Sorry for the late response.Alter service <SVC1> (Add contract <CONTRACT>) does not really work, need help
Hi,
Sure you can run
The service contract bindings of the initiator service (SVC1) are irelevant. Is the target service (SVC2) that has to be bound to a specific contract. Try altering SVC2.|||Create Service SVC1 ON QUEUE QUEUE1 (CONTRACT1);
alter service SVC1 (add contract CONTRACT2);
But You can only send message using contract1, when you try to send msg using contract2, it always say can not find CONTRACT "contract2".
The following query shows the contract is there.
select s.*,c.* from sys.service_contract_usages U
inner join sys.services S on S.service_id = U.service_id
inner join sys.service_contracts C on U.service_contract_id=C.service_contract_id
where S.name='SVC1'
declare @.lMsg xml
declare @.ConversationHandle uniqueidentifier
set @.lMsg = '<test>testing</test>'
Begin Transaction
Begin Dialog @.ConversationHandle
From Service SVC1
To Service 'SVC2'
On Contract contract1
WITH Encryption=off;
SEND
ON CONVERSATION @.ConversationHandle
Message Type [type1]
Commit
The above works, but the following will not work.
declare @.lMsg xml
declare @.ConversationHandle uniqueidentifier
set @.lMsg = '<test>testing</test>'
Begin Transaction
Begin Dialog @.ConversationHandle
From Service SVC1
To Service 'SVC2'
On Contract CONTRACT2
WITH Encryption=off;
SEND
ON CONVERSATION @.ConversationHandle
Message Type [type2]
(@.lMsg)
Commit
Any idea ?
Thanks!
I mean everywhere SVC2. it is a typo when I posted the msg, my bad.
But still the alter service is still not working, any idea ?
|||Works fine for me:
Code Snippet
use tempdb
go
create message type mt1 validation = none;
create message type mt2 validation = none;
create contract sc1 (mt1 sent by any);
create contract sc2 (mt2 sent by any);
create queue q1;
create queue q2;
create service svc1 on queue q1;
create service svc2 on queue q2 ([sc1]);
alter service svc2 (add contract [sc2]);
go
declare @.h uniqueidentifier;
begin dialog conversation @.h
from service svc1
to service 'svc2', 'current database'
on contract [sc2]
with encryption = off;
send on conversation @.h message type [mt2];
waitfor (receive message_type_name, service_contract_name, * from q2);
go
message_type_name service_contract_name status priority queuing_order conversation_group_id conversation_handle message_sequence_number service_name service_id service_contract_name service_contract_id message_type_name message_type_id validation message_body
-- -- -- -- -- -- -- -- - -- - -
mt2 sc2 1 0 0 DDCD3F76-9C50-DC11-B57C-00188B111155 DECD3F76-9C50-DC11-B57C-00188B111155 0 svc2 65537 sc2 65537 mt2 65537 N NULL
(1 row(s) affected)
|||I am using distributed environment, It did not work when I tried.
I will re-test it, and update you.
|||It works, My bad, because I have different contract names, which caused the failure. Sorry for the late response.Wednesday, March 7, 2012
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
> > >
> > >
> >
> >
>
Subscribe to:
Posts (Atom)