Archiving

Question

Archiving

MSSQL_NOOB

SSCommitted

Points: 1605
More actions
January 14, 2013 at 10:13 am

#393934

MSSQL2008 with 1 TB Harddisk space
Databases:
DB1 - data for 2012.
DB1_Arch - data for 2011.
*** Inherited this server; looks like he idea was DB1 is the production; and DB1_Arch is the archive of DB1
Question 1: When I issue the following query
SELECT file_id, name, type_desc, physical_name, size /1024 / 1024 AS gbsize, max_size/1024/1024 AS gbmaxsize
FROM sys.database_files ;
I get 96 gbsize for Data file and 2gb for Log file for DB1.
However, when I do Reports, Standard Reports, Disk Usage on DB1, I get 790 xxx.xx MB for data file size (which is about 770gb) and 18 xxx.xx MB (or about 17gb) in log file size.
Which is right?
Question 2: There's a request to archive 2012 data to DB1_Archive; then make a backup of it to a different file server. Then delete data from the current DB1 database. Immediate issue I'm facing - if DB1 is in fact 770gb, and total disk is only 1 TB; how do I archive to DB1_Archive first before deleting from DB1?? The math for the space just isn't there. Any suggestions?
Thanks

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Jason-299789 SSC-Insane Points: 21601 More actions · Answer 1

Check the index sizes on the source tables, as they can sometimes be left off the target database when archiving.

You could also investigate the use of Page level compression on the Target DB, which depending on the data profile could reduce the size significantly.

You could also look at putting the Target database on different server with more space then use the scripts that you were going to use as sources for SSIS and have the target as the destination.

_________________________________________________________________________
SSC Guide to Posting and Best Practices

ScottPletcher SSC Guru Points: 100942 More actions · Answer 2

size /1024 / 1024 AS gbsize

That is not the right computation to get gb. "size" is the number/count of 8K pages. So, to get gb from size, you need to do this calc:

CAST(size / 128.0 / 1024 AS int) AS gbsize

SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.

ScottPletcher SSC Guru Points: 100942 More actions · Answer 3

If the computation "size / 1024 / 1024" yielded 96 gbsize,

the correct computation of "size / 128 / 1024" would yield 8 times that, or:

~768gb

SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.

MSSQL_NOOB SSCommitted Points: 1605 More actions · Answer 4

Thank you all for the corrections.

Anyone with any comments on how do I archive the database? Since MSSQL doesn't support UNC path for MDF - getting error "the file .mdf is on a network path that is not supported for database file" nor a mapped network drive.

e4d4 SSCertifiable Points: 5777 More actions · Answer 5

MSSQL_NOOB (1/15/2013)
Thank you all for the corrections.
Anyone with any comments on how do I archive the database? Since MSSQL doesn't support UNC path for MDF - getting error "the file .mdf is on a network path that is not supported for database file" nor a mapped network drive.

Support, from SQL 2008 without Trace Flag 1807 http://www.brentozar.com/archive/2012/01/sql-server-databases-on-network-shares-nas/ but this should be considered as temporary solution.

MSSQL_NOOB SSCommitted Points: 1605 More actions · Answer 6

Haven't tried this yet http://www.sqlservercentral.com/articles/delete/96780/ because I completed the archiving project a few weeks ago. But will definitely try it the next round of archiving. Thought I post this here for my personal future reference or help anyone that had the same issue I did.