I have a database that we are having problems with. Somehow the log has became 400 Gb and the database has been rendered useless. I want to drop all existing connections to the database and then detach the database.
Basically what I'm going to do is get rid of the giant log file and create a new one and reattach if it works. If not, we're going going to restore from backups.
If the log is useless, you can use these commands, but please document on them yourself before applying on a production server.
BACKUP LOG WITH NO_LOG for disgard the pages from log,
sp_helpdb for looking the name of the files of the db
DBCC SHRINKFILE('your log filename ', 0) -- for trunking the physical file to the size specified.
If you are sure there are no open transactions you can put the database in single user mode.
ALTER DATABASE [YourDB] SET SINGLE_USER WITH NO_WAIT
When you are done put it back in multi user mode ALTER DATABASE [YourDB] SET MULTI_USER WITH NO_WAIT
Does
backup log yourdb with truncate_only then dbcc shrinkdatabase(yourdb) not shrink the logfile for you ?
Well basically no commands would execute agaisnt the database, at all. What we ended up doing was turning off the service and creating empty copies of the mdf and ldf files and replacing the ones being used by sql server. After that we restored the database from the last backup and voila, it's working again (mostly).
ALTER DATABASE [DB_NAME_HERE] SET SINGLE_USER WITH ROLLBACK IMMEDIATE
ALTER DATABASE [DB_NAME_HERE] SET MULTI_USER