| Author |
Topic |
|
sql_er
Constraint Violating Yak Guru
267 Posts |
 Posted - 2010-09-29 : 11:49:57
|
| Hi,I was testing out on my local sql server whether stopping SQL Service and then deleting transaction log file of a database would re-create the transaction log once sql service is started.The test failed.Now I cannot even access the SQL Server as the transaction log I deleted was for a database which is the default database for the user.Is there any way to fix this? I need to somehow get access to the server and the database, as I have some useful data there.Thanks a lot! |
|
|
Transact Charlie
Master Smack Fu Yak Hacker
3451 Posts |
Posted - 2010-09-29 : 12:21:22
|
| So you were testing something that you (I'm guessing) knew could be dangerous on a server that had a database that has some useful data on it.well, at least you've probably learned *something* from the experience.Backups? you've got backups right?Charlie===============================================================Msg 3903, Level 16, State 1, Line 1736The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION |
 |
|
|
GilaMonster
Master Smack Fu Yak Hacker
4507 Posts |
Posted - 2010-09-29 : 12:30:33
|
| SQL will not automatically recreate the log unless the database was cleanly shut down.Do you have another account that has a different default DB? The sysadmin's default DB should be master. If not, you can change the database you want to connect to. I can't remember how to do it in query analyser, play around with the options on the login dialog.--Gail ShawSQL Server MVP |
|
|
|
sql_er
Constraint Violating Yak Guru
267 Posts |
Posted - 2010-09-29 : 12:59:50
|
| Charlie - yeah it was not the brightest idea. We were having a critical issue on the production server where trans log grew to 55GB and filled up disc space. I had to act fast to test out a solution and at that time my local db did not matter that much [although I did not expect it would lead to this]. And of course I don't have any backups ...Gail - I don't have another account, unfortunately. I always connected using Windows Authentication. I think the default was 'master', but I changed it to user db to avoid changing it every time i logged in. I guess that was a mistake.I don't see what I can do on the query analyzer - everything is grayed out, unless I have a username/password to login.What am I missing?Thank you! |
|
|
|
GilaMonster
Master Smack Fu Yak Hacker
4507 Posts |
Posted - 2010-09-29 : 13:15:54
|
| I don't even have QA installed any longer. Isn't there an 'options' button or tab or something like that? This is on the login dialog box.The administrative logins should always have master as their default, to avoid this kind of problem when a user database is dropped/detached/not available.p.s. DO NOT EVER delete a transaction log. Especially if the DB in question is out of space. It's just begging for a bad situation made far, far worse.--Gail ShawSQL Server MVP |
|
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2010-09-29 : 13:31:34
|
| Give this a shot, run on the command line:osql -Sserver -E -dmaster -Q"exec sp_defaultdb 'login', 'master'"Change the server and login parts to match your settings. If it doesn't work and you know your SA password, try:osql -Sserver -Usa -Ppassword -dmaster -Q"exec sp_defaultdb 'login', 'master'" |
|
|
|
russell
Pyro-ma-ni-yak
5072 Posts |
Posted - 2010-09-29 : 13:40:02
|
| Your loacal problem aside, did you resolve the production problem? |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2010-09-29 : 14:20:05
|
| I'm too cautious to try this, but if I did I would rename the file, rather than delete it ... not much help suggesting your bolt the stable door - given that the horse has bolted already!What happens if you start the SQL Service in Single User mode - can you then just run a SQL command from that (I can't remember - maybe you still have to connect from QA/SSMS) |
|
|
|
sql_er
Constraint Violating Yak Guru
267 Posts |
Posted - 2010-09-29 : 14:39:03
|
| russell - yes the production problem got resolved. We took the machine out of the production pool. I then restarted the sql service. It took 30 mins or so, but once server came back up the transaction log was already truncated. I then had to shrink the log file a bit and all was back to normal.robvolk - thank you for the suggestion. I was able to change the default db for one of the users I found username/password for and was able to login to QA with that user. The only issue is that this user has very limited privs [just exec to some stored procedures], so I can't do much with it. I don't know my sa password. When I try to login with the user of my local computer, which is admin user, it says this user does not exist ...What user is used internally for windows authentication. Isn't it my local computer user? Thank you for the help! |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2010-09-29 : 14:53:29
|
| If you log on to Windows using ADMINISTRATOR (or equivalent user with Administrator rights) and then use Windows Authentication to connect to SQL Server you should be connected as SA - then you can changes passwords etc.Once you've got it sorted consider creating a SQL Login with SA rights and a ridiculously obscure password, write it down and lock it in the safe for another rainy day ... |
|
|
|
sql_er
Constraint Violating Yak Guru
267 Posts |
Posted - 2010-09-29 : 15:25:51
|
| Kristen - I think the problem is that I don't know what the 'sa' password is, so even though I was able to get to Query Analyzer I can't login as adminSince I am not able to login as admin to sql server, but am admin on my local machine, would it be possible to create admin mssql user using osql command line?Thank you! |
|
|
|
GilaMonster
Master Smack Fu Yak Hacker
4507 Posts |
Posted - 2010-09-29 : 16:35:59
|
| The first command line that robvolk gave you should log you in to SQL server as the local administrator.--Gail ShawSQL Server MVP |
|
|
|
sql_er
Constraint Violating Yak Guru
267 Posts |
Posted - 2010-09-29 : 16:57:27
|
| Gail - I thought the 1st command would just change the default db for the user name i provide to be master database.ThanksNegmat |
|
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2010-09-29 : 17:08:43
|
| What Gail meant was that the credentials it uses (-E, trusted connection) should connect you, if you log in to the server as the local Administrator account. The only reason it won't is if you removed the BUILTIN\Administrators group from your SQL Server. |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2010-09-30 : 04:08:12
|
| "I think the problem is that I don't know what the 'sa' password is, so even though I was able to get to Query Analyzer I can't login as admin"What you are referring to is a SQL login. If instead you login using Windows Authentication, instead of SQL login, then you can get around the fact that you don't know the SA password. Hence log in to Windows as ADMINISTRATOR and then connect to SQL Server (using Windows authentication - i.e. you won't be asked for a password at all)Similar to using the Trusted Connection using Rob's command-line route. |
|
|
|
|
With trans log deleted can no longer access server