June 11, 2014 at 4:06 am
Hi - I have a SQL DB which is around 55GB in size. The Server space available is just short of this so I currently have no way of running a full backup, however I do have trn logs running successfully & a full backup for the 6th June.
I've discussed this with our Technical Services Department who have agreed to increase the space but want to know what to increase this to.
Can you please advise how I can check how much space is currently being used and what steps to take to determine growth, to ensure I suggest an appropriate amount of space.
I'm looking to keep 2 days worth of retention for the backup.
Many thanks
Dax
June 11, 2014 at 6:52 am
Is 55GB the size including the transaction log and free space?
The backup size can be calculated as the total database size - free space - size of the transaction log.
Also, you can enable backup compression, assuming you are running an Enterprise edition of SQL Server
June 11, 2014 at 7:10 am
SQLSACT (6/11/2014)
Also, you can enable backup compression, assuming you are running an Enterprise edition of SQL Server
It's available in Standard edition if you're running 2008 R2 or higher:
http://msdn.microsoft.com/en-us/library/cc645993%28v=sql.105%29.ASPX
June 11, 2014 at 7:23 am
Hi - Many thanks for that, unfortunately I won't be able to make use of the compression as the DB is running on SQL2008 standard edition.
The mdf is 55,632,128kb & the ldf is 2,377,088. How do I query the free space? Is there a view or table I can query to get an idea of how this is growing to give me a better idea of future growth, as I don't want to be in the position of having to increase space again in the short term?
Many thanks for your help.
Regards
Dax
June 11, 2014 at 7:33 am
To check free space, just right click on the database and select properties
June 11, 2014 at 7:45 am
A good place to start is to look at the 6th June backup. What size was that? Assuming your database hasn't had any significant growth between then and now, multiply by three (because you'll want to make sure that your third backup completes before you delete the oldest) and then allow for transaction log backups. If you can look at the size needed for those in the msdb.dbo.backupset table. You'll need to average it out over a long period, making sure you include the times of highest log activity.
Don't forget to include space for backing up system databases and other databases you have on the server, and maybe for taking ad hoc backups.
John
June 11, 2014 at 8:04 am
right click the database, select reports - standard reports - disk usage. The space used in the data files will be a very good approximation of your backup size.
this query will show you the size of backups over time (run it in the context of your database) -
declare @dbname sysname
set @dbname = db_name()
print @dbname
select backup_start_date,backup_finish_date,backup_size/1048576 as 'size in MB'
from msdb..backupset
where database_name = @dbname and type = 'D'
---------------------------------------------------------------------
June 11, 2014 at 11:56 am
You can run differential backup. For these 4 days, it will be smaller than full.
June 12, 2014 at 3:31 am
SQL Guy 1 (6/11/2014)
You can run differential backup. For these 4 days, it will be smaller than full.
Not necessarily. If, for example, you've rebuilt all your indexes during that time, the differential will be about the same size as the full.
John
June 12, 2014 at 4:24 am
Hi - No index work has been carried out on the DB. Would it be safe to run a differential on the DB? I have 49gb available on the drive and the mdf is around 56gb.
Many thanks
Dax
June 12, 2014 at 5:07 am
Dax
A differential backs up every page (or is it extent?) that has been changed since the last full backup. The index rebuild was just an example - if anything else has altered most pages in your database then the differential is going to be close to the size of the full. Otherwise, you should be safe. Nevertheless, I wouldn't recommend trying it if this is a production server and the disk you are backing up to is shared with any database or log files. Do you have the option to back up over the network?
John
June 12, 2014 at 5:43 am
Hi - I do but that would mean changing the user account for the SQL Agent Service via configuration manager to use a doamin user, to give the appropriate rights to write to the UNC path.
I've read somewhere that when changing the Agent account you may need to also bring down the MSSQLSERVER Service?
Many thanks
Dax
June 12, 2014 at 5:46 am
Dax
No, you can stop just SQL Server Agent. Or you could use a proxy account. Bear in mind that will only work for scheduled backups. If you do them ad hoc then the SQL Server account will need permissions on the destination location as well.
John
June 12, 2014 at 5:49 am
That's great, thanks for that
June 12, 2014 at 9:28 am
Agreed, no.
But always use the SQL Server management tools to change SQL accounts, never the Windows tools.
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
Viewing 15 posts - 1 through 15 (of 16 total)
You must be logged in to reply to this topic. Login to reply