Transaction Log deleted !

bogdanm

Hi everyone,

I have some questions about recovering a database with the transaction log file deleted. One of the "IT" guys thought that it was too big and deleted it. And the database was detached from the master db.
So, after a few hours of reading/searching i'v made a backup of the ndf and mdf file and renamed them on the actual location. I created a new database with the same name and locations of the files (data and log files). I turned sql off, replaced the new files with the old one (containing data), and started sql. With this, i could see the database instance, but it couldnt be accessed. So i turned the database to emergency mode and single user and started the DBCC CHECKDB. After 14 hours it's still running, but i cant see any resource consumption (processor, memory, space on hdd). In the books online, i have found that i can see the server requests in sys.dm_exec_requests. I see some processes started (in background) at the same time as i runned the DBCC CHECKDB, but i cant see the real command running and it's progress.
From this point i dont know what to do next. Wait (maybe a few days, db has 17.5GB) and see if anything it's happening or stop this process and rethink the approach?
And no, they don't have any backups (neither sql or nav).

Thanks a lot,

Noelkiller

Hello,
you can test the attachment procedure, when your right clic on the db directory. The log file is missing so delete the line of it.

David_Singleton

I agree with Noel Killer, the transaction log should not matter.

Just reattach the database.

Not that I recommend it in live, but I regularly just delete the Transaction log in local Development databases so I don't have to spend time compressing them. I never had a problem.
If you search Books on line and MSDN you will find articles about this where they say its possible but don't actually recommend it.

ara3n

as mentioned above, use attach the database. When the screen comes up with list of the other two files. Delete the log file from the list and SQL will create a new log file. Do NAV backup and create a new db and restore the backup.

I suggest also to look at backup process so that you can backup the log file and then truncate it so it doesn't grow.

Fisherman

ara3n wrote:

as mentioned above, use attach the database. When the screen comes up with list of the other two files. Delete the log file from the list and SQL will create a new log file. Do NAV backup and create a new db and restore the backup.

I suggest also to look at backup process so that you can backup the log file and then truncate it so it doesn't grow.

I never, ever recommend truncating the log in a production box. It should really only be done as a last resort. As a DBA, I've seen disasters happen because of it.

Typically, with SQL Server, I recommend a minimum of hourly transaction log dumps (backup). A transaction log backup and checkpoint will mark entries in the log as inactive. At the next full database backup, those entries will be removed from the log, but you have to make sure that your fenceposts are correct. If you don't set your database and trans log backups correctly, then you may end up invalidating the transaction log (i.e. - you can only restore to the last full database backup).

The transaction log is immensely important if you need to be able to restore to a particular point in time. Let's say, for example, that your server went bye-bye mid-day. If you had your last full backup and the hourly transaction logs on tape or external storage array (SAN), which you should, and if those transaction logs are valid (you didn't set up the backups incorrectly), then you can restore to the last transaction log backup. Assuming that you have hourly trans log dumps, that should be the last hour.

Assuming that you set it up correctly, the physical size of the translog shouldn't become an issue - an average rule of thumb is 25-30% of the size of the database. Truncating and shrinking that file also causes performance issues, because when the SQL Server runs out of room in the trans log, it has to expand it before it can write additional entries.

If you don't need to be able to restore to the latest hour, then just set the database to the Simple logging option. That ensures that the transaction log entries are rolled off whenever a backup is run.

Also - consider one other thing. MS does not charge a licensing fee for a warm standby SQL Server, so long as that server is not used for any other purpose than as a warm standby (no transactions, reporting, cubing, etc...), and so long as the number of processors on that server do not exceed those of the original. In the event of an outage on the original server, the warm spare can be activated, and the licensing transfers to it while the original server is down. That is important, because you can use replication or mirroring to protect against downtime, which limits (but doesn't eliminate) your dependence on the trans log for restoration.

bbrown

Fisherman wrote:

...Also - consider one other thing. MS does not charge a licensing fee for a warm standby SQL Server, ...

I hear this all the time. Microsoft's policy is "if it's running, it needs a license". The standby policy applies to a cold standby and is only applicable if you are covered by a volume liensing agreement. If you purchased licensing via the retail or OEM markets, this provision does not apply.

bbrown

Ignore my above comment, looks like MS put this back in place for SQL 2008. Trying to keep up with MS licensing policy is a full-time job in itself. This used to be more restrictive.

:oops:

Fisherman

Tell me about it. I have to call them constantly to verify licensing.

It's really funny when you call once, get one answer, call a second time, and get a completely different one. :?

I was actually told by a MS licensing agent that SQL 2005 licensing allows this as well, which I've also read in the SQL Unleashed book for 2005, but I haven't found it in the EULA.