Wednesday, March 6, 2013

[MS SQL Server] DBCC Table Check Long Running/Locking Issues

[MS SQL Server] DBCC Table Check Long Running/Locking Issues

DBCC Table Check Long Running/Locking Issues

Posted: 05 Mar 2013 06:46 AM PST

I have two 'identical' systems, both running SQL 2008 Enterprise with SP2 and Windows 2008 64bit. The DB's are around 800GB with 31,000 tables. When DBCC CHECKDB gets to the TABLE CHECK stage, on one it takes out a Sch-S lock on the table it is doing then clears the lock and moves on to the next table.When it runs that on the other it also takes out a Sch-S lock but doesn't release the previous one before it takes out the next one so the number of locks grows and grows as the DBCC progresses. The DBCC also runs for at least four times longer than normal when this behaviour happens. The estimated_completion_time in sys.dm_exec_requests seems to be correct to start with but extends as the job runs longer and longer. A reboot seems to fix the issue and it has happened 4 times in 12 months.The job isn't explicitly set to use TABLOCK and there are no messages in the errorlog. None of the reasons listed in the help page for DBCC regarding reasons why a snapshot can't be made seem applicable but do I assume it isn't using one??Any thoughts on what could cause this or what to look for next time it happens?

select permission on view

Posted: 06 Mar 2013 04:06 AM PST

I understand one reason to use view is for security.So we have a view and granted a user select permission to that view.But we got a denied message when select the view?The SELECT permission was denied on the object 'table1', database 'mydb', schema 'dbo'.It is denied because it doesn't have select for underlying table. My understand is we don't need underlying table permisson.Why it asks underlying table permission?One thing i notice though the view is different schema than the underlying table, does it make a difference?Thanks

2nd node on Cluster turned off by infrastructure

Posted: 06 Mar 2013 04:23 AM PST

I inherited a virtual Sql2008 (sp1) active/active Cluster for our SharePoint installation.Just determined the 2nd node in the Cluster has been turned off by infrastructure for awhile, business-as-usual.There are multiple articles available for how to remove the 2nd node (restart, setup.exe, maintenance,...)Is the 2nd node uninstall needed or do I now have a psuedo single node cluster?OR if I lose the first node will the Cluster be FUBAR?thanksEDIT: Just realized I placed the Topic in the wrong folder, should have been High - Availability

Database getting into recovery mode

Posted: 06 Mar 2013 03:43 AM PST

Database getting into recovery mode is every morning.

How notificate create database event

Posted: 02 Mar 2011 12:32 AM PST

Hi,I need to detect when a new database is created in my SQL Server and send a mail notification to all dba-s.My idea is to do that with notification services: first i capture the create database event, second i enquee message in the queue and finally i process this queue sending notification mails to all dba-s.Someone could tell me if it is possible? How I can read the messages from a queue to send notifications by mail? How i can see the queue content?Many thanks in advance.

sql connections

Posted: 06 Mar 2013 01:09 AM PST

I want to know whaich connections are using which driver / api to connect to my server.For instance I'm told one application is using ODBC which I am sure it is not.But how do you see ODBC, ADODB, OLEDB, ... from the server's prespective?

Question about DBA authority versus responsibility

Posted: 06 Mar 2013 12:10 AM PST

Hi,I'm looking for some advice for how I can assert myself in this situation without alienating my colleagues or causing trouble.I recently was asked to help fix a problem with an application, at which time I discovered that the application in question is running a SQL Server database that I wasn't aware of. Because it hadn't been set up with the basic log management stuff, the log grew to fill up almost the whole disk, causing the problem. I guess the first issue is that my organization has a separation of duties such that I am not notified about applications that run SQL Server databases. But beyond that, after I asked to be made sysadmin on the server so I can maintain it, I was told that this can be done but it doesn't fall under the databases that I need to maintain. Given that I was called in to fix the problem, of course, I think I am already maintaining it.The problem is, at the moment, that group installs and sets up SQL Server and then hands it over to me for applications that fall under my group. So I can't claim that I install and administer everything. However, I would feel better knowing that I am admin on systems that are running SQL Server, because I am the DBA and should anything happen with those servers, ultimate responsibility would lie with me anyway.I realize I don't have a specific question (maybe there is none), but I'm wondering if anyone out there has guidance for how I should proceed, or what I need to find out in order to proceed.Thanks for any help.- webrunner

Retrieving the T-SQL command syntax for a full and differential backup within a maintenance plan

Posted: 05 Mar 2013 11:28 PM PST

Hello --I have created several maintenance plans for backing up the databases on our server which include Full, Differential, and Transaction Log backups. When I created the plans, I made sure to have a text file generated for the transaction log backup which includes the T-SQL command syntax. However, I forgot to do the same for the Full and Differential backups.I would like to get the command syntax being used for the aforementioned backups, and I wanted to know how would I go about doing that?Thanks.

Lazy Writer

Posted: 05 Mar 2013 04:40 PM PST

Hi AllI want to begin by stating that I have read the corresponding chapters in the SQL 2008 Internals (Kalen Delaney) book and have consulted with other resources as well.I just want to confirm my understanding of the lazy writer process.The lazy writer process looks after both the data cache as well as the plan cache, using different algorithms for each cache.Is this correct?Thanks

System-health extended-event session does not capture latest deadlocks

Posted: 20 Dec 2012 08:04 PM PST

While running the following query to capture the latest deadlocks recorded in the default system-health extended-event session, I noticed that the latest deadlock captured was 3 days ago. However, in the SQL ERRORLOG I see that several deadlocks have occurred as recently as today.Has anyone else noticed this issue?[code="sql"]--http://www.quest.com/whitepaper/how-to-use-sql-servers-extended-events-and-notifications816315.aspx;WITH SystemHealthAS ( SELECT CAST ( target_data AS xml ) AS SessionXML FROM sys.dm_xe_session_targets st INNER JOIN sys.dm_xe_sessions s ON s.[address] = st.event_session_address WHERE name = 'system_health')SELECT Deadlock.value ( '@timestamp', 'datetime' ) AS DeadlockDateTime, CAST ( Deadlock.value ( '(data/value)[1]', 'Nvarchar(max)' ) AS XML ) AS DeadlockGraphFROM SystemHealth sCROSS APPLY SessionXML.nodes ( '//RingBufferTarget/event' ) AS t (Deadlock)WHERE Deadlock.value ( '@name', 'nvarchar(128)' ) = N'xml_deadlock_report'ORDER BY Deadlock.value ( '@timestamp', 'datetime' );[/code]
Posted by at
Labels:

No comments:

Post a Comment

Subscribe to: Post Comments (Atom)

Search This Blog