Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

SQL Server crashes when data drive full? Expand / Collapse
Author
Message
Posted Tuesday, October 23, 2012 9:03 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Monday, July 14, 2014 1:19 PM
Points: 323, Visits: 1,456
Hi. Has anyone experienced this in SQL 2005; that SQL Server goes down when the data or log drive drives are full? I haven't but a couple people in the company have. We're on 9.0.4053 (SP3) which I think is still a supported version.

This doesn't make sense to me. An autogrowth should fail and that be the end of it.

Thanks,
Ken
Post #1376116
Posted Tuesday, October 23, 2012 9:15 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 4:54 PM
Points: 42,434, Visits: 35,488
A full drive shouldn't cause SQL to crash. It'll cause transactions to fail, that's about all.

Check the SQL error log, see what's the last few entries in the log



Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #1376130
Posted Tuesday, October 23, 2012 9:44 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Monday, July 14, 2014 1:19 PM
Points: 323, Visits: 1,456
Thanks, not seeing much in the logs. I'm tempted to let the data drive fill up just to see what happens . But our end user folks would be going crazy with alerts, not to mention it's a production DB.
Post #1376154
Posted Tuesday, October 23, 2012 11:00 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Friday, July 18, 2014 6:01 PM
Points: 351, Visits: 900
So take a full backup of your production database and restore it to a test server. Then let the drives on that machine fill and see if you can recreate the behavior. Good research, maybe you'll find a solution and production isn't harmed by the testing.
Post #1376176
Posted Wednesday, October 24, 2012 4:48 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Friday, May 30, 2014 8:15 AM
Points: 1,056, Visits: 2,687
Scott D. Jacobson (10/23/2012)
So take a full backup of your production database and restore it to a test server. Then let the drives on that machine fill and see if you can recreate the behavior. Good research, maybe you'll find a solution and production isn't harmed by the testing.


Scott,

Hope server crash is something related to a sql service or setting nothing related to DB.


Regards
Durai Nagarajan
Post #1376406
Posted Thursday, October 25, 2012 8:33 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Friday, July 18, 2014 9:25 AM
Points: 1,229, Visits: 2,727
GilaMonster (10/23/2012)
A full drive shouldn't cause SQL to crash. It'll cause transactions to fail, that's about all.

Check the SQL error log, see what's the last few entries in the log


I agree with Gail, it shouldn't cause it to crash, just won't be able to add new data if trans log or mdf file try to expand. You need to look at the SQL Server log and the Windows event logs to see why SQL Server crashes. There should be details in there.



Post #1377029
Posted Friday, October 26, 2012 8:01 AM


SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Today @ 8:36 AM
Points: 4,319, Visits: 6,112
1) I suppose the failure of some system action could cause SQL to barf (just a supposition here)

2) SQL 2005 SP3 is unsupported by Microsoft


Best,

Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru at GMail
Post #1377600
Posted Friday, October 26, 2012 8:08 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Yesterday @ 1:09 PM
Points: 1,715, Visits: 1,320
Gail - what if it were a system database, in particular the master database?

Chris Powell

George: You're kidding.
Elroy: Nope.
George: Then lie to me and say you're kidding.
Post #1377605
Posted Friday, October 26, 2012 8:23 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Friday, July 18, 2014 9:25 AM
Points: 1,229, Visits: 2,727
Meet George Jetson (10/26/2012)
Gail - what if it were a system database, in particular the master database?


THe master database will probably never grow. All it holds is logins and records of the application databases. Typically it is under 10mb.



Post #1377622
Posted Friday, October 26, 2012 8:26 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Monday, July 14, 2014 1:19 PM
Points: 323, Visits: 1,456
Truth is, this machine is flakey. Maybe it's tempdb. It was setup by another group with way too many files, 16 on an 8 core box. We've been trying to take it down to 8 files. That may be neither here nor there but the reason we're stuck on 2005 SP3 is that I'm TOLD our server admin team tried to get it up to SP4 but that "it wouldn't take". That the original group put some incompatible software on it that prevented SQL from being updated. As far as I know this original group had only installed database drivers and the like on it so SSIS packages/jobs could pull from other sources.

Thanks,
Ken
Post #1377626
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse