January 14, 2013 at 10:13 am
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
January 14, 2013 at 10:33 am
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
January 14, 2013 at 12:23 pm
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) "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".
January 14, 2013 at 12:26 pm
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) "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".
January 15, 2013 at 12:09 pm
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.
January 15, 2013 at 2:02 pm
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.
March 7, 2013 at 3:54 pm
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.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply