I'm using SQL Server 2008. My database is almost 2GB in size. 90% of it is one table (as per sp_spaceused), that I need don't for most of my work. I was wondering if it was possible to take this table, and have it backed up in a separate file, allowing me to transfer the important data on a more frequent basis than this one. My guess...
This is puzzling me - in SQL Server 2005/2008, I can specify the default file group which is then used for all data tables. I can also specify another filegroup for all BLOB type fields. But I cannot figure out if and how I can specify a default filegroup for indices..... My setup usually is: * primary filegroup - nothing but the syste...
Hello everyone, I can not think of any reasons why we need to have multiple files inside a file group. The reason why I think of this way is we can control from T-SQL (end user) level about file group, but can not control from T-SQL (end user) level about individual files of a file group. Any comments or ideas why files are still needed...
Hi folks, i've just created a secondary filegroup and wish to move some tables over to it and then make it read-only. I'm not sure how to do this? do i just use the ALTER blah blah TO MyFileGroup ? ...
Hi folks, I'm creating a new DB and have a bunch of static data that won't change. If it does, it will be a manual process AND it will happen very rarely. This data is a mix of varchars and Geographies. I'm guessing it could be around 100K or so in total, over 4 or so tables. Questions Should I put these on a READ ONLY filegroup Ca...
Has anyone had an experience storing user uploaded images in a MSSQL 2008 Database using Filegroups and Filestreams? I read a few articles that seemed to say they are a good idea because you get all the advantages of storing images on disk and in the db. For example I have implemented this for my current project but it seems like I'm d...
hi i am a beginner with sql server 2008, i was trying to add a file to a filegroup so that i an create a table which uses filestream, but i keep getting an errors. here is the code that i am trying: ALTER DATABASE dbtry1 ADD FILEGROUP dbtry1_fg_filestream CONTAINS FILESTREAM GO ALTER DATABASE dbtry1 ADD FILE ( NAME= 'dbtry1_files...
Hi, I have tried taking the backup of database containing filestream and it works fine. I have tried restoring it on other server also and that works too. Now, i am facing a problem. Our database is big in size (approx. 320 GB) and it takes time to backup and restore it. Therefore, the client wants us to suggest some technique to redu...
I always ignore this option when creating a new database on SQL Server 2005, simply because we can ignore something that we do not understand and leave it as it is. (I'm not so into DBA) so now I am curious what it is about. From your experience, when do you think we need to add secondary data files to my database and why do we need it...
Consider a SQL script designed to copy rows from one table to another in a SQL 2000 database. The transfer involves 750,000 rows in a simple: INSERT INTO TableB([ColA],[ColB]....[ColG]) SELECT [ColA],[ColB]....[ColG] FROM TableA This is a long running query, perhaps in part because ColB is of type ntext. There are a handful of CONVER...
Our database is currently at 64 Gb and one of our apps started to fail with the following error: System.Data.SqlClient.SqlException: Could not allocate space for object 'cnv.LoggedUnpreparedSpos'.'PK_LoggedUnpreparedSpos' in database 'travelgateway' because the 'PRIMARY' filegroup is full. Create disk space by deleting unneeded files...
Recently I've been trying to restructure an old database that was not designed with filegroups (just the default PRIMARY) and, among other things, move a bunch of tables to a new Data filegroup residing on a SAN. I know how to migrate the data: ALTER TABLE MyTable DROP CONSTRAINT PK_MyTable WITH (MOVE TO [MyDB_Data]) ALTER TABLE MyTab...
Hello, I have SQL Server 2008 Ent and OLTP database with two big tables. How I can move this tables to another filegroup without service interrupting? Now, about 100-130 records inserted and 30-50 records updated each second in this tables. Each table have about 100M records and six fields (including one field geography). I looking for ...
Okay, I am pretty sure at this point that this cannot be done, but here goes: I have a production SQL Server 2005 database with two filegroups, PRIMARY and SECONDARY. PRIMARY consists of about 2 gigs of typical database data. SECONDARY consists of 15 gigs of archived data that no longer changes. I would like to create a QA database in ...
Can I create a FILESTREAM FILEGROUP on a remote UNC path or is it necessary to have it on a local disk on the server? ...
On our dev boxes, our database resides entirely in the PRIMARY filegroup, and everything works fine. On one of our production servers, recently upgraded from 2005 to 2008, we noticed it was performing slower than it should. On this machine, there are two filegroups - PRIMARY and INDEXES. Both filegroups contain 1 file per logical vo...