Writing MS SQL transaction log to a non-cluster disk : Urgent

Normally that error reflects on Dependencies that are not available, or are now different from when (sql server) was installed.

There is a good blog : http://blogs.msdn.com/b/sqlserverfaq/archive/2010/10/27/sql-server-2008-service-fails-to-start-after-applying-service-pack-1.aspx that describes resolution. Normally to do with a path name that cannot be resolved / found.

If SQL Server is still up (even in simple recovery), you can also check via SSMS : Check Server properties in Database Settings, and find the path under Database default locations.

Pinal Dave wrote about this on his blog as well (little bit different) : http://blog.sqlauthority.com/2015/05/22/sql-server-only-formatted-files-on-which-the-cluster-resource-of-the-server-has-a-dependency-can-be-used/

And MS has a KB which talks about changing dependencies (for an earlier version) : https://support.microsoft.com/en-us/kb/295732

Hope that helps...

In our case, it does not appear to be "path could not be found":
our DBA says it could not write "transction log" to a drive tho he could
take an SQL dump to another drive that is not part of the cluster service.

By adding a new partition to the cluster service, he was able to write the
"transaction log" to that added partitition.

Which of the link you've given above specically will enable us to write
the transaction log to a drive/partition that is not part of the cluster
service/resource?

I would first go through Pinal Dave's blog.

The fact that a new partition could be written to, to me, that does imply "path" problem at least from a Resources perspective. When the partition is added after, then SQL knows about it and so it is available. Those dependencies can be a little tricky and often a restart is needed to resolve (tho 2014 seems fine).

But go through Pinal's blog first. While it was for creating a DB on a clustered drive (which you will need doing), the steps he supplied is what I would be suggesting as well. Also read the comment making sure you are looking at resources... Click on properties on the SQL Server Resource.

In a cluster environment you can only stored data and log files in a cluster resource disk that are dependent on the SQL Server service. If you don't do that SQL Server won't recognize the disk and throws the error that you're getting now.

@Vitor, while that is true (ie Database files must be on shared storage when you have a clustered SQL Server), so long as SQL Server *thinks* the disk is part of the clustered service, then you can register the disk as a dependency. But you first have to recognise the disk (cluadmin).

I know that SQL2012 and more recent allows for a local TempDB, but not too sure about earlier (it has been a while).

I think the disk that is NOT part of the cluster needs to recognised by the cluster.

Anyway - official reading for 2008 is : https://msdn.microsoft.com/en-us/library/ms177447(v=sql.105).aspx

But, in thinking this through a bit more, whay would you want a (non-simple recovery mode) transaction log outside the cluster anyway - kinda defeats the purpose ?

>why would you want a (non-simple recovery mode) transaction log outside the cluster anyway

I don't know why but the developer of this product was not reachable at that time & their
SQL requires to set recovery to "BULK_LOGGED", not simple mode & we find from our past
testing on a test standalone server, the size of the transaction log is about 110GB which is
more than all the free space of the disk resources available to this cluster service.

So is there a wa

So is there a way to 'fool' or work around this for Win2008 R2?

Took about 12 hrs to reach that size.

So Vitor, I guess you are referring to something like the following:
http://tctblgs.azurewebsites.net/shrinking-sql-log-files-in-an-availability-group-cluster-or-database-mirror/
After backing up the t-logs, delete away;  so we have to set the size of the t-log
so that MS SQL will create a new t-log file when it reaches that size, is this right?

In our environment to ask for more storage (& vCPU & vRAM) takes days
to be approved.

http://logicalread.solarwinds.com/sql-server-tempdb-best-practices-placement-w01/#.VsMvRLR94sY

referring to above link, looks like it is possible to write/create tempdb to a non-cluster disk or
a local disk;  does the same apply to t-log as well?

I guess the need to have all db files including t-log files on a cluster disk is as quoted in above link:
"Until SQL Server 2012, a failover cluster instance of SQL Server required all its database files to be on shared disk resources within the cluster. This was to ensure that when the instance failed over to another node in the cluster, all its dependent disks could be moved with it."

But there's a way to work around/force it to local disk for tempdb;  no mention for t-log in above link