Disk space

Question

Post reply

Disk space

PSB

SSChampion

Points: 13195
More actions
January 26, 2016 at 12:20 pm

#307923

Hi,
Our test server is running out of space in the G (Log files) and F drive(Data files and backups drive ) .
We have roughly 8 user SQL databases installed on it .
DB A = 15.8 GB
DB B = 18 GB
DB C = 6.38 MB
DB D = 5 MB
DB E = 17 GB
DB F = 88 MB
DB G = 13.6 GB
DB H = 30.2 GB
What is the recommended disk size needed so that we do not run into such issues very frequently?
Thanks,
PSB

Viewing 13 posts - 1 through 12 (of 12 total)

You must be logged in to reply to this topic. Login to reply

Igor Micev SSC-Dedicated Points: 33109 More actions · Answer 1

The sizes you mentioned don't have 100+GB together, and that is a small amount for a server running 8 databases.

What about the backup folder, what's its size?

Igor Micev,My blog: www.igormicev.com

PSB SSChampion Points: 13195 More actions · Answer 2

Under F drive we have two folders :

BACKUP -- 77.1 GB

DATA --47.5 GB

Under G drive : Used space is 12.6 GB and Free space is 27.3 GB

We keep running out of space every now and then . Will setting the auto growth option help ? Not sure what will be the best option for each database ?

We have multiple SSIS packages running frequently through SQL Server agent jobs . Do you think this might be the root cause ?

Attached is a screenshot of what it looks like now after I have shrinked the log files.

Thanks,

PSB

Igor Micev SSC-Dedicated Points: 33109 More actions · Answer 3

Small disks volumes.

The sql server is probably installed on C.

Can you check the tempdb database, its data and log files. They could be big, so you do something to release the C and E.

And why you don't use H?

Igor Micev,My blog: www.igormicev.com

PSB SSChampion Points: 13195 More actions · Answer 4

Yes SQL was installed on the C drive . C/Temp/SQL Install (3.986 GB of 3.96 GB )

TempDB Size = 13367.00 MB

C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA is the path of data as well log file . Both are located in the same subfolder DATA for tempdb.

Data file :

Size = 29.7 GB size , Size on Disk = 29.7 GB

Log file :

Size = 1.16 GB , Size on disk = 1.16 GB

Igor Micev SSC-Dedicated Points: 33109 More actions · Answer 5

You can add some more files in the Tempdb. Put them on other disks. Sql server will start to use them. See best practices for configuring tempdb on the net.

If you can allow to restart the instance you could decrease (shrink) the temdb to reduce its size.

Igor Micev,My blog: www.igormicev.com

PSB SSChampion Points: 13195 More actions · Answer 6

Hi,

My comments below :

You can add some more files in the Tempdb. See best practices for configuring tempdb on the net. ----> What other files can I place on tempdb ? Not sure what you mean by some more files on TempDB.

Put them on other disks. Sql server will start to use them. ---->Can I use the H drive ?

If you can allow to restart the instance you could decrease (shrink) the temdb to reduce its size. ----> Shrink log files or Data file ? Should I restart SQL Server at the test server ?

Thanks for helping,

PSB

Ed Wagner SSC Guru Points: 287026 More actions · Answer 7

I love Igor's idea of distributing tempdb to different drives as a short-term solution. You really need to free up space on C: and this will help you do it, but it will do nothing to help increase your overall capacity. Your databases are pretty small, but the drives are also small and might not be adequate for the long term.

If you're using SAN volumes, the SAN administrator could allocate more space for you if it's available. If you have local drives they you should start thinking about getting larger drives.

Long term, I think determining your data needs over time would help. If you know how much your data requirements grow over time, you can project how long you have left before you run out of space. There's an article at http://www.sqlservercentral.com/articles/Drive+space/134523/ that will show you how to go about doing this. The data you build up over time can also help you provide justification to management for getting larger drives or more space allocated from the SAN, whichever is suited to your setup.

Tally Tables - Performance Personified
String Splitting with True Performance
Best practices on how to ask questions

Jeff Moden SSC Guru Points: 1004366 More actions · Answer 8

PSB (1/26/2016)
Hi,
Our test server is running out of space in the G (Log files) and F drive(Data files and backups drive ) .
We have roughly 8 user SQL databases installed on it .
DB A = 15.8 GB
DB B = 18 GB
DB C = 6.38 MB
DB D = 5 MB
DB E = 17 GB
DB F = 88 MB
DB G = 13.6 GB
DB H = 30.2 GB
What is the recommended disk size needed so that we do not run into such issues very frequently?
Thanks,
PSB

Are these drives on a SAN or are they partitions on internal hard drives? If on a SAN, tell the SAN admins not to be such stingy buggers. If they're on internal hard drives, spend $500USD and get a drive or two that's measured in tera bytes. You good folks have probably already spent more than$500USD messing around with trying to save space on something that simply needs more room.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

PSB SSChampion Points: 13195 More actions · Answer 9

Hi All,

Thanks for helping . Attached are a few screenshots of my drives. These are SAS drives . Please provide your recommendation accordingly .

Thanks,

PSB

Wayne West SSC-Insane Points: 22586 More actions · Answer 10

It's generally considered not a good practice to install SQL Server, or at least the system databases and log files, on C:. It's possible for SQL or the OS to grow and choke out the other and cause a system crash.

Personally, I always try to put TempDB and its log on its own volume, preferably on its own spindle or drives if you're on a SAN.

Are you actually running SQL 2005? If you upgrade to a later edition you can compress your backups at the SQL Server level and recover a lot of space that way.

-----
[font="Arial"]Knowledge is of two kinds. We know a subject ourselves or we know where we can find information upon it. --Samuel Johnson[/font]

Jeff Moden SSC Guru Points: 1004366 More actions · Answer 11

We put the following on their own drives whenever possible.

1. C: Operating System with good headroom.

2. D: Programs

3. E: Swap File is on a separate drive.

4. L: LDF files except TempDB and Archive/Scratch databases

5. M: MDF files except TempDB and Archive/Scratch databases

6. R: Backups (R: for "Restore") and are on a separate system.

7. S: Archive and Scratch MDF and LDF

8. T: TempDB files (MDF, NDF, LDF). They're small enough (none of 8 files have grown past 2GB so can't justify separate physical drives)

Not sure how the folks in NetOps do it but they can add space to any drive fairly easily. We don't have to do that very often though. They've been quite generous with allocating space and I'm usually able to give them at least a 3 month warning as to when a drive will become full.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

PSB SSChampion Points: 13195 More actions · Answer 12

PSB

SSChampion

Points: 13195

January 27, 2016 at 11:41 am

#1854791

Thanks All for your valuable input.