December 1, 2011 at 9:31 am
We have a request from application team to refresh database for testing. This will be here for a short period and the drive space is low. As you may guess, the log file is huge and db is to be changed to simple mode.(Im ok with shrinking because DB will be refreshed soon.) So I shrink the log to help save some space.
The shrink is taking a long time. Its close to an hour already and running. The log file was at 70+ GB and I tried to shrink to 35 GB in one shot
1. I was thinking if I had set it to simple and then tried shrink, it would have done it sooner.
2. I was wondering what can happen if the application team now sees the DB available and connects and goes ahead with their work?
Please suggest
December 1, 2011 at 9:32 am
DBCC SHRINKFILE (JobPortal_log, 100) WITH NO_INFOMSGS
This works great for my log files!
December 1, 2011 at 4:08 pm
Yep, you need to verify that the log will actually shrink as much as want before issuing the SHRINK command. That means putting the db in simple mode first.
You likely won't be able to KILL the task thru the query screen, but you can try KILLing the related spid. Likely won't help, but can't hurt.
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
December 1, 2011 at 4:47 pm
fave_dba (12/1/2011)
We have a request from application team to refresh database for testing. This will be here for a short period and the drive space is low. As you may guess, the log file is huge and db is to be changed to simple mode.(Im ok with shrinking because DB will be refreshed soon.) So I shrink the log to help save some space.The shrink is taking a long time. Its close to an hour already and running. The log file was at 70+ GB and I tried to shrink to 35 GB in one shot
1. I was thinking if I had set it to simple and then tried shrink, it would have done it sooner.
2. I was wondering what can happen if the application team now sees the DB available and connects and goes ahead with their work?
Please suggest
Out of curiousity, what's the recovery mode currently on the DB, and have you done a Backup Log in the meanwhile? Is there a lot of activity constantly going on in the DB while you're trying to shrink?
If they start working in it it's going to take a bit longer, yes.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
December 1, 2011 at 4:49 pm
Evil Kraig F (12/1/2011)
fave_dba (12/1/2011)
We have a request from application team to refresh database for testing. This will be here for a short period and the drive space is low. As you may guess, the log file is huge and db is to be changed to simple mode.(Im ok with shrinking because DB will be refreshed soon.) So I shrink the log to help save some space.The shrink is taking a long time. Its close to an hour already and running. The log file was at 70+ GB and I tried to shrink to 35 GB in one shot
1. I was thinking if I had set it to simple and then tried shrink, it would have done it sooner.
2. I was wondering what can happen if the application team now sees the DB available and connects and goes ahead with their work?
Please suggest
Out of curiousity, what's the recovery mode currently on the DB, and have you done a Backup Log in the meanwhile? Is there a lot of activity constantly going on in the DB while you're trying to shrink?
If they start working in it it's going to take a bit longer, yes.
Another good reason to do these sorts of operations during a maintenance window.
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
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply