1

I am trying to build a non clustered index as follows:

CREATE NONCLUSTERED INDEX IX_MyIndexName ON MyTable(MyColumn) INCLUDE (Column1,Column2,Column3)

However, when i do so, I get a 9002 error:

The transaction log for database 'myDatabase' is full due to 'ACTIVE_TRANSACTION'.

When I run DBCC OPENTRAN()I get

No active open transactions

I am thinking that it is the create index statement itself that is the active transaction filling the log as by the time I run DBCC OPENTRAN() the transaction will have rolled back?

The database is in simple recovery mode (It is a reporting database created from a backup of the production database)

The log is set to an initial size of 2MB, to autogrow by 5120MB and is limited to 100000MB.

If I run DBCC SQLPERF(LOGSPACE) I can see the log size is 1.125MB and Log space used is 31%

The disk that holds the log file has over 1TB free space

I tried to change the initial size of the log using

ALTER DATABASE ... MODIFY FILE....SIZE=...

and am getting

MODIFY FILE encountered operating system error 59(An unexpected network error occurred.) while attempting to expand the physical file \\path\to\my\file.ldf

So I wonder if when SQL server is trying to expand the transaction log to process the transaction, there is this unexpected network error occurring and therefore the transaction fails.

I get the network error every time I run the MODIFY FILE command for the database in question and the same happens for another database (both of these databases have active connections) but I can resize other databases we have that do not have any active transactions (the files for all of the databases are in the same location) so I am thinking this network error may be related to number of connections?

SQL server is running as a domain account and that account has full control over the folder that the database files are stored in.

I suspect moving the database to a local drive rather than a network drive could solve this so that is my next step but that will have to be out of hours.

In the mean time, does anyone have any suggestions of what may solve this? Am I heading down the right track?

EDIT

I've added an additional log file to the database, this time it's initial size is 10,000MB, autogrowth by 64MB, unlimited max space. I set the location as a local drive and ran the create index statement and it worked.

I then dropped the new log file and recreated on the same network location, dropped the index I created before, re-ran the create index and all worked OK.

Next step is to go back to the single log file and put it on a local drive (I'll leave the size the same) and see if the log auto grows

  • What is current log size? If it's less then the limit of 100000MB, you may just have no space on your disk to make the log file grow – sepupic Oct 26 '17 at 9:45
  • Oops, forgot to mention disk space, I'll update the question – SEarle1986 Oct 26 '17 at 10:08
  • Theres 1TB free – SEarle1986 Oct 26 '17 at 10:10
  • Did you check SQL Server error log to see if the same error occurs when server itself tries to grow log file? And what is your current log size? – sepupic Oct 26 '17 at 10:12
  • Is your database actually running on a network drive as in \\SERVERNAME\SHARE\FOLDER\SUBFOLDER? I suspect moving the database to a local drive rather than a network drive could solve this so... Further reading: SQL Server Can Run Databases from Network Shares & NAS states it can be done, but... – hot2use Oct 26 '17 at 11:17
0

We've changed over the log file disk location to a local disk and that has fixed the problem.

It is still not entirely clear why the transaction log was not expanding in the network location as there didn't seem to be a logical reason why it wouldn't but its something to bear in mind in the future

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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