October 29, 2014 at 7:37 am
Hello,
The situation is a SQL Server Express R2 database. The database and log files are stored locally on the users PC (please don't ask why; it's a political situation and I'm just trying to help out the users of the database. It's not how we do things usually).
Anyhow, the user's hard disk is 160Gb. Originally the user's hard disk hard 2 80Gb partitions and I've already expanded the primary partition to encompass the entire hard disk because Windows was complaining of limited free space.
Windows 7 takes up +/-10gb. Let's say the user's other programs/user data take up 20Gb. That leaves 130gb. The log file has increased to 117Gb and there's actually 3Gb of free space left so we're going to be facing the same problem before too long.
I've also tried shrinking both the database and log files using the wizard; it freed up around 2Gb.
The only option I haven't tried is the 'Reorganise space before releasing unused space' and the 'Shrink file to (file size)' options offered by the Shrink File Wizard (see attached image).
What do these option do? What do I enter for file size if I choose the latter option? What are the repercussions of this? Will I be able to restore the database to its current condition if anything catastrophic happened and I'd already used the above options?
I'd be grateful for any advice.
(P.S. We're also considering buying the user a much larger hard disk.)
October 29, 2014 at 7:43 am
What's the database recovery model?
If it set to "FULL", your log will grow bigger and bigger without a backup process to truncate it.
If you can't afford taking log backups on this machine or you don't need point in time recovery, I suggest that you change your recovery model to "SIMPLE" (which will probably be appropriate for a PC). Once you change the recovery model, you will be able to shring the log file.
-- Gianluca Sartori
October 29, 2014 at 7:54 am
Take a read through these:
http://www.sqlservercentral.com/articles/Administration/64582/
http://www.sqlservercentral.com/articles/Transaction+Logs/72488/
I suspect you have a DB in full recovery and you're not taking log backups. In that case the log will grow until it fills the drive.
If you shrink and specify a file size smaller than the contents of the log, the file will only shrink to the size of the contents of the log.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
October 29, 2014 at 8:36 am
Thank you both for your replies. I need to get back to the users as you've raised some interesting questions about data recovery.
October 29, 2014 at 9:52 am
I hope you don't have any critical data being stored on a user's local drive. I know you said it's a political situation, but storing critical data on a local hard drive without backups is just begging for disaster.
Normal backups would seem to be called for, but then space would be needed to store them.
October 29, 2014 at 10:18 am
Ed Wagner (10/29/2014)
I hope you don't have any critical data being stored on a user's local drive. I know you said it's a political situation, but storing critical data on a local hard drive without backups is just begging for disaster.Normal backups would seem to be called for, but then space would be needed to store them.
We've bought the user a very large external hard disk for backups, so that shouldn't be an issue! Thanks for the reply.
October 29, 2014 at 12:03 pm
M Joomun (10/29/2014)
Ed Wagner (10/29/2014)
I hope you don't have any critical data being stored on a user's local drive. I know you said it's a political situation, but storing critical data on a local hard drive without backups is just begging for disaster.Normal backups would seem to be called for, but then space would be needed to store them.
We've bought the user a very large external hard disk for backups, so that shouldn't be an issue! Thanks for the reply.
And you're taking proper SQL backups to it and copying them to some secure offsite storage at regular intervals?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
October 30, 2014 at 3:22 am
GilaMonster (10/29/2014)
M Joomun (10/29/2014)
Ed Wagner (10/29/2014)
I hope you don't have any critical data being stored on a user's local drive. I know you said it's a political situation, but storing critical data on a local hard drive without backups is just begging for disaster.Normal backups would seem to be called for, but then space would be needed to store them.
We've bought the user a very large external hard disk for backups, so that shouldn't be an issue! Thanks for the reply.
And you're taking proper SQL backups to it and copying them to some secure offsite storage at regular intervals?
Yes, to both.
November 4, 2014 at 3:13 am
Thanks to all for your suggestions so far. I've had a chance to speak to the user about how we proceed and I've summarised below.
1. Make full backup of database and log files
2. Change 'Recovery Model' to 'Simple'
3. Shrink files
Questions: Shrink both database and log files or just the log file?
Do I need to use only the 'Free unused space option' or is the other option ('Reorganise data etc' and 'Shrink file to (size)' relevant?
4. Continue with full daily backups
Any comments would be very welcome.
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply