June 10, 2014 at 7:12 am
Hi - I have a couple of DB's that have trn logs that are far bigger than the data files. MDF file around 83mb LDF around 1gb. It's highly likely that the DB's were set up in Full Recovery & a trn log backup hadn't been taken for a while. For these DB's Trn logs are now backing up every 30 mins. What would be best practice to reduce the size of these TRN logs? We have a in-house document which suggests the following steps:
(1) Change the Recovery mode from Full to Simple
(2) Run a backup against the DB
(3) Check the location of the current transaction log & make a note of this.
(4) Take the DB off-line
(5) Rename the current Transaction log file.
(6) Bring the database back on-line. At this point a new transaction log file is created.
I also need to backup a database to a UNC path. My colleague has created the doamin account which has the appropraite rights. Do I just need to change the SQL agent service account via surface configuration manager to use this new Domain account?
I would really apprciate any help as I want to adopt a best practice approach and not a fix which could potentially cause more damage.
Kind Regards
Dax
June 10, 2014 at 7:17 am
dax.latchford (6/10/2014)
We have a in-house document which suggests the following steps:(1) Change the Recovery mode from Full to Simple
(2) Run a backup against the DB
(3) Check the location of the current transaction log & make a note of this.
(4) Take the DB off-line
(5) Rename the current Transaction log file.
(6) Bring the database back on-line. At this point a new transaction log file is created.
Gah! File that document in the dustbin under 'really, really bad ideas'
Doing that could result in the DB not coming back online in step 6. The tran log can't just be deleted without potential problems.
If you have log backups running regularly, the process for getting the log size down has a single step:
Step 1: Run DBCC ShrinkFile (2,<sensible size for the transaction log>)
Sensible size may take some time to determine, you need to figure out what the max space used in the log is. Easiest just to monitor the used % for a week and see what the max space the log uses is. It'll probably be when you run index rebuilds.
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
June 10, 2014 at 7:28 am
Hi - Many thanks for coming back so quickly. I will take your advice and will delete this piece of documentation which I believe was historically taken from another forum.
Also what would be a 'Best Practice' approach to shrink the log file?
Also as mentioned my colleague has created a domain account with the rights to backup to a UNC location. Is it safe for me to do the following to use this account:
(1) Launch SQL Configuration Manager
(2) Stop the SQL Agent Service
(3) Change the SQL Agent Service to use the domain account
(4) Bring the SQL Agent Service back on-line
Thanks Again
Kind Regards
Dax
June 10, 2014 at 7:29 am
dax.latchford (6/10/2014)
Also what would be a 'Best Practice' approach to shrink the log file?
I thought I already answered that. If you have regular log backups running, then do a once-off shrink of the log to a sensible size. Sensible size may take some time to determine, you need to figure out what the max space used in the log is. Easiest just to monitor the used % for a week and see what the max space the log uses is. It'll probably be when you run index rebuilds.
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
June 10, 2014 at 7:34 am
Hi - Yes sorry you have answered, my oversight?
Do the steps ref: SQL Agent Service account appear ok to you?
Kind Regards
Dax
June 10, 2014 at 7:35 am
GilaMonster (6/10/2014)
dax.latchford (6/10/2014)
We have a in-house document which suggests the following steps:(1) Change the Recovery mode from Full to Simple
(2) Run a backup against the DB
(3) Check the location of the current transaction log & make a note of this.
(4) Take the DB off-line
(5) Rename the current Transaction log file.
(6) Bring the database back on-line. At this point a new transaction log file is created.
Gah! File that document in the dustbin under 'really, really bad ideas'
Doing that could result in the DB not coming back online in step 6. The tran log can't just be deleted without potential problems.
Gail forgot to mention her blog post on the topic
http://sqlinthewild.co.za/index.php/2009/06/09/deleting-the-transaction-log/
In short a very very bad idea.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
June 10, 2014 at 7:36 am
dax.latchford (6/10/2014)
Hi - Yes sorry you have answered, my oversight?Do the steps ref: SQL Agent Service account appear ok to you?
Kind Regards
Dax
You change that via SQL Server Configuration Manager.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
June 10, 2014 at 7:42 am
SQLRNNR (6/10/2014)
Gailforgotwas too lazy tomentionlook up the URL for her blog post on the topic while at work
😀
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
June 10, 2014 at 7:47 am
GilaMonster (6/10/2014)
SQLRNNR (6/10/2014)
Gailforgotwas too lazy tomentionlook up the URL for her blog post on the topic while at work😀
:hehe:
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
June 10, 2014 at 7:52 am
dax.latchford (6/10/2014)
Also as mentioned my colleague has created a domain account with the rights to backup to a UNC location. Is it safe for me to do the following to use this account:(1) Launch SQL Configuration Manager
(2) Stop the SQL Agent Service
(3) Change the SQL Agent Service to use the domain account
(4) Bring the SQL Agent Service back on-line
That should work for backing up to a UNC, although generally backing up to a UNC isn't suggested. You're relying on the network connection to stay up and stable for the duration of the backup. If the network "hiccups" you could end up with no backup, or a corrupt backup file.
Better to backup to a local folder, then copy the backup file to the UNC (which should work OK with the Agent service account your colleague created,) and can be done entirely from within an Agent job.
June 10, 2014 at 8:04 am
That's great, many thanks for the info. Much appreciated!
Kind Regards
Dax
June 10, 2014 at 8:10 am
jasona.work (6/10/2014)
dax.latchford (6/10/2014)
Also as mentioned my colleague has created a domain account with the rights to backup to a UNC location. Is it safe for me to do the following to use this account:(1) Launch SQL Configuration Manager
(2) Stop the SQL Agent Service
(3) Change the SQL Agent Service to use the domain account
(4) Bring the SQL Agent Service back on-line
That should work for backing up to a UNC, although generally backing up to a UNC isn't suggested. You're relying on the network connection to stay up and stable for the duration of the backup. If the network "hiccups" you could end up with no backup, or a corrupt backup file.
Better to backup to a local folder, then copy the backup file to the UNC (which should work OK with the Agent service account your colleague created,) and can be done entirely from within an Agent job.
100% agreement, although it does depend on what "local" looks like. I've seen network attached storage as the local data storage and then the difference between backing up locally and backing up to a UNC reduce somewhat, depending where the UNC is.
"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 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply