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 for Recovring database

I wanted to move my database log file to another drive, but unfortunately I
executed wrong command .
alter database ABC modify file
(name = 'ABC_log',filename= 'D:\Program Files\Microsoft SQL
Server\MSSQL\data\ABC_log.LDF')
instead of
alter database ABC2 modify file
(name = 'ABC2_log',filename= 'D:\Program Files\Microsoft SQL
Server\MSSQL\data\ABC2_log.LDF')
After this, I found my database in Suspect mode, so I executed these commands
sp_configure 'allow updates', 1
GO
RECONFIGURE WITH OVERRIDE
GO
EXEC sp_resetstatus 'ABC2'
And restarted DB services, but database was still in suspect mode, after
checking every possibility, I detached that database, I don’t know why I did
this.
But now I am not able to attach database back. When I try doing this it’s
give me following error
Error 5173: Cannot associate files with different databases.
By the way when I attach this mdf file, it changes the logical file name to
ABC_Data.mdf
But when I execute following query, it displays different logical file name
with ABC.mdf
dbcc checkprimaryfile (N'D:\Program Files\Microsoft SQL
Server\MSSQL\Data\ABC2.mdf', 3)
I believe if I change this logical file name I’ll be able to attach database
back.
Kindly help me the recovering database.
Note: I do not have the transaction log backup & current DB backup.
Farhan Iqbal
Error 5173 means, you are trying to attach files that belong to another
database. Check the file names that you are using for your attach.
"Farhan" <Farhan@.discussions.microsoft.com> wrote in message
news:89333AE6-EE72-4863-A73B-C9419112318C@.microsoft.com...
>I wanted to move my database log file to another drive, but unfortunately I
> executed wrong command .
> alter database ABC modify file
> (name = 'ABC_log',filename= 'D:\Program Files\Microsoft SQL
> Server\MSSQL\data\ABC_log.LDF')
> instead of
> alter database ABC2 modify file
> (name = 'ABC2_log',filename= 'D:\Program Files\Microsoft SQL
> Server\MSSQL\data\ABC2_log.LDF')
>
> After this, I found my database in Suspect mode, so I executed these
> commands
> sp_configure 'allow updates', 1
> GO
> RECONFIGURE WITH OVERRIDE
> GO
> EXEC sp_resetstatus 'ABC2'
> And restarted DB services, but database was still in suspect mode, after
> checking every possibility, I detached that database, I don't know why I
> did
> this.
> But now I am not able to attach database back. When I try doing this it's
> give me following error
> Error 5173: Cannot associate files with different databases.
> By the way when I attach this mdf file, it changes the logical file name
> to
> ABC_Data.mdf
> But when I execute following query, it displays different logical file
> name
> with ABC.mdf
> dbcc checkprimaryfile (N'D:\Program Files\Microsoft SQL
> Server\MSSQL\Data\ABC2.mdf', 3)
> I believe if I change this logical file name I'll be able to attach
> database
> back.
> Kindly help me the recovering database.
> Note: I do not have the transaction log backup & current DB backup.
> Farhan Iqbal
>

Help Needed Disadter Recovery!

Our c drive has failed on our SQL Server following a major system crash!

We are going to have to re-install Windows and SQL Server.

Our backup failed last night.

When we re-install Windows we going to be able to see the drives in with our .mdf and .ldf file on them.

After we re-install sql server 2000 will we be able to run sp_dbattach to re-connect the mdf and ldf files?

Please help!

SimonYou will be able to atach database but if you had replication going on it will fail.

help moving system databases

sql 2000 sp3.
i need to move my system databases to a new drive. i've been reading kb
article 224071. it says to do model before msdb, but it doesn't say
when to do master. so do you do master first or last?
master is independent from the others, as you don't attach or reattach
master, you just change the location in the startup properties in Enterprise
Manager.
Jacco Schalkwijk
SQL Server MVP
"ch" <ch@.dontemailme.com> wrote in message
news:409F91AB.493BB3C4@.dontemailme.com...
> sql 2000 sp3.
> i need to move my system databases to a new drive. i've been reading kb
> article 224071. it says to do model before msdb, but it doesn't say
> when to do master. so do you do master first or last?
>
>

help moving system databases

sql 2000 sp3.
i need to move my system databases to a new drive. i've been reading kb
article 224071. it says to do model before msdb, but it doesn't say
when to do master. so do you do master first or last?master is independent from the others, as you don't attach or reattach
master, you just change the location in the startup properties in Enterprise
Manager.
Jacco Schalkwijk
SQL Server MVP
"ch" <ch@.dontemailme.com> wrote in message
news:409F91AB.493BB3C4@.dontemailme.com...
> sql 2000 sp3.
> i need to move my system databases to a new drive. i've been reading kb
> article 224071. it says to do model before msdb, but it doesn't say
> when to do master. so do you do master first or last?
>
>

help moving system databases

sql 2000 sp3.
i need to move my system databases to a new drive. i've been reading kb
article 224071. it says to do model before msdb, but it doesn't say
when to do master. so do you do master first or last?master is independent from the others, as you don't attach or reattach
master, you just change the location in the startup properties in Enterprise
Manager.
--
Jacco Schalkwijk
SQL Server MVP
"ch" <ch@.dontemailme.com> wrote in message
news:409F91AB.493BB3C4@.dontemailme.com...
> sql 2000 sp3.
> i need to move my system databases to a new drive. i've been reading kb
> article 224071. it says to do model before msdb, but it doesn't say
> when to do master. so do you do master first or last?
>
>