September 21, 2009 at 7:22 am
I had a very odd issue with transaction log growth come up on one of my development servers this weekend. A set of 3 databases used by one application caused the server to run out of disk space because of log growth. What is odd is that these databases exist on the development server solely because I restored them to it to test my backups. I had done this a few months ago. There should be no activity in the database and the logins (windows) the application uses have never been created on the development server so I know the application can't access them and there shouldn't be any activity on the database. Only myself and one other developer/admin have access to the server.
I have no jobs running against the databases and I don't even query them.
I realize I could have (and now have) changed the recovery model to simple, but the question is why is it growing in the first place?
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
September 21, 2009 at 7:33 am
Has to be activity. Maintenance plans? Checked the default trace?
September 21, 2009 at 7:35 am
Yes, it is weird.
Did you do any bulk insert to there during this time?
Another reason could be the indexes. If they were deleted or corrupted then a simple select statement against relatively large table could create a log size problem.
I am just guessing.:-)
September 21, 2009 at 7:39 am
- You shouldn't have switched them to simple recovery, before you have the answer to your question.
If you hadn't switched it to simple recovery, you might have been able to analyse its log file(s) using a trial version of log-analyser softwares.
- I would double check with the other dev/admin, so (s)he didn't activate a general purpose instance wide script like " rebuild my indexes for all databases of the instance " or did test a maintenance plan (not using sqlagent, but e.g. windows scheduled tasks)
- has builtin\administrators been removed from your sqlinstance logins ?
(if not, it may have been abuised)
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
September 21, 2009 at 7:51 am
Steve Jones - Editor (9/21/2009)
Has to be activity. Maintenance plans? Checked the default trace?
First thing I looked at was default trace. Only see DBCC SQLPERF(LOGSPACE) and autogrow and autoshrink events.
Setting up a server-side trace to run against the db's now.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
September 21, 2009 at 7:56 am
ALZDBA (9/21/2009)
- You shouldn't have switched them to simple recovery, before you have the answer to your question.If you hadn't switched it to simple recovery, you might have been able to analyse its log file(s) using a trial version of log-analyser softwares.
- I would double check with the other dev/admin, so (s)he didn't activate a general purpose instance wide script like " rebuild my indexes for all databases of the instance " or did test a maintenance plan (not using sqlagent, but e.g. windows scheduled tasks)
- has builtin\administrators been removed from your sqlinstance logins ?
(if not, it may have been abuised)
Didn't even think of using a log file analyzer. Since it is a dev instance I just wanted to free the space up. I should have put them in SIMPLE right after the backup.
There are no jobs on the server that would be a doing a rebuild. Anything would have to be coming from outside an agent job.
Builtin\Administrators has not been removed, but has been "disabled" by having sa rights removed.
Setting up a server-side trace to monitor activity on those db's to see if anything is happening.
I also disabled autogrow so if someone is doing something I should get an error.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
September 21, 2009 at 8:01 am
One thing I'd be concerned about is an app pointed at the wrong server. Are the db names the same as production?
I''m sure your trace will show something.
September 21, 2009 at 8:12 am
Jack Corbett (9/21/2009)
Builtin\Administrators has not been removed, but has been "disabled" by having sa rights removed.
I noticed if you add builtin\administrators, it gets individual auth in every database, so not only for the sysadmin group.
So builtin\admins still provides a means of entry (login) to the instance, so this might still come up with authorisation paths:
EXEC master..xp_logininfo @acctname = 'yourdomain\yourregularlogn',@option = 'all'
go
It's worth a doublecheck 😉
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
September 21, 2009 at 8:54 am
Steve Jones - Editor (9/21/2009)
One thing I'd be concerned about is an app pointed at the wrong server. Are the db names the same as production?I''m sure your trace will show something.
Yeah that was my first thought as the names are the same. But, none of the logins for the application, which uses windows authentication, are on the dev server. They can't be using sa because I don't even know the sa password. I can get it, but I don't know it. It was autogenerated as were the service account passwords.
So far the only thing in the trace is me.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
September 21, 2009 at 8:57 am
ALZDBA (9/21/2009)
Jack Corbett (9/21/2009)
Builtin\Administrators has not been removed, but has been "disabled" by having sa rights removed.I noticed if you add builtin\administrators, it gets individual auth in every database, so not only for the sysadmin group.
So builtin\admins still provides a means of entry (login) to the instance, so this might still come up with authorisation paths:
EXEC master..xp_logininfo @acctname = 'yourdomain\yourregularlogn',@option = 'all'go
It's worth a doublecheck 😉
Only database access granted to BUILTIN\Administrators is the public role. Even so an admin would have to be doing it and none are. They let me know about the issue because of an Operations Manager alert.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
September 22, 2009 at 11:11 am
Just a quick follow up. Ran a server-side trace for 24 hours filtering on database name. And not one event found. I might have missed some events as I just did RPC:Completed, SP:Completed, SQL:BatchCompleted and Logins and Logouts. I can see DBCC events in the default trace so I figured I didn't need those.
Only Logins/logouts were me, MOM, reporting services and Agent.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
September 22, 2009 at 11:29 am
And the db still grew?
September 22, 2009 at 11:41 am
Nope not this time, of course, I had it in SIMPLE Recovery. I will be changing the recovery model back and re-starting this trace again this afternoon for another 24 hour session.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
September 22, 2009 at 11:47 am
Let us know. That's a strange one. If you aren't getting notes on what's causing growth, I'll ping soem MS people. Might be worth burning a case with them to track it down. Or expand your trace.
September 22, 2009 at 11:54 am
Will do. Will update this thread with whatever happens.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
Viewing 15 posts - 1 through 15 (of 18 total)
You must be logged in to reply to this topic. Login to reply