October 6, 2010 at 5:35 am
GilaMonster (10/6/2010)
Brandie Tarvin (10/6/2010)
I do have to ask this. You have full database backups scheduled, but it's not backing up the MDF file?How do you know it's not backing up the MDF file? What software is backing up the database?
Note that he is a new DBA. Probably worried about not having file-level backups of the files, or not understanding what a database backup is.
The underlined is exactly what I thought he was probably thinking.
October 7, 2010 at 3:51 am
Hi,
First, like to thank for all the advises given so far.
my scheduled backup is from Right click on DB->Task->Backup, and choose Database. The output file is having extension .bak
Thanks....
Brandie Tarvin (10/6/2010)
liewsb (10/6/2010)
We have a schedule job to do a full backup on the database, but not these two file, i.e. MDF and LDF files. How to create a transaction log backup??I do have to ask this. You have full database backups scheduled, but it's not backing up the MDF file?
How do you know it's not backing up the MDF file? What software is backing up the database?
October 7, 2010 at 5:38 am
liewsb (10/7/2010)
my scheduled backup is from Right click on DB->Task->Backup, and choose Database. The output file is having extension .bak
But you don't say what type of backup you're doing. There are several. Full, Differential, Partial, Filegroup, Transaction Log. Which one(s) are you choosing when you do this? FYI, the extension of the backup file is MEANINGLESS. I could put .abc as the extension of my backup file if I wanted to and SQL Server would still save the file. EDIT: Good practice is to use .bak (data backups) and .trn (log backups).
As a side note, you really need to automate this process. If you win the lottery and run off to Tahiti, will anyone else remember to back up your database?
Maint. Plans are an easy way of doing it for the first time users. T-SQL jobs are good too, if you're brave enough to try it. Open up Books Online and look up Backup Database for more details on T-SQL. Maintenance Plans in BOL can also help you.
October 7, 2010 at 10:47 am
Hi,
The backup is on Database and the mode set is FULL. This backup plan is scheduled to run on every monday, wednesday and friday @ 10p.m. Thanks.
Brandie Tarvin (10/7/2010)
liewsb (10/7/2010)
my scheduled backup is from Right click on DB->Task->Backup, and choose Database. The output file is having extension .bakBut you don't say what type of backup you're doing. There are several. Full, Differential, Partial, Filegroup, Transaction Log. Which one(s) are you choosing when you do this? FYI, the extension of the backup file is MEANINGLESS. I could put .abc as the extension of my backup file if I wanted to and SQL Server would still save the file. EDIT: Good practice is to use .bak (data backups) and .trn (log backups).
As a side note, you really need to automate this process. If you win the lottery and run off to Tahiti, will anyone else remember to back up your database?
Maint. Plans are an easy way of doing it for the first time users. T-SQL jobs are good too, if you're brave enough to try it. Open up Books Online and look up Backup Database for more details on T-SQL. Maintenance Plans in BOL can also help you.
October 7, 2010 at 11:05 am
Hi Gail and Brandie,
Thanks for your helps so far.
AS I just take other this DB job, I really need you all helps to slove this urgent issue now...
My current database is:
1. Database Name is HW_stats. Full Recovery model is set.
2. There is a database backup plan scheduled to run on every monday, wednesday and friday @ 10p.m. The output file is HW_stats.bak, size about 52GB.
3. Having HW_stats_Log.LDF file ( above 300GB) and HW_stats_Data.MDF file (around 70GB).
4. No transation log backups operation was done (I can't find any..)
As the disk space left only with around 30GB, I therefore need to shrink down (reduce) the size of these 2 files.
Can you all guide me through in detail what can I do now. Do a log backup or swing it to simple recovery mode ?? Pls advise. How can I proceed..?
Thanks... a lot and please help.
Regards,
3.
Brandie Tarvin (10/6/2010)
liewsb (10/6/2010)
We have a schedule job to do a full backup on the database, but not these two file, i.e. MDF and LDF files. How to create a transaction log backup??I do have to ask this. You have full database backups scheduled, but it's not backing up the MDF file?
How do you know it's not backing up the MDF file? What software is backing up the database?
October 7, 2010 at 11:30 am
Switch to simple recovery model. Run a checkpoint.
Once that's done, shrink the log to a reasonable size (NOT 1). Maybe start at 10 or 15 GB (as a rough guess)
Then decide, based on business requirements whether to leave the DB in simple recovery or put it back to full and start taking transaction log backups. You did read my article, didn't you.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
October 7, 2010 at 11:45 am
liewsb (10/7/2010)
Hi Gail and Brandie,Thanks for your helps so far.
AS I just take other this DB job, I really need you all helps to slove this urgent issue now...
Stop. Take a deep breath. Release slowly. Repeat.
You still have 30GB of space left so this issue is not urgent enough for you to panic.
Go back to beginning of this thread and read the article Gail linked. Then read it again.
Come back to this thread and tell us if you have any questions about that article. We'll help you the best we can. THEN, and only then, deal with the database log issue.
You have time to do this. Take the time to do it correctly.
October 7, 2010 at 11:31 pm
Hi, Brandie/Gail,
First, Really appreciate the helps from you all.
Yes, I would like to be sure before I start to do it.
I have read the article several times, here is what I have understand:
The recovery mode that you choose will affect the log and how it can be managed. It will determine how long log entries remain in the log.
Two different type of recovery model:
Simple Recovery model:
- Inactive log records can be discarded. It can be removed from the transaction log by performing checkpoint operation.
- Transaction log backups cannot be done.
- Recovery of database can only be done up until latest full or differential database backup.
Full Recovery Model:
- Inactive log records are retained in the transaction log until a log backup occurs.
- Periodic log backup must be done so that log file will not grow full.
- Database can be recovered to any time using combination of full, differential and log backups.
Log Chain:
- To restore database to a point in time, log chain must stretch unbroken from a full or diff backup to the point that the database needs to be recovered to.
- Log chain can be broken either by a log truncation, missing of log backup file or a switch to simple recovery mode. Once the log chian is broken, database cannot be restored past that point.
- Not to truncate the transaction log of a database.
- If log chain is broken, a new full or diff backup of database is needed to restrat the log chain.
What I have realised from the article:
I should decide what recovery model to use. I.e. Simple or Full. If full recovery is chosen, periodic log backups operation need to be done and at any point in time, do not truncate the transction log of the database. ( BACKUP LOG <HW_Stats> WITH TRUNCATE_ONLY)
I think the first thing I need to do now is to free up my disk space by shrinking the huge log file. And decide on which recovery model to be used from there..
I will adpot the advise from Gail, by:
1. Switch to simple recovery model. Run a checkpoint.
2.shrink the log to a reasonable size (NOT 1). Maybe start at 10 or 15 GB (as a rough guess)
3. Then decide on which model of recovery to be used from here..
Regards,
Brandie Tarvin (10/7/2010)
liewsb (10/7/2010)
Hi Gail and Brandie,Thanks for your helps so far.
AS I just take other this DB job, I really need you all helps to slove this urgent issue now...
Stop. Take a deep breath. Release slowly. Repeat.
You still have 30GB of space left so this issue is not urgent enough for you to panic.
Go back to beginning of this thread and read the article Gail linked. Then read it again.
Come back to this thread and tell us if you have any questions about that article. We'll help you the best we can. THEN, and only then, deal with the database log issue.
You have time to do this. Take the time to do it correctly.
October 8, 2010 at 12:58 am
Hi Gail,
Thanks for your helps ...
I will adpot your advice given. Before I proceed, I have few question to check with you. Sorry for bearing with me...
I have a SQL 2005 Server installed with SQL 2005 Standard Edition. Database Name = HW_Stats, Recovery model = FULL, backup plan set to backup database 3 times per week, backup type = FULL. (output file = HW_Stats_backup_2010062200.bak, around 54GB), LOG file = HW_Stats_LOG.LDF (around 390GB) and Data file = HW_Stats_Data.MDF (around 70GB).
See my question below:
my understanding is:
By switching it to simple recovery model, a checkpoint can be run. All inactive log records are removed from the transaction log.
To switch the DB to simple recovery model:
Right click on the Database->Propertise->Options->Recovery model-> change from Full to Simple->Ok. (Is this the right way?)
Run a checkpoint:
Questions: Sorry to ask that how to run a checkpoint on the database? What is the things to look up for after running checkpoint?
Shrink the log:
I think the file ID for Log file is 2 and file ID for Data file is 1. (Somehow check before)
Is this the right command to execuate:
DBCC SHRINKFILE(<HW_Stats_Log>, 2)
How can I specify the target file size (Say 15GB) in the command? Can you show me the correct SQL command to excuate?
Thanks and Appreciate your kindly helps, Gail..
Regards,
GilaMonster (10/7/2010)
Switch to simple recovery model. Run a checkpoint.Once that's done, shrink the log to a reasonable size (NOT 1). Maybe start at 10 or 15 GB (as a rough guess)
Then decide, based on business requirements whether to leave the DB in simple recovery or put it back to full and start taking transaction log backups. You did read my article, didn't you.
October 8, 2010 at 2:13 am
liewsb (10/8/2010)
By switching it to simple recovery model, a checkpoint can be run. All inactive log records are removed from the transaction log.
Checkpoints run in all recovery models. In simple the checkpoint will make most of the log inactive
Questions: Sorry to ask that how to run a checkpoint on the database?
Open a new query window. Type CHECKPOINT. Hit run.
Checkpoints run regularly so no worries about what it'll do
Shrink the log:
Is this the right command to execuate:
DBCC SHRINKFILE(<HW_Stats_Log>, 2)
How can I specify the target file size (Say 15GB) in the command?
Shrink file takes 2 parameters. The first is either the name or the ID of the file. The second is the target size in MB. The command you have there will shrink your log to 2 MB.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
October 8, 2010 at 3:20 am
Hi Gila,
Thanks for your prompt response... Really appreciated...
Sorry that some questions from me...
-You means only need to run checkpoint for one time?
-what you means by "Checkpoints run regularly"?
-Will it have impact to the database?
-How would I know that the task is complete?
-Will the checkpoint process fail? If yes, What I need to do?
As for Shrinking the log:
Is it that I only need to execuate the following command?
DBCC SHRINKFILE(HW_Stats_Log, 15)
Do I need do shrink the database or the MDF file?
so, Just to confirm, I will do this as follows:
1. Change the Database recovery model to simple.
2. Execuate checkpoint command;
3. Shrink the log:
DBCC SHRINKFILE(HW_Stats_Log, 15)
-- Regards,
GilaMonster (10/8/2010)
liewsb (10/8/2010)
By switching it to simple recovery model, a checkpoint can be run. All inactive log records are removed from the transaction log.Checkpoints run in all recovery models. In simple the checkpoint will make most of the log inactive
Questions: Sorry to ask that how to run a checkpoint on the database?
Open a new query window. Type CHECKPOINT. Hit run.
Checkpoints run regularly so no worries about what it'll do
Shrink the log:
Is this the right command to execuate:
DBCC SHRINKFILE(<HW_Stats_Log>, 2)
How can I specify the target file size (Say 15GB) in the command?
Shrink file takes 2 parameters. The first is either the name or the ID of the file. The second is the target size in MB. The command you have there will shrink your log to 2 MB.
October 8, 2010 at 4:00 am
liewsb (10/8/2010)
-You means only need to run checkpoint for one time?
Yes.
-what you means by "Checkpoints run regularly"?
-Will it have impact to the database?
-How would I know that the task is complete?
-Will the checkpoint process fail? If yes, What I need to do?
It's a system process that runs regularly. you cannot stop or disable it, you don't need to check anything about it.
Rather just forget I said anything about it.
DBCC SHRINKFILE(HW_Stats_Log, 15)
Do you really want to shrink the log to 15 MB?
Do I need do shrink the database or the MDF file?
NO!
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
October 8, 2010 at 5:23 am
Be aware that just typing in CHECKPOINT and executing it does not mean that it will execute immediately. SQL Server will run it immediately only if it's convenient to the engine. Otherwise, it will put it in the "to be run" queue and run it later.
I've seen it wait up to a full hour before the checkpoint actually runs. However, it'll probably make time to run it if you change the database Recovery Model.
Also, there are 3 recovery models, not 2. You missed BULK-LOGGED. Did you deliberately not want to consider that or did you not understand what that one is?
October 8, 2010 at 6:55 am
Brandie Tarvin (10/8/2010)
Be aware that just typing in CHECKPOINT and executing it does not mean that it will execute immediately. SQL Server will run it immediately only if it's convenient to the engine. Otherwise, it will put it in the "to be run" queue and run it later.
Errr....
From BoL:
Writes all dirty pages for the current database to disk. Dirty pages are data pages that have been entered into the buffer cache and modified, but not yet written to disk. Checkpoints save time during a later recovery by creating a point at which all dirty pages are guaranteed to have been written to disk.
...
In general, the amount time required for a checkpoint operation increases with the number of dirty pages that the operation must write. To minimize the performance impact on other applications, SQL Server by default adjusts the frequency of writes that a checkpoint operation performs. SQL Server uses this strategy for automatic checkpoints and for any CHECKPOINT statement that does not specify a checkpoint_duration value. Decreasing the write frequency increases the time the checkpoint operation requires to complete.
It may not finish immediately, depending on the amount of dirty pages and the requested checkpoint duration, but it should start straight away.
I've often run checkpoint and then immediately queried the tran log and seen the commands LOP_BEGIN_CKPT and LOP_END_CKPT sitting there, or at least LOP_BEGIN_CKPT.
Got a demo or reference?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
October 8, 2010 at 7:01 am
GilaMonster (10/8/2010)
It may not finish immediately, depending on the amount of dirty pages and the requested checkpoint duration, but it should start straight away.Got a demo or reference?
Only personal experience. I'm not sure if I can replicate the conditions under which it occurred either, because I never tried to track them.
EDIT: Of course, now that this subject has come up. I'll write down everything the next time it happens so I can follow the trail.
Viewing 15 posts - 16 through 30 (of 44 total)
You must be logged in to reply to this topic. Login to reply