[SQL Server 2008 issues] DBCC CHECKDB Failing |
- DBCC CHECKDB Failing
- SQL server:warning when editing or changing schema.
- Index on a view
- Detect Database is being recovered condition.
- sql:space at the end of string
| Posted: 13 Apr 2013 07:01 PM PDT Hi,We are running DBCC CHECKDB WITH ALL_ERRORMSGS.After couple of minutes, the execution fails, sql service crashes and we get the following error:Msg 64, Level 20, State 0, Line 0A transport-level error has occurred when receiving results from the server. (provider: TCP Provider, error: 0 - The specified network name is no longer available.)We then retry executing the same command again. In the second attempt the command executes successfully.Why is it failing on the first attempt? |
| SQL server:warning when editing or changing schema. Posted: 12 Apr 2013 11:22 PM PDT In our production sql server we have so many important databases. I have created two login 'admin'(has all permission) and 'read'(has only public and datareader permission) . I have done this only because when i want to see or read the database i use login 'read' so that there is no chance of any mistakes happening like deletion,rename etc when i access in SSMS.(Sometimes it happens i by mistake press f2 button and then some button and table name gets renamed).So i use the 'read' login.When i required to change table columns etc then i use 'admin' login. So i use 'admin; login only when i required and otherwise i use 'read' login only for protecting data.So do i have any option by which i can use my 'admin' login always , but whenever any schema(like column rename,delete) changes i want a alert so that i can came to know about it. Is there any option for doing it? |
| Posted: 13 Apr 2013 09:46 AM PDT Hi,DTA keeps recommending creating views and then building indexes on them. On a few high cost queries, the recommendation will be a 99% performance gain so I really want to do it but in the past I have done it twice and both times it caused an incident when it was time to do inserts on the table with an indexed view. This error has put me in a lot of hot water so I am afraid to make it again."INSERT failed because the following SET options have incorrect settings: 'QUOTED_IDENTIFIER'. Verify that SET options are correct for use with indexed views and/or indexes on computed columns and/or filtered indexes and/or query notifications and/or XML data type methods and/or spatial index operations."Are indexed views as dangerous as they seem?Thanks for reading.Howard |
| Detect Database is being recovered condition. Posted: 13 Apr 2013 12:56 AM PDT I have "Live" databases and log shipped databases co-mingled on the same server.The log ship state that the log shipped databases are in is normally STAND BY unless they are actively being refreshed with the latest logs.I am using the following TSQL.IF EXISTS ( SELECT * FROM sys.databases d WHERE d.is_in_standby=0 AND d.is_read_only=0 AND d.state=0 AND d.name = 'MyDB' AND d.state_desc='ONLINE' )BEGIN Exec ('USE MyDB:Run some sql')END Based on this I would expect that I could bypass all my databases that are being log shipped or are in standby or recovery mode.However, using the above TSQL I still intermittently receive the following error:Database 'MyDB' is being recovered. Waiting until recovery is finished.Is there another way to detect this condition for a database?This is a SQL 2k8 database.Thanks in advance |
| sql:space at the end of string Posted: 12 Apr 2013 07:29 PM PDT I have a sql code like shown belowdeclare @str nvarchar(max),@i intset @i=0set @str='abc 'declare @tbl Table(a nvarchar(max)) insert @tbl select @strwhile (select a from @tbl)<>''begin set @i=@i+1 set @str = substring(@str,2,len(@str)) update @tbl set a=@strselect * from @tblendHere @str has value 'abc ' (there is an space at the end) when above query is executed it will stop when only a space is present in 'a'.Also output of this query is:bcc<here blank>for the above query if i give input @str as 'abcd'then output will bebcdcdd<here blank>So in the first case that is @str='abc ' i want to get output likebcc<here blank><here blank>Now the code is checking for space and because of that i am having problem. But i want it to consider the space at the end also.Same is the problem in sql for len also.Both len('a') and len('a ') will return 1.So if anyone please help on my query so that it will give my desired output. |
| You are subscribed to email updates from SQLServerCentral / SQL Server 2008 / SQL Server 2008 - General To stop receiving these emails, you may unsubscribe now. | Email delivery powered by Google |
| Google Inc., 20 West Kinzie, Chicago IL USA 60610 | |
No comments:
Post a Comment