April 19, 2012 at 7:59 pm
Hello all -
Have an issue that has come up with one of my test db's. I did some deletes of a lot of data earlier on this database, and really ended up taxing my db's log file. Such to the extent that before I was able to restart the MSSQLSERVER default instance service, I was down to 133mb out of 100gb on my disk partition. Well - I closed out SSMS (was waiting on another colleague who had moved a massive amount of data into the db), and then restarted the service.
The db is now 'In Recovery', and I am just letting it do it's thing, but is this going to resolve itself? Or is it hose now? Mind you - we do not have a backup of the db because it is a new design, and we were just in the process of populating it with data (new filegroups added, old tables removed, etc...).
Any and all advice is welcome!
Rich
April 19, 2012 at 8:06 pm
Whew! Nevermind! IT just came back. Now - I pray there is not torn page corruption!
:w00t:
April 20, 2012 at 3:50 am
In recovery just means SQL's running crash recovery on the databases. It will do that to every database every time SQL starts up.
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 20, 2012 at 9:08 am
Hi Gail! 😀
I honestly had never seen it do that before when restarting the service. The thing was there was a large import of data going on to one of the tables, or said db, and it was kinda messy.
Good news is that it looks to be in tact. Bad news is that I have a very upset developer who was trying to pump in millions of rows when the service was reset. Ah communication with dev types - always fun.
Take care and have a great weekend!
😀
April 20, 2012 at 9:10 am
Rich Yarger (4/20/2012)
I honestly had never seen it do that before when restarting the service.
It usually takes seconds at best
Good news is that it looks to be in tact. Bad news is that I have a very upset developer who was trying to pump in millions of rows when the service was reset. Ah communication with dev types - always fun.
Yes, we developers do tend to get irritated if servers disappear without warning....
p.s. Why was it restarted in the first place?
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 20, 2012 at 9:34 am
GilaMonster (4/20/2012)
p.s. Why was it restarted in the first place?
The database's log file had jacked up to the near max on the share that was established for the ldf's (it nearly ran out), and I thought his job was done (because he told me earlier in the day that he was "finishing it up", and didn't say anything about it not being done before he left work). I needed space in a hurry, and I knew the system ldf's would reset with a restart of the service.
April 20, 2012 at 9:42 am
But, why restart? Unless it was TempDB's log, restart would do nothing. Could have just killed the active processes & manually shrunk the user DB's 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 20, 2012 at 9:50 am
I wound up retrieving about 7gb of space back with the service restart. It had more to do with not wanting the db to run out and crash at that point, than anything else. It's a 100gb partition and was getting dangerously low - I needed to free up space quick, and was just trying to make sure nothing crashed, so I stopped everything to see what I could do to free up space, and then restarted it again. When it restarted - I wound up with about 7gb free space, and figured it was from the system ldf's (because I didn't wind up deleting anything from that partition - there was nothing to get rid of).
April 20, 2012 at 10:27 am
Rich Yarger (4/20/2012)
I wound up with about 7gb free space, and figured it was from the system ldf's (because I didn't wind up deleting anything from that partition - there was nothing to get rid of).
TempDB data or log. Only things that would reduce in size after a restart.
SQL won't crash if the log drive gets out of space (though if it's TempDB, it gets nasty). The databases just go read-only until the log clears.
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 20, 2012 at 10:53 am
The TEMPDB.ldf was in that directory, so I figured it would free up space by restarting the service. TEMPDB is on a partition all his own. One for him, one for the ldf's, and a RAID 5 array for the mdf's/ndf's.
April 20, 2012 at 11:07 am
Edit: NM, misread.
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 20, 2012 at 12:07 pm
GilaMonster (4/20/2012)
Edit: NM, misread.
My fault - I should have been more clear when I said "system ldf's" that TEMPDB's log was in there.
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply