| Author |
Topic |
|
Abu-Dina
Posting Yak Master
206 Posts |
 Posted - 2012-07-27 : 09:25:17
|
| Hi there,We have a very slow running SQL server so I logged into the server and it's virtually out of disk space where the SQL server is installed. SQL Server is installed on drive D which currently has 267GB used with less than 10MB free disk space left!Would this impact the performance of SQL server or is it just a case of users not being able to create objects if the DB files are stored on the disk that's out of disk space?Hope I'm making sense.Thanks. |
|
|
nigelrivett
Master Smack Fu Yak Hacker
3385 Posts |
Posted - 2012-07-27 : 10:18:58
|
| Might have fragmentation issues.If you run out of space (and looks like you have) then the system will crash if it needs to expand and you are likely to end up with corrupted databases.If this is really the disk where the database files reside and the databases don't have free space then you are probably in trouble. The slowness could actually be due to failures.Are logs on the same drive? Do yoou have a large log file that is taking up the space?First thing to do is release some space then find out what is actually slow on the server - which queries.==========================================Cursors are useful if you don't know sql.SSIS can be used in a similar way.Beer is not cold and it isn't fizzy. |
 |
|
|
komkrit
Yak Posting Veteran
60 Posts |
Posted - 2012-07-27 : 10:48:03
|
| Dear Abu-Dina,You just got 2 issues,1. Performance2. CapacityPerformance issue may come from several root cause, for example "Table Scan", "Insufficient Memory"Capacity issue can lead to a critical situation such as "Database Corrupt" or "Instance Down" in the case your system database (master, tempdb) or system file (error log) stored at insufficient disk. But insufficient disk could not lead to slow response. My experience, I have just once critial situation that out of space of Drive C: and no more system temporary file were generated.It cause unexpected problem becuase we don't know which process need to write a temporary file but they can't.In my case, I could not access database or even log in to console and have to restart server. It's very sad.Between database corrupt and slow response, which one is more critical ?In your case seem like it reached maximum of capacity, move data file to another drive and/or increate disk size.- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -Komkrit YensirikulCurrently be a DBA in worldwide hotel reservation. Experienced in top leading banking environment. |
|
|
|
Abu-Dina
Posting Yak Master
206 Posts |
Posted - 2012-07-27 : 11:01:33
|
Hi Nigel,Thanks for your response.quote:
The slowness could actually be due to failures.Are logs on the same drive? Do you have a large log file that is taking up the space?
The SQL Server holds about 70 databases but these are mainly data staging databases and the majority are not used.When I've looked in the folders where the logs are stored, I didn't see a large log file but I did find a database file that was last updated in December 2011 which is 37GB in size! I'm in the process of archiving this so hopefully this should resolve the disk space issue for now.quote:
First thing to do is release some space then find out what is actually slow on the server - which queries.
I've also setup a performance trace based on Brent Ozar's artcle here: http://www.brentozar.com/archive/2006/12/dba-101-using-perfmon-for-sql-performance-tuning/It's mainly ad-hoc queries. For example when user was running an alter table statement and adding a column with a default of 0 to a table of 4 million rows and it took 5 minutes. Let's see if freeing up the disk space makes a difference. I will also check the level of fragmentation on the drive.Thanks. |
|
|
|
Abu-Dina
Posting Yak Master
206 Posts |
Posted - 2012-07-27 : 11:09:05
|
quote:
Originally posted by komkrit
Dear Abu-Dina,You just got 2 issues,1. Performance2. CapacityPerformance issue may come from several root cause, for example "Table Scan", "Insufficient Memory"Capacity issue can lead to a critical situation such as "Database Corrupt" or "Instance Down" in the case your system database (master, tempdb) or system file (error log) stored at insufficient disk. But insufficient disk could not lead to slow response. My experience, I have just once critial situation that out of space of Drive C: and no more system temporary file were generated.It cause unexpected problem becuase we don't know which process need to write a temporary file but they can't.In my case, I could not access database or even log in to console and have to restart server. It's very sad.Between database corrupt and slow response, which one is more critical ?In your case seem like it reached maximum of capacity, move data file to another drive and/or increate disk size.- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -Komkrit YensirikulCurrently be a DBA in worldwide hotel reservation. Experienced in top leading banking environment.
Hi,Thanks for your time.I had a feeling that's the case but I was hoping that disk space and performance are somehow linked? I've now freed up some 10GB of space on the server so at least people can get on with their work but I've also setup a trace based on Brent Ozar's excellent blog article and I've noticed that the Available memory drops to below 100MB which suggests SQL Server is being starved of memory so has to make more disk IO?It's my first week in this new role and I just want to make sure I make a good impression! Sorry if I'm sounding like a total fool! |
|
|
|
komkrit
Yak Posting Veteran
60 Posts |
Posted - 2012-07-27 : 12:13:35
|
| Hello Abu-Dina,Best practice for configure memory in SQL Server.Set SQL Server Maximum memory as much as possible. But !! not more than approximately 75% of total physical memory.Because if you let SQL Server as default maximum value(unlimted) then SQL Server will consume all of memory.Purpose of set up 75% is to give free memory to OS for other process or OS process itself.You may set more than 75% up to size of your total physical memory.But keep in mind, reserve free memory for OS at lease 500MB to 1GBGive SQL Server memory at least 1GB and as much as possible.If you do above and free memory less than 100MB, you have too small physical memory.- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -Komkrit YensirikulCurrently be a DBA in worldwide hotel reservation. Experienced in top leading banking environment. |
|
|
|
Transact Charlie
Master Smack Fu Yak Hacker
3451 Posts |
Posted - 2012-07-28 : 13:42:18
|
What;s your backup strategy for all those dbs? By default when you create a database it'll be in recovery model: Full. This means the transaction log will be growing and growing until you take a log backup of it...... So your disk may be completely filled with log.... (DON'T DELETE LOG - it's important)since you don't seem to care about the db's on the box that much you should probably be using simple recovery if you are not already....http://msdn.microsoft.com/en-us/library/ms189275.aspxI'd go back and look at Nigel's posts quote:
Might have fragmentation issues.If you run out of space (and looks like you have) then the system will crash if it needs to expand and you are likely to end up with corrupted databases.If this is really the disk where the database files reside and the databases don't have free space then you are probably in trouble. The slowness could actually be due to failures.Are logs on the same drive? Do yoou have a large log file that is taking up the space?First thing to do is release some space then find out what is actually slow on the server - which queries.
He knows what he's talking about.I haven't come across komkrit before but I can't see any advice here that is actually helpful to your problem at hand.Transact CharlieMsg 3903.. The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION. http://nosqlsolution.blogspot.co.uk/ |
|
|
|
Abu-Dina
Posting Yak Master
206 Posts |
Posted - 2012-07-29 : 06:21:16
|
quote:
Originally posted by Transact Charlie
What;s your backup strategy for all those dbs? By default when you create a database it'll be in recovery model: Full. This means the transaction log will be growing and growing until you take a log backup of it...... So your disk may be completely filled with log.... (DON'T DELETE LOG - it's important)
Good question and the answer is I don't know! It's my first week with this company. They have no DBA and I don't think anyone knows how many instances of SQL Server and what verions are installed. It's a mixture of 2000, 2005 and 2008.I've been tasked with re-desiging their data merge methodology so it's development than administration but from what I've seen so far I really think I should spend some time to sort out the SQL Servers and databases first then work on re-developing their code!quote:
since you don't seem to care about the db's on the box that much you should probably be using simple recovery if you are not already....http://msdn.microsoft.com/en-us/library/ms189275.aspx
Absolutely you are correct. But do you tink it's a good idea to document how many SQL Server instances they have and number of DBs, sizes, usage frequency etc Only then will I be able to decide what recovery model to use for what. Do you agree?quote:
I'd go back and look at Nigel's posts quote:
Might have fragmentation issues.If you run out of space (and looks like you have) then the system will crash if it needs to expand and you are likely to end up with corrupted databases.If this is really the disk where the database files reside and the databases don't have free space then you are probably in trouble. The slowness could actually be due to failures.Are logs on the same drive? Do yoou have a large log file that is taking up the space?First thing to do is release some space then find out what is actually slow on the server - which queries.
He knows what he's talking about.I haven't come across komkrit before but I can't see any advice here that is actually helpful to your problem at hand.Transact CharlieMsg 3903.. The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION. http://nosqlsolution.blogspot.co.uk/
Yes..... when nr speaks I listen! |
|
|
|
Transact Charlie
Master Smack Fu Yak Hacker
3451 Posts |
Posted - 2012-07-29 : 18:39:07
|
quote:
Originally posted by Abu-Dina
quote:
Originally posted by Transact Charlie
What;s your backup strategy for all those dbs? By default when you create a database it'll be in recovery model: Full. This means the transaction log will be growing and growing until you take a log backup of it...... So your disk may be completely filled with log.... (DON'T DELETE LOG - it's important)
Good question and the answer is I don't know! It's my first week with this company. They have no DBA and I don't think anyone knows how many instances of SQL Server and what verions are installed. It's a mixture of 2000, 2005 and 2008.I've been tasked with re-desiging their data merge methodology so it's development than administration but from what I've seen so far I really think I should spend some time to sort out the SQL Servers and databases first then work on re-developing their code!quote:
since you don't seem to care about the db's on the box that much you should probably be using simple recovery if you are not already....http://msdn.microsoft.com/en-us/library/ms189275.aspx
Absolutely you are correct. But do you tink it's a good idea to document how many SQL Server instances they have and number of DBs, sizes, usage frequency etc Only then will I be able to decide what recovery model to use for what. Do you agree?
Absolutely! My point was only that your disk space problems may be entirely down to non backup up transaction logs that are completely useless to you!Transact CharlieMsg 3903.. The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION. http://nosqlsolution.blogspot.co.uk/ |
|
|
|
|
SQL Server performance issues