disk size
-
i want to determine the disk sizes whether to increase or not . as we are facing the backup jobs failed due to low disk space . on what factors need to consider to analyse to suugest .
ex: i have a server of 205 gb of disk space where the backups are taking on daily basis with a retention of 3 days , some times backup jobs failed due to low disk space , then in shrinked the log files and increased the space and .trn ving the retention of 1 week .
pls suggest on this
-
ramyours2003 (1/22/2010)
i want to determine the disk sizes whether to increase or not . as we are facing the backup jobs failed due to low disk space . on what factors need to consider to analyse to suugest .Its depends on ur environment.You only have to determine the disk size.
I ll tell the way(or clue).
--Pass the Db name here
SELECTDATABASE_NAME,[TYPE],BACKUP_FINISH_DATE,
(BACKUP_SIZE/1024/1024) SIZE_MB,PHYSICAL_DEVICE_NAME,
[USER_NAME]
FROM MSDB.DBO.BACKUPSET A JOIN MSDB.DBO.BACKUPMEDIAFAMILY B
ON(A.MEDIA_SET_ID=B.MEDIA_SET_ID) WHERE
--PHYSICAL_DEVICE_NAME like 'e%'and
DATABASE_NAME in('DBname')and
--type ='l' and
BACKUP_FINISH_DATE>=GETDATE()-4 order by BACKUP_FINISH_DATE desc
Edit:For more info
Muthukkumaran Kaliyamoorthy
https://www.sqlserverblogforum.com/ -
thanks for ur response , we ve lot of dbs on a server , how can we calculate the disk size
-
If you are running out of disk space, then it is necessary to increase your disk space.
You could find the disk free space by using Explorer.
If you want to know the database file size and how much the database files will grow, that is a trending and analysis that needs to be done over time. Since you are already out of disk space - it is difficult to do that part to figure out how much disk to upgrade.
If you want to find the size of each database to determine whether to keep that database on the same server or move to a different server - there are scripts in the scripts section that would help for that. I recommend browsing them and finding one that suits your needs.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events -
ramyours2003 (1/22/2010)
i want to determine the disk sizes whether to increase or not . as we are facing the backup jobs failed due to low disk space . on what factors need to consider to analyse to suugest .ex: i have a server of 205 gb of disk space where the backups are taking on daily basis with a retention of 3 days , some times backup jobs failed due to low disk space , then in shrinked the log files and increased the space and .trn ving the retention of 1 week .
pls suggest on this
You have to gather stats of various databases size by what size it is growing. Once you have ample data then you can come to an conclusion the rate at which database size is growing, along with this you also have to get sizes various types of backups. Once you have this you will have fair amount of idea what is your requirement of disk space. Once you arrive at a number I always give another 30 - 50% of extra bandwidth. We always calcuate the number keeping in mind for next two years.
How are you backing up your database through native or third party tools?
If you can make use of 3rd party tools by which you can take advantage of compression.(If you are planning to migrate to SQL 2008, then compression feature is there and you can take advantage of that). By doing so you gain around 70 -80% of space directly from compression.
As far as 3rd party backup tools there are so many available but I have worked only with SQLLItespeed and Hyperbac so I can talk about this. Advantage with Hyperbac no change of code is required if you make use of this product.
You have mentioned that backups will be retained for 3 days, is it possible for you to keep only one day backup at any given point of time? This way you can save good amount of space.
Similarly for transaction log backups, what we do is we keep only 2 days worth of transaction log backups and delete all tlog backups older than penultimate full backup.
To start with initally if possible retaining one full backup and two days worth of transaction log backups will give you ample space.
-
I use the backup history data in MSDB to look at database growth over time.
You could copy your backup files to another server or to tape to free up space.
-
homebrew01 (1/22/2010)
I use the backup history data in MSDB to look at database growth over time.I don't this so this will give you good results. consider the case if the database only under goes changes by doing updates, database size would remain same, but backup files will not be consistent.
-
murthykalyani (1/22/2010)
homebrew01 (1/22/2010)
I use the backup history data in MSDB to look at database growth over time.I don't this so this will give you good results. consider the case if the database only under goes changes by doing updates, database size would remain same, but backup files will not be consistent.
The transaction log backups can vary a lot in size based on activity, but the FULL backup should consistently reflect the database size (minus free space).
-
The transaction log backups can vary a lot in size based on activity, but the FULL backup should consistently reflect the database size (minus free space).
Agree with you, but the free space of database will all keep on changing, if to have more accurate results, we have to take free space of database immediately after backup is done, and at times backup mail fail. To me it is convoluted.
The best way is to directly gather from master.dbo.sysaltfiles or master.sys.master_files (SQL 2005 and above).
-
I was suggesting using the backup information to get growth trends from the past to help in planning for the future. They will not be accurate for point in time today, but can be useful as a general guide for space planning.
If you see that Database_A backup size has grown 4% per month over the last year, and Database_B has grown 7% per month over the last year, then you can get an idea of what disk space you will need 1, 2 and 3 years from now.
-
Homebrew's suggestion is an excellent way to stablish growth trends.
-
Using backup history assumes you don't delete backup history as some people do. It was pointed out in another thread a little while back that having large history files in MSDB can hinder SQL performance. I plan to remove t-log backup history, while retaining full backup history.
-
I also would agree that it looks to be a very efficient method of trending your database growth.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events -
thanks to every one who shared your valubale inputs on this .
-
As far as 3rd party backup tools there are so many available but I have worked only with SQLLItespeed and Hyperbac so I can talk about this. Advantage with Hyperbac no change of code is required if you make use of this product.
Quest LiteSpeed also has a new version available, the LiteSpeed Engine for SQL Server, that works without any code changes on your part. You install it, and you immediately get compressed backups without changing your backup jobs, maintenance plans, etc.
Viewing 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply