April 30, 2015 at 5:33 am
Hi ,
I would like to know what happens if i shrink the database with truncate only option and do a full backup or transaction log backup ? are the full backup or transaction log backup valid? I know that the performance of the database is bad if i shrink the database. so could some one explain me what happens to full backup or transaction log backups?
thanks in advance
April 30, 2015 at 6:34 am
RamSteve (4/30/2015)
Hi ,I would like to know what happens if i shrink the database with truncate only option and do a full backup or transaction log backup ?
with the truncate option sql server will just remove free space as far as the last allocated extent in each database file. Log files are not affected by truncate option.
The backup will run as usual what did you expect it to do?
RamSteve (4/30/2015)
are the full backup or transaction log backup valid?
what makes you think they wouldn't be?
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
April 30, 2015 at 6:54 am
Shrinking the database files is shrinking the database files. Backing up a database is doing a page by page copy of the database and backing up the log is copying out the completed transactions as well as truncating the completed transactions that were backed up. Two different operations with two different sets of processes and two different completion points, one, a smaller database file, the other a backup file.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
April 30, 2015 at 7:11 am
To be more specific .. I make huge transactions in database like delete and Insert and Update. Right after this transactions i Shrink the Database and take Transaction log back up or full backup. If Disaster happens i need to restore this backups. So will there be any issues in recovering my data ?
April 30, 2015 at 7:12 am
When you shrink the database, it fragments your indexes very effectively. Other than having an initial size that was way off, why would you want to shrink it?
April 30, 2015 at 7:32 am
RamSteve (4/30/2015)
To be more specific .. I make huge transactions in database like delete and Insert and Update. Right after this transactions i Shrink the Database and take Transaction log back up or full backup. If Disaster happens i need to restore this backups. So will there be any issues in recovering my data ?
No. You can recover your data.
But, please stop doing that. You're hurting your system. Just do a search of the internet to get many, thorough, lists of exactly why that's a bad practice. In particular, look for anything by Paul Randal.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
May 1, 2015 at 2:22 am
Just to add (hopefully) a little extra support to the excellent advice already given:-
It is usually not a good idea to shrink your database - it grew to that size for a reason. When that "reason" happens again (tomorrow's new data load for example) the database is going to have a lot of hard work to do just to add back all that extra room it needs again. If you had left things as they were, less of this growth would be needed.
Your storage is clearly capable of holding the peak amount of data that is being handled, so why worry?
May 1, 2015 at 6:31 am
EMarkM (5/1/2015)
When that "reason" happens again (tomorrow's new data load for example)
or an index rebuild!
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
May 4, 2015 at 3:35 pm
In the scenario you describe, if you shrink your database after deletes it will only grow again during inserts. The time it takes to do the shrink adds time to the process, as does the time to grow the file during the inserts.
Then there is the matter of fragmented indexes from the shrink. Yes you can rebuild indexes, but that adds even more time & resource overhead.
So you are better off just leaving the database file at the larger size. Note that shrinking a database does not reduce the size of backup files. Backup file size is dependent on the amount of data in the data file, not the size of the data file.
May 4, 2015 at 3:47 pm
dan-572483 (5/4/2015)
In the scenario you describe, if you shrink your database after deletes it will only grow again during inserts. The time it takes to do the shrink adds time to the process, as does the time to grow the file during the inserts.Then there is the matter of fragmented indexes from the shrink. Yes you can rebuild indexes, but that adds even more time & resource overhead.
So you are better off just leaving the database file at the larger size. Note that shrinking a database does not reduce the size of backup files. Backup file size is dependent on the amount of data in the data file, not the size of the data file.
The shrink and grow of a database leads to fragmented files in storage, not to fragmented indexes. Heck, we could just run a defrag on the indexes and fix the issue. No, this is fragmentation at the OS level.
Just a note. Everything else you say is right.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply