Help on sql 7.0 sp4 tempdb is full

My C: drive was getting low on disk space so I:
Changed the allotted space for tempdb to not grow any larger.
Reduced the memory space from 815 meg to 637 meg in sql server properties.
Rebooted the NT 4 server.
Now I am getting this error every minute. Also, in Enterprise Manager ,
database, shows: "no items".
Help!
[298] SQLServer Error: 9002, The log file for database 'tempdb' is full Back
up the transacton log for the database to free up some log space. [SQLSTATE
42000]
Thank you
Lancer 3Hi Lancer,
The first option I would try is to move the tempdb to another drive which
has sufficient space and allow DB to auto grow.
But if you want to keep it in the existing drive and want to limit the
growth of tempdb, it is a good idea to run the following on a scheduled
basis.... Do this during the non peak hours....
BACKUP LOG tempdb WITH TRUNCATE_ONLY
DBCC SHRINKDATABASE('tempdb',8)
Also go through this article
http://www.sql-server-performance.com/tempdb.asp
--
Thanks
Yogishsql

Help on sql 7.0 sp4 tempdb is full

My C: drive was getting low on disk space so I:
Changed the allotted space for tempdb to not grow any larger.
Reduced the memory space from 815 meg to 637 meg in sql server properties.
Rebooted the NT 4 server.
Now I am getting this error every minute. Also, in Enterprise Manager ,
database, shows: "no items".
Help!
[298] SQLServer Error: 9002, The log file for database 'tempdb' is full
Back
up the transacton log for the database to free up some log space. [SQLST
ATE
42000]
Thank you
Lancer 3Hi Lancer,
The first option I would try is to move the tempdb to another drive which
has sufficient space and allow DB to auto grow.
But if you want to keep it in the existing drive and want to limit the
growth of tempdb, it is a good idea to run the following on a scheduled
basis.... Do this during the non peak hours....
BACKUP LOG tempdb WITH TRUNCATE_ONLY
DBCC SHRINKDATABASE('tempdb',8)
Also go through this article
http://www.sql-server-performance.com/tempdb.asp
Thanks
Yogish

Help on sql 7.0 sp4 tempdb is full

My C: drive was getting low on disk space so I:
Changed the allotted space for tempdb to not grow any larger.
Reduced the memory space from 815 meg to 637 meg in sql server properties.
Rebooted the NT 4 server.
Now I am getting this error every minute. Also, in Enterprise Manager ,
database, shows: "no items".
Help!
[298] SQLServer Error: 9002, The log file for database 'tempdb' is full Back
up the transacton log for the database to free up some log space. [SQLSTATE
42000]
Thank you
Lancer 3
Hi Lancer,
The first option I would try is to move the tempdb to another drive which
has sufficient space and allow DB to auto grow.
But if you want to keep it in the existing drive and want to limit the
growth of tempdb, it is a good idea to run the following on a scheduled
basis.... Do this during the non peak hours....
BACKUP LOG tempdb WITH TRUNCATE_ONLY
DBCC SHRINKDATABASE('tempdb',8)
Also go through this article
http://www.sql-server-performance.com/tempdb.asp
Thanks
Yogish

Help Needed - "," as decimal Point instead of "."

Hi All,
If we change the System settings to Swedish(Finland), the decimal space is a
Comma "," instead of a Dot "." Now the problem is SQL Server does not
recognize this and gives error. We can not do collation setting for numeric
fields also.
Any help on the same will be highly appreciated.
Regards
Rajib
hi Rajib,
"Rajib Chatterjee" <rajibc@.hotmail.com> ha scritto nel messaggio
news:ev$SNjTXEHA.1144@.TK2MSFTNGP10.phx.gbl...
> Hi All,
> If we change the System settings to Swedish(Finland), the decimal space is
a
> Comma "," instead of a Dot "." Now the problem is SQL Server does not
> recognize this and gives error. We can not do collation setting for
numeric
> fields also.
SQL Server alwayse recognize decimal separator as "." (dot)... provide that
all your expressions are in this format
SET NOCOUNT ON
DECLARE @.n NUMERIC (12 , 5)
SELECT @.n = 1234567.12345
SELECT @.n AS [Number]
, CONVERT(NUMERIC (12 , 5), '1234567.12345') [Cast]
, CASE WHEN @.n = CONVERT(NUMERIC (12 , 5), '1234567.12345') THEN '=' ELSE
'#' END AS [Equal]
--<--
Number Cast Equal
-- -- --
1234567.12345 1234567.12345 =
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
DbaMgr2k ver 0.8.0 - DbaMgr ver 0.54.0
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
-- remove DMO to reply
|||hi Rajib,
"Rajib Chatterjee" <rajibc@.hotmail.com> ha scritto nel messaggio
news:ev$SNjTXEHA.1144@.TK2MSFTNGP10.phx.gbl...
> Hi All,
> If we change the System settings to Swedish(Finland), the decimal space is
a
> Comma "," instead of a Dot "." Now the problem is SQL Server does not
> recognize this and gives error. We can not do collation setting for
numeric
> fields also.
SQL Server alwayse recognize decimal separator as "." (dot)... provide that
all your expressions are in this format
SET NOCOUNT ON
DECLARE @.n NUMERIC (12 , 5)
SELECT @.n = 1234567.12345
SELECT @.n AS [Number]
, CONVERT(NUMERIC (12 , 5), '1234567.12345') [Cast]
, CASE WHEN @.n = CONVERT(NUMERIC (12 , 5), '1234567.12345') THEN '=' ELSE
'#' END AS [Equal]
--<--
Number Cast Equal
-- -- --
1234567.12345 1234567.12345 =
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
DbaMgr2k ver 0.8.0 - DbaMgr ver 0.54.0
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
-- remove DMO to reply
|||Hi Andrea,
Many Thanks!!
Can you please elaborate it further... We have an application front end
which is passing the data to Stored Procedure in the back end. Front end
changes the decimal places to COMMA automatically and try sending the same
data to back-end and SP fails to update the DB .
The problem is not only with COMMA but with anything apart from DOT "." ,
what SQL Server rejects. What would be the most effective way to develop of
a full proved system.
Regards
Rajib
"Andrea Montanari" <andrea.sqlDMO@.virgilio.it> wrote in message
news:2kbdd5Fc8tmU1@.uni-berlin.de...[vbcol=seagreen]
> hi Rajib,
> "Rajib Chatterjee" <rajibc@.hotmail.com> ha scritto nel messaggio
> news:ev$SNjTXEHA.1144@.TK2MSFTNGP10.phx.gbl...
is
> a
> numeric
> SQL Server alwayse recognize decimal separator as "." (dot)... provide
that
> all your expressions are in this format
> SET NOCOUNT ON
> DECLARE @.n NUMERIC (12 , 5)
> SELECT @.n = 1234567.12345
> SELECT @.n AS [Number]
> , CONVERT(NUMERIC (12 , 5), '1234567.12345') [Cast]
> , CASE WHEN @.n = CONVERT(NUMERIC (12 , 5), '1234567.12345') THEN '=' ELSE
> '#' END AS [Equal]
> --<--
> Number Cast Equal
> -- -- --
> 1234567.12345 1234567.12345 =
> --
> Andrea Montanari (Microsoft MVP - SQL Server)
> http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
> DbaMgr2k ver 0.8.0 - DbaMgr ver 0.54.0
> (my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
> interface)
> -- remove DMO to reply
>
|||Hi Andrea,
Many Thanks!!
Can you please elaborate it further... We have an application front end
which is passing the data to Stored Procedure in the back end. Front end
changes the decimal places to COMMA automatically and try sending the same
data to back-end and SP fails to update the DB .
The problem is not only with COMMA but with anything apart from DOT "." ,
what SQL Server rejects. What would be the most effective way to develop of
a full proved system.
Regards
Rajib
"Andrea Montanari" <andrea.sqlDMO@.virgilio.it> wrote in message
news:2kbdd5Fc8tmU1@.uni-berlin.de...[vbcol=seagreen]
> hi Rajib,
> "Rajib Chatterjee" <rajibc@.hotmail.com> ha scritto nel messaggio
> news:ev$SNjTXEHA.1144@.TK2MSFTNGP10.phx.gbl...
is
> a
> numeric
> SQL Server alwayse recognize decimal separator as "." (dot)... provide
that
> all your expressions are in this format
> SET NOCOUNT ON
> DECLARE @.n NUMERIC (12 , 5)
> SELECT @.n = 1234567.12345
> SELECT @.n AS [Number]
> , CONVERT(NUMERIC (12 , 5), '1234567.12345') [Cast]
> , CASE WHEN @.n = CONVERT(NUMERIC (12 , 5), '1234567.12345') THEN '=' ELSE
> '#' END AS [Equal]
> --<--
> Number Cast Equal
> -- -- --
> 1234567.12345 1234567.12345 =
> --
> Andrea Montanari (Microsoft MVP - SQL Server)
> http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
> DbaMgr2k ver 0.8.0 - DbaMgr ver 0.54.0
> (my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
> interface)
> -- remove DMO to reply
>
|||hi Rajib,
"Rajib Chatterjee" <rajibc@.hotmail.com> ha scritto nel messaggio
news:emEq3nZXEHA.3284@.TK2MSFTNGP12.phx.gbl...
> Hi Andrea,
> Many Thanks!!
> Can you please elaborate it further...
answered privately...
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
DbaMgr2k ver 0.8.0 - DbaMgr ver 0.54.0
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
-- remove DMO to reply
|||hi Rajib,
"Rajib Chatterjee" <rajibc@.hotmail.com> ha scritto nel messaggio
news:emEq3nZXEHA.3284@.TK2MSFTNGP12.phx.gbl...
> Hi Andrea,
> Many Thanks!!
> Can you please elaborate it further...
answered privately...
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
DbaMgr2k ver 0.8.0 - DbaMgr ver 0.54.0
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
-- remove DMO to reply
|||Thanks a lot!!
"Andrea Montanari" <andrea.sqlDMO@.virgilio.it> wrote in message
news:2kdf5eF10epbU1@.uni-berlin.de...
> hi Rajib,
> "Rajib Chatterjee" <rajibc@.hotmail.com> ha scritto nel messaggio
> news:emEq3nZXEHA.3284@.TK2MSFTNGP12.phx.gbl...
> answered privately...
> --
> Andrea Montanari (Microsoft MVP - SQL Server)
> http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
> DbaMgr2k ver 0.8.0 - DbaMgr ver 0.54.0
> (my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
> interface)
> -- remove DMO to reply
>

Help Needed

Greetings,

I have sql server 2000 sp3 on windows 2000 server. On one
of the database we have with 40 GB data. It have more than
100 GB of free space in the filegroup. I tried to shrink
that file using DBCC SHRINKFILE('FILENAME') and it gave
me following error

Server: Msg 3140, Level 16, State 3, Line 1
Could not adjust the space allocation for
file 'dbfile_Dat'.
DBCC execution completed. If DBCC printed error messages,
contact your system administrator.

here the space is adequate

Can anyone help me with this?
Any help would be appreciated

TIA(pankaj_wolfhunter@.yahoo.co.in) writes:
> I have sql server 2000 sp3 on windows 2000 server. On one
> of the database we have with 40 GB data. It have more than
> 100 GB of free space in the filegroup. I tried to shrink
> that file using DBCC SHRINKFILE('FILENAME') and it gave
> me following error
> Server: Msg 3140, Level 16, State 3, Line 1
> Could not adjust the space allocation for
> file 'dbfile_Dat'.
> DBCC execution completed. If DBCC printed error messages,
> contact your system administrator.

If I understand this correctly, the database file is 140 GB, of which 100 GB
is unused, and you want to shrink the file to 40 GB?

Did you try specifying a target size:

DBCC SHRINKFILE('dbfile_Dat', 42000)

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