Hot answers tagged

9

What you're trying to do would leave the database in a (transactionally) inconsistent state, hence it isn't possible. The Partial Database Availability whitepaper is a useful reference guide and includes an example of how to check whether a particular table or file is online. If your data access were through stored procedures, you could relatively easily ...


5

60.000 files and 50 GB should be absolutely no problem. You might want to read "Special Consideration for Large Environments". A challenge could be to come up with a recovery plan.


5

this isn't exactly how filestream and filetables work, you can't specify the name of an existing share as SQL server will try and create the share that you specify in SQL Server Configuration Manager. This is because the share is not mapped directly to a folder on the filesystem like a normal share, but an NTFS data container that lives in the filestream ...


5

I finally found how to do it. I've found some very old backups. The data changed meanwhile, and it was important to have an up-to-date version of this data, which meant that it wasn't possible to just restore the old backup. Instead, I did the following, thanks to Michael Eklöf on ServerFault: Copy the current .mdf and .ldf files. List the filegroups of ...


5

A good option for you, I would imagine, is to split table logically, so every section would be in its own folder and have under 300k files(=records). In SQL Server logical table split is usually done with Partitioning (this is an Enterprise feature). Partitioning essentially maps sections of table (partitions) to physical storage (filegroups). Various ...


4

You could isolate the table with a FILESTREAM in a separate database and create a reference to it in the PRODUCTION database using a view. This would allow you to do what you want without resorting to hacks.


4

To use FILESTREAM, it has to be configured at the OS and instance level. At the OS level, FILESTREAM is enabled either during the installation of SQL Server 2008 or by running SQL Server Configuration Manager. Once FILESTREAM has been setup, you have to create a FILESTREAM filegroup. You then have to create a data container in the FILESTREAM filegroup. Once ...


3

Actually you can put as many files as you want in a file group. Basically file group feature included in sql server so that you can archive your organization data properly and can query the data efficiently. Its also depend on the hard disk space on server and configuration that which raid model you use for data storage.


3

If your files are going to be >1MB, then FILESTREAM is your best option. You can look at BOL documentation here, which clearly describes and advises you when is the best situation to use it.


3

The problem this caused was that the log file (which also gets backed up) is now unnecessarily large because it includes the FILESTREAM data. I'm not sure if you mean the log file itself is too large or that the log file backups become too large. If it's the former then how often were you backing the log up? Depending on the application design you may be ...


3

A solution for a database set to recovery mode SIMPLE is having the FILESTREAM data in a read-only file group (which isn't your ideal option), and then backing up only the read/write file groups with DIFFERENTIAL like this: BACKUP DATABASE [name] READ_WRITE_FILEGROUPS TO DISK = '' WITH DIFFERENTIAL, COMPRESSION; It will get any data that has changed in ...


2

It seems like insufficient info to tell something definitely. For example, are you deleting through Transact-SQL or through Win32API, etc. The FILESTREAM data is not deleted immediately from file system because SQL Server transaction logging under full and bulk recovery models permit the crash recovery. Have you deleted with CHECKPOINT delete from ...


2

Unfortunately no, not in SQL Server 2008. According to FILESTREAM Overview - multiple containers can be added to a single FILESTREAM Filegroup but only as of SQL 2012.


2

I would suggest double checking all the requirements for enabling FILESTREAM as per: How to: Enable FILESTREAM


2

So the linefeed before peas and dhdhjsk should be skipped but the linefeed before save is part of the data. Which linefeeds should be skipped and which are part of the data? I think such a format is very error prone and should be avoided. Perhaps you can do it the following way but I did not verify this define "," as the physical record separator join ...


2

The error seems to point at the SQL Server Service account not being able to access the filestream data. Try to change the SQL Server Service account using the SQL Server Configuration Manager (NOT the Windows Services manager!) to one of the build in accounts. Then change it right back to the original account (you will need to know the password here!). ...


2

Just as caveat it is best not to disturb the physical files involved with FILESTREAM for a database. Below are a few good links on the architecture: Paul Randal White Paper - FILESTREAM Storage Paul Randal Blog Post - FILESTREAM directory structure As quoted from Paul's blog post above: The FILESTREAM file names are actually the log-sequence number ...


2

Really not sure what else aside from PBM might be set up to try so forcibly to disable xp_cmdshell, but it definitely sounds like something set up by IT in some way. I don't think this is really causing any problems, so you could just wait until the rest of your team is available and bring it up to them. If you want to get more information about it, you ...


2

Although replication may not be High Availability in design, it depends on your definition of HA. Certainly it has been used for HA by many people. If replication is down long enough it can be marked as Inactive. To automatically reactivate a replication, you could try using Kin's response: SQL Server replication subscriptions marked as inactive This ...


2

If you have applied the latest update for SQL Server 2012 - Cumulative Update 2 for Service Pack 3 as at the time of writing - and the issue persists, you should open a support ticket, or report the issue on Connect. The underlying issue shares some common features with an existing report: String summary - slow statistics creation on VARBINARY(MAX) column ...


2

I do not believe your extracted quote is relevant to your problem. Your issue could be that your listener has multiple IP addresses. Under the default configuration, the listener will have multiple A records in DNS and your client will cache only one of them. Each time the cache expires it will randomly grab one of the IP addresses and sometimes it will get ...


2

I feel dirty providing this as an option, but if you choose to segregate the FILESTREAM data into its own database, you could maintain RI between the tables in the separate dbs by way of triggers: Triggers -> Remarks -> Limitations: A trigger is created only in the current database; however, a trigger can reference objects outside the current ...


1

I found a solution. Here is example where filetable name is 'physical_files', target path is 'folder1', target file have stream_id = 'FF9CF452-B522-E411-A464-00259060BBB9' declare @path varchar(MAX); set @path = '\folder'; declare @parent hierarchyid; set @parent = GetPathLocator(CONCAT(FileTableRootPath('dbo.physical_files'), @path)); select @parent ...


1

I believe the mystery is cleared up. The documentation only says that the SQL Server account needs full access to the filestream folder, but when I checked who had access to the main DATA folder, I saw that the SYSTEM account also had access. I gave full rights to the FS folder to the SYSTEM user, and now I don't get this error anymore.


1

Turned out to be an HBSS issue. Typically, I would have had this turned off during install but the environment I'm working with is particularly secured where you can't have HBSS set to "learning mode." it turns out that filestream creates a registry key for each directory created and these exceptions have to be in place before creating the filestream ...


1

You are not sacrificing performance as in database performance using varbinary(MAX). You are not searching on the varbinary(MAX). You are not indexing the column. What is nice about in the table is single consistent backup. The down side is a bigger backup. Delivering the file to the client varbinary(MAX) is going to be less efficient than FILESTEAM or ...


1

Assuming you are doing native backups to disk, and then Veritas backs up to tape: Do your onsite backups to disk. Setup blob storage on Azure (or S3 with Amazon) and then script copying that file to that storage. You would then have Veritas backup that file as well from the local disk to tape. If you happen to be on SQL Server 2012 or higher you can backup ...


1

Did found the answer in SQL Server support site. https://connect.microsoft.com/SQLServer/feedback/details/793017/filestream-problem-when-installing-sql-server-2012-sp1-side-by-side-with-sql-server-2008-r2 It's a bug in SQL Server 2012. Hear is the copy of workaround suggested. A workaround is to put the FILESTREAM containers of either a SQL Server 2012 ...


1

To completely remove FILESTREAM features from a database, you need to perform the following steps. Delete all FILESTREAM columns from all tables Disassociate tables from FILESTREAM filegroups Remove all FILESTREAM Data Containers (filegroup files – you might have more than one of them) Remove all FILESTREAM filegroup (there may be more than one of them) ...


1

Indeed, that was the answer. I set the db recovery model to "Simple", waited a minute for the filestream data to clear up, and then I could remove the filestream file and filegroup.



Only top voted, non community-wiki answers of a minimum length are eligible