August 19, 2011 at 9:46 am
Along with that shrink, make sure you account for VLFs. Check the article in my sig list by Kimberly Tripp
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
August 19, 2011 at 9:50 am
Guras (8/19/2011)
I ran the DBCC openTran(mydb)but there is no open transactions.
How do I to locate queries that consume a large amount of log space?
Thanks for your help.
Beyond doing a trace, I provide a handy solution for that in the article I referenced. Using the method I show, you don't have to be running a trace and you don't have to be watching the server at the time the offending process happens. 😉
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
August 19, 2011 at 11:36 am
Thank you all for the replies, they all have been a great help. After reading all the information I have decided to SHRINK the t-log file (and I uderstand that the t-log back up chain will be broken)
Here is the plan I am planning to undertake
1.Find the off peak hour ( mostly late at night) and bring the databse to the SINGLE USER MODE
2.Do a regular t-log back up.
3. Shrink the file to the samll size as possible by running
DBCC SHRINKFILE(mydblogfile_log,TRUNCATEONLY)
4.Alter the databse to modify the transaction log file to a size
ALTER DATABASE mydatabase
MODIFYFILE
(NAME = mydblogfile_log, SIZE = 64MB)
5. Perform a full back up
6. Check that the regular t-log back up is running
Please let me know if I am missing out on some important step. Thanks a lot for all the help. This forum is really great!
August 19, 2011 at 11:50 am
You don't need single user mode, and nothing in that set of steps will break the log chain.
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 4 posts - 16 through 18 (of 18 total)
You must be logged in to reply to this topic. Login to reply