July 29, 2004 at 7:56 pm
Hi
My company's tempdb log file is getting full. Please help me I am in database. I appreciate if you give step by step process...urgent. And any way I can make a job which will take care automatically whenever it comes 90 % full.
Thanks
July 29, 2004 at 11:38 pm
July 30, 2004 at 6:38 am
There are lots of things you can do to 'shrink' the tempdb. But all you really need to do is stop and start the SQL Server services.
That causes TEMPDB to be rebuilt to it's original size. You shouldn't be using the TEMPDB for any permanent databases anyways since any crash/stopping of services will cause everything in that DB to be lost and the db to be rebuilt. (That's why it's called TEMPdb).
There's lots of posts on this subject. Please take time before posting to do a search to see if your question has already been answered. It's easier and you can get you answer quicker that way.
-SQLBill
July 30, 2004 at 8:00 am
Hi,
Thanks for your time. Yes recovery model is set to "Simple"
I found one of the script which take care of tempdb
Script to Shrink a Log File
/* Run "SELECT fileid, name,filename FROM <db_name>..sysfiles" to get
the fileid you want to shrink.
example :SELECT fileid, name, filename FROM tempdb .. sysfiles*/
USE <db_name>
GO
DBCC shrinkfile(<fileid>,notruncate)
DBCC shrinkfile(<fileid>,truncateonly)
CREATE TABLE t1 (char1 char(4000))
GO
DECLARE @i int
SELECT @i = 0
WHILE (1 = 1)
BEGIN
WHILE (@i < 100)
BEGIN
INSERT INTO t1 values ('a')
SELECT @i = @i +1
END
TRUNCATE TABLE t1
backup log <db_name> with truncate_only
END
GO
July 30, 2004 at 8:02 am
Hi,
Thanks for your time. I did what you want me to do and I found one of the script which take care of my tempdb
Script to Shrink a Log File
/* Run "SELECT fileid, name,filename FROM <db_name>..sysfiles" to get
the fileid you want to shrink.
example :SELECT fileid, name, filename FROM tempdb .. sysfiles*/
USE <db_name>
GO
DBCC shrinkfile(<fileid>,notruncate)
DBCC shrinkfile(<fileid>,truncateonly)
CREATE TABLE t1 (char1 char(4000))
GO
DECLARE @i int
SELECT @i = 0
WHILE (1 = 1)
BEGIN
WHILE (@i < 100)
BEGIN
INSERT INTO t1 values ('a')
SELECT @i = @i +1
END
TRUNCATE TABLE t1
backup log <db_name> with truncate_only
END
GO
August 12, 2004 at 7:35 am
is there any space on your harddisk?
what about restarting the Server?
Alamir Mohamed
Alamir_mohamed@yahoo.com
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply