Recently our Domain team created an exclusive OU for DBA. I’d like to control all the SQL service accounts. But SQL server not only has SQL Engine and SQL Agent accounts but also SSIS/SSAS/SSRS/Full Text, etc.....
How do I know all the accounts in SQL Server? If you have SQL 2008+, you can use a new DMV “sys.dm_server_services”.
If you have SQL 2005, you have to use undocumented “xp_regread” function to read registry records that in Windows Server.
It’s also fit to SQL 2008+…
Limitation:
In order to be compatible with SQL 2005 ( no default value for T-SQL variable), I changed the code script method.
A lot of posts that used “RESTORE FILELISTONLY” and “BackupSet” table to grab backup files.
Yes, I agree it would be more accurate for latest backup and correct LSN/DB logical name. But if someone drop/detach DB and left backup histories and backup files. It would be incorrect. Additional, sometimes when you use “RESTORE FILELISTONLY” to grab backup files, it will show “Access Denied” message.
In order to avoid the problem, I use “sys.Master_files” table to match “Backupset” and “BackupsetMediafamily”.
Severity: 14, Logon Login failed . Reason: Failed to open the explicitly specified Database
No comments2014-11-10
I tried to figure it out from someone recommend to find out who tried to login the database.
The user is me and is a database sysadmin. So it slap backup the wrong solution.
What caused it?
The user is me and is a database sysadmin. So it slap backup the wrong solution.
What caused it?
Situation:
I created the customized SQL Endpoint for 3rd party vendor uses. I grant "Connection" permission for the account. Deny "Public" connect to Default [Tcp] Endpoint.
Turn on Firewall (ex: port 4999), Allow Remote Connection in SQL, Enable Tcp portocol, Name Pipe, SharedMemory. But it still showed the Error 64 even if the account was successfully established.
There are two facts of table that DBA want to check
1. For Tables structural change
1. For Tables structural change
IF OBJECT_ID('tempdb..#lastaccess1') IS NOT NULL
DROP TABLE #lastaccess1;
CREATE TABLE
#lastaccess1(
[DBName]
sysname,
[TableNames] NVARCHAR(256),
[create_date] datetime,
[modify_date] datetime,
)
DECLARE @Statement1 VARCHAR(4000)
SET @Statement1 = 'USE [?]
SELECT
''?'' as [DBNAME], [TableName] =
name,
create_date, modify_date
FROM
sys.tables
ORDER BY modify_date DESC'
INSERT INTO #lastaccess1([DBName] ,[TableNames]
,[create_date],
[modify_date] )
EXEC sp_MSforeachdb @Statement1;
SELECT * FROM #lastaccess1 WHERE
[DBNAME] not in ('master', 'msdb','model','tempdb');
DROP TABLE #lastaccess1;
The situation depends on what your SQL environment are? For
my case, I need to “DENY IMPERSONATE ON LOGIN::” to most of DBA logins and
general Logins except real necessary DBAs. But I don’t like to COPY/PASTE
command for lots users (if your server has over 20 accounts), so I created a
script to do this.
It’s an inefficient T-SQL script due to my desperate boss wants to see
the results for all SQL instances… if you can modify it for me, I will be very
appreciated.
Today I've tried failover the server to the other one. It's been failed all the time but not the disks or network adapter.
Just SQL engine and SQL agent..... I cannot find any answers online.......
So I was disappointed and want to reinstall it. When I did that , I found one thing was very interesting..
The DB that our junior DBA installed was not installed as the Failover node but a stand-alone...
OMG..
Just SQL engine and SQL agent..... I cannot find any answers online.......
So I was disappointed and want to reinstall it. When I did that , I found one thing was very interesting..
The DB that our junior DBA installed was not installed as the Failover node but a stand-alone...
OMG..
Msg 1807 Could not obtain exclusive lock on database 'model'. Retry the operation later.
No comments2014-08-12
Errors like this:
Msg 1807, Level 16, State 3, Line 1
Could not obtain exclusive lock on database 'model'. Retry the operation later.
Msg 1802, Level 16, State 4, Line 1
CREATE DATABASE failed. Some file names listed could not be created. Check related errors.
Msg 1807, Level 16, State 3, Line 1
Could not obtain exclusive lock on database 'model'. Retry the operation later.
Msg 1802, Level 16, State 4, Line 1
CREATE DATABASE failed. Some file names listed could not be created. Check related errors.
Replication has 3 roles: Publication, Distribution and Subscription.
The source is from Oracle, and I want the data from Oracle push to SQL Server and not updatable.
Publication: Oracle
Distribution: SQL Server A
Subscriptions: SQL Server A ( I installedont the same Server/Instance), you can add/change it if you want.
The source is from Oracle, and I want the data from Oracle push to SQL Server and not updatable.
Publication: Oracle
Distribution: SQL Server A
Subscriptions: SQL Server A ( I installedont the same Server/Instance), you can add/change it if you want.
http://borntolearn.mslearn.net/btl/b/weblog/archive/2014/03/10/certification-update-sql-server-2014.aspx
To help students stay current with upcoming release of SQL Server 2014, all MCSE-level SQL exams (464 through 467) will be updated on April 24, 2014 to cover solutions based on both SQL Server 2012 and 2014 products.
To help students stay current with upcoming release of SQL Server 2014, all MCSE-level SQL exams (464 through 467) will be updated on April 24, 2014 to cover solutions based on both SQL Server 2012 and 2014 products.
[SQL]HotFix KB2969896 - a dangerous data loss bug in SQL Server 2012 and 2014
No comments2014-07-05
Important!!
The online index rebuild can cause index corruption or data loss when it is used together with concurrent queries that modify many rows in Microsoft SQL Server 2012 Service Pack 1 or Microsoft SQL Server 2014.
This issue occurs when the following conditions are true:
The online index rebuild can cause index corruption or data loss when it is used together with concurrent queries that modify many rows in Microsoft SQL Server 2012 Service Pack 1 or Microsoft SQL Server 2014.
This issue occurs when the following conditions are true:
- The online index build query runs in parallel mode.
- Deadlock error and a fatal error such as "lock timeout" occur in a specific order
FIX: KB2969896
Purposes: Collection of Connections, Sessions, T-SQLs..etc
--sys.dm_exec_connections, http://msdn.microsoft.com/en-us/library/ms181509.aspx
--sys.dm_exec_sessions, http://msdn.microsoft.com/en-us/library/ms176013.aspx
--sys.dm_exec_requests, http://msdn.microsoft.com/en-us/library/ms177648.aspx
--sys.dm_exec_query_stats, http://msdn.microsoft.com/en-us/library/ms189741.aspx
--sys.dm_exec_sql_text, http://msdn.microsoft.com/en-us/library/ms181929.aspx
- These DMV could do a collection of I/O-related database management objects(DMOs), also will help you investigate the related I/O info
- I/O investigation derived from
--sys.dm_exec_connections, http://msdn.microsoft.com/en-us/library/ms181509.aspx
--sys.dm_exec_sessions, http://msdn.microsoft.com/en-us/library/ms176013.aspx
--sys.dm_exec_requests, http://msdn.microsoft.com/en-us/library/ms177648.aspx
--sys.dm_exec_query_stats, http://msdn.microsoft.com/en-us/library/ms189741.aspx
--sys.dm_exec_sql_text, http://msdn.microsoft.com/en-us/library/ms181929.aspx
Situation:
One of the DB I/O issues encountered, and I found the "Log" file and the "Data" file located at the same folder/ the same LUN.
So I need to move the "Log" file to improve the performance.
Environment: Clustered
Preliminary: move the instance to the other node.
Command: Type the command to move tempdb Log file
ALTER DATABASE TempDB MODIFY FILE (NAME = templog, FILENAME = 'H:\MSSQL\Data\templog.ldf') GO
Then if I want to DELETE the old tempdb.ldf, I have to restart SQL Server service
But I forgot, it's on Clustered environment, so when I opend SSMS, I got an error below
Subscribe to:
Posts (Atom)