July 29, 2011 at 12:11 pm
Hello,
When I run a manual shrink task on the log file of my DB it works great. When I hit the script button and copy and paste the same command into a job it bombs out with the following error "Executed as user: Domain\MyAccount. Cannot shrink log file 2 (FSDBntLOG.dat) because all logical log files are in use. [SQLSTATE 01000] (Message 9008) DBCC execution completed. If DBCC printed error messages, contact your system administrator. [SQLSTATE 01000] (Message 2528). The step succeeded."
This DB is setup to full recovery mode, not simple. This makes no sense to me. Any help is appreciated.
July 29, 2011 at 12:16 pm
You're right, it makes no sense shrinking the logs.
Please read through BOTH those articles.
http://www.sqlservercentral.com/articles/Transaction+Log/72488/
July 29, 2011 at 1:38 pm
The real question here is why are you managing your log files by shrinking them. You should have frequently scheduled (every 15 minutes is a good start) transaction log backups in place. The log files will stabilize on the size they need to be to support that interval of transactions.
If you have that setup, and your log files continually grow - then you have something else blocking SQL Server from marking the VLF's in the transaction log as reusable. This could be a long running open transaction or a broken replication setup.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
July 29, 2011 at 1:41 pm
We have a huge job that runs once a month and it blows up the size of the log file so instead of backing this up everynight I would like to shrink it down and let it expand to its normal size. That is what our current though process is on it.
July 29, 2011 at 1:45 pm
Rather leave it at the size it needs to be unless you're absolutely desperate for space. A large log does not impact the DB. Log growth on the other hand does, the log has to be zero-initialised and transactions will have to wait for that to proceed.
If you absolutely must shrink (and it's strongly not recommended) just shrink it down to the usual size. Don't shrink it to nothing and let it grow, you're asking for log fragmentation (unless you happen to have the growth increments set optimally) and probably file system fragmentation.
There is absolutely nothing wrong with having a log file that's huge and mostly empty. Last job I had, our DB had a 250GB log file. It never reached more than 2% during the day. The overnight jobs usually took to to 80%. We left it at 250GB.
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
July 29, 2011 at 1:48 pm
Thanks for your advice everyone. This newbie is just going to leave the log file alone then. Learn something new every day.
July 29, 2011 at 1:56 pm
one more question pertaining to this....when my NT Backup runs it is gonig to copy that entire 250GB file to backup...do you just let this happen? When I did a shrink before the backup it cut hours off my backup time.
July 29, 2011 at 2:26 pm
jmorgan 12692 (7/29/2011)
one more question pertaining to this....when my NT Backup runs it is gonig to copy that entire 250GB file to backup...do you just let this happen? When I did a shrink before the backup it cut hours off my backup time.
Do you mean system level backup?
Question in regars to your native SQL Server backup strategy... are you taking additional tlog backups in between full backups? like an hourly tlog backup or so?
_____________________________________
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at Amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.July 29, 2011 at 2:28 pm
Yes, system level backup.
July 29, 2011 at 2:31 pm
That is not how SQL databases should be backed up. A file-level copy of the data and log files will very likely be useless. The files are locked open by SQL server and the way SQL uses them it can not be assumed that you can copy the open files and attach them elsewhere.
Read up on BACKUP DATABASE (and BACKUP LOG), especially if you're in full recovery.
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
Viewing 10 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply