September 24, 2009 at 9:20 am
I use SQL Server 2005 and suddenly one of my drive just have around 10% of space left. so i know that it is a very good idea to take a backup then shrink it. but i do not have time to do a full backup since it will take hours and i think taking a log backup would be a good idea. so i need a command that will take a transaction log backup.
Thanks.
September 24, 2009 at 9:21 am
What is your DB's recovery model?
September 24, 2009 at 9:22 am
It is full and i can never change it to simple since it is a Prod database.
September 24, 2009 at 9:26 am
The drive that is getting full, Does it have the Log file or the data file? Do you have Transaction log back ups running? Do you have any back up plan?
-Roy
September 24, 2009 at 9:28 am
actually that drive only holds log files only. we have seperate drives for log and data files. and we take diff backups every day and once a week full backups. that's all we do.
September 24, 2009 at 9:29 am
BACKUP LOG yourDB TO DISK = 'D:\SQLBackups\yourDB.trn'
DBCC SHRINKFILE ('yourDB_log', 200) WITH NO_INFOMSGS
You should run it as one single transaction
If you don't have a log shipping or replication running on your database,
in case of emergency use this:
BACKUP LOG yourDB WITH NO_LOG
DBCC SHRINKDATABASE (yourDB, TRUNCATEONLY)
If you run the second code don't forget to take a full backup immediately after that.
September 24, 2009 at 9:31 am
Put a DR Plan to do transaction log shipping for the DB. Once Transaction log is taken, it will make sure that it releases the space used in the log file. Keep in mind that the file size will not get reduced automatically. But it will not grow any bigger if you keep taking regular transaction log back ups. Shirniking the Database or Log is a very bad idea.
-Roy
September 24, 2009 at 9:35 am
Thanks a lot.
September 24, 2009 at 9:38 am
so one more question
what will the below command do
BACKUP LOG P003 WITH NO_LOG.
where will the log file save.
September 24, 2009 at 9:43 am
Shirniking the Database or Log is a very bad idea
Shrinking The Log file in case of emergency situation is the only idea.
And it is very common when even having great backup strategy with tr.log backup people got an emergency situation with tr.log space issue. It is enough to have just one application query with the bad design to create this situation.
September 24, 2009 at 9:46 am
espanolanthony (9/24/2009)
so one more questionwhat will the below command do
BACKUP LOG P003 WITH NO_LOG.
where will the log file save.
It will be saved nowhere.
As I said run this script in critical situation only, and SQL Server will create new empty liog file for you.
BACKUP LOG yourDB WITH NO_LOG
DBCC SHRINKDATABASE (yourDB, TRUNCATEONLY)
Again, don't forget to take full backup after that, this script brakes LSN chain.
September 24, 2009 at 10:58 am
If your database is using the full recovery model, you really need to setup transaction log backups. This will keep your transaction logs from filling up your log drive. If you have no need for point in time recovery of your databases, then you should have your databases using the simple recovery model.
Full and Differential backups do not mark the space in the transaction log files for reuse. this is only done by transaction log backups.
September 24, 2009 at 11:16 am
If your database is using the full recovery model, you really need to setup transaction log backups.
That is absolutely correct.
This will keep your transaction logs from filling up your log drive.
That is not exactly correct.
This will only TRY to keep your transaction logs from filling up your log drive and will truncate a log on certain time (for Full recovery model) or at checkpoint (for Simple recovery model).
That means if you have badly designed SELECT or UPDATE statement, which runs as one single transaction against 1 million rows database - your log file will not be truncated automatically until transaction is completed - even in case of Simple Recovery model. But very often your drive space disappears before that moment, especially if your log file configured for unrestricted growing.
So, I am just trying to say that yes, we must have a tr.log backup job as a part of backup strategy in case of Full recovery model. But regardless of backup strategy we should be ready to shrink tr.log file in case of critical situation.
September 24, 2009 at 11:35 am
Curious, how does a SELECT statement fill a transaction log? I can understand UPDATE, DELETE, and INSERT; but SELECT?
September 24, 2009 at 12:02 pm
Lynn,
Curious, how does a SELECT statement fill a transaction log? I can understand UPDATE, DELETE, and INSERT; but SELECT?
If it is only one process running against this DB, it will not fill tr.log, because for SELECT statement truncount =0. You are right. But this is unreal situation to have only one process.
In real life we have the multiple processes running against a database simultaneously.
So, for example we have a badly designed SELECT statement, (something like a big report), which runs for hours. This report can easily create locks on several tables of course for hours as well.
In this case any UPDATE/DELETE/INSERT process will try to modify one single row in these tables for hours, filling a transaction log.
Viewing 15 posts - 1 through 15 (of 23 total)
You must be logged in to reply to this topic. Login to reply