September 30, 2008 at 10:19 am
So, here is the problem:
My work place has a database with MS Access as the front end and a SQL Server as the back end. For some reason, the SQL Server was set up on a Virtual Machine. We use MS Server 2003.
Anyways, the problem is the transaction log file for the database. The log file is extremely large. So large that it took up all the disk space, thus, it would not let me add anymore information into the database.
So, I used SQL Server Manager and I detached the database from the server and tried to move the log file. However, when I did so, it would not let me attach the database again. Then I put the log file back in the same location as it was before, back to its original state. I then tried to attach the database again, however, the problem now is that it would not attach. The error says:
"A connection could not be established to (LOCAL).
Reason: Cannot Open user default database. Login failed.
Please verify SQL Server is running and check your SQL Server registration properties (by right-clicking on the (LOCAL) node) and try again."
Furthermore, is it possible add attach a database in while only having the database file without the log file?
Also, if I have the .BAK file, can that help me in anyways?
Sorry, I am new at this, and so a lot of questions, however, any help will be appreciated. Thanks my online friends.
September 30, 2008 at 10:29 am
tfang (9/30/2008)
Anyways, the problem is the transaction log file for the database. The log file is extremely large. So large that it took up all the disk space, thus, it would not let me add anymore information into the database.
Your database is in full recovery model and you have no log backups running.
You need to decide whether to start taking log backups (allowing point-in-time recovery) or switch to simple recovery (where you can only restore to the last full/diff backup)
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
September 30, 2008 at 10:38 am
The reason you can't connect is that your login has, as its default DB, the database that you detached. It doesn't matter where the DB is, just that it's not in SQL.
Assuming you're using management studio, disconnect the server from object explorer. Select connect to a db engin and, when the login dialog pops up, go to the second tab. From there you can select a different database. I recommend master. Once you're connected like that, you can attach the DB, with its log file.
Once attached, run the following
ALTER DATABASE < DB name > SET RECOVERY SIMPLE
checkpoint
ALTER DATABASE < DB name > SET RECOVERY FULL
Take a database backup and then set up log backups. Or, if you decide you don't ned point-in-time recovery, leave the DB in simple recovery.
Read this for some basics of transaction log management:
http://sqlinthewild.co.za/index.php/2008/07/23/recovery-model-and-transaction-logs/
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 3 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply