Disk is full - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Administration

Disk is full

Author

Topic

albertkohl
Aged Yak Warrior

740 Posts

Posted - 2011-04-11 : 19:16:01

um... OPPS!

okay, so i was running a dbcc dbreindex ([table])

and now my disk drive is full from the logging... how can i setup a log on another disk so that every spills over to the second disk without running out of space in the db?

Thanks!

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2011-04-11 : 19:16:53

Just add the second log via ALTER DATABASE or via the GUI (right click database, properties, ...).

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2011-04-11 : 19:17:05

Are you using SQL 2000?

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

albertkohl
Aged Yak Warrior

740 Posts

Posted - 2011-04-11 : 19:22:02

sql 2k8.

that's what i did. so sql's just smart enough to start using the new log file?

albertkohl
Aged Yak Warrior

740 Posts

Posted - 2011-04-11 : 19:27:09

also, just for future ref, right now i have my log file sitting on a 4-disk stripe (10k rpm drives) and my data is partitioned out across 16 disks. should i have a log file for every disk or something? or is there a rule of thumb i can follow for the logging?

Thanks!
(btw, your probably gunna tell me to do tlog backups more often, but i'm already doing them every 15min, incase that changes your response. )

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2011-04-11 : 19:49:48

If you are using SQL 2008, then why are you using DBCC DBREINDEX?

SQL uses the new log file once the first one fills up. It only ever uses one at a time and only ever spills into the next file once the previous one is full.

You should not have a log file for every disk, just one log file per database unless you've got storage problems.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

albertkohl
Aged Yak Warrior

740 Posts

Posted - 2011-04-12 : 00:10:45

just always used that. what would you recommend to replace it?

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2011-04-12 : 11:04:45

ALTER INDEX

DBCC DBREINDEX is deprecated and has been replaced with ALTER INDEX.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

Jahanzaib
Posting Yak Master

115 Posts

Posted - 2011-04-15 : 14:48:39

There are 2 things for indexes

1-Rebuild
2-Reorganize

When Rebuild the indexes:
When index avg_fragmentation > 30 percent

When ReOraganize the indexes:
When index avg_fragementation < 30 percent

you can check this with dynamic management view
sys.dm_db_index_physical_stats

For automatically Rebuilding of Index visit
http://aureus-salah.com/2010/08/05/sql-server-automate-index-rebuilding/

For further information regarding this view
http://msdn.microsoft.com/en-us/library/ms188917.aspx

Regards,
Syed Jahanzaib Bin Hassan
MCTS,MCITP,OCA,OCP,OCE,SCJP,IBMCDBA

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2011-04-15 : 14:52:29

It's actually a dynamic management function and not a view.

And I like to promote my version: http://weblogs.sqlteam.com/tarad/archive/2009/11/03/DefragmentingRebuilding-Indexes-in-SQL-Server-2005-and-2008Again.aspx

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

Jahanzaib
Posting Yak Master

115 Posts

Posted - 2011-04-18 : 00:55:49

Thanks for correction

Regards,

Syed Jahanzaib Bin Hassan
MCTS,MCITP,OCA,OCP,OCE,SCJP,IBMCDBA

My Blog
www.aureus-salah.com

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources