Cannot shrink log file due to orphaned replicated transactions 3

[flstffatboy]

Hello all,

I have a SQL Server 2000 test database which is a backup and restored copy from our production database. The production database has transactional replication running on it but the test database does not.

I'm having issues with trying to shrink the transaction log for the test database. Im getting a message that replicated transactions exist in the log.

I have tried to run sp_removedbreplication on the test database and then backup and shrink the log but it will not work.

I have also tried to do a sp_detach_db, rename the log files and then tried to run a sp_attach_db but I keep getting the error below.


Server: Msg 1813, Level 16, State 2, Line 1
Could not open new database 'preproddb'. CREATE DATABASE is aborted.
Device activation error. The physical file name 'L:\Program Files\Microsoft SQL Server\MSSQL\Data\preproddb_Log.LDF' may be incorrect.
Device activation error. The physical file name 'L:\Program Files\Microsoft SQL Server\MSSQL\Data\preproddb_Log2_Log.LDF' may be incorrect.

Can you attach a SQL Server 2000 database without the log files? I thought the log files will be created if they do not exist.

Thank you in advance for your help!

FLSTF

 

[mrdenny]

You can use sp_attach_single_file_db to attach a database without the log files.

Denny
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / Microsoft Windows SharePoint Services 3.0: Configuration / Microsoft Office SharePoint Server 2007: Configuration)
MCITP Database Administrator (SQL 2005) / Database Developer (SQL 2005)

My Blog

 

[flstffatboy]

mrdenny,

Thanks for your reply. I should have noted yesterday that I tried that as well. Below is the exact steps I used when using the sp_attach_single_file_db

1. Detach the database
EXEC sp_detach_db 'preproddb', 'true'

2. Rename the log files associated to the preproddb database.

3. Attach single file
EXEC sp_attach_single_file_db @dbname = 'preproddb',
@physname = 'H:\Program Files\Microsoft
SQL Server\MSSQL\Data\preproddb_Data.MDF'

I recieve the same error as when I try to use the sp_attach_db. Error below:

Server: Msg 1813, Level 16, State 2, Line 1
Could not open new database 'preproddb'. CREATE DATABASE is aborted.
Device activation error. The physical file name 'L:\Program Files\Microsoft SQL Server\MSSQL\Data\preproddb_Log.LDF' may be incorrect.
Device activation error. The physical file name 'L:\Program Files\Microsoft SQL Server\MSSQL\Data\preproddb_Log2_Log.LDF' may be incorrect.


Am I missing an option when I run the sp_attach_single_file_db which forces the creation of a new log file?

Thanks,
FLSTF

 

[mrdenny]

Does the L drive exist? Does the folder in the error exist?

Denny
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / Microsoft Windows SharePoint Services 3.0: Configuration / Microsoft Office SharePoint Server 2007: Configuration)
MCITP Database Administrator (SQL 2005) / Database Developer (SQL 2005)

My Blog

 

[flstffatboy]

mrdenny,

Yes both the L drive and the path to the DATA folder exist. I'm trying to do the detach/attach in the current path the log files already exist in. I have only renamed the existing log files in the L drive of the DATA folder after doing the detach but prior to attaching.

FLSTF

 

[mrdenny]

There isn't any switch that you should have to apply. It should recreate the data files automatically.

Try doing a CREATE DATABASE WITH ATTACH_REBUILD_LOG and see if that does the trick.

Denny
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / Microsoft Windows SharePoint Services 3.0: Configuration / Microsoft Office SharePoint Server 2007: Configuration)
MCITP Database Administrator (SQL 2005) / Database Developer (SQL 2005)

My Blog

 

[flstffatboy]

mrdenny,

I do not see any option on the create database statement with the attach_rebuild_log option. I'm running SQL Server 2000. I did however see a Create Database with a FOR ATTACH option. I tried this and received the same error I did when I tried to do a attach or attach_single_file.

I really appreciate your help and time.

FLSTF

 

[Ovatvvon]

flstffatboy,

The command Denny gave you was for Query Analyzer / SSMS. It is T-SQL code. Not a GUI option.


-Ovatvvon :-Q

 

[flstffatboy]

Ovatvvon,

Understood. I wasn't using the GUI and I looked up the syntax in BOL and could not find a WITH option under the CREATE DATABSE statement for SQL Server 2000. I even tried the following which returned an invalid statement.

CREATE DATABASE preproddb
ON PRIMARY (FILENAME = 'H:\Program Files\Microsoft SQL Server\MSSQL\Data\preproddb_Data.MDF')
WITH ATTACH_REBUILD_LOG

Am I missing something?


Thanks,
FLSTF

 

[mrdenny]

Looks like the ATTACH_REBUILD_LOG is a SQL 2005 and up option. Lets go ahead and attach the database with the log entries and see what we can do to get the log cleaned up.

What do you get when you run sp_removedbreplication on the database?

Denny
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / Microsoft Windows SharePoint Services 3.0: Configuration / Microsoft Office SharePoint Server 2007: Configuration)
MCITP Database Administrator (SQL 2005) / Database Developer (SQL 2005)

My Blog

 

[flstffatboy]

This gives me the message that the command completed successfully.

Still unable to shrink the log though.

Thanks,
FLSTF

 

[Ovatvvon]

Are you able to stop replication, run COMMIT on the database and/or tran log backup, and then do a shrink before starting up replication again?


-Ovatvvon :-Q

 

[flstffatboy]

This particular database should not have replication running on it. It was created from a full backup from our production database which had replication running on it and restored to our test instance. I have backed up production and restored to our test database over 100 times whithout ever having this problem. I even tried a backup from production and restore to try and fix this issue but I get the same problem each time now.

Thanks,
FLSTF

 

[Ovatvvon]

Does this use DTC?

What happens when you open up component services? Are there any open transactions? Can you commit or abort them? How does that affect your ability to shrink the database if so?


On the server:
1) Start -> Run -> "mmc.exe" -> OK Button
2) In MMC, click File, then "Add/Remove Snap-In"
3) Click ADD Button
4) Select Component Services, Click the ADD Button. Then click the Close Button.
5) Click the OK Button.
6) Double Click on Component Services, and continue cycling down through the tree through: Computers -> My Computer -> Distributed Transaction Coordinator -> Transaction List.
7) Expand the STATUS column to see if each transaction is "Active", or if it is "In Doubt", etc. You can right-click on them, and select Resolve to have your options displayed: Commit, Abort, or Forget.

Hopefully this will help.


-Ovatvvon :-Q

 

[flstffatboy]

Ovatvvon,

When you say does this use DTS are you refering to the backup and restore from production to our test environment OR if we have ever used DTS period on the database? The backup to retore does not use DTS but I have used DTS before in conjunction with the database for other tasks.

I have tried what you have said and there are no transactions in any status of the transaction list.

Thanks,
FLSTF

 

[mrdenny]

Any in dought transactions would have been rolled back when the database was restored.

What happens when you try do to a backup log with truncate_only?

Denny
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / Microsoft Windows SharePoint Services 3.0: Configuration / Microsoft Office SharePoint Server 2007: Configuration)
MCITP Database Administrator (SQL 2005) / Database Developer (SQL 2005)

My Blog

 

[Ovatvvon]

Denny,

I've very recently had a database that did not rollback the in-doubt transactions. It also would not let me commit or specifically abort the transactions. The only thing it'd let me do is 'forget' them.

I admit, very strange, and no one else on my team had seen tha before, but that was the case. I do think there was some issue with the database, however, due to further issues. It would not load on the system stating it could not communicate with DTC due to unresolved transactions (even after I had manually cleared them), and would not let me detach the database either. The only thing I could do was delete it, and restore from the latest full/tran log backups.

Just thought it would be worth checking out.


-Ovatvvon :-Q

 

[flstffatboy]

When I run the backup log with truncate_only I get the following message:

The log was not truncated because records at the beginning of the log are pending replication. Ensure the Log Reader Agent is running or use sp_repldone to mark transactions as distributed.

When I run sp_repldone I receive the following message:
Server: Msg 18757, Level 16, State 1, Procedure sp_repldone, Line 1
The database is not published.

Thanks,
FLSTF

 

[mrdenny]

I would recommend publishing the database and then run sp_repldone then removing replication from the database.

Denny
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / Microsoft Windows SharePoint Services 3.0: Configuration / Microsoft Office SharePoint Server 2007: Configuration)
MCITP Database Administrator (SQL 2005) / Database Developer (SQL 2005)

My Blog

 

[flstffatboy]

mrdenny,

Just tried your suggestion and receive the following message when running sp_repldone

Server: Msg 18752, Level 16, State 1, Procedure sp_repldone, Line 1
Another log reader is replicating the database.

Not sure why it would think another log reader is replication this database. It has never had replication setup on it prior to this.

FLSTF