Tempdb Log file location - SQL Server would not create it

Question

Post reply

Tempdb Log file location - SQL Server would not create it

quackhandle1975

SSChampion

Points: 11055
More actions
December 10, 2014 at 4:30 am

#313658

Hi,
have an issue with a VM SQL 2008 R2 SP2 instance that when I try to alter the location/path of tempdb log file, sql service fails to come back online, so have to go in via sqlcmd and use the default settings. SQL service account has full control permissions of the E:\MSSQL\TempLog drive/folder and there is 80GB of free space but still no luck. Have done this many times in last few weeks, seems to be just this one instance.
alter database tempdb
modify file (name = tempdev, filename = 'D:\MSSQL\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\tempdb.mdf')
go
alter database tempdb
modify file (name = templog, filename = 'E:\MSSQL\TempLog\templog.ldf')
go
Any reason why SQL Server wouldn't like the above?
Error log output:
2014-12-10 10:16:05.39 spid10s CREATE FILE encountered operating system error 5(failed to retrieve text for this error. Reason: 15105) while attempting to open or create the physical file 'E:\MSSQL\Templog\templog.ldf'.
2014-12-10 10:16:05.39 spid10s Could not create tempdb. You may not have enough disk space available. Free additional disk space by deleting other files on the tempdb drive and then restart SQL Server. Check for additional errors in the event log that may indicate why the tempdb files could not be initialized.
Cheers
qh
[font="Tahoma"]Who looks outside, dreams; who looks inside, awakes. – Carl Jung.[/font]

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

Shafat Husain SSCrazy Points: 2754 More actions · Answer 1

Hi,

Try passing a size parameter with your original query:

alter database tempdb

modify file (name = tempdev, filename = 'D:\MSSQL\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\tempdb.mdf', size = 1GB)

go

alter database tempdb

modify file (name = templog, filename = 'E:\MSSQL\TempLog\templog.ldf', size = 1GB)

go

Regards,
Shafat Husain
🙂
And Your Lord Never Forgets...!! (64:19 -- Quran)

quackhandle1975 SSChampion Points: 11055 More actions · Answer 2

Thanks Shafat, that's an interesting solution, maybe worth ago, however the instance in question is in UAT testing phase so it's off limits ATM. In the new year it will be going into full production so want to get this resolved before then.

qh

[font="Tahoma"]Who looks outside, dreams; who looks inside, awakes. – Carl Jung.[/font]

Perry Whittle SSC Guru Points: 233792 More actions · Answer 3

OS error 5 while attempting to open or create the physical file 'E:\MSSQL\Templog\templog.ldf' is pretty clear. Double check the path and ACLs on the folder and file level

What does this query return when run against TempDB at present

SET NOCOUNT ON

SELECTdf.name AS LogicalFileName

, physical_name AS PhysicalOSName

, (df.size / 128) AS SizeMBs

, (FILEPROPERTY(df.name, 'SpaceUsed') / 128) AS SpaceUsedMBs

, CASE

WHEN df.max_size / 128 = 0 THEN CAST(df.size / 128 as varchar(50)) + ' MBs'--'Unlimited'

WHEN df.max_size / 128 = 2097152 and df.growth = 0 THEN 'No growth'

WHEN df.max_size = 268435456 THEN '2TB'

ELSE CAST(df.max_size / 128 AS VARCHAR(10)) + ' MBs'

END AS MaxGrowthSize

, CASE df.is_percent_growth

WHEN 0 THEN CAST(df.growth / 128 AS VARCHAR(10)) + ' MBs'

ELSE CAST(df.growth AS VARCHAR(10)) + ' %'

END AS Growth

FROM sys.database_files df

ORDER BY df.type

-----------------------------------------------------------------------------------------------------------

"Ya can't make an omelette without breaking just a few eggs" 😉

quackhandle1975 SSChampion Points: 11055 More actions · Answer 4

I see what your script is doing, have checked the path and ACL (sql service account has full control) but still would not work. I have moved the templog file back to default d: drive whilst the testing is going on, but I suspect it is something to do with the E:\ drive on the VM. Server admin guy I work with is going to remove it (not many dbs are using it) and re present it.

Cheers

qh

[font="Tahoma"]Who looks outside, dreams; who looks inside, awakes. – Carl Jung.[/font]

Gail Shaw SSC Guru Points: 1004494 More actions · Answer 5

OS error 5 is access denied.

If it's a share check the permissions on the share and the folder. Make sure that there isn't an existing file of the matching name that SQL doesn't have permissions to.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

quackhandle1975 SSChampion Points: 11055 More actions · Answer 6

Thanks Gail, can't do any harm to recheck but SQL Service account had full control on that drive/paths/etc. SQL was installed by non DBA a while back so once testing is complete I can do a proper check.

qh

[font="Tahoma"]Who looks outside, dreams; who looks inside, awakes. – Carl Jung.[/font]

Perry Whittle SSC Guru Points: 233792 More actions · Answer 7

quackhandle1975 (12/10/2014)
I see what your script is doing, have checked the path and ACL (sql service account has full control) but still would not work. I have moved the templog file back to default d: drive whilst the testing is going on, but I suspect it is something to do with the E:\ drive on the VM. Server admin guy I work with is going to remove it (not many dbs are using it) and re present it.
Cheers
qh

Check the NTFS ACLs on the actual file too, if inheritance is disabled the file could have the wrong permissions

-----------------------------------------------------------------------------------------------------------

"Ya can't make an omelette without breaking just a few eggs" 😉