Have Geek Brain

Disable database connections in MS Sql Server 2000

I have a database that we are having problems with. Somehow the magazine became 400 Gb and the database turned out to be useless. I want to drop all existing database connections 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 reconnect if it works. If not, we're going to restore backups.

+1


source to share


4 answers


Well, basically, no commands will execute agaisnt on the database at all. What we ended up with was to disable the service and create empty copies of the mdf and ldf files and replace those used by the sql server. After that, we restored the database from the last backup and voila, it works again (mostly).



0


source


If the log is not useful, you can use these commands, but please document them yourself before using them on a production server.

BACKUP LOG WITH NO_LOG to discard pages from the log,



sp_helpdb to find the name of the db files

DBCC SHRINKFILE ('your log file name', 0) - to translate the physical file to the specified size.

+1


source


If you are sure that 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're done, return it to multi-user ALTER DATABASE [YourDB] SET MULTI_USER WITH NO_WAIT

Whether there is a

backup yourdb log with truncate_only then dbcc shrinkdatabase (yourdb) doesn't shrink the log file for you?

+1


source 


ALTER DATABASE [DB_NAME_HERE] SET SINGLE_USER WITH ROLLBACK IMMEDIATE

ALTER DATABASE [DB_NAME_HERE] SET MULTI_USER
0


source






More articles:


All Articles