SQL 2012 :: Determine FILEGROUP On DB In Server
Dec 4, 2014I must determine FILEGROUP on my DB in SQL Server. Because when i wants to retrieve files which is stored in db, it would be face with error about FILEGROUP.
View 1 Replies
ADVERTISEMENT
SQL Server 2012 :: Determine FILEGROUP On DB?
Dec 4, 2014i must determin FILEGROUP on my DB in SQL Server. because when i wants to retrieve files which is stored in db, it would be face with error about FILEGROUP.
View 1 Replies View RelatedSQL 2012 :: Change Name Of FILEGROUP Of DB In Server
Dec 5, 2014I create a db by following code. Now I want to change name of FILEGROUP. How can I do it?
CREATE DATABASE MyDb
ON
PRIMARY ( NAME = MyDat,
FILENAME = 'c:datamydat.mdf'),
FILEGROUP MyGroup CONTAINS FILESTREAM( NAME = My,
FILENAME = 'c:datamy1')
[Code] .....
SQL Server 2012 :: Change Name Of FILEGROUP Of DB
Dec 5, 2014I create a db by following code. now i want to change name of FILEGROUP. how can i do it?
CREATE DATABASE MyDb
ON
PRIMARY ( NAME = MyDat,
FILENAME = 'c:datamydat.mdf'),
FILEGROUP MyGroup CONTAINS FILESTREAM( NAME = My,
[Code] .....
SQL Server 2012 :: Create Filestream For Filegroup For A DB?
Dec 5, 2014i have a DB in SQL Server 2008. and i wants to store file in DB.
how can i create filestream for filegroup for a DB in SQL Server 2008.
SQL Server 2012 :: How To Determine Metadata On DBCC Results
May 26, 2015I can find many examples of loading DBCC results into tables. They all begin with a create table statement defining the results. My question is , other than trial and error, is there a way to determine what data types will be returned. Sure you can say that first element looks like an integer, but is it really a bigint, and that text string can be varchar(max) but will char(2) work.
I'm not looking for an answer for a specific DBCC function, but rather a generic way I can determine the characteristics of any DBCC result set.
I tried
SELECT *
INTO #tmp
FROM OPENROWSET('SQLOLEDB',
'Server=ray;Trusted_Connection=Yes;Database=Ed_sandbox',
'Set FmtOnly OFF; DBCC loginfo WITH tableresults ')
but I got back
Msg 11527, Level 16, State 1, Procedure sp_describe_first_result_set, Line 1
The metadata could not be determined because statement 'DBCC loginfo WITH tableresults' does not support metadata discovery.
SQL Server 2012 :: Determine Read / Write Frequency On Database Table
Oct 6, 2015How do I determine the read/write frequency on a database table? I am trying to do this on a 2012 and 2008 R2 servers.
View 2 Replies View RelatedSQL Server 2012 :: Determine Which Column Is Causing Error Converting Data Type Varchar To Numeric?
Aug 14, 2014I'm moving data from one database to another (INSERT INTO ... SELECT ... FROM ....) and am encountering this error:
Msg 8114, Level 16, State 5, Line 6
Error converting data type varchar to numeric.
My problem is that Line 6 is:
set @brn_pk = '0D4BDE66347C440F'
so that is obviously not the problem and my query has almost 200 columns. I can go through one by one and compare what column is int in my destination table and what is varchar in my source tables, but that could take quite a while. How I can work out what column is causing the problem?
SQL 2012 :: Ignore One Filegroup From Backup
Sep 3, 2014My intention is to make a copy of a database with some data excluded. One way to do this:
- Make a copy-only backup of the source database
- Restore the backup as a new database
- Delete undesired data from the new database
In my case, the source database has two file groups, primary and secondary. The secondary has just one table with huge amount of data and this file group appears as one physical file. Is there any way to make a partial backup, which does not include this file group or table, and it would still be possible to restore the backup to a new database? My point is to avoid copying huge amount of data if it won't be needed.
SQL 2012 :: How To Add New Filegroup To Existing Partition Scheme
Jul 10, 2014How to add some more ranges to existing partition schema and function?
Already My table partitioned on date ranges,
6 partitions , each partition contains 6 moths data, so total data is 3 years.
i.e. 1 partition data- from jan2012 to Jun2012
2 partition data- from july2012 to dec2012
3 partition data- from jan2013 to Jun2013
4 partition data- from july2013 to dec2013
5 partition data- from jan2014 to Jun2014
6 partition data- from july2014 to dec2014
After Jan2015 data will go to Primary file group(Default)
Now customer wants to add two more file groups with these partitions ranges, i.e. jan2015 to jun15 and Jul15 to dec15.
File group and ndf file adding is OK, But
how to alter partition scheme and partition function with these additional ranges to existing partition function and scheme?
partitioned table size is 200 GB.
SQL 2012 :: File And Filegroup Backup Components Greyed Out
Apr 22, 2014I am looking at the file / filegroup level backup and recovery options within SQL Server and I'm struggling with the following concept.
Books online assures me that it is possible to perform a file restore whilst the database is in the simple recovery model.
So I have set up a database with two separate file groups, a read/write primary and a read only "secondary". Each filegroup has 2 underlying data files.
I have then created a "live" customers tables within the primary filegroup and assigned my existing "archive" customers tables within the secondary filegroup.
If I try to perform a file or filegroup level backup within management studio, those options are greyed out. I can only perform a database backup.
If I switch back to the full recovery model, the options are no longer greyed out.
So my question is this, is file level backup and recovery actually supported in the simple user model, do you have to perform this task outside of management studio, or (as is likely) am I missing something crucial?
SQL 2012 :: How To Determine When A Statistic Was Last Used By The Optimizer
Aug 25, 2015Is there a DMV or similar in SQL 2012, or SQL 2008, that shows when a statistic was last used by the optimizer? I would like to cleanup some of the auto-generated stats, assuming it's possible to do so. In particular I'm looking to drop those statistics that were created by one-off queries, data loads, etc, and are now doing nothing but adding to the execution time of Update Statistics jobs.
View 6 Replies View RelatedSQL 2012 :: Determine Size Of Database
Sep 17, 2015I am working on Sql Server 2012. and I have multiple databases there. Out of those, i want to move one of my databases to other SQL server 2012, For that i was trying to get approximate size of my database on current server. As i don't have the admin rights, so i can't get that. Can i get the approximate size by right clicking on database and by using the size property Under Database category to get the size idea?
View 4 Replies View RelatedSQL 2012 :: Determine When SSAS Service Last Restarted?
Oct 20, 2014Is there a way to determine when the SSAS Service last restarted? preferably a [mdx] query like you can do with the DB engine using a T-SQL query but any method that does not going to the servers OS and checking would be fine.
View 1 Replies View RelatedSQL 2012 :: Determine If A Certain Table Has Been Accessed In The Past?
Nov 4, 2015code that I can use to determine if a certain table has been accessed in the past?
View 6 Replies View RelatedSQL Server 2008 :: Different Filegroup For Each Schema In One Database
Mar 4, 2015Can we create the Database with two schema and having the separate file group for each schema.
View 5 Replies View RelatedSQL Server 2008 :: Moving Into A Specific Filegroup
Jun 3, 2015SQL 2008 R2
I have a partitioned table in which one of the partitions is on the Primary filegroup. I want to move the data off of that Primary filegroup, and and on to a new filegroup named RTFG6.
Scheme and function currently defined as:
CREATE PARTITION SCHEME [PS1_Left_id] AS PARTITION [PF1_Left_id] TO ([RTFG1], [RTFG2], [RTFG3], [RTFG4], [RTFG5], [PRIMARY])
CREATE PARTITION FUNCTION [PF1_Left_id](int) AS RANGE LEFT FOR VALUES (10, 15, 35, 48, 53)
I've tried split and merge, and for whatever reason, always end up with the Primary filegroup holding data.
How do i get it off of Primary completely, and onto RTFG1 to RTFG6?
I don't want to export to a holding table and re-create the table if i can avoid it, due to identity columns and relationships with multiple tables.
Problem With Database Filegroup Restructuring In SQL Server?
Mar 7, 2006Hi,I have the following SQL procedure I am running to clean up a filegroupand move all data to a single .MDF file:use <db_name>print 'Move <db_name> db contents to MDF file'DBCC SHRINKFILE ('<db_name>_1_Data', EMPTYFILE)DBCC SHRINKFILE ('<db_name>_Log', EMPTYFILE)DBCC SHRINKFILE ('<db_name>_2_Data', EMPTYFILE)DBCC SHRINKFILE ('<db_name>_log2', EMPTYFILE)goTrouble is that I get the following error:Server: Msg 1105, Level 17, State 2, Line 3Could not allocate space for object '<company_name>. Inv. Line' indatabase '<db_name>' because the 'Data Filegroup 1' filegroup is full.There are five files:- a .MDF file (which is already set as the primary filegroup, 43MB insize);- <db_name>_1_data.ndf (this file is 11GB in size);- <db_name>_2_data.ndf (this file is 4GB in size, empty & cannot beshrunk);- 2 .LDF filesI have been able to remove the "<db_name>_2_data.ndf" filesuccessfully, but moving the objects from filegroup 1 to the primaryfilegroup has yielded the same error message every time. Any ideas onwhat I can do to resolve this?
View 1 Replies View RelatedHow To Determine How Much RAM My SQL Server Is Actually Using???
Jul 20, 2005I've been doing a lot of reading on this and my head is starting tohurt! It seems to be quite a feat to work out how much memory isactually being used by our server.I'm running W2K advanced server with SQL 2000 EE, 8GB of RAM, a min of4GB and a max of 6GB is assigned to SQL server.I'm trying to work out whether we've assigned enough or toomuch/little memory to SQL server. My first thought was to let SQLdymanically manage its own memory and see how much it uses, of coursewhen AWE (/3GB /PAE) is enabled it will just use all that isavailable.In perfmon "target server memory" = 6.1GB, "total server memory" =6.1GB, "total pages" = 768000 ( x 8KB = 6.1GB).My second thought was to use "total pages" - the average "free pages"= average mem used, therefore giving me the average amount of memoryused by SQL. I found out that SQL uses a min of 4GB (the min weassigned) and the max of all the memory, 6GB.Is there an easier way of finding out how much memory is actually usedin this situation or is going by the above average the best way?What i'm unsure about is will SQL just use all memory assigned to ituntil it has the whole DB in memory? 20GB including indexes etc....Any help would be greatly apprechiated.
View 2 Replies View RelatedSQL Server Admin 2014 :: Restore Filegroup From One Database To Another
Mar 14, 2014I have two databases like each other that one is the backup of another. Each DB have 2 filegroups. I want to replace one filegroup from one db to another. How do I do this? Or how do I backup and then restore?
View 3 Replies View RelatedSQL Server Admin 2014 :: Adding New Partition To Filegroup
Nov 28, 2014I work for a 24/7 shop. We currently have a table that is partition on monthly. I have to created a script that will add a new file group, add the new file to the group, and alter the the partition scheme and function. However, I need for this process to not cause a lock on the table. Typically I get the locking and issues when I am run the split command. Is there a way to prevent this from happening?
View 4 Replies View RelatedSQL Server Admin 2014 :: Separate Max Fields From The Others To Another Filegroup?
Jan 18, 2015we have many max fields in our database .
A role in performance says : It's better to separate this fields to another FileGroup with separate file.
Is the result good for performance? and what are the risks?
SQL Server 2014 :: Remove Memory Optimized Filegroup
Apr 28, 2015I read this: [URL] ....
Which says you must drop the database to remove the filegroup.
I deleted all the objects and then backed up the DB and restored it and the filegroup is still there.
I was skeptical but some of the comments made me think this might work.
Do I really have to restore from a pre-memory optimized state?
SQL Server Admin 2014 :: Filegroup By Table Category
Oct 14, 2015I'm being asked to create multiple filegroups for a new database based on the table type, transaction, lookup, misc... From what i'm reading this doesn't make sense. I'm reading either large tables get file groups, nonclustered indexes when they are about the same size of the data, or a few other reasons...
First of all, we are talking about the same disk (please don't ask me about how it is configured) and I'm not sure yet if restoring separate file groups is even going to be necessary.
So here are my questions (beyond, the test and see what happens) because in the end I'm going to probably have to do what i'm told. So this is for my professional knowledge.
1. Does file groups separated by table type make sense?
2. Should you put tables that are queried often together in the same or different file groups.
3. I'm pretty sure you can't restore single file group for write access, am I correct?
Backup And Restore Of Filestream Enabled Filegroup In Server
Oct 16, 2015I am new to DBA activities. I have a database name Sample_DB in SQL Server 2014.
This database has below files
LogicalName FileType
FileGroup
Sample_DB_Rows ROWS Data
PRIMARY
Sample_DB_C1 ROWS Data
Country1
Sample_DB_C2 ROWS Data
Country2
Sample_DB_Docs FILESTREAM Data
FileGroupForDoc
My database has 3 tables namely Tbl1, Tbl2 and Tbl3.
Tbl1 data stored in primary file group
Tbl2 is partitioned based on a key column CountryId and stored in respective filegroup
Tbl3 is enabled to store documents, files etc., and its data will be stored in filestream enabled filegroup.
Problem:
I need to take backup of each file group separately.
I need to restore the backed up file group separately. Is this possible, how to do it.
SQL Server Admin 2014 :: Determine Which Server Is Currently Active For Availability Groups
Nov 25, 2014I want two write a small script to determine which is the currently active (primary) server in the AG.
Right now, I see that using SELECT * FROM SYS.dm_hadr_availability_replica_states I can determine the role. However, when the server goes down and switches to the secondary node, I don't believe that the role changes (or does it?). How do I determine which is the active node?
SQL Server 2008 :: Merge Moves Data Into Wrong Filegroup?
Feb 18, 2015I've got a partitioned table where I am trying to switch the first partition into a staging table, merge the first boundary and later drop the file and the file group.
This is my starting point:
boundary_idBoundary_valuePartitionNumber
11/09/2012 0:002
21/10/2012 0:003
PartitionNumberPartitionRowsFileGroupName
1799AdtLog_Archive_201208
2300AdtLog_Archive_201209
After I switch partition 1 to a staging table I run:
ALTER PARTITION SCHEME My_ps NEXT USED AdtLog_Archive_201209;
and
ALTER PARTITION FUNCTION My_pf() MERGE RANGE ('2012-09-01 00:00:00.000');
I expect the 300 records from the former 2nd partition to stay in AdtLog_Archive_201209, however I get this:
PartitionNumberPartitionRowsFileGroupName
1300AdtLog_Archive_201208
2310AdtLog_Archive_201210
How do I make sure that the data stays in AdtLog_Archive_201209 file group?
How To Determine The Version Of SQL Server Programatically.
Aug 16, 2007What is the official supported way to determine the version of SQL server and installed Service Pack programatically.
Different components such as Analysis Services or SSIS can have service pack applied at different times. So what is the right officially supported way to determine the version of those components.
Thank you in advance.
SQL Server Admin 2014 :: How To Find Used Space In Memory Optimized Filegroup
Jun 11, 2015How do i find Total allocated space and used space of a memory optimized filegroup?
use memory_optimized_db
Go
select (SUM(size)*8.0)/1024.0 as Space,
FILEGROUP_NAME ( data_space_id ) , type_desc from sys.database_files
group by data_space_id,type_desc;
above query gives "current used size of the container " of memory optimized file group but doesn't give Total space detail.
SQL Server Admin 2014 :: Move Text Data From Primary To New Filegroup?
Oct 15, 2015I need to modify a table to reside on a new filegroup and also point TEXTIMAGE_ON to that filegroup instead of PRIMARY. Apparently in the past, the only way to achieve this via SQL is to create a new table, copy over data, drop the old table and rename the new table to the original name. I found this solution in the SQL Server 2005 forum.
Is there any other way to alter this table in order to point the TEXTIMAGE_ON to new filegroup using SQL Server 2014? We are on Standard edition. The technique I am using is the drop constraint (with move option) and add constraint (to new filegroup) commands. The data and indexes move, but not the text data (it still is in primary filegroup).
SQL Server 2008 :: How To Determine Baseline For Servers
Apr 9, 2015I've been put in a situation where I have a number of SQL DB's running on 2005/2008 which I have responsibility for. I've been given limited information so am looking at a starting point to determine where I go from here.
I have of course ensured there is a backup strategy in place to secure the data.
SQL Server 2014 :: Possible To Determine Dataflow Within A Database?
May 14, 2015Is it possible to determine the data flow within a database? e.g. I start by adding/inserting a record in MainTable1, then through a trigger SubTable1, SubTable2 have records inserted into, SubTable3 has a record updated into, then a StoredProcedure1 is run which updates first SubTable4, then a StoredProcedure2 is run which adds a record in SubTable5. So by simply adding a record in MainTable1, I affect 5 other tables.
Imagine a more complex process than this one now (which I'm currently facing and I want to win time and precision; I will still investigate it myself (if I will find an answer first - I will post it here)) Is there a way I could "extract" this flow by using T-SQL?
SQL Server Admin 2014 :: Primary Filegroup For System Objects / Secondary For Data
Jul 27, 2015I have been creating databases in SQL 2008 with a primary filegroup for the system objects and a secondary, marked Default, for the data.
We are preparing a migration to SQL 2014, and the administrator is complaining he won't adopt this structure on the new servers because 'there is no benefit' and 'a backup cannot be restored (!?)'.