Dropping a Transaction Log Device- SQL Server 6.5 1

[stoolpigeon]

I am a programmer at a small company, and I also wear the DBA hat (if sitting w/a copy of "SQL Server 6.5 Unleased" in front of the server makes me a DBA)

I had problems w/my transaction log filling up. So I expanded the log device but it didn't help. So I added a new device on another drive that was significantly larger.

Now I want to delete the old device but when I do it tells me that there is still data from the database in that device and if I delete it the database will be dropped.

If I run sp_helplog for the database- it shows me that the transaction log starts on the new device.

I have dumped the transaction log repeatedly. What am I missing?

I would like to get the old transaction log device empty- so that I can drop it and free up that space on the server.

Thanks for whatever help is available.

 

[tlbroadbent]

Have you used sp_logdevice to move the log to the new device?

To empty the transaction log, run the following SQL commands from ISQL/w in the specified order.

USE dbName
Go
DUMP TRAN dbname with NO_LOG
Go
DBCC CHECKDB
Go
DBCC CHECKALLOC
Go
DBCC NEWALLOC
Go
DBCC CHECKTABLE(syslogs)
Go

This should correct problems with the transaction log. If not, check the following links for more help with transaction logs.

Why does my SQL Server log show that it's still full? - I have truncated it.

http://www.windows2000faq.com/Articles/Index.cfm?ArticleID=14068

INF: Causes of SQL Transaction Log Filling Up

http://support.microsoft.com/support/kb/articles/Q110/1/39.asp

INF: Transaction Log Still Full After DUMP TRAN WITH NO_LOG

http://support.microsoft.com/support/kb/articles/Q184/4/99.ASP

Terry L. Broadbent
FAQ183-874 contains tips for posting questions in these forums.
NOTE: Reference to the FAQ is not directed at any individual.

 

[stoolpigeon]

I have run sp_logdevice and it tells me that everything is moved over. I will run all of the above as you have laid it out and see if that gets me there.

Thanks for the suggesstion as well as the links.