database stack in recovery mode

my 140gb database stack in recovery mode after I restarted sql server service. Log file were truncated few hours just the day before and when I checked now log files are 44gb.
any idea of what I can do to get it on line?
I have a feeling it can take more than one day to rollback all transaction and this is my production database.
backup didn't finish as I only have previous day backup and restore may take more time as log file on a backup maybe as big as current.
is there a way to truncate the log. sql service was restarted  off hours.
leop1212Asked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Vitor MontalvãoMSSQL Senior EngineerCommented:
Unfortunaly you can't do nothing but wait.
It's seems to me that the database as an high number of virtual log files (VLF) and the first thing to do after the database comes online is to reduce the number of VLF's.
leop1212Author Commented:
should I kill any processes to speed it up?
I had to restart sql service as I was try to copy another small db 600mg to a new db.
I ran backup of source created an empty  target db and ran a restore from a backup file of source. restore 100% but target DB stack in restoring (log files is source is only 200meg)
so I restarted SQL server (and that is my main DB is in recover now)
so now I have 3 problems
1. db in recovery
2. target db in restoring
3. can't create new database as model db is not available ..

I've created target db on another SQL server created new target and try to restore db on second box with same results. restore 100% but new database is still says restoring
so should I kill any spids to speed up main db recovery and clear model db ....?
why my small db stack in
Vitor MontalvãoMSSQL Senior EngineerCommented:
There's nothing you can do to speed the process. Recovery is the last operation from a restore process.
You can check the number of VLF's in the source database by running this command: DBCC LOGINFO

Weird that you don't have a model db. Did you delete it?
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

leop1212Author Commented:
no. model db is there I think it just locked by one of the speeds I had ths problem before and when I killed all speeds I got it working again
but why is my small db restore is not working and still says "restoring" even on the second sql server
which looks like transaction log again?
can I just take source db offline copy db and log and rename them and then reattached both?
Vitor MontalvãoMSSQL Senior EngineerCommented:
but why is my small db restore is not working and still says "restoring" even on the second sql server
Check for locks.

can I just take source db offline copy db and log and rename them and then reattached both?
Yes, that will be a faster way but remember to reduce the number of VLF's before performing this action.
leop1212Author Commented:
how do I reduce num of vlf
Vitor MontalvãoMSSQL Senior EngineerCommented:
First of all you need to shrink the transaction log to the minimum size possible (1MB). For that you may need to perform some transaction log backups and shrinks after each transaction log backup. Be sure that no one is connected to the database so you won't have the transaction log growing during the process.

After that configure the transaction log file to grow in 8GB chunks so it will reduce the number of VLF's (larger but fewer).
leop1212Author Commented:
I have my db initial sync with salesforce. that maybe the cause of huge logs
Vitor MontalvãoMSSQL Senior EngineerCommented:
How may rows returns the DBCC LOGINFO command?
leop1212Author Commented:
FileId      FileSize             StartOffset          FSeqNo      Status      Parity CreateLSN
----------- -------------------- -------------------- ----------- ----------- ------ ---------------------------------------
2           253952               8192                 414         0           128    0
2           262144               262144               417         2           128    0
2           262144               524288               415         0           128    186000000012800011
2           262144               786432               416         0           128    187000000014400024

(4 row(s) affected)
leop1212Author Commented:
(3449 row(s) affected)

DBCC execution completed. If DBCC printed error messages, contact your system administrator.
Vitor MontalvãoMSSQL Senior EngineerCommented:
So, it is 3449 rows or only 4?
And what's the size of the transaction log?
leop1212Author Commented:
I still see my main DB without recovery on my workstation via old MSMS connection
should I try to truncated log files with
BACKUP LOG mydb WITH TRUNCATE_ONLY
DBCC SHRINKFILE( mydb_log, 2)          

or

use mydb
ALTER DATABASE mydb
SET RECOVERY SIMPLE;
GO

-- Shrink the truncated log file.
DBCC SHRINKFILE (mydb_log,500);
GO

-- Reset the database recovery model.
ALTER DATABASE mydb SET RECOVERY FULL;
GO
leop1212Author Commented:
4 was on the master
mydb is 3449
leop1212Author Commented:
transaction log is 44gb
was truncated last night to 500mg and now 45gb
Vitor MontalvãoMSSQL Senior EngineerCommented:
Changing the recovery model to simple will be faster but don't forget that you'll break the backup chain and a full backup must be performed after set the recovery model to full.
The decision is yours.
Vitor MontalvãoMSSQL Senior EngineerCommented:
was truncated last night to 500mg and now 45gb
Means that you have a lot of things running out on the database.
If you set the grow of the transaction log to 8GB it will create 16 VLF's of 512MB each so when the file reaches 48GB it will have 96 VLF's instead of 3449. Can you see the difference?

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
leop1212Author Commented:
thank you very much for your knowledge and fast respond.
for some reason SQL box froze up shortly and I had to cold boot it and db came back.
i will post another question on backup/restore strategies and would be nice if you can answer it too
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2008

From novice to tech pro — start learning today.