Problems with log - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

Data Corruption Issues

Problems with log

Author

Topic

faulty
Starting Member

8 Posts

Posted - 2009-09-06 : 12:47:24

Hi friends
I'm running shareportal and the drive where db was located reached the size limit. The database becomes non-operational. File log is huge. the last backup of this clients is about 1 year old. I'm running Sql server 2008.
I've tried attach mdf without ldf file unsuccessfuly
Please help me!

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2009-09-06 : 13:09:51

Do you have access to the database?
Switch recovery model to 'simple' and sql server 2008 will automatically truncate the log.

No, you're never too old to Yak'n'Roll if you're too young to die.

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts

Posted - 2009-09-06 : 15:47:52

Please read through this - [url]http://www.sqlservercentral.com/articles/64582/[/url]

I do hope that you haven't deleted the transaction log. If you haven't, please attach the DB with the log and either set the DB to simple recovery or start taking log backups. If you have, see this blog post on hacking the DB back into the server. Note, may result in data loss.
http://sqlinthewild.co.za/index.php/2009/06/09/deleting-the-transaction-log/

Why is the only backup a year old? Is the data so unimportant that no one cares about losing the whole lot?

--
Gail Shaw
SQL Server MVP

faulty
Starting Member

8 Posts

Posted - 2009-09-06 : 17:56:13

quote:
Originally posted by webfred

Do you have access to the database?
Switch recovery model to 'simple' and sql server 2008 will automatically truncate the log.

No, you're never too old to Yak'n'Roll if you're too young to die.

Thank's for the answer
I really didn't know if i have access to database. It seems to be attached to SQL, but i can't do some operations with it. Have a different icon in the database name and can't expand it.

The wss_content2k8 is the only backup i can restore and it's about 1 year old

faulty
Starting Member

8 Posts

Posted - 2009-09-06 : 18:06:08

quote:
Originally posted by GilaMonster
I do hope that you haven't deleted the transaction log. If you haven't, please attach the DB with the log and either set the DB to simple recovery or start taking log backups. If you have, see this blog post on hacking the DB back into the server. Note, may result in data loss.
http://sqlinthewild.co.za/index.php/2009/06/09/deleting-the-transaction-log/

Why is the only backup a year old? Is the data so unimportant that no one cares about losing the whole lot?

--
Gail Shaw
SQL Server MVP

but how can i attach the db if i haven't one? I only have that... "thing" that sql server interface shows. If i stop sql i can manage the files. If i copy them to another location and try to attach db with different name, it returns an error. "One or more files do not match the primary file of the database."

Yes, no backups, big stupidity. admins don't care. i'm just some guy who's trying to solve the problem.

Sorry my bad english. i'm portuguese.

thank's in advance

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts

Posted - 2009-09-07 : 03:20:38

So the DB is attached to SQL? If it wasn't, you wouldn't see an icon at all for it.

Please run the following and post the results?
SELECT name, state_desc, is_cleanly_shutdown, is_in_standby, recovery_model_desc, log_reuse_wait_desc FROM sys.databases where name LIKE 'WSS%'

Can you please explain, step by step, exactly what you did to the DB?

--
Gail Shaw
SQL Server MVP

faulty
Starting Member

8 Posts

Posted - 2009-09-07 : 05:56:12

quote:
Originally posted by GilaMonster

So the DB is attached to SQL? If it wasn't, you wouldn't see an icon at all for it.

Please run the following and post the results?
SELECT name, state_desc, is_cleanly_shutdown, is_in_standby, recovery_model_desc, log_reuse_wait_desc FROM sys.databases where name LIKE 'WSS%'

Can you please explain, step by step, exactly what you did to the DB?

--
Gail Shaw
SQL Server MVP

yes, i think db is attached but not working.
here the results

I think that when the error occurred admins did a disk cleanup and move some files to other disks. I think they did not touch in the db.

Please, help me :/

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts

Posted - 2009-09-08 : 03:22:36

Recovery pending means there's a problem with the log.

Check with the admins if they moved the ldf file while SQL was stopped. Also, please check the Error log, there will be references to this database. Post them here.

--
Gail Shaw
SQL Server MVP

faulty
Starting Member

8 Posts

Posted - 2009-09-08 : 06:42:36

yes, they move sql files to another disk and then try attach. didn't work. renamed db, then create new db with original name and switch db files.

The log reports that log have problems and should be restored froam a backup ou rebuilt

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts

Posted - 2009-09-08 : 10:56:01

Post the exact messages from the log please. A description of what they say does not aid in debugging.

--
Gail Shaw
SQL Server MVP

faulty
Starting Member

8 Posts

Posted - 2009-09-08 : 14:30:41

how can i do that? where's error log?

faulty
Starting Member

8 Posts

Posted - 2009-09-08 : 16:26:26

quote:
Originally posted by faulty

how can i do that? where's error log?

Now, i stoped SQL Server, copy both mdf and log to an external disk and i installed sql 2008 in my box. What's the easy / best way to solve this? I'm a bit desperate :(

thanks

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts

Posted - 2009-09-08 : 20:39:20

Let's not move the DB elsewhere, there's a good chance it won't reattach. Safest is to try and fix it where it is.

The operations shown there (emergency mode and a repair) are the correct ones. It looks like the repair is failing because the expected folder doesn't exist.
Does the location D:\BD\Sharepoint\ exist? If not, create it, set the DB to emergency and run repair again.

--
Gail Shaw
SQL Server MVP

faulty
Starting Member

8 Posts

Posted - 2009-09-10 : 14:35:43

done! it's repaired!
Thank you GilaMonster! If you were here i'd buy you some beers! ;-)
Many thanks!

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts

Posted - 2009-09-11 : 03:42:24

Excellent

Now, set up some regular backups and have a word with the 'admins' about moving SQL's files around.

--
Gail Shaw
SQL Server MVP

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources