October 14, 2009 at 11:11 am
I had warning on a database that log file is full, which is Primary Log #0. So, I decide to add another log file #1 at the different location to give room for the log process. Eventually I move the current log #0 to the new drive as well along with the new log file #1. I supply this command,
Alter Database mydb
add file (
name = mydb_log_1,
filename = 'E:\SS2K5_Log\mydb_log_1.ldf',
size = 50MB,
Filegrowth = 5MB
)
Then execute the following command to move the log #0
Alter Database mydb
Modify file (name=mydb_log_0, filename = 'E:\SS2K5_Log\mydb_log_0.ldf');
Here is my issue, I still got the warning log file is full and I did not see the log_1.ldf created at all. We are using SQL 2005 x64 on Windows 2003 R2 x64. Later, I had to stop the service to bring back the server again then I lost the whole database saying ldf not exists. What did I do wrong and Why the log file is not created? Any ideas please?
October 14, 2009 at 11:37 am
You can only have one Physical Log file, if you are getting warnings about your Log file running out of space then either backup the log file , which you should really be doing anyway, this wil mark space for re-use, or increase the size of the log file so that it has room to grow.
October 14, 2009 at 11:54 am
Log Files can be more than one according to BOL. At least one is a must.
October 14, 2009 at 12:16 pm
You can have more than one log file though unless you really do not have the drive space to accommodate the log there is no advantage to having more than one, you would have been better off just backing up the log or even truncating it.
The alter database does not actually move the log file, did you offline the database, move the log file to the new location and online it again?
---------------------------------------------------------------------
October 14, 2009 at 12:24 pm
Noep, I did not do that... I just added a file and modified the existing one. That did not take any effect. My biggest concern is why the physical file is not created by the alter database command?
October 14, 2009 at 12:26 pm
Also, in adding file you have added a database file, you should have coded
Alter Database mydb
add LOG file
(input parms)
when you get your database back remove this erroneous file
by
dbcc shrinkfile(mydb_log_1,emptyfile)
then actually remove it with
alter database mydb remove file mydb_log_1
Just in case You do have a backup of the database you can go back to before this process started?
---------------------------------------------------------------------
October 14, 2009 at 2:31 pm
steveb mentioned backing up the transaction log - are you? You asked:
Here is my issue, I still got the warning log file is full and I did not see the log_1.ldf created at all. We are using SQL 2005 x64 on Windows 2003 R2 x64. Later, I had to stop the service to bring back the server again then I lost the whole database saying ldf not exists. What did I do wrong and Why the log file is not created? Any ideas please?
What you did wrong was setting up the database to use full recovery model and did not setup a process to backup the transaction log on a regular basis. Thus, when you ran out of disk space for the transaction log, you attempted to fix the problem by adding a log file.
As George has shown, you didn't really add a log file - you added a data file (note: SQL Server does not care what the file extensions are). The next thing you did was alter where the log file is located - but you have not moved the log file to that new location.
So, when you tried to restart SQL Server - it cannot bring that database up because it can't find the log file in it's new location. To fix this problem, go to the old location and copy the log file over to the new location. After that, SQL Server will be able to start the database and you will be able to get your system back up and running.
Please review the article I link to in my signature about managing transaction logs. And, make sure you create a job to backup the transaction log on a regular basis (every 30 minutes is a good start).
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
October 14, 2009 at 2:51 pm
Thank you for the detail answer that helped me a lot, now I understand where did I drop the ball.
October 14, 2009 at 5:07 pm
sihaab (10/14/2009)
Thank you for the detail answer that helped me a lot, now I understand where did I drop the ball.
I just read what I posted and it may have come across a bit harsh. If that is the case, I did not mean it that way and was only attempting to outline what happened.
Anyways, were you able to get SQL Server started again? If not, is there anything else we can do to help you out?
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
October 14, 2009 at 5:54 pm
Well, we continue to google it and able to bring it back the database again without log files. It took 2 hours to bring the database from suspect more live. It was 700 GB not bad for two hours than restore.
BTW, no harsh at all... when some one made mistake he has to get yelled at... at the end we should make sure we all learn from each other. And you have taught me something today. Thanks a bunch.
This is from Paul worked fine... ( http://www.sqlskills.com/blogs/paul/ )
ok - in that case, becase your database wasn't cleanly shutdown you'll need to use the last resort (assuming you're using SQL Server 2005)
* create a database of equal size to the one you're trying to attach
* shutdown the server
* swap in the old mdf file
* bring up the server and let the database attempt to be recovered and then go into suspect mode
* put the database into emergency mode with ALTER DATABASE
* run DBCC CHECKDB (dbname, REPAIR_ALLOW_DATA_LOSS) which will rebuild the log and run full repair
Your database will be available again but you'll have lost data and the data won't be transactionally consistent - see the following blog posts:
* https://blogs.msdn.com/sqlserverstorageengine/archive/2006/06/15/632398.aspx
* https://blogs.msdn.com/sqlserverstorageengine/archive/2006/06/16/633645.aspx
* https://blogs.msdn.com/sqlserverstorageengine/archive/2006/06/18/636105.aspx
If you're on SQL Server 2000, you can still do this but you'll need to use the undocumented DBCC REBUILD_LOG at your own risk. If you're unsure about any of this, you should contact Product Support to help you.
Thanks
October 15, 2009 at 3:35 am
so stopping SQL server, moving the log to correct location and restarting did not work?
---------------------------------------------------------------------
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply