SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Administration
 filegroups manteinance time consuming
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

alejo46
Posting Yak Master

Colombia
127 Posts

Posted - 05/27/2013 :  20:08:54  Show Profile  Reply with Quote
Good evening
because the filefroups are running out of space there are 2 mantainance procedures to increase filegroups space depending on 2 scenarios:

1st scenario OK
1.backup the whole table in a flat file
2.drop the table

2nd scenario (here's the problem)
2.1 if the table is large enough in the business logic there is a script the creates a temp table based in a where condition i.e: select * into temp_tablexx from tablexx
where start_date >= 20130101 and start_date <= 20130228
2.2 backup the temp_tablexx in a flat file
2.3 delete the records based in the where clause

Im not a DBA nor a developer but according the above script (select into) is not time consuming and overheat other processes by creating the temp table?

Instead wouldnt it better to backup the records to delete in a flat file ?

thanks in advance


James K
Flowing Fount of Yak Knowledge

3559 Posts

Posted - 05/27/2013 :  20:45:51  Show Profile  Reply with Quote
I didn't quite understand the problem you are trying to solve - however, backing up a database table to a flat file is really not ideal for a variety of reasons. The second approach you mentioned can be time consuming because it could potentially require a table scan, and long running transactions. If you are trying to add more disk space or move file groups, see if any of the approaches described on this MSDN page would work for you: http://msdn.microsoft.com/en-us/library/bb522469.aspx
Go to Top of Page

alejo46
Posting Yak Master

Colombia
127 Posts

Posted - 05/27/2013 :  21:15:48  Show Profile  Reply with Quote
thanks you very much, aid ive got a question: what are some reasons we shouldnt backup to a flat table?
2.I read the MSDN page you shared and its quite useful but there must be enough available space in disks, right ?
3. in case theres no space left in disks its better to backup the records to delete in a flat file instead creating the temp table ?
Thanks in advance
Go to Top of Page

jackv
Flowing Fount of Yak Knowledge

United Kingdom
2004 Posts

Posted - 05/28/2013 :  01:25:04  Show Profile  Visit jackv's Homepage  Reply with Quote
If the data can be archived - think about creating a regular archive procedure. You could set up an archive database on cheaper storage space.
Some questions and issues on archiving databases - http://www.sqlserver-dba.com/2013/01/sql-server-archiving-data-into-a-separate-database.html

Jack Vamvas
--------------------
http://www.sqlserver-dba.com
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.06 seconds. Powered By: Snitz Forums 2000