Sql-server – SQL Server 2005 Huge DB issue

maintenancesql serversql-server-2005

My DB files are growing very fast ( designer is not me and just a weekly report makes 7 GB growth ). And finally, I don't have enough space in the disk. And number of un-reported weeks counting up.

I have some options to perform but need your recommendations :

Shrink the database ( does shrinking gives the space to OS ? )
Detach and attach DB from an external disk or network storage. ( Is network storage supported by SQL Server 2005 ? )
Change RAID config from 1 to 5 to double the disk size. ( this is safest option but also dangerous of RAID config change. )

Thanks for these great and helpful answers regarding to my question. I have checked the log file and 99% of space is unused. So, I shrunk it. Since the DB is just used when we need some reports ( on time request, not always need to access to db ) I think performance won't be an issue for me.

Best Answer

Shrinking the database will allow for you to give back some space to the O/S, but that space comes at a performance cost as the shrink activity fragments the database. You can fix this by rebuilding your indexes, but that usually causes the space to be reclaimed. And the ides that a shrink would be a solution is false, because you are not addressing the growth issue itself, you are merely trying to put a bandage on a sucking chest wound.

Placing your files on disks that can be expanded easily is your best bet. External storage works well (or a SAN, if possible). You cannot use a UNC path for your database files, but you could use DFS to mount a drive and point to a network location and everything would work fine.

HTH

Related Solutions

Sql-server – Sql Server – Best Practices for Growing Database Files

You should be aiming to auto-grow as little as possible. Seven times a day is excruciating, even with instant file initialization.

Don't do a Shrink Database. Ever. Shrinkfile, maybe, but only after an extraordinary event. Shrinking it just to grow again is an exercise in futility and should actually be called auto-fragment.

If recovery model is simple, there is no way on earth you should need to grow your log file by 250 GB. The used space in the file will clean itself out automatically over time, unless you started a transaction a month ago and have no intentions of ever committing it or rolling it back.

So my advice would be:

Auto-grow the data file manually during a quiet period to a size that will accommodate several months of growth. What are you saving it for in the meantime?

Set the auto-growth increment for the data file to something relatively small (so that it doesn't interrupt users when it does happen), and alert on this event (you can catch it in the default trace, for example, or through extended events). This can tell you that you are hitting the high point you estimated and it is time to grow manually again. At this point you will want to keep this manual in case you want to add a new file / filegroup on a different drive to accommodate the space, since eventually you will fill the current drive.

Auto-grow the log file to, say, twice the largest it's ever been. It shouldn't auto-grow further unless there is some abnormal transaction holding things up. You should monitor for this event as well, so that you know about them.

Sql-server – Identify cause of unusual transaction log growth

This SQL shows queries with most "logical_writes":

SELECT TOP 100
    st.text,
    execution_count,
    total_elapsed_time,
    total_worker_time,
    total_logical_reads,
    total_logical_writes,
    total_physical_reads,
    total_clr_time,
    creation_time, last_execution_time,
    pl.query_plan
FROM sys.dm_exec_query_stats ps with (NOLOCK)
    Cross Apply sys.dm_exec_sql_text(ps.sql_handle) st
    Cross Apply sys.dm_exec_query_plan(ps.plan_handle) pl
ORDER BY total_logical_writes desc
OPTION (RECOMPILE);

This T-SQL could help to find direction, but it is not really what you asked for, as logical write != write. and logical write != log write.

It could be very very interesting to find more complete answer to your question.

Best Answer

Related Solutions

Sql-server – Sql Server – Best Practices for Growing Database Files

Sql-server – Identify cause of unusual transaction log growth

Related Question