There is no space in the server

Question

Post reply

There is no space in the server

parthi-1705

SSCrazy Eights

Points: 9277
More actions
August 25, 2010 at 9:05 am

#141762

Hi
I am having a server where SQL server 2008 EE is begin used in my production,there is no more space in that server i cant able to even run DBCC Command,so that i thought of shrink files/DB, it is throwing error, how can i solve it This was the question asked by my senior,i have not faced this type of situation i suddenly said add additional Harddisk or dirve i am not sure this is correct or not
even though if it is added how cum it will be solved?
whether there are some other conditions or things can be done to solve ?
Thanks
Parthi
Thanks
Parthi

Viewing 15 posts - 1 through 15 (of 20 total)

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

homebrew01 SSC Guru Points: 55548 More actions · Answer 1

If you restart SQL, you may free up some space from Tempdb that will at least let you function.

Why did you run out of space ? Are your log files being managed properly ? (backed up frequently if full recovery)

parthi-1705 SSCrazy Eights Points: 9277 More actions · Answer 2

Are your log files being managed properly ? (backed up frequently if full recovery)

Yes it is in Full recovery model, log files are done @ proper interval due to some reason it became full.

Thanks

Parthi

Thanks
Parthi

homebrew01 SSC Guru Points: 55548 More actions · Answer 3

homebrew01

SSC Guru

Points: 55548

August 25, 2010 at 1:56 pm

#1211896

What used up the space ?

parthi-1705 SSCrazy Eights Points: 9277 More actions · Answer 4

hi,

It is just the situation that was given to me, it may be due to some more data might have up added additionally or due to some other reason the main thing is how to overcome the situation how to solv if it arrives in future.

The main objective is how to handle this kind of problem.

Thanks

Parthi

:pinch:

Thanks
Parthi

ananthbhaskar Right there with Babe Points: 783 More actions · Answer 5

in this kind of situation,

there are lot of things to be considered, the main thing is what is there in the disk drive????????????

is it DB related only then all the above things work.

and for clearing up the drive space, if it is DB related(like backup files, log files etc)

you can always keep the latest backup file and delete the older in this kind of situation,

and for log file ( never restart the prod server without proper approvals)

try taking the log backup for the DB's which should truncate the log and free up the space orlese after taking the backup type backup log <Db name> with truncate_only which should free up the space

you can try the same thing for tempdb instead of restarting to free up the space and then shrinking the file

Perry Whittle SSC Guru Points: 234013 More actions · Answer 6

parthi-1705 (8/25/2010)
hi,
It is just the situation that was given to me, it may be due to some more data might have up added additionally or due to some other reason the main thing is how to overcome the situation how to solv if it arrives in future.
The main objective is how to handle this kind of problem.
Thanks
Parthi
:pinch:

are you gathering answers for an interview question by any chance ?

-----------------------------------------------------------------------------------------------------------

"Ya can't make an omelette without breaking just a few eggs" 😉

parthi-1705 SSCrazy Eights Points: 9277 More actions · Answer 7

Perry Whittle (8/26/2010)
parthi-1705 (8/25/2010)
hi,
It is just the situation that was given to me, it may be due to some more data might have up added additionally or due to some other reason the main thing is how to overcome the situation how to solv if it arrives in future.
The main objective is how to handle this kind of problem.
Thanks
Parthi
:pinch:
are you gathering answers for an interview question by any chance ?

AS i Quoted fist itself that This was the question asked by my senior to me it might be testing me or to know the answer he asked me that i dont know

:angry:

Is it is possiable to add additional H/D [XP_fixeddrives is used to show the free space if new disk is added will it be reflect here]

:angry:

Thanks

Parthi

Thanks
Parthi

pankushmehta Ten Centuries Points: 1217 More actions · Answer 8

You can start by backing up the TLOG to a network location and then shrinking the log files, which will start to free up some space.

If you add additional storage, it will help, as you can move some of the databases to the new storage. However you will require some downtime, as you will have to detach the database and then move them to the new disks and then reattach them.

if you have some databases that do not get used(can happen even in production environment!), you can back them up and delete them temporarily till you fix the space issue.

Some of the data files can also be big. you can try and shrink them( but this will cause fragmentation in your data).

SQLRNNR SSC Guru Points: 281334 More actions · Answer 9

Hypothetical or reality?

I would go with restarting services - but first I would make sure tlogs are backed up and the log files are a decent size.

Next I would ensure that historical backups are not stored on that server - copy them to the network and delete from the server.

Next I would restart the services if tempdb was large.

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

LDK SSC Enthusiast Points: 186 More actions · Answer 10

You say you are using SQL2008 EE (express edition)?

SQL2005EE had data file size restrictions of 4Gb I think? Once your EE DB grew to 4Gb+ it would start giving erros such as this (no disk space etc)

perhaps something to look at?

LDK

SQLRNNR SSC Guru Points: 281334 More actions · Answer 11

LDK (8/26/2010)
You say you are using SQL2008 EE (express edition)?
SQL2005EE had data file size restrictions of 4Gb I think? Once your EE DB grew to 4Gb+ it would start giving erros such as this (no disk space etc)
perhaps something to look at?
LDK

EE = Enterprise Edition

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

LDK SSC Enthusiast Points: 186 More actions · Answer 12

LDK

SSC Enthusiast

Points: 186

August 26, 2010 at 10:54 pm

#1212717

ah ok, my bad

parthi-1705 SSCrazy Eights Points: 9277 More actions · Answer 13

pankushmehta (8/26/2010)
You can start by backing up the TLOG to a network location and then shrinking the log files, which will start to free up some space.
If you add additional storage, it will help, as you can move some of the databases to the new storage. However you will require some downtime, as you will have to detach the database and then move them to the new disks and then reattach them.
if you have some databases that do not get used(can happen even in production environment!), you can back them up and delete them temporarily till you fix the space issue.
Some of the data files can also be big. you can try and shrink them( but this will cause fragmentation in your data).

How it is possiable to add additional disk to existing disk and how to move the existing data to new disk without restart

How to point the new disk to existing data

I cant able to run a single query in the window howcome i will be able to shrink/backup(backing up the TLOG to a network location) the database/files.

Thanks

Parthi

Thanks
Parthi

SQLRNNR SSC Guru Points: 281334 More actions · Answer 14

parthi-1705 (8/27/2010)
pankushmehta (8/26/2010)
You can start by backing up the TLOG to a network location and then shrinking the log files, which will start to free up some space.
If you add additional storage, it will help, as you can move some of the databases to the new storage. However you will require some downtime, as you will have to detach the database and then move them to the new disks and then reattach them.
if you have some databases that do not get used(can happen even in production environment!), you can back them up and delete them temporarily till you fix the space issue.
Some of the data files can also be big. you can try and shrink them( but this will cause fragmentation in your data).
How it is possiable to add additional disk to existing disk and how to move the existing data to new disk without restart
How to point the new disk to existing data
I cant able to run a single query in the window howcome i will be able to shrink/backup(backing up the TLOG to a network location) the database/files.
Thanks
Parthi

There are multiple ways to add disk space and have the data use the new space. One method is to extend your partition.

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events