November 4, 2007 at 12:25 am
One of my databases has a rather large Log file ~12GB compared to data ~1.5GB. Before I started managing this DB there were a lot of jobs that are not enabled anymore, recovery mode was not Simple, and it may be set to simple. I think a fresh *.ldf would be fine.
I hear if a Log is big is supposed to be like that. But, I want to start a new log and from there let it grow.
I tried Detach db, and it seemed to me there were no connections, but I could not detach because system kept saying there were connections (I could not see any Lock, or any Connection using Profiler, or with exec sp_who 'active'). I suspect db is linked to another server, maybe that is the cause.
I have a few concerns after reading BOL on detach/attach.
Can anyone tell me if can really detach this large db and create a new Log file using exec sp_attach_single_file_db? Are there any risks like loss of data or logins after doing a further attach?
November 4, 2007 at 12:40 am
Transaction log holds all your transactions since last time the checkpoint was issued. These are all the queries u executed on that database. so in case of crash u will not be able to recover ur transactions or data since last Diff backup or Full backup which ever u have taken. If u have put ur database in simple mode than u can just shrink log files after refresh.
November 4, 2007 at 12:44 am
even if u dont see anything even on sp_who2 u should take following into consideration. close ur enterprise manager or if ur Database is highlited since u touched it then click on some other database on ur enterprise or management studio. In ur query analyzer, Drop down any other database other that this database whose log file ur trying to detach.
November 4, 2007 at 1:03 am
Hi,
If you are going to set recovery model to simple then try the following
1.) You can shrink your db logfile so that the size can be reduced.
Shrinking the Transaction Log -- This is applicable for SQL2005 also
If you are going to keep the recovery mode in full then try the following
1.) To add new log file to the database
ALTER DATABASE yourdbname
ADD LOG FILE
(
NAME = logfilename,
FILENAME = "Physical path to store the log file",
SIZE = 5MB,
MAXSIZE = 100MB,
FILEGROWTH = 5MB
);
GO
2.) Refer the below link to stop transaction log file growth
How to stop the transaction log of a SQL Server database from growing unexpectedly
How to avoid using shrink in SQL Server 2005?
else
Take log backup at a regular intervals
You can't detach the db until you close last connection to the db.
"sp_attach_single_file_db" with this procedure you can't add one more log file to the db. This procedure is to attach the db when data file alone exists so that it will create a new log file and attach the db.
Regards..Vidhya Sagar
SQL-Articles
November 4, 2007 at 1:29 am
Mayank Khatri (11/4/2007)
Transaction log holds all your transactions since last time the checkpoint was issued. These are all the queries u executed on that database. so in case of crash u will not be able to recover ur transactions or data since last Diff backup or Full backup which ever u have taken. If u have put ur database in simple mode than u can just shrink log files after refresh.
you did not answer any of my questions
November 4, 2007 at 1:35 am
Hey,
Please review Vidhya Sagar's steps, as this is how you can create a new log file.
Thanks,
Phillip Cox
November 4, 2007 at 1:55 pm
I think I relied ur question, maybe i was not clear enough.
xintanaka
Q1)
Can anyone tell me if can really detach this large db and create a new Log file using exec sp_attach_single_file_db? Are there any risks like loss of data or logins after doing a further attach?
U will not loose ur current data or logins with freash ldf. Reason: Like I said it holds transactions so in case u want to recover those old transactions, or data u will not be able to do that.
Q2) tried Detach db, and it seemed to me there were no connections, but I could not detach because system kept saying there were connections (I could not see any Lock, or any Connection using Profiler, or with exec sp_who 'active'). I suspect db is linked to another server, maybe that is the cause
U couldn't detach even after seeing in ur sp_who2 because of the reason i mentioned in my second post.
Q3) Getting empty transaction log file
a3) That can be done using recovery simple and shrinking ur file as i mentioned in first post. With shrinking the file i meant using DBCC SHRINKFILE
November 5, 2007 at 12:26 pm
Thanks vidhya sagar this is what I was looking for.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply