Thursday, August 1, 2013

[SQL 2012] cannot remove FILESTREAM file

[SQL 2012] cannot remove FILESTREAM file

cannot remove FILESTREAM file

Posted: 01 Aug 2013 12:52 AM PDT

Hello, we just recently migrated to SharePoint 2013 and I'm trying to move around FILESTREAM files on all of our content databases (we're running SQL Server 2012) and I'm getting an error when deleting a FILESTREAM file even if it's empty. The content database is in simple recovery mode and I've emptied the file and confirmed that it is empty but dropping the file brings an error saying it is not empty:[code="sql"]USE WSS_Content_20122013_Classes;GOCHECKPOINT;EXEC sp_filestream_force_garbage_collection @dbname = N'WSS_Content_20122013_Classes';DBCC SHRINKFILE (RBSFilestreamFile, EMPTYFILE);ALTER DATABASE WSS_Content_20122013_Classes REMOVE FILE RBSFilestreamFile [/code]Here's the error I get:File ID 1 of database ID 7 cannot be shrunk as it is either being shrunk by another process or is empty.DBCC execution completed. If DBCC printed error messages, contact your system administrator.Msg 5042, Level 16, State 13, Line 4The file 'RBSFilestreamFile' cannot be removed because it is not empty. Any idea on how to resolve this?

Scaling SSRS

Posted: 31 Jul 2013 11:37 PM PDT

This question has been asked in some form or another before, but not for SQL Server 2012 (that I have found) and without an adequate answer, so I am going to take a shot at explaining what we are doing and hopefully someone will have some experience they can share. We are currently using SQL Server 2012 Reporting Services for some internal business reporting. The current setup has been relatively simple: A single server with SQL Server 2012, along with all of the SQL Server Reporting Services components (tools, web server services, etc) installed locally. All reports run locally on the box though they do reach out via linked server to some external data sources. My main question focuses on scaling this setup, should we decide to open up access to SSRS reports to either more users internally, or possibly even externally via a .NET web application. My two concerns with scaling are cost and performance. In terms of cost, has anyone created a core reporting server with the SQL Server Reporting Services components/databases and then installed the SSRS web services on multiple, separate web servers, pointing them back to the core SSRS server? If so, how has your licensing worked? I have been unable to find a clear answer as to whether you can run such a setup with SQL Server licensing only on the core reporting server...or do you have to license each web server with the SSRS web services installed as well? If so, that makes this an extraordinarily expensive reporting solution to scale out on. If you have successfully built and licensed this type of environment, what has been your experience in terms of performance? Any major gotchas or caveats that you could share? And have you found a solid formula you use for user concurrency vs. number of web servers being used? Thank you ahead of time for any wisdom you can share!

Send Email Alerts immediately depending upon data/text in a column

Posted: 31 Jul 2013 10:04 AM PDT

Hi Experts,We do custom logging into a table. The requirement is that the developers should receive email immediately whenever there is [b]specific data[/b] in that columnAny Input is AppreciatedThanks a ton in adavnce!

Service Pack 1 on SQL Server 2012

Posted: 31 Jul 2013 07:12 AM PDT

On a single VM, I have the following1) A default Instance of SQL Server 2012 (SP1 build number 11.0.3128.0) with database engine, SSAS, SSIS, DQS and MDS etc2) A named instance of SSAS for SSAS tabular mode3) A named instance of SSAS in SharePoint integrated mode for PowerPivot.1) After the initial installation of SQL Server 2012, I have installed the SP1 on default instance. So, all is good. 2) Now, I want to install the sp1 on SSAS tabular mode instance ( I have couple of databases installed on this currently) and SP1 on SSAS in SharePoint integrated mode(Nothing on this instance).Can somebody please provide me a guide or steps or link as to how I can approach this. When I apply service pack, obviously I dont want to disturb my default instance because everything is good there. When I apply service pack on tabular mode, will it effect my existing databases? If yes, how should i approach it? Greatly appreciate if anybody can give me some pointers and I am under the gun to get this done.

List All Jobs & Their Steps

Posted: 31 Jul 2013 05:21 AM PDT

This is another of my quick scripts that I want to store where I can find easily. If you want to take it, please help yourself.This script will return all SQL Agent jobs, and give a step by step break down of what the job does. I included a column called Date_List_Generated to ensure I was looking at the latest version.[code="sql"]Select sJob.Name As Job_Name ,sJob.Description ,sJob.Originating_Server ,sJob.Start_Step_ID As Start_At_Step ,Case When sJob.Enabled = 1 Then 'Enabled' When sJob.Enabled = 0 Then 'Not Enabled' Else 'Unknown Status' End As Job_Status ,Replace(Replace(sCat.Name,'[',''),']','') As Category ,sJStp.Step_ID As Step_No ,sJStp.step_name AS StepName ,Case sJStp.SubSystem When 'ActiveScripting' Then 'ActiveX Script' When 'CmdExec' Then 'Operating system (CmdExec)' When 'PowerShell' Then 'PowerShell' When 'Distribution' Then 'Replication Distributor' When 'Merge' Then 'Replication Merge' When 'QueueReader' Then 'Replication Queue Reader' When 'Snapshot' Then 'Replication Snapshot' When 'LogReader' Then 'Replication Transaction-Log Reader' When 'ANALYSISCOMMAND' Then 'SQL Server Analysis Services Command' When 'ANALYSISQUERY' Then 'SQL Server Analysis Services Query' When 'SSIS' Then 'SQL Server Integration Services Package' When 'TSQL' Then 'Transact-SQL script (T-SQL)' Else sJStp.SubSystem End As Step_Type ,sJStp.database_name AS Database_Name ,sJStp.command AS ExecutableCommand ,Case sJStp.on_success_action When 1 Then 'Quit the job reporting success' When 2 Then 'Quit the job reporting failure' When 3 Then 'Go to the next step' When 4 Then 'Go to Step: ' + QuoteName(Cast(sJStp.On_Success_Step_ID As Varchar(3))) + ' ' + sOSSTP.Step_Name End As On_Success_Action ,sJStp.retry_attempts AS RetryAttempts ,sJStp.retry_interval AS RetryInterval_Minutes ,Case sJStp.on_fail_action When 1 Then 'Quit the job reporting success' When 2 Then 'Quit the job reporting failure' When 3 Then 'Go to the next step' When 4 Then 'Go to Step: ' + QuoteName(Cast(sJStp.On_Fail_Step_ID As Varchar(3))) + ' ' + sOFSTP.step_name End As On_Failure_Action ,GetDate() As Date_List_GeneratedFrom MSDB.dbo.SysJobSteps As sJStp Inner Join MSDB.dbo.SysJobs As sJob On sJStp.Job_ID = sJob.Job_ID Left Join MSDB.dbo.SysJobSteps As sOSSTP On sJStp.Job_ID = sOSSTP.Job_ID And sJStp.On_Success_Step_ID = sOSSTP.Step_ID Left Join MSDB.dbo.SysJobSteps As sOFSTP On sJStp.Job_ID = sOFSTP.Job_ID And sJStp.On_Fail_Step_ID = sOFSTP.Step_ID Inner Join MSDB..SysCategories sCat On sJob.Category_ID = sCat.Category_IDOrder By Job_Status ,Job_Name;[/code]
Posted by at

No comments:

Post a Comment

Subscribe to: Post Comments (Atom)

Search This Blog