Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Archiving Expand / Collapse
Author
Message
Posted Monday, January 14, 2013 10:13 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Wednesday, October 8, 2014 8:57 AM
Points: 57, Visits: 168
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
Post #1406832
Posted Monday, January 14, 2013 10:33 AM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Friday, October 3, 2014 9:25 AM
Points: 895, Visits: 2,432
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
Post #1406841
Posted Monday, January 14, 2013 12:23 PM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 3:31 PM
Points: 2,197, Visits: 3,307

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)

Carl Sagan said: "There is no such thing as a dumb question." Sagan obviously never watched a congressional hearing!
Post #1406901
Posted Monday, January 14, 2013 12:26 PM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 3:31 PM
Points: 2,197, Visits: 3,307
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)

Carl Sagan said: "There is no such thing as a dumb question." Sagan obviously never watched a congressional hearing!
Post #1406905
Posted Tuesday, January 15, 2013 12:09 PM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Wednesday, October 8, 2014 8:57 AM
Points: 57, Visits: 168
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.
Post #1407417
Posted Tuesday, January 15, 2013 2:02 PM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Today @ 2:42 PM
Points: 224, Visits: 1,732
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.
Post #1407489
Posted Thursday, March 7, 2013 3:54 PM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Wednesday, October 8, 2014 8:57 AM
Points: 57, Visits: 168
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.
Post #1428310
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse