HI, We want to move the databases from old server "sql 2000" to the new server "sql 2008", we want to estimate the data files size and the log files for the next 2_3 years anyone know the procedure or steps to achive this issus? i need very urggent answer thanks alot
try this...might help you...SELECT s.database_name, m .physical_device_name, cast (s.backup_size/1000000 as varchar(14))+' '+'MB' as bkSize, CAST (DATEDIFF(second,s.backup_start_date , s.backup_finish_date)AS VARCHAR(4))+' '+'Seconds' TimeTaken,s .backup_start_date, CASE s.[type] WHEN 'D' THEN 'Full'WHEN 'I' THEN 'Differential'WHEN 'L' THEN 'Transaction Log'END as BackupType, s .server_name, s .recovery_modelFROM msdb.dbo.backupset s inner join msdb.dbo.backupmediafamily mON s.media_set_id = m.media_set_idWHERE DATEPART(WEEKDAY, backup_start_date) = 2 and backup_start_date > '2010-06-02' and type = 'D'ORDER BY database_name, backup_start_date, backup_finish_date
you can modify your backup_start_date > '2010-06-02 from which date you want to analyse the backup growth. As well if want to see only full backup then comment rest backup type.
In SQL Server 2008, you have a feature called, Management Data Warehjouse from which you can collecte data for database growth. From that you can predict.
Do you have any statistics in terms what was the growth since last 1 year or 1 month? The easy way is to see what is growth on daily basis taking into consideration of any batch process during weekend or import/export of data on regular basis, just calcuate the size of row on day basis to estimate what will be growth for next few years.