January 16, 2009 at 6:46 pm
We have a custumerDB and its properties are
size:14399.06 MB
space available:4652.36 MB
customerdb.mdf : 4.59 GB;
customerdb.ldf : 9.46 GB;
I took backup of the customterDB(using Litespeed) and the .bak file size is 15.6MB..
and I tried to restore the customerDb from this .bak on the different server and everything is fine..
BUT,My question is WHY my .bak size is 15.6 MB?
-----------------------------------------
Its a totally different question,doesnt related to above question.
2)How can I un-install the hot fix installed on sql 2005?
January 16, 2009 at 7:04 pm
Can you run sp_spaceused in the database that you are backing up and post the output here (don't have to include the database name)?
Thanks.
David
@SQLTentmaker“He is no fool who gives what he cannot keep to gain that which he cannot lose” - Jim Elliot
January 16, 2009 at 7:29 pm
database_namedatabase_sizeunallocated space
XXXXXXX 14399.06 MB4652.36 MB
reserveddataindex_sizeunused
50000 KB45568 KB1128 KB3304 KB
January 16, 2009 at 7:39 pm
It looks like there is very little data in the database based on this output so, it doesn't surprise me that the backup is pretty small. My guess is that if you run DBCC SQLPERF (LOGSPACE) you will find that the log for this database is also pretty empty. Being that this is the case your actual backup should be pretty small.
The most important thing is that you were able to use this backup to restore somewhere else and verified that it works. That is the best test.
Hope this helps.
David
@SQLTentmaker“He is no fool who gives what he cannot keep to gain that which he cannot lose” - Jim Elliot
January 16, 2009 at 10:25 pm
Here is the result when the run DBCC command
Database NameLog Size (MB)Log Space Used (%)Status
xxxxxxxxxxxxxx9697.867 96.01259 0
(I am confused little bit)..That means when we take a backup it only consider .mdf file size ?
----
Suppose if .ldf is corrupted of test1 DB.. Then can we take ldf file of some other DB say
test2 db and backup the test1 db ?
January 17, 2009 at 1:01 am
kiransuram19 (1/16/2009)
(I am confused little bit)..That means when we take a backup it only consider .mdf file size ?
The mdf and enough of the log to be able to restore the database to a consistent state.
Suppose if .ldf is corrupted of test1 DB.. Then can we take ldf file of some other DB say
test2 db and backup the test1 db ?
No. You can never just replace the log with that of another database and expect things to work. You wouldn't even be able to bring the database online.
Not directly related to your question, but that log is big and full. What recovery model and are you backing the log up?
Please read through this - Managing Transaction Logs[/url]
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
January 17, 2009 at 1:54 am
kiransuram19 (1/17/2009)
1) How can I Truncate T log so that it can only delete the in-active transactions in the log fileand this should
Did you read the article that I linked to?
Simple answer - regular log backups.
Please tone that font down. It reads like you're shouting at me.
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
January 17, 2009 at 2:04 am
Sorry for that font size my intention is to stress that point thats it..
Thankyou verymuch for replies..
January 17, 2009 at 2:46 am
__ I got this answer from Books Online
NO_LOG | TRUNCATE_ONLY
Removes the inactive part of the log without making a backup copy of it and truncates the log by discarding all but the active log. This option frees space. Specifying a backup device is unnecessary because the log backup is not saved. NO_LOG and TRUNCATE_ONLY are synonyms.
_____
GilaMonster --- This above is the answer I was looking for..
Thankyou for your time and thankyou for pointing to the article( Managing logs) .
January 17, 2009 at 4:11 am
kiransuram19 (1/17/2009)
NO_LOG | TRUNCATE_ONLYGilaMonster --- This above is the answer I was looking for.
No, it's not. You stated (in the post that you deleted) that you don't want to break the log chain. The statement you quoted does exactly that. You will not be able to take log backups after that and you will have no ability to restore to anything other than the last full backup.
If you're intending to just truncate the log, set the DB into simple recovery and leave the log alone. That's only an option if losing all data after the last full backup is acceptable to you and your business.
If you need point-in-time recovery (which is the whole point of full or bulk-logged recovery) then you have to take log backups.
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
January 17, 2009 at 9:54 pm
Thankyou very much-- Gail Shaw.. I got the point Now..
Still I have lot of confustion and lot my friends as all as we are new to database part
1)When we backup a T-log does it have both active and In-active transactions? If yes ,what is the purpose of In -active transactions ?(Since In-active trans are already written in mdf )
2)In simple mode suppose the ldf is 80 gb and got truncated by sql server checkpoint process,
then space will get back to Operating system or I have to shrink the log file?
3) In Full Mode after taking t-log backup does OS get space back automatically or we have to shrink it?
4)You mentionted that when we backup the T-log with Truncate only or No_log then log chain breaks and I agree with this point.
***
Let say we have a TestDB where we have Full backup scheduled every day and T-logs scheduled every hour ..,Suppose If 16 th hour T-log got corrupted( may be any reason ) and then log chain breaks ..then the 17 th hour will contain the chain form last FUll backup or Last T-lOg backup?
I know that T-log will have changes from previous last backup(May be FUll/Diff/T-log).. But what happens in the above scenario?
5)Any idea how to uninstal a HOT-FIX ?
Any help is really appreciated
Thanks- Kiran
January 18, 2009 at 2:22 am
kiransuram19 (1/17/2009)
1)When we backup a T-log does it have both active and In-active transactions? If yes ,what is the purpose of In -active transactions ?(Since In-active trans are already written in mdf )
To restore the DB if needed and to get it to the point of failure. The whole reason that you're backing up the log at all
2)In simple mode suppose the ldf is 80 gb and got truncated by sql server checkpoint process,
then space will get back to Operating system or I have to shrink the log file?
It will not be released and you should not shrink it unless you know the log will never get that big again. If the log has to grow, it slows down all operation in the DB for the duration of the grow operation. Additionally, repeated shrink/grow causes fragmentation at the file system level, which is hard to fix.
3) In Full Mode after taking t-log backup does OS get space back automatically or we have to shrink it?
See above
Let say we have a TestDB where we have Full backup scheduled every day and T-logs scheduled every hour ..,Suppose If 16 th hour T-log got corrupted( may be any reason ) and then log chain breaks ..then the 17 th hour will contain the chain form last FUll backup or Last T-lOg backup?
Do you mean that the log itself is corrupt, or that the backup that you took is corrupt?
In the first case, the DB will go suspect, so the log chain is the least of your worries. In the second, the next log backup will, as always have the log records since the previous transaction log (the corrupt one). Since one of the chain is corrupt and unusable, any log backups taken past that point are useless, as they cannot be restored.
I know that T-log will have changes from previous last backup(May be FUll/Diff/T-log).. But what happens in the above scenario?
Tran lock backups contain the log records since the last transaction log backup. Not fill or diff.
The only time a tran log backup's changes are since a full, is when it's the first log backup in a chain. All others will be since the previous tran log backup.
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
January 18, 2009 at 2:38 am
Do you mean that the log itself is corrupt, or that the backup that you took is corrupt?
I mean t-log backup NOT ldf file.
-- Suppose we took differnetial at 10AM and t-log backup at 11 AM then log backup will have changes from previous differential backup rite?
Thanks for your time in answering my questions .
January 18, 2009 at 2:53 am
When was the previous log backup? Was there anything that broke the log chain since then (switch to simple, truncate only)?
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
January 18, 2009 at 2:58 am
Just I want to know can we take a t-log backup after Differtial backup ?
Viewing 15 posts - 1 through 15 (of 19 total)
You must be logged in to reply to this topic. Login to reply