-2

I'm trying to rebuild a log file for my database. I want to know if what is happening is normal. This is what I'm doing:

use master
ALTER DATABASE my_db SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
ALTER DATABASE my_db REBUILD LOG ON (NAME=my_db,FILENAME='C:\MSSQL\Data\my_db_log.ldf')
ALTER DATABASE my_db SET MULTI_USER WITH ROLLBACK IMMEDIATE;

It's been running for over 30 mins now and the messages that are provided are a whole bunch of these:

Nonqualified transactions are being rolled back.
Estimated rollback completion: 100%

Those messages continue to get produced, but is that normal when rebuilding a log? Is the rebuild expected to take a long time when the previous log file was 147 GB? Or is this in a continuous loop?

I didn't create the environment. The customer has very limited space available. The log file was causing disk space errors due to it continuously growing. The database for the log file has recovery set to Bulk_Logged.

The original log file was moved to a different server (not copied). When I realized that was done, I had more space created and moved the file back onto this server. However, that was not being recognized, so I had to rebuild one.

So the rollback is necessary. Is the rebuild actually taking place? If I have a db backup scheduled for later tonight, should I pause that so the log file can continue to get rebuilt or should that be completed by then? Remember, the original log was 147 GB.

After nearly 20 hours, it is still running. It is in single user mode. Also, when I attempt to look at the properties of the database, I get denied because the database "is in transition". Technically, it's in Emergency mode.

On the actual server, it shows single user (person icon on the database). From a remote server SSMS, it says Emergency, as it should be when rebuilding a log file.

0

1 Answer 1

0

According to your analysis, the only thing going on is the ROLLBACK from forever. Although the command in question works just fine on my test computer, or it just might be a machine state problem.

Every once in a while, a SQL Server transaction will ROLLBACK for infinite time. It is good to let it roll back if at all possible, but sometimes you have no choice but to (brace yourself) Restart the SQL Server service. (If this is a production server, schedule the restart for an off time.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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