databases file space utilization 100%?

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2005 » Administering » databases file space utilization 100%?

databases file space utilization 100%?

Rate Topic

Display Mode

Topic Options

Author

Message

MSQLDBA

Posted Tuesday, January 10, 2012 11:26 PM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, June 21, 2012 8:49 AM
Points: 129, Visits: 551

Hi All,
Today i monitor my databases i found some of my databases are utilized 100% file space. What is the best solution to fix it,
1) Shrink db or
2) create new .ndf file.
or any other solution?

Thanks in advance.
Regards,
M.I

________________________________________
M.I.

Learning is a path with no destination...

Post #1233733

Suresh B.

Posted Wednesday, January 11, 2012 12:23 AM

Ten Centuries

Group: General Forum Members
Last Login: Wednesday, June 12, 2013 2:54 AM
Points: 1,076, Visits: 5,128

You have not mentioned whether you have monitored the drive space.
Is AutoGrow is ON?

1) You can't shrink file, if 100% file space is utilized
2) If you have free space is in the drive, no need to add new .ndf file.

Additional notes:
1) If AutoGrowth if ON, file will grow automatically.
2) Shinking DB is NOT a best practice
3) Pre-size your files.

Post #1233753

Jayanth_Kurup

Posted Wednesday, January 11, 2012 12:45 AM

SSCommitted

Group: General Forum Members
Last Login: Tuesday, May 07, 2013 6:43 AM
Points: 1,785, Visits: 1,008

By file space do you mean space on the drive ( disk space) or the file has reached the max size for the file .

You mentioned ndf , but most of the time the file that first reach the limit if the ldf file could u confirm which file your referring to .
The answer could depend on which environment the database is hosted on. E.g if it was a dev enviornment you could get away with just shrinking the db. If its a prod enviornment you might want to visit your sizing or your backup plans and check the autogrowth and max file size properties for the database.

Jayanth Kurup

Post #1233758

Jack Corbett

Posted Wednesday, January 11, 2012 8:59 AM

SSChampion

Group: General Forum Members
Last Login: Yesterday @ 1:41 PM
Points: 10,613, Visits: 11,952

You don't want to shrink and that won't fix the issue if all the space in the file is used. If you have drive space you should grow the file. One thing you can do is use the backup history to determine how the DB is growing and then grow the file to an appropriate size so that it will not need to grow again for a best guess predetermined time based on history. A best practice is have your files set to autogrow a reasonable set MB as a backup to monitoring and growing it on a schedule based on % space available.

Jack Corbett

Applications Developer

Don't let the good be the enemy of the best. -- Paul Fleming

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
How to Post Performance Problems
Crosstabs and Pivots or How to turn rows into columns Part 1
Crosstabs and Pivots or How to turn rows into columns Part 2

Post #1234071

Jeff Moden

Posted Wednesday, January 11, 2012 5:17 PM

SSC-Dedicated

Group: General Forum Members
Last Login: Yesterday @ 9:01 PM
Points: 33,111, Visits: 27,037

MSQLDBA (1/10/2012)

What's using the space? The log file? What is the recovery mode for your databases and are you doing regularbackups?

--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."

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Post #1234481

« Prev Topic | Next Topic »

Permissions