I changed 'lightweight pooling' to 0, as you suggested, and after restarting
the server (just to make sure...) everything was back to normal !
Thank you very much for your time and invaluable help.
PS: by checking ERRORLOG I found out that the server did some database
recovering after the restart which immediately finished successfully, in
contrast to previous times when I was continously getting the error "[393]
Waiting for SQL Server to recover databases...". I don't know if that has to
do with the 'lightweight pooling' option change.
"Sue Hoegemeier" wrote:
> If you tried stopping and then restarting just the Agent
> service and it won't start, reporting the same error then my
> guess would be that you are running SQL Server in
> lightweight pooling mode. Set it back to the default of
> thread mode.
> sp_configure 'allow updates', 1
> go
> reconfigure with override
> go
> sp_configure 'lightweight pooling', 0
> go
> reconfigure with override
> go
> -Sue
Yes it was from the lightweight pooling change. You rarely
would change that setting - and as you saw changing default
settings can sometimes have adverse affects. The issues you
saw were all symptoms of problems from having lightweight
pooling on.
-Sue
On Tue, 21 Feb 2006 01:12:28 -0800, "vsiat"
<vsiat@.discussions.microsoft.com> wrote:
[vbcol=seagreen]
>I changed 'lightweight pooling' to 0, as you suggested, and after restarting
>the server (just to make sure...) everything was back to normal !
>Thank you very much for your time and invaluable help.
>PS: by checking ERRORLOG I found out that the server did some database
>recovering after the restart which immediately finished successfully, in
>contrast to previous times when I was continously getting the error "[393]
>Waiting for SQL Server to recover databases...". I don't know if that has to
>do with the 'lightweight pooling' option change.
>
>"Sue Hoegemeier" wrote:
Showing posts with label back. Show all posts
Showing posts with label back. Show all posts
Wednesday, March 21, 2012
Wednesday, March 7, 2012
Error 0: Timeout Expired HYT00 when trying to backup DBs
I am getting this error when I try to back up the larger DB's on my
server. The smaller ones back up ok. Is there a parameter I can change
to increase this timeout? I have gone into ODBC, Query Analyzer, and
SQL server properties and played around with some settings, but nothing
seems to work.
Any help is appreciated!
server. The smaller ones back up ok. Is there a parameter I can change
to increase this timeout? I have gone into ODBC, Query Analyzer, and
SQL server properties and played around with some settings, but nothing
seems to work.
Any help is appreciated!
Phil(Iam74gibson@.houston.rr.com) writes:
> I am getting this error when I try to back up the larger DB's on my
> server. The smaller ones back up ok. Is there a parameter I can change
> to increase this timeout? I have gone into ODBC, Query Analyzer, and
> SQL server properties and played around with some settings, but nothing
> seems to work.
> Any help is appreciated!
So from where do you run this backup?
The timeout is a client-side thing, and different clients have different
timeouts. Many run with 30 seconds which is the default for ODBC,
Enterprise Manager and many others. In Query Analyzer there is no
timeout by default. Neither is there any default timeout for Agent jobs.
--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp
Sunday, February 19, 2012
Error - log file for tempdb is full
Hi,
I'm using SQL Server 2000.
I often get this message on my server saying "the log file for database
'tempdb' is full. Back up the transaction log for the database to free up
some log space..". But it does not allow me to backup and truncate the
transaction log file for the tempdb, maunally from the Enterpirse manager.
The only way is to re-start the server which often is not a preferred way
becuase there are more than 300 users connected to the server and its
difficult and bad for the buisness.
Also, sometime ago I tried the following DBCC command:
DBCC SHRINKFILE(tempdb_log, 1)
But unfortunately after that whenever I re-start the server, the Autogrow
option for the tempdb is only "1 KB", which is very very low in our company's
buisness, which is a real time scenario with people working 24/7 and doing
heavy transactions.
So I have to remember alwyas , to manually change the autogrow option to 10%
or more.
Can someone please help. Its being a nuisance now specially with me only as
the database support person in the company and with me on leave sometimes
causes problems.
Thanks,
Somi
Hi,
Have a look into the below URL:-
http://www.aspfaq.com/show.asp?id=2446
Thanks
Hari
SQL Server MVP
"Somi" <Somi@.discussions.microsoft.com> wrote in message
news:94F948DC-0A8C-40B4-A125-FFE8FB0E25A4@.microsoft.com...
> Hi,
> I'm using SQL Server 2000.
> I often get this message on my server saying "the log file for database
> 'tempdb' is full. Back up the transaction log for the database to free up
> some log space..". But it does not allow me to backup and truncate the
> transaction log file for the tempdb, maunally from the Enterpirse manager.
> The only way is to re-start the server which often is not a preferred way
> becuase there are more than 300 users connected to the server and its
> difficult and bad for the buisness.
> Also, sometime ago I tried the following DBCC command:
> DBCC SHRINKFILE(tempdb_log, 1)
> But unfortunately after that whenever I re-start the server, the Autogrow
> option for the tempdb is only "1 KB", which is very very low in our
> company's
> buisness, which is a real time scenario with people working 24/7 and doing
> heavy transactions.
> So I have to remember alwyas , to manually change the autogrow option to
> 10%
> or more.
> Can someone please help. Its being a nuisance now specially with me only
> as
> the database support person in the company and with me on leave sometimes
> causes problems.
> Thanks,
> Somi
>
|||Hi,
try out this and schedule it in a job it will not effect ur ERP
database.
backup log tempdb with truncate_only
dbcc shrinkfile(templog,1)
select * from sysfiles
u can also create a stored procedure and schedule it that will check
the logfile size and if it reached to a certain size u can execute the
code(shrink) the shrinkfile will free the space on logfile but it will
not shrink the space on disk.
u have to backup the logfirst and then execute shrink file then it will
free the space on disk.
hope this help u
from
Doller
Hari Prasad wrote:[vbcol=seagreen]
> Hi,
> Have a look into the below URL:-
> http://www.aspfaq.com/show.asp?id=2446
> Thanks
> Hari
> SQL Server MVP
> "Somi" <Somi@.discussions.microsoft.com> wrote in message
> news:94F948DC-0A8C-40B4-A125-FFE8FB0E25A4@.microsoft.com...
|||Thanks Hari and Doller,
I am going through all the options provided by you both.
But I still have a doubt in my mind,
when I use "dbcc shrinkfile(templog,1)" command, will it restrict the auto
grow option of the tempdb log file to only 1 MB ? and this definitely is not
what i want in our scenario of heavy and continuous transactions & constant
use of #temp tables, cursors etc.
As I mentioned in my main issue that I've already tried this option and that
seems to have cause this problem that now the auto grow option for the tempdb
sits at 1 MB only, unitl i manually change it everytime the server re-starts.
Thanks,
Somi.
"doller" wrote:
> Hi,
> try out this and schedule it in a job it will not effect ur ERP
> database.
> backup log tempdb with truncate_only
> dbcc shrinkfile(templog,1)
> select * from sysfiles
> u can also create a stored procedure and schedule it that will check
> the logfile size and if it reached to a certain size u can execute the
> code(shrink) the shrinkfile will free the space on logfile but it will
> not shrink the space on disk.
> u have to backup the logfirst and then execute shrink file then it will
> free the space on disk.
> hope this help u
> from
> Doller
>
> Hari Prasad wrote:
>
|||1. Set the autogrow option on Model to 10%, tempdb is created from model.
2. Ensure there is enough disk space for the tempdb log - it will always
truncate automatically, you must be sure there is enough space for the log
on the hard drive... If not, add a second log on another drive - or move the
log to another drive..
that should take care of you...
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Somi" <Somi@.discussions.microsoft.com> wrote in message
news:94F948DC-0A8C-40B4-A125-FFE8FB0E25A4@.microsoft.com...
> Hi,
> I'm using SQL Server 2000.
> I often get this message on my server saying "the log file for database
> 'tempdb' is full. Back up the transaction log for the database to free up
> some log space..". But it does not allow me to backup and truncate the
> transaction log file for the tempdb, maunally from the Enterpirse manager.
> The only way is to re-start the server which often is not a preferred way
> becuase there are more than 300 users connected to the server and its
> difficult and bad for the buisness.
> Also, sometime ago I tried the following DBCC command:
> DBCC SHRINKFILE(tempdb_log, 1)
> But unfortunately after that whenever I re-start the server, the Autogrow
> option for the tempdb is only "1 KB", which is very very low in our
> company's
> buisness, which is a real time scenario with people working 24/7 and doing
> heavy transactions.
> So I have to remember alwyas , to manually change the autogrow option to
> 10%
> or more.
> Can someone please help. Its being a nuisance now specially with me only
> as
> the database support person in the company and with me on leave sometimes
> causes problems.
> Thanks,
> Somi
>
I'm using SQL Server 2000.
I often get this message on my server saying "the log file for database
'tempdb' is full. Back up the transaction log for the database to free up
some log space..". But it does not allow me to backup and truncate the
transaction log file for the tempdb, maunally from the Enterpirse manager.
The only way is to re-start the server which often is not a preferred way
becuase there are more than 300 users connected to the server and its
difficult and bad for the buisness.
Also, sometime ago I tried the following DBCC command:
DBCC SHRINKFILE(tempdb_log, 1)
But unfortunately after that whenever I re-start the server, the Autogrow
option for the tempdb is only "1 KB", which is very very low in our company's
buisness, which is a real time scenario with people working 24/7 and doing
heavy transactions.
So I have to remember alwyas , to manually change the autogrow option to 10%
or more.
Can someone please help. Its being a nuisance now specially with me only as
the database support person in the company and with me on leave sometimes
causes problems.
Thanks,
Somi
Hi,
Have a look into the below URL:-
http://www.aspfaq.com/show.asp?id=2446
Thanks
Hari
SQL Server MVP
"Somi" <Somi@.discussions.microsoft.com> wrote in message
news:94F948DC-0A8C-40B4-A125-FFE8FB0E25A4@.microsoft.com...
> Hi,
> I'm using SQL Server 2000.
> I often get this message on my server saying "the log file for database
> 'tempdb' is full. Back up the transaction log for the database to free up
> some log space..". But it does not allow me to backup and truncate the
> transaction log file for the tempdb, maunally from the Enterpirse manager.
> The only way is to re-start the server which often is not a preferred way
> becuase there are more than 300 users connected to the server and its
> difficult and bad for the buisness.
> Also, sometime ago I tried the following DBCC command:
> DBCC SHRINKFILE(tempdb_log, 1)
> But unfortunately after that whenever I re-start the server, the Autogrow
> option for the tempdb is only "1 KB", which is very very low in our
> company's
> buisness, which is a real time scenario with people working 24/7 and doing
> heavy transactions.
> So I have to remember alwyas , to manually change the autogrow option to
> 10%
> or more.
> Can someone please help. Its being a nuisance now specially with me only
> as
> the database support person in the company and with me on leave sometimes
> causes problems.
> Thanks,
> Somi
>
|||Hi,
try out this and schedule it in a job it will not effect ur ERP
database.
backup log tempdb with truncate_only
dbcc shrinkfile(templog,1)
select * from sysfiles
u can also create a stored procedure and schedule it that will check
the logfile size and if it reached to a certain size u can execute the
code(shrink) the shrinkfile will free the space on logfile but it will
not shrink the space on disk.
u have to backup the logfirst and then execute shrink file then it will
free the space on disk.
hope this help u
from
Doller
Hari Prasad wrote:[vbcol=seagreen]
> Hi,
> Have a look into the below URL:-
> http://www.aspfaq.com/show.asp?id=2446
> Thanks
> Hari
> SQL Server MVP
> "Somi" <Somi@.discussions.microsoft.com> wrote in message
> news:94F948DC-0A8C-40B4-A125-FFE8FB0E25A4@.microsoft.com...
|||Thanks Hari and Doller,
I am going through all the options provided by you both.
But I still have a doubt in my mind,
when I use "dbcc shrinkfile(templog,1)" command, will it restrict the auto
grow option of the tempdb log file to only 1 MB ? and this definitely is not
what i want in our scenario of heavy and continuous transactions & constant
use of #temp tables, cursors etc.
As I mentioned in my main issue that I've already tried this option and that
seems to have cause this problem that now the auto grow option for the tempdb
sits at 1 MB only, unitl i manually change it everytime the server re-starts.
Thanks,
Somi.
"doller" wrote:
> Hi,
> try out this and schedule it in a job it will not effect ur ERP
> database.
> backup log tempdb with truncate_only
> dbcc shrinkfile(templog,1)
> select * from sysfiles
> u can also create a stored procedure and schedule it that will check
> the logfile size and if it reached to a certain size u can execute the
> code(shrink) the shrinkfile will free the space on logfile but it will
> not shrink the space on disk.
> u have to backup the logfirst and then execute shrink file then it will
> free the space on disk.
> hope this help u
> from
> Doller
>
> Hari Prasad wrote:
>
|||1. Set the autogrow option on Model to 10%, tempdb is created from model.
2. Ensure there is enough disk space for the tempdb log - it will always
truncate automatically, you must be sure there is enough space for the log
on the hard drive... If not, add a second log on another drive - or move the
log to another drive..
that should take care of you...
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Somi" <Somi@.discussions.microsoft.com> wrote in message
news:94F948DC-0A8C-40B4-A125-FFE8FB0E25A4@.microsoft.com...
> Hi,
> I'm using SQL Server 2000.
> I often get this message on my server saying "the log file for database
> 'tempdb' is full. Back up the transaction log for the database to free up
> some log space..". But it does not allow me to backup and truncate the
> transaction log file for the tempdb, maunally from the Enterpirse manager.
> The only way is to re-start the server which often is not a preferred way
> becuase there are more than 300 users connected to the server and its
> difficult and bad for the buisness.
> Also, sometime ago I tried the following DBCC command:
> DBCC SHRINKFILE(tempdb_log, 1)
> But unfortunately after that whenever I re-start the server, the Autogrow
> option for the tempdb is only "1 KB", which is very very low in our
> company's
> buisness, which is a real time scenario with people working 24/7 and doing
> heavy transactions.
> So I have to remember alwyas , to manually change the autogrow option to
> 10%
> or more.
> Can someone please help. Its being a nuisance now specially with me only
> as
> the database support person in the company and with me on leave sometimes
> causes problems.
> Thanks,
> Somi
>
Error - log file for tempdb is full
Hi,
I'm using SQL Server 2000.
I often get this message on my server saying "the log file for database
'tempdb' is full. Back up the transaction log for the database to free up
some log space..". But it does not allow me to backup and truncate the
transaction log file for the tempdb, maunally from the Enterpirse manager.
The only way is to re-start the server which often is not a preferred way
becuase there are more than 300 users connected to the server and its
difficult and bad for the buisness.
Also, sometime ago I tried the following DBCC command:
DBCC SHRINKFILE(tempdb_log, 1)
But unfortunately after that whenever I re-start the server, the Autogrow
option for the tempdb is only "1 KB", which is very very low in our company's
buisness, which is a real time scenario with people working 24/7 and doing
heavy transactions.
So I have to remember alwyas , to manually change the autogrow option to 10%
or more.
Can someone please help. Its being a nuisance now specially with me only as
the database support person in the company and with me on leave sometimes
causes problems.
Thanks,
SomiHi,
Have a look into the below URL:-
http://www.aspfaq.com/show.asp?id=2446
Thanks
Hari
SQL Server MVP
"Somi" <Somi@.discussions.microsoft.com> wrote in message
news:94F948DC-0A8C-40B4-A125-FFE8FB0E25A4@.microsoft.com...
> Hi,
> I'm using SQL Server 2000.
> I often get this message on my server saying "the log file for database
> 'tempdb' is full. Back up the transaction log for the database to free up
> some log space..". But it does not allow me to backup and truncate the
> transaction log file for the tempdb, maunally from the Enterpirse manager.
> The only way is to re-start the server which often is not a preferred way
> becuase there are more than 300 users connected to the server and its
> difficult and bad for the buisness.
> Also, sometime ago I tried the following DBCC command:
> DBCC SHRINKFILE(tempdb_log, 1)
> But unfortunately after that whenever I re-start the server, the Autogrow
> option for the tempdb is only "1 KB", which is very very low in our
> company's
> buisness, which is a real time scenario with people working 24/7 and doing
> heavy transactions.
> So I have to remember alwyas , to manually change the autogrow option to
> 10%
> or more.
> Can someone please help. Its being a nuisance now specially with me only
> as
> the database support person in the company and with me on leave sometimes
> causes problems.
> Thanks,
> Somi
>|||Hi,
try out this and schedule it in a job it will not effect ur ERP
database.
backup log tempdb with truncate_only
dbcc shrinkfile(templog,1)
select * from sysfiles
u can also create a stored procedure and schedule it that will check
the logfile size and if it reached to a certain size u can execute the
code(shrink) the shrinkfile will free the space on logfile but it will
not shrink the space on disk.
u have to backup the logfirst and then execute shrink file then it will
free the space on disk.
hope this help u
from
Doller
Hari Prasad wrote:
> Hi,
> Have a look into the below URL:-
> http://www.aspfaq.com/show.asp?id=2446
> Thanks
> Hari
> SQL Server MVP
> "Somi" <Somi@.discussions.microsoft.com> wrote in message
> news:94F948DC-0A8C-40B4-A125-FFE8FB0E25A4@.microsoft.com...
> > Hi,
> > I'm using SQL Server 2000.
> > I often get this message on my server saying "the log file for database
> > 'tempdb' is full. Back up the transaction log for the database to free up
> > some log space..". But it does not allow me to backup and truncate the
> > transaction log file for the tempdb, maunally from the Enterpirse manager.
> > The only way is to re-start the server which often is not a preferred way
> > becuase there are more than 300 users connected to the server and its
> > difficult and bad for the buisness.
> >
> > Also, sometime ago I tried the following DBCC command:
> > DBCC SHRINKFILE(tempdb_log, 1)
> >
> > But unfortunately after that whenever I re-start the server, the Autogrow
> > option for the tempdb is only "1 KB", which is very very low in our
> > company's
> > buisness, which is a real time scenario with people working 24/7 and doing
> > heavy transactions.
> >
> > So I have to remember alwyas , to manually change the autogrow option to
> > 10%
> > or more.
> >
> > Can someone please help. Its being a nuisance now specially with me only
> > as
> > the database support person in the company and with me on leave sometimes
> > causes problems.
> >
> > Thanks,
> > Somi
> >|||Thanks Hari and Doller,
I am going through all the options provided by you both.
But I still have a doubt in my mind,
when I use "dbcc shrinkfile(templog,1)" command, will it restrict the auto
grow option of the tempdb log file to only 1 MB ' and this definitely is not
what i want in our scenario of heavy and continuous transactions & constant
use of #temp tables, cursors etc.
As I mentioned in my main issue that I've already tried this option and that
seems to have cause this problem that now the auto grow option for the tempdb
sits at 1 MB only, unitl i manually change it everytime the server re-starts.
Thanks,
Somi.
"doller" wrote:
> Hi,
> try out this and schedule it in a job it will not effect ur ERP
> database.
> backup log tempdb with truncate_only
> dbcc shrinkfile(templog,1)
> select * from sysfiles
> u can also create a stored procedure and schedule it that will check
> the logfile size and if it reached to a certain size u can execute the
> code(shrink) the shrinkfile will free the space on logfile but it will
> not shrink the space on disk.
> u have to backup the logfirst and then execute shrink file then it will
> free the space on disk.
> hope this help u
> from
> Doller
>
> Hari Prasad wrote:
> > Hi,
> >
> > Have a look into the below URL:-
> >
> > http://www.aspfaq.com/show.asp?id=2446
> >
> > Thanks
> > Hari
> > SQL Server MVP
> >
> > "Somi" <Somi@.discussions.microsoft.com> wrote in message
> > news:94F948DC-0A8C-40B4-A125-FFE8FB0E25A4@.microsoft.com...
> > > Hi,
> > > I'm using SQL Server 2000.
> > > I often get this message on my server saying "the log file for database
> > > 'tempdb' is full. Back up the transaction log for the database to free up
> > > some log space..". But it does not allow me to backup and truncate the
> > > transaction log file for the tempdb, maunally from the Enterpirse manager.
> > > The only way is to re-start the server which often is not a preferred way
> > > becuase there are more than 300 users connected to the server and its
> > > difficult and bad for the buisness.
> > >
> > > Also, sometime ago I tried the following DBCC command:
> > > DBCC SHRINKFILE(tempdb_log, 1)
> > >
> > > But unfortunately after that whenever I re-start the server, the Autogrow
> > > option for the tempdb is only "1 KB", which is very very low in our
> > > company's
> > > buisness, which is a real time scenario with people working 24/7 and doing
> > > heavy transactions.
> > >
> > > So I have to remember alwyas , to manually change the autogrow option to
> > > 10%
> > > or more.
> > >
> > > Can someone please help. Its being a nuisance now specially with me only
> > > as
> > > the database support person in the company and with me on leave sometimes
> > > causes problems.
> > >
> > > Thanks,
> > > Somi
> > >
>|||1. Set the autogrow option on Model to 10%, tempdb is created from model.
2. Ensure there is enough disk space for the tempdb log - it will always
truncate automatically, you must be sure there is enough space for the log
on the hard drive... If not, add a second log on another drive - or move the
log to another drive..
that should take care of you...
--
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Somi" <Somi@.discussions.microsoft.com> wrote in message
news:94F948DC-0A8C-40B4-A125-FFE8FB0E25A4@.microsoft.com...
> Hi,
> I'm using SQL Server 2000.
> I often get this message on my server saying "the log file for database
> 'tempdb' is full. Back up the transaction log for the database to free up
> some log space..". But it does not allow me to backup and truncate the
> transaction log file for the tempdb, maunally from the Enterpirse manager.
> The only way is to re-start the server which often is not a preferred way
> becuase there are more than 300 users connected to the server and its
> difficult and bad for the buisness.
> Also, sometime ago I tried the following DBCC command:
> DBCC SHRINKFILE(tempdb_log, 1)
> But unfortunately after that whenever I re-start the server, the Autogrow
> option for the tempdb is only "1 KB", which is very very low in our
> company's
> buisness, which is a real time scenario with people working 24/7 and doing
> heavy transactions.
> So I have to remember alwyas , to manually change the autogrow option to
> 10%
> or more.
> Can someone please help. Its being a nuisance now specially with me only
> as
> the database support person in the company and with me on leave sometimes
> causes problems.
> Thanks,
> Somi
>
I'm using SQL Server 2000.
I often get this message on my server saying "the log file for database
'tempdb' is full. Back up the transaction log for the database to free up
some log space..". But it does not allow me to backup and truncate the
transaction log file for the tempdb, maunally from the Enterpirse manager.
The only way is to re-start the server which often is not a preferred way
becuase there are more than 300 users connected to the server and its
difficult and bad for the buisness.
Also, sometime ago I tried the following DBCC command:
DBCC SHRINKFILE(tempdb_log, 1)
But unfortunately after that whenever I re-start the server, the Autogrow
option for the tempdb is only "1 KB", which is very very low in our company's
buisness, which is a real time scenario with people working 24/7 and doing
heavy transactions.
So I have to remember alwyas , to manually change the autogrow option to 10%
or more.
Can someone please help. Its being a nuisance now specially with me only as
the database support person in the company and with me on leave sometimes
causes problems.
Thanks,
SomiHi,
Have a look into the below URL:-
http://www.aspfaq.com/show.asp?id=2446
Thanks
Hari
SQL Server MVP
"Somi" <Somi@.discussions.microsoft.com> wrote in message
news:94F948DC-0A8C-40B4-A125-FFE8FB0E25A4@.microsoft.com...
> Hi,
> I'm using SQL Server 2000.
> I often get this message on my server saying "the log file for database
> 'tempdb' is full. Back up the transaction log for the database to free up
> some log space..". But it does not allow me to backup and truncate the
> transaction log file for the tempdb, maunally from the Enterpirse manager.
> The only way is to re-start the server which often is not a preferred way
> becuase there are more than 300 users connected to the server and its
> difficult and bad for the buisness.
> Also, sometime ago I tried the following DBCC command:
> DBCC SHRINKFILE(tempdb_log, 1)
> But unfortunately after that whenever I re-start the server, the Autogrow
> option for the tempdb is only "1 KB", which is very very low in our
> company's
> buisness, which is a real time scenario with people working 24/7 and doing
> heavy transactions.
> So I have to remember alwyas , to manually change the autogrow option to
> 10%
> or more.
> Can someone please help. Its being a nuisance now specially with me only
> as
> the database support person in the company and with me on leave sometimes
> causes problems.
> Thanks,
> Somi
>|||Hi,
try out this and schedule it in a job it will not effect ur ERP
database.
backup log tempdb with truncate_only
dbcc shrinkfile(templog,1)
select * from sysfiles
u can also create a stored procedure and schedule it that will check
the logfile size and if it reached to a certain size u can execute the
code(shrink) the shrinkfile will free the space on logfile but it will
not shrink the space on disk.
u have to backup the logfirst and then execute shrink file then it will
free the space on disk.
hope this help u
from
Doller
Hari Prasad wrote:
> Hi,
> Have a look into the below URL:-
> http://www.aspfaq.com/show.asp?id=2446
> Thanks
> Hari
> SQL Server MVP
> "Somi" <Somi@.discussions.microsoft.com> wrote in message
> news:94F948DC-0A8C-40B4-A125-FFE8FB0E25A4@.microsoft.com...
> > Hi,
> > I'm using SQL Server 2000.
> > I often get this message on my server saying "the log file for database
> > 'tempdb' is full. Back up the transaction log for the database to free up
> > some log space..". But it does not allow me to backup and truncate the
> > transaction log file for the tempdb, maunally from the Enterpirse manager.
> > The only way is to re-start the server which often is not a preferred way
> > becuase there are more than 300 users connected to the server and its
> > difficult and bad for the buisness.
> >
> > Also, sometime ago I tried the following DBCC command:
> > DBCC SHRINKFILE(tempdb_log, 1)
> >
> > But unfortunately after that whenever I re-start the server, the Autogrow
> > option for the tempdb is only "1 KB", which is very very low in our
> > company's
> > buisness, which is a real time scenario with people working 24/7 and doing
> > heavy transactions.
> >
> > So I have to remember alwyas , to manually change the autogrow option to
> > 10%
> > or more.
> >
> > Can someone please help. Its being a nuisance now specially with me only
> > as
> > the database support person in the company and with me on leave sometimes
> > causes problems.
> >
> > Thanks,
> > Somi
> >|||Thanks Hari and Doller,
I am going through all the options provided by you both.
But I still have a doubt in my mind,
when I use "dbcc shrinkfile(templog,1)" command, will it restrict the auto
grow option of the tempdb log file to only 1 MB ' and this definitely is not
what i want in our scenario of heavy and continuous transactions & constant
use of #temp tables, cursors etc.
As I mentioned in my main issue that I've already tried this option and that
seems to have cause this problem that now the auto grow option for the tempdb
sits at 1 MB only, unitl i manually change it everytime the server re-starts.
Thanks,
Somi.
"doller" wrote:
> Hi,
> try out this and schedule it in a job it will not effect ur ERP
> database.
> backup log tempdb with truncate_only
> dbcc shrinkfile(templog,1)
> select * from sysfiles
> u can also create a stored procedure and schedule it that will check
> the logfile size and if it reached to a certain size u can execute the
> code(shrink) the shrinkfile will free the space on logfile but it will
> not shrink the space on disk.
> u have to backup the logfirst and then execute shrink file then it will
> free the space on disk.
> hope this help u
> from
> Doller
>
> Hari Prasad wrote:
> > Hi,
> >
> > Have a look into the below URL:-
> >
> > http://www.aspfaq.com/show.asp?id=2446
> >
> > Thanks
> > Hari
> > SQL Server MVP
> >
> > "Somi" <Somi@.discussions.microsoft.com> wrote in message
> > news:94F948DC-0A8C-40B4-A125-FFE8FB0E25A4@.microsoft.com...
> > > Hi,
> > > I'm using SQL Server 2000.
> > > I often get this message on my server saying "the log file for database
> > > 'tempdb' is full. Back up the transaction log for the database to free up
> > > some log space..". But it does not allow me to backup and truncate the
> > > transaction log file for the tempdb, maunally from the Enterpirse manager.
> > > The only way is to re-start the server which often is not a preferred way
> > > becuase there are more than 300 users connected to the server and its
> > > difficult and bad for the buisness.
> > >
> > > Also, sometime ago I tried the following DBCC command:
> > > DBCC SHRINKFILE(tempdb_log, 1)
> > >
> > > But unfortunately after that whenever I re-start the server, the Autogrow
> > > option for the tempdb is only "1 KB", which is very very low in our
> > > company's
> > > buisness, which is a real time scenario with people working 24/7 and doing
> > > heavy transactions.
> > >
> > > So I have to remember alwyas , to manually change the autogrow option to
> > > 10%
> > > or more.
> > >
> > > Can someone please help. Its being a nuisance now specially with me only
> > > as
> > > the database support person in the company and with me on leave sometimes
> > > causes problems.
> > >
> > > Thanks,
> > > Somi
> > >
>|||1. Set the autogrow option on Model to 10%, tempdb is created from model.
2. Ensure there is enough disk space for the tempdb log - it will always
truncate automatically, you must be sure there is enough space for the log
on the hard drive... If not, add a second log on another drive - or move the
log to another drive..
that should take care of you...
--
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Somi" <Somi@.discussions.microsoft.com> wrote in message
news:94F948DC-0A8C-40B4-A125-FFE8FB0E25A4@.microsoft.com...
> Hi,
> I'm using SQL Server 2000.
> I often get this message on my server saying "the log file for database
> 'tempdb' is full. Back up the transaction log for the database to free up
> some log space..". But it does not allow me to backup and truncate the
> transaction log file for the tempdb, maunally from the Enterpirse manager.
> The only way is to re-start the server which often is not a preferred way
> becuase there are more than 300 users connected to the server and its
> difficult and bad for the buisness.
> Also, sometime ago I tried the following DBCC command:
> DBCC SHRINKFILE(tempdb_log, 1)
> But unfortunately after that whenever I re-start the server, the Autogrow
> option for the tempdb is only "1 KB", which is very very low in our
> company's
> buisness, which is a real time scenario with people working 24/7 and doing
> heavy transactions.
> So I have to remember alwyas , to manually change the autogrow option to
> 10%
> or more.
> Can someone please help. Its being a nuisance now specially with me only
> as
> the database support person in the company and with me on leave sometimes
> causes problems.
> Thanks,
> Somi
>
Error - log file for tempdb is full
Hi,
I'm using SQL Server 2000.
I often get this message on my server saying "the log file for database
'tempdb' is full. Back up the transaction log for the database to free up
some log space..". But it does not allow me to backup and truncate the
transaction log file for the tempdb, maunally from the Enterpirse manager.
The only way is to re-start the server which often is not a preferred way
becuase there are more than 300 users connected to the server and its
difficult and bad for the buisness.
Also, sometime ago I tried the following DBCC command:
DBCC SHRINKFILE(tempdb_log, 1)
But unfortunately after that whenever I re-start the server, the Autogrow
option for the tempdb is only "1 KB", which is very very low in our company'
s
buisness, which is a real time scenario with people working 24/7 and doing
heavy transactions.
So I have to remember alwyas , to manually change the autogrow option to 10%
or more.
Can someone please help. Its being a nuisance now specially with me only as
the database support person in the company and with me on leave sometimes
causes problems.
Thanks,
SomiHi,
Have a look into the below URL:-
http://www.aspfaq.com/show.asp?id=2446
Thanks
Hari
SQL Server MVP
"Somi" <Somi@.discussions.microsoft.com> wrote in message
news:94F948DC-0A8C-40B4-A125-FFE8FB0E25A4@.microsoft.com...
> Hi,
> I'm using SQL Server 2000.
> I often get this message on my server saying "the log file for database
> 'tempdb' is full. Back up the transaction log for the database to free up
> some log space..". But it does not allow me to backup and truncate the
> transaction log file for the tempdb, maunally from the Enterpirse manager.
> The only way is to re-start the server which often is not a preferred way
> becuase there are more than 300 users connected to the server and its
> difficult and bad for the buisness.
> Also, sometime ago I tried the following DBCC command:
> DBCC SHRINKFILE(tempdb_log, 1)
> But unfortunately after that whenever I re-start the server, the Autogrow
> option for the tempdb is only "1 KB", which is very very low in our
> company's
> buisness, which is a real time scenario with people working 24/7 and doing
> heavy transactions.
> So I have to remember alwyas , to manually change the autogrow option to
> 10%
> or more.
> Can someone please help. Its being a nuisance now specially with me only
> as
> the database support person in the company and with me on leave sometimes
> causes problems.
> Thanks,
> Somi
>|||Hi,
try out this and schedule it in a job it will not effect ur ERP
database.
backup log tempdb with truncate_only
dbcc shrinkfile(templog,1)
select * from sysfiles
u can also create a stored procedure and schedule it that will check
the logfile size and if it reached to a certain size u can execute the
code(shrink) the shrinkfile will free the space on logfile but it will
not shrink the space on disk.
u have to backup the logfirst and then execute shrink file then it will
free the space on disk.
hope this help u
from
Doller
Hari Prasad wrote:[vbcol=seagreen]
> Hi,
> Have a look into the below URL:-
> http://www.aspfaq.com/show.asp?id=2446
> Thanks
> Hari
> SQL Server MVP
> "Somi" <Somi@.discussions.microsoft.com> wrote in message
> news:94F948DC-0A8C-40B4-A125-FFE8FB0E25A4@.microsoft.com...|||Thanks Hari and Doller,
I am going through all the options provided by you both.
But I still have a doubt in my mind,
when I use "dbcc shrinkfile(templog,1)" command, will it restrict the auto
grow option of the tempdb log file to only 1 MB ' and this definitely is no
t
what i want in our scenario of heavy and continuous transactions & constant
use of #temp tables, cursors etc.
As I mentioned in my main issue that I've already tried this option and that
seems to have cause this problem that now the auto grow option for the tempd
b
sits at 1 MB only, unitl i manually change it everytime the server re-starts
.
Thanks,
Somi.
"doller" wrote:
> Hi,
> try out this and schedule it in a job it will not effect ur ERP
> database.
> backup log tempdb with truncate_only
> dbcc shrinkfile(templog,1)
> select * from sysfiles
> u can also create a stored procedure and schedule it that will check
> the logfile size and if it reached to a certain size u can execute the
> code(shrink) the shrinkfile will free the space on logfile but it will
> not shrink the space on disk.
> u have to backup the logfirst and then execute shrink file then it will
> free the space on disk.
> hope this help u
> from
> Doller
>
> Hari Prasad wrote:
>|||1. Set the autogrow option on Model to 10%, tempdb is created from model.
2. Ensure there is enough disk space for the tempdb log - it will always
truncate automatically, you must be sure there is enough space for the log
on the hard drive... If not, add a second log on another drive - or move the
log to another drive..
that should take care of you...
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Somi" <Somi@.discussions.microsoft.com> wrote in message
news:94F948DC-0A8C-40B4-A125-FFE8FB0E25A4@.microsoft.com...
> Hi,
> I'm using SQL Server 2000.
> I often get this message on my server saying "the log file for database
> 'tempdb' is full. Back up the transaction log for the database to free up
> some log space..". But it does not allow me to backup and truncate the
> transaction log file for the tempdb, maunally from the Enterpirse manager.
> The only way is to re-start the server which often is not a preferred way
> becuase there are more than 300 users connected to the server and its
> difficult and bad for the buisness.
> Also, sometime ago I tried the following DBCC command:
> DBCC SHRINKFILE(tempdb_log, 1)
> But unfortunately after that whenever I re-start the server, the Autogrow
> option for the tempdb is only "1 KB", which is very very low in our
> company's
> buisness, which is a real time scenario with people working 24/7 and doing
> heavy transactions.
> So I have to remember alwyas , to manually change the autogrow option to
> 10%
> or more.
> Can someone please help. Its being a nuisance now specially with me only
> as
> the database support person in the company and with me on leave sometimes
> causes problems.
> Thanks,
> Somi
>
I'm using SQL Server 2000.
I often get this message on my server saying "the log file for database
'tempdb' is full. Back up the transaction log for the database to free up
some log space..". But it does not allow me to backup and truncate the
transaction log file for the tempdb, maunally from the Enterpirse manager.
The only way is to re-start the server which often is not a preferred way
becuase there are more than 300 users connected to the server and its
difficult and bad for the buisness.
Also, sometime ago I tried the following DBCC command:
DBCC SHRINKFILE(tempdb_log, 1)
But unfortunately after that whenever I re-start the server, the Autogrow
option for the tempdb is only "1 KB", which is very very low in our company'
s
buisness, which is a real time scenario with people working 24/7 and doing
heavy transactions.
So I have to remember alwyas , to manually change the autogrow option to 10%
or more.
Can someone please help. Its being a nuisance now specially with me only as
the database support person in the company and with me on leave sometimes
causes problems.
Thanks,
SomiHi,
Have a look into the below URL:-
http://www.aspfaq.com/show.asp?id=2446
Thanks
Hari
SQL Server MVP
"Somi" <Somi@.discussions.microsoft.com> wrote in message
news:94F948DC-0A8C-40B4-A125-FFE8FB0E25A4@.microsoft.com...
> Hi,
> I'm using SQL Server 2000.
> I often get this message on my server saying "the log file for database
> 'tempdb' is full. Back up the transaction log for the database to free up
> some log space..". But it does not allow me to backup and truncate the
> transaction log file for the tempdb, maunally from the Enterpirse manager.
> The only way is to re-start the server which often is not a preferred way
> becuase there are more than 300 users connected to the server and its
> difficult and bad for the buisness.
> Also, sometime ago I tried the following DBCC command:
> DBCC SHRINKFILE(tempdb_log, 1)
> But unfortunately after that whenever I re-start the server, the Autogrow
> option for the tempdb is only "1 KB", which is very very low in our
> company's
> buisness, which is a real time scenario with people working 24/7 and doing
> heavy transactions.
> So I have to remember alwyas , to manually change the autogrow option to
> 10%
> or more.
> Can someone please help. Its being a nuisance now specially with me only
> as
> the database support person in the company and with me on leave sometimes
> causes problems.
> Thanks,
> Somi
>|||Hi,
try out this and schedule it in a job it will not effect ur ERP
database.
backup log tempdb with truncate_only
dbcc shrinkfile(templog,1)
select * from sysfiles
u can also create a stored procedure and schedule it that will check
the logfile size and if it reached to a certain size u can execute the
code(shrink) the shrinkfile will free the space on logfile but it will
not shrink the space on disk.
u have to backup the logfirst and then execute shrink file then it will
free the space on disk.
hope this help u
from
Doller
Hari Prasad wrote:[vbcol=seagreen]
> Hi,
> Have a look into the below URL:-
> http://www.aspfaq.com/show.asp?id=2446
> Thanks
> Hari
> SQL Server MVP
> "Somi" <Somi@.discussions.microsoft.com> wrote in message
> news:94F948DC-0A8C-40B4-A125-FFE8FB0E25A4@.microsoft.com...|||Thanks Hari and Doller,
I am going through all the options provided by you both.
But I still have a doubt in my mind,
when I use "dbcc shrinkfile(templog,1)" command, will it restrict the auto
grow option of the tempdb log file to only 1 MB ' and this definitely is no
t
what i want in our scenario of heavy and continuous transactions & constant
use of #temp tables, cursors etc.
As I mentioned in my main issue that I've already tried this option and that
seems to have cause this problem that now the auto grow option for the tempd
b
sits at 1 MB only, unitl i manually change it everytime the server re-starts
.
Thanks,
Somi.
"doller" wrote:
> Hi,
> try out this and schedule it in a job it will not effect ur ERP
> database.
> backup log tempdb with truncate_only
> dbcc shrinkfile(templog,1)
> select * from sysfiles
> u can also create a stored procedure and schedule it that will check
> the logfile size and if it reached to a certain size u can execute the
> code(shrink) the shrinkfile will free the space on logfile but it will
> not shrink the space on disk.
> u have to backup the logfirst and then execute shrink file then it will
> free the space on disk.
> hope this help u
> from
> Doller
>
> Hari Prasad wrote:
>|||1. Set the autogrow option on Model to 10%, tempdb is created from model.
2. Ensure there is enough disk space for the tempdb log - it will always
truncate automatically, you must be sure there is enough space for the log
on the hard drive... If not, add a second log on another drive - or move the
log to another drive..
that should take care of you...
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Somi" <Somi@.discussions.microsoft.com> wrote in message
news:94F948DC-0A8C-40B4-A125-FFE8FB0E25A4@.microsoft.com...
> Hi,
> I'm using SQL Server 2000.
> I often get this message on my server saying "the log file for database
> 'tempdb' is full. Back up the transaction log for the database to free up
> some log space..". But it does not allow me to backup and truncate the
> transaction log file for the tempdb, maunally from the Enterpirse manager.
> The only way is to re-start the server which often is not a preferred way
> becuase there are more than 300 users connected to the server and its
> difficult and bad for the buisness.
> Also, sometime ago I tried the following DBCC command:
> DBCC SHRINKFILE(tempdb_log, 1)
> But unfortunately after that whenever I re-start the server, the Autogrow
> option for the tempdb is only "1 KB", which is very very low in our
> company's
> buisness, which is a real time scenario with people working 24/7 and doing
> heavy transactions.
> So I have to remember alwyas , to manually change the autogrow option to
> 10%
> or more.
> Can someone please help. Its being a nuisance now specially with me only
> as
> the database support person in the company and with me on leave sometimes
> causes problems.
> Thanks,
> Somi
>
Error - Attempted to read or write protected memory
Hi,
I am back with one more problem..
I have created few reports using SSRS 2005. I am using Oracle database in Data Source to fetch my data. It is working fine and showing me report correctly. But after running the report 8 to 10 times, it starts giving me Memory error. To get rid of that, I need to recycle (stop-start) ReportingService from IIS.
I am exactly getting following error...
Attempted to read or write protected memory. This is often an indication that other memory is corrupt.
I am not getting the actual problem, why is it giving memory error only after running few times? Please let me know if anyone facing same problem or knowing the solution for the problem.
Thanks,
HMaheta
Dear HMaheta,
Check the DTC (Distributed Transaction Coordinator) service. It should be set up to to start automatically.
HTH,
Suprotim Agarwal
|||
Hi Suprotim,
Thanks for your reply.
I checked for the DTC. It is running on the server and also marked for auto start up.
Is there any other thing, which I am missing?
Thanks,
HMaheta
Subscribe to:
Posts (Atom)