Tuesday, October 1, 2013

[SQL 2012] Unable to shrink Database

[SQL 2012] Unable to shrink Database

Unable to shrink Database

Posted: 30 Sep 2013 11:54 PM PDT

I have a production databases running on SQL Server 2008 R2 Ent editionand.The size of this DB is 170 GB. Unallocated space available in this database of around 130 GB. Please see below stats of this database: database_name database_size unallocated space--------------- --------------- ------------------Manager 174929.31 MB 131038.92 MBreserved data index_size unused ------------- ------------ ------------- ----------44329360 KB 38557904 KB 5342496 KB 428960 KBI have tried to shrink this database from GUI & through command (DBCC SHRINKFILE), but unable to do so. Unallocated space is remain the same.I have tried following ways to get this DB shrink:1) Defrag all the indexes then ran DBCC SHRINKFILE2) Trunate T-Log file then ran DBCC SHRINKFILE3) Manually ran Ghost cleanup stored procedure then ran DBCC SHRINKFILEbut finally the Output of DBCC CHECKALLOC is still same: The total number of extents = 740590, used pages = 5808614, and reserved pages = 5923760 in this database.I have tried all possible way but DB is not getting shrink. Can any one help me on this issue?

SQL Server Merge - Pull replication error

Posted: 30 Sep 2013 09:41 PM PDT

I have a configuration problem to create a Merge/Pull replication between two databases SQLServer 2012.The machine Publisher is a Windows Server 2008 R2 not in windows domain, this Server is also the Distributor. The machine Subscriber is a Windows 8 not in windows domain.I have made the following settings: in the Published Server i created the distributor's folder, "repldata" and I also shared it and suser "everyone full control". The path's folder is \servername\repldatasee image: [img]http://www.alessandrosenesi.com/download/distributor.jpg[/img]I created Publisher, see image: [img]http://www.alessandrosenesi.com/download/Publisher.jpg [/img]I created Subscriber see image: [img]http://www.alessandrosenesi.com/download/subscriber.jpg[/img]I have done some settings in Distributor, Publisher and Subscriber but I don't get results, I always have access error.see image: [img]http://www.alessandrosenesi.com/download/log.jpg[/img]-----------------------------------------------------------------------------------Error messages:The schema script 'BOOKING_2.sch' could not be propagated to the subscriber. (Source: MSSQL_REPL, Error number: MSSQL_REPL-2147201001)The process could not read file '\omemacchina\repldata\uncomemacchina$MSSQLSER VER2012_nomereplica\20130930125944\BOOKING_2.sch' due to OS error 5. (Source: MSSQL_REPL, Error number: MSSQL_REPL0)Access Denied.(Source: MSSQL_REPL, Error number: MSSQL_REPL5)-----------------------------------------------------------------------------------Can you tell me where is the problem? I have to do this replication but it looks like to be not possibleThanks in advanceAlessandro

Traces question

Posted: 30 Sep 2013 08:49 PM PDT

Hi,I'm running a server trace to determine where the procedures take more time to execute.I'm tracking events 10, 12, 43 and 45.My procedures call lots of other procedures but my main question is: shouldn't the sum of the time taken inside a procedure (ObjectType = 'procedure name') be the same as the time taken by it's execution (TextData LIKE 'EXEC procedure name')?The procedure proc0 calls proc1 and the sum of proc1 takes 0,23ms (event 45 and objectype = 'proc1'). but event 43 of proc1 takes 5ms... Is the difference from SQL "loading" and parsing proc1? proc1 is already in cache so no time to do that should be taken...Why does this happen?Thanks,Pedro

could not understand what the error is

Posted: 30 Sep 2013 05:20 AM PDT

Hi guys,I got an error message like this....i could not figure out what made it to occur, do any one have any idea???---------------------------------------------------Error Type - System.Data.SqlClient.SqlExceptionError Source - .Net SqlClient Data ProviderError Details - System.Data.SqlClient.SqlException (0x80131904): The SELECT permission was denied on the object 'IncidentAttachment', database 'OHLEGRMS_LOB', schema 'dbo'. at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction) at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, Boolean callerHasConnectionLock, Boolean asyncClose) at System.Data.SqlClient.TdsParser.TryRun(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj, Boolean& dataReady) at System.Data.SqlClient.SqlDataReader.TryConsumeMetaData() at System.Data.SqlClient.SqlDataReader.get_MetaData() at System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString) at System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async, Int32 timeout, Task& task, Boolean asyncWrite) at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, TaskCompletionSource`1 completion, Int32 timeout, Task& task, Boolean asyncWrite) at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method) at System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior behavior, String method) at System.Data.SqlClient.SqlCommand.ExecuteDbDataReader(CommandBehavior behavior) at System.Data.Common.DbCommand.System.Data.IDbCommand.ExecuteReader(CommandBehavior behavior) at System.Data.Common.DbDataAdapter.FillInternal(DataSet dataset, DataTable[] datatables, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior) at System.Data.Common.DbDataAdapter.Fill(DataSet dataSet, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior) at System.Data.Common.DbDataAdapter.Fill(DataSet dataSet) at Microsoft.Practices.EnterpriseLibrary.Data.Database.DoLoadDataSet(IDbCommand command, DataSet dataSet, String[] tableNames) at Microsoft.Practices.EnterpriseLibrary.Data.Database.LoadDataSet(DbCommand command, DataSet dataSet, String[] tableNames) at Microsoft.Practices.EnterpriseLibrary.Data.Database.LoadDataSet(DbCommand command, DataSet dataSet, String tableName) at Microsoft.Practices.EnterpriseLibrary.Data.Database.ExecuteDataSet(DbCommand command) at OTECH.Framework.DataProvider.SQLDataProvider.ExecuteDataSet(String spName, Object[] parameters, Int32& totalrecords) at OTECH.Entity.Framework.IEntity.SelectAllFor(IDBCommand command, Int32& totalrecords) at Ohleg.WebUI.Incident.IncidentAttachments.LoadAttachments() at Ohleg.WebUI.BasePage.OnLoad(EventArgs e) at Ohleg.WebUI.Incident.IncidentBasePage.OnLoad(EventArgs e) at System.Web.UI.Control.LoadRecursive() at System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint)ClientConnectionId:xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxSERVERNAME9/30/2013 1:46:11 PMMicrosoft.Practices.EnterpriseLibrary.ExceptionHandling, Version=5.0.414.0, Culture=neutral, PublicKeyToken=null/LM/W3SVC/4/ROOT-1-130249518647548018522IIS APPPOOL\RMS2013 ProdNumber of Session Variables:5PropertyRequired2069872 = TrueVehicleRequired2069872 = FalseIncidentType2069872 = 1SubmissionRequired2069872 = TrueLastError = System.Data.SqlClient.SqlException (0x80131904): The SELECT permission was denied on the object 'IncidentAttachment', database 'OHLEGRMS_LOB', schema 'dbo'. at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction) at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, Boolean callerHasConnectionLock, Boolean asyncClose) at System.Data.SqlClient.TdsParser.TryRun(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj, Boolean& dataReady) at System.Data.SqlClient.SqlDataReader.TryConsumeMetaData() at System.Data.SqlClient.SqlDataReader.get_MetaData() at System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString) at System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async, Int32 timeout, Task& task, Boolean asyncWrite) at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, TaskCompletionSource`1 completion, Int32 timeout, Task& task, Boolean asyncWrite) at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method) at System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior behavior, String method) at System.Data.SqlClient.SqlCommand.ExecuteDbDataReader(CommandBehavior behavior) at System.Data.Common.DbCommand.System.Data.IDbCommand.ExecuteReader(CommandBehavior behavior) at System.Data.Common.DbDataAdapter.FillInternal(DataSet dataset, DataTable[] datatables, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior) at System.Data.Common.DbDataAdapter.Fill(DataSet dataSet, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior) at System.Data.Common.DbDataAdapter.Fill(DataSet dataSet) at Microsoft.Practices.EnterpriseLibrary.Data.Database.DoLoadDataSet(IDbCommand command, DataSet dataSet, String[] tableNames) at Microsoft.Practices.EnterpriseLibrary.Data.Database.LoadDataSet(DbCommand command, DataSet dataSet, String[] tableNames) at Microsoft.Practices.EnterpriseLibrary.Data.Database.LoadDataSet(DbCommand command, DataSet dataSet, String tableName) at Microsoft.Practices.EnterpriseLibrary.Data.Database.ExecuteDataSet(DbCommand command) at OTECH.Framework.DataProvider.SQLDataProvider.ExecuteDataSet(String spName, Object[] parameters, Int32& totalrecords) at OTECH.Entity.Framework.IEntity.SelectAllFor(IDBCommand command, Int32& totalrecords) at Ohleg.WebUI.Incident.IncidentAttachments.LoadAttachments() at Ohleg.WebUI.BasePage.OnLoad(EventArgs e) at Ohleg.WebUI.Incident.IncidentBasePage.OnLoad(EventArgs e) at System.Web.UI.Control.LoadRecursive() at System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint)---------------------------------------------------------------------------:hehe:
Posted by at

No comments:

Post a Comment

Subscribe to: Post Comments (Atom)

Search This Blog