SQL Server 2005 Huge DB issue

Question

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 :

1. Shrink the database ( does shrinking gives the space to OS ? )

2. Detach and attach DB from an external disk or network storage. ( Is network storage supported by SQL Server 2005 ? )

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

Answer 1

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

Answer 2

If you have a relevant backup plan running your log file will not grow indefinitely. You will probably find that the space within it is mainly unallocated - as blocks of pages in the log get backed up (depending on your backup/recovery model of course, read up on those options if you are unfamiliar with them) they are marked as free to be used again later but are not released to the OS. Next time your big process runs, causes many inserts/updates and so needs a few Gb of log space MSSQL won't need to ask for more space from the OS as it can just use the blocks that are already allocated to the log file but not currently holding anything that can't be written over.

If you are seeing GBytes of extra growth in your log files every time that weekly process is run then you need to check your backup plan as that means SQLServer is not seeing the log pages as reusable either because your backup regime is not correct for the data and activity (or it could be as simple as you are not taking backups up often enough at all).

Growth in the data files is similar: if there is a large chunk of free space in the files then that will get used next time some extra space is needed instead of asking the OS for more to be allocated. One thing to look out for if your data is unexpectedly large is over-indexing - you might find large tables have indexes that are never really used (either they are not needed at all or don't offer much benefit over another index that is also present). Classic examples of this is finding every column in a wide table has its own index (which is usually, though not always, a sign of bad table/index design), or finding an index for "column1 and column2" and one for column1 on its own: the query planner and runner will be able to use the compound index just as well as the simple one for queries based on column1, so there is no need to use space with the extra index. Maintaining indexes that are not useful also has an impact on write performance. This book has a couple of sections covering common index problems if you want to look at the issue more, and is worth a read for all developers and DBAs anyway IMO.

Shrinking files unnecessarily can lead to excess fragmentation within the files, which can harm performance, so take care if you go that route.

Regarding the switch from RAID1 to RAID5: I would not recommend that. RAID5 often has performance problems with write-heavy database work (it is likely to significantly slow down that huge processes that generates GBytes worth of data and transaction log entries) because of the extra-reads-before-any-write-to-update-checksums issue. Rather than adding one drive to go from R1 to R5 I'd go for two extra drives and use R10 - still double the space but without the write performance issue (that is assuming there is room in the server for two extra drives, of course!). What-ever you do in that area, converting from one RAID arrangement to another is likely to involve a large down-time as you backup the data, rebuild the array, and copy the data back on (unless you buy a whole new set of drives and can have both new and old arrays running at the same time, which will reduce the downtime quite a bit). Another option would be to add two drives as a new RAID1 array and then use Windows' dynamic volumes to span over these two arrays (giving you the size of RAID10, but without some of the performance gain that can be gleaned from striping). OR you could keep the two arrays separate, and keep the data files on one and the transaction logs on the other - keeping the data and logs on separate spindles this way can significantly improve the performance of write operations by reducing the head movements required to update both the log and the data file. This last option also has minimal down-time as once the drive are in the rest can be done "live": create new log files on the new volume and tell SQL Server to migrate all active pages to there from the existing files then drop the old files (there will be performance degradation during this operation, but not down-time).

Answer 3

Need to see what tables are growing so fast.

You could have a problem similar to one I've encountered in the past, where even when the table data was deleted, the space was not freed by the table, so the table had been using/reporting many GB, but had only 100,000 rows (our solution was to rebuild the table).

If there is no problem of hidden data, then you can create new file group on a different drive (storage..etc) and create data files there. After that the solution would be to recreate the big tables and their indexes on this new file group.

Most important now is to understand what/why tables grow. You can see also this question: Is there a relaible way to determine when you should run DBCC CLEANTABLE to reclaim space? for some information regarding space issues.

Answer 4

I suggest you to treat the problem from a different angle as it has been said here as well by SQLRockstar. You are keen to just fix the problem and not to find the cause of the problem which is a wrong approache because the cause will re-appear and give you same troubles.

Things to check/do:

1. Is the database in Full recovery model? If you are using the DB just for Reporting than maybe you should cinsider changing the recovery model to Simple. In this model, SQL Server maintains only a minimal amount of information in the transaction log. SQL Server truncates the transaction log each time the database reaches a transaction checkpoint. Truncation is not equal to Shrink. After Truncate the log file will have perhaps the same size but the space inside will be freed for next growing. And it is better to leave like this, because an additional log file growing will put extra pressure in disks each time it needs to grow the log file.

2. If the Data file is also growing as well you should identify what objects are the biggest inside it and if these can be reduced in space. If you didn't design it, maybe there are unused indexes you are not aware that have big sizes?

Here are some USEFUL CODES to find this out: Table and Index size

Answer 5

First thing you need to check is how much free space available within the data and log files.

SELECT name AS 'FileName' , physical_name AS 'PhysicalName', size/128 AS 'TotalSizeinMB',
    size/128.0 - CAST(FILEPROPERTY(name, 'SpaceUsed') AS int)/128.0 AS 'AvailableSpaceInMB', 
    CAST(FILEPROPERTY(name, 'SpaceUsed') AS int)/128.0 AS 'ActualSpaceUsedInMB', 
    (CAST(FILEPROPERTY(name, 'SpaceUsed') AS int)/128.0)/(size/128)*100. as '%SpaceUsed'
    FROM sys.database_files;

Simply trying shrinking won't help if there is no free space in the files. Also look at the auto growth settings on the data + log files. There are some bugs + inadvertently people set a large auto growth and sometimes that is too much.

Generally in the cases like yours, I won't recommend SHRINKING and adding more space is the right way to go.

Answer 6

data files about 90 GB. log files are about 20 GB. – Olgun Kaya 19 hours ago

I second that sankar.

Don't shrink database it will grow again and again and cause the fragmentation.

comparing 90 gb data file with 20 gb log file is too huge.Why the log file went 20 gb check the recovery models and backup plan.