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?
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