Description:
Error: 9002, Severity: 17, State: 6
The log file for database 'tempdb' is full. Back up the
transaction log for the database to free up some log
space.
Rather than have to check the size of the tempdb.mdf every
day to make sure it's not consuming too much drive space,
we would like to find out if anyone has found an automated
way of backing up the tempdb to reduce the log size, or
maybe even schedule a restart of the SQL services in the
middle of the night to reset the log to its default size?
Any help would be greatly appreciated.If it's growing too large, it's because your application requires it.
Either make more room on the disk for tempdb, move tempdb to a different
drive, or fix the application so it doesn't require so much space.
http://www.aspfaq.com/2446
Of course you can schedule a job to restart SQL Server, etc. But this is a
really bad hack at best.
--
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/
"Animatrix1" <anonymous@.discussions.microsoft.com> wrote in message
news:4d2601c42c6b$7e9f2be0$a601280a@.phx.gbl...
> Description:
> Error: 9002, Severity: 17, State: 6
> The log file for database 'tempdb' is full. Back up the
> transaction log for the database to free up some log
> space.
> Rather than have to check the size of the tempdb.mdf every
> day to make sure it's not consuming too much drive space,
> we would like to find out if anyone has found an automated
> way of backing up the tempdb to reduce the log size, or
> maybe even schedule a restart of the SQL services in the
> middle of the night to reset the log to its default size?
> Any help would be greatly appreciated.|||We've also had tempdb spiral out of control. We are
using PeopleSoft as the front end application, and I can
tell you with great assurance, that there is no possible
way the application requires a 20 gig tempdb, to support
a 5 gig database.
We find the tempdb slowly grows over time. Sometimes
quickly, normally slowly. The only solution we came up
with, was the limit the size of tempdb, to something
large, but not all consuming.
We have about 15 installations of PeopleSoft, of varying
version levels with SQLServer. I've only seen two of
them suffer from this problem. So it's certainly not the
norm.
Fred...
>--Original Message--
>If it's growing too large, it's because your application
requires it.
>Either make more room on the disk for tempdb, move
tempdb to a different
>drive, or fix the application so it doesn't require so
much space.
>http://www.aspfaq.com/2446
>Of course you can schedule a job to restart SQL Server,
etc. But this is a
>really bad hack at best.
>--
>Aaron Bertrand
>SQL Server MVP
>http://www.aspfaq.com/
>
>
>
>"Animatrix1" <anonymous@.discussions.microsoft.com> wrote
in message
>news:4d2601c42c6b$7e9f2be0$a601280a@.phx.gbl...
>> Description:
>> Error: 9002, Severity: 17, State: 6
>> The log file for database 'tempdb' is full. Back up the
>> transaction log for the database to free up some log
>> space.
>> Rather than have to check the size of the tempdb.mdf
every
>> day to make sure it's not consuming too much drive
space,
>> we would like to find out if anyone has found an
automated
>> way of backing up the tempdb to reduce the log size, or
>> maybe even schedule a restart of the SQL services in
the
>> middle of the night to reset the log to its default
size?
>> Any help would be greatly appreciated.
>
>.
>
Showing posts with label transaction. Show all posts
Showing posts with label transaction. Show all posts
Monday, March 19, 2012
Sunday, March 11, 2012
Controlling a Transaction by User in SQL Server 2000
Hey Folks!
I have a typical requirement by my client. On submitting a Update (Bulk) button a huge database operation starts. A huge bulk update operation need to be performed. This would take 2-3 minutes some times. Client wants a cancel button in this case where he can be given a way to cancel the database Transaction.
Please let me know in case if there is a way out.
Thanks, in advance.
Regards,
Uday.D
Hi ,
Where u want to handle transactions ...
from vb.net / C# or in Store Procedure (in SQL Server 2000) itself ...i prefers in Store Procedure
following links may help you
http://www.codeproject.com/database/sqlservertransactions.asp
http://www.samspublishing.com/articles/article.asp?p=27225&rl=1
Thanks Amit for the same.
But, the links you had given does not give me the solution.
I am looking at the functionality something similar to the "Cancel executing Query Method" button in Query Anlayser in Sql Server 2005. If we run a huge query and click it when the query is processing the SQL execution engine can be stopped by clicking the button.
I hope this throws some more light.
control transaction in sybase database using sql-server 2000
Can anyone help me with this scenario!!!!
I have a sybase database and a sqlserver 2000 database.
I want to insert data into sybase database table thru sql-server 2000 using distributed queries
When i execute the following the transaction
Create procedure myCurrentDataBaseProcedure
as
begin
begin tran
insert into mytable values(1)
if @.@.error <>0
begin
rollback transaction
return
end
insert into sybasedatabaseserver.databasename.dbo.tablename values(1)
if @.@.error <>0
begin
rollback transaction
return
end
commit transaction
end
The procedure is created in sql server database
trying to execute this procedure..shows error
The first part of the procedure is executed.
But the error is here
insert into sybasedatabaseserver.databasename.dbo.tablename values(1)
The data is succesfully inserted in the local database
I am unable to insert data into the remote database
Can anyone suggest me wht shd i do in this scenario
Are there any drivers to be loaded to commit this transactions
Pl.Helpcan anyone help me regarding this topic|||what was the error?
go thru books on line (BOL) about
sp_addlinkedserver and sp_addlinkedsrvlogin.
Originally posted by RajiniKasturi
Can anyone help me with this scenario!!!!
I have a sybase database and a sqlserver 2000 database.
I want to insert data into sybase database table thru sql-server 2000 using distributed queries
When i execute the following the transaction
Create procedure myCurrentDataBaseProcedure
as
begin
begin tran
insert into mytable values(1)
if @.@.error <>0
begin
rollback transaction
return
end
insert into sybasedatabaseserver.databasename.dbo.tablename values(1)
if @.@.error <>0
begin
rollback transaction
return
end
commit transaction
end
The procedure is created in sql server database
trying to execute this procedure..shows error
The first part of the procedure is executed.
But the error is here
insert into sybasedatabaseserver.databasename.dbo.tablename values(1)
The data is succesfully inserted in the local database
I am unable to insert data into the remote database
Can anyone suggest me wht shd i do in this scenario
Are there any drivers to be loaded to commit this transactions
Pl.Help|||Thanks for the reply
See, actually I dont have any problem connecting to the remote nor executing the individual statement which i mentioned in the code.
But if i have all the sql statements as a single stored procedure
i am unable to control the transaction
That is I shd first insert some values into the local server database.table then the same data shd get inserted in the remote server. If both are correct then the transaction shd be committed else both shd rollback
when i execute the second statement
insert into sybasedatabaseserver.databasename.dbo.tablename values(1)
the error says remote server not found.
but if i execute the single statement it is working perfectly
Wht could be wrong in the transaction.|||I guess it should be BEGIN DISTRIBUTED TRANSACTION
instead of begin tran.
let me check it out.
Originally posted by RajiniKasturi
Thanks for the reply
See, actually I dont have any problem connecting to the remote nor executing the individual statement which i mentioned in the code.
But if i have all the sql statements as a single stored procedure
i am unable to control the transaction
That is I shd first insert some values into the local server database.table then the same data shd get inserted in the remote server. If both are correct then the transaction shd be committed else both shd rollback
when i execute the second statement
insert into sybasedatabaseserver.databasename.dbo.tablename values(1)
the error says remote server not found.
but if i execute the single statement it is working perfectly
Wht could be wrong in the transaction.|||Thanks again for the prompt reply
It is not working with that option too
Is there is anyway I can achieve this??
Can u pl.help me
I have a sybase database and a sqlserver 2000 database.
I want to insert data into sybase database table thru sql-server 2000 using distributed queries
When i execute the following the transaction
Create procedure myCurrentDataBaseProcedure
as
begin
begin tran
insert into mytable values(1)
if @.@.error <>0
begin
rollback transaction
return
end
insert into sybasedatabaseserver.databasename.dbo.tablename values(1)
if @.@.error <>0
begin
rollback transaction
return
end
commit transaction
end
The procedure is created in sql server database
trying to execute this procedure..shows error
The first part of the procedure is executed.
But the error is here
insert into sybasedatabaseserver.databasename.dbo.tablename values(1)
The data is succesfully inserted in the local database
I am unable to insert data into the remote database
Can anyone suggest me wht shd i do in this scenario
Are there any drivers to be loaded to commit this transactions
Pl.Helpcan anyone help me regarding this topic|||what was the error?
go thru books on line (BOL) about
sp_addlinkedserver and sp_addlinkedsrvlogin.
Originally posted by RajiniKasturi
Can anyone help me with this scenario!!!!
I have a sybase database and a sqlserver 2000 database.
I want to insert data into sybase database table thru sql-server 2000 using distributed queries
When i execute the following the transaction
Create procedure myCurrentDataBaseProcedure
as
begin
begin tran
insert into mytable values(1)
if @.@.error <>0
begin
rollback transaction
return
end
insert into sybasedatabaseserver.databasename.dbo.tablename values(1)
if @.@.error <>0
begin
rollback transaction
return
end
commit transaction
end
The procedure is created in sql server database
trying to execute this procedure..shows error
The first part of the procedure is executed.
But the error is here
insert into sybasedatabaseserver.databasename.dbo.tablename values(1)
The data is succesfully inserted in the local database
I am unable to insert data into the remote database
Can anyone suggest me wht shd i do in this scenario
Are there any drivers to be loaded to commit this transactions
Pl.Help|||Thanks for the reply
See, actually I dont have any problem connecting to the remote nor executing the individual statement which i mentioned in the code.
But if i have all the sql statements as a single stored procedure
i am unable to control the transaction
That is I shd first insert some values into the local server database.table then the same data shd get inserted in the remote server. If both are correct then the transaction shd be committed else both shd rollback
when i execute the second statement
insert into sybasedatabaseserver.databasename.dbo.tablename values(1)
the error says remote server not found.
but if i execute the single statement it is working perfectly
Wht could be wrong in the transaction.|||I guess it should be BEGIN DISTRIBUTED TRANSACTION
instead of begin tran.
let me check it out.
Originally posted by RajiniKasturi
Thanks for the reply
See, actually I dont have any problem connecting to the remote nor executing the individual statement which i mentioned in the code.
But if i have all the sql statements as a single stored procedure
i am unable to control the transaction
That is I shd first insert some values into the local server database.table then the same data shd get inserted in the remote server. If both are correct then the transaction shd be committed else both shd rollback
when i execute the second statement
insert into sybasedatabaseserver.databasename.dbo.tablename values(1)
the error says remote server not found.
but if i execute the single statement it is working perfectly
Wht could be wrong in the transaction.|||Thanks again for the prompt reply
It is not working with that option too
Is there is anyway I can achieve this??
Can u pl.help me
control transaction duration
Hi guys,
can I control the duration of a transaction ?
Using ADO I can set a command timeout, but using T-SQL or
modifying some SQLserver parameter, can I set a sort
of timeout on a transaction and get the same result (i.e. prevent
a transaction from running too long) ?
Many thanks for your kind help
Max
You can control max time you wait when you wait to be granted a lock. Check out SET LOCK_TIMEOUT.
However, you cannot set the max time you hold a transaction open or how long a query can run at the TSQL level
(ignoring the query governor), this has to be done in the client app (using ADO, ADO.NET of whatever API you
are using).
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"madmax" <madmax@.discussions.microsoft.com> wrote in message
news:52836935-399C-4C37-9240-E23ABCC9F7B4@.microsoft.com...
> Hi guys,
> can I control the duration of a transaction ?
> Using ADO I can set a command timeout, but using T-SQL or
> modifying some SQLserver parameter, can I set a sort
> of timeout on a transaction and get the same result (i.e. prevent
> a transaction from running too long) ?
> Many thanks for your kind help
> Max
can I control the duration of a transaction ?
Using ADO I can set a command timeout, but using T-SQL or
modifying some SQLserver parameter, can I set a sort
of timeout on a transaction and get the same result (i.e. prevent
a transaction from running too long) ?
Many thanks for your kind help
Max
You can control max time you wait when you wait to be granted a lock. Check out SET LOCK_TIMEOUT.
However, you cannot set the max time you hold a transaction open or how long a query can run at the TSQL level
(ignoring the query governor), this has to be done in the client app (using ADO, ADO.NET of whatever API you
are using).
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"madmax" <madmax@.discussions.microsoft.com> wrote in message
news:52836935-399C-4C37-9240-E23ABCC9F7B4@.microsoft.com...
> Hi guys,
> can I control the duration of a transaction ?
> Using ADO I can set a command timeout, but using T-SQL or
> modifying some SQLserver parameter, can I set a sort
> of timeout on a transaction and get the same result (i.e. prevent
> a transaction from running too long) ?
> Many thanks for your kind help
> Max
control transaction duration
Hi guys,
can I control the duration of a transaction ?
Using ADO I can set a command timeout, but using T-SQL or
modifying some SQLserver parameter, can I set a sort
of timeout on a transaction and get the same result (i.e. prevent
a transaction from running too long) ?
Many thanks for your kind help
MaxYou can control max time you wait when you wait to be granted a lock. Check out SET LOCK_TIMEOUT.
However, you cannot set the max time you hold a transaction open or how long a query can run at the TSQL level
(ignoring the query governor), this has to be done in the client app (using ADO, ADO.NET of whatever API you
are using).
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"madmax" <madmax@.discussions.microsoft.com> wrote in message
news:52836935-399C-4C37-9240-E23ABCC9F7B4@.microsoft.com...
> Hi guys,
> can I control the duration of a transaction ?
> Using ADO I can set a command timeout, but using T-SQL or
> modifying some SQLserver parameter, can I set a sort
> of timeout on a transaction and get the same result (i.e. prevent
> a transaction from running too long) ?
> Many thanks for your kind help
> Max
can I control the duration of a transaction ?
Using ADO I can set a command timeout, but using T-SQL or
modifying some SQLserver parameter, can I set a sort
of timeout on a transaction and get the same result (i.e. prevent
a transaction from running too long) ?
Many thanks for your kind help
MaxYou can control max time you wait when you wait to be granted a lock. Check out SET LOCK_TIMEOUT.
However, you cannot set the max time you hold a transaction open or how long a query can run at the TSQL level
(ignoring the query governor), this has to be done in the client app (using ADO, ADO.NET of whatever API you
are using).
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"madmax" <madmax@.discussions.microsoft.com> wrote in message
news:52836935-399C-4C37-9240-E23ABCC9F7B4@.microsoft.com...
> Hi guys,
> can I control the duration of a transaction ?
> Using ADO I can set a command timeout, but using T-SQL or
> modifying some SQLserver parameter, can I set a sort
> of timeout on a transaction and get the same result (i.e. prevent
> a transaction from running too long) ?
> Many thanks for your kind help
> Max
control transaction duration
Hi guys,
can I control the duration of a transaction ?
Using ADO I can set a command timeout, but using T-SQL or
modifying some SQLserver parameter, can I set a sort
of timeout on a transaction and get the same result (i.e. prevent
a transaction from running too long) ?
Many thanks for your kind help
MaxYou can control max time you wait when you wait to be granted a lock. Check
out SET LOCK_TIMEOUT.
However, you cannot set the max time you hold a transaction open or how long
a query can run at the TSQL level
(ignoring the query governor), this has to be done in the client app (using
ADO, ADO.NET of whatever API you
are using).
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"madmax" <madmax@.discussions.microsoft.com> wrote in message
news:52836935-399C-4C37-9240-E23ABCC9F7B4@.microsoft.com...
> Hi guys,
> can I control the duration of a transaction ?
> Using ADO I can set a command timeout, but using T-SQL or
> modifying some SQLserver parameter, can I set a sort
> of timeout on a transaction and get the same result (i.e. prevent
> a transaction from running too long) ?
> Many thanks for your kind help
> Max
can I control the duration of a transaction ?
Using ADO I can set a command timeout, but using T-SQL or
modifying some SQLserver parameter, can I set a sort
of timeout on a transaction and get the same result (i.e. prevent
a transaction from running too long) ?
Many thanks for your kind help
MaxYou can control max time you wait when you wait to be granted a lock. Check
out SET LOCK_TIMEOUT.
However, you cannot set the max time you hold a transaction open or how long
a query can run at the TSQL level
(ignoring the query governor), this has to be done in the client app (using
ADO, ADO.NET of whatever API you
are using).
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"madmax" <madmax@.discussions.microsoft.com> wrote in message
news:52836935-399C-4C37-9240-E23ABCC9F7B4@.microsoft.com...
> Hi guys,
> can I control the duration of a transaction ?
> Using ADO I can set a command timeout, but using T-SQL or
> modifying some SQLserver parameter, can I set a sort
> of timeout on a transaction and get the same result (i.e. prevent
> a transaction from running too long) ?
> Many thanks for your kind help
> Max
Wednesday, March 7, 2012
Continuing on error...
Is it possible to continue on with the newer replication operations even when
an error occurs ? I have set up transaction replication between two SQL
servers. When an error occurs, all the subsequent changes to the database are
not replicated, even if they might be error - free. Can this be done ?
Prakash.
Yes. See the page titled 'distribution agent utility' in SQL Server Books
Online. Distribution agent has a parameter caleld -SkipErrors, that can be
used for this purpose.
HTH,
Vyas, MVP (SQL Server)
http://vyaskn.tripod.com/
"Prakash" <prakash@.msn.com> wrote in message
news:85041798-9961-4091-A593-5E58B0EA6426@.microsoft.com...
Is it possible to continue on with the newer replication operations even
when
an error occurs ? I have set up transaction replication between two SQL
servers. When an error occurs, all the subsequent changes to the database
are
not replicated, even if they might be error - free. Can this be done ?
Prakash.
|||The correct page title is "Replication Distribution Agent Utility"
HTH,
Vyas, MVP (SQL Server)
http://vyaskn.tripod.com/
"Prakash" <prakash@.msn.com> wrote in message
news:85041798-9961-4091-A593-5E58B0EA6426@.microsoft.com...
Is it possible to continue on with the newer replication operations even
when
an error occurs ? I have set up transaction replication between two SQL
servers. When an error occurs, all the subsequent changes to the database
are
not replicated, even if they might be error - free. Can this be done ?
Prakash.
|||there is also the continue on data consistency errors, which you can access
by right clicking on your distirbution agent, selecting agent profiles, and
then selecting this profile.
After changing your profile you need to stop and start your distribution
agent.
Hilary Cotter
Looking for a book on SQL Server replication?
http://www.nwsu.com/0974973602.html
"Prakash" <prakash@.msn.com> wrote in message
news:85041798-9961-4091-A593-5E58B0EA6426@.microsoft.com...
> Is it possible to continue on with the newer replication operations even
when
> an error occurs ? I have set up transaction replication between two SQL
> servers. When an error occurs, all the subsequent changes to the database
are
> not replicated, even if they might be error - free. Can this be done ?
> Prakash.
an error occurs ? I have set up transaction replication between two SQL
servers. When an error occurs, all the subsequent changes to the database are
not replicated, even if they might be error - free. Can this be done ?
Prakash.
Yes. See the page titled 'distribution agent utility' in SQL Server Books
Online. Distribution agent has a parameter caleld -SkipErrors, that can be
used for this purpose.
HTH,
Vyas, MVP (SQL Server)
http://vyaskn.tripod.com/
"Prakash" <prakash@.msn.com> wrote in message
news:85041798-9961-4091-A593-5E58B0EA6426@.microsoft.com...
Is it possible to continue on with the newer replication operations even
when
an error occurs ? I have set up transaction replication between two SQL
servers. When an error occurs, all the subsequent changes to the database
are
not replicated, even if they might be error - free. Can this be done ?
Prakash.
|||The correct page title is "Replication Distribution Agent Utility"
HTH,
Vyas, MVP (SQL Server)
http://vyaskn.tripod.com/
"Prakash" <prakash@.msn.com> wrote in message
news:85041798-9961-4091-A593-5E58B0EA6426@.microsoft.com...
Is it possible to continue on with the newer replication operations even
when
an error occurs ? I have set up transaction replication between two SQL
servers. When an error occurs, all the subsequent changes to the database
are
not replicated, even if they might be error - free. Can this be done ?
Prakash.
|||there is also the continue on data consistency errors, which you can access
by right clicking on your distirbution agent, selecting agent profiles, and
then selecting this profile.
After changing your profile you need to stop and start your distribution
agent.
Hilary Cotter
Looking for a book on SQL Server replication?
http://www.nwsu.com/0974973602.html
"Prakash" <prakash@.msn.com> wrote in message
news:85041798-9961-4091-A593-5E58B0EA6426@.microsoft.com...
> Is it possible to continue on with the newer replication operations even
when
> an error occurs ? I have set up transaction replication between two SQL
> servers. When an error occurs, all the subsequent changes to the database
are
> not replicated, even if they might be error - free. Can this be done ?
> Prakash.
Context Connection Transaction
Hello Guys,
I need some some clarifications on how Context connections and transactions inter operate in CLR.
The context connection allows for ADO objects to be " running in the same transaction space". So the association to the current transaction is implied. So as long as I set for example my SqlCommand to use the context connection I am going to be running under the same transaction.
SqlConnection sqlConn = new SqlConnection("context connection=true");
SqlCommand sqlComm = new SqlCommand("EXEC myCommand", sqlConn);
I guess my ambiguity comes from the fact that the Transaction is not specifically specified.
In addition what happens upon a trigger that for example watches and insert on a table? If the insert occurs under a transaction, I would assume that I will be also picking up that transaction in the CRL Trigger, thus the whole operation would seem atomic.
Thank you,
Lubomir
Hi Lubomir!
I see that in your post you ask questions and give right answers to them yourself
Thank you,
Friday, February 10, 2012
Consolidating group names
Hello All,
I have some groups set up in a matrix that basically group by transaction names. I am trying to consolidate all but one into the same group. Right now I have the expression of...
Code Snippet
=iif(Fields!TestName.Value="Name Search","Name Search","Logon Function")
this consolidates the aggregate results and group fine but it does not label the groups as expected. "Name Search" comes up as "Name Search" but instead instead of "Logon Function", It displays the rolled up group as the name of the fist group field. Is there a way to make an alias inside of an expression?
Never mind. Answered my own question. Had to put the code into the field properties too.
Subscribe to:
Posts (Atom)