The postings on this site are my own and do not represent my Employer's positions, advice or strategies.

LifeAsBob - Blog

 

Home

No Ads ever, except search!
Friday, March 29, 2024 Login
Public

Size and Move TempDB Log file 7/10/2008 3:14:07 PM

Today I was preparing TempDB for some best practices and had to move and size the log file.

I was not able to size the log file of tempDB until I had moved it....and of course I mean physically move it first, which requires stopping and starting the instance.

I was in the process of creating 16 tempdb data files of an even size on a specific mount point and moving the tempdb log file to another mount point.  The current location only had 5gb free, the new location for tempdb's log file would be 33gb in size, i tried to move and size it at the same time and the command failed.  Then I moved the file.  Stopped and started the instance.  Than sized the log file.  PIA.

USE [master]

GO

alter database tempdb

modify file (name='templog', filename= 'D:\EAM\EAMQATempLog\TempDB.LDF')

alter database tempdb

modify file (name='templog', size=10240MB, filename= 'D:\EAM\EAMQATempLog\TempDB.LDF')

ALTER DATABASE [tempdb]

MODIFY FILE ( NAME = N'tempdev', SIZE = 2048MB,

filename = 'D:\EAM\EAMQATempDB\tempdb.mdf' )

ALTER DATABASE [tempdb]

add FILE ( NAME = N'tempdev2', SIZE = 2048 MB,

filename = 'D:\EAM\EAMQATempDB\tempdb2.ndf' ) to filegroup [PRIMARY]

ALTER DATABASE [tempdb]

add FILE ( NAME = N'tempdev3', SIZE = 2048 MB,

filename = 'D:\EAM\EAMQATempDB\tempdb3.ndf' ) to filegroup [PRIMARY]

ALTER DATABASE [tempdb]

add FILE ( NAME = N'tempdev4', SIZE = 2048 MB,

filename = 'D:\EAM\EAMQATempDB\tempdb4.ndf' ) to filegroup [PRIMARY]

ALTER DATABASE [tempdb]

add FILE ( NAME = N'tempdev5', SIZE = 2048 MB,

filename = 'D:\EAM\EAMQATempDB\tempdb5.ndf' ) to filegroup [PRIMARY]

ALTER DATABASE [tempdb]

add FILE ( NAME = N'tempdev6', SIZE = 2048 MB,

filename = 'D:\EAM\EAMQATempDB\tempdb6.ndf' ) to filegroup [PRIMARY]

ALTER DATABASE [tempdb]

add FILE ( NAME = N'tempdev7', SIZE = 2048 MB,

filename = 'D:\EAM\EAMQATempDB\tempdb7.ndf' ) to filegroup [PRIMARY]

ALTER DATABASE [tempdb]

add FILE ( NAME = N'tempdev8', SIZE = 2048 MB,

filename = 'D:\EAM\EAMQATempDB\tempdb8.ndf' ) to filegroup [PRIMARY]

ALTER DATABASE [tempdb]

add FILE ( NAME = N'tempdev9', SIZE = 2048 MB,

filename = 'D:\EAM\EAMQATempDB\tempdb9.ndf' ) to filegroup [PRIMARY]

ALTER DATABASE [tempdb]

add FILE ( NAME = N'tempdev10', SIZE = 2048 MB,

filename = 'D:\EAM\EAMQATempDB\tempdb10.ndf' ) to filegroup [PRIMARY]

ALTER DATABASE [tempdb]

add FILE ( NAME = N'tempdev11', SIZE = 2048 MB,

filename = 'D:\EAM\EAMQATempDB\tempdb11.ndf' ) to filegroup [PRIMARY]

ALTER DATABASE [tempdb]

add FILE ( NAME = N'tempdev12', SIZE = 2048 MB,

filename = 'D:\EAM\EAMQATempDB\tempdb12.ndf' ) to filegroup [PRIMARY]

ALTER DATABASE [tempdb]

add FILE ( NAME = N'tempdev13', SIZE = 2048 MB,

filename = 'D:\EAM\EAMQATempDB\tempdb13.ndf' ) to filegroup [PRIMARY]

ALTER DATABASE [tempdb]

add FILE ( NAME = N'tempdev14', SIZE = 2048 MB,

filename = 'D:\EAM\EAMQATempDB\tempdb14.ndf' ) to filegroup [PRIMARY]

ALTER DATABASE [tempdb]

add FILE ( NAME = N'tempdev15', SIZE = 2048 MB,

filename = 'D:\EAM\EAMQATempDB\tempdb15.ndf' ) to filegroup [PRIMARY]

ALTER DATABASE [tempdb]

add FILE ( NAME = N'tempdev16', SIZE = 2048 MB,

filename = 'D:\EAM\EAMQATempDB\tempdb16.ndf' ) to filegroup [PRIMARY]

GO

alter database tempdb

modify file (name='templog', filename= 'D:\EAM\EAMQATempLog\TempDB.LDF')

alter database tempdb

modify file (name='templog', size=10240MB, filename= 'D:\EAM\EAMQATempLog\TempDB.LDF')


Blog Home