I have a problem... One of my database files both data and log file is huge and when i am trying to shrink the files and database it's not shrinking since space available is 0 %.
Please advice me how to get the space in file system during this type of problem.
Thanks Leslie Richards
asked Aug 29 '11 at 02:00 PM in Default
To shrink a data file or log file you have to issue a checkpoint first. What version of SQL Server are you using?
answered Aug 29 '11 at 02:01 PM
OK. Huge log file would indicate either a massive amount of transactional activity, or no transaction log backups have been scheduled. I go with the latter.
Make sure that, as well as doing a "Full" or "Differential" backup, you're also doing a "Transaction Log" backup. Schedule this to run, well, according to how much data you can afford to lose... If it helps, I have some servers that back up TLogs every 15 minutes, and others that back up TLogs every hour. And some (non-production) servers that have all databases set to "SIMPLE" recovery mode which remove the need for transaction log backups.
OK, this is a slightly elderly post, but it's a good starting point... http://www.simple-talk.com/sql/backup-and-recovery/sql-server-2005-backups/
Once you've corrected the lack-of-transaction-log-backup issue, you may find you need to wait a little while before you can successfully shrink the transaction log file.
Is the hard drive completely full where the data files are stored? You say the data files are full but that might be simply because autogrowth is turned off. If there is drive space then you could grow the data files a little to get you through this time while you review and fix the situation.
Do you have alternative hardware that some of these databases can be moved to?
You need to meet with hardware team and system managers and get the needs of the company matching up with the capabilities of the hardware and then get busy in the middle making sure the data is kept safe and secure.
You need to review each database and consider its recovery model, DR/BC plans, backup strategy and growth predictions for the next 12 months. Once you have that then you can start to plan and monitor the databases and adjust your plans/predictions accordingly.
answered Aug 29 '11 at 03:44 PM