April 26, 2016 at 9:48 pm
hi,
could any one know how to add and remove tempdb datafile to tempdb.
reason. we need allocate more space for tempdb and remove in few days time.
how do we do that.
to be considered.
how to do it by considering windows failover scenario will get effect sql server by doing this(this job only in one single instance only)
What is the datafile remove procedure from instance.( assume This instance site on windows cluster)
thanks
April 28, 2016 at 5:45 am
ashwan (4/26/2016)
hi,could any one know how to add and remove tempdb datafile to tempdb.
reason. we need allocate more space for tempdb and remove in few days time.
how do we do that.
to be considered.
how to do it by considering windows failover scenario will get effect sql server by doing this(this job only in one single instance only)
What is the datafile remove procedure from instance.( assume This instance site on windows cluster)
thanks
add a datafile using the following as an example
ALTER DATABASE tempdb ADD FILE(NAME = tempdev_8,
FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\DATA\tempdev_8.ndf',
SIZE = 512MB, FILEGROWTH = 1024MB, MAXSIZE = 10240MB)
A restart of the sql server service is not enforced but if the tempdb is already heavily used and you add a new file it would make sense to restart to kick the proportional fill algorithm.
To remove the file you would use the following and then restart the sql server service
ALTER DATABASE [tempdb] REMOVE FILE tempdev_8
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply