Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

For the SQL server transaction log file, it is possible to set its target file to a disk on a remote machine?

I am working on a BI project. In its current iteration, when the data warehouse loading processes run, the transaction log can balloon (>100 GB) in size before it has a chance to truncate. There isn't any room to add more disks to this server, and future data loading operations will involve substantially more data and so I don't think there will be enough disk space for the log. But we have a development server in the same rack with 900+ GB free and I was thinking we could use that free disk space so that bigger loads will work.

share|improve this question
    
SQL Server 2012 and above are the ones I know of that support SMB fileshare. –  Shawn Melton Jun 9 at 19:33
2  
Actually I was wrong, but be advised of the issues that could arise from any network hiccup that occurs: blogs.msdn.com/b/varund/archive/2010/09/02/… –  Shawn Melton Jun 9 at 19:36
2  
Process your data in batches of, say, 20K rows each. Commit at the end of each batch, including some checkpoint/ restart values within the transaction scope. Use the simple recovery model and/ or frequent log backups during the ETL. –  Michael Green Jul 9 at 11:39

3 Answers 3

You should focus on breaking your ETL process in chunks to manage transaction log growth. There are other options like setting recovery model to bulk log selective transactions but with bulk logged recovery model you loose point in time recovery. And I do agree setting log file on remote storage is not a suggested practice and it will make operations slow.

share|improve this answer

It is supported to have database files on network share, but Its inviting a disaster as any network blip might corrupt your database.

Microsoft recommends that you store database files either on Local Disk or on Storage Area Networks (SANs).

You have to tune your data warehouse loading process. There are many parameters that can be tuned to have minimally logged operations and fast loading of data in sql server. e.g.

Also, if you want to be proactive, then create an alert for Transaction log full, so you get notified when the log is about to get filled up.

Read up on :

share|improve this answer
3  
What is your evidence to support the statement "but its inviting a disaster as any network blip will corrupt your database"? –  Mark Storey-Smith Jul 9 at 11:52
    
@MarkStorey-Smith I should have said might as I encountered it when trying that with sql 2008R2 when the feature just got released. Dont have a repro now :-) –  Kin Jul 9 at 15:43
    
In that case I'm inclined to suggest you remove the inference. Statements like this have a nasty habit of becoming dogma if they proliferate. –  Mark Storey-Smith Jul 10 at 0:01

Yes it is possible...

I want to call out a few points before offering the solution:

  • You must configure permissions so that the appropriate applications have the full control over the directory and files in the destination. This includes:
    • SQL Server Service Account or whichever account you are running the SQL Server process under.
    • If batch jobs run under a different account, and you perform full transaction log backups, this account must also have permissions.
  • Beware of the latency of committing across a remote drive will impact performance
  • Remote drives can be bottlenecks if the remote server is using the drive for other purposes (such as page files, or a enterprise file share).
  • Performance test this scenario!!

With those concerns out of the way (plus more that were highlighted by @James in the comments).... here is a possible solution.


As a pre-requisite - this example assumes that you have a mapped drive to the E:\. The access is available to the appropriate SQL accounts.

To quote a referenced site entitled Move SQL Server transaction log files to a different location via TSQL and SSMS:

In a nutshell, the three main steps involved in moving a log file to new location are:

  1. Detach the database
  2. Move log file to new location
  3. Attach the database by referencing the new location of the transaction log file

Detach script is as follows:

Use MASTER
GO

-- Set database to single user mode
ALTER DATABASE adventureWorks
SET SINGLE_USER
GO

-- Detach the database
sp_detach_db 'AdventureWorks'
GO

Once the detach has completed, move the file manually via whatever means necessary. Then re-attach with the following T-SQL:

USE master
GO

sp_attach_DB 'AdventureWorks', 
'D:\Program Files\Microsoft SQL Server\MSSQL\Data\AdventureWorks_Data.mdf', -- Data File
'E:\Move LogFile here through T-SQL\AdventureWorks_Log.ldf'  -- Transaction Log File
GO
share|improve this answer
2  
I don't think this addresses the actual question. The OP appears to be asking about hosting the transaction log on a remote server, over a CIFS share or some other means. –  James Jul 8 at 16:46
    
This answer is assuming that `E:` is mapped to a remote server, rather than local storage –  Dominic Zukiewicz Jul 9 at 10:44
2  
Any other feedback as part of the downvotes? The question is ... is it possible to set its target file to a disk on a remote machine?. I believe I have given a valid option here. –  Dominic Zukiewicz Jul 9 at 10:48
2  
I would assume the downvotes are due to the mapped drive being unnecessary. Database files on the network are fully supported. –  Mark Storey-Smith Jul 9 at 11:55
    
@James - thank you for your honesty :) I have to admit I didn't know the more advanced items (like the trace flags) etc, but I will add guidance to my answer to call these out as points to research. –  Dominic Zukiewicz Jul 9 at 15:18

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.