April 6, 2011 at 5:10 am
The log file of the database (in mirroring - so full recovery model) grows to fast.
So I want to shrink the log file (and if it works, put this action in a recurrencing job).
The log file has a restricted autogrowth of 100GB, log file is now 95GB (and 30% free log space at the moment).
Can I use following command:
USE [Blob]
GO
DBCC SHRINKFILE ('Blob_log',1000) --> or DBCC SHRINKFILE ('Blob_log',0,TRUNCATEONLY)
GO
Or is this action not possible on a mirrored db?
What (job) action can I take?
Many thanks,
Sven
April 6, 2011 at 5:15 am
Are you taking regular transaction log backups?
If not you will need to otherwise the log file will continue to grow no matter what you do, there should be no reason to shrink a log file on any kind of regular basis.
Cheers
Mat
April 6, 2011 at 5:19 am
Please read through these: http://www.sqlservercentral.com/articles/64582/, http://www.sqlservercentral.com/articles/Transaction+Log/72488/
Shrinking the log is only for when it's grown beyond normal size due to some unusual operation. If it's normal operation, the log will just grow back (and fragment in the process). Do not regularly shrink anything (DB or log)
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
April 6, 2011 at 5:20 am
No we are not taking any backups (our 'backup' is the mirrored db)...
And if we need to take log backups, I need to apply those log backups to the mirror db?
Or does mirroring take this (only log backup on principal) without problem?
April 6, 2011 at 5:29 am
Rhox (4/6/2011)
No we are not taking any backups (our 'backup' is the mirrored db)...
Mirroring is NOT a backup. It's a high-availability solution.
http://sqlinthewild.co.za/index.php/2009/05/08/high-availability-backups/
Without backups how do you expect to fix a situation where a critical table has had data deleted?
And if we need to take log backups, I need to apply those log backups to the mirror db?
Or does mirroring take this (only log backup on principal) without problem?
You need to take backups. Regular full backups, regular log backups. Please read the first of the articles I references. no, you don't need to apply them to the mirror, log records are copied across by the mirroring. You still need to take 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
April 6, 2011 at 5:37 am
This database contains mostly photo's uploaded by an application.
The decision to choose mirroring is already taken before I started.
Mirroring was good for business. Backups were not necessary.
So I only need to start a backup maintenance of the principal database?
And no log backups of those principal database need to be restored on the mirror database?
April 6, 2011 at 5:49 am
Rhox (4/6/2011)
Mirroring was good for business. Backups were not necessary.
So if someone dropped the table that stores those photos, business would be happy with the complete loss of all the photos, with no possibility to recover?
So I only need to start a backup maintenance of the principal database?
Full and log backups. See the first article I referenced.
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
April 6, 2011 at 6:01 am
GilaMonster (4/6/2011)
Rhox (4/6/2011)
No we are not taking any backups (our 'backup' is the mirrored db)...Mirroring is NOT a backup. It's a high-availability solution.
http://sqlinthewild.co.za/index.php/2009/05/08/high-availability-backups/
Without backups how do you expect to fix a situation where a critical table has had data deleted?
And if we need to take log backups, I need to apply those log backups to the mirror db?
Or does mirroring take this (only log backup on principal) without problem?
You need to take backups. Regular full backups, regular log backups. Please read the first of the articles I references. no, you don't need to apply them to the mirror, log records are copied across by the mirroring. You still need to take backups.
If you do not taking the backup of mirror database, What U will do at the feluer of Primery server?
Ali
MCTS SQL Server2k8
April 6, 2011 at 6:09 am
So (assuming no backups are needed) I can use following command:
BACKUP DATABASE Blob TO DISK = N'nul'
So no actual backup is taken...?
April 6, 2011 at 6:14 am
Sure, if you're completely happy with not being able to recover data if it is deleted.
Seriously, what happens if someone drops the table that stores those photos? Or accidentally deletes half the table? How do you expect to recover without backups?
You need backups, unless this DB can be completely and trivially recovered if it is destroyed (and I don't mean recovered from the mirror, there are many disasters that mirroring will NOT help with)
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
April 6, 2011 at 6:22 am
GilaMonster (4/6/2011)
Seriously, what happens if someone drops the table that stores those photos? Or accidentally deletes half the table? How do you expect to recover without backups?
That was my question too when I started.
Only answer I got: that was decided to be the most usefull.
And it's not my decision 🙁
I think the photo's are saved on the network too... but I didn't looked better to that.
April 6, 2011 at 6:25 am
Ditto on everything Gail writes. In addition to backups, you actually need to test your ability to restore those databases. I randomly pick a server and them randomly pick some databases to restore on a regular basis.
April 6, 2011 at 6:27 am
Rhox (4/6/2011)
That was my question too when I started.Only answer I got: that was decided to be the most usefull.
And it's not my decision 🙁
I think the photo's are saved on the network too... but I didn't looked better to that.
The problem is that it will be your head if it happens and you can't recover. I've seen DBAs fired because they couldn't recover deleted data, regardless of previous decisions from management about the need for 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
April 6, 2011 at 6:30 am
I do understand Gila's comments...
I understand the attention of backups.
But I only can advise my managers because it's their responsibility 🙁
I'm only 'hired' for their DBA task they asks... maybe later I can get more responsibility on the whole maintenance.
I'm glad for all the answers here!
Thanks
April 6, 2011 at 6:33 am
Gail/Ali, Is there any way to restore the data from Mirror to Primary if something happened to primary data?
_____________________________________________
One ounce of practice is more important than tonnes of dreams
Viewing 15 posts - 1 through 15 (of 16 total)
You must be logged in to reply to this topic. Login to reply