Giving pain to DBA
-
I have a production database of 145 GB .I take backup once a week and do deffriential backup 3 times a week and Transaction log backup every 2 mins.
Now the problem is that i can have a very huge log file 200 GB.
I cannot shutdown the server becasue it is running 24*7 mode.
Can anybody suggest how to switch the log file and delete the old log file.
Dont answer in bookish language . if anybody did it practically then pls reply.
from
Mohd Sufian
-
HI
are these transaction logs or just general logs.
you could write a vb scrit that zips up old logs (if you want to keep them) and puts them onto another server
or set the "delete files older than" option for either tranaction logs or other logs..
hope that helps
-
Hi,
Nice to see ur reply.But can u tell me how many log files by default sql server create for a database.
can u pls tell me how to set delete files older then on production server.
As i mentioned the server is running 24*7 mode so i can not bear attaching and detaching of database.
thanx
-
Check out this link:
"Dynamically shrink the transaction log with a stored procedure"
http://codetempest.com/node/10
It shows you how to create a stored proc in your master database that you can then schedule as a regular job to keep tranaction log size under control
G. Milner
-
Sorry but I have to be "bookish". If you can't read Books Online you should put up with "bookish" replies.
What recovery mode is the database set to?
How often do you you backup the transaction log?
What sort of maintenance do you currently run?
Read the Books Online title "Transaction Logs"
--------------------
Colt 45 - the original point and click interface
-
may i know what is the recovery mode for your database?
since you have transaction log backup every 2 mins, you can change your database recovery mode to Simple.
to shrink ur log file, u can use backup the transaction with_truncate_only.
and then, just run DBCC shrink file to shrink the transaction log.
Leo
-
Thanx for all ur replies.
But i want that u all should have a good look at the question again.
I havent asked for shrinks.
Have u all tried shrinking a logfile on production server .if no then pls try then u will come to know what u will loose or gain.
secondly i asked that is there any way to forcelly switch the current archive file to other.
for those who told me to read BOI thanx .Can u pls tell me from where is the description for switch logfile in BOI of no then pls before replying to the questation think and if dont know the answer wait for the answer.
sufian
-
Mr.Phill Carter,
Thanx for suggesting to read BOI.But i suggest u that before replying any question first read it carefully then reply.Ok
If i had asked the question then i might have studied the BOI and when i did not find any clue then i posted the question.
If u had did practically then reply.
or if it is not possible then pls tell me with reason
sufian
-
Sufian,
u can try this code:
backup log db_name with truncate_only
dbcc shrinkfile (logfile_name, size)
u can truncate the log even when ur database is online.
u can put any parameter for the size. its unit is in MB.
so, let say u put dbcc shrinkfile(mydb_log, 2), ur log file will become 2MB after u run the code.
i thought i have sent u this code just now. anyway, u can try.
Leo
-
To answer your question, there is only one log file and you can't switch it. The solution here is to truncate the log file and then shrink the file itself to release the physical space on the drive.
There is no danger to do this.
-
Again:
Check out this link:
"Dynamically shrink the transaction log with a stored procedure"
http://codetempest.com/node/10
It shows you how to create a stored proc in your master database that you can then schedule as a regular job to keep tranaction log size under control.
G. Milner
G. Milner
-
Guys!
This is not the way to go! If he is doing a log backup every 2 minutes then of cours his recovery mode shouldn't be set to simple. Otherwise it makes no sense to make to log backups as the log enties will be freed up at each checkpoint!
Yvan: You are not quite right. You can have more the one log file, you have just create it for example by EM. But you are right you cannot switch the usage of the logfiles SQLServer is managing it by his own. On the other side it doesn't make too much sense to have several log files as the log is written and read sequencially and the advantage of multiple files (on eventually multiple spindles) will not be taken into consideration.
So there is no danger to shrink the files of a production database, as I'm doing it for much bigger databases as you have mentioned.
Just one think to add to Leo's comment: If you specify the size of the file to be shrinked, the size specified cannot be smaller the the initial size given when you have created your database
There will be some performance issues, but not too dramatical. As you have a log backup every 2 mins you can go straight to the dbcc shrinkfile
Bye
Gabor -
thanks, Gabor.
my previous SQL Server database was > 900GB.
it is a database used by a factory. of course, it needs to be up 24 x 7.
however, my transaction log is about 80-90 GB only.
if a transaction can grow up to 200GB, i think the application that is using it causes a lot of unusual log to the database. if not, the backup job should have problem.
i don't think 2 mins can have 200GB transaction comes in to the database while the database size is only < 200 GB.
some where is wrong.
Leo
-
Hi there!
Performing the truncate log works for me, here is an example:
backup log <database name> with truncate_only
For me, I run this as a job after my database maintenance plans run. You can run this inside a dts procedure or a regular job stored procedure and assign it a schedule! One less headache!
Our database kept running out of space because of this problem.
Good Luck!
Maurice Ray
Database Administrator
Computer Associates
-
Maurice,
Take care by using the backup log <database name> with truncate_only command because you will loose the possibility to restore the data if your database is not in a simple recovery mode (and if it is, then the transaction log backup makes no sense)
Otherwise I agree what Leo stated. Something is wrond with this database is the log backup is REALLY running every 2 mins, then only a huge insert/delete/update or indexdefrag can make the log size as big!
Sufian: Is this the case?
Anyhow! You can go ahead with the shrink file without any danger!
Bye
Gabor
Viewing 15 posts - 1 through 15 (of 107 total)
You must be logged in to reply to this topic. Login to reply