October 30, 2009 at 7:28 pm
Hi All,
Am using sql 2000 Standard Edition.
Issue with tempdb. Received tempdb log file full error and abnormally the script which has runhas failed.
I my scenario, The tempdb is placed in D:\ drive and reached 15 GB of space.
I checked the available free space using xp_fixeddrives and it shows D:\ as 1 GB free space.
Note : Tempdb mdf and ldf is set to 10% growth.
I tried to move all the unnecessary files to network share folder.
Since, it is a Data warehouse Enviornment which is not a 24/7 system, what have done is
restarted the sql server which would re-create the tempdb with initial parameters and able to get around 25 GB of free space.
My question is, since had a choice of restarting the server i have done the restart.
But assuming it is 24/7 running server, then would be my possible steps to resolve the problem apart from cleaning up unnecessary backup and other files....
Can i directly use dbcc shrinkdatabase, shrinkfile to required MB?
Which one should is the best solution?
Also, i would like to know what are all the things / transactions / objects / operations which are responsible for the growth of tempdb?
I would like to know the steps for troubleshooting this issue in 2000 as well as 2005.
Not only tempdb, how to handle such situations for application databases?
Expecting for more comments on this.
Thanks in advance.
November 2, 2009 at 12:01 am
Do you have other drive available on server with free space? If yes then you can follow following steps to resolve issue without restarting sql server:
1. Alter tempdb and add log file or data file whatever is full
2. Stop growth for primary file that is placed on drive
above steps will ensure that your system will continue running fine and you will not need to restart if other drive has sufficient disk space.
Now check data file space used using query http://www.sqlvillage.com/View%20Space%20utilization%20at%20Data%20File%20and%20Database%20Level.htm to determine whether dbcc shrinkfile can release space and if yes then from which file.
[font="Verdana"]--www.sqlvillage.com[/size][/font]
February 20, 2010 at 2:06 am
Hi,
Can i know the reason why you are dividing by 128 in the first SELECT query
1. Script that will display space utilization detail at data file level:
use <DBName>
GO
select convert(numeric(10,2),round(a.size/128.,2)) as 'File Size (MB)'
,convert(numeric(10,2),round(fileproperty( a.name,'SpaceUsed')/128.,2)) as 'Used Space (MB)'
,convert(numeric(10,2),round((a.size-fileproperty( a.name,'SpaceUsed'))/128.,2)) as 'Unused Space (MB)'
,a.name as 'FileName'
from sysfiles
and in the select query multiplied by 8192 and dividing by 1024.
Is that due to fact that 1 page = 8KB and dividing by 1024 to get the output in MB's ???????
set @vsql = 'select ''' + @dbName + ''', str((sum(a.total_pages)*8192)/1024.,15,0) Reserved,'
February 20, 2010 at 2:24 am
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply